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