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