1 PACKAGE BODY INV_Pick_Release_PUB AS
2 /* $Header: INVPPICB.pls 120.41.12020000.7 2013/03/29 10:53:20 brana ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_Pick_Release_PUB';
6
7 is_debug BOOLEAN := NULL;
8 all_del_det_bo_tbl WSH_INTERFACE.ChangedAttributeTabType;
9 g_org_grouping_rule_id NUMBER;
10 g_organization_id NUMBER;
11 g_print_mode VARCHAR2(1);
12
13 -- Bug# 4258360: Added these global constants which refer to the allocation method
14 -- stored for the current pick release batch (refers to INV_CACHE.wpb_rec.allocation_method)
15 g_inventory_only CONSTANT VARCHAR2(1) := 'I';
16 g_crossdock_only CONSTANT VARCHAR2(1) := 'C';
17 g_prioritize_inventory CONSTANT VARCHAR2(1) := 'N';
18 g_prioritize_crossdock CONSTANT VARCHAR2(1) := 'X';
19
20
21 -- Start of Comments
22 -- API name Pick_Release
23 -- Type Public
24 -- Purpose
25 -- Pick releases the move order lines passed in.
26 --
27 -- Input Parameters
28 -- p_api_version_number
29 -- API version number (current version is 1.0)
30 -- p_init_msg_list (optional, default FND_API.G_FALSE)
31 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
32 -- if set to FND_API.G_TRUE
33 -- initialize error message list
34 -- if set to FND_API.G_FALSE - not initialize error
35 -- message list
36 -- p_commit (optional, default FND_API.G_FALSE)
37 -- whether or not to commit the changes to database
38 --
39 -- p_mo_line_tbl
40 -- Table of Move Order Line records to pick release
41 -- p_auto_pick_confirm (optional, default 2)
42 -- Overrides org-level parameter for whether to automatically call
43 -- pick confirm after release
44 -- Valid values: 1 (yes) or 2 (no)
45 -- p_grouping_rule_id
46 -- Overrides org-level and Move Order header-level grouping rule for
47 -- generating pick slip numbers
48 -- p_allow_partial_pick
49 -- TRUE if the pick release process should continue after a line fails to
50 -- be detailed completely. FALSE if the process should stop and roll
51 -- back all changes if a line cannot be fully detailed.
52 -- NOTE: Printing pick slips as the lines are detailed is only supported if
53 -- this parameter is TRUE, since a commit must be done before printing.
54 --
55 -- Output Parameters
56 -- x_return_status
57 -- if the pick release process succeeds, the value is
58 -- fnd_api.g_ret_sts_success;
59 -- if there is an expected error, the value is
60 -- fnd_api.g_ret_sts_error;
61 -- if there is an unexpected error, the value is
62 -- fnd_api.g_ret_sts_unexp_error;
63 -- x_msg_count
64 -- if there is one or more errors, the number of error messages
65 -- in the buffer
66 -- x_msg_data
67 -- if there is one and only one error, the error message
68 -- (See fnd_api package for more details about the above output parameters)
69 -- x_pick_release_status
70 -- This output parameter is a table of records (of type
71 -- INV_Release_Status_Tbl_Type) which specifies the pick release status
72 -- for each move order line that is passed in.
73 --
74
75 procedure print_debug( p_message in varchar2, p_module in varchar2) is
76 begin
77 inv_trx_util_pub.trace(p_message, 'PICKREL');
78 end;
79
80
81
82 --2509322:Earlier when ever a component in a model is short we backorder all
83 -- components to the difference of new model quantity and original
84 -- and repick release all.But if many components are short then we end
85 -- up splitting many delivery details.
86 -- Now avoided multiple splits by storing back order details and do only once.
87
88 PROCEDURE Store_smc_bo_details
89 (x_return_status OUT NOCOPY VARCHAR2,
90 back_order_det_tbl IN WSH_INTERFACE.ChangedAttributeTabType) IS
91 l_delivery_detail_id NUMBER;
92 l_new_cycle_count_quantity NUMBER:=0;
93 l_old_cycle_count_quantity NUMBER:=0;
94
95
96
97 BEGIN
98 If is_debug Then
99 print_debug('Inside Store_smc_bo_details','INV_PICK_RELEASE_PUB');
100 print_debug('delivery detail'||back_order_det_tbl(1).delivery_detail_id,
101 'INV_PICK_RELEASE_PUB');
102 End If;
103 x_return_status := fnd_api.g_ret_sts_success;
104
105 if back_order_det_tbl.count >0 then
106 l_delivery_detail_id :=back_order_det_tbl(1).delivery_detail_id;
107 If is_debug Then
108 print_debug('Delivery detail'||l_delivery_detail_id,
109 'INV_PICK_RELEASE_PUB');
110 End If;
111 l_new_cycle_count_quantity :=back_order_det_tbl(1).cycle_count_quantity;
112
113 end if;
114 IF all_del_det_bo_tbl.EXISTS(l_delivery_detail_id) then
115 l_old_cycle_count_quantity :=
116 all_del_det_bo_tbl(l_delivery_detail_id).cycle_count_quantity;
117 all_del_det_bo_tbl(l_delivery_detail_id).cycle_count_quantity :=
118 l_old_cycle_count_quantity+l_new_cycle_count_quantity;
119 ELSE
120 all_del_det_bo_tbl(l_delivery_detail_id) :=back_order_det_tbl(1);
121 END IF;
122
123 If is_debug Then
124 print_debug('New Cycle count Qty: '
125 ||all_del_det_bo_tbl(l_delivery_detail_id).cycle_count_quantity
126 ,'INV_PICK_RELEASE_PUB');
127 End If;
128 EXCEPTION
129 WHEN FND_API.G_EXC_ERROR THEN
130 --
131 x_return_status := FND_API.G_RET_STS_ERROR;
132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
133 if is_debug then
134 print_debug(SQLERRM,'INV_PICK_RELEASE_PUB');
135 end if;
136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
137 WHEN OTHERS THEN
138 if is_debug then
139 print_debug(SQLERRM,'INV_PICK_RELEASE_PUB');
140 end if;
141 ROLLBACK TO Pick_Release_PUB;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 END;
144
145 --2509322:Earlier when ever a component in a model is short we backorder all
146 -- components to the difference of new model quantity and original
147 -- and repick release all.But if many components are short then we end
148 -- up splitting many delivery details.
149 -- Now avoided multiple splits by storing back order details and do only once.
150
151 PROCEDURE Backorder_SMC_DETAILS(x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_data OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER
154 ) IS
155 l_api_name VARCHAR2(100):='Backorder_SMC_DETAILS';
156 l_delivery_detail_id NUMBER;
157 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
158 l_del_index INTEGER;
159
160 BEGIN
161 x_return_status := fnd_api.g_ret_sts_success;
162
163 IF all_del_det_bo_tbl.COUNT >0 THEN
164 l_del_index :=all_del_det_bo_tbl.FIRST ;
165 LOOP
166 l_shipping_attr(1).source_header_id :=
167 all_del_det_bo_tbl(l_del_index).source_header_id;
168 l_shipping_attr(1).source_line_id :=
169 all_del_det_bo_tbl(l_del_index).source_line_id;
170 l_shipping_attr(1).ship_from_org_id :=
171 all_del_det_bo_tbl(l_del_index).ship_from_org_id;
172 l_shipping_attr(1).released_status :=
173 all_del_det_bo_tbl(l_del_index).released_status;
174 l_shipping_attr(1).delivery_detail_id :=
175 all_del_det_bo_tbl(l_del_index).delivery_detail_id;
176 l_shipping_attr(1).action_flag := 'B';
177 l_shipping_attr(1).cycle_count_quantity :=
178 all_del_det_bo_tbl(l_del_index).cycle_count_quantity;
179
180 l_shipping_attr(1).subinventory :=
181 all_del_det_bo_tbl(l_del_index).subinventory ;
182 l_shipping_attr(1).locator_id :=all_del_det_bo_tbl(l_del_index).locator_id;
183
184 if is_debug then
185 print_debug('Backordering SMC','INV_PICK_RELEASE_PUB');
186 print_debug('Delivery detail'|| l_shipping_attr(1).delivery_detail_id,
187 'INV_PICK_RELEASE_PUB');
188 end if;
189
190 WSH_INTERFACE.Update_Shipping_Attributes
191 (p_source_code => 'INV',
192 p_changed_attributes => l_shipping_attr,
193 x_return_status => x_return_status
194 );
195
196 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
197 if is_debug then
198 print_debug('return error from update shipping attributes',
199 'Inv_Pick_Release_Pub.Pick_Release');
200 end if;
201 raise FND_API.G_EXC_ERROR;
202 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
203 if is_debug then
204 print_debug('return error from update shipping attributes',
205 'Inv_Pick_Release_Pub.Pick_Release');
206 end if;
207 raise FND_API.G_EXC_UNEXPECTED_ERROR;
208 end if;
209
210 l_shipping_attr.DELETE;
211 EXIT WHEN l_del_index =all_del_det_bo_tbl.LAST;
212 l_del_index :=all_del_det_bo_tbl.NEXT(l_del_index);
213
214 END LOOP;
215 END IF;
216 all_del_det_bo_tbl.DELETE;
217 x_return_status :=fnd_api.g_ret_sts_success;
218 EXCEPTION
219 WHEN FND_API.G_EXC_ERROR THEN
220 --
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 --
223 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
224 , p_data => x_msg_data);
225 --
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 --
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 --
230 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
231 , p_data => x_msg_data);
232 --
233 WHEN OTHERS THEN
234 ROLLBACK TO Pick_Release_PUB;
235 --
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 --
238 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
239 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
240 END IF;
241 --
242 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
243 , p_data => x_msg_data);
244 END;
245
246
247 --Sort
248 -- This procedure sorts the input lines so that lines with the same
249 -- header_id are contiguous. This sort used quick sort, but quick sort
250 -- doesn't preserve the order of the records within a header. It's
251 -- necessary to preserve this order because of shipsets - lines belonging
252 -- to the same shipset must be consecutive in the table. Because
253 -- these lines will usually be mostly in order already, we can use the
254 -- InsertionSort algorithm.
255
256 procedure sort( p_trolin_tbl IN OUT NOCOPY INV_Move_Order_PUB.TROLIN_TBL_TYPE,
257 p_low_index IN NUMBER,
258 p_high_index IN NUMBER) IS
259 l_low_index NUMBER := p_low_index;
260 l_high_index NUMBER := p_high_index;
261 l_pivot_idx NUMBER;
262 l_prior_index NUMBER;
263 l_trolin_rec INV_MOVE_ORDER_PUB.TROLIN_REC_TYPE;
264 l_pivot_rec INV_MOVE_ORDER_PUB.TROLIN_REC_TYPE;
265 i NUMBER;
266 j NUMBER;
267
268 BEGIN
269
270 IF l_low_Index >= l_high_index THEN
271 RETURN;
272 END IF;
273 -- for each record in table but the first
274 i := p_trolin_tbl.NEXT(l_low_index);
275 LOOP
276 j := i;
277 l_pivot_rec := p_trolin_tbl(i);
278 -- table to left of current location (j) is already sorted.
279 -- Copy current record (pivot rec) from table.
280 -- Look at record to left (prior index). If record to
281 -- left has header greater than current record, move record to
282 -- left one place to the right (the spot that used to be
283 -- occupied by the currect record). Continue to move current
284 -- record to the left until the prior record's header id is
285 -- <= current record's header id. At that point, save pivot
286 -- rec into table at current location.
287 While j > l_low_index Loop
288 l_prior_index := p_trolin_tbl.PRIOR(j);
289 IF l_pivot_rec.header_id >= p_trolin_tbl(l_prior_index).header_id Then
290 EXIT;
291 END IF;
292 p_trolin_tbl(j) := p_trolin_tbl(l_prior_index);
293 j := l_prior_index;
294 End Loop;
295 p_trolin_tbl(j) := l_pivot_rec;
296 EXIT WHEN p_trolin_tbl.LAST = i;
297 i := p_trolin_tbl.NEXT(i);
298 END LOOP;
299 END sort;
300
301 PROCEDURE test_sort( p_trolin_tbl IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Tbl_Type) IS
302 BEGIN
303 sort(p_trolin_tbl, p_trolin_tbl.FIRST, p_trolin_tbl.LAST);
304 END test_sort;
305
306 -- the following API is added for assign pick slip numbers after cartonize
307 -- for patchset J bulk picking
308 PROCEDURE assign_pick_slip_number(
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 p_move_order_header_id IN NUMBER DEFAULT 0,
313 p_ps_mode IN VARCHAR2,
314 p_grouping_rule_id IN NUMBER,
315 p_allow_partial_pick IN VARCHAR2) IS
316
317
318 l_pick_slip_mode VARCHAR2(1); -- The print pick slip mode (immediate or deferred) that should be used
319 l_pick_slip_number NUMBER; -- The pick slip number to put on the Move Order Line Details for a Line.
320 l_ready_to_print VARCHAR2(1); -- The flag for whether we need to commit and print after receiving
321 -- the current pick slip number.
322 l_api_return_status VARCHAR2(1); -- The return status of APIs called within the Process Line API.
323 l_api_error_code NUMBER; -- The error code of APIs called within the Process Line API.
324 l_api_error_msg VARCHAR2(100); -- The error message returned by certain APIs called within Process_Line
325 l_count NUMBER;
326 l_message VARCHAR2(255);
327 l_report_set_id NUMBER;
328 l_request_number VARCHAR2(80);
329 l_call_mode VARCHAR2(1); --bug 1968032 will not commit if not null when called from SE.
330 l_grouping_rule_id NUMBER;
331 l_get_header_rule NUMBER; -- 1 (yes) if the grouping rule ID was
332 -- not passed in and the headers have
333 -- different grouping rules, or 2 (no)
334 -- otherwise.
335 l_mso_header_id NUMBER;
336 l_organization_id NUMBER;
337 l_api_name CONSTANT VARCHAR2(30) := 'ASSIGN_PICK_SLIP_NUMBER';
338 CURSOR l_mold_crs IS
339 SELECT mmtt.transaction_temp_id
340 , mmtt.subinventory_code
341 , mmtt.locator_id
342 , mmtt.transfer_to_location
343 , mmtt.organization_id
344 , wdd.oe_header_id
345 , wdd.oe_line_id
346 , wdd.customer_id
347 , wdd.freight_code
348 , wdd.ship_to_location
349 , wdd.shipment_priority_code
350 , wdd.trip_stop_id
351 , wdd.shipping_delivery_id
352 , mol.ship_set_id
353 , mol.ship_model_id
354 , mmtt.parent_line_id
355 , mmtt.transfer_subinventory
356 , mmtt.project_id
357 , mmtt.task_id
358 , mmtt.inventory_item_id
359 , mmtt.revision
360 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,wsh_inv_delivery_details_v wdd
361 WHERE mmtt.move_order_line_id = mol.line_id
362 AND mol.header_id = p_move_order_header_id
363 AND wdd.move_order_line_id = mol.line_id
364 AND mmtt.pick_slip_number IS NULL;
365
366 -- the following cursor will be used when calling from concurrent program
367 CURSOR l_mold_crs_con IS
368 SELECT wct.transaction_temp_id
369 FROM wms_cartonization_temp wct
370 WHERE wct.parent_line_id = wct.transaction_temp_id; -- only parent lines
371
372 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
373
374 BEGIN
375 IF (l_debug = 1) THEN
376 print_debug('get pick slip number for move order header '||p_move_order_header_id,
377 'Inv_Pick_Release_PVT.assign_pick_slip_number');
378 print_debug('p_grouping_rule_id = '||p_grouping_rule_id,'Inv_Pick_Release_PVT.assign_pick_slip_number');
379 END IF;
380 SAVEPOINT assign_pick_slip;
381
382 IF p_move_order_header_id = -1 THEN
383 IF (l_debug = 1) THEN
384 print_debug('calling from concurrent program ',
385 'Inv_Pick_Release_PVT.assign_pick_slip_number');
386 END IF;
387 For mmtt_line in l_mold_crs_con LOOP
388 UPDATE mtl_material_transactions_temp
389 SET pick_slip_number = wsh_pick_slip_numbers_s.nextval
390 WHERE transaction_temp_id = mmtt_line.transaction_temp_id;
391 END LOOP;
392 ELSE
393
394 -- The Move Order Lines may need to have their grouping rule ID defaulted
395 -- from the header. This is only necessary if the Grouping Rule ID was not
396 -- passed in as a parameter.
397 IF p_grouping_rule_id <> FND_API.G_MISS_NUM THEN
398 l_grouping_rule_id := p_grouping_rule_id;
399 l_get_header_rule := 2;
400 ELSE
401 l_get_header_rule := 1;
402 END IF;
403
404 IF (l_debug = 1) THEN
405 print_debug('l_get_header_rule = '||l_get_header_rule,'Inv_Pick_Release_PVT.assign_pick_slip_number');
406 END IF;
407
408 IF l_get_header_rule = 1 THEN
409 BEGIN
410 SELECT grouping_rule_id,organization_id
411 INTO l_grouping_rule_id,l_organization_id
412 FROM mtl_txn_request_headers
413 WHERE header_id = p_move_order_header_id;
414 EXCEPTION
415 WHEN no_data_found THEN
416 ROLLBACK TO Assign_Pick_slip;
417 FND_MESSAGE.SET_NAME('INV','INV_NO_HEADER_FOUND');
418 FND_MESSAGE.SET_TOKEN('MO_LINE_ID','');
419 FND_MSG_PUB.Add;
420 RAISE fnd_api.g_exc_unexpected_error;
421 END;
422
423 IF (l_debug = 1) THEN
424 print_debug('l_grouping_rule_id = '||l_grouping_rule_id||',l_organization_id = '||l_organization_id,
425 'Inv_Pick_Release_PVT.assign_pick_slip_number');
426 END IF;
427
428 -- If the header did not have a grouping rule ID, retrieve it from
429 -- the organization-level default.
430 IF l_grouping_rule_id IS NULL THEN
431 BEGIN
432 SELECT pick_grouping_rule_id
433 INTO l_grouping_rule_id
434 FROM wsh_shipping_parameters -- Bug 16309114 changed from wsh_parameters
435 WHERE organization_id = l_organization_id;
436 EXCEPTION
437 WHEN no_data_found THEN
438 ROLLBACK TO Assign_pick_slip;
439 FND_MESSAGE.SET_NAME('INV','INV-NO ORG INFORMATION');
440 FND_MSG_PUB.Add;
441 RAISE fnd_api.g_exc_unexpected_error;
442 END;
443 END IF; -- get header rule
444 END IF; -- return status
445
446 For mmtt_line in l_mold_crs LOOP
447
448 IF mmtt_line.parent_line_id is not null THEN -- parent line
449 -- assign a seperate pick slip number for parent task and call WMS's pick slip
450 -- report to print out
451 UPDATE mtl_material_transactions_temp
452 SET pick_slip_number = wsh_pick_slip_numbers_s.nextval
453 WHERE transaction_temp_id = mmtt_line.parent_line_id;
454 IF ( p_ps_mode <> 'I' ) THEN
455 WSH_INV_INTEGRATION_GRP.FIND_PRINTER
456 ( p_subinventory => mmtt_line.subinventory_code
457 , p_organization_id => mmtt_line.organization_id
458 , x_error_message => l_api_error_msg
459 , x_api_Status => l_api_return_status
460 ) ;
461
462 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
463 IF (l_debug = 1) THEN
464 print_debug('return error from WSH_INV_INTEGRATION.find_printer',
465 'Inv_Pick_Release_Pvt.Process_Line');
466 END IF;
467 RAISE fnd_api.g_exc_unexpected_error;
468 END IF;
469
470 END IF ;
471 IF p_ps_mode = 'I' and
472 p_allow_partial_pick = fnd_api.g_true THEN
473 COMMIT WORK;
474
475 BEGIN
476
477 /*Added for Bug # 6144354 */
478 SELECT request_number
479 INTO l_request_number
480 FROM mtl_txn_request_headers
481 WHERE header_id = p_move_order_header_id;
482 --AND organization_id = mmtt_line.organization_id; --bug 8829363
483
484 IF (l_debug = 1) THEN
485 print_debug('organization_id '||mmtt_line.organization_id,'Inv_Pick_Release_PVT.assign_pick_slip_number');
486 END IF;
487 /*End of modifications for Bug # 6144354 */
488
489 SELECT document_set_id
490 INTO l_report_set_id
491 FROM wsh_picking_batches
492 WHERE NAME = l_request_number;
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 x_return_status := fnd_api.g_ret_sts_error;
496 RAISE fnd_api.g_exc_error;
497 END;
498
499 wsh_pr_pick_slip_number.print_pick_slip(
500 p_pick_slip_number => l_pick_slip_number
501 , p_report_set_id => l_report_set_id
502 , p_organization_id => mmtt_line.organization_id
503 , x_api_status => l_api_return_status
504 , x_error_message => l_api_error_msg
505 ); -- don't need to call WMS new pick slip report, call shipping's api and add new wms report to the
506 -- proper document set
507
508 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
509 ROLLBACK TO assign_pick_slip;
510 fnd_message.set_name('INV', 'INV_PRINT_PICK_SLIP_FAILED');
511 fnd_message.set_token('PICK_SLIP_NUM', TO_CHAR(l_pick_slip_number));
512 fnd_msg_pub.ADD;
513 RAISE fnd_api.g_exc_unexpected_error;
514 END IF;
515 END IF;
516
517 ELSE
518 l_call_mode := NULL;
519 -- Bug 2666620: Inline branching to call either WSH or INV get_pick_slip_number
520 inv_pick_release_pvt.get_pick_slip_number(
521 p_ps_mode => p_ps_mode
522 , p_pick_grouping_rule_id => l_grouping_rule_id
523 , p_org_id => mmtt_line.organization_id
524 , p_header_id => mmtt_line.oe_header_id
525 , p_customer_id => mmtt_line.customer_id
526 , p_ship_method_code => mmtt_line.freight_code
527 , p_ship_to_loc_id => mmtt_line.ship_to_location
528 , p_shipment_priority => mmtt_line.shipment_priority_code
529 , p_subinventory => mmtt_line.subinventory_code
530 , p_trip_stop_id => mmtt_line.trip_stop_id
531 , p_delivery_id => mmtt_line.shipping_delivery_id
532 , x_pick_slip_number => l_pick_slip_number
533 , x_ready_to_print => l_ready_to_print
534 , x_api_status => l_api_return_status
535 , x_error_message => l_api_error_msg
536 , x_call_mode => l_call_mode
537 , p_dest_subinv => mmtt_line.transfer_subinventory
538 , p_dest_locator_id => mmtt_line.transfer_to_location
539 , p_project_id => mmtt_line.project_id
540 , p_task_id => mmtt_line.task_id
541 , p_inventory_item_id => mmtt_line.inventory_item_id
542 , p_locator_id => mmtt_line.locator_id
543 , p_revision => mmtt_line.revision
544 );
545 IF (l_debug = 1) THEN
546 print_debug('l_call_mode'|| l_call_mode, 'Inv_Pick_Release_PVT.Process_Line');
547 END IF;
548
549 IF l_api_return_status <> fnd_api.g_ret_sts_success
550 OR l_pick_slip_number = -1 THEN
551 ROLLBACK TO assign_pick_slip;
552 fnd_message.set_name('INV', 'INV_NO_PICK_SLIP_NUMBER');
553 fnd_msg_pub.ADD;
554 RAISE fnd_api.g_exc_unexpected_error;
555 END IF;
556
557 IF ( p_ps_mode <> 'I' ) THEN
558 WSH_INV_INTEGRATION_GRP.FIND_PRINTER
559 ( p_subinventory => mmtt_line.subinventory_code
560 , p_organization_id => mmtt_line.organization_id
561 , x_error_message => l_api_error_msg
562 , x_api_Status => l_api_return_status
563 ) ;
564
565 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
566 IF (l_debug = 1) THEN
567 print_debug('return error from WSH_INV_INTEGRATION.find_printer',
568 'Inv_Pick_Release_Pvt.Process_Line');
569 END IF;
570 RAISE fnd_api.g_exc_unexpected_error;
571 END IF;
572
573 END IF ;
574 l_mso_header_id := inv_salesorder.get_salesorder_for_oeheader(mmtt_line.oe_header_id);
575 -- Assign the pick slip number to the record in MTL_MATERIAL_TRANSACTIONS_TEMP
576 UPDATE mtl_material_transactions_temp
577 SET pick_slip_number = l_pick_slip_number
578 , transaction_source_id = l_mso_header_id
579 , trx_source_line_id = mmtt_line.oe_line_id
580 , demand_source_header_id = l_mso_header_id
581 , demand_source_line = mmtt_line.oe_line_id
582 WHERE transaction_temp_id = mmtt_line.transaction_temp_id;
583
584 -- If the pick slip is ready to be printed (and partial
585 -- picking is allowed) commit
586 -- and print at this point.
587 -- Bug 1663376 - Don't Commit if Ship_set_Id is not null,
588 -- since we need to be able to rollback
589 IF l_ready_to_print = fnd_api.g_true
590 AND p_allow_partial_pick = fnd_api.g_true
591 AND mmtt_line.ship_set_id IS NULL
592 AND mmtt_line.ship_model_id IS NULL
593 AND l_call_mode IS NULL THEN
594 COMMIT WORK;
595
596 BEGIN
597 SELECT request_number
598 INTO l_request_number
599 FROM mtl_txn_request_headers
600 WHERE header_id = p_move_order_header_id;
601 --AND organization_id = l_organization_id; Bug 8829363
602
603 SELECT document_set_id
604 INTO l_report_set_id
605 FROM wsh_picking_batches
606 WHERE NAME = l_request_number;
607 EXCEPTION
608 WHEN NO_DATA_FOUND THEN
609 x_return_status := fnd_api.g_ret_sts_error;
610 RAISE fnd_api.g_exc_error;
611 END;
612
613 wsh_pr_pick_slip_number.print_pick_slip(
614 p_pick_slip_number => l_pick_slip_number
615 , p_report_set_id => l_report_set_id
616 , p_organization_id => mmtt_line.organization_id
617 , x_api_status => l_api_return_status
618 , x_error_message => l_api_error_msg
619 );
620
621 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
622 ROLLBACK TO process_line_pvt;
623 fnd_message.set_name('INV', 'INV_PRINT_PICK_SLIP_FAILED');
624 fnd_message.set_token('PICK_SLIP_NUM', TO_CHAR(l_pick_slip_number));
625 fnd_msg_pub.ADD;
626 RAISE fnd_api.g_exc_unexpected_error;
627 END IF;
628 END IF;
629 END IF;
630 END LOOP;
631 END IF; -- p_move_ordeR_header <> -1
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 ROLLBACK TO Assign_pick_slip;
635 --
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 --
638 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
639 , p_data => x_msg_data);
640 --
641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
642 ROLLBACK TO Assign_pick_slip;
643 --
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 --
646 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
647 , p_data => x_msg_data);
648 --
649 WHEN OTHERS THEN
650 ROLLBACK TO Assign_pick_slip;
651 --
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 --
654 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
655 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
656 END IF;
657 --
658 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
659 , p_data => x_msg_data);
660 END assign_pick_slip_number;
661
662
663 -- Bug# 4258360
664 -- Pick_Release API is overloaded for R12 changes related to the Planned Crossdocking project.
665 PROCEDURE Pick_Release
666 (
667 p_api_version IN NUMBER
668 ,p_init_msg_list IN VARCHAR2
669 ,p_commit IN VARCHAR2
670 ,x_return_status OUT NOCOPY VARCHAR2
671 ,x_msg_count OUT NOCOPY NUMBER
672 ,x_msg_data OUT NOCOPY VARCHAR2
673 ,p_mo_line_tbl IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
674 ,p_auto_pick_confirm IN NUMBER
675 ,p_grouping_rule_id IN NUMBER
676 ,p_allow_partial_pick IN VARCHAR2
677 ,x_pick_release_status OUT NOCOPY INV_Release_Status_Tbl_Type
678 ,p_plan_tasks IN BOOLEAN
679 ,p_skip_cartonization IN BOOLEAN := FALSE
680 ,p_mo_transact_date IN DATE
681 )
682 IS
683 l_wsh_release_table WSH_PR_CRITERIA.relRecTabTyp;
684 l_trolin_delivery_ids WSH_UTIL_CORE.Id_Tab_Type;
685 l_del_detail_id WSH_PICK_LIST.DelDetTabTyp;
686
687 BEGIN
688 INV_Pick_Release_Pub.Pick_Release
689 (
690 p_api_version => p_api_version
691 ,p_init_msg_list => p_init_msg_list
692 ,p_commit => p_commit
693 ,x_return_status => x_return_status
694 ,x_msg_count => x_msg_count
695 ,x_msg_data => x_msg_data
696 ,p_mo_line_tbl => p_mo_line_tbl
697 ,p_auto_pick_confirm => p_auto_pick_confirm
698 ,p_grouping_rule_id => p_grouping_rule_id
699 ,p_allow_partial_pick => p_allow_partial_pick
700 ,x_pick_release_status => x_pick_release_status
701 ,p_plan_tasks => p_plan_tasks
702 ,p_skip_cartonization => p_skip_cartonization
703 ,p_wsh_release_table => l_wsh_release_table
704 ,p_trolin_delivery_ids => l_trolin_delivery_ids
705 ,p_del_detail_id => l_del_detail_id
706 ,p_mo_transact_date => p_mo_transact_date
707 ,p_dynamic_replenishment => NULL --Added R12.1 Replenishment Proj 6710368
708 );
709 END Pick_Release;
710
711
712 -- Bug# 4258360
713 -- Pick_Release API is overloaded for R12 changes related to the Planned Crossdocking project.
714 -- Three new IN OUT parameters are added.
715 PROCEDURE Pick_Release
716 (
717 p_api_version IN NUMBER
718 ,p_init_msg_list IN VARCHAR2
719 ,p_commit IN VARCHAR2
720 ,x_return_status OUT NOCOPY VARCHAR2
721 ,x_msg_count OUT NOCOPY NUMBER
722 ,x_msg_data OUT NOCOPY VARCHAR2
723 ,p_mo_line_tbl IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
724 ,p_auto_pick_confirm IN NUMBER
725 ,p_grouping_rule_id IN NUMBER
726 ,p_allow_partial_pick IN VARCHAR2
727 ,x_pick_release_status OUT NOCOPY INV_Release_Status_Tbl_Type
728 ,p_plan_tasks IN BOOLEAN
729 ,p_skip_cartonization IN BOOLEAN
730 ,p_wsh_release_table IN OUT NOCOPY WSH_PR_CRITERIA.relRecTabTyp
731 ,p_trolin_delivery_ids IN OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type
732 ,p_del_detail_id IN OUT NOCOPY WSH_PICK_LIST.DelDetTabTyp
733 ,p_mo_transact_date IN DATE
734 ,p_dynamic_replenishment IN VARCHAR2 --Added R12.1 Replenishment Proj 6710368
735 ) IS
736 l_api_version CONSTANT NUMBER := 1.0;
737 l_api_name CONSTANT VARCHAR2(30) := 'Pick_Release';
738
739 l_mo_line_count NUMBER;
740 -- The number of move order lines to
741 -- be pick released.
742 l_line_index NUMBER;
743 -- The index of the line in the table
744 -- being processed
745 l_mo_line INV_Move_Order_PUB.TROLIN_REC_TYPE;
746 -- Temporary record to hold information
747 -- on the record being processed.
748 l_organization_id NUMBER;
749 -- The organization ID to use (based
750 -- on the move order lines passed in).
751 l_print_mode VARCHAR2(1);
752 -- The pick slip printing mode to use
753 -- (I = immediate, E = deferred)
754 l_mo_type NUMBER; -- The type of the move order (should
755 -- be Pick Wave - 3)
756 l_mo_number VARCHAR2(30);
757 -- The move order number
758 l_grouping_rule_id NUMBER; -- The grouping rule ID to use (which
759 -- may come from the parameter passed
760 -- in or the default in the header).
761 l_get_header_rule NUMBER; -- 1 (yes) if the grouping rule ID was
762 -- not passed in and the headers have
763 -- different grouping rules, or 2 (no)
764 -- otherwise.
765 l_grouping_rules_differ NUMBER; -- Flag which tells whether the move
766 -- order lines have differing grouping
767 -- rule IDS in their headers.
768 l_auto_pick_confirm NUMBER; -- Whether or not to call the pick
769 -- confirm process automatically.
770 -- This may come from the parameter
771 -- passed in or the org-level
772 -- parameter.
773 l_api_return_status VARCHAR2(1);
774 -- The return status of APIs called
775 -- within the Pick Release API.
776 l_processed_row_count NUMBER := 0;
777 -- The number of rows which have been
778 -- processed.
779 l_detail_rec_count NUMBER := 0;
780 l_reservation_exists NUMBER := 0; --BUG13604664
781
782 l_mo_line_tbl INV_Move_Order_Pub.Trolin_Tbl_Type := p_mo_line_tbl;
783 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
784 l_smc_backorder_det_tbl WSH_INTERFACE.ChangedAttributeTabType;
785 l_shipset_smc_backorder_rec WSH_INTEGRATION.BackorderRecType;
786 l_action_flag VARCHAR2(1);
787 l_quantity NUMBER;
788 -- HW INVCONV Added Qty2 variables
789 l_quantity2 NUMBER;
790 l_transaction_quantity2 NUMBER;
791 l_transaction_quantity NUMBER;
792 l_delivery_detail_id NUMBER;
793 l_source_header_id NUMBER;
794 l_source_line_id NUMBER;
795 l_released_status VARCHAR2(1);
796 l_line_status Number;
797 -- used for processing ship sets
798 l_cur_ship_set_id NUMBER := NULL;
799 l_set_index NUMBER;
800 l_start_index NUMBER;
801 l_set_process NUMBER;
802 l_start_process NUMBER;
803 -- used for processing ship model complete
804 l_model_reloop BOOLEAN := FALSE;
805 l_cur_ship_model_id NUMBER := NULL;
806 l_cur_txn_source_line_id NUMBER;
807 l_cur_txn_source_qty NUMBER;
808 -- HW INVCONV -Added Qty2
809 l_cur_txn_source_qty2 NUMBER;
810 l_cur_txn_source_req_qty NUMBER;
811 l_txn_source_line_uom VARCHAR2(3);
812 l_new_model_quantity NUMBER;
813 l_set_txn_source_line_id NUMBER := NULL;
814 l_set_txn_source_req_qty NUMBER;
815 l_set_txn_source_uom VARCHAR2(3);
816 l_set_new_req_qty NUMBER;
817 l_new_line_quantity NUMBER;
818 l_tree_id NUMBER;
819 l_revision_control_code NUMBER;
820 l_lot_control_code NUMBER;
821 l_revision_controlled BOOLEAN;
822 l_lot_controlled BOOLEAN;
823 l_req_msg VARCHAR2(255);
824 g_pjm_unit_eff_enabled VARCHAR2(1) :=NULL;
825 l_demand_source_type NUMBER;
826 l_mso_header_id NUMBER;
827 l_oe_header_id NUMBER;
828 l_reservable_type NUMBER := NULL;
829
830 l_last_rec NUMBER;
831 l_item_index NUMBER;
832 l_qtree_line_index NUMBER;
833 l_wms_installed BOOLEAN;
834 l_multiple_headers BOOLEAN := FALSE;
835 l_first_header_id NUMBER := NULL;
836 l_quantity_delivered NUMBER;
837 -- HW INVCONV -Added Qty2
838 l_quantity2_delivered NUMBER;
839 l_backup_id NUMBER;
840 l_current_header_id NUMBER := NULL;
841 l_return_value BOOLEAN := TRUE;
842 l_do_cartonization number := NVL(FND_PROFILE.VALUE('WMS_ASSIGN_TASK_TYPE'),1); --added for Bug3237702
843
844
845 TYPE quantity_tree_tbl_type is TABLE OF NUMBER
846 INDEX BY BINARY_INTEGER;
847
848
849 --there will be one record in this table for every item in the MO batch.
850 --if the item is unit effective,
851 -- first line rec will hold the txn_source_line_id of first MO line
852 -- for this item; last_line_rec will hold the txn_source_line_id of
853 -- the last MO line for this item in the batch;
854 --This table is indexed based on the item_id
855 TYPE qtree_item_rec_type IS RECORD (
856 tree_id NUMBER
857 ,unit_effective VARCHAR2(1)
858 ,first_line_rec NUMBER
859 ,last_line_rec NUMBER
860 );
861
862 --if item is unit effective controlled, there will be one record
863 -- in this table for each separate sales order line in this batch;
864 --transaction type id and move order line id are needed to get the
865 -- information on demand to build the correct quantity tree;
866 --next_line_rec contains the txn_source_line_id of the next MO line
867 -- for this item;
868 --This table is indexed by txn_source_line_id
869 TYPE qtree_line_rec_type IS RECORD (
870 tree_id NUMBER
871 ,move_order_line_id NUMBER
872 ,transaction_type_id NUMBER
873 ,next_line_rec NUMBER
874 );
875
876 TYPE qtree_item_tbl_type IS TABLE OF qtree_item_rec_type
877 INDEX BY BINARY_INTEGER;
878
879 TYPE qtree_line_tbl_type IS TABLE OF qtree_line_rec_type
880 INDEX BY BINARY_INTEGER;
881
882 l_qtree_item_tbl qtree_item_tbl_type;
883 l_qtree_line_tbl qtree_line_tbl_type;
884
885
886 --This table is used to make sure that restore tree and backup tree
887 -- never get called more than once for a given tree within a shipset.
888 -- These two procedures can be a little slow, it's best to avoid calling
889 -- more than necessary. This table will be indexed by tree_id; if
890 -- a record exists in this table for a given tree id, then the tree
891 -- is currently backed up and has not been restored.
892 -- This table is deleted at the beginning of each shipset/model.
893 l_qtree_backup_tbl quantity_tree_tbl_type;
894
895
896 TYPE uom_tbl_type IS TABLE OF VARCHAR2(3)
897 INDEX BY BINARY_INTEGER;
898
899 l_primary_uom_tbl uom_tbl_type;
900
901 l_debug NUMBER;
902
903 -- Bug# 4258360: Index pointer table so we can navigate through p_wsh_release_table
904 -- easily if needed based on the allocation mode for the pick release batch.
905 -- This should only be used for allocation mode = N (Prioritize Inventory)
906 TYPE wdd_index_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
907 l_wdd_index_tbl wdd_index_tbl;
908
909 -- Index used for adding crossdocked WDD lines into the input tables,
910 -- p_trolin_delivery_ids and p_del_detail_id for allocation mode of X (Prioritize Crossdock).
911 -- This is needed to enter the crossdocked WDD lines from p_wsh_release_table into the
912 -- delivery tables so a delivery can be created for them if needed.
913 -- This will also be used for other crossdock related changes.
914 -- l_xdock_next_index is needed when removing backordered WDD lines from the
915 -- delivery tables.
916 l_xdock_index PLS_INTEGER;
917 l_xdock_next_index PLS_INTEGER;
918
919 -- Table storing the delivery detail ID's for backordered lines. These records
920 -- should be removed from the input tables, p_trolin_delivery_ids and p_del_detail_id
921 -- for allocation modes of I (Inventory Only) and X (Prioritize Crossdock).
922 -- This is so deliveries are not autocreated for them later on by Shipping.
923 -- UPDATE: This is not used anymore due to change in what Shipping passes for the
924 -- delivery tables.
925 TYPE backordered_wdd_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
926 l_backordered_wdd_tbl backordered_wdd_tbl;
927
928 -- Variables used to call the Shipping API to update a WDD line during partial or zero
929 -- allocation for allocation mode of N (Prioritize Inventory). This will be used
930 -- primarily to update the released_status and to null out the move_order_line_id.
931 l_detail_info_tab WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
932 l_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
933 l_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
934
935 -- Variables used to call the Shipping API to split a WDD line during partial
936 -- allocation for allocation mode of N (Prioritize Inventory).
937 l_detail_id_tab WSH_UTIL_CORE.id_tab_type;
938 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
939 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
940 l_split_wdd_rel_rec WSH_PR_CRITERIA.relRecTyp;
941 l_split_delivery_detail_id NUMBER;
942
943 -- Variable storing the allocation method for the current picking batch
944 l_allocation_method VARCHAR2(1);
945
946 -- Variable used to store the lower tolerance for the current move order line
947 l_lower_tolerance NUMBER;
948
949 -- Bug 4349602: save all MOL IDs in current batch
950 TYPE l_molid_tbltyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
951 l_mol_id_tbl l_molid_tbltyp;
952 l_mol_id_index NUMBER;
953 l_atr_org NUMBER; -- r12 replenishment project
954
955 --Bug 6696594
956 l_transaction_id INV_LABEL.transaction_id_rec_type;
957 l_counter NUMBER := 1 ;
958 honor_case_pick_count NUMBER := 0;
959 honor_case_pick VARCHAR2(1) := 'Y';
960 l_label_status VARCHAR2(500);
961 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
962 v_transaction_id INV_LABEL.transaction_id_rec_type;
963 --Bug 6696594
964
965 --MUOM Fulfillment Project
966 l_fulfill_base varchar2(1):='P';
967 l_lower_tolerance2 NUMBER;
968
969 -- Bug 9130704
970 -- Cursor to fetch temp IDs for case pick labels
971 CURSOR c_wpr_casepick_labels (p_mo_hdr_id NUMBER) IS
972 -- Non-bulk tasks
973 SELECT mmtt.transaction_temp_id
974 FROM mtl_material_transactions_temp mmtt
975 WHERE mmtt.move_order_header_id = p_mo_hdr_id
976 AND mmtt.parent_line_id IS NULL
977 AND EXISTS
978 ( SELECT 'x'
979 FROM wms_user_task_type_attributes wutta
980 WHERE wutta.organization_id = mmtt.organization_id
981 AND wutta.user_task_type_id = mmtt.standard_operation_id
982 AND wutta.honor_case_pick_flag = 'Y'
983 )
984 UNION ALL
985 -- Bulk pick parent tasks
986 SELECT mmtt.transaction_temp_id
987 FROM mtl_material_transactions_temp mmtt
988 WHERE mmtt.transaction_temp_id IN
989 ( SELECT DISTINCT mmtt2.parent_line_id
990 FROM mtl_material_transactions_temp mmtt2
991 WHERE mmtt2.move_order_header_id = p_mo_hdr_id
992 AND mmtt2.parent_line_id IS NOT NULL
993 AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
994 )
995 AND EXISTS
996 ( SELECT 'x'
997 FROM wms_user_task_type_attributes wutta
998 WHERE wutta.organization_id = mmtt.organization_id
999 AND wutta.user_task_type_id = mmtt.standard_operation_id
1000 AND wutta.honor_case_pick_flag = 'Y'
1001 );
1002
1003
1004 BEGIN
1005 -- because the debug profile rarely changes, only check it once per
1006 -- session, instead of once per batch
1007 IF is_debug IS NULL THEN
1008 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1009 if l_debug = 1 then
1010 is_debug := TRUE;
1011 else
1012 is_debug := FALSE;
1013 end if;
1014 END IF;
1015
1016 -- Set savepoint for this API
1017 If is_debug then
1018 print_debug('Inside Pick_Release', 'INV_Pick_Release_Pub.Pick_Release');
1019 print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment, 'INV_Pick_Release_Pub.Pick_Release');
1020 End If;
1021
1022 l_return_value := inv_cache.set_pick_release(TRUE); --Added for bug3237702
1023 inv_log_util.g_maintain_log_profile := TRUE; -- Bug 5558315 - duplication so no dependency btw inv_cache and inv_log
1024
1025 SAVEPOINT Pick_Release_PUB;
1026
1027 -- Standard Call to check for call compatibility
1028 IF NOT fnd_api.Compatible_API_Call(l_api_version , p_api_version , l_api_name , G_PKG_NAME) THEN
1029 If is_debug then
1030 print_debug('Fnd_APi not compatible','INV_Pick_Release_Pub.Pick_Release');
1031 End If;
1032 RAISE fnd_api.g_exc_unexpected_error;
1033 END IF;
1034
1035 -- Initialize message list if p_init_msg_list is set to true
1036 IF fnd_api.to_Boolean(p_init_msg_list) THEN
1037 fnd_msg_pub.initialize;
1038 END IF;
1039
1040 -- Initialize API return status to success
1041 x_return_status := fnd_api.g_ret_sts_success;
1042
1043
1044 -- Validate parameters
1045
1046 -- First determine whether the table of move order lines in p_mo_line_tbl has
1047 -- any records
1048 l_mo_line_count := p_mo_line_tbl.COUNT;
1049 IF l_mo_line_count = 0 THEN
1050 If is_debug then
1051 print_debug('No Lines to pick', 'INV_Pick_Release_Pub.Pick_Release');
1052 End If;
1053
1054 ROLLBACK TO Pick_Release_PUB;
1055 FND_MESSAGE.SET_NAME('INV','INV_NO_LINES_TO_PICK');
1056 FND_MSG_PUB.Add;
1057 RAISE fnd_api.g_exc_unexpected_error;
1058 END IF;
1059
1060 -- Set move order transaction date if passed in as not NULL
1061 IF p_mo_transact_date <> fnd_api.g_miss_date THEN
1062 inv_cache.mo_transaction_date := p_mo_transact_date;
1063 END IF;
1064
1065 -- Validate that all move order lines are from the same org, that all lines
1066 -- have a status of pre-approved(7) or approved(3),and that all of the move
1067 -- order lines are of type Pick Wave (3)
1068 l_line_index := l_mo_line_tbl.FIRST;
1069 l_mol_id_index := 1;
1070 l_organization_id := l_mo_line_tbl(l_line_index).organization_id;
1071 LOOP
1072 l_mo_line := l_mo_line_tbl(l_line_index);
1073
1074 --This clause checks to see if there are multiple headers in this
1075 -- table of move orders. If there are, we need to sort these lines
1076 -- for cartonization (see call to sort() below).
1077 if l_first_header_id IS NULL THEN
1078 l_first_header_id := l_mo_line.header_id;
1079 else
1080 IF l_first_header_id <> l_mo_line.header_id THEN
1081 l_multiple_headers := TRUE;
1082 END IF;
1083 end if;
1084
1085 -- only process the valid move order, fix bug 1540709.
1086 IF (l_mo_line.return_status <> FND_API.G_RET_STS_UNEXP_ERROR and
1087 l_mo_line.return_status <> FND_API.G_RET_STS_ERROR) THEN
1088
1089 -- Verify that the lines are all for the same organization
1090 IF l_mo_line.organization_id <> l_organization_id THEN
1091 If is_debug then
1092 print_debug('Error: Trying to pick for different org','INV_Pick_Release_Pub.Pick_Release');
1093 End If;
1094
1095 ROLLBACK TO Pick_Release_PUB;
1096 FND_MESSAGE.SET_NAME('INV','INV_PICK_DIFFERENT_ORG');
1097 FND_MSG_PUB.Add;
1098 RAISE fnd_api.g_exc_unexpected_error;
1099 END IF;
1100
1101 -- Verify that the line status is approved or pre-approved
1102 IF (l_mo_line.line_status <> 3 AND l_mo_line.line_status <> 7) THEN
1103 If is_debug then
1104 print_debug('Error: Invalid Move Order Line Status','INV_Pick_Release_Pub.Pick_Release');
1105 End If;
1106
1107 ROLLBACK TO Pick_Release_PUB;
1108 FND_MESSAGE.SET_NAME('INV','INV_PICK_LINE_STATUS');
1109 FND_MSG_PUB.Add;
1110 RAISE fnd_api.g_exc_unexpected_error;
1111 END IF;
1112
1113 IF l_mo_line.header_id <> l_current_header_id OR
1114 l_current_header_id IS NULL THEN
1115 l_return_value := INV_CACHE.set_mtrh_rec(l_mo_line.header_Id);
1116 If NOT l_return_value Then
1117 If is_debug then
1118 print_debug('Error setting cache for move order header ','INV_Pick_Release_Pub.Pick_Release');
1119 End If;
1120 RAISE fnd_api.g_exc_unexpected_error;
1121 End If;
1122 l_mo_type := INV_CACHE.mtrh_rec.move_order_type;
1123 l_mo_number := INV_CACHE.mtrh_rec.request_number;
1124 l_current_header_id := l_mo_line.header_id;
1125 END IF;
1126
1127 IF l_mo_type <> 3 THEN
1128 If is_debug then
1129 print_debug('Error: Trying to release non pick wave move order','INV_Pick_Release_Pub.Pick_Release');
1130 End If;
1131
1132 ROLLBACK TO Pick_Release_PUB;
1133 FND_MESSAGE.SET_NAME('INV','INV_NON_PICK_WAVE_MO');
1134 FND_MESSAGE.SET_TOKEN('MO_NUMBER',l_mo_number);
1135 FND_MSG_PUB.Add;
1136 RAISE fnd_api.g_exc_unexpected_error;
1137 END IF;
1138 l_mol_id_tbl(l_mol_id_index) := l_mo_line.line_id;
1139 l_mol_id_index := l_mol_id_index + 1;
1140 END IF;
1141
1142 IF NOT l_qtree_item_tbl.exists(l_mo_line.inventory_item_id) THEN
1143 --determine if item is unit-effective or not, and create
1144 --record in item_tbl
1145
1146 If is_debug then
1147 print_debug('Storing rec in qtree_item_tbl', 'PICKREL');
1148 End If;
1149 --Bug2242924 For ship model Backorder was not happening correctly so
1150 -- create_tree for every line
1151 -- Bug 2363739 - call unit_effective_item instead of enabled
1152 --old: select pjm_unit_eff.enabled into g_pjm_unit_eff_enabled from dual;
1153
1154 l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective :=
1155 pjm_unit_eff.unit_effective_item(
1156 x_item_id => l_mo_line.inventory_item_id
1157 ,x_organization_id => l_organization_id);
1158
1159 --if item is unit effective control, we have to build one tree for
1160 -- every sales order line. Create record in line_tbl
1161 If l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective =
1162 'Y' Then
1163
1164 If is_debug then
1165 print_debug('Item is unit effective', 'PICKREL');
1166 End If;
1167 l_qtree_line_tbl(l_mo_line.txn_source_line_id).next_line_rec := 0;
1168 l_qtree_line_tbl(l_mo_line.txn_source_line_id).move_order_line_id :=
1169 l_mo_line.line_id;
1170 l_qtree_line_tbl(l_mo_line.txn_source_line_id).transaction_type_id :=
1171 l_mo_line.transaction_type_id;
1172 l_qtree_item_tbl(l_mo_line.inventory_item_id).first_line_rec :=
1173 l_mo_line.txn_source_line_id;
1174 l_qtree_item_tbl(l_mo_line.inventory_item_id).last_line_rec :=
1175 l_mo_line.txn_source_line_id;
1176 End If;
1177
1178 --item entry already exists, but the item is effective control;
1179 --so, we have to create another entry in line tbl if one does not
1180 -- already exist for this sales order line
1181 ELSIF l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective = 'Y'
1182 AND NOT l_qtree_line_tbl.exists(l_mo_line.txn_source_line_id) THEN
1183
1184 If is_debug then
1185 print_debug('Item is unit effective. Inserting new line rec','PICKREL');
1186 End If;
1187 --make the next record the last one for this item
1188 l_last_rec:=l_qtree_item_tbl(l_mo_line.inventory_item_id).last_line_rec;
1189 l_qtree_line_tbl(l_last_rec).next_line_rec :=
1190 l_mo_line.txn_source_line_id;
1191 l_qtree_item_tbl(l_mo_line.inventory_item_id).last_line_rec :=
1192 l_mo_line.txn_source_line_id;
1193
1194 --initialize values in new line record
1195 l_qtree_line_tbl(l_mo_line.txn_source_line_id).next_line_rec := 0;
1196 l_qtree_line_tbl(l_mo_line.txn_source_line_id).move_order_line_id :=
1197 l_mo_line.line_id;
1198 l_qtree_line_tbl(l_mo_line.txn_source_line_id).transaction_type_id :=
1199 l_mo_line.transaction_type_id;
1200
1201 --IF item is not unit controlled and already exists in item_tbl,
1202 -- or if item is unit eff controlled and line already exists in line tbl,
1203 -- no need to do anything
1204 ELSE
1205 If is_debug then
1206 print_debug('Item/line recs already exist in table','PICKREL');
1207 End If;
1208 END IF;
1209
1210 EXIT WHEN l_line_index = p_mo_line_tbl.LAST;
1211 l_line_index := p_mo_line_tbl.NEXT(l_line_index);
1212 END LOOP;
1213
1214 l_item_index := l_qtree_item_tbl.FIRST;
1215 If is_debug then
1216 print_debug('Begin item loop. First item id:' || l_item_index,
1217 'PICKREL');
1218 End If;
1219
1220 LOOP
1221 EXIT WHEN l_item_index = 0;
1222 If is_debug then
1223 print_debug('Build quantity tree for item id:' || l_item_index,'PICKREL');
1224 End If;
1225
1226 BEGIN
1227 SELECT revision_qty_control_code, lot_control_code,
1228 primary_uom_code, NVL(reservable_type,1)
1229 INTO l_revision_control_code, l_lot_control_code,
1230 l_primary_uom_tbl(l_item_index),
1231 l_reservable_type
1232 FROM mtl_system_items
1233 WHERE organization_id = l_organization_id
1234 AND inventory_item_id = l_item_index;
1235 EXCEPTION
1236 WHEN no_data_found THEN
1237 ROLLBACK TO Pick_Release_PUB;
1238 If is_debug then
1239 print_debug('No Item Info found','Inv_Pick_Release_Pub.Pick_Release');
1240 End If;
1241 RAISE fnd_api.g_exc_unexpected_error;
1242 END;
1243
1244 IF l_revision_control_code = 1 THEN
1245 l_revision_controlled := FALSE;
1246 ELSE
1247 l_revision_controlled := TRUE;
1248 END IF;
1249
1250 IF l_lot_control_code = 1 THEN
1251 l_lot_controlled := FALSE;
1252 ELSE
1253 l_lot_controlled := TRUE;
1254 END IF;
1255
1256 -- if not unit effective, build qty tree for item
1257 IF l_qtree_item_tbl(l_item_index).unit_effective = 'N' AND l_reservable_type = 1 THEN
1258 -- Because of the quantity tree rearchitechture, we
1259 -- can pass dummy values for demand source info.
1260 -- Bug 1890424 - Pass sysdate to create_tree so
1261 -- expired lots don't appear as available
1262
1263 inv_quantity_tree_pvt.create_tree
1264 (
1265 p_api_version_number => 1.0
1266 ,p_init_msg_lst => fnd_api.g_false
1267 ,x_return_status => l_api_return_status
1268 ,x_msg_count => x_msg_count
1269 ,x_msg_data => x_msg_data
1270 ,p_organization_id => l_organization_id
1271 ,p_inventory_item_id => l_item_index
1272 ,p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1273 ,p_is_revision_control => l_revision_controlled
1274 ,p_is_lot_control => l_lot_controlled
1275 ,p_is_serial_control => FALSE
1276 ,p_asset_sub_only => FALSE
1277 ,p_include_suggestion => FALSE
1278 ,p_demand_source_type_id => -99
1279 ,p_demand_source_header_id => -99
1280 ,p_demand_source_line_id => -99
1281 ,p_demand_source_delivery => NULL
1282 ,p_demand_source_name => NULL
1283 ,p_lot_expiration_date => SYSDATE
1284 ,x_tree_id => l_tree_id
1285 ,p_exclusive => inv_quantity_tree_pvt.g_exclusive
1286 ,p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
1287 );
1288
1289 If is_debug then
1290 print_debug('Tree id from Normal Create tree'||l_tree_id,'Inv_Pick_Release_PVT.Process_Line');
1291 End If;
1292
1293 l_qtree_item_tbl(l_item_index).tree_id := l_tree_id;
1294
1295 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1296 RAISE fnd_api.g_exc_error ;
1297 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1298 RAISE fnd_api.g_exc_unexpected_error;
1299 END IF;
1300
1301 --Bug 2500570.If unable to get delivery detail we should not
1302 --error for batch.
1303 ELSIF l_qtree_item_tbl(l_item_index).unit_effective = 'Y' AND
1304 l_reservable_type = 1 THEN
1305 --loop through all lines
1306 l_qtree_line_index := l_qtree_item_tbl(l_item_index).first_line_rec;
1307 Loop
1308 EXIT WHEN l_qtree_line_index = 0;
1309 --get demand information
1310 BEGIN
1311 SELECT SOURCE_HEADER_ID
1312 INTO l_OE_HEADER_ID
1313 FROM wsh_delivery_details
1314 WHERE move_order_line_id =
1315 l_qtree_line_tbl(l_qtree_line_index).move_order_line_id
1316 AND move_order_line_id is not NULL
1317 AND released_status = 'S';
1318 EXCEPTION
1319 WHEN others THEN
1320 l_OE_HEADER_ID :=-9999;
1321 If is_debug then
1322 print_debug('No data found-Delivery Info',
1323 'Inv_Pick_Release_PUB.Pick_release');
1324 End If;
1325 --ROLLBACK TO Pick_Release_PUB;
1326 --FND_MESSAGE.SET_NAME('INV','INV_DELIV_INFO_MISSING');
1327 -- FND_MSG_PUB.Add;
1328 -- RAISE fnd_api.g_exc_unexpected_error;
1329 END;
1330
1331 l_return_value := INV_CACHE.set_mso_rec(l_oe_header_id);
1332 IF NOT l_return_value THEN
1333 l_mso_header_id :=-9999;
1334 If is_debug then
1335 print_debug('No Mtl_Sales_Order ID found for oe header','Inv_Pick_Release_PUB.Process_Line');
1336 End If;
1337 ELSE
1338 l_mso_header_id := INV_CACHE.mso_rec.sales_order_id;
1339 END IF;
1340
1341 BEGIN
1342 select t.transaction_source_type_id
1343 into l_demand_source_type
1344 from mtl_transaction_types t, mtl_txn_source_types st
1345 where t.transaction_type_id =
1346 l_qtree_line_tbl(l_qtree_line_index).transaction_type_id
1347 and t.transaction_source_type_id = st.transaction_source_type_id;
1348 exception
1349 when others then
1350 l_demand_source_type :=-9999;
1351 If is_debug then
1352 print_debug('No data found-Transaction types','Inv_Pick_Release_PVT.Process_Line');
1353 End If;
1354 END;
1355
1356 inv_quantity_tree_pvt.create_tree
1357 (
1358 p_api_version_number => 1.0
1359 ,p_init_msg_lst => fnd_api.g_false
1360 ,x_return_status => l_api_return_status
1361 ,x_msg_count => x_msg_count
1362 ,x_msg_data => x_msg_data
1363 ,p_organization_id => l_organization_id
1364 ,p_inventory_item_id => l_item_index
1365 ,p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
1366 ,p_is_revision_control => l_revision_controlled
1367 ,p_is_lot_control => l_lot_controlled
1368 ,p_is_serial_control => FALSE
1369 ,p_asset_sub_only => FALSE
1370 ,p_include_suggestion => FALSE
1371 ,p_demand_source_type_id => l_demand_source_type
1372 ,p_demand_source_header_id => l_mso_header_id
1373 ,p_demand_source_line_id => l_qtree_line_index
1374 ,p_demand_source_delivery => NULL
1375 ,p_demand_source_name => NULL
1376 ,p_lot_expiration_date => SYSDATE
1377 ,x_tree_id => l_tree_id
1378 ,p_exclusive => inv_quantity_tree_pvt.g_exclusive
1379 ,p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
1380 );
1381 If is_debug then
1382 print_debug('Tree id from PJM Create tree'||l_tree_id,'Inv_Pick_Release_PVT.Process_Line');
1383 End If;
1384
1385 l_qtree_line_tbl(l_qtree_line_index).tree_id := l_tree_id;
1386
1387 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1388 If is_debug then
1389 print_debug('Error from Create tree','Inv_pick_release_pub.Pick_release');
1390 End If;
1391 RAISE fnd_api.g_exc_error ;
1392 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1393 If is_debug then
1394 print_debug('Unexpected error from Create tree','Inv_pick_release_pub.Pick_release');
1395 End If;
1396 RAISE fnd_api.g_exc_unexpected_error;
1397 END IF;
1398
1399 l_qtree_line_index :=
1400 l_qtree_line_tbl(l_qtree_line_index).next_line_rec;
1401 End Loop; --End of loop through order lines
1402
1403 END IF; -- unit effective
1404
1405 EXIT WHEN l_item_index = l_qtree_item_tbl.LAST;
1406 l_item_index := l_qtree_item_tbl.NEXT(l_item_index);
1407 END LOOP;
1408
1409 -- now sort the move order line by the header_id
1410 --BENCHMARK - check cache
1411 --l_wms_installed := inv_install.adv_inv_installed(
1412 -- l_mo_line_tbl(l_line_index).organization_id);
1413 l_return_value := INV_CACHE.set_wms_installed(l_organization_id);
1414 If NOT l_return_value Then
1415 If is_debug then
1416 print_debug('Error setting cache for wms installed','INV_Pick_Release_Pub.Pick_Release');
1417 End If;
1418 RAISE fnd_api.g_exc_unexpected_error;
1419 End If;
1420 l_wms_installed := INV_CACHE.wms_installed;
1421 --sort only necessary if wms installed and multiple headers in the
1422 -- line_tbl
1423 IF l_wms_installed and l_multiple_headers THEN
1424 sort(l_mo_line_tbl, l_mo_line_tbl.FIRST, l_mo_line_tbl.LAST);
1425 END IF;
1426
1427 -- Determine whether or not to automatically pick confirm
1428 IF p_auto_pick_confirm IS NOT NULL THEN
1429 IF (p_auto_pick_confirm <> 1 AND p_auto_pick_confirm <> 2) THEN
1430 If is_debug then
1431 print_debug('Error: Invalid auto_pick_confirm flag','INV_Pick_Release_Pub.Pick_Release');
1432 End If;
1433 ROLLBACK TO Pick_Release_PUB;
1434 FND_MESSAGE.SET_NAME('INV','INV_AUTO_PICK_CONFIRM_PARAM');
1435 FND_MSG_PUB.Add;
1436 RAISE fnd_api.g_exc_unexpected_error;
1437 ELSE
1438 l_auto_pick_confirm := p_auto_pick_confirm;
1439 END IF;
1440 ELSE
1441 -- Retrieve the org-level parameter for auto-pick confirm
1442 l_return_value := INV_CACHE.set_org_rec(l_organization_id);
1443 If NOT l_return_value Then
1444 If is_debug then
1445 print_debug('Error setting cache for organization',
1446 'INV_Pick_Release_Pub.Pick_Release');
1447 End If;
1448 RAISE fnd_api.g_exc_unexpected_error;
1449 End If;
1450 l_auto_pick_confirm:= INV_CACHE.org_rec.mo_pick_confirm_required;
1451 END IF;
1452
1453 -- Determine what printing mode to use when pick releasing lines.
1454 IF g_organization_id IS NOT NULL AND
1455 g_organization_id = l_organization_id AND
1456 g_print_mode IS NOT NULL THEN
1457
1458 l_print_mode := g_print_mode;
1459 ELSE
1460
1461 BEGIN
1462 SELECT print_pick_slip_mode, pick_grouping_rule_id
1463 INTO l_print_mode, g_org_grouping_rule_id
1464 FROM WSH_SHIPPING_PARAMETERS
1465 WHERE organization_id = l_organization_id;
1466 EXCEPTION
1467 WHEN no_data_found THEN
1468 If is_debug then
1469 print_debug('Error: print_pick_slip_mode not defined','INV_Pick_Release_Pub.Pick_Release');
1470 End If;
1471 ROLLBACK TO Pick_Release_PUB;
1472 FND_MESSAGE.SET_NAME('INV','INV_WSH_ORG_NOT_FOUND');
1473 FND_MSG_PUB.Add;
1474 RAISE fnd_api.g_exc_unexpected_error;
1475 END;
1476
1477 g_organization_id := l_organization_id;
1478 g_print_mode := l_print_mode;
1479 END IF;
1480
1481 -- Validate parameter for allowing partial pick release
1482 IF p_allow_partial_pick <> fnd_api.g_true AND
1483 p_allow_partial_pick <> fnd_api.g_false THEN
1484
1485 If is_debug then
1486 print_debug('Error: invalid partial pick parameter','INV_Pick_Release_Pub.Pick_Release');
1487 End If;
1488 ROLLBACK TO Pick_Release_PUB;
1489 FND_MESSAGE.SET_NAME('INV','INV_INVALID_PARTIAL_PICK_PARAM');
1490 FND_MSG_PUB.Add;
1491 RAISE fnd_api.g_exc_unexpected_error;
1492 END IF;
1493
1494 If is_debug then
1495 print_debug('p_allow_partial_pick is ' || p_allow_partial_pick,'Inv_Pick_Release_Pub.Pick_Release');
1496 End If;
1497
1498 -- The Move Order Lines may need to have their grouping rule ID defaulted
1499 -- from the header. This is only necessary if the Grouping Rule ID was not
1500 -- passed in as a parameter.
1501 IF p_grouping_rule_id IS NOT NULL AND p_grouping_rule_id <> fnd_api.G_MISS_NUM THEN
1502 l_grouping_rule_id := p_grouping_rule_id;
1503 l_get_header_rule := 2;
1504 ELSE
1505 l_get_header_rule := 1;
1506 END IF;
1507
1508 -- Bug# 4258360: Query for and cache the picking batch record.
1509 -- Even if multiple move order headers are created for this batch (not likely),
1510 -- they should all refer to the same picking batch record. A value for the move
1511 -- order header record should be cached by now in INV_CACHE. 'Batch' refers to
1512 -- the set of MOL's passed into the pick release API and not the entire set of pick
1513 -- release lines.
1514 -- {{
1515 -- Pick Release a batch such that you have orders from multiple
1516 -- organizations. This will cause multiple MOH to be created.
1517 -- If successfully pick released, then no bug.
1518 -- }}
1519 IF (NOT INV_CACHE.set_wpb_rec
1520 (p_batch_id => NULL,
1521 p_request_number => INV_CACHE.mtrh_rec.request_number)) THEN
1522 IF (is_debug) THEN
1523 print_debug('Error setting cache for WSH picking batch record','INV_Pick_Release_Pub.Pick_Release');
1524 END IF;
1525 RAISE fnd_api.g_exc_unexpected_error;
1526 END IF;
1527 -- Set the allocation method variable
1528 l_allocation_method := NVL(INV_CACHE.wpb_rec.allocation_method, g_inventory_only);
1529
1530 -- Bug# 4258360: Validate that p_trolin_delivery_ids and p_del_detail_id tables have the
1531 -- same number of entries. There should be a one to one relationship between the tables
1532 -- and the indices used. If NULL tables are passed, the counts should still match (0 = 0).
1533 -- NULL empty tables should be passed by Shipping for this. These tables are used to store
1534 -- crossdocked WDD lines so deliveries can be created for them.
1535 IF (p_del_detail_id.COUNT <> p_trolin_delivery_ids.COUNT) THEN
1536 IF (is_debug) THEN
1537 print_debug('Mismatch in size of input tables from Shipping for delivery creation','INV_Pick_Release_Pub.Pick_Release');
1538 END IF;
1539 RAISE fnd_api.g_exc_unexpected_error;
1540 END IF;
1541
1542 -- Bug# 4258360: Loop through p_wsh_release_table to build the index pointer table to it.
1543 -- Do this only if p_wsh_release_table has entries in it. Crossdocking will be called after
1544 -- Inventory allocation has completed. Since it makes use of the release table, we need to keep
1545 -- that updated and in sync. We also want to update any backordered lines in the release table
1546 -- so Shipping can know which lines were backordered and not auto-create deliveries for them.
1547 -- UPDATE: Do not need to insert lines into the delivery tables for Prioritize Crossdock.
1548 -- Since Shipping is not populating the delivery tables inputted, they will just be used to store
1549 -- the crossdocked WDD lines. The crossdock pegging API will now insert the crossdocked WDD lines
1550 -- into these delivery tables.
1551 -- {{
1552 -- Run PR in Prioritize Inventory mode. Ensure that some rows get
1553 -- allocated from INV and some get x-docked. The ones that got
1554 -- allocated from INV should not get x-docked.
1555 -- }}
1556 -- {{
1557 -- Run PR in Prioritize x-dock mode. Ensure that no deliveries are
1558 -- created prior to running PR. Also ensure some WDDs get x-docked
1559 -- and some get allocated from INV. All these WDDs should have deliveries
1560 -- created at the end of PR run.
1561 -- }}
1562 IF (p_wsh_release_table.COUNT > 0) THEN
1563 l_line_index := p_wsh_release_table.FIRST;
1564 l_wdd_index_tbl.DELETE;
1565 IF (is_debug) THEN
1566 print_debug('Build the WDD index pointer table (Prioritize Inventory)',
1567 'Inv_Pick_Release_Pub.Pick_Release');
1568 END IF;
1569 LOOP
1570 -- Store this WDD record into the WDD index pointer table
1571 l_wdd_index_tbl(p_wsh_release_table(l_line_index).delivery_detail_id) := l_line_index;
1572
1573 EXIT WHEN l_line_index = p_wsh_release_table.LAST;
1574 l_line_index := p_wsh_release_table.NEXT(l_line_index);
1575 END LOOP;
1576 END IF;
1577
1578 if l_wms_installed then -- 14781148
1579 IF (is_debug) THEN
1580 print_debug('Before calling the update_wave_lines_tbl ','Inv_Pick_Release_Pub.Pick_Release');
1581 END IF;
1582
1583 wms_wave_planning_pvt.update_wave_lines_tbl( p_mo_line_table => l_mo_line_tbl
1584 ,x_return_status => l_return_status);
1585
1586 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1587 IF (l_debug = 1) THEN
1588 print_debug('After calling the update_wave_lines_tbl which has returned error - Still Continue with rest of Pick Release','Inv_Pick_Release_Pub.Pick_Release');
1589 END IF;
1590 END IF;
1591
1592 IF (is_debug) THEN
1593 print_debug('After calling the update_wave_lines_tbl ','Inv_Pick_Release_Pub.Pick_Release');
1594 END IF;
1595 end if; -- 14781148
1596
1597
1598 -- Validation and initialization complete. Begin pick release processing row-by-row.
1599 l_line_index := l_mo_line_tbl.FIRST;
1600 l_organization_id := l_mo_line.organization_id;
1601 LOOP
1602 l_mo_line := l_mo_line_tbl(l_line_index);
1603 -- only process the valid move order, fix bug 1540709.
1604 IF (l_mo_line.return_status <> FND_API.G_RET_STS_UNEXP_ERROR and
1605 l_mo_line.return_status <> FND_API.G_RET_STS_ERROR) THEN
1606 -- First retrieve the new Grouping Rule ID if necessary.
1607 IF l_get_header_rule = 1 THEN
1608 l_return_value := INV_CACHE.set_mtrh_rec(l_mo_line.header_id);
1609 If NOT l_return_value Then
1610 If is_debug then
1611 print_debug('Error setting cache for move order header ',
1612 'INV_Pick_Release_Pub.Pick_Release');
1613 End If;
1614 RAISE fnd_api.g_exc_unexpected_error;
1615 End If;
1616 l_grouping_rule_id := INV_CACHE.mtrh_rec.grouping_rule_id;
1617
1618 -- If the header did not have a grouping rule ID, retrieve it from
1619 -- the organization-level default.
1620 IF l_grouping_rule_id IS NULL THEN
1621 If g_organization_id IS NOT NULL And
1622 g_organization_id = l_organization_id And
1623 g_org_grouping_rule_id IS NOT NULL Then
1624
1625 l_grouping_rule_id := g_org_grouping_rule_id;
1626
1627 Else
1628 BEGIN
1629 SELECT pick_grouping_rule_id
1630 INTO l_grouping_rule_id
1631 FROM wsh_shipping_parameters
1632 WHERE organization_id = l_organization_id;
1633 EXCEPTION
1634 WHEN no_data_found THEN
1635 If is_debug then
1636 print_debug('Error finding org grouping rules',
1637 'INV_Pick_Release_Pub.Pick_Release');
1638 End If;
1639 ROLLBACK TO Pick_Release_PUB;
1640 FND_MESSAGE.SET_NAME('INV','INV-NO ORG INFORMATION');
1641 FND_MSG_PUB.Add;
1642 RAISE fnd_api.g_exc_unexpected_error;
1643 END;
1644
1645 If g_organization_id IS NULL Or
1646 g_organization_id <> l_organization_id Then
1647 g_organization_id := l_organization_id;
1648 -- null out other org based global variables
1649 g_print_mode := NULL;
1650 End If;
1651
1652 g_org_grouping_rule_id := l_grouping_rule_id;
1653 End If;
1654
1655 END IF; -- get header rule
1656 END IF; -- return status
1657
1658
1659 IF l_mo_line.ship_set_id IS NOT NULL AND
1660 (l_cur_ship_set_id IS NULL OR
1661 l_cur_ship_set_id <> l_mo_line.ship_set_id) THEN
1662
1663 SAVEPOINT SHIPSET;
1664 l_cur_ship_set_id := l_mo_line.ship_set_id;
1665 l_start_index := l_line_index;
1666 l_start_process := l_processed_row_count;
1667 l_qtree_backup_tbl.DELETE;
1668 If is_debug then
1669 print_debug('Start Shipset :' || l_cur_ship_set_id,
1670 'Inv_Pick_Release_Pub.Pick_Release');
1671 End If;
1672 ELSIF l_cur_ship_set_id IS NOT NULL AND
1673 l_mo_line.ship_set_id IS NULL THEN
1674 If is_debug then
1675 print_debug('End of Shipset :' || l_cur_ship_set_id,
1676 'Inv_Pick_Release_Pub.Pick_Release');
1677 End If;
1678 l_cur_ship_set_id := NULL;
1679 l_qtree_backup_tbl.DELETE;
1680 END IF;
1681 --2509322
1682 IF l_cur_ship_model_id IS NOT NULL AND
1683 l_mo_line.ship_model_id IS NULL OR
1684 l_mo_line.ship_model_id <>l_cur_ship_model_id then
1685 Backorder_SMC_DETAILS(l_api_return_status ,
1686 x_msg_data ,
1687 x_msg_count ) ;
1688 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1689 RAISE fnd_api.g_exc_error ;
1690 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1691 RAISE fnd_api.g_exc_unexpected_error;
1692 END IF;
1693 END IF;
1694
1695 IF l_mo_line.ship_model_id IS NOT NULL AND
1696 (l_cur_ship_model_id IS NULL OR
1697 l_cur_ship_model_id <> l_mo_line.ship_model_id) THEN
1698
1699 SAVEPOINT SHIPMODEL;
1700 l_cur_ship_model_id := l_mo_line.ship_model_id;
1701 l_start_index := l_line_index;
1702 l_start_process := l_processed_row_count;
1703 l_qtree_backup_tbl.DELETE;
1704 If is_debug then
1705 print_debug('Start Ship Model :' || l_cur_ship_model_id,
1706 'Inv_Pick_Release_Pub.Pick_Release');
1707 End If;
1708 ELSIF l_cur_ship_model_id IS NOT NULL AND
1709 l_mo_line.ship_model_id IS NULL THEN
1710 If is_debug then
1711 print_debug('End of Ship Model :' || l_cur_ship_model_id,
1712 'Inv_Pick_Release_Pub.Pick_Release');
1713 End If;
1714 l_cur_ship_model_id := NULL;
1715 l_qtree_backup_tbl.DELETE;
1716 END IF;
1717
1718 IF (l_mo_line.ship_set_id IS NOT NULL OR
1719 l_mo_line.ship_model_id IS NOT NULL) THEN
1720
1721 --find tree id. If item is unit effective, get tree id from
1722 -- qtree_line_tbl. Else, get it from qtree_item_tbl.
1723 If l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective='Y'
1724 Then
1725 l_tree_id := l_qtree_line_tbl(l_mo_line.txn_source_line_id).tree_id;
1726 Else
1727 l_tree_id := l_qtree_item_tbl(l_mo_line.inventory_item_id).tree_id;
1728 End If;
1729
1730 --only backup the tree if it is not already backed up
1731 If Not l_qtree_backup_tbl.Exists(l_tree_id) Then
1732 If is_debug then
1733 print_debug('Backing up qty tree: ' || l_tree_id,
1734 'Inv_Pick_Release_Pub.Pick_Release');
1735 End If;
1736 --Bug 2814919
1737 if l_tree_id is not null then
1738 inv_quantity_tree_pvt.backup_tree(
1739 x_return_status => l_api_return_status
1740 ,p_tree_id => l_tree_id
1741 ,x_backup_id => l_backup_id
1742 );
1743
1744 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1745 RAISE fnd_api.g_exc_error ;
1746 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
1747 RAISE fnd_api.g_exc_unexpected_error;
1748 END IF;
1749 End If;
1750
1751 --Bug 2814919
1752 if l_tree_id is not null then
1753 l_qtree_backup_tbl(l_tree_id) := l_backup_id;
1754 End if;
1755
1756 End If;
1757
1758 END IF; -- shipset/ship model NOT NULL
1759
1760 -- Need to keep track of the quantity allocated so far for this
1761 -- sales order - there can be multiple move orders per
1762 -- sales order line, and when dealing with ship model complete,
1763 -- the quantity requested and quantity allocated for the
1764 -- sales order line is important
1765 IF l_cur_txn_source_line_id IS NULL OR
1766 l_mo_line.txn_source_line_id <> l_cur_txn_source_line_id THEN
1767
1768 l_cur_txn_source_line_id := l_mo_line.txn_source_line_id;
1769 l_cur_txn_source_qty := 0;
1770 -- HW INVCONV Added Qty2
1771 l_cur_txn_source_qty2 := 0;
1772 If is_debug then
1773 print_debug('Set Current Txn Src Line:' || l_cur_txn_source_line_id,'Inv_Pick_Release_Pub.Pick_Release');
1774 End If;
1775 END IF;
1776
1777 g_pick_expired_lots := FALSE;
1778 g_pick_expired_lots := USER_PKG_LOT.use_expired_lots (
1779 p_organization_id => l_mo_line.organization_id
1780 , p_inventory_item_id => l_mo_line.inventory_item_id
1781 , p_demand_source_type_id => l_mo_line.transaction_source_type_id
1782 , p_demand_source_line_id => l_mo_line.txn_source_line_id
1783 );
1784
1785 -- Call the Pick Release Process_Line API on the current Move Order Line
1786 If is_debug then
1787 print_debug('calling INV_Pick_Release_PVT.process_line',
1788 'Inv_Pick_Release_Pub.Pick_Release');
1789 End If;
1790
1791 INV_Pick_Release_PVT.Process_Line(
1792 p_api_version => 1.0
1793 ,p_init_msg_list => fnd_api.g_false
1794 ,p_commit => fnd_api.g_false
1795 ,x_return_status => l_api_return_status
1796 ,x_msg_count => x_msg_count
1797 ,x_msg_data => x_msg_data
1798 ,p_mo_line_rec => l_mo_line
1799 ,p_grouping_rule_id => l_grouping_rule_id
1800 ,p_allow_partial_pick => p_allow_partial_pick
1801 ,p_print_mode => l_print_mode
1802 ,x_detail_rec_count => l_detail_rec_count
1803 ,p_plan_tasks => p_plan_tasks
1804 );
1805
1806 If is_debug then
1807 print_debug('l_return_status from process_line is '|| l_api_return_status, 'Inv_Pick_Release_Pub.Pick_Release');
1808 End If;
1809
1810 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1811 -- Return error if Process_Line returns error and
1812 -- allow_partial_pick is false, since we can't pick full quantity
1813 IF p_allow_partial_pick = fnd_api.g_false THEN
1814 x_pick_release_status.delete;
1815 ROLLBACK TO Pick_Release_PUB;
1816 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_PICK_FULL');
1817 FND_MSG_PUB.Add;
1818 RAISE fnd_api.g_exc_unexpected_error;
1819 END IF;
1820 END IF;
1821
1822 l_quantity := l_mo_line.quantity;
1823 -- HW INVCONV Added Qty2
1824 l_quantity2 := l_mo_line.secondary_quantity;
1825 l_line_status := l_mo_line.line_status;
1826 l_quantity_delivered := l_mo_line.quantity_delivered;
1827 l_quantity2_delivered := l_mo_line.secondary_quantity_delivered;
1828
1829 -- For non reservable items this status is set to 5 in
1830 -- process_line API and the below processing should not be done
1831
1832 IF l_line_status <> 5 THEN
1833 --BENCHMARK - can we use quantity detailed, or perhaps
1834 --quantity detailed - quantity delivered
1835
1836 --select nvl(sum(transaction_quantity),0)
1837 --into l_transaction_quantity
1838 --from mtl_material_transactions_Temp
1839 --where move_order_line_id = l_mo_line.line_id;
1840
1841 l_transaction_quantity := nvl(l_mo_line.quantity_detailed,0) -
1842 nvl(l_mo_line.quantity_delivered,0);
1843 l_cur_txn_source_qty := nvl(l_cur_txn_source_qty,0) + nvl(l_transaction_quantity,0);
1844
1845 -- HW INVCONV Added Qty2
1846 l_transaction_quantity2 := nvl(l_mo_line.secondary_quantity_detailed,0) -
1847 nvl(l_mo_line.secondary_quantity_delivered,0);
1848 l_cur_txn_source_qty2 := nvl(l_cur_txn_source_qty2,0) + nvl(l_transaction_quantity2,0);
1849
1850 IF (l_mo_line.required_quantity IS NOT NULL) THEN
1851 l_quantity :=l_mo_line.required_quantity;
1852 END IF;
1853
1854 If is_debug then
1855 print_debug('l_quantity '|| l_quantity, 'Inv_Pick_Release_Pub.Pick_Release');
1856 print_debug('l_line_status '|| l_line_status, 'Inv_Pick_Release_Pub.Pick_Release');
1857 print_debug('l_quantity_delivered '|| l_quantity_delivered, 'Inv_Pick_Release_Pub.Pick_Release');
1858 print_debug('l_transaction_quantity '|| l_transaction_quantity, 'Inv_Pick_Release_Pub.Pick_Release');
1859 print_debug('l_cur_txn_source_qty '|| l_cur_txn_source_qty, 'Inv_Pick_Release_Pub.Pick_Release');
1860 print_debug('l_quantity2_delivered '|| l_quantity2_delivered, 'Inv_Pick_Release_Pub.Pick_Release');
1861 print_debug('l_transaction_quantity2 '|| l_transaction_quantity2, 'Inv_Pick_Release_Pub.Pick_Release');
1862 print_debug('l_cur_txn_source_qty2 '|| l_cur_txn_source_qty2, 'Inv_Pick_Release_Pub.Pick_Release');
1863 print_debug('l_cur_ship_set_id '|| l_cur_ship_set_id, 'Inv_Pick_Release_Pub.Pick_Release');
1864 print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment,'INV_Pick_Release_Pub.Pick_Release');
1865 end if;
1866
1867 -- If the total allocated quantity is less than the requested
1868 -- quantity, call shipping to backorder the missing quantity.
1869 -- Update the move order line to change the requested quantity
1870 -- to be equal to the allocated quantity
1871
1872 -- Get the tolerance set while allocating the line
1873 -- If quantity is within tolerance then do not backorder shipset
1874 --
1875 -- l_lower_tolerance := l_quantity * inv_pick_release_pvt.g_min_tolerance;
1876 -- Bug 5188796: g_min_tolerance is a qty, not a %, so use as is
1877 l_lower_tolerance := inv_pick_release_pvt.g_min_tolerance;
1878 l_lower_tolerance2 := inv_pick_release_pvt.g_min_tolerance2;
1879
1880 --MUOM Fulfillment Project
1881 inv_utilities.get_inv_fulfillment_base(
1882 p_source_line_id => l_mo_line.txn_source_line_id,
1883 p_demand_source_type_id => l_mo_line.transaction_source_type_id,
1884 p_org_id => l_mo_line.organization_id,
1885 x_fulfillment_base => l_fulfill_base
1886 );
1887 --
1888 If is_debug then
1889 print_debug('l_lower_tolerance '|| l_lower_tolerance, 'Inv_Pick_Release_Pub.Pick_Release');
1890 print_debug('l_lower_tolerance2 '|| l_lower_tolerance2, 'Inv_Pick_Release_Pub.Pick_Release');
1891 print_debug('l_fulfill_base '|| l_fulfill_base, 'Inv_Pick_Release_Pub.Pick_Release');
1892 end if ;
1893 --
1894 -- Bug #2748751
1895 -- If move order is partially transacted and the allocations are split
1896 -- again, the allocated quantity will be lesser than move order quantity
1897 -- In that case, we need to compare the allocated quantity against the
1898 -- difference of move order requested quantity and delivered quantity
1899 --MUOM Fulfillemnt Project
1900 IF (((l_transaction_quantity < (l_quantity - NVL(l_quantity_delivered,0) - l_lower_tolerance)) and l_fulfill_base<>'S')
1901 or ((l_transaction_quantity2 < (l_quantity2 - NVL(l_quantity2_delivered,0) - l_lower_tolerance2)) and l_fulfill_base='S')) THEN
1902
1903 -- For shipsets, if any of the lines fail to allocate completely,
1904 -- rollback all allocations and then back order all of the
1905 -- move order lines for that ship set.
1906 If is_debug then
1907 print_debug('l_cur_ship_set_id:'||l_cur_ship_set_id,'Inv_Pick_Release_Pub.Pick_Release');
1908 print_debug('l_cur_ship_model_id:'||l_cur_ship_model_id ,'Inv_Pick_Release_Pub.Pick_Release');
1909 print_debug('g_pick_release_caller :'||g_pick_release_caller,'Inv_Pick_Release_Pub.Pick_Release');
1910 end if;
1911 IF ( l_cur_ship_set_id IS NOT NULL AND NVL(g_pick_release_caller,'###') <> 'WMS-PICK-EXCEPTION') THEN --9556463
1912
1913 -- Bug 2461353, 2411016
1914 -- Call Shipping and let them know for a ship set we were not able
1915 -- to detail complete so back ordering started.
1916
1917 BEGIN
1918 If is_debug then
1919 print_debug('Update shipping that ship set detailing failed',
1920 'Inv_Pick_Release_Pub.Pick_Release');
1921 End If;
1922
1923 l_shipset_smc_backorder_rec.move_order_line_id:=l_mo_line.line_id;
1924 l_shipset_smc_backorder_rec.ship_set_id :=l_cur_ship_set_id;
1925
1926
1927 wsh_integration.ins_backorder_ss_smc_rec
1928 (p_api_version_number => 1.0,
1929 p_source_code => 'INV',
1930 p_init_msg_list => fnd_api.g_false,
1931 p_backorder_rec => l_shipset_smc_backorder_rec,
1932 x_return_status => l_api_return_status,
1933 x_msg_count => x_msg_count,
1934 x_msg_data => x_msg_data);
1935
1936 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1937 If is_debug then
1938 print_debug('Error occured while updating shipping for ' ||
1939 'failed ship set','Inv_Pick_Release_Pub.Pick_Release');
1940 print_debug('l_return_status' || l_api_return_status,
1941 'Inv_Pick_Release_Pub.Pick_Release');
1942 End If;
1943 END IF;
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 If is_debug then
1948 print_debug('When other exception: ' || Sqlerrm,
1949 'Inv_Pick_Release_Pub.Pick_Release');
1950 print_debug('l_return_status' || l_api_return_status,
1951 'Inv_Pick_Release_Pub.Pick_Release');
1952 End If;
1953 NULL;
1954 --no need to error out for reporting purpose.
1955 END;
1956
1957 If is_debug then
1958 print_debug('Rollback for shipset :' || l_cur_ship_set_id,
1959 'Inv_Pick_Release_Pub.Pick_Release');
1960 End If;
1961
1962 ROLLBACK to SHIPSET;
1963 l_set_index := l_start_index;
1964 l_set_process := l_start_process;
1965
1966 --loop through all move order lines for this ship set
1967 LOOP
1968 l_mo_line := l_mo_line_tbl(l_set_index);
1969
1970 --find tree id. If item is unit effective, get tree id from
1971 -- qtree_line_tbl. Else, get it from qtree_item_tbl.
1972 IF l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective='Y'
1973 THEN
1974 l_tree_id :=
1975 l_qtree_line_tbl(l_mo_line.txn_source_line_id).tree_id;
1976 ELSE
1977 l_tree_id :=
1978 l_qtree_item_tbl(l_mo_line.inventory_item_id).tree_id;
1979 END IF;
1980
1981 -- only restore tree if it is currently in backup table.
1982 -- Tree would not be in backup table if current move order line
1983 -- was not allocated or if the tree had already been restored
1984 -- for a previous move order line.
1985 IF l_qtree_backup_tbl.EXISTS(l_tree_id) THEN
1986
1987 If is_debug then
1988 print_debug('Restoring Quantity Tree: ' || l_tree_id,
1989 'Inv_Pick_Release_Pub.Pick_Release');
1990 End If;
1991
1992 inv_quantity_tree_pvt.restore_tree
1993 (x_return_status => l_api_return_status
1994 ,p_tree_id => l_tree_id
1995 ,p_backup_id => l_qtree_backup_tbl(l_tree_id)
1996 );
1997
1998 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
1999 If is_debug then
2000 print_debug('Error in Restore_Tree',
2001 'Inv_Pick_Release_Pub.Pick_Release');
2002 End If;
2003 raise FND_API.G_EXC_ERROR;
2004 elsif l_api_return_status=FND_API.G_RET_STS_UNEXP_ERROR then
2005 If is_debug then
2006 print_debug('Unexpected error in Restore_tree',
2007 'Inv_Pick_Release_Pub.Pick_Release');
2008 End If;
2009 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2010 end if;
2011
2012 --delete entry, so we don't restore tree more than once
2013 l_qtree_backup_tbl.DELETE(l_tree_id);
2014
2015 END IF;
2016
2017 If is_debug then
2018 print_debug('Backorder mo line:' || l_mo_line.line_id,
2019 'Inv_Pick_Release_Pub.Pick_Release');
2020 End If;
2021
2022 l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2023 If NOT l_return_value Then
2024 If is_debug then
2025 print_debug('Error setting cache for delivery line',
2026 'INV_Pick_Release_Pub.Pick_Release');
2027 End If;
2028 RAISE fnd_api.g_exc_unexpected_error;
2029 End If;
2030 l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2031 l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2032 l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2033 l_released_status := INV_CACHE.wdd_rec.released_status;
2034
2035
2036 --Call Update_Shipping_Attributes to backorder detail line
2037 l_shipping_attr(1).source_header_id := l_source_header_id;
2038 l_shipping_attr(1).source_line_id := l_source_line_id;
2039 l_shipping_attr(1).ship_from_org_id := l_mo_line.organization_id;
2040 l_shipping_attr(1).released_status := l_released_status;
2041 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
2042 l_shipping_attr(1).action_flag := 'B';
2043 l_shipping_attr(1).cycle_count_quantity := l_mo_line.quantity;
2044 -- HW INVCONV Added Qty2
2045 l_shipping_attr(1).cycle_count_quantity2 := l_mo_line.secondary_quantity;
2046 l_shipping_attr(1).subinventory := l_mo_line.from_subinventory_code;
2047 l_shipping_attr(1).locator_id := l_mo_line.from_locator_id;
2048
2049
2050 WSH_INTERFACE.Update_Shipping_Attributes
2051 (p_source_code => 'INV',
2052 p_changed_attributes => l_shipping_attr,
2053 x_return_status => l_api_return_status
2054 );
2055 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
2056 If is_debug then
2057 print_debug('return error from update shipping attributes',
2058 'Inv_Pick_Release_Pub.Pick_Release');
2059 End If;
2060 raise FND_API.G_EXC_ERROR;
2061 elsif l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2062 If is_debug then
2063 print_debug('return error from update shipping attributes',
2064 'Inv_Pick_Release_Pub.Pick_Release');
2065 End If;
2066 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2067 end if;
2068
2069 --close the move order line
2070 -- HW INVCONV Update Qty2
2071 update mtl_txn_request_lines
2072 set quantity = 0
2073 ,quantity_detailed = 0
2074 ,secondary_quantity = decode(secondary_quantity,fnd_api.g_miss_num, NULL, 0)
2075 ,secondary_quantity_detailed = decode(secondary_quantity_detailed,fnd_api.g_miss_num, NULL, 0)
2076 ,line_status = 5
2077 ,status_date =sysdate --BUG 7560455
2078 where line_id = l_mo_line.line_id;
2079
2080 -- Exit if there are no more move order lines to detail
2081 -- or when the next move order is not for the same ship set.
2082 -- Exit before updating the pick_release_status tbl
2083 -- for the last line. The table gets updated for the last
2084 -- line later.
2085 -- l_set_index should always be equal to the last line
2086 -- in the current ship set, so that the logic at the
2087 -- end of the outer loop works correctly.
2088 EXIT WHEN l_mo_line_tbl.LAST = l_set_index;
2089 l_set_index := l_mo_line_tbl.NEXT(l_set_index);
2090 if nvl(l_mo_line_tbl(l_set_index).ship_set_id,-1)
2091 <> l_cur_ship_set_id then
2092 l_set_index := l_mo_line_tbl.PRIOR(l_set_index);
2093 EXIT;
2094 end if;
2095
2096 --If next line is for same ship set, update output table
2097 l_set_process := l_set_process + 1;
2098 x_pick_release_status(l_set_process).mo_line_id :=
2099 l_mo_line.line_id;
2100 x_pick_release_status(l_set_process).return_status :=
2101 l_api_return_status;
2102 x_pick_release_status(l_set_process).detail_rec_count := 0;
2103 If is_debug then
2104 print_debug('x_pick_release_status ' || l_set_process ||
2105 ' mo_line_id = ' || l_mo_line.line_id,
2106 'Pick_release_Pub');
2107 print_debug('x_pick_release_status ' || l_set_process ||
2108 ' return_status = '|| l_api_return_status,
2109 'Pick_release_Pub');
2110 print_debug('x_pick_release_status ' || l_set_process ||
2111 ' detail_rec_count = 0', 'Pick_Release_Pub');
2112 End If;
2113 END LOOP;
2114
2115 -- at the end of this loop, l_mo_line and l_set_index
2116 -- point to the last line for this ship set. l_set_process
2117 -- is the index of the last entry in the pick release status
2118 -- table. This allows all of the logic near the end of the
2119 -- loop to work correctly.
2120 l_line_index := l_set_index;
2121 l_cur_ship_set_id := NULL;
2122 l_processed_row_count := l_set_process;
2123 l_detail_rec_count := 0;
2124 l_qtree_backup_tbl.DELETE;
2125 If is_debug then
2126 print_debug('Finished backordering all lines in shipset',
2127 'Inv_Pick_Release_Pub.Pick_Release');
2128 End If;
2129
2130 --For Ship Models, if a move order line does not fully
2131 -- allocate, we have to determine the new model quantity. Then,
2132 -- once we know the new model quantity, we have to change the
2133 -- quantity on each move order line to reflect the new model
2134 -- quantity. Backorders have to created for the lines which
2135 -- have their quantity reduced.
2136 ELSIF (l_cur_ship_model_id IS NOT NULL AND NVL(g_pick_release_caller,'###') <> 'WMS-PICK-EXCEPTION') THEN --9556463
2137 -- Bug 2461353, 2411016
2138 --Call Shipping and let them know for a ship model we were not
2139 --able to allocate completely
2140 BEGIN
2141 If is_debug then
2142 print_debug('Update shipping that ship model detailing partial',
2143 'Inv_Pick_Release_Pub.Pick_Release');
2144 End If;
2145
2146 l_shipset_smc_backorder_rec.move_order_line_id:=l_mo_line.line_id;
2147 l_shipset_smc_backorder_rec.ship_model_id :=l_cur_ship_model_id;
2148
2149 wsh_integration.ins_backorder_ss_smc_rec
2150 (p_api_version_number => 1.0,
2151 p_source_code => 'INV',
2152 p_init_msg_list => fnd_api.g_false,
2153 p_backorder_rec => l_shipset_smc_backorder_rec,
2154 x_return_status => l_api_return_status,
2155 x_msg_count => x_msg_count,
2156 x_msg_data => x_msg_data);
2157
2158 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2159 If is_debug then
2160 print_debug('Error occured while updating shipping for ' ||
2161 'failed ship set',
2162 'Inv_Pick_Release_Pub.Pick_Release');
2163 print_debug('l_return_status'||l_api_return_status,
2164 'Inv_Pick_Release_Pub.Pick_Release');
2165 End If;
2166 END IF;
2167 EXCEPTION
2168 WHEN OTHERS THEN
2169 If is_debug then
2170 print_debug('When other exception',
2171 'Inv_Pick_Release_Pub.Pick_Release');
2172 End If;
2173 NULL;
2174 --no need to error out for reporting purpose.
2175 END;
2176
2177 If is_debug then
2178 print_debug('Rolling back for ship model :' ||l_cur_ship_model_id,
2179 'Inv_Pick_Release_Pub.Pick_Release');
2180 End If;
2181
2182 ROLLBACK to SHIPMODEL;
2183 l_set_index := l_start_index;
2184 l_set_process := l_start_process;
2185
2186 If is_debug then
2187 print_debug('OE Line: ' || l_cur_txn_source_line_id,
2188 'Inv_Pick_Release_Pub.Pick_Release');
2189 End If;
2190
2191 -- Get the sales order line quantity. We need the order
2192 -- line quantity to determine the new model quantity. We
2193 -- can't just use the move order line quantity, b/c there
2194 -- could be multiple move orders per sales order line.
2195 BEGIN
2196 SELECT ordered_quantity, order_quantity_uom
2197 INTO l_cur_txn_source_req_qty, l_txn_source_line_uom
2198 FROM OE_ORDER_LINES_ALL
2199 WHERE line_id = l_cur_txn_source_line_id;
2200 EXCEPTION
2201 WHEN NO_DATA_FOUND then
2202 If is_debug then
2203 print_debug('No Order Line Quantity found',
2204 'Inv_Pick_Release_Pub.Pick_Release');
2205 End If;
2206 ROLLBACK TO Pick_Release_PUB;
2207 FND_MESSAGE.SET_NAME('INV','INV_DELIV_INFO_MISSING');
2208 FND_MSG_PUB.Add;
2209 RAISE fnd_api.g_exc_unexpected_error;
2210 END;
2211
2212 -- convert to primary UOM
2213 IF l_txn_source_line_uom <>
2214 l_primary_uom_tbl(l_mo_line.inventory_item_id) THEN
2215
2216 l_cur_txn_source_req_qty :=
2217 inv_convert.inv_um_convert(
2218 l_mo_line.inventory_item_id
2219 ,NULL
2220 ,l_cur_txn_source_req_qty
2221 ,l_txn_source_line_uom
2222 ,l_primary_uom_tbl(l_mo_line.inventory_item_id)
2223 ,NULL
2224 ,NULL);
2225 END IF;
2226 -- find new model quantity.
2227 -- new model qty = floor((allocated for this sales order /
2228 -- requested for this sales order) *
2229 -- original model quantity)
2230 -- We take the floor because we can only ship whole numbers
2231 -- of the top model
2232 l_new_model_quantity := floor(l_cur_txn_source_qty * l_mo_line.model_quantity /
2233 l_cur_txn_source_req_qty);
2234 --l_new_model_quantity :=
2235 --floor(l_cur_txn_source_qty * l_mo_line.model_quantity/
2236 --l_mo_line.quantity);
2237
2238 --We keep model quantity and quantity in PUOM so should not
2239 --find it from order lines.
2240 If is_debug then
2241 print_debug('New model qty ' || l_new_model_quantity,
2242 'Inv_Pick_Release_Pub.Pick_Release');
2243 End If;
2244
2245 --loop through all move order lines for this ship model
2246 LOOP
2247 l_mo_line := l_mo_line_tbl(l_set_index);
2248 If is_debug then
2249 print_debug('SHIPMODEL-Current mo line:'||l_mo_line.line_id,
2250 'Inv_Pick_Release_Pub.Pick_Release');
2251 End If;
2252
2253 --find tree id. If item is unit effective, get tree id from
2254 -- qtree_line_tbl. Else, get it from qtree_item_tbl.
2255 IF l_qtree_item_tbl(l_mo_line.inventory_item_id).unit_effective='Y'
2256 THEN
2257 l_tree_id :=
2258 l_qtree_line_tbl(l_mo_line.txn_source_line_id).tree_id;
2259 ELSE
2260 l_tree_id :=
2261 l_qtree_item_tbl(l_mo_line.inventory_item_id).tree_id;
2262 END IF;
2263
2264 -- only restore tree if it is currently in backup table.
2265 -- Tree would not be in backup table if current move order line
2266 -- was not allocated or if the tree had already been restored
2267 -- for a previous move order line.
2268 IF l_qtree_backup_tbl.EXISTS(l_tree_id) THEN
2269
2270 If is_debug then
2271 print_debug('Restoring Quantity Tree: ' || l_tree_id,
2272 'Inv_Pick_Release_Pub.Pick_Release');
2273 End If;
2274 inv_quantity_tree_pvt.restore_tree
2275 (x_return_status => l_api_return_status
2276 ,p_tree_id => l_tree_id
2277 ,p_backup_id => l_qtree_backup_tbl(l_tree_id)
2278 );
2279 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
2280 If is_debug then
2281 print_debug('Error in Restore_Tree',
2282 'Inv_Pick_Release_Pub.Pick_Release');
2283 End If;
2284 raise FND_API.G_EXC_ERROR;
2285 elsif l_api_return_status=FND_API.G_RET_STS_UNEXP_ERROR then
2286 If is_debug then
2287 print_debug('Unexpected error in Restore_tree',
2288 'Inv_Pick_Release_Pub.Pick_Release');
2289 End If;
2290 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2291 end if;
2292
2293 --delete entry, so we don't restore tree more than once
2294 l_qtree_backup_tbl.DELETE(l_tree_id);
2295
2296 END IF;
2297
2298
2299 -- whenever line changes, find sales order quantity for
2300 -- this line
2301 IF l_set_txn_source_line_Id IS NULL OR
2302 l_set_txn_source_line_id <> l_mo_line.txn_source_line_id
2303 THEN
2304 l_set_txn_source_line_id := l_mo_line.txn_source_line_id;
2305 If is_debug then
2306 print_debug('OE Line: ' || l_set_txn_source_line_id,
2307 'Inv_Pick_Release_Pub.Pick_Release');
2308 End If;
2309
2310 -- if we already got the qty, don't get it again
2311 IF l_set_txn_source_line_id = l_cur_txn_source_line_id Then
2312 l_set_txn_source_req_qty := l_cur_txn_source_req_qty;
2313 ELSE
2314 BEGIN
2315 -- Bug 3340502, fetching order_quantity_uom in variable
2316 -- l_txn_source_line_uom instead of l_set_txn_source_uom
2317 SELECT ordered_quantity, order_quantity_uom
2318 INTO l_set_txn_source_req_qty, l_txn_source_line_uom
2319 FROM OE_ORDER_LINES_ALL
2320 WHERE line_id = l_set_txn_source_line_id;
2321 EXCEPTION
2322 WHEN NO_DATA_FOUND then
2323 If is_debug then
2324 print_debug('No Order Line Quantity found',
2325 'Inv_Pick_Release_Pub.Pick_Release');
2326 End If;
2327 ROLLBACK TO Pick_Release_PUB;
2328 FND_MESSAGE.SET_NAME('INV','INV_DELIV_INFO_MISSING');
2329 FND_MSG_PUB.Add;
2330 RAISE fnd_api.g_exc_unexpected_error;
2331 END;
2332
2333 --convert to primary quantity
2334 if l_txn_source_line_uom <>
2335 l_primary_uom_tbl(l_mo_line.inventory_item_id) then
2336
2337 l_cur_txn_source_req_qty :=
2338 inv_convert.inv_um_convert(
2339 l_mo_line.inventory_item_id
2340 ,NULL
2341 ,l_cur_txn_source_req_qty
2342 ,l_txn_source_line_uom
2343 ,l_primary_uom_tbl(l_mo_line.inventory_item_id)
2344 ,NULL
2345 ,NULL);
2346 end if;
2347 END IF;
2348
2349 -- based on new model quantity, find new move order
2350 -- line quantity
2351 -- l_set_new_req_qty := (l_mo_line.quantity *
2352 -- l_new_model_quantity
2353 -- / l_mo_line.model_quantity);
2354
2355 l_set_new_req_qty := l_set_txn_source_req_qty *
2356 l_new_model_quantity /
2357 l_mo_line.model_quantity;
2358
2359 If is_debug then
2360 print_debug('New req qty: ' || l_set_new_req_qty,
2361 'Inv_Pick_Release_Pub.Pick_Release');
2362 End If;
2363 END IF;
2364
2365 -- set new move order line quantity
2366 IF l_set_new_req_qty >= l_mo_line.quantity THEN
2367 l_new_line_quantity := l_mo_line.quantity;
2368 ELSE
2369 -- if new line quantity < previous line qty,
2370 -- backorder
2371 l_new_line_quantity := l_set_new_req_qty;
2372
2373 If is_debug then
2374 print_debug('New line qty: ' || l_new_line_quantity,
2375 'Inv_Pick_Release_Pub.Pick_Release');
2376 End If;
2377
2378 l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2379 If NOT l_return_value Then
2380 If is_debug then
2381 print_debug('Error setting cache for delivery line',
2382 'INV_Pick_Release_Pub.Pick_Release');
2383 End If;
2384 RAISE fnd_api.g_exc_unexpected_error;
2385 End If;
2386 l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2387 l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2388 l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2389 l_released_status := INV_CACHE.wdd_rec.released_status;
2390
2391 --Call Update_Shipping_Attributes to backorder detail line
2392 l_shipping_attr(1).source_header_id := l_source_header_id;
2393 l_shipping_attr(1).source_line_id := l_source_line_id;
2394 l_shipping_attr(1).ship_from_org_id := l_mo_line.organization_id;
2395 l_shipping_attr(1).released_status := l_released_status;
2396 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
2397 l_shipping_attr(1).action_flag := 'B';
2398 l_shipping_attr(1).cycle_count_quantity := l_mo_line.quantity - l_new_line_quantity;
2399 l_shipping_attr(1).subinventory := l_mo_line.from_subinventory_code;
2400 l_shipping_attr(1).locator_id := l_mo_line.from_locator_id;
2401 l_smc_backorder_det_tbl(1) :=l_shipping_attr(1);
2402 -- 2509322: Earlier whenever a component in a model is short we
2403 -- backorder all
2404 -- components to the difference of new model quantity and original
2405 -- and repick release all.But if many components are short then
2406 -- we end up splitting many delivery details.
2407 -- Now avoided multiple splits by storing back order details
2408 -- and do only once.
2409
2410 Store_smc_bo_details(x_return_status => l_api_return_status,
2411 back_order_det_tbl =>l_smc_backorder_det_tbl);
2412 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
2413 If is_debug then
2414 print_debug(' return error E from Store_smc_bo_details',
2415 'Inv_Pick_Release_Pub.Pick_Release');
2416 End If;
2417 l_smc_backorder_det_tbl.DELETE;
2418 raise FND_API.G_EXC_ERROR;
2419 elsif l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2420 If is_debug then
2421 print_debug(' return error U from Store_smc_bo_details',
2422 'Inv_Pick_Release_Pub.Pick_Release');
2423 End If;
2424 l_smc_backorder_det_tbl.DELETE;
2425 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2426 end if;
2427
2428 l_smc_backorder_det_tbl.DELETE;
2429
2430
2431 --WSH_INTERFACE.Update_Shipping_Attributes
2432 --(p_source_code => 'INV',
2433 --p_changed_attributes => l_shipping_attr,
2434 --x_return_status => l_api_return_status
2435 --);
2436
2437 END IF;
2438
2439 l_set_new_req_qty := l_set_new_req_qty - l_new_line_quantity;
2440
2441 -- Update mo line with new quantity and model quantity;
2442 --If mo line quantity is 0, close the move order line
2443 IF l_new_line_quantity = 0 THEN
2444 update mtl_txn_request_lines
2445 set quantity = 0
2446 ,quantity_detailed = 0
2447 ,line_status = 5
2448 ,status_date =sysdate --BUG 7560455
2449 ,model_quantity = l_new_model_quantity
2450 where line_id = l_mo_line.line_id;
2451 l_mo_line_tbl(l_set_index).quantity_detailed := 0;
2452 l_mo_line_tbl(l_set_index).line_status := 5;
2453 ELSE
2454 update mtl_txn_request_lines
2455 set quantity = l_new_line_quantity
2456 ,quantity_detailed = NULL
2457 ,model_quantity = l_new_model_quantity
2458 where line_id = l_mo_line.line_id;
2459 l_mo_line_tbl(l_set_index).quantity_detailed := NULL;
2460 END IF;
2461 l_mo_line_tbl(l_set_index).quantity := l_new_line_quantity;
2462
2463 -- Bug# 3085075. Commented out the next line so that model qty does not
2464 -- become fractional.
2465 --l_mo_line_tbl(l_set_index).model_quantity := l_new_model_quantity;
2466
2467 If is_debug then
2468 print_debug('Finished Updating Mo Line',
2469 'Inv_Pick_Release_Pub.Pick_Release');
2470 End If;
2471
2472 -- Exit if there are no more move order lines to detail
2473 -- or when the next move order is not for the same ship model.
2474 -- Exit before updating the pick_release_status tbl
2475 -- for the last line. The table gets updated for the last
2476 -- line later.
2477 -- l_set_index should always be equal to the last line
2478 -- in the current ship set, so that the logic at the
2479 -- end of the outer loop works correctly.
2480 EXIT WHEN l_mo_line_tbl.LAST = l_set_index;
2481 l_set_index := l_mo_line_tbl.NEXT(l_set_index);
2482 if nvl(l_mo_line_tbl(l_set_index).ship_model_id,-99)
2483 <> l_cur_ship_model_id then
2484 l_set_index := l_mo_line_tbl.PRIOR(l_set_index);
2485 EXIT;
2486 end if;
2487
2488 -- Only update status table if model_quantity = 0;
2489 -- If model quantity <> 0, then we loop through all these
2490 -- records again, re-detailing them for the new line quantities.
2491 -- The status table will get populated at that point.
2492 -- But, if model_quantity = 0, then we don't look at these
2493 -- mo lines again, since the quantity for all the lines = 0.
2494 -- We have to update the status now.
2495 if l_new_model_quantity = 0 then
2496
2497 --If next line is for same ship set, update output table
2498 l_set_process := l_set_process + 1;
2499 x_pick_release_status(l_set_process).mo_line_id := l_mo_line.line_id;
2500 x_pick_release_status(l_set_process).return_status := l_api_return_status;
2501 x_pick_release_status(l_set_process).detail_rec_count := 0;
2502 If is_debug then
2503 print_debug('x_pick_release_status ' || l_set_process ||
2504 ' mo_line_id = ' || l_mo_line.line_id,
2505 'Pick_release_Pub');
2506 print_debug('x_pick_release_status ' || l_set_process ||
2507 ' return_status = '|| l_api_return_status,
2508 'Pick_release_Pub');
2509 print_debug('x_pick_release_status ' || l_set_process ||
2510 ' detail_rec_count = 0', 'Pick_Release_Pub');
2511 End If;
2512 end if;
2513 END LOOP;
2514
2515 --reset global values, since we are relooping
2516 --Bug 2706558 - reset cur_txn_source_qty and cur_txn_source_line_id
2517 l_cur_ship_model_id := NULL;
2518 l_qtree_backup_tbl.DELETE;
2519 l_cur_txn_source_qty := 0;
2520 -- HW INVCONV Added Qty2
2521 l_cur_txn_source_qty2 :=0;
2522 l_cur_txn_source_line_id := NULL;
2523
2524 -- If new model quantity = 0, then we backordered all of the
2525 -- lines. No need to try to redetail. Set line index
2526 -- to point at the last mo line we backordered.
2527 IF l_new_model_quantity = 0 THEN
2528 l_line_index := l_set_index;
2529 l_processed_row_count := l_set_process;
2530 l_detail_rec_count := 0;
2531
2532 If is_debug then
2533 print_debug('Backordered all lines with this Ship Model Id',
2534 'Inv_Pick_Release_Pub.Pick_Release');
2535 End If;
2536 Backorder_SMC_DETAILS(l_api_return_status ,
2537 x_msg_data ,
2538 x_msg_count
2539 );
2540 if( l_api_return_status = FND_API.G_RET_STS_ERROR ) then
2541 If is_debug then
2542 print_debug('return error E from Backorder_SMC_DETAILS',
2543 'Inv_Pick_Release_Pub.Pick_Release');
2544 End If;
2545 raise FND_API.G_EXC_ERROR;
2546 elsif l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2547 If is_debug then
2548 print_debug('return error U from Backorder_SMC_DETAILS',
2549 'Inv_Pick_Release_Pub.Pick_Release');
2550 End If;
2551 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2552 end if;
2553
2554 ELSE
2555
2556 -- If new model quantity <> 0, then we want to loop
2557 -- through these lines again. Set line index to
2558 -- point to the first record with this ship
2559 -- model id. Also we need to turn on the reloop flag so that
2560 -- the line index is not incremented later.
2561 l_line_index := l_start_index;
2562 l_processed_row_count := l_start_process;
2563 l_model_reloop := TRUE;
2564 END IF;
2565
2566 ELSE
2567 -- Move order line is not fully allocated and it is not part of a
2568 -- shipset or ship model
2569
2570
2571 -- 8519286
2572 /* l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id); */
2573 l_return_value := FALSE;
2574
2575 BEGIN
2576 SELECT *
2577 INTO INV_CACHE.wdd_rec
2578 FROM WSH_DELIVERY_DETAILS
2579 WHERE move_order_line_id = l_mo_line.line_id
2580 AND NVL(released_status, 'Z') NOT IN ('Y','C');
2581 l_return_value := TRUE;
2582 EXCEPTION
2583 WHEN NO_DATA_FOUND THEN
2584 l_return_value := FALSE;
2585 WHEN OTHERS THEN
2586 l_return_value := FALSE;
2587 END;
2588 -- End of 8519286
2589
2590
2591 -- Retrieve the WDD record corresponding to the current MOL
2592 l_return_value := INV_CACHE.set_wdd_rec(l_mo_line.line_id);
2593 IF (NOT l_return_value) THEN
2594 IF (is_debug) THEN
2595 print_debug('Error setting cache for delivery line',
2596 'INV_Pick_Release_Pub.Pick_Release');
2597 END IF;
2598 RAISE fnd_api.g_exc_unexpected_error;
2599 END IF;
2600 l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2601 l_source_header_id := INV_CACHE.wdd_rec.source_header_id;
2602 l_source_line_id := INV_CACHE.wdd_rec.source_line_id;
2603 l_released_status := INV_CACHE.wdd_rec.released_status;
2604
2605 -- Bug# 4258360: If allocation mode = N (Prioritize Inventory), instead of simply
2606 -- backordering the unallocated quantity on the WDD record, we need to perform
2607 -- new logic to support crossdocking. The WDD record needs to be split or updated
2608 -- properly so we can still try to allocate material through crossdocking later.
2609 -- {{
2610 -- Run PR in Prioritize INV mode and ensure that one WDD
2611 -- does not get allocated at all. That WDD should get
2612 -- x-docked and later deliveries created for same.
2613 -- }}
2614 -- {{
2615 -- Run PR in Prioritize INV mode and ensure that one WDD
2616 -- gets partially allocated. That WDD should get split and
2617 -- x-docked and later deliveries created for the new WDD.
2618 -- Also ensure that the original WDD has the correct qty.
2619 -- }}
2620 IF (l_allocation_method = g_prioritize_inventory) AND (p_wsh_release_table.COUNT > 0) THEN
2621 IF (l_transaction_quantity = 0) THEN
2622
2623
2624 -- Move order line is not allocated at all.
2625 -- Do not backorder the current WDD line yet since crossdocking can still
2626 -- potentially allocate material for this. Update the WDD record to null
2627 -- out the move_order_line_id column and reset the released_status to the
2628 -- original value from the corresponding record in p_wsh_release_table.
2629
2630
2631 -- R12.1 replenishment Project 6681109/6710368
2632 -- changes based ON p_dynamic_replenishment
2633 IF (is_debug) THEN
2634 print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment,
2635 'INV_Pick_Release_Pub.Pick_Release');
2636 END IF;
2637
2638
2639 l_detail_info_tab(1).delivery_detail_id := l_delivery_detail_id;
2640 l_detail_info_tab(1).released_status :=
2641 p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status;
2642
2643 IF NVL(p_dynamic_replenishment,'N') = 'Y' THEN
2644
2645 IF (is_debug) THEN
2646 print_debug('Mark WDD repl_status as RR','INV_Pick_Release_Pub.Pick_Release');
2647 END IF;
2648 --if qty is available somewhere in the org, we will try to replenish it first
2649 -- mark the demand lines for replenishment requested status
2650 l_detail_info_tab(1).replenishment_status := 'R';
2651 l_in_rec.caller := 'WMS_REP';
2652 l_in_rec.action_code := 'UPDATE';
2653
2654 ELSE
2655
2656 -- When calling the Shipping package WSH_INTERFACE_EXT_GRP instead of
2657 -- WSH_INTERFACE_GRP, we have to pass a G_MISS_NUM value instead of NULL
2658 -- in order to properly NULL out the move_order_line_id value.
2659
2660 l_detail_info_tab(1).move_order_line_id := fnd_api.g_miss_num;
2661
2662 -- Caller needs to be WMS_XDOCK% in order for shipping to allow this action
2663 l_in_rec.caller := 'WMS_XDOCK.INVPPICB';
2664 l_in_rec.action_code := 'UPDATE';
2665
2666 END IF;
2667
2668 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
2669 (p_api_version_number => 1.0,
2670 p_init_msg_list => fnd_api.g_false,
2671 p_commit => fnd_api.g_false,
2672 x_return_status => l_api_return_status,
2673 x_msg_count => x_msg_count,
2674 x_msg_data => x_msg_data,
2675 p_detail_info_tab => l_detail_info_tab,
2676 p_in_rec => l_in_rec,
2677 x_out_rec => l_out_rec
2678 );
2679
2680 IF (l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
2681 IF (is_debug) THEN
2682 print_debug('Error returned from Create_Update_Delivery_Detail API',
2683 'Inv_Pick_Release_Pub.Pick_Release');
2684 END IF;
2685 RAISE FND_API.G_EXC_ERROR;
2686 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2687 IF (is_debug) THEN
2688 print_debug('Unexpected errror from Create_Update_Delivery_Detail API',
2689 'Inv_Pick_Release_Pub.Pick_Release');
2690 END IF;
2691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2692 END IF;
2693
2694 ELSE -- means (l_transaction_quantity <> 0)
2695 -- Move order line is partially allocated
2696 -- Split the WDD line with the partial quantity allocated. The new WDD
2697 -- line with the unallocated quantity will retain the original released_status
2698 -- on the original WDD record in p_wsh_release_table
2699 l_detail_id_tab(1) := l_delivery_detail_id;
2700 -- Caller needs to be WMS_XDOCK% in order for shipping to allow this action
2701 l_action_prms.caller := 'WMS_XDOCK.INVPPICB';
2702 l_action_prms.action_code := 'SPLIT-LINE';
2703 l_action_prms.split_quantity :=
2704 (l_quantity - NVL(l_quantity_delivered,0)) - l_transaction_quantity;
2705 l_action_prms.split_quantity2 :=
2706 (l_quantity2 - NVL(l_quantity2_delivered,0)) - l_transaction_quantity2;
2707
2708 WSH_INTERFACE_GRP.Delivery_Detail_Action
2709 (p_api_version_number => 1.0,
2710 p_init_msg_list => fnd_api.g_false,
2711 p_commit => fnd_api.g_false,
2712 x_return_status => l_api_return_status,
2713 x_msg_count => x_msg_count,
2714 x_msg_data => x_msg_data,
2715 p_detail_id_tab => l_detail_id_tab,
2716 p_action_prms => l_action_prms,
2717 x_action_out_rec => l_action_out_rec
2718 );
2719
2720 IF (l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
2721 IF (is_debug) THEN
2722 print_debug('Error returned from Split Delivery_Detail_Action API',
2723 'Inv_Pick_Release_Pub.Pick_Release');
2724 END IF;
2725 RAISE FND_API.G_EXC_ERROR;
2726 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2727 IF (is_debug) THEN
2728 print_debug('Unexpected errror from Split Delivery_Detail_Action API',
2729 'Inv_Pick_Release_Pub.Pick_Release');
2730 END IF;
2731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2732 END IF;
2733
2734 l_xdock_index := l_action_out_rec.result_id_tab.FIRST;
2735 l_split_delivery_detail_id := l_action_out_rec.result_id_tab(l_xdock_index);
2736
2737 -- Update the split WDD line for the unallocated quantity to null out the
2738 -- move_order_line_id column and reset the released_status to the original
2739 -- value in the corresponding WDD record (original one) in p_wsh_release_table
2740 l_detail_info_tab(1).delivery_detail_id := l_split_delivery_detail_id;
2741 l_detail_info_tab(1).released_status :=
2742 p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status;
2743 -- When calling the Shipping package WSH_INTERFACE_EXT_GRP instead of
2744 -- WSH_INTERFACE_GRP, we have to pass a G_MISS_NUM value instead of NULL
2745 -- in order to properly NULL out the move_order_line_id value.
2746 --l_detail_info_tab(1).move_order_line_id := NULL;
2747 l_detail_info_tab(1).move_order_line_id := fnd_api.g_miss_num;
2748
2749
2750 IF NVL(p_dynamic_replenishment,'N') = 'Y' THEN
2751 IF (is_debug) THEN
2752 print_debug(' Mark repl_status of WDD as RR','INV_Pick_Release_Pub.Pick_Release');
2753 END IF;
2754 -- mark the new split demand lines for replenishment requested status
2755 l_detail_info_tab(1).replenishment_status := 'R';
2756 l_in_rec.caller := 'WMS_REP';
2757 ELSE
2758 -- Caller needs to be WMS_XDOCK% in order for shipping to allow this action
2759 l_in_rec.caller := 'WMS_XDOCK.INVPPICB';
2760 END IF;
2761
2762
2763 l_in_rec.action_code := 'UPDATE';
2764
2765 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
2766 (p_api_version_number => 1.0,
2767 p_init_msg_list => fnd_api.g_false,
2768 p_commit => fnd_api.g_false,
2769 x_return_status => l_api_return_status,
2770 x_msg_count => x_msg_count,
2771 x_msg_data => x_msg_data,
2772 p_detail_info_tab => l_detail_info_tab,
2773 p_in_rec => l_in_rec,
2774 x_out_rec => l_out_rec
2775 );
2776
2777 IF (l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
2778 IF (is_debug) THEN
2779 print_debug('Error returned from Create_Update_Delivery_Detail API',
2780 'Inv_Pick_Release_Pub.Pick_Release');
2781 END IF;
2782 RAISE FND_API.G_EXC_ERROR;
2783 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2784 IF (is_debug) THEN
2785 print_debug('Unexpected errror from Create_Update_Delivery_Detail API',
2786 'Inv_Pick_Release_Pub.Pick_Release');
2787 END IF;
2788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2789 END IF;
2790
2791 -- Insert the split WDD line into p_wsh_release_table.
2792 -- The split WDD release record should be the same as the original one with
2793 -- only the following fields modified: delivery_detail_id, move_order_line_id
2794 -- replenishment_status, and requested_quantity fields
2795 l_split_wdd_rel_rec := p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id));
2796 l_split_wdd_rel_rec.delivery_detail_id := l_split_delivery_detail_id;
2797 l_split_wdd_rel_rec.move_order_line_id := NULL;
2798 l_split_wdd_rel_rec.requested_quantity :=
2799 (l_quantity - NVL(l_quantity_delivered,0)) - l_transaction_quantity;
2800 l_split_wdd_rel_rec.requested_quantity2 :=
2801 (l_quantity2 - NVL(l_quantity2_delivered,0)) - l_transaction_quantity2;
2802
2803
2804 l_xdock_index := p_wsh_release_table.LAST + 1;
2805 p_wsh_release_table(l_xdock_index) := l_split_wdd_rel_rec;
2806
2807 -- Insert a new record into p_trolin_delivery_ids and p_del_detail_id
2808 -- for the split WDD line created.
2809 -- UPDATE: Do not need to do this anymore. The delivery tables passed in by
2810 -- Shipping are used for storing crossdocked WDD lines. If this split line
2811 -- is later allocated from Crossdocking, the crossdock API will insert them
2812 -- into the delivery tables.
2813 /*l_xdock_index := NVL(p_del_detail_id.LAST, 0) + 1;
2814 p_del_detail_id(l_xdock_index) := l_split_wdd_rel_rec.delivery_detail_id;
2815 p_trolin_delivery_ids(l_xdock_index) := l_split_wdd_rel_rec.delivery_id;*/
2816
2817 -- Update the original WDD line in p_wsh_release_table with
2818 -- released_status = 'S' and the corresponding allocated quantity
2819 l_xdock_index := l_wdd_index_tbl(l_delivery_detail_id);
2820 p_wsh_release_table(l_xdock_index).released_status := 'S';
2821 p_wsh_release_table(l_xdock_index).requested_quantity := l_transaction_quantity;
2822 p_wsh_release_table(l_xdock_index).requested_quantity2 := l_transaction_quantity2;
2823
2824 END IF; -- for IF (l_transaction_quantity = 0) THEN
2825 ELSE
2826 -- Original code which is used for allocation mode = I (Inventory Only)
2827 -- and X (Prioritize Crossdock).
2828 -- NOTE: I believe cycle_count_quantity should technically be:
2829 -- (l_quantity - NVL(l_quantity_delivered,0)) - l_transaction_quantity
2830 -- Same thing goes for cycle_count_quantity2. Since the transaction quantity
2831 -- variable already takes the quantity delivered into account, the requested
2832 -- quantity on the MOL (l_quantity) should do the same. Not making the changes
2833 -- yet but leaving the comments here for the future. -etam
2834 l_shipping_attr(1).source_header_id := l_source_header_id;
2835 l_shipping_attr(1).source_line_id := l_source_line_id;
2836
2837 l_shipping_attr(1).ship_from_org_id := l_mo_line.organization_id;
2838 l_shipping_attr(1).released_status := l_released_status;
2839 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
2840
2841 select count(1) INTO l_reservation_exists
2842 from mtl_reservations mr
2843 WHERE MR.DEMAND_SOURCE_LINE_ID = l_source_line_id
2844 and MR.DEMAND_SOURCE_HEADER_ID =
2845 inv_salesorder.get_salesorder_for_oeheader(l_source_header_id)
2846 and MR.demand_source_type_id = decode(INV_CACHE.wdd_rec.source_document_type_id, 10, 8, 2)
2847 and MR.SUBINVENTORY_CODE IS NOT NULL --locator is not needed
2848 and (nvl(mr.staged_flag,'N') = 'N' and nvl(mr.detailed_quantity,0) = 0);
2849
2850 IF is_debug THEN
2851 print_debug('The value of l_reservation_exists is - ' ||l_reservation_exists,'Inv_Pick_Release_Pub.Pick_Release');
2852 END IF;
2853
2854 --Note: Inside the API Update_Shipping_Attributes, wdd is split and qty are backordered
2855 --in case action_flag is 'B'. Backorder qty is passed
2856 --from INV as cycle_count_quantity below and requested qty is obtained from WDD table by
2857 --shipping. Now in case of 'R', shipping team will make change
2858 -- to mark those lines as Replenishment Requested instead of backordering them
2859
2860
2861 IF (l_reservation_exists = 0 and NVL(p_dynamic_replenishment,'N') = 'Y') THEN --BUG13604664
2862 IF is_debug THEN
2863 print_debug('Marking line status as RR',
2864 'Inv_Pick_Release_Pub.Pick_Release');
2865 END IF;
2866 l_shipping_attr(1).action_flag := 'R';
2867 ELSE
2868 l_shipping_attr(1).action_flag := 'B';
2869 END IF;
2870
2871 --l_shipping_attr(1).cycle_count_quantity := (l_quantity - l_transaction_quantity);
2872 -- HW INVCONV - Added Qty2
2873 --l_shipping_attr(1).cycle_count_quantity2 := (l_quantity2 - l_transaction_quantity2);
2874
2875 -- End of 8519286
2876 l_shipping_attr(1).cycle_count_quantity := (l_quantity - NVL(l_quantity_delivered,0) - l_transaction_quantity);
2877 l_shipping_attr(1).cycle_count_quantity2 := (l_quantity2 - NVL(l_quantity2_delivered,0) - l_transaction_quantity2);
2878 -- End of 8519286
2879
2880 --MUOM Fulfillment Project
2881 IF ( l_fulfill_base ='S') THEN
2882 l_shipping_attr(1).cycle_count_quantity := inv_convert.inv_um_convert(
2883 item_id => l_mo_line.inventory_item_id
2884 , PRECISION => NULL
2885 , from_quantity => l_shipping_attr(1).cycle_count_quantity2
2886 , from_unit => l_mo_line.secondary_uom
2887 , to_unit => l_primary_uom_tbl(l_mo_line.inventory_item_id)
2888 , from_name => NULL
2889 , to_name => NULL
2890 );
2891 END IF;
2892
2893
2894 l_shipping_attr(1).subinventory := l_mo_line.from_subinventory_code;
2895 l_shipping_attr(1).locator_id := l_mo_line.from_locator_id;
2896
2897 WSH_INTERFACE.Update_Shipping_Attributes
2898 (p_source_code => 'INV',
2899 p_changed_attributes => l_shipping_attr,
2900 x_return_status => l_api_return_status
2901 );
2902 IF (l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
2903 IF is_debug THEN
2904 print_debug('return error from update shipping attributes',
2905 'Inv_Pick_Release_Pub.Pick_Release');
2906 END IF;
2907 RAISE FND_API.G_EXC_ERROR;
2908 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2909 IF is_debug THEN
2910 print_debug('return error from update shipping attributes',
2911 'Inv_Pick_Release_Pub.Pick_Release');
2912 END IF;
2913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2914 END IF;
2915 END IF; -- for IF (l_allocation_method = g_prioritize_inventory) AND (p_wsh...
2916
2917 -- HW INVCONV Added secondary_quantity
2918 -- Update the current move order line depending on how much quantity
2919 -- was successfully allocated from inventory.
2920 IF (l_transaction_quantity = 0) THEN
2921 -- Close the move order line created since no quantity was allocated
2922 UPDATE mtl_txn_request_lines
2923 SET line_status = 5,
2924 status_date =sysdate, --BUG 7560455
2925 quantity = l_transaction_quantity,
2926 secondary_quantity = DECODE(secondary_quantity, fnd_api.g_miss_num, NULL,
2927 l_transaction_quantity2)
2928 WHERE line_id = l_mo_line.line_id;
2929
2930 -- Bug# 4258360: For allocation modes of I (Inventory Only) or
2931 -- X (Prioritize Crossdock), allocation (both from inventory and crossdock)
2932 -- has completed. We need to remove the backordered lines from
2933 -- p_trolin_delivery_ids and p_del_detail_id tables so deliveries are not
2934 -- autocreated for them later on by Shipping. Store the lines which did
2935 -- not get any material allocated at all in PLSQL table l_backordered_wdd_tbl.
2936 IF (l_allocation_method IN (g_inventory_only, g_prioritize_crossdock)) AND
2937 (p_wsh_release_table.COUNT > 0) THEN
2938 -- UPDATE: Do not need to do this anymore. The delivery tables inputted from
2939 -- Shipping are used only to store crossdocked WDD lines. They will be
2940 -- empty initially. Instead, set the released_status for the line to be 'B'
2941 -- in the inputted release table.
2942 --l_backordered_wdd_tbl(l_delivery_detail_id) := TRUE;
2943 p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status := 'B';
2944 END IF;
2945 ELSE
2946 --Bug 9199956 The primary quantity in mtrl should match the quantity in primary uom. Since for Pick Wave move orders
2947 --The uom is always primary uom setting it to quantity value.
2948 -- Update the move order line to the partial quantity that was allocated
2949 UPDATE mtl_txn_request_lines
2950 SET quantity = l_transaction_quantity,
2951 secondary_quantity = DECODE(secondary_quantity, fnd_api.g_miss_num, NULL,
2952 l_transaction_quantity2)
2953 ,primary_quantity=l_transaction_quantity
2954 WHERE line_id = l_mo_line.line_id;
2955 END IF;
2956
2957 END IF; --cur ship set id
2958 ELSIF (((l_transaction_quantity = (l_quantity - NVL(l_quantity_delivered,0))) AND l_fulfill_base<>'S') OR
2959 ((l_transaction_quantity2 = (l_quantity2 - NVL(l_quantity2_delivered,0))) AND l_fulfill_base = 'S'))THEN
2960 -- Bug# 4258360: If allocation mode = N (Prioritize Inventory), we need to update
2961 -- the corresponding WDD record in p_wsh_release_table to a released_status of 'S'.
2962 -- This is so the crossdock API (which will be called later on) knows the WDD record has
2963 -- been fully allocated already. This is added for the R12 Planned Crossdocking project.
2964 -- {{
2965 -- Run PR in prioritize INV mode and ensure entire WDD
2966 -- gets allocated. That WDD should not be re-allocated for
2967 -- x-docking.
2968 -- }}
2969 IF (l_allocation_method = g_prioritize_inventory) THEN
2970 -- Retrieve the WDD record associated with the current MOL
2971 IF (NOT INV_CACHE.set_wdd_rec(l_mo_line.line_id)) THEN
2972 IF (is_debug) THEN
2973 print_debug('Error setting cache for WDD delivery line',
2974 'INV_Pick_Release_Pub.Pick_Release');
2975 END IF;
2976 RAISE fnd_api.g_exc_unexpected_error;
2977 END IF;
2978 l_delivery_detail_id := INV_CACHE.wdd_rec.delivery_detail_id;
2979
2980 -- Update WDD record in release table with a released status of 'S'
2981 IF (p_wsh_release_table.COUNT > 0) THEN
2982 p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status := 'S';
2983 END IF;
2984 END IF;
2985
2986 END IF; -- transaction quantity < quantity
2987 END IF; --line status = 5
2988
2989 -- If there is no need to reloop for processsing partial quantities
2990 -- FOR ship models
2991 IF (l_model_reloop <> TRUE) THEN
2992 -- Populate return status structure with the processing status of
2993 -- this row
2994 l_processed_row_count := l_processed_row_count + 1;
2995 x_pick_release_status(l_processed_row_count).mo_line_id := l_mo_line.line_id;
2996 x_pick_release_status(l_processed_row_count).return_status := l_api_return_status;
2997 x_pick_release_status(l_processed_row_count).detail_rec_count := l_detail_rec_count;
2998 If is_debug then
2999 print_debug('x_pick_release_status ' || l_processed_row_count ||
3000 ' mo_line_id = ' ||
3001 x_pick_release_status(l_processed_row_count).mo_line_id,
3002 'Pick_release_Pub');
3003 print_debug('x_pick_release_status ' || l_processed_row_count ||
3004 ' return_status = ' ||
3005 x_pick_release_status(l_processed_row_count).return_status,
3006 'Pick_release_Pub');
3007 print_debug('x_pick_release_status ' || l_processed_row_count ||
3008 ' detail_rec_count = ' ||
3009 x_pick_release_status(l_processed_row_count).detail_rec_count,
3010 'Pick_Release_Pub');
3011 print_Debug('detail record count is ' ||
3012 x_pick_release_status(l_processed_row_count).detail_rec_count,
3013 'Inv_Pick_Release_Pub.Pick_Release');
3014 End If;
3015 END IF;
3016 l_detail_rec_count := 0;
3017 --Update the Pick Release API's return status to an error if the line could
3018 -- not be processed. Note that processing of other lines will continue.
3019 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3020 l_api_return_status = fnd_api.g_ret_sts_error THEN
3021 x_return_status := fnd_api.g_ret_sts_error;
3022 END IF;
3023 END IF; -- mo line return status <> ERROR
3024
3025 -- Bug 2776309
3026 -- If model reloop is required then do not exit.
3027 -- Exit in the case where the new model quantity = 0.
3028 EXIT WHEN l_line_index = l_mo_line_tbl.last AND (l_model_reloop <> TRUE);
3029
3030 IF (l_model_reloop <> TRUE) THEN
3031 l_line_index := l_mo_line_tbl.NEXT(l_line_index);
3032 ELSE
3033 --Don't increment the line index and turn off the reloop variable
3034 l_model_reloop := FALSE;
3035 END IF;
3036 END LOOP;
3037
3038 IF is_debug then
3039 print_debug('after calling inv_pick_release_pvt',
3040 'Inv_Pick_Release_Pub.Pick_Release');
3041 END IF;
3042
3043 -- To ensure that lock_flag should not get updated to Y unnecessarily i.e., only change needed only when high volume related code executes
3044 -- WMS installed should be true
3045 -- Auto Pick Confirm should be no
3046 -- The profile value of WMS_ASSIGN_TASK_TYPE should be NULL or YES(default)
3047 -- Value of p_skip_cartonization passed as TRUE only when parallel pick release happens
3048 IF l_wms_installed AND p_auto_pick_confirm = 2 AND l_do_cartonization = 1 AND p_skip_cartonization = TRUE THEN
3049 BEGIN
3050 IF is_debug THEN
3051 print_debug('Updating lock_flag of MMTT records','Inv_Pick_Release_Pub.Pick_Release');
3052 END IF;
3053 FORALL ii IN l_mol_id_tbl.FIRST..l_mol_id_tbl.LAST
3054 update mtl_material_transactions_temp
3055 set lock_flag = 'Y'
3056 where move_order_line_id =l_mol_id_tbl(ii);
3057 EXCEPTION
3058 WHEN OTHERS THEN
3059 IF is_debug THEN
3060 print_debug('Error in updating MMTT records lock_flag: ' || sqlerrm
3061 ,'Inv_Pick_Release_Pub.Pick_Release');
3062 END IF;
3063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3064 END;
3065 END IF;
3066
3067 -- Bug 4349602: Deleting Move Order Lines which are not allocated
3068 BEGIN
3069 IF is_debug THEN
3070 print_debug('Deleting MOLs in status 5','Inv_Pick_Release_Pub.Pick_Release');
3071 END IF;
3072 FORALL ii IN l_mol_id_tbl.FIRST..l_mol_id_tbl.LAST
3073 DELETE FROM mtl_txn_request_lines mtrl
3074 WHERE line_status = 5
3075 AND line_id = l_mol_id_tbl(ii)
3076 AND EXISTS
3077 ( SELECT 'x'
3078 FROM mtl_system_items msi
3079 WHERE msi.organization_id = mtrl.organization_id
3080 AND msi.inventory_item_id = mtrl.inventory_item_id
3081 AND NVL(msi.reservable_type,1) = 1
3082 );
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 IF is_debug THEN
3086 print_debug('Error in Deleting Move Order Lines: ' || sqlerrm
3087 ,'Inv_Pick_Release_Pub.Pick_Release');
3088 END IF;
3089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3090 END;
3091
3092 IF l_wms_installed THEN
3093 -- 09/14/2000 added call to cartonization api
3094 l_line_index := l_mo_line_tbl.FIRST;
3095 l_api_return_status := '';
3096 IF is_debug THEN
3097 print_Debug('l_mo_line_tbl.count is ' || l_mo_line_tbl.COUNT,
3098 'Inv_Pick_Release_pub.Pick_Release');
3099 END IF;
3100 -- Calling device integration api to set global var which will decide
3101 -- whether to process device request or not, if it is a WMS organization.
3102 IF wms_device_integration_pvt.wms_call_device_request IS NULL THEN
3103 -- Bug# 4491974
3104 -- Changed l_mo_line_tbl(1).organization_id to l_mo_line_tbl(l_line_index).organization_id
3105 -- since it is possible to have null value at 1 which will lead to ORA-1403 error.
3106 wms_device_integration_pvt.is_device_set_up(
3107 --l_mo_line_tbl(1).organization_id,
3108 l_mo_line_tbl(l_line_index).organization_id,
3109 11,
3110 l_api_return_status);
3111 END IF;
3112
3113 IF (l_do_cartonization = 1 AND NOT p_skip_cartonization) THEN --Added for bug3237702
3114 LOOP
3115 If is_debug then
3116 print_debug('headeR_id for line index ' || l_line_index || ' is ' ||
3117 l_mo_line_tbl(l_line_index).header_id,
3118 'Inv_Pick_Release_Pub.Pick_Release');
3119 End If;
3120
3121 IF l_line_index >= l_mo_line_tbl.COUNT THEN
3122 -- it's the last line in this group
3123 If is_debug then
3124 print_debug('calling cartonize api',
3125 'Inv_Pick_Release_Pub.Pick_Release');
3126 End If;
3127
3128 WMS_CARTNZN_PUB.cartonize
3129 (
3130 p_api_version => 1,
3131 p_init_msg_list => fnd_api.g_false,
3132 p_commit => fnd_api.g_false,
3133 p_validation_level => fnd_api.g_valid_level_full,
3134 x_return_status => l_api_return_status,
3135 x_msg_count => x_msg_count,
3136 x_msg_data => x_msg_data,
3137 p_out_bound => 'Y',
3138 p_org_id => l_mo_line_tbl(l_line_index).organization_id,
3139 p_move_order_header_id => l_mo_line_tbl(l_line_index).header_id
3140 );
3141
3142 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3143 l_api_return_status = fnd_api.g_ret_sts_error THEN
3144 If is_debug then
3145 print_debug('error from cartonize api',
3146 'Inv_Pick_Release_Pub.Pick_Release');
3147 print_debug('error count ' || x_msg_count,
3148 'Inv_Pick_Release_Pub.Pick_Release');
3149 print_debug('error msg ' || x_msg_data,
3150 'Inv_Pick_Release_Pub.Pick_Release');
3151 x_return_status := fnd_api.g_ret_sts_error;
3152 End If;
3153 ELSE -- patchset J bulk picking
3154 IF (WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) THEN
3155 IF (is_debug) THEN
3156 print_debug('PATCHSET J -- BULK PICKING --- START',
3157 'Inv_Pick_Release_Pub.Pick_Release');
3158 END IF;
3159 assign_pick_slip_number
3160 (x_return_status => l_api_return_status,
3161 x_msg_count => x_msg_count,
3162 x_msg_data => x_msg_data,
3163 p_move_order_header_id => l_mo_line_tbl(l_line_index).header_id,
3164 p_ps_mode => l_print_mode,
3165 p_grouping_rule_id => p_grouping_rule_id,
3166 p_allow_partial_pick => p_allow_partial_pick);
3167
3168 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3169 l_api_return_status = fnd_api.g_ret_sts_error THEN
3170 print_debug('error from assign_pick_slip_number api',
3171 'Inv_Pick_Release_Pub.Pick_Release');
3172 print_debug('error count ' || x_msg_count,
3173 'Inv_Pick_Release_Pub.Pick_Release');
3174 print_debug('error msg ' || x_msg_data,
3175 'Inv_Pick_Release_Pub.Pick_Release');
3176 x_return_status := fnd_api.g_ret_sts_error;
3177 END IF;
3178 IF (is_debug) THEN
3179 print_debug('PATCHSET J -- BULK PICKING --- END',
3180 'Inv_Pick_Release_Pub.Pick_Release');
3181 END IF;
3182 END IF;
3183 END IF;
3184 EXIT;
3185
3186 ELSIF l_mo_line_tbl(l_line_index).header_id =
3187 l_mo_line_tbl(l_mo_line_tbl.NEXT(l_line_index)).header_id THEN
3188 l_line_index := l_mo_line_tbl.NEXT(l_line_index); --Changed bug4102518;
3189 ELSE
3190 -- call cartonize API
3191 If is_debug then
3192 print_debug('calling cartonize api',
3193 'Inv_Pick_Release_Pub.Pick_Release');
3194 End If;
3195
3196 WMS_CARTNZN_PUB.cartonize
3197 (
3198 p_api_version => 1,
3199 p_init_msg_list => fnd_api.g_false,
3200 p_commit => fnd_api.g_false,
3201 p_validation_level => fnd_api.g_valid_level_full,
3202 x_return_status => l_api_return_status,
3203 x_msg_count => x_msg_count,
3204 x_msg_data => x_msg_data,
3205 p_out_bound => 'Y',
3206 p_org_id => l_mo_line_tbl(l_line_index).organization_id,
3207 p_move_order_header_id => l_mo_line_tbl(l_line_index).header_id
3208 );
3209
3210 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3211 l_api_return_status = fnd_api.g_ret_sts_error THEN
3212 If is_debug then
3213 print_debug('error from cartonize api',
3214 'Inv_Pick_Release_Pub.Pick_Release');
3215 print_debug('error count ' || x_msg_count,
3216 'Inv_Pick_Release_Pub.Pick_Release');
3217 print_debug('error msg ' || x_msg_data,
3218 'Inv_Pick_Release_Pub.Pick_Release');
3219 End If;
3220 x_return_status := fnd_api.g_ret_sts_error;
3221 ELSE -- patchset J bulk picking
3222 IF (WMS_CONTROL.G_CURRENT_RELEASE_LEVEL >= INV_RELEASE.G_J_RELEASE_LEVEL) THEN
3223 IF (is_debug) THEN
3224 print_debug('PATCHSET J -- BULK PICKING --- START',
3225 'Inv_Pick_Release_Pub.Pick_Release');
3226 print_debug('calling assign_pick_slip_number',
3227 'Inv_Pick_Release_Pub.Pick_Release');
3228 END IF;
3229
3230 assign_pick_slip_number
3231 (x_return_status => l_api_return_status,
3232 x_msg_count => x_msg_count,
3233 x_msg_data => x_msg_data,
3234 p_move_order_header_id => l_mo_line_tbl(l_line_index).header_id,
3235 p_ps_mode => l_print_mode,
3236 p_grouping_rule_id => p_grouping_rule_id,
3237 p_allow_partial_pick => p_allow_partial_pick);
3238
3239 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3240 l_api_return_status = fnd_api.g_ret_sts_error THEN
3241 print_debug('error from assign_pick_slip_number api',
3242 'Inv_Pick_Release_Pub.Pick_Release');
3243 print_debug('error count ' || x_msg_count,
3244 'Inv_Pick_Release_Pub.Pick_Release');
3245 print_debug('error msg ' || x_msg_data,
3246 'Inv_Pick_Release_Pub.Pick_Release');
3247 x_return_status := fnd_api.g_ret_sts_error;
3248 END IF;
3249 IF (is_debug) THEN
3250 print_debug('PATCHSET J -- BULK PICKING --- END',
3251 'Inv_Pick_Release_Pub.Pick_Release');
3252 END IF;
3253 END IF;
3254 END IF;
3255
3256 IF is_debug THEN
3257 print_debug('success from cartonize api',
3258 'Inv_Pick_Release_Pub.Pick_Release');
3259 END IF;
3260 l_line_index := l_line_index + 1;
3261 END IF;
3262 EXIT WHEN l_line_index > l_mo_line_tbl.LAST;
3263 END LOOP;
3264 END IF; -- Do cartonization?? --Added bug3237702
3265 END IF; -- wms installed
3266
3267 -- At this point, each Move Order Line has been processed.
3268 -- If automatic pick confirmation is chosen, call pick confirm now.
3269 IF l_auto_pick_confirm = 1 THEN
3270 NULL;
3271 END IF;
3272
3273 -- bug 9130704
3274 -- Do not execute the label printing related code
3275 -- unnecessarily when doing parallel pick release (p_skip_cartonization set to TRUE)
3276 -- Start Bug 6696594
3277 IF (l_wms_installed AND NOT p_skip_cartonization) THEN
3278 BEGIN
3279 -- bug 9130704
3280
3281 IF (l_debug = 1) THEN
3282 print_debug('Move header id: ' || l_mo_line_tbl(l_mo_line_tbl.FIRST).header_id, 'Inv_Pick_Release_Pub.Pick_Release');
3283 END IF;
3284
3285 OPEN c_wpr_casepick_labels(l_mo_line_tbl(l_mo_line_tbl.FIRST).header_id);
3286
3287 LOOP --{
3288 FETCH c_wpr_casepick_labels BULK COLLECT
3289 INTO v_transaction_id LIMIT 1000;
3290
3291 EXIT WHEN v_transaction_id.COUNT = 0;
3292
3293 IF (l_debug = 1) THEN
3294 print_debug('Calling print_label with v_transaction_id.count : ' || v_transaction_id.COUNT, 'Inv_Pick_Release_Pub.Pick_Release');
3295 END IF;
3296
3297 l_return_status := fnd_api.g_ret_sts_success;
3298
3299 inv_label.print_label (
3300 x_return_status => l_return_status
3301 , x_msg_count => x_msg_count
3302 , x_msg_data => x_msg_data
3303 , x_label_status => l_label_status
3304 , p_api_version => 1.0
3305 , p_print_mode => 1
3306 , p_business_flow_code => 42 --Business Flow Pick Release
3307 , p_transaction_id => v_transaction_id);
3308
3309 IF ( l_return_status <> fnd_api.g_ret_sts_success ) THEN
3310 IF (l_debug = 1) THEN
3311 print_debug('failed to print labels', 'Inv_Pick_Release_Pub.Pick_Release');
3312 END IF;
3313 fnd_message.set_name('WMS','WMS_PRINT_LABEL_FAIL');
3314 fnd_msg_pub.ADD;
3315 END IF;
3316
3317
3318 END LOOP; --}
3319
3320 IF c_wpr_casepick_labels%ISOPEN THEN
3321 CLOSE c_wpr_casepick_labels;
3322 END IF;
3323
3324 EXCEPTION
3325 WHEN OTHERS THEN
3326 IF (l_debug = 1) THEN
3327 print_debug('Exception occured while calling print_label', 'Inv_Pick_Release_Pub.Pick_Release');
3328 END IF;
3329 fnd_message.set_name('WMS', 'WMS_PRINT_LABEL_FAIL');
3330 fnd_msg_pub.ADD;
3331
3332 IF c_wpr_casepick_labels%ISOPEN THEN
3333 CLOSE c_wpr_casepick_labels;
3334 END IF;
3335
3336 END;
3337 END IF;
3338 --END Bug 6696594
3339
3340
3341 -- Call Device Integration API to send the details of this
3342 -- PickRelease Wave to devices, if it is a WMS organization.
3343 -- All the MoveOrderLines should have the same MO Header Id. So
3344 -- picking the FIRST line's Header Id
3345 -- Note: We don't check for the return condition of this API as
3346 -- we let the PickRelease process succeed whether DeviceIntegration
3347 -- succeeds or fails.
3348 --
3349 -- bug 9130704: WMS_DEVICE_INTEGRATION_PVT.device_request should not
3350 -- be called for parallel pick release
3351
3352 IF (l_wms_installed AND NOT p_skip_cartonization) THEN
3353 WMS_DEVICE_INTEGRATION_PVT.device_request
3354 (p_bus_event => WMS_DEVICE_INTEGRATION_PVT.WMS_BE_PICK_RELEASE,
3355 p_call_ctx => WMS_Device_integration_pvt.DEV_REQ_AUTO,
3356 p_task_trx_id => l_mo_line_tbl(l_mo_line_tbl.FIRST).header_id,
3357 -- Bug 6401204 Passing the Organization id as WCS API isn't called correctly
3358 p_org_id => l_mo_line_tbl(l_mo_line_tbl.FIRST).organization_id,
3359 x_request_msg => l_req_msg,
3360 x_return_status => l_api_return_status,
3361 x_msg_count => x_msg_count,
3362 x_msg_data => x_msg_data
3363 );
3364 IF is_debug THEN
3365 print_debug('Device_API: return stat:'||l_api_return_status,
3366 'PICKREL');
3367 END IF;
3368 END IF;
3369
3370 -- Bug 2776309
3371 -- The below call should never backorder but if this
3372 -- happens then do not check l_cur_ship_model_id
3373 --IF l_cur_ship_model_id is not NULL then
3374 --2509322
3375 backorder_smc_details(l_api_return_status,
3376 x_msg_data,
3377 x_msg_count);
3378 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3379 RAISE fnd_api.g_exc_error;
3380 ELSIF l_api_return_status = fnd_api.g_ret_sts_unexp_error THEN
3381 RAISE fnd_api.g_exc_unexpected_error;
3382 END IF;
3383 -- END IF;
3384
3385 --bug 2408329: Since Quantity trees are cached and not built in a pick release
3386 --session, available qty seen is wrong during a blanket pick release.
3387 --Now Clear_cache after pick release so as to ensure when new locks are
3388 --obtained, the tree is built with latest db state.
3389 inv_quantity_tree_grp.clear_quantity_cache;
3390
3391 -- Bug# 4258360: For allocation modes of I (Inventory Only) or
3392 -- X (Prioritize Crossdock), allocation (both from inventory and crossdock)
3393 -- has completed. We need to remove the backordered lines from
3394 -- p_trolin_delivery_ids and p_del_detail_id tables so deliveries are not
3395 -- autocreated for them later on by Shipping.
3396 -- UPDATE: Do not need to do this logic anymore. Shipping does not populate the delivery
3397 -- tables inputted. Those are only used to store crossdocked lines for creation of deliveries.
3398 -- {{
3399 -- Run PR in Inventory Only mode and ensure no delivery exists for WDD
3400 -- Also ensure that the WDD is not allocated. After PR completes
3401 -- WDD should be back ordered and no delivery should exist for it.
3402 -- }}
3403 -- {{
3404 -- Run PR in Prioritize Xdock mode and ensure no delivery exists for WDD
3405 -- Also ensure that the WDD is not allocated. After PR completes
3406 -- WDD should be back ordered and no delivery should exist for it.
3407 -- }}
3408 /*IF (l_allocation_method IN (g_inventory_only, g_prioritize_crossdock) AND
3409 p_del_detail_id.COUNT > 0) THEN
3410 IF (is_debug) THEN
3411 print_debug('Remove the backordered WDD lines from the inputted delivery tables: ' ||
3412 l_allocation_method, 'Inv_Pick_Release_Pub.Pick_Release');
3413 END IF;
3414 l_xdock_index := p_del_detail_id.FIRST;
3415 l_xdock_next_index := p_del_detail_id.FIRST;
3416 -- Loop through table p_del_detail_id. If that WDD is backordered (value exists in
3417 -- l_backordered_wdd_tbl), then delete that entry from p_del_detail_id and the corresponding
3418 -- one in p_trolin_delivery_ids.
3419 LOOP
3420 l_xdock_index := l_xdock_next_index;
3421 l_xdock_next_index := p_del_detail_id.NEXT(l_xdock_next_index);
3422 -- Exit out of loop when l_xdock_index is null, meaning we have
3423 -- reached the last entry in the table.
3424 EXIT WHEN l_xdock_index IS NULL;
3425
3426 IF (l_backordered_wdd_tbl.EXISTS(p_del_detail_id(l_xdock_index))) THEN
3427 p_del_detail_id.DELETE(l_xdock_index);
3428 p_trolin_delivery_ids.DELETE(l_xdock_index);
3429 END IF;
3430 END LOOP;
3431 END IF;*/
3432
3433 -- Bug# 4258360: For allocation modes of N (Prioritize Inventory), call the
3434 -- Crossdock Pegging API here since inventory allocation has been completed
3435 IF (l_allocation_method = g_prioritize_inventory) THEN
3436 IF (is_debug) THEN
3437 print_debug('Call the Planned_Cross_Dock API (Prioritize Inventory)',
3438 'Inv_Pick_Release_Pub.Pick_Release');
3439 END IF;
3440 WMS_XDOCK_PEGGING_PUB.Planned_Cross_Dock
3441 (p_api_version => 1.0,
3442 p_init_msg_list => fnd_api.g_false,
3443 p_commit => fnd_api.g_false,
3444 x_return_status => l_api_return_status,
3445 x_msg_count => x_msg_count,
3446 x_msg_data => x_msg_data,
3447 p_batch_id => INV_CACHE.wpb_rec.batch_id,
3448 p_wsh_release_table => p_wsh_release_table,
3449 p_trolin_delivery_ids => p_trolin_delivery_ids,
3450 p_del_detail_id => p_del_detail_id);
3451
3452 IF (l_api_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3453 IF (is_debug) THEN
3454 print_debug('Success returned from Planned_Cross_Dock API',
3455 'Inv_Pick_Release_Pub.Pick_Release');
3456 END IF;
3457 ELSIF (l_api_return_status = FND_API.G_RET_STS_ERROR) THEN
3458 IF (is_debug) THEN
3459 print_debug('Error returned from Planned_Cross_Dock API',
3460 'Inv_Pick_Release_Pub.Pick_Release');
3461 END IF;
3462 RAISE FND_API.G_EXC_ERROR;
3463 ELSIF (l_api_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3464 IF (is_debug) THEN
3465 print_debug('Unexpected error returned from Planned_Cross_Dock API',
3466 'Inv_Pick_Release_Pub.Pick_Release');
3467 END IF;
3468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3469 END IF;
3470 END IF;
3471
3472 -- Standard call to commit
3473 IF p_commit = fnd_api.g_true THEN
3474 COMMIT;
3475 END IF;
3476
3477 -- Standard call to get message count and if count is 1, get message info
3478 IF is_debug THEN
3479 print_Debug('x_return_status is ' || x_return_status,
3480 'Inv_Pick_Release_Pub.Pick_Release');
3481 END IF;
3482
3483 inv_cache.mo_transaction_date := NULL;
3484 l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3485 inv_log_util.g_maintain_log_profile := FALSE;
3486
3487 EXCEPTION
3488 WHEN FND_API.G_EXC_ERROR THEN
3489 --
3490 x_return_status := FND_API.G_RET_STS_ERROR;
3491 l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3492 inv_log_util.g_maintain_log_profile := TRUE;
3493 --
3494 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3495 , p_data => x_msg_data);
3496 --
3497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3498 --
3499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3500 l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3501 inv_log_util.g_maintain_log_profile := TRUE;
3502 --
3503 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3504 , p_data => x_msg_data);
3505 --
3506 WHEN OTHERS THEN
3507 IF is_debug THEN
3508 print_Debug('Other error: ' || sqlerrm,
3509 'Inv_Pick_Release_Pub.Pick_Release');
3510 END IF;
3511
3512 ROLLBACK TO Pick_Release_PUB;
3513 --
3514 l_return_value := inv_cache.set_pick_release(FALSE); --Added bug3237702
3515 inv_log_util.g_maintain_log_profile := TRUE;
3516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3517 --
3518 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3519 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3520 END IF;
3521 --
3522 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3523 , p_data => x_msg_data);
3524 END Pick_Release;
3525
3526
3527 -- Start of Comments
3528 -- API name Reserve_Unconfirmed_Quantity
3529 -- Type Public
3530 --
3531 -- Purpose
3532 -- Transfers a reservation on material which is missing or damaged to an
3533 -- appropriate demand source.
3534 --
3535 -- Input Parameters
3536 -- p_missing_quantity
3537 -- The quantity to be transferred to a Cycle Count reservation, in the primary
3538 -- UOM for the item.
3539 -- p_organization_id
3540 -- The organization in which the reservation(s) should be created
3541 -- p_reservation_id
3542 -- The reservation to transfer quantity from (not required if demand source
3543 -- parameters are given).
3544 -- p_demand_source_type_id
3545 -- The demand source type ID for the reservation to be transferred
3546 -- p_demand_source_header_id
3547 -- The demand source header ID for the reservation to be transferred
3548 -- p_demand_source_line_id
3549 -- The demand source line ID for the reservation to be transferred
3550 -- p_inventory_item_id
3551 -- The item which is missing or damaged.
3552 -- p_subinventory_code
3553 -- The subinventory in which the material is missing or damaged.
3554 -- p_locator_id
3555 -- The locator in which the material is missing or damaged.
3556 -- p_revision
3557 -- The revision of the item which is missing or damaged.
3558 -- p_lot_number
3559 -- The lot number of the item which is missing or damaged.
3560 --
3561 -- Output Parameters
3562 -- x_return_status
3563 -- if the pick release process succeeds, the value is
3564 -- fnd_api.g_ret_sts_success;
3565 -- if there is an expected error, the value is
3566 -- fnd_api.g_ret_sts_error;
3567 -- if there is an unexpected error, the value is
3568 -- fnd_api.g_ret_sts_unexp_error;
3569 -- x_msg_count
3570 -- if there is one or more errors, the number of error messages
3571 -- in the buffer
3572 -- x_msg_data
3573 -- if there is one and only one error, the error message
3574 -- (See fnd_api package for more details about the above output parameters)
3575 --
3576
3577 -- HW INVCONV added p_missing_quantity2
3578
3579 PROCEDURE Reserve_Unconfirmed_Quantity
3580 (
3581 p_api_version IN NUMBER
3582 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
3583 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
3584 ,x_return_status OUT NOCOPY VARCHAR2
3585 ,x_msg_count OUT NOCOPY NUMBER
3586 ,x_msg_data OUT NOCOPY VARCHAR2
3587 ,p_missing_quantity IN NUMBER
3588 ,p_missing_quantity2 IN NUMBER DEFAULT fnd_api.g_miss_num
3589 ,p_reservation_id IN NUMBER DEFAULT fnd_api.g_miss_num
3590 ,p_demand_source_header_id IN NUMBER DEFAULT fnd_api.g_miss_num
3591 ,p_demand_source_line_id IN NUMBER DEFAULT fnd_api.g_miss_num
3592 ,p_organization_id IN NUMBER DEFAULT fnd_api.g_miss_num
3593 ,p_inventory_item_id IN NUMBER DEFAULT fnd_api.g_miss_num
3594 ,p_subinventory_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char
3595 ,p_locator_id IN NUMBER DEFAULT fnd_api.g_miss_num
3596 ,p_revision IN VARCHAR2 DEFAULT fnd_api.g_miss_char
3597 ,p_lot_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char
3598 ) IS
3599 l_api_version CONSTANT NUMBER := 1.0;
3600 l_api_name CONSTANT VARCHAR2(30) := 'Reserve_Unconfirmed_Quantity';
3601
3602 l_reservation_id NUMBER; -- The reservation to transfer quantity from
3603 -- If invoked at Pick Confirm, this will
3604 -- typically be passed in as a parameter,
3605 -- based on the reservation tied to the Move
3606 -- Order Line Detail which is being Pick
3607 -- Confirmed. If invoked at Ship Confirm,
3608 -- this will most likely be derived from the
3609 -- demand and supply source parameters.
3610 l_reservation_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
3611 -- Temporary reservation record for retrieving
3612 -- the reservation to transfer.
3613 l_reservation_count NUMBER; -- The number of reservations which match the
3614 -- demand/supply source parameters passed in.
3615 l_cc_reservation_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
3616 -- Temporary reservation record for the amount
3617 -- to be transferred to Cycle Count.
3618 l_reservations_tbl INV_Reservation_GLOBAL.MTL_RESERVATION_TBL_TYPE;
3619 -- The table of reservations for given
3620 -- supply and demand source
3621 l_dummy_sn INV_Reservation_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
3622 l_new_rsv_id NUMBER; -- The reservation ID that has been transferred
3623 -- to or updated
3624 l_mso_header_id NUMBER; -- The header ID for the record in
3625 -- MTL_SALES_ORDERS that corresponds to the OE
3626 -- header and line passed in.
3627 l_api_return_status VARCHAR2(1);
3628 -- The return status of APIs called
3629 -- within this API.
3630 l_api_error_code NUMBER; -- The error code of APIs called within
3631 -- this API.
3632 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3633 BEGIN
3634 -- Set savepoint for this API
3635 SAVEPOINT Reserve_Unconfirmed_Qty_PUB;
3636
3637 -- Standard Call to check for call compatibility
3638 IF NOT fnd_api.Compatible_API_Call(l_api_version
3639 , p_api_version
3640 , l_api_name
3641 , G_PKG_NAME) THEN
3642 RAISE fnd_api.g_exc_unexpected_error;
3643 END IF;
3644
3645 -- Initialize message list if p_init_msg_list is set to true
3646 IF fnd_api.to_Boolean(p_init_msg_list) THEN
3647 fnd_msg_pub.initialize;
3648 END IF;
3649
3650 -- Initialize API return status to success
3651 x_return_status := fnd_api.g_ret_sts_success;
3652
3653 -- Validate parameters
3654
3655 -- First make sure that missing quantity is not <= 0
3656 IF p_missing_quantity <= 0 THEN
3657 FND_MESSAGE.SET_NAME('INV','INV_NO_QTY_TO_TRANSFER');
3658 FND_MSG_PUB.Add;
3659 RAISE fnd_api.g_exc_unexpected_error;
3660 END IF;
3661
3662 -- Determine whether the reservation id was given, or if it must be derived
3663 -- based on the supply source parameters.
3664 IF p_reservation_id <> fnd_api.g_miss_num AND
3665 p_reservation_id IS NOT NULL THEN
3666 -- Initialize the reservation querying record with the reservation ID
3667 l_reservation_id := p_reservation_id;
3668 l_reservation_rec.reservation_id := l_reservation_id;
3669 ELSE
3670 -- Initialize the reservation record with the demand/supply source
3671 -- information. At minimum, must have a demand source type, line and header,
3672 -- and an item and organization ID
3673 IF p_inventory_item_id = fnd_api.g_miss_num OR
3674 p_inventory_item_id IS NULL OR
3675 p_organization_id = fnd_api.g_miss_num OR
3676 p_organization_id IS NULL OR
3677 p_demand_source_header_id = fnd_api.g_miss_num OR
3678 p_demand_source_header_id IS NULL OR
3679 p_demand_source_line_id = fnd_api.g_miss_num OR
3680 p_demand_source_line_id IS NULL THEN
3681 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_ID_RSV');
3682 FND_MSG_PUB.Add;
3683 RAISE fnd_api.g_exc_unexpected_error;
3684 END IF;
3685
3686 -- First attempt to convert the demand source header id given
3687 -- (the OE header id) to the MTL_SALES_ORDERS id to be used.
3688 /*l_mso_header_id :=
3689 inv_salesorder.get_salesorder_for_oeheader(p_demand_source_header_id);
3690 IF l_mso_header_id IS NULL THEN
3691 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
3692 FND_MSG_PUB.Add;
3693 RAISE fnd_api.g_exc_unexpected_error;
3694 END IF;*/
3695
3696 l_reservation_rec.inventory_item_id := p_inventory_item_id;
3697 l_reservation_rec.organization_id := p_organization_id;
3698 l_reservation_rec.demand_source_type_id :=
3699 INV_Reservation_GLOBAL.g_source_type_oe;
3700 l_reservation_rec.demand_source_header_id := p_demand_source_header_id;
3701 l_reservation_rec.demand_source_line_id := p_demand_source_line_id;
3702 -- R12 Crossdock changes
3703 l_reservation_rec.demand_source_line_detail := NULL;
3704
3705 IF p_subinventory_code <> fnd_api.g_miss_char THEN
3706 l_reservation_rec.subinventory_code := p_subinventory_code;
3707 ELSE
3708 l_reservation_rec.subinventory_code := NULL;
3709 END IF;
3710
3711 IF p_locator_id <> fnd_api.g_miss_num THEN
3712 l_reservation_rec.locator_id := p_locator_id;
3713 ELSE
3714 l_reservation_rec.locator_id := NULL;
3715 END IF;
3716
3717 IF p_revision <> fnd_api.g_miss_char THEN
3718 l_reservation_rec.revision := p_revision;
3719 ELSE
3720 l_reservation_rec.revision := NULL;
3721 END IF;
3722
3723 IF p_lot_number <> fnd_api.g_miss_char THEN
3724 l_reservation_rec.lot_number := p_lot_number;
3725 ELSE
3726 l_reservation_rec.lot_number := NULL;
3727 END IF;
3728 END IF;
3729
3730 -- Retrieve the reservation information
3731 INV_Reservation_PUB.Query_Reservation
3732 (
3733 p_api_version_number => 1.0
3734 , p_init_msg_lst => fnd_api.g_false
3735 , x_return_status => l_api_return_status
3736 , x_msg_count => x_msg_count
3737 , x_msg_data => x_msg_data
3738 , p_query_input => l_reservation_rec
3739 , x_mtl_reservation_tbl => l_reservations_tbl
3740 , x_mtl_reservation_tbl_count => l_reservation_count
3741 , x_error_code => l_api_error_code
3742 );
3743 -- Return an error if the query reservations call failed
3744 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3745 FND_MESSAGE.SET_NAME('INV','INV_QRY_RSV_FAILED');
3746 FND_MSG_PUB.Add;
3747 RAISE fnd_api.g_exc_unexpected_error;
3748 END IF;
3749
3750 -- Only 1 reservation record should have been returned, since the parameters
3751 -- passed are supposed to uniquely identify a reservation record.
3752 IF l_reservation_count = 0 THEN
3753 FND_MESSAGE.SET_NAME('INV','INV_NO_RSVS_FOUND');
3754 FND_MSG_PUB.Add;
3755 RAISE fnd_api.g_exc_unexpected_error;
3756 END IF;
3757
3758 IF l_reservation_count > 1 THEN
3759 FND_MESSAGE.SET_NAME('INV','INV_NON_UNIQUE_RSV');
3760 FND_MSG_PUB.Add;
3761 RAISE fnd_api.g_exc_unexpected_error;
3762 END IF;
3763
3764 -- Determine whether the quantity to transfer is greater
3765 -- than the currently reserved quantity
3766 IF p_missing_quantity > l_reservations_tbl(1).primary_reservation_quantity THEN
3767 FND_MESSAGE.SET_NAME('INV','INV_INSUFF_QTY_RSV');
3768 FND_MSG_PUB.Add;
3769 RAISE fnd_api.g_exc_unexpected_error;
3770 END IF;
3771
3772 -- Initialize the querying record with the reservation ID so that the update
3773 -- will be more efficient.
3774 l_reservation_rec.reservation_id := l_reservations_tbl(1).reservation_id;
3775
3776 -- Validation complete - ready to transfer reservation to appropriate source
3777 l_cc_reservation_rec.primary_reservation_quantity := p_missing_quantity;
3778 l_cc_reservation_rec.primary_uom_code := l_reservations_tbl(1).primary_uom_code;
3779 l_cc_reservation_rec.detailed_quantity := 0;
3780 l_cc_reservation_rec.demand_source_type_id := 9;
3781 -- HW INVCONV
3782 l_cc_reservation_rec.secondary_reservation_quantity := p_missing_quantity2;
3783 l_cc_reservation_rec.secondary_uom_code := l_reservations_tbl(1).secondary_uom_code;
3784 l_cc_reservation_rec.secondary_detailed_quantity := 0;
3785 -- End of HW INVCONV
3786 l_cc_reservation_rec.demand_source_header_id := -1;
3787 l_cc_reservation_rec.demand_source_line_id := -1;
3788 -- R12 Crossdock changes
3789 l_cc_reservation_rec.demand_source_line_detail := -1;
3790
3791 l_cc_reservation_rec.subinventory_code := p_subinventory_code;
3792 l_cc_reservation_rec.locator_id := p_locator_id;
3793 l_cc_reservation_rec.revision := p_revision;
3794 l_cc_reservation_rec.lot_number := p_lot_number;
3795
3796 -- Make the call to the Transfer Reservation API
3797 INV_Reservation_PUB.Transfer_Reservation
3798 (
3799 p_api_version_number => 1.0
3800 , p_init_msg_lst => fnd_api.g_true
3801 , x_return_status => l_api_return_status
3802 , x_msg_count => x_msg_count
3803 , x_msg_data => x_msg_data
3804 , p_original_rsv_rec => l_reservation_rec
3805 , p_to_rsv_rec => l_cc_reservation_rec
3806 , p_original_serial_number => l_dummy_sn
3807 , p_to_serial_number => l_dummy_sn
3808 , p_validation_flag => fnd_api.g_true
3809 , x_to_reservation_id => l_new_rsv_id
3810 );
3811 -- Return an error if the transfer reservations call failed
3812 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3813 FND_MESSAGE.SET_NAME('INV','INV_TRANSFER_RSV_FAILED');
3814 FND_MSG_PUB.Add;
3815 RAISE fnd_api.g_exc_unexpected_error;
3816 END IF;
3817
3818 -- Commit if necessary
3819 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3820 ROLLBACK TO Reserve_Unconfirmed_Qty_PUB;
3821 ELSE
3822 -- Standard call to commit
3823 IF p_commit = fnd_api.g_true THEN
3824 COMMIT;
3825 END IF;
3826 END IF;
3827
3828 EXCEPTION
3829 WHEN FND_API.G_EXC_ERROR THEN
3830 --
3831 x_return_status := FND_API.G_RET_STS_ERROR;
3832 --
3833 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3834 , p_data => x_msg_data);
3835 --
3836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3837 --
3838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3839 --
3840 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3841 , p_data => x_msg_data);
3842 --
3843 WHEN OTHERS THEN
3844 ROLLBACK TO Reserve_Unconfirmed_Qty_PUB;
3845 --
3846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3847 --
3848 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3849 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3850 END IF;
3851 --
3852 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3853 , p_data => x_msg_data);
3854 END Reserve_Unconfirmed_Quantity;
3855
3856
3857 PROCEDURE call_cartonization (
3858 p_api_version IN NUMBER
3859 , p_init_msg_list IN VARCHAR2
3860 , p_commit IN VARCHAR2
3861 , p_validation_level IN NUMBER
3862 , x_return_status OUT NOCOPY VARCHAR2
3863 , x_msg_count OUT NOCOPY NUMBER
3864 , x_msg_data OUT NOCOPY VARCHAR2
3865 , p_out_bound IN VARCHAR2
3866 , p_org_id IN NUMBER
3867 , p_move_order_header_id IN NUMBER
3868 , p_grouping_rule_id IN NUMBER
3869 , p_allow_partial_pick IN VARCHAR2
3870 ) IS
3871
3872 l_print_mode VARCHAR(1);
3873 l_debug NUMBER;
3874 l_api_return_status VARCHAR2(1);
3875 l_do_cartonization number := NVL(FND_PROFILE.VALUE('WMS_ASSIGN_TASK_TYPE'),1);
3876
3877 BEGIN
3878 -- Set savepoint for this API
3879 SAVEPOINT PR_Call_cartonization;
3880
3881 -- because the debug profile rarely changes, only check it once per
3882 -- session, instead of once per batch
3883 IF is_debug IS NULL THEN
3884 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3885 if l_debug = 1 then
3886 is_debug := TRUE;
3887 else
3888 is_debug := FALSE;
3889 end if;
3890 END IF;
3891
3892 IF l_do_cartonization = 1 THEN
3893 -- Determine what printing mode to use when pick releasing lines.
3894 IF g_organization_id IS NOT NULL AND
3895 g_organization_id = p_org_id AND
3896 g_print_mode IS NOT NULL THEN
3897
3898 l_print_mode := g_print_mode;
3899 ELSE
3900
3901 BEGIN
3902 SELECT print_pick_slip_mode, pick_grouping_rule_id
3903 INTO l_print_mode, g_org_grouping_rule_id
3904 FROM WSH_SHIPPING_PARAMETERS
3905 WHERE organization_id = p_org_id;
3906 EXCEPTION
3907 WHEN no_data_found THEN
3908 If is_debug then
3909 print_debug('Error: print_pick_slip_mode not defined',
3910 'INV_Pick_Release_Pub.Pick_Release');
3911 End If;
3912 --ROLLBACK TO Pick_Release_PUB;
3913 FND_MESSAGE.SET_NAME('INV','INV_WSH_ORG_NOT_FOUND');
3914 FND_MSG_PUB.Add;
3915 RAISE fnd_api.g_exc_unexpected_error;
3916 END;
3917
3918 g_organization_id := p_org_id;
3919 g_print_mode := l_print_mode;
3920 END IF;
3921
3922 -- call cartonize API
3923 If is_debug then
3924 print_debug('calling cartonize api',
3925 'Inv_Pick_Release_Pub.Pick_Release');
3926 End If;
3927
3928 IF p_move_order_header_id <> -1 and p_move_order_header_id <> 0 and p_move_order_header_id IS NOT NULL THEN
3929 update mtl_material_transactions_temp
3930 set lock_flag = NULL
3931 where move_order_header_id = p_move_order_header_id
3932 and organization_id = p_org_id
3933 and lock_flag is not null;
3934 END IF;
3935
3936 WMS_CARTNZN_PUB.cartonize
3937 (
3938 p_api_version => 1,
3939 p_init_msg_list => fnd_api.g_false,
3940 p_commit => fnd_api.g_false,
3941 p_validation_level => fnd_api.g_valid_level_full,
3942 x_return_status => l_api_return_status,
3943 x_msg_count => x_msg_count,
3944 x_msg_data => x_msg_data,
3945 p_out_bound => 'Y',
3946 p_org_id => p_org_id,
3947 p_move_order_header_id => p_move_order_header_id
3948 );
3949 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3950 l_api_return_status = fnd_api.g_ret_sts_error THEN
3951 If is_debug then
3952 print_debug('error from cartonize api',
3953 'Inv_Pick_Release_Pub.Call_Cartonization');
3954 print_debug('error count ' || x_msg_count,
3955 'Inv_Pick_Release_Pub.Call_Cartonization');
3956 print_debug('error msg ' || x_msg_data,
3957 'Inv_Pick_Release_Pub.Call_Cartonization');
3958 End If;
3959 x_return_status := fnd_api.g_ret_sts_error;
3960 ELSE
3961 IF (is_debug) THEN print_debug('PATCHSET J -- BULK PICKING --- START',
3962 'Inv_Pick_Release_Pub.Call_Cartonization');
3963 print_debug('calling assign_pick_slip_number',
3964 'Inv_Pick_Release_Pub.Call_Cartonization');
3965 END IF;
3966 assign_pick_slip_number(
3967 x_return_status => l_api_return_status,
3968 x_msg_count => x_msg_count,
3969 x_msg_data => x_msg_data,
3970 p_move_order_header_id => p_move_order_header_id,
3971 p_ps_mode => l_print_mode,
3972 p_grouping_rule_id => p_grouping_rule_id,
3973 p_allow_partial_pick => p_allow_partial_pick);
3974 IF l_api_return_status = fnd_api.g_ret_sts_unexp_error OR
3975 l_api_return_status = fnd_api.g_ret_sts_error THEN
3976 print_debug('error from assign_pick_slip_number api',
3977 'Inv_Pick_Release_Pub.Call_Cartonization');
3978 print_debug('error count ' || x_msg_count,
3979 'Inv_Pick_Release_Pub.Call_Cartonization');
3980 print_debug('error msg ' || x_msg_data,
3981 'Inv_Pick_Release_Pub.Call_Cartonization');
3982 x_return_status := fnd_api.g_ret_sts_error;
3983 END IF;
3984 END IF;
3985
3986 If is_debug then
3987 print_debug('success from cartonize api',
3988 'Inv_Pick_Release_Pub.Call_Cartonization');
3989 End If;
3990 END IF; --l_do_Cartonization=1
3991 EXCEPTION
3992 WHEN FND_API.G_EXC_ERROR THEN
3993 --
3994 x_return_status := FND_API.G_RET_STS_ERROR;
3995 --
3996 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
3997 , p_data => x_msg_data);
3998 --
3999 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4000 --
4001 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4002 --
4003 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
4004 , p_data => x_msg_data);
4005 --
4006 WHEN OTHERS THEN
4007 ROLLBACK TO PR_Call_cartonization;
4008 --
4009 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4010 --
4011 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4012 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Call_Cartonization');
4013 END IF;
4014 --
4015 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
4016 , p_data => x_msg_data);
4017 END call_cartonization;
4018
4019 /* Bug 7504490* - Added the procedure Reserve_Unconfqty_lpn. This procedure transfers the reservation
4020 of the remaining quantity (task qty-picked qty) to cycle count reservation and ensures that the
4021 lpn_id is stamped on the reservation if the task was for an allocated lpn. This is similar to the
4022 Reserve_unconfirmed_quantity API except for passing the lpn_id to the reservation record. */
4023
4024 PROCEDURE Reserve_Unconfqty_lpn
4025 (
4026 p_api_version IN NUMBER
4027 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
4028 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
4029 ,x_return_status OUT NOCOPY VARCHAR2
4030 ,x_msg_count OUT NOCOPY NUMBER
4031 ,x_msg_data OUT NOCOPY VARCHAR2
4032 ,x_new_rsv_id OUT NOCOPY NUMBER -- bug8301348
4033 ,p_missing_quantity IN NUMBER
4034 ,p_secondary_missing_quantity IN NUMBER DEFAULT NULL /*9251210*/
4035 ,p_reservation_id IN NUMBER DEFAULT fnd_api.g_miss_num
4036 ,p_demand_source_header_id IN NUMBER DEFAULT fnd_api.g_miss_num
4037 ,p_demand_source_line_id IN NUMBER DEFAULT fnd_api.g_miss_num
4038 ,p_organization_id IN NUMBER DEFAULT fnd_api.g_miss_num
4039 ,p_inventory_item_id IN NUMBER DEFAULT fnd_api.g_miss_num
4040 ,p_subinventory_code IN VARCHAR2 DEFAULT fnd_api.g_miss_char
4041 ,p_locator_id IN NUMBER DEFAULT fnd_api.g_miss_num
4042 ,p_revision IN VARCHAR2 DEFAULT fnd_api.g_miss_char
4043 ,p_lot_number IN VARCHAR2 DEFAULT fnd_api.g_miss_char
4044 ,p_lpn_id IN NUMBER DEFAULT fnd_api.g_miss_num
4045 ) IS
4046 l_api_version CONSTANT NUMBER := 1.0;
4047 l_api_name CONSTANT VARCHAR2(30) := 'Reserve_Unconfirmed_Quantity';
4048
4049 l_reservation_id NUMBER; -- The reservation to transfer quantity from
4050 -- If invoked at Pick Confirm, this will
4051 -- typically be passed in as a parameter,
4052 -- based on the reservation tied to the Move
4053 -- Order Line Detail which is being Pick
4054 -- Confirmed. If invoked at Ship Confirm,
4055 -- this will most likely be derived from the
4056 -- demand and supply source parameters.
4057 l_reservation_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
4058 -- Temporary reservation record for retrieving
4059 -- the reservation to transfer.
4060 l_reservation_count NUMBER; -- The number of reservations which match the
4061 -- demand/supply source parameters passed in.
4062 l_cc_reservation_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
4063 -- Temporary reservation record for the amount
4064 -- to be transferred to Cycle Count.
4065 l_reservations_tbl INV_Reservation_GLOBAL.MTL_RESERVATION_TBL_TYPE;
4066 -- The table of reservations for given
4067 -- supply and demand source
4068 l_dummy_sn INV_Reservation_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
4069 l_new_rsv_id NUMBER; -- The reservation ID that has been transferred
4070 -- to or updated
4071 l_mso_header_id NUMBER; -- The header ID for the record in
4072 -- MTL_SALES_ORDERS that corresponds to the OE
4073 -- header and line passed in.
4074 l_api_return_status VARCHAR2(1);
4075 -- The return status of APIs called
4076 -- within this API.
4077 l_api_error_code NUMBER; -- The error code of APIs called within
4078 -- this API.
4079 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4080 BEGIN
4081 -- Set savepoint for this API
4082 SAVEPOINT Reserve_Unconfirmed_Qty_PUB;
4083
4084 -- Standard Call to check for call compatibility
4085 IF NOT fnd_api.Compatible_API_Call(l_api_version
4086 , p_api_version
4087 , l_api_name
4088 , G_PKG_NAME) THEN
4089 RAISE fnd_api.g_exc_unexpected_error;
4090 END IF;
4091
4092 -- Initialize message list if p_init_msg_list is set to true
4093 IF fnd_api.to_Boolean(p_init_msg_list) THEN
4094 fnd_msg_pub.initialize;
4095 END IF;
4096
4097 -- Initialize API return status to success
4098 x_return_status := fnd_api.g_ret_sts_success;
4099
4100 -- Validate parameters
4101
4102 -- First make sure that missing quantity is not <= 0
4103 IF p_missing_quantity <= 0 THEN
4104 FND_MESSAGE.SET_NAME('INV','INV_NO_QTY_TO_TRANSFER');
4105 FND_MSG_PUB.Add;
4106 RAISE fnd_api.g_exc_unexpected_error;
4107 END IF;
4108
4109 -- Determine whether the reservation id was given, or if it must be derived
4110 -- based on the supply source parameters.
4111 IF p_reservation_id <> fnd_api.g_miss_num AND
4112 p_reservation_id IS NOT NULL THEN
4113 -- Initialize the reservation querying record with the reservation ID
4114 l_reservation_id := p_reservation_id;
4115 l_reservation_rec.reservation_id := l_reservation_id;
4116 ELSE
4117 -- Initialize the reservation record with the demand/supply source
4118 -- information. At minimum, must have a demand source type, line and header,
4119 -- and an item and organization ID
4120 IF p_inventory_item_id = fnd_api.g_miss_num OR
4121 p_inventory_item_id IS NULL OR
4122 p_organization_id = fnd_api.g_miss_num OR
4123 p_organization_id IS NULL OR
4124 p_demand_source_header_id = fnd_api.g_miss_num OR
4125 p_demand_source_header_id IS NULL OR
4126 p_demand_source_line_id = fnd_api.g_miss_num OR
4127 p_demand_source_line_id IS NULL THEN
4128 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_ID_RSV');
4129 FND_MSG_PUB.Add;
4130 RAISE fnd_api.g_exc_unexpected_error;
4131 END IF;
4132
4133 -- First attempt to convert the demand source header id given
4134 -- (the OE header id) to the MTL_SALES_ORDERS id to be used.
4135 /*l_mso_header_id :=
4136 inv_salesorder.get_salesorder_for_oeheader(p_demand_source_header_id);
4137 IF l_mso_header_id IS NULL THEN
4138 FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
4139 FND_MSG_PUB.Add;
4140 RAISE fnd_api.g_exc_unexpected_error;
4141 END IF;*/
4142
4143 l_reservation_rec.inventory_item_id := p_inventory_item_id;
4144 l_reservation_rec.organization_id := p_organization_id;
4145 l_reservation_rec.demand_source_type_id :=
4146 INV_Reservation_GLOBAL.g_source_type_oe;
4147 l_reservation_rec.demand_source_header_id := p_demand_source_header_id;
4148 l_reservation_rec.demand_source_line_id := p_demand_source_line_id;
4149
4150 IF p_subinventory_code <> fnd_api.g_miss_char THEN
4151 l_reservation_rec.subinventory_code := p_subinventory_code;
4152 ELSE
4153 l_reservation_rec.subinventory_code := NULL;
4154 END IF;
4155
4156 IF p_locator_id <> fnd_api.g_miss_num THEN
4157 l_reservation_rec.locator_id := p_locator_id;
4158 ELSE
4159 l_reservation_rec.locator_id := NULL;
4160 END IF;
4161
4162 IF p_revision <> fnd_api.g_miss_char THEN
4163 l_reservation_rec.revision := p_revision;
4164 ELSE
4165 l_reservation_rec.revision := NULL;
4166 END IF;
4167
4168 IF p_lot_number <> fnd_api.g_miss_char THEN
4169 l_reservation_rec.lot_number := p_lot_number;
4170 ELSE
4171 l_reservation_rec.lot_number := NULL;
4172 END IF;
4173
4174 /* Bug 7504490 - Checking for the allocated_lpn_id passed */
4175 IF p_lpn_id <> fnd_api.g_miss_char THEN
4176 l_reservation_rec.lpn_id := p_lpn_id;
4177 ELSE
4178 l_reservation_rec.lpn_id := NULL;
4179 END IF;
4180 END IF;
4181
4182 -- Retrieve the reservation information
4183 INV_Reservation_PUB.Query_Reservation
4184 (
4185 p_api_version_number => 1.0
4186 , p_init_msg_lst => fnd_api.g_false
4187 , x_return_status => l_api_return_status
4188 , x_msg_count => x_msg_count
4189 , x_msg_data => x_msg_data
4190 , p_query_input => l_reservation_rec
4191 , x_mtl_reservation_tbl => l_reservations_tbl
4192 , x_mtl_reservation_tbl_count => l_reservation_count
4193 , x_error_code => l_api_error_code
4194 );
4195 -- Return an error if the query reservations call failed
4196 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
4197 FND_MESSAGE.SET_NAME('INV','INV_QRY_RSV_FAILED');
4198 FND_MSG_PUB.Add;
4199 RAISE fnd_api.g_exc_unexpected_error;
4200 END IF;
4201
4202 -- Only 1 reservation record should have been returned, since the parameters
4203 -- passed are supposed to uniquely identify a reservation record.
4204 IF l_reservation_count = 0 THEN
4205 FND_MESSAGE.SET_NAME('INV','INV_NO_RSVS_FOUND');
4206 FND_MSG_PUB.Add;
4207 RAISE fnd_api.g_exc_unexpected_error;
4208 END IF;
4209
4210 IF l_reservation_count > 1 THEN
4211 FND_MESSAGE.SET_NAME('INV','INV_NON_UNIQUE_RSV');
4212 FND_MSG_PUB.Add;
4213 RAISE fnd_api.g_exc_unexpected_error;
4214 END IF;
4215
4216 -- Determine whether the quantity to transfer is greater
4217 -- than the currently reserved quantity
4218 IF p_missing_quantity > l_reservations_tbl(1).primary_reservation_quantity THEN
4219 FND_MESSAGE.SET_NAME('INV','INV_INSUFF_QTY_RSV');
4220 FND_MSG_PUB.Add;
4221 RAISE fnd_api.g_exc_unexpected_error;
4222 END IF;
4223
4224 -- Initialize the querying record with the reservation ID so that the update
4225 -- will be more efficient.
4226 l_reservation_rec.reservation_id := l_reservations_tbl(1).reservation_id;
4227
4228 -- Validation complete - ready to transfer reservation to appropriate source
4229 l_cc_reservation_rec.primary_reservation_quantity := p_missing_quantity;
4230 l_cc_reservation_rec.primary_uom_code := l_reservations_tbl(1).primary_uom_code;
4231 l_cc_reservation_rec.secondary_reservation_quantity := p_secondary_missing_quantity ; --9251213
4232 l_cc_reservation_rec.detailed_quantity := 0;
4233 l_cc_reservation_rec.secondary_detailed_quantity := 0; --9251213
4234 l_cc_reservation_rec.demand_source_type_id := 9;
4235 l_cc_reservation_rec.demand_source_header_id := -1;
4236 l_cc_reservation_rec.demand_source_line_id := -1;
4237 l_cc_reservation_rec.subinventory_code := p_subinventory_code;
4238 l_cc_reservation_rec.locator_id := p_locator_id;
4239 l_cc_reservation_rec.revision := p_revision;
4240 l_cc_reservation_rec.lot_number := p_lot_number;
4241 /* Bug 7504490 - passing the lpn_id to the CC reservation record. */
4242 l_cc_reservation_rec.lpn_id := p_lpn_id;
4243
4244 -- Make the call to the Transfer Reservation API
4245 INV_Reservation_PUB.Transfer_Reservation
4246 (
4247 p_api_version_number => 1.0
4248 , p_init_msg_lst => fnd_api.g_true
4249 , x_return_status => l_api_return_status
4250 , x_msg_count => x_msg_count
4251 , x_msg_data => x_msg_data
4252 , p_original_rsv_rec => l_reservation_rec
4253 , p_to_rsv_rec => l_cc_reservation_rec
4254 , p_original_serial_number => l_dummy_sn
4255 , p_to_serial_number => l_dummy_sn
4256 , p_validation_flag => fnd_api.g_true
4257 , x_to_reservation_id => l_new_rsv_id
4258 );
4259 -- Return an error if the transfer reservations call failed
4260 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
4261 FND_MESSAGE.SET_NAME('INV','INV_TRANSFER_RSV_FAILED');
4262 FND_MSG_PUB.Add;
4263 RAISE fnd_api.g_exc_unexpected_error;
4264 END IF;
4265 x_new_rsv_id := l_new_rsv_id ; --Bug#8301348
4266 -- Commit if necessary
4267 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4268 ROLLBACK TO Reserve_Unconfirmed_Qty_PUB;
4269 ELSE
4270 -- Standard call to commit
4271 IF p_commit = fnd_api.g_true THEN
4272 COMMIT;
4273 END IF;
4274 END IF;
4275
4276 EXCEPTION
4277 WHEN FND_API.G_EXC_ERROR THEN
4278 --
4279 x_return_status := FND_API.G_RET_STS_ERROR;
4280 --
4281 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
4282 , p_data => x_msg_data);
4283 --
4284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4285 --
4286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4287 --
4288 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
4289 , p_data => x_msg_data);
4290 --
4291 WHEN OTHERS THEN
4292 ROLLBACK TO Reserve_Unconfirmed_Qty_PUB;
4293 --
4294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4295 --
4296 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4297 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4298 END IF;
4299 --
4300 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
4301 , p_data => x_msg_data);
4302 END Reserve_Unconfqty_lpn;
4303
4304
4305 END INV_Pick_Release_PUB;
[Home] [Help]