[Home] [Help]
PACKAGE BODY: APPS.INV_PICK_RELEASE_PVT
Source
1 PACKAGE BODY inv_pick_release_pvt AS
2 /* $Header: INVVPICB.pls 120.45.12020000.7 2013/03/29 16:31:28 brana ship $ */
3
4 -- Global constant holding the package name
5
6 TYPE all_item_reservation_type IS TABLE OF NUMBER
7 INDEX BY BINARY_INTEGER;
8 g_item_non_inv_rsv all_item_reservation_type;
9 g_item_sec_non_inv_rsv all_item_reservation_type;
10
11 g_backorder_cache all_item_reservation_type;
12 g_cleared_cache all_item_reservation_type;
13
14 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_Pick_Release_PVT';
15 is_debug BOOLEAN := NULL;
16 g_sub_reservable_type NUMBER := 1;
17 g_transaction_type_id NUMBER;
18 g_transaction_source_type_id NUMBER;
19 g_organization_id NUMBER := NULL;
20 g_inventory_item_id NUMBER := NULL;
21 g_default_subinventory VARCHAR2(10);
22 g_default_locator_id NUMBER := -1;
23 g_request_number VARCHAR2(30);
24 g_report_set_id NUMBER;
25 g_use_backorder_cache NUMBER;
26 g_honor_pick_from VARCHAR2(1);
27
28 PROCEDURE print_debug(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
29 BEGIN
30 IF is_debug THEN
31 inv_pick_wave_pick_confirm_pub.tracelog(p_message, p_module);
32 gmi_reservation_util.println('pick release '||p_message);
33 END IF;
34 END;
35
36 FUNCTION check_backorder_cache (
37 p_org_id NUMBER
38 , p_inventory_item_id NUMBER
39 , p_ignore_reservations BOOLEAN
40 , p_demand_line_id NUMBER)
41 return BOOLEAN IS
42
43 l_quantity_reserved NUMBER;
44 l_debug NUMBER;
45
46 BEGIN
47 IF is_debug IS NULL THEN
48 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
49 If l_debug = 1 Then
50 is_debug := TRUE;
51 Else
52 is_debug := FALSE;
53 End If;
54 END IF;
55
56 if is_debug then
57 print_debug('g_use_backorder_cache = '||g_use_backorder_cache, 'INV_PICK_RELEASE_PVT.CHECK_BACKORDER_CACHE');
58 end if;
59
60 -- Check whether backorder caching is turned off
61 -- Bug 6997809, the profile value when No is 2, so changing 0 to 2.
62 IF g_use_backorder_cache IS NULL THEN
63 g_use_backorder_cache := NVL(FND_PROFILE.VALUE('INV_BACKORDER_CACHE'),2);
64 END IF;
65
66 -- Bug 6997809, the profile value when No is 2, so changing 0 to 2.
67 IF g_use_backorder_cache = 2 THEN
68 If is_debug then
69 print_debug('Profile is No, returning FALSE', 'INV_PICK_RELEASE_PVT.CHECK_BACKORDER_CACHE');
70 end if;
71 RETURN FALSE;
72 END IF;
73
74 -- First check to determine if any reservations exist for the demand line
75 If NOT p_ignore_reservations THEN
76 -- Bug 4494038: exclude crossdock reservations
77 SELECT sum(reservation_quantity - detailed_quantity)
78 INTO l_quantity_reserved
79 FROM mtl_reservations
80 WHERE demand_source_line_id = p_demand_line_id
81 AND inventory_item_id = p_inventory_item_id
82 AND organization_id = p_org_id
83 AND nvl(staged_flag,'N') <> 'Y'
84 AND demand_source_line_detail IS NULL;
85
86 IF l_quantity_reserved > 0 THEN
87 if is_debug then
88 print_debug('l_quantity_reserved > 0, returning FALSE', 'INV_PICK_RELEASE_PVT.CHECK_BACKORDER_CACHE');
89 end if;
90 RETURN FALSE;
91 END IF;
92 END IF;
93
94 -- IF an entry exists in the cache for this item verify Org and batch then return TRUE
95 IF g_backorder_cache.EXISTS(p_inventory_item_id) THEN
96 IF g_backorder_cache(p_inventory_item_id) = p_org_id THEN
97 if is_debug then
98 print_debug('Found item cache, returning TRUE', 'INV_PICK_RELEASE_PVT.CHECK_BACKORDER_CACHE');
99 end if;
100 RETURN TRUE;
101 END IF;
102 END IF;
103 -- IF no reservation exists or no match found in cache then return FALSE
104 if is_debug then
105 print_debug('End returning FALSE', 'INV_PICK_RELEASE_PVT.CHECK_BACKORDER_CACHE');
106 end if;
107 RETURN FALSE;
108 END;
109
110 PROCEDURE clear_backorder_cache IS
111 BEGIN
112 g_backorder_cache := g_cleared_cache;
113 END;
114
115
116 PROCEDURE release_mo_tasks (p_header_id NUMBER) IS
117 BEGIN
118 UPDATE mtl_material_transactions_temp
119 SET wms_task_status = 1
120 WHERE move_order_header_id = p_header_id;
121
122 -- 8249710
123 -- the following query updates the wms_task_status of parent records if exist
124 UPDATE mtl_material_transactions_temp
125 SET wms_task_status = 1
126 WHERE transaction_temp_id IN
127 ( SELECT DISTINCT mmtt2.parent_line_id
128 FROM mtl_material_transactions_temp mmtt2
129 WHERE move_order_header_id = p_header_id
130 AND parent_line_id IS NOT NULL
131 );
132
133 END;
134
135 PROCEDURE get_tolerance(p_mo_line_id NUMBER,
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_msg_count OUT NOCOPY VARCHAR2,
138 x_msg_data OUT NOCOPY VARCHAR2,
139 x_max_tolerance OUT NOCOPY NUMBER,
140 x_min_tolerance OUT NOCOPY NUMBER,
141 x_max_tolerance2 OUT NOCOPY NUMBER, --MUOM Fulfillment Project
142 x_min_tolerance2 OUT NOCOPY NUMBER ) IS
143
144 CURSOR c_mo_type IS
145 SELECT move_order_typE
146 FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
147 WHERE mtrl.header_id = mtrh.header_id
148 AND mtrl.line_id = p_mo_line_id;
149
150 CURSOR c_detail_info IS
151 SELECT inventory_item_id,
152 organization_id,
153 requested_quantity,
154 requested_quantity_uom,
155 requested_quantity_uom2,
156 requested_quantity2, --MUOM Fulfillment Project
157 ship_tolerance_above,
158 ship_tolerance_below,
159 source_header_id,
160 source_line_set_id,
161 source_code,
162 source_line_id
163 FROM wsh_delivery_details
164 WHERE move_order_line_id = p_mo_line_id;
165
166 l_detail_info c_detail_info%ROWTYPE;
167 l_minmaxinrectype WSH_DETAILS_VALIDATIONS.MinMaxInRecType;
168 l_minmaxinoutrectype WSH_DETAILS_VALIDATIONS.MinMaxInOutRecType;
169 l_minmaxoutrectype WSH_DETAILS_VALIDATIONS.MinMaxOutRecType;
170 l_quantity_uom WSH_DELIVERY_DETAILS.requested_quantity_uom%TYPE;
171 l_min_quantity NUMBER;
172 l_max_quantity NUMBER;
173 l_quantity_uom2 WSH_DELIVERY_DETAILS.requested_quantity_uom%TYPE;
174 l_min_quantity2 NUMBER;
175 l_max_quantity2 NUMBER;
176 l_req_quantity NUMBER;
177 l_move_order_type NUMBER;
178 l_debug NUMBER;
179
180 l_req_quantity2 NUMBER; --MUOM Fulfillment Project
181
182 BEGIN
183
184 IF is_debug IS NULL THEN
185 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
186 If l_debug = 1 Then
187 is_debug := TRUE;
188 Else
189 is_debug := FALSE;
190 End If;
191 END IF;
192
193 OPEN c_mo_type;
194 FETCH c_mo_type
195 INTO l_move_order_type;
196 IF NOT c_mo_type%FOUND THEN
197 x_return_status := fnd_api.g_ret_sts_error;
198 IF is_debug THEN
199 print_debug('Move Order Line not found ' || p_mo_line_id,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
200 END IF;
201 END IF;
202 CLOSE c_mo_type;
203
204 -- {{ Test Case # UTK-REALLOC-3.2.6:60 }}
205 -- Description: Allocate Requisition move order. No tolerances used.
206 -- Tolerance used for Pick Wave Move orders
207 -- For this release no tolerance for all other types of move orders
208 IF (l_move_order_type = inv_globals.g_move_order_requisition) THEN
209 --SELECT * FROM mtl_parameters;
210 x_max_tolerance := 0;
211 x_min_tolerance := 0;
212 x_max_tolerance2 := 0;
213 x_min_tolerance2 := 0;
214
215
216 ELSIF (l_move_order_type = inv_globals.g_move_order_pick_wave
217 AND NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N') = 'Y') THEN
218
219 OPEN c_detail_info;
220 FETCH c_detail_info INTO l_detail_info;
221 --l_found_flag := c_detail_info%FOUND;
222
223 IF NOT c_detail_info%FOUND THEN
224 x_return_status := fnd_api.g_ret_sts_error;
225 IF is_debug THEN
226 print_debug('Delivery Detail not found ' || p_mo_line_id,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
227 END IF;
228 ELSE
229 l_minmaxinrectype.source_code := l_detail_info.source_code;
230 l_minmaxinrectype.line_id := l_detail_info.source_line_id;
231 l_minmaxinrectype.source_header_id := l_detail_info.source_header_id;
232 l_minmaxinrectype.source_line_set_id := l_detail_info.source_line_set_id;
233 l_minmaxinrectype.ship_tolerance_above := l_detail_info.ship_tolerance_above;
234 l_minmaxinrectype.ship_tolerance_below := l_detail_info.ship_tolerance_below;
235 l_minmaxinrectype.action_flag := 'P'; -- pick confirm
236 l_minmaxinrectype.lock_flag := 'N';
237 l_minmaxinrectype.quantity_uom := l_detail_info.requested_quantity_uom;
238 l_minmaxinrectype.quantity_uom2 := l_detail_info.requested_quantity_uom2;
239
240 WSH_DETAILS_VALIDATIONS.get_min_max_tolerance_quantity
241 (p_in_attributes => l_minmaxinrectype,
242 x_out_attributes => l_minmaxoutrectype,
243 p_inout_attributes => l_minmaxinoutrectype,
244 x_return_status => x_return_status,
245 x_msg_count => x_msg_count,
246 x_msg_data => x_msg_data
247 );
248
249 l_quantity_uom := l_minmaxoutrectype.quantity_uom;
250 l_min_quantity := l_minmaxoutrectype.min_remaining_quantity;
251 l_max_quantity := l_minmaxoutrectype.max_remaining_quantity;
252 l_quantity_uom2 := l_minmaxoutrectype.quantity2_uom;
253 l_min_quantity2 := l_minmaxoutrectype.min_remaining_quantity2;
254 l_max_quantity2 := l_minmaxoutrectype.max_remaining_quantity2;
255 l_req_quantity := l_detail_info.requested_quantity;
256 l_req_quantity2 := l_detail_info.requested_quantity2; --MUOM Fulfillment Project
257
258 --x_max_tolerance := least(greatest((l_max_quantity - l_req_quantity) / l_req_quantity, 0), l_detail_info.ship_tolerance_above);
259 x_max_tolerance := l_max_quantity - l_req_quantity;
260
261 --x_min_tolerance := l_req_quantity - l_min_quantity; --Bug 7238552
262 x_min_tolerance := 0; -- Bug 7449487
263
264 --MUOM Fulfillment Project
265 x_max_tolerance2 := l_max_quantity2 - l_req_quantity2;
266 x_min_tolerance2 := 0;
267
268 IF is_debug THEN
269 print_debug('Return Status '||x_return_status,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
270 print_debug('Req Qty '||l_req_quantity,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
271 print_debug('Max Qty '||l_max_quantity,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
272 print_debug('Min Qty '||l_min_quantity,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
273 print_debug('Qty UOM '||l_quantity_uom,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
274 print_debug('Max Tolerance '||x_max_tolerance,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
275 print_debug('Min Tolerance '||x_min_tolerance,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
276 --MUOM Fulfillment Project
277 print_debug('Req Qty2 '||l_detail_info.requested_quantity2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
278 print_debug('Max Qty2 '||l_max_quantity2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
279 print_debug('Min Qty2 '||l_min_quantity2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
280 print_debug('Qty UOM2'||l_quantity_uom2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
281 print_debug('Max Tolerance2 '||x_max_tolerance2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
282 print_debug('Min Tolerance2 '||x_min_tolerance2,'INV_PICK_RELEASE_PVT.GET_TOLERANCE');
283 END IF;
284 END IF; -- if c_detail_info found
285 CLOSE c_detail_info;
286 ELSE
287 x_max_tolerance := 0;
288 x_min_tolerance := 0;
289 x_max_tolerance2 := 0; --MUOM Fulfillment Project
290 x_min_tolerance2 := 0; --MUOM Fulfillment Project
291 END IF;
292 g_max_tolerance := x_max_tolerance;
293 g_min_tolerance := x_min_tolerance;
294 g_max_tolerance2 := x_max_tolerance2; --MUOM Fulfillment Project
295 g_min_tolerance2 := x_min_tolerance2; --MUOM Fulfillment Project
296 END get_tolerance;
297
298
299 -- This function returns 2 if the reservation does not match the move order
300 -- line, 1 otherwise.
301 FUNCTION reservation_matches(
302 p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
303 , p_res_rec IN inv_reservation_global.mtl_reservation_rec_type
304 )
305 RETURN NUMBER IS
306 l_mo_from_sub VARCHAR2(10);
307 l_mo_from_loc NUMBER;
308 BEGIN
309 -- Bug 2363651 - We will give preference to the reservation, not to the
310 -- value on the move order line. This will remove the issue of excess
311 -- reservations from getting created if the from sub, from locator, rev,
312 -- or is specified on the move order line
313
314 IF p_res_rec.ship_ready_flag = 1 THEN
315 RETURN 2;
316 END IF;
317
318 -- BUG 3352603
319 -- Bug 3195332, removing
320 -- p_res_rec.primary_reservation_quantity-nvl(p_res_rec.detailed_quantity,0)) <=0 condtion,
321 -- as this was creating multiple reservations for the same controls. Keeping the
322 -- p_res_rec.primary_reservation_quantity <0 check for data corruption.
323
324 IF p_res_rec.primary_reservation_quantity <0 THEN
325 return 2;
326 END IF;
327
328 -- BUG 4702900
329 -- In a reversal of the fix made above 2363651 the move order will have
330 -- preference if the profile WSH_HONOR_PICK_FROM is set.
331 -- To avoid the problem of excess reservations, the lines for which the
332 -- from sub on the reservation does not match will not be passed from
333 -- shipping. See 4529693 for further details
334 IF g_honor_pick_from IS NULL THEN
335 g_honor_pick_from := NVL(FND_PROFILE.VALUE('WSH_HONOR_PICK_FROM'),'N');
336 END IF;
337 l_mo_from_sub := p_mo_line_rec.from_subinventory_code;
338 IF l_mo_from_sub IS NOT NULL THEN
339 IF l_mo_from_sub <> nvl(p_res_rec.subinventory_code,l_mo_from_sub) THEN
340 IF (is_debug) THEN
341 print_debug('Reservation with different SUB to move order skipped ',
342 'INV_PICK_RELEASE_PVT.RESERVATION_MATCHES');
343 END IF;
344 return 2;
345 END IF;
346 l_mo_from_loc := p_mo_line_rec.from_locator_id;
347 IF l_mo_from_loc IS NOT NULL THEN
348 IF l_mo_from_loc <> nvl(p_res_rec.locator_id,l_mo_from_loc) THEN
349 IF (is_debug) THEN
350 print_debug('Reservation with different Locator to move order skipped ',
351 'INV_PICK_RELEASE_PVT.RESERVATION_MATCHES');
352 END IF;
353 return 2;
354 END IF;
355 END IF;
356 END IF;
357
358 --Completed reservations should not be used for detailing
359
360 -- If the reservation has not been filtered out yet, it is a match.
361 RETURN 1;
362 END reservation_matches;
363 /* FP-J PAR Replenishment Counts: 4 new input parameters are introduced viz.,
364 p_dest_subinv, p_dest_locator_id, p_project_id, p_task_id. This is as a result
365 of moving Supply Subinv, Supply Locator, Project and Task to 'Common' group
366 from 'Manufacturing' group in Grouping Rule form. */
367 PROCEDURE get_pick_slip_number(
368 p_ps_mode VARCHAR2
369 , p_pick_grouping_rule_id NUMBER
370 , p_org_id NUMBER
371 , p_header_id NUMBER
372 , p_customer_id NUMBER
373 , p_ship_method_code VARCHAR2
374 , p_ship_to_loc_id NUMBER
375 , p_shipment_priority VARCHAR2
376 , p_subinventory VARCHAR2
377 , p_trip_stop_id NUMBER
378 , p_delivery_id NUMBER
379 , x_pick_slip_number OUT NOCOPY NUMBER
380 , x_ready_to_print OUT NOCOPY VARCHAR2
381 , x_api_status OUT NOCOPY VARCHAR2
382 , x_error_message OUT NOCOPY VARCHAR2
383 , x_call_mode OUT NOCOPY VARCHAR2
384 , p_dest_subinv VARCHAR2 DEFAULT NULL
385 , p_dest_locator_id NUMBER DEFAULT NULL
386 , p_project_id NUMBER DEFAULT NULL
387 , p_task_id NUMBER DEFAULT NULL
388 , p_inventory_item_id NUMBER DEFAULT NULL
389 , p_locator_id NUMBER DEFAULT NULL
390 , p_revision VARCHAR2 DEFAULT NULL
391 ) IS
392 p_pr_mode BOOLEAN := WSH_PICK_LIST.G_PICK_REL_PARALLEL;
393 BEGIN
394 x_api_status := fnd_api.g_ret_sts_success;
395 -- Bug 2666620:
396 -- If the Patchset Level of Shipping is less than 'I' then call
397 -- Shipping's GET_PICK_SLIP_NUMBER.
398 -- Otherwise calls Inventory's GET_PICK_SLIP_NUMBER
399 IF wsh_code_control.get_code_release_level < '110509' THEN
400 IF (is_debug) THEN
401 print_debug('Calling WSH_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER',
402 'INV_PICK_RELEASE_PVT.GET_PICK_SLIP_NUMBER');
403 END IF;
404 wsh_pr_pick_slip_number.get_pick_slip_number(
405 p_ps_mode => p_ps_mode
406 , p_pick_grouping_rule_id => p_pick_grouping_rule_id
407 , p_org_id => p_org_id
408 , p_header_id => p_header_id
409 , p_customer_id => p_customer_id
410 , p_ship_method_code => p_ship_method_code
411 , p_ship_to_loc_id => p_ship_to_loc_id
412 , p_shipment_priority => p_shipment_priority
413 , p_subinventory => p_subinventory
414 , p_trip_stop_id => p_trip_stop_id
415 , p_delivery_id => p_delivery_id
416 , x_pick_slip_number => x_pick_slip_number
417 , x_ready_to_print => x_ready_to_print
418 , x_api_status => x_api_status
419 , x_error_message => x_error_message
420 , x_call_mode => x_call_mode
421 );
422 ELSE
423 IF (is_debug) THEN
424 print_debug('Calling INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER',
425 'GET_PICK_SLIP_NUMBER');
426 END IF;
427 /* If p_pr_mode is TRUE then calling the api get_pick_slip_number_parallel
428 for Parallel Pick-Release enhancement */
429 IF p_pr_mode THEN
430 inv_pr_pick_slip_number.get_pick_slip_number_parallel(
431 p_ps_mode => p_ps_mode
432 , p_pick_grouping_rule_id => p_pick_grouping_rule_id
433 , p_org_id => p_org_id
434 , p_header_id => p_header_id
435 , p_customer_id => p_customer_id
436 , p_ship_method_code => p_ship_method_code
437 , p_ship_to_loc_id => p_ship_to_loc_id
438 , p_shipment_priority => p_shipment_priority
439 , p_subinventory => p_subinventory
440 , p_trip_stop_id => p_trip_stop_id
441 , p_delivery_id => p_delivery_id
442 , p_inventory_item_id => p_inventory_item_id
443 , p_locator_id => p_locator_id
444 , p_revision => p_revision
445 , x_pick_slip_number => x_pick_slip_number
446 , x_ready_to_print => x_ready_to_print
447 , x_api_status => x_api_status
448 , x_error_message => x_error_message
449 , x_call_mode => x_call_mode
450 , p_dest_subinventory => p_dest_subinv
451 , p_dest_locator_id => p_dest_locator_id
452 , p_project_id => p_project_id
453 , p_task_id => p_task_id
454 );
455
456 ELSE
457 /* FP-J PAR Replenishment Count: Call the API with 4 new additional
458 input parameters so that these are
459 also honored for grouping for pick wave move orders */
460 inv_pr_pick_slip_number.get_pick_slip_number(
461 p_ps_mode => p_ps_mode
462 , p_pick_grouping_rule_id => p_pick_grouping_rule_id
463 , p_org_id => p_org_id
464 , p_header_id => p_header_id
465 , p_customer_id => p_customer_id
466 , p_ship_method_code => p_ship_method_code
467 , p_ship_to_loc_id => p_ship_to_loc_id
468 , p_shipment_priority => p_shipment_priority
469 , p_subinventory => p_subinventory
470 , p_trip_stop_id => p_trip_stop_id
471 , p_delivery_id => p_delivery_id
472 , p_inventory_item_id => p_inventory_item_id
473 , p_locator_id => p_locator_id
474 , p_revision => p_revision
475 , x_pick_slip_number => x_pick_slip_number
476 , x_ready_to_print => x_ready_to_print
477 , x_api_status => x_api_status
478 , x_error_message => x_error_message
479 , x_call_mode => x_call_mode
480 , p_dest_subinventory => p_dest_subinv
481 , p_dest_locator_id => p_dest_locator_id
482 , p_project_id => p_project_id
483 , p_task_id => p_task_id
484 );
485 END IF;
486 END IF;
487 END get_pick_slip_number;
488
489
490 -- Adding x_rsv_qty2_available parameter to pass the secondary quantity available to reserve bug #7377744
491 PROCEDURE process_reservations(
492 x_return_status OUT NOCOPY VARCHAR2
493 , x_msg_count OUT NOCOPY NUMBER
494 , x_msg_data OUT NOCOPY VARCHAR2
495 , p_demand_info IN wsh_inv_delivery_details_v%ROWTYPE
496 , p_mo_line_rec IN inv_move_order_pub.trolin_rec_type
497 , p_mso_line_id IN NUMBER
498 , p_demand_source_type IN VARCHAR2
499 , p_demand_source_name IN VARCHAR2
500 , p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
501 , x_demand_rsvs_ordered OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
502 , x_rsv_qty_available OUT NOCOPY NUMBER
503 , x_rsv_qty2_available OUT NOCOPY NUMBER
504 ) IS
505 -- A local copy of the move order line record
506 l_qry_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
507 -- Record for querying up matching reservations for the move order line
508 l_qry_rsv_rec_by_id inv_reservation_global.mtl_reservation_rec_type;
509 -- Record for querying up a single reservation record by the reservation ID
510 l_update_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
511 -- Record for updating reservations
512 l_demand_reservations inv_reservation_global.mtl_reservation_tbl_type;
513 -- The table of reservations for the line's demand source
514 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
515 -- The above table in descending order of detail, and with any non-matching records filtered out.
516 l_reservation_count NUMBER; -- The number of reservations for the line
517 l_all_reservation_count NUMBER; -- The number of reservations for the line
518 l_reservation_count_by_id NUMBER; -- The number of reservations returned when querying by reservation ID (should be 1)
519 l_mso_header_id NUMBER; -- The MTL_SALES_ORDERS header ID, which should be derived from the OE header ID
520 -- and used for reservation queries.
521 l_org_wide_res_id NUMBER := -1; -- If an org-wide reservation exists for the move order line, this variable will
522 -- contain its reservation ID.
523 l_res_tbl_index NUMBER; -- An index to the elements of the reservations table.
524 l_res_ordered_index NUMBER; -- An index to the elements of the ordered and filtered reservations table.
525 l_qty_to_detail_unconv NUMBER; -- The quantity which should be detailed, in the UOM of the move order line.
526 l_reserved_quantity NUMBER := 0; -- The quantity for the given move order line which is reserved.
527 l_sec_reserved_quantity NUMBER := 0; -- The quantity for the given move order line which is reserved.
528 l_unreserved_quantity NUMBER; -- The quantity for the given move order line which is not reserved.
529 l_sec_unreserved_quantity NUMBER; -- The quantity for the given move order line which is not reserved.
530 l_qty_available_to_reserve NUMBER; -- The quantity which can still be reserved.
531 l_qty_on_hand NUMBER; -- The org-wide quantity on-hand
532 l_qty_res_on_hand NUMBER; -- The org-wide reservable quantity on-hand
533 l_qty_res NUMBER; -- The org-wide quantity reserved
534 l_qty_sug NUMBER; -- The org-wide quantity suggested
535 l_qty_att NUMBER; -- The org-wide available to transact
536 l_quantity_to_reserve NUMBER; -- The additional quantity which should be reserved.
537 l_sec_qty_available_to_reserve NUMBER; -- The quantity which can still be reserved.
538 l_sec_qty_on_hand NUMBER; -- The org-wide quantity on-hand
539 l_sec_qty_res_on_hand NUMBER; -- The org-wide reservable quantity on-hand
540 l_sec_qty_res NUMBER; -- The org-wide quantity reserved
541 l_sec_qty_sug NUMBER; -- The org-wide quantity suggested
542 l_sec_qty_att NUMBER; -- The org-wide available to transact
543 l_sec_quantity_to_reserve NUMBER; -- The additional quantity which should be reserved.
544 -- Equivalent to the MIN of the unreserved quantity and the quantity available to reserve.
545 l_qty_succ_reserved NUMBER; -- The quantity which was successfully reserved (if a reservation was created)
546 l_quantity_to_detail NUMBER; -- The quantity for the move order line which should be detailed.
547 -- Equivalent to the Move Order Line quantity minus the detailed quantity
548 -- and converted to the primary UOM.
549 l_sec_qty_succ_reserved NUMBER; -- The quantity which was successfully reserved (if a reservation was created)
550 l_sec_quantity_to_detail NUMBER; -- The quantity for the move order line which should be detailed.
551
552 l_primary_uom VARCHAR2(3); -- The primary UOM for the item
553 l_secondary_uom VARCHAR2(3); -- The secondary UOM for the item
554 l_grade_code VARCHAR2(150); -- grade required
555
556 l_msg_data VARCHAR2(2000);
557 l_msg_count NUMBER;
558 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
559 l_api_error_code NUMBER; -- The error code of APIs called within the Process Line API.
560 l_api_error_msg VARCHAR2(100); -- The error message returned by certain APIs called within Process_Line
561 l_count NUMBER;
562 l_message VARCHAR2(255);
563 l_demand_source_type NUMBER := p_demand_source_type;
564 l_non_inv_reservations_qty NUMBER := 0;
565 l_primary_reservation_qty NUMBER := 0;
566
567 l_non_inv_sec_reservation_qty NUMBER := 0;
568 l_secondary_reservation_qty NUMBER := 0;
569
570 l_staged_flag VARCHAR2(1);
571 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
572 l_revision_control_code NUMBER;
573 l_lot_control_code NUMBER;
574 l_lot_divisible_flag VARCHAR2(1);
575 l_serial_control_code NUMBER;
576 l_is_revision_control BOOLEAN;
577 l_is_lot_control BOOLEAN;
578 l_is_serial_control BOOLEAN;
579 source_from_sub VARCHAR2(20);
580 source_from_loc NUMBER;
581 l_return_value BOOLEAN := TRUE;
582 l_new_prim_rsv_quantity NUMBER;
583 l_new_sec_rsv_quantity NUMBER;
584 l_index NUMBER;
585 l_new_rsv_quantity NUMBER;
586
587 -- Bug 2972143
588 l_new_demand_rsvs_ordered INV_Reservation_GLOBAL.MTL_RESERVATION_TBL_TYPE;
589
590 -- Bug 4171297
591 l_order_line_qty_unconv NUMBER; -- The order line quantity, in the order line uom.
592 l_order_line_qty NUMBER; -- The order line quantity, converted in the primary uom.
593 l_order_line_uom VARCHAR2(3); -- The UOM in which the order line quantity is specified.
594 l_primary_staged_qty NUMBER := 0; -- Sum of staged reservation qty for this order line.
595 l_orig_quantity_to_detail NUMBER;
596
597 l_rsv_serials inv_reservation_global.serial_number_tbl_type;
598 l_pri_sec_conv NUMBER;
599 --Expired lots custom hook
600 l_exp_date DATE;
601
602 --MUOM Fulfillment Project
603 l_fulfill_base varchar2(1):='P';
604
605 CURSOR get_rsv_serials (rsv_id NUMBER) IS
606 SELECT reservation_id, serial_number
607 FROM mtl_serial_numbers msn
608 WHERE reservation_id = rsv_id
609 AND current_status = 3
610 AND NOT EXISTS (Select serial_number
611 From mtl_serial_numbers_temp msnt
612 Where msn.serial_number between msnt.fm_serial_number and nvl(msnt.to_serial_number,msnt.fm_serial_number)
613 and (msn.group_mark_id = msnt.transaction_temp_id
614 OR msn.group_mark_id = msnt.group_header_id
615 )
616 ); -- Bug 8618236
617 -- Possible Performance improvement
618 -- Select serials for this item into a table and use that table
619
620
621 BEGIN
622 IF (is_debug ) THEN
623 print_debug('Inside Process_Reservations', 'Inv_Pick_Release_PVT.Process_Reservations');
624 END IF;
625
626 BEGIN
627 -- Bug 4494038: exclude crossdock reservations
628 IF NOT g_item_non_inv_rsv.exists(p_mo_line_rec.inventory_item_id) THEN
629 SELECT sum(primary_reservation_quantity)
630 , sum(secondary_reservation_quantity)
631 INTO g_item_non_inv_rsv(p_mo_line_rec.inventory_item_id)
632 , g_item_sec_non_inv_rsv(p_mo_line_rec.inventory_item_id)
633 FROM mtl_reservations
634 WHERE organization_id = p_mo_line_rec.organization_id
635 AND inventory_item_id = p_mo_line_rec.inventory_item_id
636 AND supply_source_type_id <> INV_Reservation_GLOBAL.g_source_type_inv
637 AND demand_source_line_detail IS NULL;
638 END IF;
639
640 -- Bug 4494038: exclude crossdock reservations
641 IF g_item_non_inv_rsv(p_mo_line_rec.inventory_item_id) > 0 THEN
642 SELECT nvl(sum(primary_reservation_quantity),0)
643 , nvl(sum(secondary_reservation_quantity),0)
644 INTO l_non_inv_reservations_qty
645 , l_non_inv_sec_reservation_qty
646 FROM mtl_reservations
647 WHERE demand_source_line_id = p_demand_info.oe_line_id
648 AND organization_id = p_mo_line_rec.organization_id
649 AND inventory_item_id = p_mo_line_rec.inventory_item_id
650 AND demand_source_type_id = p_demand_source_type
651 AND supply_source_type_id <> INV_Reservation_GLOBAL.g_source_type_inv
652 AND demand_source_line_detail IS NULL;
653 ELSE
654 l_non_inv_reservations_qty := 0;
655 l_non_inv_sec_reservation_qty := 0;
656 END IF;
657
658 EXCEPTION
659 WHEN no_data_found THEN
660 l_non_inv_reservations_qty := 0;
661 l_non_inv_sec_reservation_qty := 0;
662 g_item_non_inv_rsv(p_mo_line_rec.inventory_item_id) := 0;
663 END;
664 IF is_debug THEN
665 print_debug('All primary reservation qty: '||l_non_inv_reservations_qty, 'Inv_Pick_Release_PVT.Process_Reservations');
666 print_debug('All secondary reservation qty: '||l_non_inv_sec_reservation_qty , 'Inv_Pick_Release_PVT.Process_Reservations');
667 END IF;
668
669 l_qry_rsv_rec.organization_id := p_mo_line_rec.organization_id;
670 l_qry_rsv_rec.inventory_item_id := p_mo_line_rec.inventory_item_id;
671 l_qry_rsv_rec.demand_source_type_id := p_demand_source_type;
672 l_qry_rsv_rec.demand_source_header_id := nvl(p_mso_line_id,fnd_api.g_miss_num);
673 l_qry_rsv_rec.demand_source_line_id := p_demand_info.oe_line_id;
674 l_qry_rsv_rec.demand_source_name := nvl(p_demand_source_name,fnd_api.g_miss_char);
675 l_qry_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
676 inv_reservation_pub.query_reservation(
677 p_api_version_number => 1.0
678 , p_init_msg_lst => fnd_api.g_false
679 , x_return_status => l_api_return_status
680 , x_msg_count => x_msg_count
681 , x_msg_data => x_msg_data
682 , p_query_input => l_qry_rsv_rec
683 --, p_cancel_order_mode => inv_reservation_global.g_cancel_order_yes
684 , x_mtl_reservation_tbl => l_demand_reservations
685 , x_mtl_reservation_tbl_count => l_reservation_count
686 , x_error_code => l_api_error_code
687 );
688
689 -- Return an error if the query reservations call failed
690 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
691 IF (is_debug) THEN
692 print_debug('return error from query reservation: '||l_api_return_status,'INV_Pick_Release_PVT.Process_Reservations');
693 END IF;
694 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
695 fnd_msg_pub.ADD;
696 RAISE fnd_api.g_exc_unexpected_error;
697 END IF;
698
699 IF (is_debug) THEN
700 print_debug('Unstaged l_non_inv_reservations_qty '|| l_non_inv_reservations_qty,'Inv_Pick_Release_PVT.Process_Reservations');
701 END IF;
702
703 -- Check if the picking subinventory (if specified on the MO) is
704 -- reservable OR not
705 IF (p_mo_line_rec.from_subinventory_code IS NOT NULL) THEN
706 l_return_value := INV_CACHE.set_fromsub_rec(
707 p_mo_line_rec.organization_id,
708 p_mo_line_rec.from_subinventory_code);
709 If NOT l_return_value Then
710 if is_debug then
711 print_debug('Error setting from sub cache','Inv_Pick_Release_PVT.Process_Reservations');
712 end if;
713 RAISE fnd_api.g_exc_unexpected_error;
714 End If;
715 g_sub_reservable_type := INV_CACHE.fromsub_rec.reservable_type;
716 ELSE
717 g_sub_reservable_type := 1;
718 END IF;
719
720 -- Filter out reservations which do not match attributes of the move
721 -- order line, and copy the reservation records into another table
722 -- in reverse order (so that this table
723 -- will have the reservations in descending order of detail).
724 IF l_reservation_count > 0 THEN
725 l_res_tbl_index := l_demand_reservations.LAST;
726 l_res_ordered_index := 0;
727
728 LOOP
729 l_staged_flag := l_demand_reservations(l_res_tbl_index).staged_flag;
730 IF NVL(l_staged_flag, 'N') = 'N' THEN
731 l_primary_reservation_qty := l_primary_reservation_qty +
732 l_demand_reservations(l_res_tbl_index).primary_reservation_quantity;
733 l_secondary_reservation_qty := l_secondary_reservation_qty +
734 l_demand_reservations(l_res_tbl_index).secondary_reservation_quantity;
735 -- Bug# 12784165 if staged_flag is 'Y', the primary rsv qty should be counted as staged qty
736 -- and take part in later calculation.
737 ELSIF NVL(l_staged_flag, 'N') = 'Y' THEN
738 l_primary_staged_qty := l_primary_staged_qty + l_demand_reservations(l_res_tbl_index).primary_reservation_quantity;
739 END IF;
740
741 IF (is_debug) THEN
742 print_debug('l_primary_reservation_qty'|| l_primary_reservation_qty,
743 'Inv_Pick_Release_PVT.process_reservations');
744
745 -- Bug 6989438
746 print_debug('l_secondary_reservation_qty'|| l_secondary_reservation_qty,
747 'Inv_Pick_Release_PVT.process_reservations');
748
749 print_debug('try to match the reservation exist with the current move order ', 'Inv_Pick_Release_PVT.process_reservations');
750 END IF;
751
752 IF reservation_matches(p_mo_line_rec, l_demand_reservations(l_res_tbl_index)) = 1
753 AND NVL(l_staged_flag, 'N') = 'N' THEN
754 l_res_ordered_index := l_res_ordered_index + 1;
755 l_demand_rsvs_ordered(l_res_ordered_index) :=
756 l_demand_reservations(l_res_tbl_index);
757 IF g_sub_reservable_type = 1 THEN
758 -- Compute the total reserved quantity by summing the quantities
759 -- for the filtered reservations (minus any quantity detailed).
760 l_reserved_quantity := l_reserved_quantity
761 + NVL(l_demand_reservations(l_res_tbl_index).primary_reservation_quantity, 0)
762 - NVL(l_demand_reservations(l_res_tbl_index).detailed_quantity,0);
763 l_sec_reserved_quantity := l_sec_reserved_quantity
764 + NVL(l_demand_reservations(l_res_tbl_index).secondary_reservation_quantity, 0)
765 - NVL(l_demand_reservations(l_res_tbl_index).secondary_detailed_quantity,0);
766 END IF;
767
768 END IF;
769
770 EXIT WHEN l_res_tbl_index = l_demand_reservations.FIRST;
771 l_res_tbl_index := l_demand_reservations.PRIOR(l_res_tbl_index);
772 END LOOP;
773 END IF;
774
775 -- Update the reservation count based on the reservations which matched
776 l_reservation_count := l_demand_rsvs_ordered.COUNT;
777
778 -- Determine whether an organization-wide reservation exists for this
779 -- move order line. Since reservations are now sorted in descending
780 -- order of detail, only the last reservation record in the table could
781 -- possibly be an org-level reservation.
782 IF l_reservation_count > 0 THEN
783 l_res_ordered_index := l_demand_rsvs_ordered.LAST;
784
785 IF l_demand_rsvs_ordered(l_res_ordered_index).revision IS NULL
786 AND l_demand_rsvs_ordered(l_res_ordered_index).lot_number IS NULL
787 AND l_demand_rsvs_ordered(l_res_ordered_index).subinventory_code IS NULL
788 AND l_demand_rsvs_ordered(l_res_ordered_index).locator_id IS NULL THEN
789 l_org_wide_res_id := l_demand_rsvs_ordered(l_res_ordered_index).reservation_id;
790 END IF;
791 END IF;
792
793
794 IF g_sub_reservable_type = 1 THEN
795
796 -- Compute the quantity which will be detailed (converted to
797 -- primary quantity)
798 l_qty_to_detail_unconv := p_mo_line_rec.quantity - NVL(p_mo_line_rec.quantity_detailed, 0);
799 l_sec_quantity_to_detail := p_mo_line_rec.secondary_quantity - NVL(p_mo_line_rec.secondary_quantity_detailed, 0);
800 l_grade_code := p_mo_line_rec.grade_code;
801
802 l_return_value := INV_CACHE.set_item_rec(
803 p_mo_line_rec.organization_id,
804 p_mo_line_rec.inventory_item_id);
805 If NOT l_return_value Then
806 if is_debug then
807 print_debug('Error setting from sub cache',
808 'Inv_Pick_Release_PVT.Process_Reservations');
809 end if;
810 RAISE fnd_api.g_exc_unexpected_error;
811 End If;
812
813 l_primary_uom:= INV_CACHE.item_rec.primary_uom_code;
814 l_secondary_uom:= INV_CACHE.item_rec.secondary_uom_code;
815 l_revision_control_code:= INV_CACHE.item_rec.revision_qty_control_code;
816 l_lot_control_code:= INV_CACHE.item_rec.lot_control_code;
817 l_serial_control_code:= INV_CACHE.item_rec.serial_number_control_code;
818
819 IF(l_lot_control_code = inv_reservation_global.g_lot_control_yes AND INV_CACHE.item_rec.lot_divisible_flag <> 'Y') THEN
820 l_lot_divisible_flag := 'N';
821 ELSE
822 l_lot_divisible_flag := 'Y';
823 END IF;
824
825 IF (l_primary_uom <> p_mo_line_rec.uom_code) THEN
826 l_quantity_to_detail := inv_convert.inv_um_convert(
827 item_id => p_mo_line_rec.inventory_item_id
828 , PRECISION => NULL
829 , from_quantity => l_qty_to_detail_unconv
830 , from_unit => p_mo_line_rec.uom_code
831 , to_unit => l_primary_uom
832 , from_name => NULL
833 , to_name => NULL
834 );
835
836 IF (l_quantity_to_detail = -99999) THEN
837 IF (is_debug) THEN
838 print_debug('Cannot convert uom to primary uom', 'Inv_Pick_release_pvt.process_reservations');
839 END IF;
840 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
841 fnd_message.set_token('UOM', l_primary_uom);
842 fnd_message.set_token('ROUTINE', 'Pick Release process');
843 fnd_msg_pub.ADD;
844 RAISE fnd_api.g_exc_unexpected_error;
845 END IF;
846 ELSE
847 l_quantity_to_detail := l_qty_to_detail_unconv;
848 END IF;
849
850 -- convert revision/lot control indicators into boolean
851 IF l_revision_control_code = 2 THEN
852 l_is_revision_control := TRUE;
853 ELSE
854 l_is_revision_control := FALSE;
855 END IF;
856
857 --
858 IF l_lot_control_code = 2 THEN
859 l_is_lot_control := TRUE;
860 ELSE
861 l_is_lot_control := FALSE;
862 END IF;
863
864 --
865 IF l_serial_control_code = 2 THEN
866 l_is_serial_control := TRUE;
867 ELSE
868 l_is_serial_control := FALSE;
869 END IF;
870
871 IF (is_debug) THEN
872 print_debug('l_reserved_quantity = ' ||l_reserved_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
873 print_debug('l_sec_reserved_quantity = ' ||l_sec_reserved_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
874 print_debug('l_quantity_to_detail = ' ||l_quantity_to_detail, 'Inv_Pick_Release_PVT.Process_Reservations');
875 print_debug('l_sec_quantity_to_detail = ' ||l_sec_quantity_to_detail, 'Inv_Pick_Release_PVT.Process_Reservations');
876 print_debug('l_non_inv_reservations_qty = ' ||l_non_inv_reservations_qty, 'Inv_Pick_Release_PVT.Process_Reservations');
877 print_debug('l_non_inv_sec_reservation_qty = ' ||l_non_inv_sec_reservation_qty, 'Inv_Pick_Release_PVT.Process_Reservations');
878 END IF;
879
880 IF (l_non_inv_reservations_qty > 0 AND l_quantity_to_detail > l_reserved_quantity -- Bug 3440014
881 ) THEN
882 IF (is_debug) THEN
883 print_debug('Adjust l_quantity_to_detail honor supply rsv', 'Inv_Pick_Release_PVT.Process_Reservations');
884 END IF;
885 -- Bug 4171297, if the order line has Wip supply reservation then l_quantity_to_detail
886 -- should be calculated based on order line quantity. Adding the below logic to calculate
887 -- the l_quantity_to_detail.
888 l_return_value := INV_CACHE.set_oola_rec(p_demand_info.oe_line_id);
889 IF NOT l_return_value Then
890 IF (is_debug) THEN
891 print_debug('Error setting cache for order line', 'Inv_Pick_Release_PVT.Process_Reservations');
892 END IF;
893 RAISE fnd_api.g_exc_unexpected_error;
894 END IF;
895
896 l_order_line_qty_unconv := INV_CACHE.oola_rec.ordered_quantity;
897 l_order_line_uom := INV_CACHE.oola_rec.order_quantity_uom;
898
899 IF (is_debug) THEN
900 print_debug('l_order_line_qty_unconv = ' ||l_order_line_qty_unconv, 'Inv_Pick_Release_PVT.Process_Reservations');
901 print_debug('l_order_line_uom = ' ||l_order_line_uom, 'Inv_Pick_Release_PVT.Process_Reservations');
902 END IF;
903
904 IF (l_primary_uom <> l_order_line_uom) THEN
905 l_order_line_qty := inv_convert.inv_um_convert(
906 item_id => p_mo_line_rec.inventory_item_id
907 , PRECISION => NULL
908 , from_quantity => l_order_line_qty_unconv
909 , from_unit => l_order_line_uom
910 , to_unit => l_primary_uom
911 , from_name => NULL
912 , to_name => NULL
913 );
914
915 IF (l_order_line_qty = -99999) THEN
916 IF (is_debug) THEN
917 print_debug('Cannot convert order quantity to primary uom', 'Inv_Pick_release_pvt.process_reservations');
918 END IF;
919 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
920 fnd_message.set_token('UOM', l_primary_uom);
921 fnd_message.set_token('ROUTINE', 'Pick Release process');
922 fnd_msg_pub.ADD;
923 RAISE fnd_api.g_exc_unexpected_error;
924 END IF;
925 ELSE
926 l_order_line_qty := l_order_line_qty_unconv;
927 END IF;
928
929 IF (is_debug) THEN
930 print_debug('l_order_line_qty = ' ||l_order_line_qty, 'Inv_Pick_Release_PVT.Process_Reservations');
931 END IF;
932
933 -- Calculated using the following formula least of move order line quantity and
934 -- order line quantity - all reservations + calculated reserved quantity for line
935 -- all reservations = l_non_inv_reservation_qty + l_primary_reservation_qty + l_primary_staged_qty
936 l_orig_quantity_to_detail := l_quantity_to_detail;
937 -- l_quantity_to_detail := l_quantity_to_detail - (l_non_inv_reservation_qty - l_primary_reservation_qty);
938 l_quantity_to_detail := least(l_quantity_to_detail, l_order_line_qty - (l_non_inv_reservations_qty + l_primary_staged_qty
939 + l_primary_reservation_qty - l_reserved_quantity));
940 --l_order_line_qty - (l_non_inv_reservation_qty - l_reserved_quantity + l_primary_staged_qty));
941
942 IF (is_debug) THEN
943 print_debug('l_quantity_to_detail = ' ||l_quantity_to_detail, 'Inv_Pick_Release_PVT.Process_Reservations');
944 END IF;
945 l_sec_quantity_to_detail := l_sec_quantity_to_detail * (l_quantity_to_detail / l_orig_quantity_to_detail);
946 END IF; /* IF (l_non_inv_reservations_qty > 0 AND l_quantity_to_detail > l_reserved_quantity */ -- Bug 3440014
947
948 -- Expired lots custom hook
949 IF inv_pick_release_pub.g_pick_expired_lots THEN
950 l_exp_date := NULL;
951 ELSE
952 l_exp_date := SYSDATE;
953 END IF;
954
955 --MUOM Fulfillment Project
956 inv_utilities.get_inv_fulfillment_base(
957 p_source_line_id => p_mo_line_rec.txn_source_line_id,
958 p_demand_source_type_id => p_demand_source_type,
959 p_org_id => p_mo_line_rec.organization_id,
960 x_fulfillment_base => l_fulfill_base
961 );
962
963 IF (is_debug) THEN
964 print_debug(' Fulfill Base = '||l_fulfill_base, 'Inv_Pick_Release_PVT.Process_Reservations');
965 END IF;
966
967 -- Handle reserving additional quantity if necessary
968 --MUOM Fulfillment Project
969 IF ((l_reserved_quantity < l_quantity_to_detail and l_fulfill_base<>'S')
970 OR (l_fulfill_base ='S' and l_sec_reserved_quantity<l_sec_quantity_to_detail))THEN
971
972 l_unreserved_quantity := l_quantity_to_detail - l_reserved_quantity;
973 l_sec_unreserved_quantity := l_sec_quantity_to_detail - l_sec_reserved_quantity;
974 -- BUG 16444577
975 IF (l_fulfill_base = 'S') THEN
976 l_unreserved_quantity := inv_convert.inv_um_convert(
977 item_id => p_mo_line_rec.inventory_item_id
978 , PRECISION => NULL
979 , from_quantity => l_sec_unreserved_quantity
980 , from_unit => l_secondary_uom
981 , to_unit => l_primary_uom
982 , from_name => NULL
983 , to_name => NULL
984 );
985 END IF;
986
987
988 IF (is_debug) THEN
989 print_debug('l_unreserved_quantity is '|| l_unreserved_quantity,'Inv_Pick_Release_PVT.Process_Reservations');
990 print_debug('l_sec_unreserved_quantity is '|| l_sec_unreserved_quantity,'Inv_Pick_Release_PVT.Process_Reservations');
991 END IF;
992
993 IF p_mo_line_rec.from_subinventory_code IS NOT NULL AND g_sub_reservable_type = 1 THEN
994 source_from_sub := p_mo_line_rec.from_subinventory_code;
995 l_is_lot_control := FALSE;
996 l_is_revision_control := FALSE;
997
998 IF p_mo_line_rec.from_locator_id IS NOT NULL THEN
999 source_from_loc := p_mo_line_rec.from_locator_id;
1000 END IF;
1001 END IF;
1002
1003 -- Call quantity tree to obtain the quantity available to reserve
1004 -- Bug 1890424 - pass in expiration date of sysdate. Expired lots
1005 -- shouldn't appear as available
1006 -- Added following secondary qty related section for Bug 7377744
1007
1008 inv_quantity_tree_pub.query_quantities(
1009 p_api_version_number => 1.0
1010 , p_init_msg_lst => fnd_api.g_false
1011 , x_return_status => l_api_return_status
1012 , x_msg_count => x_msg_count
1013 , x_msg_data => x_msg_data
1014 , p_organization_id => p_mo_line_rec.organization_id
1015 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1016 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
1017 , p_is_revision_control => l_is_revision_control
1018 , p_is_lot_control => l_is_lot_control
1019 , p_is_serial_control => l_is_serial_control
1020 , p_demand_source_type_id => p_demand_source_type
1021 , p_demand_source_header_id => p_mso_line_id
1022 , p_demand_source_line_id => p_demand_info.oe_line_id
1023 , p_demand_source_name => p_demand_source_name
1024 , p_revision => NULL
1025 , p_lot_number => NULL
1026 , p_lot_expiration_date => l_exp_date
1027 , p_subinventory_code => source_from_sub
1028 , p_locator_id => source_from_loc
1029 , x_qoh => l_qty_on_hand
1030 , x_rqoh => l_qty_res_on_hand
1031 , x_qr => l_qty_res
1032 , x_qs => l_qty_sug
1033 , x_att => l_qty_att
1034 , x_atr => l_qty_available_to_reserve
1035
1036 , p_grade_code => l_grade_code
1037 , x_sqoh => l_sec_qty_on_hand
1038 , x_srqoh => l_sec_qty_res_on_hand
1039 , x_sqr => l_sec_qty_res
1040 , x_sqs => l_sec_qty_sug
1041 , x_satt => l_sec_qty_att
1042 , x_satr => l_sec_qty_available_to_reserve
1043 );
1044 IF (is_debug) THEN
1045 print_debug('Reservable qty from qtytree '|| l_qty_available_to_reserve, 'Inv_Pick_Release_PVT.Process_Reservations');
1046 print_debug('Reservable qty2 from qtytree '|| l_sec_qty_available_to_reserve, 'Inv_Pick_Release_PVT.Process_Reservations');
1047 END IF;
1048
1049 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1050 IF (is_debug) THEN
1051 print_debug('Error from query quantity tree', 'Inv_Pick_Release_PVT.Process_Reservations');
1052 END IF;
1053 fnd_message.set_name('INV', 'INV_QRY_QTY_FAILED');
1054 fnd_msg_pub.ADD;
1055 RAISE fnd_api.g_exc_unexpected_error;
1056 END IF;
1057
1058 IF (is_debug) THEN
1059 print_debug('l_qty_available_to_reserve = '||l_qty_available_to_reserve||', l_qty_att = '||l_qty_att||', p_demand_source_type = '||p_demand_source_type, 'Inv_Pick_Release_PVT.Process_Reservations');
1060 END IF;
1061
1062 --Bug 8560030, added below code in order to allow picking of non-rsvable lots for Internal Orders.
1063 IF (l_qty_available_to_reserve <= 0 AND l_qty_att > 0 AND p_demand_source_type = 8 AND l_is_lot_control) THEN
1064 IF g_prf_pick_nonrsv_lots IS NULL THEN
1065 g_prf_pick_nonrsv_lots := NVL(FND_PROFILE.VALUE('INV_PICK_NONRSV_LOTS'),2);
1066 IF (is_debug) THEN
1067 print_debug('g_prf_pick_nonrsv_lots = '||g_prf_pick_nonrsv_lots, 'Inv_Pick_Release_PVT.Process_Reservations');
1068 END IF;
1069 END IF;
1070 IF g_prf_pick_nonrsv_lots = 1 THEN
1071 g_pick_nonrsv_lots := 1;
1072 END IF;
1073 END IF;
1074
1075 l_quantity_to_reserve := l_unreserved_quantity;
1076 l_sec_quantity_to_reserve := l_sec_unreserved_quantity;
1077
1078 --MUOM Fulfillment Project
1079 IF (is_debug) THEN
1080 print_debug('l_quantity_to_reserve = '||l_quantity_to_reserve||', l_unreserved_quantity = '||l_unreserved_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
1081 print_debug('l_sec_quantity_to_reserve = '||l_sec_quantity_to_reserve||', l_sec_unreserved_quantity = '||l_sec_unreserved_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
1082 print_debug('l_qty_available_to_reserve = '||l_qty_available_to_reserve||', l_sec_qty_available_to_reserve = '||l_sec_qty_available_to_reserve||' , l_fulfill_base ='||l_fulfill_base, 'Inv_Pick_Release_PVT.Process_Reservations');
1083 END IF;
1084
1085 IF ((l_qty_available_to_reserve < l_unreserved_quantity and l_fulfill_base<>'S') or
1086 ( l_fulfill_base='S' and l_sec_qty_available_to_reserve<=l_sec_unreserved_quantity))
1087 THEN
1088 l_quantity_to_reserve := l_qty_available_to_reserve;
1089 l_sec_quantity_to_reserve := l_sec_qty_available_to_reserve; -- Bug 7377744
1090 -- Backorder cache. If sufficient quantity is not available then add item to backorder cache
1091 IF source_from_sub IS NULL THEN
1092 g_backorder_cache(p_mo_line_rec.inventory_item_id) := p_mo_line_rec.organization_id;
1093 END IF;
1094 END IF;
1095 --
1096 IF (l_fulfill_base='S' AND l_qty_available_to_reserve < l_unreserved_quantity) THEN
1097 l_quantity_to_reserve := l_qty_available_to_reserve;
1098 END IF;
1099 --
1100 ----MUOM Fulfillment Project
1101 IF (( l_quantity_to_reserve > 0 and l_fulfill_base<>'S') or ( l_fulfill_base='S' and l_sec_quantity_to_reserve>0)) THEN
1102 x_rsv_qty_available := l_quantity_to_reserve;
1103 x_rsv_qty2_available := l_sec_quantity_to_reserve; --Bug #7377744
1104
1105 -- Since there is unreserved quantity which can be reserved, create
1106 -- or update an org-level reservation for the remaining quantity.
1107 IF l_org_wide_res_id <> -1 THEN
1108 /*
1109 -- Update the existing reservation
1110 l_res_ordered_index := l_demand_rsvs_ordered.LAST;
1111 l_qry_rsv_rec := l_demand_rsvs_ordered(l_res_ordered_index);
1112 l_update_rsv_rec := l_qry_rsv_rec;
1113 l_update_rsv_rec.primary_reservation_quantity := l_update_rsv_rec.primary_reservation_quantity + l_quantity_to_reserve;
1114 l_update_rsv_rec.reservation_quantity := NULL; -- Force update of reserved qty
1115 IF (is_debug) THEN
1116 print_debug('Org wide reservation exist', 'Inv_Pick_Release_PVT.Process_Reservations');
1117 print_debug('update reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
1118 END IF;
1119 inv_reservation_pub.update_reservation(
1120 p_api_version_number => 1.0
1121 , p_init_msg_lst => fnd_api.g_false
1122 , x_return_status => l_api_return_status
1123 , x_msg_count => x_msg_count
1124 , x_msg_data => x_msg_data
1125 , p_original_rsv_rec => l_qry_rsv_rec
1126 , p_to_rsv_rec => l_update_rsv_rec
1127 , p_original_serial_number => l_dummy_sn
1128 , p_to_serial_number => l_dummy_sn
1129 , p_validation_flag => fnd_api.g_true
1130 );
1131
1132 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1133 IF (is_debug) THEN
1134 print_debug('error in update reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
1135 END IF;
1136 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
1137 fnd_msg_pub.ADD;
1138 RAISE fnd_api.g_exc_unexpected_error;
1139 ELSE
1140 --Requery the reservation record and update it in the local table.
1141 IF (is_debug) THEN
1142 print_debug('query that reservation again',
1143 'Inv_Pick_release_pvt.Process_Reservations');
1144 END IF;
1145 l_qry_rsv_rec_by_id.reservation_id := l_update_rsv_rec.reservation_id;
1146 inv_reservation_pub.query_reservation(
1147 p_api_version_number => 1.0
1148 , p_init_msg_lst => fnd_api.g_true
1149 , x_return_status => l_api_return_status
1150 , x_msg_count => x_msg_count
1151 , x_msg_data => x_msg_data
1152 , p_query_input => l_qry_rsv_rec_by_id
1153 , x_mtl_reservation_tbl => l_demand_reservations
1154 , x_mtl_reservation_tbl_count => l_reservation_count_by_id
1155 , x_error_code => l_api_error_code
1156 );
1157
1158 -- Return an error if the query reservations call failed
1159 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1160 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1161 fnd_msg_pub.ADD;
1162 RAISE fnd_api.g_exc_unexpected_error;
1163 END IF;
1164
1165 l_res_tbl_index := l_demand_reservations.FIRST;
1166 l_demand_rsvs_ordered(l_res_ordered_index) := l_demand_reservations(l_res_tbl_index);
1167 END IF;
1168 */
1169 --update quantity tree
1170 IF is_debug THEN
1171 print_debug('updating quantity tree','Inv_Pick_Release_PVT.Process_Reservations');
1172 END IF;
1173
1174 -- Added following secondary qty related section for Bug 7377744
1175 inv_quantity_tree_pub.update_quantities(
1176 p_api_version_number => 1.0
1177 , p_init_msg_lst => fnd_api.g_false
1178 , x_return_status => l_api_return_status
1179 , x_msg_count => x_msg_count
1180 , x_msg_data => x_msg_data
1181 , p_organization_id => p_mo_line_rec.organization_id
1182 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1183 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
1184 , p_is_revision_control => l_is_revision_control
1185 , p_is_lot_control => l_is_lot_control
1186 , p_is_serial_control => l_is_serial_control
1187 , p_demand_source_type_id => p_demand_source_type
1188 , p_demand_source_header_id => p_mso_line_id
1189 , p_demand_source_line_id => p_demand_info.oe_line_id
1190 , p_demand_source_name => p_demand_source_name
1191 , p_revision => NULL
1192 , p_lot_number => NULL
1193 , p_lot_expiration_date => l_exp_date
1194 , p_subinventory_code => NULL
1195 , p_locator_id => NULL
1196 , p_primary_quantity => l_quantity_to_reserve
1197 , p_secondary_quantity => l_sec_quantity_to_reserve -- Bug 7377744
1198 , p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
1199 , x_qoh => l_qty_on_hand
1200 , x_rqoh => l_qty_res_on_hand
1201 , x_qr => l_qty_res
1202 , x_qs => l_qty_sug
1203 , x_att => l_qty_att
1204 , x_atr => l_qty_available_to_reserve
1205
1206
1207 , p_grade_code => l_grade_code
1208 , x_sqoh => l_sec_qty_on_hand
1209 , x_srqoh => l_sec_qty_res_on_hand
1210 , x_sqr => l_sec_qty_res
1211 , x_sqs => l_sec_qty_sug
1212 , x_satt => l_sec_qty_att
1213 , x_satr => l_sec_qty_available_to_reserve
1214 );
1215 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1216 IF (is_debug) THEN
1217 print_debug('Error from query quantity tree', 'Inv_Pick_Release_PVT.Process_Reservations');
1218 END IF;
1219 fnd_message.set_name('INV', 'INV_QRY_QTY_FAILED');
1220 fnd_msg_pub.ADD;
1221 RAISE fnd_api.g_exc_unexpected_error;
1222 END IF;
1223
1224 --find new reservation quantities
1225 l_index := l_demand_rsvs_ordered.LAST;
1226 l_new_prim_rsv_quantity := l_demand_rsvs_ordered(l_index).primary_reservation_quantity
1227 + l_quantity_to_reserve;
1228
1229 l_new_sec_rsv_quantity := l_demand_rsvs_ordered(l_index).secondary_reservation_quantity
1230 + l_sec_quantity_to_reserve;
1231
1232 --handle conversion to reservation UOM
1233 IF l_demand_rsvs_ordered(l_index).reservation_uom_code IS NULL THEN
1234 --when missing rsv UOM, assume primary UOM
1235 l_new_rsv_quantity := l_new_prim_rsv_quantity;
1236 ELSIF l_demand_rsvs_ordered(l_index).reservation_uom_code = l_primary_uom THEN
1237 --reservation UOM = primary UOM
1238 l_new_rsv_quantity := l_new_prim_rsv_quantity;
1239 --MUOM Fulfillment Project
1240 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_index).reservation_uom_code = l_secondary_uom THEN
1241 l_new_rsv_quantity := l_new_sec_rsv_quantity;
1242 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_index).reservation_uom_code <> l_secondary_uom AND l_demand_rsvs_ordered(l_index).reservation_uom_code <> l_primary_uom THEN
1243
1244 l_new_rsv_quantity := inv_convert.inv_um_convert(
1245 item_id => p_mo_line_rec.inventory_item_id
1246 , PRECISION => NULL
1247 , from_quantity => l_new_sec_rsv_quantity
1248 , from_unit => l_secondary_uom
1249 , to_unit => l_demand_rsvs_ordered(l_index).reservation_uom_code
1250 , from_name => NULL
1251 , to_name => NULL
1252 );
1253
1254 IF (l_new_rsv_quantity = -99999) THEN
1255 IF (is_debug) THEN
1256 print_debug('Cannot convert Seconday uom to rsv uom', 'Inv_Pick_release_pvt.process_reservations');
1257 END IF;
1258 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1259 fnd_message.set_token('UOM', l_secondary_uom);
1260 fnd_message.set_token('ROUTINE', 'Pick Release process');
1261 fnd_msg_pub.ADD;
1262 RAISE fnd_api.g_exc_unexpected_error;
1263 END IF;
1264 ELSE
1265 l_new_rsv_quantity := inv_convert.inv_um_convert(
1266 item_id => p_mo_line_rec.inventory_item_id
1267 , PRECISION => NULL
1268 , from_quantity => l_new_prim_rsv_quantity
1269 , from_unit => l_primary_uom
1270 , to_unit => l_demand_rsvs_ordered(l_index).reservation_uom_code
1271 , from_name => NULL
1272 , to_name => NULL
1273 );
1274
1275 IF (l_new_rsv_quantity = -99999) THEN
1276 IF (is_debug) THEN
1277 print_debug('Cannot convert primary uom to rsv uom', 'Inv_Pick_release_pvt.process_reservations');
1278 END IF;
1279 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
1280 fnd_message.set_token('UOM', l_primary_uom);
1281 fnd_message.set_token('ROUTINE', 'Pick Release process');
1282 fnd_msg_pub.ADD;
1283 RAISE fnd_api.g_exc_unexpected_error;
1284 END IF;
1285 END IF;
1286 IF (is_debug) THEN
1287 print_debug('0 New prim rsv qty: ' || l_new_prim_rsv_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
1288 print_debug('New rsv qty: ' || l_new_rsv_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
1289 print_debug('New sec rsv qty: ' || l_new_sec_rsv_quantity, 'Inv_Pick_Release_PVT.Process_Reservations');
1290 END IF;
1291
1292 UPDATE mtl_reservations
1293 SET primary_reservation_quantity = l_new_prim_rsv_quantity
1294 ,reservation_quantity = l_new_rsv_quantity
1295 ,secondary_reservation_quantity = l_new_sec_rsv_quantity
1296 WHERE reservation_id = l_org_wide_res_id;
1297
1298 inv_rsv_synch.for_update(
1299 p_reservation_id => l_org_wide_res_id
1300 , x_return_status => l_api_return_status
1301 , x_msg_count => x_msg_count
1302 , x_msg_data => x_msg_data);
1303
1304 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1305 IF (is_debug) THEN
1306 print_debug('Error from inv_rsv_synch.for_update', 'Inv_Pick_Release_PVT.Process_Reservations');
1307 END IF;
1308 RAISE fnd_api.g_exc_error;
1309 END IF;
1310 --
1311 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1312 IF (is_debug) THEN
1313 print_debug('Unexp. error from inv_rsv_synch.for_update', 'Inv_Pick_Release_PVT.Process_Reservations');
1314 END IF;
1315 RAISE fnd_api.g_exc_unexpected_error;
1316 END IF;
1317
1318 l_demand_rsvs_ordered(l_index).primary_reservation_quantity := l_new_prim_rsv_quantity;
1319 l_demand_rsvs_ordered(l_index).secondary_reservation_quantity := l_new_sec_rsv_quantity;
1320 l_demand_rsvs_ordered(l_index).reservation_quantity := l_new_rsv_quantity;
1321
1322 ELSIF ( l_lot_divisible_flag = 'Y') THEN
1323 -- If no org-wide reservation existed before, created one now.
1324 IF (is_debug) THEN
1325 print_debug('no org-wide reservation exist, need to create one',
1326 'Inv_Pick_Release_Pvt.Process_Reservations');
1327 END IF;
1328 --Bug 12655248 The secondary uom and secondary quantity should be null when the tracking quantity at item level is not Primary and secondary
1329 IF(INV_CACHE.item_rec.tracking_quantity_ind<>'PS') THEN
1330 l_secondary_uom:=NULL;
1331 l_sec_quantity_to_reserve:=NULL;
1332 END IF;
1333
1334 l_update_rsv_rec.reservation_id := NULL; -- cannot know
1335 l_update_rsv_rec.requirement_date := SYSDATE;
1336 l_update_rsv_rec.organization_id := p_mo_line_rec.organization_id;
1337 l_update_rsv_rec.inventory_item_id :=p_mo_line_rec.inventory_item_id;
1338 l_update_rsv_rec.demand_source_type_id := p_demand_source_type;
1339 l_update_rsv_rec.demand_source_name := p_demand_source_name;
1340 --INV_Reservation_Global.g_source_type_oe; -- order entry
1341 l_update_rsv_rec.demand_source_header_id := p_mso_line_id;
1342 l_update_rsv_rec.demand_source_line_id := p_demand_info.oe_line_id;
1343 l_update_rsv_rec.demand_source_delivery := NULL;
1344 l_update_rsv_rec.primary_uom_code := l_primary_uom;
1345 l_update_rsv_rec.secondary_uom_code := l_secondary_uom;
1346 l_update_rsv_rec.primary_uom_id := NULL;
1347 l_update_rsv_rec.secondary_uom_id := NULL;
1348 l_update_rsv_rec.reservation_uom_code := NULL;
1349 l_update_rsv_rec.reservation_uom_id := NULL;
1350 l_update_rsv_rec.reservation_quantity := NULL;
1351 l_update_rsv_rec.primary_reservation_quantity := l_quantity_to_reserve;
1352 l_update_rsv_rec.secondary_reservation_quantity := l_sec_quantity_to_reserve;
1353 --l_update_rsv_rec.grade_code := l_grade_code;
1354 l_update_rsv_rec.autodetail_group_id := NULL;
1355 l_update_rsv_rec.external_source_code := NULL;
1356 l_update_rsv_rec.external_source_line_id := NULL;
1357 l_update_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
1358 l_update_rsv_rec.supply_source_header_id := NULL;
1359 l_update_rsv_rec.supply_source_line_id := NULL;
1360 l_update_rsv_rec.supply_source_name := NULL;
1361 l_update_rsv_rec.supply_source_line_detail := NULL;
1362 l_update_rsv_rec.revision := NULL;
1363 l_update_rsv_rec.subinventory_code := NULL;
1364 l_update_rsv_rec.subinventory_id := NULL;
1365 l_update_rsv_rec.locator_id := NULL;
1366 l_update_rsv_rec.lot_number := NULL;
1367 l_update_rsv_rec.lot_number_id := NULL;
1368 l_update_rsv_rec.pick_slip_number := NULL;
1369 l_update_rsv_rec.lpn_id := NULL;
1370 l_update_rsv_rec.attribute_category := NULL;
1371 l_update_rsv_rec.attribute1 := NULL;
1372 l_update_rsv_rec.attribute2 := NULL;
1373 l_update_rsv_rec.attribute3 := NULL;
1374 l_update_rsv_rec.attribute4 := NULL;
1375 l_update_rsv_rec.attribute5 := NULL;
1376 l_update_rsv_rec.attribute6 := NULL;
1377 l_update_rsv_rec.attribute7 := NULL;
1378 l_update_rsv_rec.attribute8 := NULL;
1379 l_update_rsv_rec.attribute9 := NULL;
1380 l_update_rsv_rec.attribute10 := NULL;
1381 l_update_rsv_rec.attribute11 := NULL;
1382 l_update_rsv_rec.attribute12 := NULL;
1383 l_update_rsv_rec.attribute13 := NULL;
1384 l_update_rsv_rec.attribute14 := NULL;
1385 l_update_rsv_rec.attribute15 := NULL;
1386 l_update_rsv_rec.ship_ready_flag := 2;
1387 -- l_update_rsv_rec.n_column1 := NULL;
1388 l_update_rsv_rec.detailed_quantity := 0;
1389
1390 --ADM bug 9959125, for OPM batch reservations, passing reservation qty, so that re-conversion doesn't happen.
1391 IF p_demand_source_type = 5 THEN
1392 l_update_rsv_rec.reservation_uom_code := p_mo_line_rec.uom_code;
1393 IF l_quantity_to_detail = l_quantity_to_reserve THEN
1394 IF l_update_rsv_rec.reservation_uom_code = l_primary_uom THEN
1395 l_update_rsv_rec.reservation_quantity := l_quantity_to_reserve;
1396 ELSE
1397 l_update_rsv_rec.reservation_quantity := l_qty_to_detail_unconv;
1398 END IF;
1399 ELSE
1400 IF l_update_rsv_rec.reservation_uom_code = l_primary_uom THEN
1401 l_update_rsv_rec.reservation_quantity := l_quantity_to_reserve;
1402 ELSIF l_update_rsv_rec.reservation_uom_code = l_secondary_uom THEN
1403 l_update_rsv_rec.reservation_quantity := l_sec_quantity_to_reserve;
1404 ELSE
1405 l_update_rsv_rec.reservation_quantity := inv_convert.inv_um_convert(item_id=> l_update_rsv_rec.inventory_item_id
1406 , lot_number => l_update_rsv_rec.lot_number
1407 , organization_id => l_update_rsv_rec.organization_id
1408 , PRECISION => NULL -- use default precision
1409 , from_quantity => l_quantity_to_reserve
1410 , from_unit => l_primary_uom
1411 , to_unit => l_update_rsv_rec.reservation_uom_code
1412 , from_name => NULL -- from uom name
1413 , to_name => NULL -- to uom name
1414 );
1415
1416 IF l_update_rsv_rec.reservation_quantity = -99999 THEN
1417 -- conversion failed
1418 fnd_message.set_name('INV', 'CAN-NOT-CONVERT-TO-PRIMARY-UOM');
1419 fnd_msg_pub.ADD;
1420 RAISE fnd_api.g_exc_error;
1421 END IF;
1422 END IF;
1423 END IF;
1424 END IF;
1425
1426
1427 IF (is_debug) THEN
1428 print_debug('create new reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
1429 END IF;
1430 inv_reservation_pub.create_reservation(
1431 p_api_version_number => 1.0
1432 , p_init_msg_lst => fnd_api.g_false
1433 , x_return_status => l_api_return_status
1434 , x_msg_count => x_msg_count
1435 , x_msg_data => x_msg_data
1436 , p_rsv_rec => l_update_rsv_rec
1437 , p_serial_number => l_dummy_sn
1438 , x_serial_number => l_dummy_sn
1439 , p_partial_reservation_flag => fnd_api.g_true
1440 , p_force_reservation_flag => fnd_api.g_false
1441 , p_validation_flag => 'Q'
1442 , x_quantity_reserved => l_qty_succ_reserved
1443 , x_reservation_id => l_org_wide_res_id
1444 , p_over_reservation_flag => 2 -- Bug 5365200 Passing p_over_reservation_flag to allow reservation of demand for overpicking case
1445 );
1446
1447 -- Return an error if the create reservation call failed
1448 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1449 IF (is_debug) THEN
1450 print_debug('error in create reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
1451 END IF;
1452 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1453 fnd_msg_pub.ADD;
1454 RAISE fnd_api.g_exc_unexpected_error;
1455 END IF;
1456
1457 -- If partial picking is not allowed and the full quantity could no
1458 -- be reserved, return an error.
1459 IF p_allow_partial_pick = fnd_api.g_false
1460 AND l_qty_succ_reserved < l_quantity_to_reserve THEN
1461 IF (is_debug) THEN
1462 print_debug('p_allow_partial_pick is false and could not reserve the quantity requested'
1463 , 'Inv-Pick_Release_PVT.Process_Reservations');
1464 END IF;
1465 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
1466 fnd_msg_pub.ADD;
1467 RAISE fnd_api.g_exc_unexpected_error;
1468 END IF;
1469
1470 -- Query up the reservation which was just created and add it to the
1471 -- filtered table.
1472 l_qry_rsv_rec_by_id.reservation_id := l_org_wide_res_id;
1473 inv_reservation_pub.query_reservation(
1474 p_api_version_number => 1.0
1475 , p_init_msg_lst => fnd_api.g_true
1476 , x_return_status => l_api_return_status
1477 , x_msg_count => x_msg_count
1478 , x_msg_data => x_msg_data
1479 , p_query_input => l_qry_rsv_rec_by_id
1480 , x_mtl_reservation_tbl => l_demand_reservations
1481 , x_mtl_reservation_tbl_count => l_reservation_count_by_id
1482 , x_error_code => l_api_error_code
1483 );
1484
1485 -- Return an error if the query reservations call failed
1486 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1487 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1488 fnd_msg_pub.ADD;
1489 RAISE fnd_api.g_exc_unexpected_error;
1490 END IF;
1491
1492 -- Add the retrieved reservation to the filtered table
1493 l_res_tbl_index := l_demand_reservations.FIRST;
1494 l_res_ordered_index := NVL(l_demand_rsvs_ordered.LAST, 0) + 1;
1495 l_demand_rsvs_ordered(l_res_ordered_index) := l_demand_reservations(l_res_tbl_index);
1496 -- Update with the actual reservation quantity
1497 x_rsv_qty_available := l_demand_reservations(l_res_tbl_index).primary_reservation_quantity;
1498 IF (is_debug) THEN
1499 print_debug('Reservation created for: '|| x_rsv_qty_available, 'Inv-Pick_Release_PVT.Process_Reservations');
1500 END IF; -- debug on
1501 END IF; -- An org-wide reservation existed
1502 END IF; -- The quantity to reserve was > 0
1503 END IF; --l_reserved_quantity < l_quantity_to_detail
1504 END IF; -- If the sub is reservable
1505
1506 --Bug 2972143 Changes, filtering out already allocated reservations from l_demand_rsvs_ordered table.
1507 --before passing the table to create suggestions API.
1508
1509 l_reservation_count := NVL(l_demand_rsvs_ordered.count,0);
1510
1511 IF (is_debug) then
1512 print_debug('l_reservation_count = '|| l_reservation_count, 'Inv-Pick_Release_PVT.Process_Reservations');
1513 END IF;
1514
1515 IF (l_reservation_count > 0) THEN
1516 l_res_tbl_index := l_demand_rsvs_ordered.FIRST;
1517 l_res_ordered_index := l_demand_rsvs_ordered.FIRST;
1518 IF (is_debug) then
1519 print_debug('Checking for Serial Reservations ', 'Inv-Pick_Release_PVT.Process_Reservations');
1520 END IF;
1521 LOOP
1522 IF ((nvl(l_demand_rsvs_ordered(l_res_tbl_index).primary_reservation_quantity,0)
1523 - nvl(l_demand_rsvs_ordered(l_res_tbl_index).detailed_quantity,0) > 0) AND l_fulfill_base <> 'S' ) OR
1524 ((nvl(l_demand_rsvs_ordered(l_res_tbl_index).secondary_reservation_quantity,0)
1525 - nvl(l_demand_rsvs_ordered(l_res_tbl_index).secondary_detailed_quantity,0) > 0) AND l_fulfill_base = 'S' ) THEN
1526
1527 -- Bug 5535030: fetch from get_rsv_serials only for serial controlled items
1528 IF (INV_CACHE.set_item_rec( p_mo_line_rec.organization_id, p_mo_line_rec.inventory_item_id)) THEN
1529 IF INV_CACHE.item_rec.serial_number_control_code NOT IN (1,6) THEN
1530 l_rsv_serials.DELETE;
1531 OPEN get_rsv_serials(l_demand_rsvs_ordered(l_res_tbl_index).reservation_id);
1532 FETCH get_rsv_serials bulk collect INTO l_rsv_serials;
1533 CLOSE get_rsv_serials;
1534 END IF;
1535 END IF;
1536
1537 IF (is_debug) then
1538 print_debug('Serial Count = '|| l_rsv_serials.count, 'Inv-Pick_Release_PVT.Process_Reservations');
1539 print_debug('Subinventory : '|| l_demand_rsvs_ordered(l_res_tbl_index).subinventory_code, 'Inv-Pick_Release_PVT.Process_Reservations');
1540 END IF;
1541 -- {{ Test Case # UTK-REALLOC-3.2.1:40 }}
1542 -- Description: Single non-detailed reservation should allocate
1543 -- {{ Test Case # UTK-REALLOC-3.2.1:41 }}
1544 -- Description: Multiple non-detailed reservations should allocate
1545 -- {{ Test Case # UTK-REALLOC-3.2.1:42 }}
1546 -- Description: Reservation that is already partially allocated should not re-allocate allocated serials
1547 IF (nvl(l_rsv_serials.COUNT,0) = 0) OR (l_demand_rsvs_ordered(l_res_tbl_index).subinventory_code IS NULL) THEN
1548 IF (is_debug) then
1549 print_debug('No Serials for this reservation', 'Inv-Pick_Release_PVT.Process_Reservations');
1550 END IF;
1551 x_demand_rsvs_ordered(l_res_ordered_index) := l_demand_rsvs_ordered(l_res_tbl_index);
1552 l_res_ordered_index := l_res_ordered_index + 1;
1553 ELSE
1554 l_pri_sec_conv := l_demand_rsvs_ordered(l_res_tbl_index).secondary_reservation_quantity / l_demand_rsvs_ordered(l_res_tbl_index).primary_reservation_quantity;
1555 -- Could use maximum of last serial and reservation quantity
1556 FOR i in nvl(l_rsv_serials.FIRST,0)..nvl(l_rsv_serials.LAST,0) LOOP
1557 x_demand_rsvs_ordered(l_res_ordered_index) := l_demand_rsvs_ordered(l_res_tbl_index);
1558 x_demand_rsvs_ordered(l_res_ordered_index).serial_number := l_rsv_serials(i).serial_number;
1559 x_demand_rsvs_ordered(l_res_ordered_index).primary_reservation_quantity := 1;
1560 x_demand_rsvs_ordered(l_res_ordered_index).secondary_reservation_quantity := l_pri_sec_conv;
1561 x_demand_rsvs_ordered(l_res_ordered_index).detailed_quantity := 0;
1562 l_demand_rsvs_ordered(l_res_tbl_index).primary_reservation_quantity := l_demand_rsvs_ordered(l_res_tbl_index).primary_reservation_quantity - 1;
1563 l_demand_rsvs_ordered(l_res_tbl_index).secondary_reservation_quantity := l_demand_rsvs_ordered(l_res_tbl_index).secondary_reservation_quantity - l_pri_sec_conv;
1564 l_res_ordered_index := l_res_ordered_index + 1;
1565 IF (is_debug) then
1566 print_debug('Serial Number - ' || l_rsv_serials(i).serial_number, 'Inv-Pick_Release_PVT.Process_Reservations');
1567 print_debug('l_res_ordered_index ' || l_res_ordered_index, 'Inv-Pick_Release_PVT.Process_Reservations');
1568 END IF;
1569 END LOOP;
1570 -- {{ Test Case # UTK-REALLOC-3.2.1:43 }}
1571 -- Description: Reservation for qty>1 with 1 serial number should allocate serial and do remainder
1572 -- {{ Test Case # UTK-REALLOC-3.2.1:44 }}
1573 -- Description: Reservation for qty>1 with multile serials < qty should allocate serials and do remainder
1574 IF l_demand_rsvs_ordered(l_res_tbl_index).primary_reservation_quantity > 0 THEN
1575 x_demand_rsvs_ordered(l_res_ordered_index) := l_demand_rsvs_ordered(l_res_tbl_index);
1576 l_res_ordered_index := l_res_ordered_index + 1;
1577 END IF;
1578 END IF;
1579 END IF;
1580
1581 EXIT WHEN l_res_tbl_index = l_demand_rsvs_ordered.LAST;
1582 l_res_tbl_index := l_demand_rsvs_ordered.NEXT(l_res_tbl_index);
1583 End LOOP;
1584 END IF;
1585 --x_demand_rsvs_ordered := l_new_demand_rsvs_ordered;
1586
1587 --x_demand_rsvs_ordered := l_demand_rsvs_ordered;
1588 IF (is_debug) THEN
1589 print_debug('Return from process_Reservations','Inv-Pick_Release_PVT.Process_Reservations');
1590 print_debug('Final Reservation Count : ' || x_demand_rsvs_ordered.count, 'Inv-Pick_Release_PVT.Process_Reservations');
1591 IF x_demand_rsvs_ordered.COUNT > 0 THEN
1592 FOR i in x_demand_rsvs_ordered.FIRST..x_demand_rsvs_ordered.LAST LOOP
1593 print_debug('Subinevntory code - ' || x_demand_rsvs_ordered(i).subinventory_code, 'Inv-Pick_Release_PVT.Process_Reservations');
1594 print_debug('Locator - ' || x_demand_rsvs_ordered(i).locator_id, 'Inv-Pick_Release_PVT.Process_Reservations');
1595 print_debug('Lot - ' || x_demand_rsvs_ordered(i).lot_number, 'Inv-Pick_Release_PVT.Process_Reservations');
1596 print_debug('Serial Number - ' || x_demand_rsvs_ordered(i).serial_number, 'Inv-Pick_Release_PVT.Process_Reservations');
1597 END LOOP;
1598 END IF;
1599 END IF;
1600 EXCEPTION
1601 WHEN fnd_api.g_exc_error THEN
1602 x_return_status := fnd_api.g_ret_sts_error;
1603 -- Get message count and data
1604 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1605 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1606 WHEN fnd_api.g_exc_unexpected_error THEN
1607 x_return_status := fnd_api.g_ret_sts_unexp_error;
1608 -- Get message count and data
1609 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1610 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1611 WHEN OTHERS THEN
1612 x_return_status := fnd_api.g_ret_sts_unexp_error;
1613
1614 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1615 fnd_msg_pub.add_exc_msg(g_pkg_name, 'INV_PICK_RELEASE_PVT');
1616 END IF;
1617
1618 -- Get message count and data
1619 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1620 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1621 END;
1622
1623 PROCEDURE process_unreservable_items(
1624 x_return_status OUT NOCOPY VARCHAR2
1625 , x_msg_count OUT NOCOPY NUMBER
1626 , x_msg_data OUT NOCOPY VARCHAR2
1627 , x_pick_slip_number OUT NOCOPY NUMBER
1628 , x_ready_to_print OUT NOCOPY VARCHAR2
1629 , x_call_mode OUT NOCOPY VARCHAR2
1630 , p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
1631 , p_demand_info IN wsh_inv_delivery_details_v%ROWTYPE
1632 , p_grouping_rule_id IN NUMBER
1633 , p_pick_slip_mode IN VARCHAR2
1634 , p_print_mode IN VARCHAR2
1635 ) IS
1636 l_shipping_attr wsh_interface.changedattributetabtype;
1637 l_report_set_id NUMBER;
1638 l_request_number VARCHAR2(80);
1639 l_return_status VARCHAR2(1);
1640 l_pick_slip_number NUMBER;
1641 l_ready_to_print VARCHAR2(1);
1642 l_api_error_msg VARCHAR2(2000);
1643 l_default_subinventory VARCHAR2(10) := fnd_api.g_miss_char;
1644 l_default_locator_id NUMBER := fnd_api.g_miss_num;
1645 l_call_mode VARCHAR2(1);
1646
1647 --8430412 add for getting revision
1648 l_revision_control NUMBER;
1649 l_rule_id NUMBER;
1650 l_revision_rule NUMBER;
1651 l_order_by VARCHAR2(1000);
1652 l_base_stmt VARCHAR2(2000);
1653 l_stmt VARCHAR2(2000);
1654 l_revision VARCHAR2(3);
1655 l_txn_date DATE;
1656 --8430412
1657
1658 BEGIN
1659 IF (is_debug) THEN
1660 print_debug('Inside Process_Unreservable', 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1661 END IF;
1662 x_return_status := fnd_api.g_ret_sts_success;
1663
1664 /* bug 1560334 jxlu */
1665
1666 IF g_organization_id IS NOT NULL and
1667 g_organization_id = p_mo_line_rec.organization_id and
1668 g_inventory_item_id IS NOT NULL and
1669 g_inventory_item_id = p_mo_line_rec.inventory_item_id and
1670 g_default_subinventory IS NOT NULL THEN
1671
1672 l_default_subinventory := g_default_subinventory;
1673 ELSE
1674 BEGIN
1675 SELECT subinventory_code
1676 INTO l_default_subinventory
1677 FROM mtl_item_sub_defaults
1678 WHERE inventory_item_id = p_mo_line_rec.inventory_item_id
1679 AND organization_id = p_mo_line_rec.organization_id
1680 AND default_type = 1;
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 NULL;
1684 END;
1685
1686 g_organization_id := p_mo_line_rec.organization_id;
1687 g_inventory_item_id := p_mo_line_rec.inventory_item_id;
1688 g_default_subinventory := l_default_subinventory;
1689 g_default_locator_id := -1;
1690
1691 END IF;
1692
1693 IF g_default_locator_id IS NOT NULL AND
1694 g_default_locator_id = -1 THEN
1695
1696 BEGIN
1697 SELECT locator_id
1698 INTO l_default_locator_id
1699 FROM mtl_item_loc_defaults
1700 WHERE inventory_item_id = p_mo_line_rec.inventory_item_id
1701 AND organization_id = p_mo_line_rec.organization_id
1702 AND subinventory_code = l_default_subinventory
1703 AND default_type = 1;
1704 EXCEPTION
1705 WHEN NO_DATA_FOUND THEN
1706 l_default_locator_id := NULL;
1707 NULL;
1708 END;
1709
1710 g_default_locator_id := l_default_locator_id;
1711
1712 ELSE
1713 l_default_locator_id := g_default_locator_id;
1714 END IF;
1715
1716 IF (is_debug) THEN
1717 print_debug('Default Sub is'|| l_default_subinventory, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1718 print_debug('Default Loc is'|| l_default_locator_id, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1719 END IF;
1720
1721 -- 8430412 begin added the following logic to get revision for non-reservable item.
1722 IF nvl(inv_cache.item_rec.revision_qty_control_code,1) = 2 THEN -- revision controlled
1723 IF (is_debug) THEN
1724 print_debug('Revision_ctrl: '||inv_cache.item_rec.revision_qty_control_code,'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1725 END IF;
1726
1727 l_rule_id := inv_cache.item_rec.picking_rule_id;
1728
1729 IF l_rule_id is NULL THEN
1730 IF inv_cache.set_org_rec(g_organization_id) THEN
1731 l_rule_id := inv_cache.org_rec.default_picking_rule_id;
1732 ELSE
1733 l_rule_id := NULL;
1734 END IF;
1735 END IF;
1736
1737 IF (is_debug) THEN
1738 print_debug('Default picking rule id: '|| l_rule_id, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1739 END IF;
1740
1741 IF l_rule_id is not NULL THEN
1742
1743 SELECT revision_sort
1744 INTO l_revision_rule
1745 FROM mtl_inv_picking_rules
1746 WHERE wms_rule_id = l_rule_id;
1747
1748 END IF;
1749
1750 IF (is_debug) THEN
1751 print_debug('Revision Sort: '|| l_revision_rule, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1752 END IF;
1753
1754 IF l_revision_rule is not NULL THEN
1755 CASE l_revision_rule
1756 WHEN 1 THEN
1757 l_order_by := 'order by mir.revision ASC';
1758 WHEN 2 THEN
1759 l_order_by := 'order by mir.revision DESC';
1760 WHEN 3 THEN
1761 l_order_by := 'order by mir.effectivity_date ASC';
1762 WHEN 4 THEN
1763 l_order_by := 'order by mir.effectivity_date DESC';
1764 END CASE;
1765
1766 l_base_stmt := 'SELECT mir.revision FROM mtl_item_revisions mir '
1767 ||' WHERE mir.organization_id = :org_id '
1768 ||' AND mir.inventory_item_id = :item_id '
1769 ||' AND mir.effectivity_date <= SYSDATE '
1770 ||l_order_by;
1771
1772 l_stmt := 'SELECT revision FROM ('||l_base_stmt
1773 ||' ) where rownum=1 ';
1774
1775 EXECUTE IMMEDIATE l_stmt INTO l_revision USING p_mo_line_rec.organization_id, p_mo_line_rec.inventory_item_id;
1776
1777 IF (is_debug) THEN
1778 print_debug('Revision : '|| l_revision, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1779 END IF;
1780
1781 ELSE --revision sort not defined
1782
1783 l_txn_date := nvl(inv_cache.mo_transaction_date, SYSDATE);
1784
1785 IF (is_debug) THEN
1786 print_debug('call bom_revisions.get_revision to get revsion, revision date = '||l_txn_date, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1787 END IF;
1788
1789 bom_revisions.GET_REVISION (type => 'PART'
1790 ,org_id => p_mo_line_rec.organization_id
1791 ,item_id => p_mo_line_rec.inventory_item_id
1792 ,rev_date => l_txn_date
1793 ,itm_rev => l_revision);
1794
1795 IF (is_debug) THEN
1796 print_debug('Revision : '||l_revision, 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1797 END IF;
1798
1799 END IF;
1800 END IF; -- revision ctrl
1801
1802 -- end 8430412
1803
1804 l_shipping_attr(1).subinventory := l_default_subinventory;
1805 l_shipping_attr(1).locator_id := l_default_locator_id;
1806 /*end of bug 1560334 jxlu */
1807 l_shipping_attr(1).source_header_id := p_demand_info.oe_header_id;
1808 l_shipping_attr(1).source_line_id := p_demand_info.oe_line_id;
1809 l_shipping_attr(1).ship_from_org_id := p_mo_line_rec.organization_id;
1810 l_shipping_attr(1).released_status := 'Y';
1811 l_shipping_attr(1).delivery_detail_id := p_demand_info.delivery_detail_id;
1812 l_shipping_attr(1).action_flag := 'U';
1813 l_shipping_attr(1).revision := l_revision; --8430412
1814 IF (is_debug) THEN
1815 print_debug('Calling WSH_Interface.Update_Shipping_Attributes',
1816 'Inv_Pick_Release_PVT.Process_Unreservable_Items');
1817 END IF;
1818 wsh_interface.update_shipping_attributes(
1819 p_source_code => 'INV'
1820 , p_changed_attributes => l_shipping_attr
1821 , x_return_status => l_return_status
1822 );
1823 IF (is_debug) THEN
1824 print_debug('after update shipping attributes',
1825 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1826 END IF;
1827
1828 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1829 IF (is_debug) THEN
1830 print_debug('return error from update shipping attributes',
1831 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1832 END IF;
1833 RAISE fnd_api.g_exc_error;
1834 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1835 IF (is_debug) THEN
1836 print_debug('return error from update shipping attributes',
1837 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1838 END IF;
1839 RAISE fnd_api.g_exc_unexpected_error;
1840 END IF;
1841
1842 -- call the get pick slip number
1843
1844 IF (is_debug) THEN
1845 print_debug('calling get_pick_slip_number',
1846 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1847 END IF;
1848 -- Bug 2666620: Inline branching to call either WSH or INV get_pick_slip_number
1849 /* FP-J PAR Replenishment Counts: It is not required to send dest_subinv,
1850 dest_locator_id, project_id and task_id. Dest Sub / Loc not required because
1851 the item is non-reservable, project and task not required because we are
1852 not sending input parameters from_locator/to_locator. */
1853 get_pick_slip_number(
1854 p_ps_mode => p_pick_slip_mode
1855 , p_pick_grouping_rule_id => p_grouping_rule_id
1856 , p_org_id => p_mo_line_rec.organization_id
1857 , p_header_id => p_demand_info.oe_header_id
1858 , p_customer_id => p_demand_info.customer_id
1859 , p_ship_method_code => p_demand_info.freight_code
1860 , p_ship_to_loc_id => p_demand_info.ship_to_location
1861 , p_shipment_priority => p_demand_info.shipment_priority_code
1862 , p_subinventory => NULL
1863 , p_trip_stop_id => p_demand_info.trip_stop_id
1864 , p_delivery_id => p_demand_info.shipping_delivery_id
1865 , x_pick_slip_number => l_pick_slip_number
1866 , x_ready_to_print => l_ready_to_print
1867 , x_api_status => l_return_status
1868 , x_error_message => l_api_error_msg
1869 , x_call_mode => l_call_mode
1870 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
1871 , p_revision => p_mo_line_rec.revision
1872 );
1873 IF (is_debug) THEN
1874 print_debug('after calling get_pick_slip_number',
1875 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1876 END IF;
1877
1878 IF l_return_status <> fnd_api.g_ret_sts_success
1879 OR l_pick_slip_number = -1 THEN
1880 IF (is_debug) THEN
1881 print_debug('return error from get_pick_slip_number',
1882 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1883 END IF;
1884 fnd_message.set_name('INV', 'INV_NO_PICK_SLIP_NUMBER');
1885 fnd_msg_pub.ADD;
1886 RAISE fnd_api.g_exc_unexpected_error;
1887 END IF;
1888
1889 if ( p_pick_slip_mode <> 'I' ) then
1890 WSH_INV_INTEGRATION_GRP.find_printer
1891 ( p_subinventory => NULL
1892 , p_organization_id => p_mo_line_rec.organization_id
1893 , x_error_message => l_api_error_msg
1894 , x_api_Status => l_return_status
1895 );
1896 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1897 IF (is_debug) THEN
1898 print_debug('return error from WSH_INV_INTEGRATION.find_printer',
1899 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1900 END IF;
1901 RAISE fnd_api.g_exc_unexpected_error;
1902 END IF;
1903 end if ;
1904
1905 IF (is_debug) THEN
1906 print_debug('update move order line',
1907 'Inv_pick_release_pvt.process_unreservable_items');
1908 END IF;
1909
1910 p_mo_line_rec.quantity_detailed := p_mo_line_rec.quantity;
1911 p_mo_line_rec.quantity_delivered := p_mo_line_rec.quantity;
1912 p_mo_line_rec.pick_slip_number := l_pick_slip_number;
1913 p_mo_line_rec.pick_slip_date := sysdate;
1914 p_mo_line_rec.line_status := 5;
1915
1916 -- Bug 6989438
1917 IF (is_debug) THEN
1918 print_debug('p_mo_line_rec.quantity '|| p_mo_line_rec.quantity, 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1919 print_debug('p_mo_line_rec.quantity '|| p_mo_line_rec.quantity, 'Inv_Pick_Release_Pvt.Process_Unreservable_Items');
1920 END IF;
1921
1922 UPDATE mtl_txn_request_lines
1923 SET quantity_detailed = p_mo_line_rec.quantity,
1924 quantity_delivered = p_mo_line_rec.quantity,
1925 pick_slip_number = l_pick_slip_number,
1926 pick_slip_date = sysdate,
1927 line_status = 5,
1928 status_date =sysdate -- BUG 6932648
1929 WHERE line_id = p_mo_line_rec.line_id;
1930
1931 x_pick_slip_number := l_pick_slip_number;
1932 x_ready_to_print := l_ready_to_print;
1933 IF (is_debug) THEN
1934 print_debug('end of process_unreservable_items ',
1935 'Inv_pick_release_pvt.process_unreservable_items');
1936 END IF;
1937 EXCEPTION
1938 WHEN fnd_api.g_exc_error THEN
1939 x_return_status := fnd_api.g_ret_sts_error;
1940 -- Get message count and data
1941 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1942 WHEN fnd_api.g_exc_unexpected_error THEN
1943 x_return_status := fnd_api.g_ret_sts_unexp_error;
1944 -- Get message count and data
1945 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1946 WHEN OTHERS THEN
1947 x_return_status := fnd_api.g_ret_sts_unexp_error;
1948
1949 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1950 fnd_msg_pub.add_exc_msg(g_pkg_name, 'INV_PICK_RELEASE_PVT');
1951 END IF;
1952
1953 -- Get message count and data
1954 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1955 END;
1956
1957 PROCEDURE process_prj_dynamic_locator(
1958 p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
1959 , p_mold_temp_id IN NUMBER
1960 , p_mold_sub_code IN VARCHAR2
1961 , p_from_locator_id IN NUMBER
1962 , p_to_locator_id IN NUMBER
1963 , x_return_status OUT NOCOPY VARCHAR2
1964 , x_msg_count OUT NOCOPY NUMBER
1965 , x_msg_data OUT NOCOPY VARCHAR2
1966 , x_to_locator_id OUT NOCOPY NUMBER
1967 , p_to_subinventory IN VARCHAR2 DEFAULT NULL
1968 ) IS
1969 l_org_loc_control NUMBER;
1970 l_item_loc_control NUMBER;
1971 l_sub_loc_control NUMBER;
1972 l_reservable_type NUMBER;
1973 l_sub_reservable_type NUMBER;
1974 l_from_locator_id NUMBER;
1975 l_to_locator_id NUMBER;
1976 l_new_to_locator_id NUMBER;
1977 l_dummy VARCHAR2(1);
1978 l_locator inv_validate.LOCATOR;
1979 l_org inv_validate.org;
1980 l_sub inv_validate.sub;
1981 success NUMBER;
1982 l_debug NUMBER;
1983 l_return_value BOOLEAN;
1984 l_to_subinventory VARCHAR2(10);
1985 BEGIN
1986 IF is_debug IS NULL THEN
1987 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1988 If l_debug = 1 Then
1989 is_debug := TRUE;
1990 Else
1991 is_debug := FALSE;
1992 End If;
1993 END IF;
1994 IF (is_debug) THEN
1995 print_debug('inside process_prj_dynamic_locator',
1996 'Inv_Pick_Release_PVt.Process_Prj_Dynamic_Locator');
1997 END IF;
1998 x_return_status := fnd_api.g_ret_sts_success;
1999
2000 IF p_to_subinventory IS NULL THEN
2001 l_to_subinventory := p_mo_line_rec.to_subinventory_code;
2002 ELSE
2003 l_to_subinventory := p_to_subinventory;
2004 END IF;
2005
2006 IF (p_mo_line_rec.project_id IS NOT NULL) THEN
2007 IF (is_debug) THEN
2008 print_debug('Move ORder has project reference',
2009 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2010 END IF;
2011
2012 l_return_value := INV_CACHE.set_org_rec(p_mo_line_rec.organization_id);
2013 IF NOT l_return_value THEN
2014 IF (is_debug) THEN
2015 print_debug('error setting org cache',
2016 'Inv_Pick_Release_PVt.Process_Prj_Dynamic_Locator');
2017 END IF;
2018 RAISE fnd_api.g_exc_unexpected_error;
2019 END IF;
2020 l_org_loc_control := INV_CACHE.org_rec.stock_locator_control_code;
2021
2022 l_return_value := INV_CACHE.set_item_rec(
2023 p_mo_line_rec.organization_id,
2024 p_mo_line_rec.inventory_item_id);
2025 IF NOT l_return_value THEN
2026 IF (is_debug) THEN
2027 print_debug('error setting item cache',
2028 'Inv_Pick_Release_PVt.Process_Prj_Dynamic_Locator');
2029 END IF;
2030 RAISE fnd_api.g_exc_unexpected_error;
2031 END IF;
2032 l_item_loc_control := INV_CACHE.item_rec.location_control_code;
2033
2034 l_return_value := INV_CACHE.set_tosub_rec(
2035 p_mo_line_rec.organization_id,
2036 l_to_subinventory);
2037 IF NOT l_return_value THEN
2038 IF (is_debug) THEN
2039 print_debug('error setting to sub cache',
2040 'Inv_Pick_Release_PVt.Process_Prj_Dynamic_Locator');
2041 END IF;
2042 RAISE fnd_api.g_exc_unexpected_error;
2043 END IF;
2044 l_sub_loc_control := INV_CACHE.tosub_rec.locator_type;
2045
2046 IF (is_debug) THEN
2047 print_debug('l_org_loc_control is '|| l_org_loc_control, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2048 print_debug('l_item_loc_control is '|| l_item_loc_control, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2049 print_debug('l_sub_loc_control is '|| l_sub_loc_control, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2050 END IF;
2051
2052 IF (l_org_loc_control = 3
2053 OR (l_org_loc_control = 4
2054 AND l_sub_loc_control = 3
2055 )
2056 OR (l_org_loc_control = 4
2057 AND l_sub_loc_control = 5
2058 AND l_item_loc_control = 3
2059 )
2060 ) THEN
2061 IF (is_debug) THEN
2062 print_debug('inside the locator control code',
2063 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2064 END IF;
2065
2066 BEGIN
2067 SELECT *
2068 INTO l_locator
2069 FROM mtl_item_locations
2070 WHERE inventory_location_id = p_to_locator_id
2071 AND organization_id = p_mo_line_rec.organization_id
2072 AND subinventory_code = l_to_subinventory;
2073 IF (is_debug) THEN
2074 print_debug('after select l_locator', 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2075 END IF;
2076 /*
2077 SELECT *
2078 INTO l_org
2079 FROM mtl_parameters
2080 WHERE organization_id = p_mo_line_rec.organization_id;
2081
2082 IF (is_debug) THEN
2083 print_debug('after select organization', 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2084 END IF;
2085
2086 SELECT *
2087 INTO l_sub
2088 FROM mtl_secondary_inventories
2089 WHERE secondary_inventory_name = p_mo_line_rec.to_subinventory_code
2090 AND organization_id = p_mo_line_rec.organization_id;
2091 */
2092 IF (is_debug) THEN
2093 print_debug('l_locator.segment1 = '|| l_locator.segment1, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2094 print_debug('l_locator.segment2 = '|| l_locator.segment2, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2095 print_debug('l_locator.segment3 = '|| l_locator.segment3, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2096 print_debug('l_locator.segment19 = '|| l_locator.segment19, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2097 print_debug('l_locator.segment20 = '|| l_locator.segment20, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2098 END IF;
2099
2100 IF (l_locator.segment19 IS NULL
2101 AND l_locator.segment20 IS NULL
2102 ) THEN
2103 --bug 2418676 - populate physical location id
2104 l_locator.physical_location_id := l_locator.inventory_location_id;
2105 l_locator.inventory_location_id := NULL;
2106 l_locator.project_id := p_mo_line_rec.project_id;
2107 l_locator.task_id := p_mo_line_rec.task_id;
2108 l_locator.segment19 := p_mo_line_rec.project_id;
2109 l_locator.segment20 := p_mo_line_rec.task_id;
2110 success := inv_validate.validatelocator(
2111 p_locator => l_locator
2112 , p_org => INV_CACHE.org_rec
2113 , p_sub => INV_CACHE.tosub_rec
2114 , p_validation_mode => inv_validate.exists_or_create
2115 , p_value_or_id => 'I'
2116 );
2117
2118 IF (success = inv_validate.t) THEN
2119 l_new_to_locator_id := l_locator.inventory_location_id;
2120 ELSE
2121 IF (is_debug) THEN
2122 print_debug('INV_Validate error', 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2123 END IF;
2124 /*FND_MSG_PUB.Count_And_Get (p_count => l_count , p_data => l_message , p_encoded => 'T');
2125 if( l_count = 0 ) then
2126 IF (is_debug) THEN
2127 print_debug('no message from detailing engine');
2128 END IF;
2129 elsif(l_count = 1) then
2130 IF (is_debug) THEN
2131 print_debug(l_message);
2132 END IF;
2133 else
2134 for i in 1..l_count LOOP
2135 l_message := fnd_msg_pub.get(i, 'T');
2136 IF (is_debug) THEN
2137 print_debug(l_message);
2138 END IF;
2139 end loop;
2140 end if;*/
2141 fnd_message.set_name('INV', 'INV_INT_LOCSEGCODE');
2142 fnd_msg_pub.ADD;
2143 RAISE fnd_api.g_exc_unexpected_error;
2144 END IF; --success
2145 ELSE --no task/project
2146 IF (NVL(TO_NUMBER(l_locator.segment19), -1) <> NVL(p_mo_line_rec.project_id, -1)
2147 AND NVL(TO_NUMBER(l_locator.segment20), -1) <> NVL(p_mo_line_rec.task_id, -1)
2148 ) THEN
2149 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
2150 fnd_msg_pub.ADD;
2151 RAISE fnd_api.g_exc_error;
2152 END IF;
2153 END IF; --task project
2154 END;
2155
2156 IF l_new_to_locator_id IS NOT NULL THEN
2157 IF (is_debug) THEN
2158 print_debug('new locator id is '|| l_new_to_locator_id,
2159 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2160 END IF;
2161
2162 If p_mo_line_rec.to_locator_id <> l_new_to_locator_id AND
2163 p_mo_line_rec.to_locator_id IS NOT NULL Then
2164
2165 UPDATE mtl_txn_request_lines
2166 SET to_locator_id = l_new_to_locator_id
2167 WHERE line_id = p_mo_line_rec.line_id;
2168
2169 p_mo_line_rec.to_locator_id := l_new_to_locator_id;
2170 End If; -- update mtrl
2171 IF (is_debug) THEN
2172 print_debug('new locator id is '|| l_new_to_locator_id, 'Inv_Pick_Release_PVT.Process_Prj_Dynamic_Locator');
2173 END IF; --debug
2174 END IF; -- new locator id is not null
2175 END IF; --locator controlled
2176 END IF;
2177 x_to_locator_id := NVL(l_new_to_locator_id, p_to_locator_id);
2178
2179 EXCEPTION
2180 WHEN fnd_api.g_exc_error THEN
2181 x_return_status := fnd_api.g_ret_sts_error;
2182 -- Get message count and data
2183 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2184 WHEN fnd_api.g_exc_unexpected_error THEN
2185 x_return_status := fnd_api.g_ret_sts_unexp_error;
2186 -- Get message count and data
2187 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2188 WHEN OTHERS THEN
2189 x_return_status := fnd_api.g_ret_sts_unexp_error;
2190
2191 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2192 fnd_msg_pub.add_exc_msg(g_pkg_name, 'INV_PICK_RELEASE_PVT');
2193 END IF;
2194
2195 -- Get message count and data
2196 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2197 END;
2198
2199 -- Start of Comments
2200 --
2201 -- Name
2202 -- PROCEDURE Process_Line
2203 --
2204 -- Package
2205 -- INV_Pick_Release_PVT
2206 --
2207 -- Purpose
2208 -- Pick releases the move order line passed in. Any necessary validation is
2209 -- assumed to have been done by the caller.
2210 --
2211 -- Input Parameters
2212 -- p_mo_line_rec
2213 -- The Move Order Line record to pick release
2214 -- p_grouping_rule_id
2215 -- The grouping rule to use for generating pick slip numbers
2216 -- p_allow_partial_pick
2217 -- TRUE if the pick release process should continue after a line fails to
2218 -- be detailed completely. FALSE if the process should stop and roll
2219 -- back all changes if a line cannot be fully detailed.
2220 -- NOTE: Printing pick slips as the lines are detailed is only supported if
2221 -- this parameter is TRUE, since a commit must be done before printing.
2222 -- p_print_mode
2223 -- Whether the pick slips should be printed as they are generated or not.
2224 -- If this is 'I' (immediate) then after a pick slip number has been returned a
2225 -- specified number of times (given in the shipping parameters), that pick
2226 -- slip will be printed immediately.
2227 -- If this is 'E' (deferred) then the pick slips will not be printed until the
2228 -- pick release process is complete.
2229 --
2230 -- Output Parameters
2231 -- x_return_status
2232 -- if the process succeeds, the value is
2233 -- fnd_api.g_ret_sts_success;
2234 -- if there is an expected error, the value is
2235 -- fnd_api.g_ret_sts_error;
2236 -- if there is an unexpected error, the value is
2237 -- fnd_api.g_ret_sts_unexp_error;
2238 -- x_msg_count
2239 -- if there is one or more errors, the number of error messages
2240 -- in the buffer
2241 -- x_msg_data
2242 -- if there is one and only one error, the error message
2243 -- (See fnd_api package for more details about the above output parameters)
2244 --
2245 -- Bug8757642. Added p_wave_simulation_mode with default vale 'N' for WavePlanning Project.
2246 -- This project is available only in for R121 and mainline. To retain dual maintenance INV code changes are made in branchline, however it will not affect any existing flow.
2247 PROCEDURE process_line(
2248 p_api_version IN NUMBER
2249 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
2250 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
2251 , x_return_status OUT NOCOPY VARCHAR2
2252 , x_msg_count OUT NOCOPY NUMBER
2253 , x_msg_data OUT NOCOPY VARCHAR2
2254 , p_mo_line_rec IN OUT NOCOPY inv_move_order_pub.trolin_rec_type
2255 , p_grouping_rule_id IN NUMBER
2256 , p_allow_partial_pick IN VARCHAR2 DEFAULT fnd_api.g_true
2257 , p_print_mode IN VARCHAR2
2258 , x_detail_rec_count OUT NOCOPY NUMBER
2259 , p_plan_tasks IN BOOLEAN DEFAULT FALSE
2260 , p_wave_simulation_mode IN VARCHAR2 DEFAULT 'N'
2261 ) IS
2262 -- Define local variables
2263 l_api_version CONSTANT NUMBER := 1.0;
2264 l_api_name CONSTANT VARCHAR2(30) := 'Process_Line';
2265 l_demand_info wsh_inv_delivery_details_v%ROWTYPE;
2266 -- All of the demand source and delivery information for the line which is being processed
2267 -- A local copy of the move order line record
2268 l_demand_rsvs_ordered inv_reservation_global.mtl_reservation_tbl_type;
2269 -- The above table in descending order of detail, and with any non-matching records filtered out.
2270 l_mso_header_id NUMBER; -- The MTL_SALES_ORDERS header ID, which should be derived from the OE header ID
2271 l_res_ordered_index NUMBER; -- An index to the elements of the ordered and filtered reservations table.
2272 l_quantity_to_detail NUMBER; -- The quantity for the move order line which should be detailed.
2273 l_primary_uom VARCHAR2(3); -- The primary UOM for the item
2274 l_secondary_uom VARCHAR2(3); -- The primary UOM for the item
2275 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2276 l_quantity_detailed NUMBER; -- The quantity for the current move order which was detailed (in primary UOM)
2277 l_sec_quantity_detailed NUMBER; -- The quantity for the current move order which was detailed (in secondary UOM)
2278 l_num_detail_recs NUMBER; -- The number of move order line details for this move order line.
2279 l_partially_detailed NUMBER; -- A flag indicating whether the line could only be partially picked. If this flag is
2280 -- set to 1 (yes) then a return value of success would instead be 'P' for partial.
2281 l_quantity_detailed_conv NUMBER; -- The quantity detailed for the current move order (in the UOM of the move order)
2282 l_mold_temp_id NUMBER; -- The transaction temp ID of the move order line detail being processed
2283 l_mold_sub_code VARCHAR2(10); -- The subinventory code for the move order line detail being processed
2284 l_pick_slip_mode VARCHAR2(1); -- The print pick slip mode (immediate or deferred) that should be used
2285 l_pick_slip_number NUMBER; -- The pick slip number to put on the Move Order Line Details for a Line.
2286 l_reservation_detailed_qty NUMBER; -- The qty detailed for a reservation.
2287 l_rsv_detailed_qty_conv NUMBER; -- The qty detailed for a reservation. (In reservation UOM)
2288 l_rsv_detailed_qty2 NUMBER; -- The qty2 detailed for a reservation.
2289 l_prev_rsv_detailed_qty NUMBER; -- The existing qty detailed for a reservation.
2290 l_prev_rsv_detailed_qty2 NUMBER; -- The existing qty2 detailed for a reservation.
2291 l_ready_to_print VARCHAR2(1); -- The flag for whether we need to commit and print after receiving
2292 -- the current pick slip number.
2293 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
2294 l_api_error_code NUMBER; -- The error code of APIs called within the Process Line API.
2295 l_api_error_msg VARCHAR2(100); -- The error message returned by certain APIs called within Process_Line
2296 l_count NUMBER;
2297 l_message VARCHAR2(255);
2298 l_report_set_id NUMBER;
2299 l_request_number VARCHAR2(80);
2300 l_reservable_type NUMBER;
2301 l_from_locator_id NUMBER;
2302 /* FP-J PAR Replenishment Counts: 3 new variables used in get_pick_slip_number() */
2303 l_dest_subinv VARCHAR2(10);
2304 l_project_id NUMBER;
2305 l_task_id NUMBER;
2306 l_to_locator_id NUMBER;
2307 l_demand_source_type NUMBER;
2308 l_debug NUMBER;
2309 l_rsv_qty_available NUMBER := 0;
2310 l_rsv_qty2_available NUMBER := 0; --bug#7377744 This variable will pass the secondary quantity to reserve
2311 l_call_mode VARCHAR2(1); --bug 1968032 will not commit if not null when called from SE.
2312 l_return_value BOOLEAN;
2313 l_index NUMBER;
2314 l_reservation_id NUMBER;
2315 l_new_prim_rsv_quantity NUMBER;
2316 l_new_sec_rsv_quantity NUMBER := 0; -- Bug 6989438
2317 l_new_rsv_quantity NUMBER;
2318 l_revision_control_code NUMBER;
2319 l_lot_control_code NUMBER;
2320 l_lot_divisible_flag VARCHAR2(1);
2321 l_serial_number_control_code NUMBER;
2322 l_is_revision_control BOOLEAN;
2323 l_is_lot_control BOOLEAN;
2324 l_is_serial_control BOOLEAN;
2325 l_qty_on_hand NUMBER;
2326 l_qty_res_on_hand NUMBER;
2327 l_qty_res NUMBER;
2328 l_qty_sug NUMBER;
2329 l_qty_att NUMBER;
2330 l_qty_available_to_reserve NUMBER;
2331 l_max_tolerance NUMBER;
2332 l_min_tolerance NUMBER;
2333 l_revision VARCHAR2(3);
2334
2335 l_reduce_rsv_qty NUMBER := 0; -- bug 6264551
2336 l_reduce_rsv_qty2 NUMBER := 0; -- bug 6989438
2337 l_original_rsv_record inv_reservation_global.mtl_reservation_rec_type ; -- for bug 7253296
2338 l_to_rsv_record inv_reservation_global.mtl_reservation_rec_type ; -- for bug 7253296
2339
2340 -- Added following variables for Bug 7377744
2341 l_sec_qty_available_to_reserve NUMBER; -- The quantity which can still be reserved.
2342 l_sec_qty_on_hand NUMBER; -- The org-wide quantity on-hand
2343 l_sec_qty_res_on_hand NUMBER; -- The org-wide reservable quantity on-hand
2344 l_sec_qty_res NUMBER; -- The org-wide quantity reserved
2345 l_sec_qty_sug NUMBER; -- The org-wide quantity suggested
2346 l_sec_qty_att NUMBER; -- The org-wide available to transact
2347 l_sec_quantity_to_reserve NUMBER; -- The additional quantity which should be reserved.
2348 l_exp_date DATE; -- Expired lots custom hook
2349
2350 --MUOM Fulfillment Project
2351 l_max_tolerance2 NUMBER;
2352 l_min_tolerance2 NUMBER;
2353 l_fulfill_base varchar2(1):='P';
2354
2355
2356
2357 --l_request_number varchar2(30); --bug 1488875
2358 -- Define cursors
2359 /* FP-J PAR Replenishment Counts: transfer_subinventory, project_id and task_id are also
2360 fetched in the cursor. These are used in calling get_pick_slip_number */
2361 CURSOR l_mold_crs(p_mo_line_id IN NUMBER) IS
2362 SELECT transaction_temp_id
2363 , subinventory_code
2364 , locator_id
2365 , transfer_subinventory
2366 , project_id
2367 , task_id
2368 , transfer_to_location
2369 , revision
2370 FROM mtl_material_transactions_temp
2371 WHERE move_order_line_id = p_mo_line_id
2372 AND pick_slip_number IS NULL;
2373
2374 /*Bug 3229204:Adding variable to specify to be passed to process_prj_dynamic_locators*/
2375 l_dest_locator_id NUMBER;
2376
2377
2378 BEGIN
2379 IF is_debug IS NULL THEN
2380 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2381 If l_debug = 1 Then
2382 is_debug := TRUE;
2383 Else
2384 is_debug := FALSE;
2385 End If;
2386 END IF;
2387 IF (is_debug) THEN
2388 print_debug('Inside Process_Line', 'Inv_Pick_Release_PVT.Process_Line');
2389 gmi_reservation_util.println('inside Inv_Pick_Release_PVT.Process_Line');
2390 END IF;
2391
2392 SAVEPOINT process_line_pvt;
2393 x_detail_rec_count := 0;
2394 l_num_detail_recs := 0;
2395
2396 -- Standard Call to check for call compatibility
2397 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2398 RAISE fnd_api.g_exc_unexpected_error;
2399 END IF;
2400
2401 -- Initialize message list if p_init_msg_list is set to true
2402 IF fnd_api.to_boolean(p_init_msg_list) THEN
2403 fnd_msg_pub.initialize;
2404 END IF;
2405
2406 -- Initialize API return status to success
2407 x_return_status := fnd_api.g_ret_sts_success;
2408
2409 -- Return success immediately if the line is already fully detailed
2410 -- check required quantity otherwise use the quantity - CMS change bug 2135900
2411 IF NVL(p_mo_line_rec.quantity_detailed, 0) >= NVL(p_mo_line_rec.required_quantity, NVL(p_mo_line_rec.quantity, 0)) THEN
2412 RETURN;
2413 END IF;
2414 -- Override the printing mode to deferred if allow partial pick is false.
2415 -- Otherwise set it based on the parameter passed in.
2416 IF p_allow_partial_pick = fnd_api.g_false THEN
2417 l_pick_slip_mode := 'E';
2418 ELSE
2419 l_pick_slip_mode := p_print_mode;
2420 END IF;
2421
2422 inv_project.set_org_client_info(l_api_return_status,
2423 p_mo_line_rec.organization_id);
2424
2425 -- Determine the demand source and delivery information for the given line.
2426
2427 BEGIN
2428 /* BENCHMARK - don't need to join to OE_ORDER_LINES_ALL or RA_CUSTOMERS,
2429 since we don't use customer number or shipment number in this code
2430 SELECT *
2431 INTO l_demand_info
2432 FROM wsh_inv_delivery_details_v
2433 WHERE move_order_line_id = p_mo_line_rec.line_id
2434 AND move_order_line_id IS NOT NULL
2435 AND released_status = 'S';
2436 */
2437 /* Bug 5570553 added the index hint with the suggestion of apps performance team */
2438 -- Bug8757642. Added p_wave_simulation_mode with default vale 'N' for WavePlanning Project.
2439 -- This project is available only in for R121 and mainline. To retain dual maintenance INV code changes are made in branchline, however it will not affect any existing flow.
2440 IF p_wave_simulation_mode = 'N' THEN
2441 SELECT /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/
2442 wdd.source_header_id oe_header_id,
2443 wts.stop_id trip_stop_id,
2444 wdd.source_line_id oe_line_id,
2445 wda.delivery_id shipping_delivery_id,
2446 wdd.customer_id customer_id,
2447 NULL, --ra.customer number
2448 wdd.ship_to_location_id ship_to_location,
2449 wdd.ship_from_location_id ship_from_location,
2450 wdd.shipment_priority_code shipment_priority_code,
2451 NULL, --ol.shipment_number shipment_number,
2452 wdd.ship_method_code freight_code,
2453 wdd.move_order_line_id move_order_line_id,
2454 wdd.released_status,
2455 wdd.delivery_detail_id
2456 INTO l_demand_info
2457 FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda,
2458 wsh_new_deliveries wnd, wsh_delivery_legs wlg, wsh_trip_stops wts
2459 WHERE wnd.delivery_id(+) = wda.delivery_id
2460 AND wlg.delivery_id(+) = wnd.delivery_id
2461 AND wts.stop_id(+) = wlg.pick_up_stop_id
2462 AND NVL(wlg.sequence_number,-1) =
2463 (SELECT NVL(min(g.sequence_number),-1)
2464 FROM wsh_delivery_legs g
2465 WHERE g.delivery_id(+) = wnd.delivery_id )
2466 AND wdd.delivery_detail_id = wda.delivery_detail_id
2467 AND wdd.move_order_line_id = p_mo_line_rec.line_id
2468 AND wdd.move_order_line_Id IS NOT NULL
2469 --AND nvl(wdd.shipment_direction,'O') in ( 'O' , 'IO')
2470 AND wdd.released_status = 'S';
2471 ELSIF p_wave_simulation_mode = 'Y' THEN
2472 SELECT /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/
2473 wdd.source_header_id oe_header_id,
2474 wdd.source_line_id oe_line_id
2475 INTO l_demand_info.oe_header_id, l_demand_info.oe_line_id
2476 FROM wsh_delivery_details wdd
2477 WHERE wdd.move_order_line_id = p_mo_line_rec.line_id;
2478 end if;
2479 EXCEPTION
2480 WHEN NO_DATA_FOUND THEN
2481 IF (is_debug) THEN
2482 print_debug('No data found-Delivery Info', 'Inv_Pick_Release_PVT.Process_Line');
2483 gmi_reservation_util.println('Process_LineNo data found-Delivery Info');
2484 END IF;
2485 ROLLBACK TO process_line_pvt;
2486 fnd_message.set_name('INV', 'INV_DELIV_INFO_MISSING');
2487 fnd_msg_pub.ADD;
2488 RAISE fnd_api.g_exc_unexpected_error;
2489 END;
2490 IF (is_debug) THEN
2491 gmi_reservation_util.println('Process_Line after fetch wdd');
2492 END IF;
2493 l_return_value := INV_CACHE.set_item_rec(p_mo_line_rec.organization_id, p_mo_line_rec.inventory_item_id);
2494 IF NOT l_return_value THEN
2495 If is_debug THEN
2496 print_debug('Error setting item cache', 'Inv_Pick_Release_PVT.Process_Line');
2497 End If;
2498 raise fnd_api.g_exc_unexpected_error;
2499 End If;
2500 l_reservable_type:= INV_CACHE.item_rec.reservable_type;
2501
2502 IF p_mo_line_rec.transaction_type_id = 52 THEN
2503 l_demand_source_type := 2;
2504 ELSIF p_mo_line_rec.transaction_type_id = 53 THEN
2505 l_demand_source_type := 8;
2506 ELSIF g_transaction_type_id = p_mo_line_rec.transaction_type_id THEN
2507 l_demand_source_type := g_transaction_source_type_id;
2508 ELSE
2509 l_return_value := INV_CACHE.set_mtt_rec(p_mo_line_rec.transaction_type_id);
2510 IF NOT l_return_value THEN
2511 If is_debug THEN
2512 print_debug('Error setting item cache','Inv_Pick_Release_PVT.Process_Line');
2513 End If;
2514 raise fnd_api.g_exc_unexpected_error;
2515 End If;
2516 l_demand_source_type := INV_CACHE.mtt_rec.transaction_source_type_id;
2517 g_transaction_type_id := p_mo_line_rec.transaction_type_id;
2518 g_transaction_source_type_id := l_demand_source_type;
2519 END IF;
2520 IF (is_debug) THEN
2521 gmi_reservation_util.println('Process_Line after trans type ');
2522 END IF;
2523
2524 -- Compute the MTL_SALES_ORDERS header ID to use when dealing with reservations.
2525 --l_mso_header_id := inv_salesorder.get_salesorder_for_oeheader(l_demand_info.oe_header_id);
2526
2527 l_return_value := INV_CACHE.set_mso_rec(l_demand_info.oe_header_id);
2528 IF NOT l_return_value THEN
2529 IF (is_debug) THEN
2530 print_debug('No Mtl_Sales_Order ID found for oe header', 'Inv_Pick_Release_PVT.Process_Line');
2531 gmi_reservation_util.println('No Mtl_Sales_Order ID found for oe header');
2532 END IF;
2533 fnd_message.set_name('INV', 'INV_COULD_NOT_GET_MSO_HEADER');
2534 fnd_msg_pub.ADD;
2535 RAISE fnd_api.g_exc_unexpected_error;
2536 END IF;
2537
2538 l_mso_header_id := INV_CACHE.mso_rec.sales_order_id;
2539
2540 IF (is_debug) THEN
2541 gmi_reservation_util.println('Process_Line, l_mso_header_id '|| l_mso_header_id);
2542
2543 print_debug('p_mo_line_rec.unit_number is '|| p_mo_line_rec.unit_number, 'Inv_Pick_Release_PVT.Process_Line');
2544 print_debug('p_mo_line_Rec.project_id is '|| p_mo_line_rec.project_id, 'Inv_Pick_Release_PVT.Process_Line');
2545 print_debug('p_mo_line_Rec.task_id is '|| p_mo_line_rec.task_id, 'Inv_Pick_Release_PVT.Process_Line');
2546 END IF;
2547
2548 -- Retrieve reservation information for that demand source
2549 -- only if the item is reservable
2550 IF (l_reservable_type = 1) THEN
2551 IF (is_debug) THEN
2552 print_debug('Calling process_reservations', 'Inv_Pick_Release_PVT.Process_Line');
2553 END IF;
2554 --bug#7377744
2555 process_reservations(
2556 x_return_status => l_api_return_status
2557 , x_msg_count => x_msg_count
2558 , x_msg_data => x_msg_data
2559 , p_demand_info => l_demand_info
2560 , p_mo_line_rec => p_mo_line_rec
2561 , p_mso_line_id => l_mso_header_id
2562 , p_demand_source_type => l_demand_source_type
2563 , p_demand_source_name => NULL
2564 , p_allow_partial_pick => p_allow_partial_pick
2565 , x_demand_rsvs_ordered => l_demand_rsvs_ordered
2566 , x_rsv_qty_available => l_rsv_qty_available
2567 , x_rsv_qty2_available => l_rsv_qty2_available
2568 );
2569
2570 -- Return an error if the query reservations call failed
2571 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2572 ROLLBACK TO process_line_pvt;
2573 RAISE fnd_api.g_exc_unexpected_error;
2574 END IF;
2575 ELSIF (l_reservable_type = 2) THEN
2576 -- bug 1412145 for non reservable item, just update shipping attr with released_status = 'Y'
2577 -- update the qty_Detailed and qty_Delivered in the move order line
2578 IF (is_debug) THEN
2579 print_debug('Calling process_unreservable_items', 'Inv_Pick_Release_PVT.Process_Line');
2580 END IF;
2581 l_call_mode := NULL;
2582 process_unreservable_items(
2583 x_return_status => l_api_return_status
2584 , x_msg_count => x_msg_count
2585 , x_msg_data => x_msg_data
2586 , x_pick_slip_number => l_pick_slip_number
2587 , x_ready_to_print => l_ready_to_print
2588 , p_mo_line_rec => p_mo_line_rec
2589 , p_demand_info => l_demand_info
2590 , p_grouping_rule_id => p_grouping_rule_id
2591 , p_pick_slip_mode => l_pick_slip_mode
2592 , p_print_mode => p_print_mode
2593 , x_call_mode => l_call_mode
2594 );
2595
2596 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2597 ROLLBACK TO process_line_pvt;
2598 RAISE fnd_api.g_exc_unexpected_error;
2599 END IF;
2600
2601 IF l_ready_to_print = fnd_api.g_true
2602 AND p_allow_partial_pick = fnd_api.g_true
2603 AND l_call_mode IS NULL THEN
2604 COMMIT WORK;
2605
2606 l_return_value := INV_CACHE.set_mtrh_rec(p_mo_line_rec.header_id);
2607 IF NOT l_return_value THEN
2608 If is_debug THEN
2609 print_debug('Error setting header cache', 'Inv_Pick_Release_PVT.Process_Line');
2610 End If;
2611 raise fnd_api.g_exc_unexpected_error;
2612 END IF;
2613 l_request_number := INV_CACHE.mtrh_rec.request_number;
2614
2615 IF g_request_number is NOT NULL and
2616 g_request_number = l_request_number AND
2617 g_report_set_id IS NOT NULL THEN
2618 l_report_set_id := g_report_set_id;
2619 ELSE
2620 BEGIN
2621 SELECT document_set_id
2622 INTO l_report_set_id
2623 FROM wsh_picking_batches
2624 WHERE NAME = l_request_number;
2625 EXCEPTION
2626 WHEN NO_DATA_FOUND THEN
2627 IF is_debug THEN
2628 print_debug('No Data found - document set','Inv_Pick_Release_PVT.Process_Line');
2629 END IF;
2630 x_return_status := fnd_api.g_ret_sts_error;
2631 RAISE fnd_api.g_exc_error;
2632 END;
2633 g_request_number := l_request_number;
2634 g_report_set_id := l_report_set_id;
2635 END IF;
2636
2637 wsh_pr_pick_slip_number.print_pick_slip(
2638 p_pick_slip_number => l_pick_slip_number
2639 , p_report_set_id => l_report_set_id
2640 , p_organization_id => p_mo_line_rec.organization_id
2641 , x_api_status => l_api_return_status
2642 , x_error_message => l_api_error_msg
2643 );
2644
2645 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2646 IF (is_debug) THEN
2647 print_debug('Error in Print Pick Slip', 'Inv_Pick_Release_PVT.Process_Line');
2648 END IF;
2649 ROLLBACK TO process_line_pvt;
2650 fnd_message.set_name('INV', 'INV_PRINT_PICK_SLIP_FAILED');
2651 fnd_message.set_token('PICK_SLIP_NUM', TO_CHAR(l_pick_slip_number));
2652 fnd_msg_pub.ADD;
2653 RAISE fnd_api.g_exc_unexpected_error;
2654 END IF;
2655 END IF; -- ready to print
2656
2657 GOTO end_pick;
2658 ELSE
2659 print_debug('Non-standard reservable type: ' || l_reservable_type, 'Inv_Pick_Release_PVT.Process_Line');
2660 END IF; -- The item is reservable
2661
2662 l_lot_control_code:= INV_CACHE.item_rec.lot_control_code;
2663
2664 IF (l_lot_control_code = inv_reservation_global.g_lot_control_yes AND INV_CACHE.item_rec.lot_divisible_flag <> 'Y') THEN
2665 l_lot_divisible_flag := 'N';
2666 ELSE
2667 l_lot_divisible_flag := 'Y';
2668 END IF;
2669
2670 -- If the sub is not reservable we still want to move further
2671 IF g_sub_reservable_type = 1 THEN
2672 IF (l_demand_rsvs_ordered.COUNT = 0 AND l_lot_divisible_flag = 'Y' AND g_pick_nonrsv_lots <> 1) THEN -- Bug 8560030
2673 IF (is_debug) THEN
2674 print_debug('Could not reserve any qty skip suggestion', 'Inv_Pick_Release_PVT.Process_Line');
2675 END IF;
2676 GOTO rsv_failed;
2677 END IF;
2678 END IF;
2679
2680 -- If lot indivisible item then set tolerance for use during allocation
2681 l_min_tolerance := 0;
2682 l_max_tolerance := 0;
2683 g_min_tolerance := 0;
2684 g_max_tolerance := 0;
2685 --MUOM Fulfillment Project
2686 l_max_tolerance2 := 0;
2687 l_min_tolerance2 := 0;
2688 g_min_tolerance2 := 0;
2689 g_max_tolerance2 := 0;
2690 -- {{ Test Case # UTK-REALLOC-3.2.6:61 }}
2691 -- Description: Tolerances on Non-lot indivisible items should not be considered
2692 IF l_lot_divisible_flag = 'N' THEN
2693 IF (is_debug) THEN
2694 print_debug('Calling get tolerance', 'Inv_Pick_Release_PVT.Process_Line');
2695 END IF;
2696 get_tolerance(p_mo_line_id => p_mo_line_rec.line_id,
2697 x_return_status => l_api_return_status,
2698 x_msg_count => x_msg_count,
2699 x_msg_data => x_msg_data,
2700 x_max_tolerance => l_max_tolerance,
2701 x_min_tolerance => l_min_tolerance,
2702 x_max_tolerance2 => l_max_tolerance2,
2703 x_min_tolerance2 => l_min_tolerance2 );
2704 IF (is_debug) THEN
2705 print_debug('max tolerance is '|| l_max_tolerance || ' , min tolerance is ' || l_min_tolerance, 'Inv_Pick_Release_PVT.Process_line');
2706 print_debug('max tolerance is '|| l_max_tolerance2 || ' , min tolerance is ' || l_min_tolerance2, 'Inv_Pick_Release_PVT.Process_line');
2707 END IF;
2708 END IF;
2709
2710 IF (is_debug) THEN
2711 print_debug('calling create suggestions', 'Inv_Pick_Release_PVT.Process_Line');
2712 print_debug('line_id is '|| p_mo_line_rec.line_id, 'Inv_Pick_Release_PVT.Process_line');
2713 END IF;
2714 --Bug3237702 starts added for caching
2715 inv_cache.tolocator_id := p_mo_line_rec.to_locator_id;
2716 inv_cache.tosubinventory_code := p_mo_line_rec.to_subinventory_code;
2717 --Bug3237702 ends
2718 -- Bug 5264987 Added p_organization_id to the create_suggestions call
2719 inv_ppengine_pvt.create_suggestions(
2720 p_api_version => 1.0
2721 , p_init_msg_list => fnd_api.g_false
2722 , p_commit => fnd_api.g_false
2723 , x_return_status => l_api_return_status
2724 , x_msg_count => x_msg_count
2725 , x_msg_data => x_msg_data
2726 , p_transaction_temp_id => p_mo_line_rec.line_id
2727 , p_reservations => l_demand_rsvs_ordered
2728 , p_suggest_serial => 'T'
2729 , p_plan_tasks => p_plan_tasks
2730 , p_organization_id => p_mo_line_rec.organization_id
2731 , p_wave_simulation_mode => p_wave_simulation_mode
2732 );
2733
2734 g_pick_nonrsv_lots := 2; -- Bug 8560030
2735
2736 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2737 IF (is_debug) THEN
2738 print_debug('l_return_status = '|| l_api_return_status, 'Inv_Pick_Release_PVT.Process_Line');
2739 print_debug('inv detailing failed', 'Inv_Pick_Release_PVT.Process_Line');
2740 END IF;
2741 /*As a part of bug 1826833, commented the line below and
2742 uncommented the the message printing code below
2743 */
2744 -- print_debug(replace(x_msg_data,chr(0),'#'), 'Inv_Pick_Release_PVT.Process_Line');
2745 fnd_msg_pub.count_and_get(p_count => l_count, p_data => l_message, p_encoded => 'F');
2746
2747 IF (l_count = 0) THEN
2748 IF (is_debug) THEN
2749 print_debug('no message from detailing engine',
2750 'Inv_Pick_Release_PVT.Process_Line');
2751 END IF;
2752 ELSIF (l_count = 1) THEN
2753 IF (is_debug) THEN
2754 print_debug(l_message, 'Inv_Pick_Release_PVT.Process_Line');
2755 END IF;
2756 ELSE
2757 FOR i IN 1 .. l_count LOOP
2758 l_message := fnd_msg_pub.get(i, 'F');
2759 IF (is_debug) THEN
2760 print_debug(l_message, 'Inv_Pick_Release_PVT.Process_Line');
2761 END IF;
2762 END LOOP;
2763
2764 fnd_msg_pub.delete_msg();
2765 END IF;
2766
2767 ROLLBACK TO process_line_pvt;
2768
2769 l_return_value := INV_CACHE.set_mtrh_rec(p_mo_line_rec.header_id);
2770 IF NOT l_return_value THEN
2771 If is_debug THEN
2772 print_debug('Error setting header cache', 'Inv_Pick_Release_PVT.Process_Line');
2773 End If;
2774 raise fnd_api.g_exc_unexpected_error;
2775 END IF;
2776 l_request_number := INV_CACHE.mtrh_rec.request_number;
2777
2778 fnd_message.set_name('INV', 'INV_DETAILING_FAILED');
2779 fnd_message.set_token('LINE_NUM', TO_CHAR(p_mo_line_rec.line_number));
2780 fnd_message.set_token('MO_NUMBER', l_request_number);
2781 fnd_msg_pub.ADD;
2782 RAISE fnd_api.g_exc_unexpected_error;
2783 END IF;
2784
2785 IF (is_debug) THEN
2786 print_debug('after calling create suggestions with return status = '||l_api_return_status, 'Inv_Pick_Release_PVT.Process_Line');
2787 END IF;
2788
2789 -- Update the detailed quantity (and if possible, the sourcing information)
2790 -- of the Move Order Line
2791 --Incase no reservations skipped create suggestions 1705058
2792 <<rsv_failed>>
2793 BEGIN
2794 SELECT NVL(SUM(primary_quantity), 0)
2795 ,NVL(sum(transaction_quantity),0)
2796 ,NVL(sum(secondary_transaction_quantity),0)
2797 ,COUNT(*)
2798 INTO l_quantity_detailed
2799 ,l_quantity_detailed_conv
2800 ,l_sec_quantity_detailed
2801 ,l_num_detail_recs
2802 FROM mtl_material_transactions_temp
2803 WHERE move_order_line_id = p_mo_line_rec.line_id;
2804 -- Bug 6989438
2805 IF (is_debug) THEN
2806 print_debug('l_quantity detailed is '|| l_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
2807 print_debug('l_sec_quantity detailed is '|| l_sec_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
2808 print_debug('l_num_detail_recs is '|| l_num_detail_recs, 'Inv_Pick_Release_PVT.Process_Line');
2809 END IF;
2810
2811 --Bug#6085577. l_quantity_detailed_conv should hold quantity in the UOM of MTRL.
2812 l_primary_uom:= INV_CACHE.item_rec.primary_uom_code; --Get primary UOM of item.
2813 IF (is_debug) THEN
2814 print_debug('Move Order line UOM '||p_mo_line_rec.uom_code || 'Pri UOM:'|| l_primary_uom,'Inv_Pick_Release_PVT.Process_Line');
2815 END IF;
2816 IF (p_mo_line_rec.uom_code <> l_primary_uom ) THEN
2817 l_quantity_detailed_conv := inv_convert.inv_um_convert(
2818 item_id => p_mo_line_rec.inventory_item_id
2819 , PRECISION => NULL
2820 , from_quantity => l_quantity_detailed
2821 , from_unit => l_primary_uom
2822 , to_unit => p_mo_line_rec.uom_code
2823 , from_name => NULL
2824 , to_name => NULL
2825 );
2826 ELSE
2827 l_quantity_detailed_conv := l_quantity_detailed;
2828 END IF; --end of Bug#6085577
2829
2830 EXCEPTION
2831 WHEN NO_DATA_FOUND THEN
2832 IF (is_debug) THEN
2833 print_debug('no detail records found', 'Inv_Pick_Release_PVT.Process_line');
2834 END IF;
2835 l_quantity_detailed := 0;
2836 l_sec_quantity_detailed := 0;
2837 l_quantity_detailed_conv := 0;
2838 l_num_detail_recs := 0;
2839 END;
2840
2841 -- If the move order line is not fully detailed, update the
2842 -- return status as appropriate.
2843 -- {{ Test Case # UTK-REALLOC-3.2.6:62 }}
2844 -- Description: Partially detailed should be set to 1 if allocation within tolerance but less
2845 -- than the requested quantity
2846 IF l_quantity_detailed < p_mo_line_rec.primary_quantity - l_min_tolerance THEN
2847 IF p_allow_partial_pick = fnd_api.g_false THEN
2848 IF (is_debug) THEN
2849 print_debug('Error - could not pick full', 'Inv_Pick_Release_PVT.Process_Line');
2850 END IF;
2851 ROLLBACK TO process_line_pvt;
2852 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
2853 fnd_msg_pub.ADD;
2854 RAISE fnd_api.g_exc_unexpected_error;
2855 ELSE
2856 -- Set a flag to later set the return status for success,
2857 -- but only partially detailed
2858 IF (is_debug) THEN
2859 print_debug('l_partially_detailed is 1', 'Inv_Pick_Release_PVT.Process_Line');
2860 END IF;
2861 l_partially_detailed := 1;
2862 END IF;
2863 ELSE
2864 IF (is_debug) THEN
2865 print_debug('l_partially_detailed is 2', 'Inv_Pick_Release_PVT.Process_Line');
2866 IF l_quantity_detailed < p_mo_line_rec.primary_quantity THEN
2867 print_debug('Underallocated with tolerance', 'Inv_Pick_Release_PVT.Process_Line');
2868 END IF;
2869 END IF;
2870 l_partially_detailed := 2;
2871 END IF;
2872
2873 --MUOM Fulfillment Project
2874 inv_utilities.get_inv_fulfillment_base(
2875 p_source_line_id => p_mo_line_rec.txn_source_line_id,
2876 p_demand_source_type_id => l_demand_source_type,
2877 p_org_id => p_mo_line_rec.organization_id,
2878 x_fulfillment_base => l_fulfill_base
2879 );
2880
2881
2882 IF (is_debug) THEN
2883 print_debug('Fulfill Base = '||l_fulfill_base, 'Inv_Pick_Release_PVT.Process_Line');
2884 print_debug('l_sec_quantity_detailed = ' ||l_sec_quantity_detailed||
2885 ' p_mo_line_rec.secondary_quantity = '||p_mo_line_rec.secondary_quantity
2886 ||'l_min_tolerance2 = ' ||l_min_tolerance2, 'Inv_Pick_Release_PVT.Process_Line');
2887 END IF;
2888
2889 IF l_fulfill_base = 'S' THEN
2890 IF l_sec_quantity_detailed < p_mo_line_rec.secondary_quantity - l_min_tolerance2 THEN
2891 IF p_allow_partial_pick = fnd_api.g_false THEN
2892 IF (is_debug) THEN
2893 print_debug('Error - could not pick full', 'Inv_Pick_Release_PVT.Process_Line');
2894 END IF;
2895 ROLLBACK TO process_line_pvt;
2896 fnd_message.set_name('INV', 'INV_COULD_NOT_PICK_FULL');
2897 fnd_msg_pub.ADD;
2898 RAISE fnd_api.g_exc_unexpected_error;
2899 ELSE
2900 -- Set a flag to later set the return status for success,
2901 -- but only partially detailed
2902 IF (is_debug) THEN
2903 print_debug('l_partially_detailed is 1', 'Inv_Pick_Release_PVT.Process_Line');
2904 END IF;
2905 l_partially_detailed := 1;
2906 END IF;
2907 ELSE
2908 IF (is_debug) THEN
2909 print_debug('l_partially_detailed is 2', 'Inv_Pick_Release_PVT.Process_Line');
2910 IF l_sec_quantity_detailed < p_mo_line_rec.secondary_quantity THEN
2911 print_debug('Underallocated with tolerance', 'Inv_Pick_Release_PVT.Process_Line');
2912 END IF;
2913 END IF;
2914 l_partially_detailed := 2;
2915 END IF;
2916 END IF;
2917 -- Expired lots custom hook
2918
2919 IF inv_pick_release_pub.g_pick_expired_lots THEN
2920 l_exp_date := NULL;
2921 ELSE
2922 l_exp_date := SYSDATE;
2923 END IF;
2924
2925 IF (l_num_detail_recs = 0) THEN
2926 p_mo_line_rec.txn_source_id := l_mso_header_id;
2927 p_mo_line_rec.quantity_detailed:=NVL(p_mo_line_rec.quantity_delivered,0);
2928 p_mo_line_rec.secondary_quantity_detailed:=NVL(p_mo_line_rec.secondary_quantity_delivered,0);
2929 p_mo_line_rec.txn_source_line_id := l_demand_info.oe_line_id;
2930 p_mo_line_rec.pick_slip_date := SYSDATE;
2931
2932 -- Bug 6989438
2933 IF (is_debug) THEN
2934 print_debug('p_mo_line_rec.quantity_delivered '||p_mo_line_rec.quantity_delivered, 'Inv_Pick_Release_PVT.Process_Line');
2935 print_debug('p_mo_line_rec.secondary_quantity_delivered '||p_mo_line_rec.secondary_quantity_delivered, 'Inv_Pick_Release_PVT.Process_Line');
2936 end if;
2937 UPDATE MTL_TXN_REQUEST_LINES
2938 SET quantity_detailed = NVL(p_mo_line_rec.quantity_delivered,0),
2939 secondary_quantity_detailed = NVL(p_mo_line_rec.secondary_quantity_delivered,0),
2940 txn_source_id = l_mso_header_id,
2941 txn_source_line_id = l_demand_info.oe_line_id,
2942 pick_slip_date = sysdate
2943 WHERE line_id = p_mo_line_rec.line_id;
2944
2945
2946 /*update unsuggested qty as already org level rsv already created*/
2947
2948 IF (l_demand_rsvs_ordered.COUNT > 0 AND l_rsv_qty_available > 0) THEN
2949 l_index := l_demand_rsvs_ordered.LAST;
2950 l_reservation_id := l_demand_rsvs_ordered(l_index).reservation_id;
2951 l_new_prim_rsv_quantity := l_demand_rsvs_ordered(l_index).primary_reservation_quantity - NVL(l_rsv_qty_available, 0);
2952 l_new_sec_rsv_quantity := l_demand_rsvs_ordered(l_index).secondary_reservation_quantity - NVL(l_rsv_qty2_available, 0);
2953 IF (is_debug) THEN
2954 print_debug('updating reservations created as suggestions failed', 'Inv_Pick_Release_PVT.Process_Line');
2955 print_debug('reduce reservation by '|| l_rsv_qty_available, 'Inv_Pick_Release_PVT.Process_Line');
2956 print_debug('reduce secondary reservation by '||l_rsv_qty2_available, 'Inv_Pick_Release_PVT.Process_Line');
2957 END IF;
2958
2959 --if setting new quantity to 0, call delete
2960 If l_new_prim_rsv_quantity = 0 Then
2961 --CHANGE - should pass validation flag that does not validate but
2962 --does update quantity tree
2963 IF is_debug THEN
2964 print_debug('Delete org level reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
2965 END IF;
2966 inv_reservation_pvt.delete_reservation(
2967 p_api_version_number => 1.0
2968 ,p_init_msg_lst => fnd_api.g_false
2969 ,x_return_status => l_api_return_status
2970 ,x_msg_count => x_msg_count
2971 ,x_msg_data => x_msg_data
2972 ,p_rsv_rec => l_demand_rsvs_ordered(l_index)
2973 ,p_original_serial_number => l_dummy_sn
2974 ,p_validation_flag => fnd_api.g_true
2975 );
2976
2977 IF (l_api_return_status = fnd_api.g_ret_sts_error) THEN
2978 IF (is_debug) THEN
2979 print_debug('return error from delete_reservation', 'Inv_Pick_Release_Pvt.Process_Line');
2980 END IF;
2981 RAISE fnd_api.g_exc_error;
2982 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
2983 IF (is_debug) THEN
2984 print_debug('return unexpected error from delete_reservation','Inv_Pick_Release_Pvt.Process_Line');
2985 END IF;
2986 RAISE fnd_api.g_exc_unexpected_error;
2987 END IF;
2988
2989 Else
2990 --update quantity tree
2991 IF is_debug THEN
2992 print_debug('updating quantity tree', 'Inv_Pick_Release_PVT.Process_Line');
2993 END IF;
2994 l_primary_uom:= INV_CACHE.item_rec.primary_uom_code;
2995 l_secondary_uom:= INV_CACHE.item_rec.secondary_uom_code;
2996 l_revision_control_code:=INV_CACHE.item_rec.revision_qty_control_code;
2997 l_lot_control_code:= INV_CACHE.item_rec.lot_control_code;
2998 l_serial_number_control_code:= INV_CACHE.item_rec.serial_number_control_code;
2999 -- convert revision/lot control indicators into boolean
3000
3001 IF l_revision_control_code = 2 THEN
3002 l_is_revision_control := TRUE;
3003 ELSE
3004 l_is_revision_control := FALSE;
3005 END IF;
3006 --
3007 IF l_lot_control_code = 2 THEN
3008 l_is_lot_control := TRUE;
3009 ELSE
3010 l_is_lot_control := FALSE;
3011 END IF;
3012 --
3013 IF l_serial_number_control_code = 2 THEN
3014 l_is_serial_control := TRUE;
3015 ELSE
3016 l_is_serial_control := FALSE;
3017 END IF;
3018
3019 -- Added secondary qty related parameters section for Bug 7377744
3020 inv_quantity_tree_pub.update_quantities(
3021 p_api_version_number => 1.0
3022 , p_init_msg_lst => fnd_api.g_false
3023 , x_return_status => l_api_return_status
3024 , x_msg_count => x_msg_count
3025 , x_msg_data => x_msg_data
3026 , p_organization_id => p_mo_line_rec.organization_id
3027 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
3028 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
3029 , p_is_revision_control => l_is_revision_control
3030 , p_is_lot_control => l_is_lot_control
3031 , p_is_serial_control => l_is_serial_control
3032 , p_demand_source_type_id => l_demand_source_type
3033 , p_demand_source_header_id => l_mso_header_id
3034 , p_demand_source_line_id => l_demand_info.oe_line_id
3035 , p_demand_source_name => NULL
3036 , p_revision => NULL
3037 , p_lot_number => NULL
3038 , p_lot_expiration_date => l_exp_date
3039 , p_subinventory_code => NULL
3040 , p_locator_id => NULL
3041 , p_primary_quantity => -(l_rsv_qty_available)
3042 , p_secondary_quantity => -(l_rsv_qty2_available) -- Bug 7377744
3043 , p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
3044 , x_qoh => l_qty_on_hand
3045 , x_rqoh => l_qty_res_on_hand
3046 , x_qr => l_qty_res
3047 , x_qs => l_qty_sug
3048 , x_att => l_qty_att
3049 , x_atr => l_qty_available_to_reserve
3050
3051
3052 , p_grade_code => p_mo_line_rec.grade_code
3053 , x_sqoh => l_sec_qty_on_hand
3054 , x_srqoh => l_sec_qty_res_on_hand
3055 , x_sqr => l_sec_qty_res
3056 , x_sqs => l_sec_qty_sug
3057 , x_satt => l_sec_qty_att
3058 , x_satr => l_sec_qty_available_to_reserve
3059 );
3060 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3061 IF (is_debug) THEN
3062 print_debug('Error from update quantity tree',
3063 'Inv_Pick_Release_PVT.Process_Line');
3064 END IF;
3065 RAISE fnd_api.g_exc_unexpected_error;
3066 END IF;
3067
3068 --handle conversion to reservation UOM
3069 IF l_demand_rsvs_ordered(l_index).reservation_uom_code IS NULL OR l_new_prim_rsv_quantity = 0 THEN
3070 --when missing rsv UOM, assume primary UOM
3071 l_new_rsv_quantity := l_new_prim_rsv_quantity;
3072 ELSIF l_demand_rsvs_ordered(l_index).reservation_uom_code =
3073 l_primary_uom THEN
3074 --reservation UOM = primary UOM
3075 l_new_rsv_quantity := l_new_prim_rsv_quantity;
3076 ELSIF l_fulfill_base = 'S' and l_demand_rsvs_ordered(l_index).reservation_uom_code = l_secondary_uom THEN
3077 l_new_rsv_quantity := l_new_sec_rsv_quantity;
3078 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_index).reservation_uom_code <> l_secondary_uom AND l_demand_rsvs_ordered(l_index).reservation_uom_code <> l_primary_uom THEN
3079 l_new_rsv_quantity := inv_convert.inv_um_convert(
3080 item_id => p_mo_line_rec.inventory_item_id
3081 , PRECISION => NULL
3082 , from_quantity => l_new_sec_rsv_quantity
3083 , from_unit => l_secondary_uom
3084 , to_unit => l_demand_rsvs_ordered(l_index).reservation_uom_code
3085 , from_name => NULL
3086 , to_name => NULL
3087 );
3088
3089 IF (l_new_rsv_quantity = -99999) THEN
3090 IF (is_debug) THEN
3091 print_debug('Cannot convert Seconday uom to rsv uom', 'Inv_Pick_release_pvt.process_reservations');
3092 END IF;
3093 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3094 fnd_message.set_token('UOM', l_secondary_uom);
3095 fnd_message.set_token('ROUTINE', 'Pick Release process');
3096 fnd_msg_pub.ADD;
3097 RAISE fnd_api.g_exc_unexpected_error;
3098 END IF;
3099 ELSE
3100 l_new_rsv_quantity := inv_convert.inv_um_convert(
3101 item_id => p_mo_line_rec.inventory_item_id
3102 , PRECISION => NULL
3103 , from_quantity => l_new_prim_rsv_quantity
3104 , from_unit => l_primary_uom
3105 , to_unit => l_demand_rsvs_ordered(l_index).reservation_uom_code
3106 , from_name => NULL
3107 , to_name => NULL
3108 );
3109
3110
3111 IF (l_new_rsv_quantity = -99999) THEN
3112 IF (is_debug) THEN
3113 print_debug('Cannot convert primary uom to rsv uom','Inv_Pick_release_pvt.process_line');
3114 END IF;
3115 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3116 fnd_message.set_token('UOM', l_primary_uom);
3117 fnd_message.set_token('ROUTINE', 'Pick Release process');
3118 fnd_msg_pub.ADD;
3119 RAISE fnd_api.g_exc_unexpected_error;
3120 END IF;
3121 END IF;
3122
3123 -- Bug 6989438
3124 IF (is_debug) THEN
3125 print_debug('1 New prim rsv qty: ' || l_new_prim_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3126 print_debug('New rsv qty: ' || l_new_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3127 print_debug('New sec rsv qty: ' || l_new_sec_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3128 END IF;
3129 UPDATE mtl_reservations
3130 SET primary_reservation_quantity = l_new_prim_rsv_quantity
3131 ,reservation_quantity = l_new_rsv_quantity
3132 ,secondary_reservation_quantity = l_new_sec_rsv_quantity -- Bug 6989438
3133 WHERE reservation_id = l_reservation_id;
3134
3135 inv_rsv_synch.for_update(
3136 p_reservation_id => l_reservation_id
3137 , x_return_status => l_api_return_status
3138 , x_msg_count => x_msg_count
3139 , x_msg_data => x_msg_data);
3140
3141 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3142 IF (is_debug) THEN
3143 print_debug('Error from inv_rsv_synch.for_update','Inv_Pick_Release_PVT.Process_Reservations');
3144 END IF;
3145 RAISE fnd_api.g_exc_error;
3146 END IF;
3147 --
3148 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
3149 IF (is_debug) THEN
3150 print_debug('Unexp. error from inv_rsv_synch.for_update','Inv_Pick_Release_PVT.Process_Reservations');
3151 END IF;
3152 RAISE fnd_api.g_exc_unexpected_error;
3153 END IF;
3154
3155 End If; -- new rsv qty = 0
3156 END IF; -- demand rsvs ordered count > 0
3157 /*update unsuggested qty as already org level rsv already created*/
3158 END IF; --num recs = 0
3159
3160
3161 /* BUG 6216137
3162 -- Reservations created during pick release should be removed if the line is
3163 -- partially backordered */
3164 l_res_ordered_index := l_demand_rsvs_ordered.LAST;
3165 IF l_demand_rsvs_ordered.COUNT > 0 AND l_partially_detailed = 1 AND l_num_detail_recs > 0 THEN
3166
3167 IF (l_debug = 1) THEN
3168 print_debug('Checking whether to unreserve non-detailed quantity or not', 'Inv_Pick_Release_PVT.Process_Line');
3169 END IF;
3170
3171 l_reduce_rsv_qty := l_rsv_qty_available;
3172 l_reduce_rsv_qty2:= l_rsv_qty2_available;
3173
3174 BEGIN
3175 SELECT NVL(SUM(ABS(primary_quantity)), 0),
3176 NVL(SUM(ABS(secondary_transaction_quantity)), 0) -- Pushkar
3177 INTO l_reservation_detailed_qty,
3178 l_rsv_detailed_qty2
3179 FROM mtl_material_transactions_temp
3180 WHERE organization_id = p_mo_line_rec.organization_id
3181 AND reservation_id = l_demand_rsvs_ordered(l_res_ordered_index).reservation_id;
3182
3183 IF l_reduce_rsv_qty > (l_demand_rsvs_ordered(l_res_ordered_index).primary_reservation_quantity - nvl(l_reservation_detailed_qty,0)) THEN
3184 l_reduce_rsv_qty := (l_demand_rsvs_ordered(l_res_ordered_index).primary_reservation_quantity - nvl(l_reservation_detailed_qty,0));
3185 l_reduce_rsv_qty2:= (l_demand_rsvs_ordered(l_res_ordered_index).secondary_reservation_quantity - nvl(l_rsv_detailed_qty2,0));
3186 END IF;
3187 EXCEPTION
3188 WHEN NO_DATA_FOUND THEN
3189 l_reduce_rsv_qty := l_rsv_qty_available;
3190 END;
3191
3192 l_new_prim_rsv_quantity := l_demand_rsvs_ordered(l_res_ordered_index).primary_reservation_quantity - NVL(l_reduce_rsv_qty, 0);
3193 l_new_sec_rsv_quantity := l_demand_rsvs_ordered(l_res_ordered_index).secondary_reservation_quantity - NVL(l_reduce_rsv_qty2, 0);
3194
3195 IF (l_debug = 1) THEN
3196 print_debug('l_reduce_rsv_qty = '||l_reduce_rsv_qty, 'Inv_Pick_Release_PVT.Process_Line');
3197 print_debug('l_new_prim_rsv_quantity = '||l_new_prim_rsv_quantity, 'Inv_Pick_Release_PVT.Process_Line');
3198 print_debug('l_new_sec_rsv_quantity = '||l_new_sec_rsv_quantity, 'Inv_Pick_Release_PVT.Process_Line');
3199 END IF;
3200
3201 IF NVL(l_reduce_rsv_qty,0) > 0 THEN
3202 IF (l_debug = 1) THEN
3203 print_debug('updating reservations created as suggestions got created with partial qty', 'Inv_Pick_Release_PVT.Process_Line');
3204 print_debug('system generated reservation '|| l_rsv_qty_available, 'Inv_Pick_Release_PVT.Process_Line');
3205 print_debug('system generated sec reservation '||l_rsv_qty2_available, 'Inv_Pick_Release_PVT.Process_Line');
3206 print_debug('reduce reservation by '|| l_reduce_rsv_qty, 'Inv_Pick_Release_PVT.Process_Line');
3207 print_debug('reduce sec reservation by '|| l_reduce_rsv_qty2, 'Inv_Pick_Release_PVT.Process_Line');
3208 END IF;
3209
3210 --handle conversion to reservation UOM
3211 IF l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code IS NULL THEN
3212 --when missing rsv UOM, assume primary UOM
3213 l_new_rsv_quantity := l_new_prim_rsv_quantity;
3214 ELSIF l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code = l_primary_uom THEN
3215 --reservation UOM = primary UOM
3216 l_new_rsv_quantity := l_new_prim_rsv_quantity;
3217 ELSIF l_fulfill_base = 'S' and l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code = l_secondary_uom THEN
3218 l_new_rsv_quantity := l_new_sec_rsv_quantity;
3219 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code <> l_secondary_uom AND l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code <> l_primary_uom THEN
3220 l_new_rsv_quantity := inv_convert.inv_um_convert(
3221 item_id => p_mo_line_rec.inventory_item_id
3222 , PRECISION => NULL
3223 , from_quantity => l_new_sec_rsv_quantity
3224 , from_unit => l_secondary_uom
3225 , to_unit => l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code
3226 , from_name => NULL
3227 , to_name => NULL
3228 );
3229
3230 IF (l_new_rsv_quantity = -99999) THEN
3231 IF (is_debug) THEN
3232 print_debug('Cannot convert Seconday uom to rsv uom', 'Inv_Pick_release_pvt.process_reservations');
3233 END IF;
3234 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3235 fnd_message.set_token('UOM', l_secondary_uom);
3236 fnd_message.set_token('ROUTINE', 'Pick Release process');
3237 fnd_msg_pub.ADD;
3238 RAISE fnd_api.g_exc_unexpected_error;
3239 END IF;
3240 ELSE
3241 l_new_rsv_quantity := inv_convert.inv_um_convert(
3242 item_id => p_mo_line_rec.inventory_item_id
3243 , PRECISION => NULL
3244 , from_quantity => l_new_prim_rsv_quantity
3245 , from_unit => l_primary_uom
3246 , to_unit => l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code
3247 , from_name => NULL
3248 , to_name => NULL
3249 );
3250
3251 IF (l_new_rsv_quantity = -99999) THEN
3252 IF (is_debug) THEN
3253 print_debug('Cannot convert primary uom to rsv uom','Inv_Pick_release_pvt.process_line');
3254 END IF;
3255 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3256 fnd_message.set_token('UOM', l_primary_uom);
3257 fnd_message.set_token('ROUTINE', 'Pick Release process');
3258 fnd_msg_pub.ADD;
3259 RAISE fnd_api.g_exc_unexpected_error;
3260 END IF;
3261 END IF;
3262 IF (is_debug) THEN
3263 print_debug('2 New prim rsv qty: ' || l_new_prim_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3264 print_debug('New rsv qty: ' || l_new_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3265 print_debug('New sec rsv qty: ' || l_new_sec_rsv_quantity,'Inv_Pick_Release_PVT.Process_Line');
3266 END IF;
3267 /* bug 7253296 - error in quantity tree */
3268 /*l_demand_rsvs_ordered(l_res_ordered_index).primary_reservation_quantity := l_new_prim_rsv_quantity;
3269 l_demand_rsvs_ordered(l_res_ordered_index).reservation_quantity := l_new_rsv_quantity;
3270
3271 UPDATE mtl_reservations
3272 SET primary_reservation_quantity = l_new_prim_rsv_quantity
3273 ,reservation_quantity = l_new_rsv_quantity
3274 WHERE reservation_id = l_demand_rsvs_ordered(l_res_ordered_index).reservation_id;*/
3275
3276 l_original_rsv_record := l_demand_rsvs_ordered(l_res_ordered_index);
3277 l_to_rsv_record := l_original_rsv_record ;
3278 l_to_rsv_record.primary_reservation_quantity := l_new_prim_rsv_quantity;
3279 l_to_rsv_record.reservation_quantity := l_new_rsv_quantity;
3280 l_to_rsv_record.secondary_reservation_quantity := l_new_sec_rsv_quantity; -- Bug 6989438
3281
3282 inv_reservation_pub.update_reservation(
3283 p_api_version_number => 1.0
3284 , p_init_msg_lst => fnd_api.g_false
3285 , x_return_status => l_api_return_status
3286 , x_msg_count => l_count
3287 , x_msg_data => l_message
3288 , p_original_rsv_rec => l_original_rsv_record
3289 , p_to_rsv_rec => l_to_rsv_record
3290 , p_original_serial_number => l_dummy_sn
3291 , p_to_serial_number => l_dummy_sn
3292 , p_validation_flag => fnd_api.g_true
3293 );
3294
3295 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3296 IF (is_debug) THEN
3297 print_debug('error in update reservation', 'Inv_Pick_Release_PVT.Process_Reservations');
3298 END IF;
3299 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
3300 fnd_msg_pub.ADD;
3301 RAISE fnd_api.g_exc_unexpected_error;
3302 END IF;
3303
3304 /* inv_rsv_synch.for_update(
3305 p_reservation_id => l_demand_rsvs_ordered(l_res_ordered_index).reservation_id
3306 , x_return_status => l_api_return_status
3307 , x_msg_count => x_msg_count
3308 , x_msg_data => x_msg_data);
3309
3310 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3311 IF (is_debug) THEN
3312 print_debug('Error from inv_rsv_synch.for_update','Inv_Pick_Release_PVT.Process_Line');
3313 END IF;
3314 RAISE fnd_api.g_exc_error;
3315 END IF;
3316 --
3317 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
3318 IF (is_debug) THEN
3319 print_debug('Unexp. error from inv_rsv_synch.for_update','Inv_Pick_Release_PVT.Process_Line');
3320 END IF;
3321 RAISE fnd_api.g_exc_unexpected_error;
3322 END IF;*/
3323 /* end bug 7253296 */
3324 END IF; /* IF NVL(l_reduce_rsv_qty,0) > 0 THEN */
3325 END IF;
3326 /* End bug 6216137 */
3327
3328 -- Update the line with the supply information if all the detail
3329 -- records match (otherwise update the line with NULL)
3330 IF l_num_detail_recs > 0 THEN
3331 -- Calculate the quantity detailed in the UOM of the move order line
3332 IF (is_debug) THEN
3333 print_debug('calling inv_convert.inv_uom_convert', 'Inv_Pick_Release_PVT.Process_Line');
3334 print_debug('l_quantity_detailed = '|| l_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
3335 print_debug('l_sec_quantity_detailed = '|| l_sec_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
3336 END IF;
3337
3338 l_quantity_detailed_conv := nvl(l_quantity_detailed_conv,0) + Nvl(p_mo_line_rec.quantity_delivered,0);
3339 l_sec_quantity_detailed := nvl(l_sec_quantity_detailed,0) + Nvl(p_mo_line_rec.secondary_quantity_delivered,0);
3340
3341 p_mo_line_rec.quantity_detailed := l_quantity_detailed_conv;
3342 p_mo_line_rec.secondary_quantity_detailed := l_sec_quantity_detailed;
3343 p_mo_line_rec.txn_source_id := l_mso_header_id;
3344 p_mo_line_rec.txn_source_line_id := l_demand_info.oe_line_id;
3345 p_mo_line_rec.pick_slip_date := sysdate;
3346
3347 -- Bug 6989438
3348 IF (is_debug) THEN
3349 print_debug('Quantity_detailed is '|| l_quantity_detailed_conv, 'Inv_Pick_Release_PVT.Process_Line');
3350 print_debug('sec Quantity_detailed is '|| l_sec_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
3351 END IF;
3352
3353 UPDATE MTL_TXN_REQUEST_LINES
3354 SET quantity_detailed = l_quantity_detailed_conv,
3355 secondary_quantity_detailed = l_sec_quantity_detailed,
3356 txn_source_id = l_mso_header_id,
3357 txn_source_line_id = l_demand_info.oe_line_id,
3358 pick_slip_date = sysdate
3359 WHERE line_id = p_mo_line_rec.line_id;
3360
3361 -- Update the quantity detailed on the reservations
3362 IF l_demand_rsvs_ordered.COUNT > 0 THEN
3363 l_res_ordered_index := l_demand_rsvs_ordered.FIRST;
3364
3365 l_primary_uom:= INV_CACHE.item_rec.primary_uom_code;
3366 l_secondary_uom:= INV_CACHE.item_rec.secondary_uom_code;
3367 l_revision_control_code:=INV_CACHE.item_rec.revision_qty_control_code;
3368 l_lot_control_code:= INV_CACHE.item_rec.lot_control_code;
3369 l_serial_number_control_code:= INV_CACHE.item_rec.serial_number_control_code;
3370 -- convert revision/lot control indicators into boolean
3371 IF l_revision_control_code = 2 THEN
3372 l_is_revision_control := TRUE;
3373 ELSE
3374 l_is_revision_control := FALSE;
3375 END IF;
3376 --
3377 IF l_lot_control_code = 2 THEN
3378 l_is_lot_control := TRUE;
3379 ELSE
3380 l_is_lot_control := FALSE;
3381 END IF;
3382 --
3383 IF l_serial_number_control_code = 2 THEN
3384 l_is_serial_control := TRUE;
3385 ELSE
3386 l_is_serial_control := FALSE;
3387 END IF;
3388
3389 LOOP
3390 l_reservation_id := l_demand_rsvs_ordered(l_res_ordered_index).reservation_id;
3391
3392 l_prev_rsv_detailed_qty := nvl(l_demand_rsvs_ordered(l_res_ordered_index).detailed_quantity,0);
3393 l_prev_rsv_detailed_qty2 := nvl(l_demand_rsvs_ordered(l_res_ordered_index).secondary_detailed_quantity,0);
3394 BEGIN
3395 SELECT NVL(SUM(ABS(primary_quantity)), 0)
3396 , NVL(SUM(ABS(secondary_transaction_quantity)), 0)
3397 INTO l_reservation_detailed_qty
3398 , l_rsv_detailed_qty2
3399 FROM mtl_material_transactions_temp
3400 WHERE organization_id = p_mo_line_rec.organization_id
3401 AND reservation_id = l_reservation_id;
3402 EXCEPTION
3403 WHEN NO_DATA_FOUND THEN
3404 l_reservation_detailed_qty := 0;
3405 l_rsv_detailed_qty2 := 0;
3406 END;
3407 --update quantity tree
3408 IF is_debug THEN
3409 print_debug('updating quantity tree', 'Inv_Pick_Release_PVT.Process_Line');
3410 END IF;
3411 inv_quantity_tree_pub.update_quantities(
3412 p_api_version_number => 1.0
3413 , p_init_msg_lst => fnd_api.g_false
3414 , x_return_status => l_api_return_status
3415 , x_msg_count => x_msg_count
3416 , x_msg_data => x_msg_data
3417 , p_organization_id => p_mo_line_rec.organization_id
3418 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
3419 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
3420 , p_is_revision_control => l_is_revision_control
3421 , p_is_lot_control => l_is_lot_control
3422 , p_is_serial_control => l_is_serial_control
3423 , p_demand_source_type_id => l_demand_source_type
3424 , p_demand_source_header_id => l_mso_header_id
3425 , p_demand_source_line_id => l_demand_info.oe_line_id
3426 , p_demand_source_name => NULL
3427 , p_revision => l_demand_rsvs_ordered(l_res_ordered_index).revision
3428 , p_lot_number => l_demand_rsvs_ordered(l_res_ordered_index).lot_number
3429 , p_lot_expiration_date => l_exp_date
3430 , p_subinventory_code => l_demand_rsvs_ordered(l_res_ordered_index).subinventory_code
3431 , p_locator_id => l_demand_rsvs_ordered(l_res_ordered_index).locator_id
3432 , p_primary_quantity => -(l_reservation_detailed_qty - l_prev_rsv_detailed_qty)
3433 , p_secondary_quantity => -(l_rsv_detailed_qty2 - l_prev_rsv_detailed_qty2) /* Bug 7377744 */
3434 , p_lpn_id => l_demand_rsvs_ordered(l_res_ordered_index).lpn_id /* Bug 7229711 */
3435 , p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
3436 , x_qoh => l_qty_on_hand
3437 , x_rqoh => l_qty_res_on_hand
3438 , x_qr => l_qty_res
3439 , x_qs => l_qty_sug
3440 , x_att => l_qty_att
3441 , x_atr => l_qty_available_to_reserve
3442 /* Added following secondary qty related section for Bug 7377744 */
3443 , p_grade_code => p_mo_line_rec.grade_code
3444 , x_sqoh => l_sec_qty_on_hand
3445 , x_srqoh => l_sec_qty_res_on_hand
3446 , x_sqr => l_sec_qty_res
3447 , x_sqs => l_sec_qty_sug
3448 , x_satt => l_sec_qty_att
3449 , x_satr => l_sec_qty_available_to_reserve
3450 );
3451 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3452 IF (is_debug) THEN
3453 print_debug('Error from update quantity tree', 'Inv_Pick_Release_PVT.Process_Line');
3454 END IF;
3455 RAISE fnd_api.g_exc_unexpected_error;
3456 END IF;
3457 IF (is_debug) THEN
3458 print_debug('update reservation with quantity_detailed', 'Inv_Pick_Release_PVT.Process_Line');
3459 print_debug('quantity_detailed'|| l_reservation_detailed_qty, 'Inv_Pick_Release_PVT.Process_Line');
3460 print_debug('prev_quantity_detailed'|| l_prev_rsv_detailed_qty, 'Inv_Pick_Release_PVT.Process_Line');
3461 END IF;
3462
3463 -- Bug Fix 5624514
3464 --handle conversion to reservation UOM
3465 IF l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code IS NULL
3466 THEN
3467 --when missing rsv UOM, assume primary UOM
3468 l_rsv_detailed_qty_conv := l_reservation_detailed_qty;
3469 ELSIF l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code =
3470 l_primary_uom THEN
3471 --reservation UOM = primary UOM
3472 l_rsv_detailed_qty_conv := l_reservation_detailed_qty;
3473 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code = l_secondary_uom THEN
3474 l_rsv_detailed_qty_conv := l_rsv_detailed_qty2;
3475 ELSIF l_fulfill_base = 'S' AND l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code <> l_secondary_uom AND l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code <> l_primary_uom THEN
3476 l_rsv_detailed_qty_conv := inv_convert.inv_um_convert(
3477 item_id => p_mo_line_rec.inventory_item_id
3478 , PRECISION => NULL
3479 , from_quantity => l_rsv_detailed_qty2
3480 , from_unit => l_secondary_uom
3481 , to_unit => l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code
3482 , from_name => NULL
3483 , to_name => NULL
3484 );
3485
3486 IF (l_new_rsv_quantity = -99999) THEN
3487 IF (is_debug) THEN
3488 print_debug('Cannot convert Seconday uom to rsv uom', 'Inv_Pick_release_pvt.process_reservations');
3489 END IF;
3490 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3491 fnd_message.set_token('UOM', l_secondary_uom);
3492 fnd_message.set_token('ROUTINE', 'Pick Release process');
3493 fnd_msg_pub.ADD;
3494 RAISE fnd_api.g_exc_unexpected_error;
3495 END IF;
3496 ELSE
3497 l_rsv_detailed_qty_conv := inv_convert.inv_um_convert(
3498 item_id => p_mo_line_rec.inventory_item_id
3499 , PRECISION => NULL
3500 , from_quantity => l_reservation_detailed_qty
3501 , from_unit => l_primary_uom
3502 , to_unit => l_demand_rsvs_ordered(l_res_ordered_index).reservation_uom_code
3503 , from_name => NULL
3504 , to_name => NULL
3505 );
3506 IF (l_rsv_detailed_qty_conv = -99999) THEN
3507 IF (is_debug) THEN
3508 print_debug('Cannot convert primary uom to rsv uom','Inv_Pick_release_pvt.process_reservations');
3509 END IF;
3510 fnd_message.set_name('INV', 'INV-CANNOT CONVERT');
3511 fnd_message.set_token('UOM', l_primary_uom);
3512 fnd_message.set_token('ROUTINE', 'Pick Release process');
3513 fnd_msg_pub.ADD;
3514 RAISE fnd_api.g_exc_unexpected_error;
3515 END IF;
3516 END IF;
3517 -- End of Bug Fix 5624514
3518 IF (is_debug) THEN
3519 print_debug('quantity_detailed conv'|| l_rsv_detailed_qty_conv, 'Inv_Pick_Release_PVT.Process_Line');
3520 END IF;
3521 -- Upper tolerance may lead to allocation above the reservation
3522 -- {{ Test Case # UTK-REALLOC-3.2.6:64 }}
3523 -- Description: If allocation is greater than reservation, increase the reserved quantity
3524
3525
3526 -- Bug 6989438
3527 IF (is_debug) THEN
3528 print_debug('l_rsv_detailed_qty_conv '|| l_rsv_detailed_qty_conv, 'Inv_Pick_Release_PVT.Process_Line');
3529 print_debug('l_rsv_detailed_qty2 '|| l_rsv_detailed_qty2, 'Inv_Pick_Release_PVT.Process_Line');
3530 END IF;
3531
3532 UPDATE mtl_reservations
3533 SET reservation_quantity = greatest(reservation_quantity, l_rsv_detailed_qty_conv) -- Bug Fix 5624514
3534 , primary_reservation_quantity = greatest(primary_reservation_quantity, l_reservation_detailed_qty)
3535 , secondary_reservation_quantity = greatest(secondary_reservation_quantity, l_rsv_detailed_qty2)
3536 , detailed_quantity = l_reservation_detailed_qty
3537 , secondary_detailed_quantity = l_rsv_detailed_qty2
3538 WHERE reservation_id = l_reservation_id;
3539
3540 EXIT WHEN l_res_ordered_index = l_demand_rsvs_ordered.LAST;
3541 l_res_ordered_index:= l_demand_rsvs_ordered.NEXT(l_res_ordered_index);
3542 END LOOP;
3543 END IF;
3544
3545 -- Obtain the pick slip number for each Move Order Line Detail created
3546 -- Bug8757642. Added p_wave_simulation_mode with default vale 'N' for WavePlanning Project.
3547 -- This project is available only in for R121 and mainline. To retain dual maintenance INV code changes are made in branchline, however it will not affect any existing flow.
3548 IF p_wave_simulation_mode = 'N' THEN
3549 OPEN l_mold_crs(p_mo_line_rec.line_id);
3550
3551 LOOP
3552 -- Retrieve each Move Order Line Detail and get the pick slip number for each
3553 FETCH l_mold_crs INTO l_mold_temp_id, l_mold_sub_code, l_from_locator_id, l_dest_subinv,
3554 l_project_id, l_task_id, l_to_locator_id, l_revision;
3555
3556 IF l_mold_crs%FOUND THEN
3557 -- bug 1159171. check if the org is dynamic locator control
3558 -- or if the sub is dynamic locator contro, and there is project and task
3559 -- for the move order line, then create a new locator with the project
3560 -- and task.
3561 IF (is_debug) THEN
3562 print_debug('Calling process_prj_dynamic_locator with the following values','Inv_Pick_Release_PVT.Process_Line');
3563 print_debug('p_from_locator_id ==> '||l_from_locator_id, 'Inv_Pick_Release_PVT.Process_Line');
3564 print_debug('p_to_locator_id ==> '||l_to_locator_id, 'Inv_Pick_Release_PVT.Process_Line');
3565 print_debug('p_mold_temp_id ==> '||l_mold_temp_id, 'Inv_Pick_Release_PVT.Process_Line');
3566 print_debug('p_mold_sub_code ==> '||l_mold_sub_code, 'Inv_Pick_Release_PVT.Process_Line');
3567 END IF;
3568
3569 /*Bug Number:3229204:l_to_locator_id was passed as a parameter for p_to_locator_id as well
3570 as x_to_locator_id .x_to_locator id is a out parameter with no copy hint,which was causing
3571 l_to_locator_id to be nulled out.*/
3572
3573 process_prj_dynamic_locator(
3574 p_mo_line_rec => p_mo_line_rec
3575 , p_mold_temp_id => l_mold_temp_id
3576 , p_mold_sub_code => l_mold_sub_code
3577 , p_from_locator_id => l_from_locator_id
3578 , p_to_locator_id => l_to_locator_id
3579 , x_return_status => l_api_return_status
3580 , x_msg_count => x_msg_count
3581 , x_msg_data => x_msg_data
3582 , x_to_locator_id => l_dest_locator_id
3583 , p_to_subinventory => l_dest_subinv
3584 );
3585
3586
3587
3588 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3589 ROLLBACK TO process_line_pvt;
3590 RAISE fnd_api.g_exc_unexpected_error;
3591 END IF;
3592
3593 IF (is_debug) THEN
3594 print_debug('Value of locator id obtained from process_prj_dynamic_locator '||l_dest_locator_id,'Inv_Pick_Release_PVT.Process_Line');
3595 END IF;
3596
3597
3598 -- patchset J, bulk picking -------------------
3599 if (WMS_INSTALL.check_install(
3600 x_return_status => l_api_return_status,
3601 x_msg_count => x_msg_count,
3602 x_msg_data => x_msg_data,
3603 p_organization_id => p_mo_line_rec.organization_id
3604 ) = TRUE
3605 and WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) then
3606 IF (l_debug = 1) THEN
3607 print_debug('PATCHSET J -- BULK PICKING, do not assign pick slip number now', 'Inv_Pick_Release_PVT.Process_Line');
3608 END IF;
3609 ELSE -- INV org or before patchset J
3610
3611 IF (is_debug) THEN
3612 print_debug('get pick slip number','Inv_Pick_Release_PVT.Process_Line');
3613 END IF;
3614 l_call_mode := NULL;
3615 -- Bug 2666620: Inline branching to call either WSH or INV get_pick_slip_number
3616 /* FP-J PAR Replenishment Counts: Pass 4 new parameters for grouping */
3617 get_pick_slip_number(
3618 p_ps_mode => l_pick_slip_mode
3619 , p_pick_grouping_rule_id => p_grouping_rule_id
3620 , p_org_id => p_mo_line_rec.organization_id
3621 , p_header_id => l_demand_info.oe_header_id
3622 , p_customer_id => l_demand_info.customer_id
3623 , p_ship_method_code => l_demand_info.freight_code
3624 , p_ship_to_loc_id => l_demand_info.ship_to_location
3625 , p_shipment_priority => l_demand_info.shipment_priority_code
3626 , p_subinventory => l_mold_sub_code
3627 , p_trip_stop_id => l_demand_info.trip_stop_id
3628 , p_delivery_id => l_demand_info.shipping_delivery_id
3629 , x_pick_slip_number => l_pick_slip_number
3630 , x_ready_to_print => l_ready_to_print
3631 , x_api_status => l_api_return_status
3632 , x_error_message => l_api_error_msg
3633 , x_call_mode => l_call_mode
3634 , p_dest_subinv => l_dest_subinv
3635 , p_dest_locator_id => l_dest_locator_id --Bug Number:3229204:Passing l_dest_locator_id instead of l_to_locator_id
3636 , p_project_id => l_project_id
3637 , p_task_id => l_task_id
3638 , p_inventory_item_id => p_mo_line_rec.inventory_item_id
3639 , p_locator_id => l_from_locator_id
3640 , p_revision => l_revision
3641 );
3642 IF (is_debug) THEN
3643 print_debug('l_call_mode'|| l_call_mode, 'Inv_Pick_Release_PVT.Process_Line');
3644 END IF;
3645
3646 IF l_api_return_status <> fnd_api.g_ret_sts_success OR l_pick_slip_number = -1 THEN
3647 ROLLBACK TO process_line_pvt;
3648 fnd_message.set_name('INV', 'INV_NO_PICK_SLIP_NUMBER');
3649 fnd_msg_pub.ADD;
3650 RAISE fnd_api.g_exc_unexpected_error;
3651 END IF;
3652 IF ( l_pick_slip_mode <> 'I' ) THEN
3653 WSH_INV_INTEGRATION_GRP.FIND_PRINTER
3654 ( p_subinventory => l_mold_sub_code
3655 , p_organization_id => p_mo_line_rec.organization_id
3656 , x_error_message => l_api_error_msg
3657 , x_api_Status => l_api_return_status
3658 ) ;
3659
3660 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3661 IF (is_debug) THEN
3662 print_debug('return error from WSH_INV_INTEGRATION.find_printer','Inv_Pick_Release_Pvt.Process_Line');
3663 END IF;
3664 RAISE fnd_api.g_exc_unexpected_error;
3665 END IF;
3666 END IF ;
3667 -- Assign the pick slip number to the record in MTL_MATERIAL_TRANSACTIONS_TEMP
3668 UPDATE mtl_material_transactions_temp
3669 SET pick_slip_number = l_pick_slip_number
3670 , transaction_source_id = l_mso_header_id
3671 , trx_source_line_id = l_demand_info.oe_line_id
3672 , demand_source_header_id = l_mso_header_id
3673 , demand_source_line = l_demand_info.oe_line_id
3674 , transfer_to_location = l_dest_locator_id
3675 WHERE transaction_temp_id = l_mold_temp_id;
3676
3677 -- If the pick slip is ready to be printed (and partial
3678 -- picking is allowed) commit
3679 -- and print at this point.
3680 -- Bug 1663376 - Don't Commit if Ship_set_Id is not null,
3681 -- since we need to be able to rollback
3682 IF l_ready_to_print = fnd_api.g_true
3683 AND p_allow_partial_pick = fnd_api.g_true
3684 AND p_mo_line_rec.ship_set_id IS NULL
3685 AND p_mo_line_rec.ship_model_id IS NULL
3686 AND l_call_mode IS NULL THEN
3687 COMMIT WORK;
3688
3689 l_return_value := INV_CACHE.set_mtrh_rec(p_mo_line_rec.header_id);
3690 IF NOT l_return_value THEN
3691 If is_debug THEN
3692 print_debug('Error setting header cache','Inv_Pick_Release_PVT.Process_Line');
3693 End If;
3694 raise fnd_api.g_exc_unexpected_error;
3695 END IF;
3696 l_request_number := INV_CACHE.mtrh_rec.request_number;
3697
3698 IF g_request_number is NOT NULL and
3699 g_request_number = l_request_number AND
3700 g_report_set_id IS NOT NULL THEN
3701 l_report_set_id := g_report_set_id;
3702 ELSE
3703 BEGIN
3704 SELECT document_set_id
3705 INTO l_report_set_id
3706 FROM wsh_picking_batches
3707 WHERE NAME = l_request_number;
3708 EXCEPTION
3709 WHEN NO_DATA_FOUND THEN
3710 IF is_debug THEN
3711 print_debug('No Data found - document set',
3712 'Inv_Pick_Release_PVT.Process_Line');
3713 END IF;
3714 x_return_status := fnd_api.g_ret_sts_error;
3715 RAISE fnd_api.g_exc_error;
3716 END;
3717 g_request_number := l_request_number;
3718 g_report_set_id := l_report_set_id;
3719 END IF;
3720 wsh_pr_pick_slip_number.print_pick_slip(
3721 p_pick_slip_number => l_pick_slip_number
3722 , p_report_set_id => l_report_set_id
3723 , p_organization_id => p_mo_line_rec.organization_id
3724 , x_api_status => l_api_return_status
3725 , x_error_message => l_api_error_msg
3726 );
3727 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3728 ROLLBACK TO process_line_pvt;
3729 fnd_message.set_name('INV', 'INV_PRINT_PICK_SLIP_FAILED');
3730 fnd_message.set_token('PICK_SLIP_NUM', TO_CHAR(l_pick_slip_number));
3731 fnd_msg_pub.ADD;
3732 RAISE fnd_api.g_exc_unexpected_error;
3733 END IF;
3734 END IF;
3735 END IF; -- end the patchset J and WMS org check
3736 END IF;
3737
3738 EXIT WHEN l_mold_crs%NOTFOUND;
3739 END LOOP;
3740
3741 CLOSE l_mold_crs;
3742 END IF; -- p_wave_simulation_mode = 'N'
3743 END IF; -- if l_num_detail_rec > 0
3744
3745 -- If the line was only partially detailed and the API was about to return success,
3746 -- set the return status to 'P' (for partial) instead.
3747 IF x_return_status = fnd_api.g_ret_sts_success
3748 AND l_partially_detailed = 1 THEN
3749 IF (is_debug) THEN
3750 print_debug('x_return_status is '|| x_return_status, 'Inv_Pick_Release_PVT.Process_Line');
3751 END IF;
3752 x_return_status := 'P';
3753 END IF;
3754
3755 select quantity_detailed
3756 , secondary_quantity_detailed
3757 into l_quantity_detailed
3758 , l_sec_quantity_detailed
3759 from mtl_txn_request_lines
3760 WHERE line_id = p_mo_line_rec.line_id;
3761 print_debug('Quantity_detailed is '|| l_quantity_detailed,'Inv_Pick_Release_PVT.Process_Line');
3762 print_debug('2nd after select sec Quantity_detailed is '|| l_sec_quantity_detailed, 'Inv_Pick_Release_PVT.Process_Line');
3763
3764 --x_detail_rec_count := l_num_detail_recs;
3765 -- Standard call to commit
3766 <<end_pick>>
3767 IF p_commit = fnd_api.g_true
3768 AND p_allow_partial_pick = fnd_api.g_true THEN
3769 COMMIT;
3770 END IF;
3771 print_debug('Commit? is '|| p_commit, 'Inv_Pick_Release_PVT.Process_Line');
3772
3773 x_detail_rec_count := l_num_detail_recs;
3774 EXCEPTION
3775 WHEN fnd_api.g_exc_error THEN
3776 x_return_status := fnd_api.g_ret_sts_error;
3777 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3778 WHEN fnd_api.g_exc_unexpected_error THEN
3779 x_return_status := fnd_api.g_ret_sts_unexp_error;
3780 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3781 WHEN OTHERS THEN
3782 x_return_status := fnd_api.g_ret_sts_unexp_error;
3783
3784 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3785 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3786 END IF;
3787
3788 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3789
3790 END process_line;
3791 END inv_pick_release_pvt;