[Home] [Help]
PACKAGE BODY: APPS.INV_EXPRESS_PICK_PUB
Source
1 PACKAGE BODY INV_EXPRESS_PICK_PUB AS
2 /* $Header: INVEXPRB.pls 120.4 2006/09/14 10:13:15 bradha noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_EXPRESS_PICK_PUB';
4 g_true VARCHAR2(10) := 'T';
5 g_pr_status_cntr NUMBER :=0;
6 /* Cached values for locator control of Org and Sub */
7 g_organization_id NUMBER;
8 g_org_loc_control_code NUMBER;
9 g_subinventory_code VARCHAR2(10);
10 g_sub_loc_control_code NUMBER;
11 G_LOGIN_ID NUMBER;
12 G_USER_ID NUMBER;
13 G_LOGIN_ID NUMBER;
14 G_PROG_APPID NUMBER;
15 G_PROG_ID NUMBER;
16 G_REQUEST_ID NUMBER;
17
18 PROCEDURE print_debug(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
19 BEGIN
20 inv_pick_wave_pick_confirm_pub.tracelog(p_message, p_module);
21 END;
22
23
24 -- This function returns TRUE if the reservation passed in is detailed
25 -- False otherwise.
26 FUNCTION check_detailed_rsv(p_mo_line_rec INV_Move_Order_PUB.TROLIN_REC_TYPE
27 ,p_reservation_rec INV_reservation_global.mtl_reservation_rec_type)
28 RETURN BOOLEAN IS
29
30 l_detailed BOOLEAN;
31 BEGIN
32 l_detailed := TRUE;
33
34 IF p_reservation_rec.subinventory_code IS NULL THEN
35 Return False;
36 END IF;
37
38 IF NOT INV_CACHE.set_item_rec(p_mo_line_rec.organization_id, p_mo_line_rec.inventory_item_id) THEN
39 print_debug('Inventory Cache Set Item Rec Failed', 'INV_Express_Pick_Pub.Check_Detailed_Rsv');
40 RAISE fnd_api.g_exc_unexpected_error;
41 END IF;
42
43 If (inv_cache.item_rec.lot_control_code = 2) AND l_detailed THEN
44 if p_reservation_rec.lot_number IS NULL THEN
45 l_detailed := FALSE;
46 end if;
47 END IF;
48
49 If (inv_cache.item_rec.revision_qty_control_code = 2) AND l_detailed THEN
50 if p_reservation_rec.revision IS NULL THEN
51 l_detailed := FALSE;
52 end if;
53 END IF;
54
55 /* Locators not supported - If Org, Item or Sub is locator controlled don't match */
56 /* If reservation has locator don't match */
57 If (inv_cache.item_rec.location_control_code in (2,3)) THEN
58 l_detailed := FALSE;
59 END IF;
60
61 If (p_reservation_rec.locator_id is NOT NULL) THEN
62 l_detailed := FALSE;
63 END IF;
64
65 IF l_detailed THEN
66 IF p_mo_line_rec.organization_id <> nvl(g_organization_id,-9999) OR
67 p_reservation_rec.subinventory_code <> nvl(g_subinventory_code,'-9999') THEN
68
69 IF NOT INV_CACHE.set_tosub_rec(p_mo_line_rec.organization_id, p_reservation_rec.subinventory_code) THEN
70 print_debug('Inventory Cache Set ToSub Rec Failed', 'INV_Express_Pick_Pub.Check_Detailed_Rsv');
71 RAISE fnd_api.g_exc_unexpected_error;
72 END IF;
73
74 IF NOT INV_CACHE.set_org_rec(p_mo_line_rec.organization_id) THEN
75 print_debug('Inventory Cache Set ORG Rec Failed', 'INV_Express_Pick_Pub.Check_Detailed_Rsv');
76 RAISE fnd_api.g_exc_unexpected_error;
77 END IF;
78
79 g_organization_id := p_mo_line_rec.organization_id;
80 g_subinventory_code := p_reservation_rec.subinventory_code;
81 g_sub_loc_control_code := inv_cache.org_rec.stock_locator_control_code;
82 g_org_loc_control_code := inv_cache.tosub_rec.locator_type;
83 END IF;
84
85 If g_sub_loc_control_code in (2,3) OR g_org_loc_control_code in (2,3) THEN
86 l_detailed := FALSE;
87 end if;
88 END IF;
89
90 Return l_detailed;
91
92 END check_detailed_rsv;
93
94
95 PROCEDURE PICK_RELEASE ( p_api_version IN NUMBER
96 ,p_init_msg_list IN VARCHAR2
97 ,P_commit IN VARCHAR2
98 ,x_return_status OUT NOCOPY VARCHAR2
99 ,x_msg_count OUT NOCOPY NUMBER
100 ,x_msg_data OUT NOCOPY VARCHAR2
101 ,p_mo_line_tbl IN INV_Move_Order_PUB.TROLIN_TBL_TYPE
102 ,p_grouping_rule_id IN NUMBER
103 ,p_allow_partial_pick IN VARCHAR2
104 ,p_reservations_tbl IN inv_reservation_global.mtl_reservation_tbl_type
105 ,p_pick_release_status_tbl OUT NOCOPY inv_express_pick_pub.p_pick_release_status_tbl
106 ) IS
107
108 /*CURSOR c_rsv_rec (p_demand_source_type_id NUMBER
109 ,p_demand_source_line_id NUMBER) IS
110 SELECT reservation_id
111 , requirement_date
112 , organization_id
113 , inventory_item_id
114 , demand_source_type_id
115 , demand_source_name
116 , demand_source_header_id
117 , demand_source_line_id
118 , demand_source_delivery
119 , primary_uom_code
120 , primary_uom_id
121 , reservation_uom_code
122 , reservation_uom_id
123 , reservation_quantity
124 , primary_reservation_quantity
125 , detailed_quantity
126 , autodetail_group_id
127 , external_source_code
128 , external_source_line_id
129 , supply_source_type_id
130 , supply_source_header_id
131 , supply_source_line_id
132 , supply_source_name
133 , supply_source_line_detail
134 , revision
135 , subinventory_code
136 , subinventory_id
137 , locator_id
138 , lot_number
139 , lot_number_id
140 , pick_slip_number
141 , lpn_id
142 , attribute_category
143 , attribute1
144 , attribute2
145 , attribute3
146 , attribute4
147 , attribute5
148 , attribute6
149 , attribute7
150 , attribute8
151 , attribute9
152 , attribute10
153 , attribute11
154 , attribute12
155 , attribute13
156 , attribute14
157 , attribute15
158 , ship_ready_flag
159 from mtl_reservations
160 where demand_source_type_id =p_demand_source_type_id
161 and demand_source_line_id = p_demand_source_line_id
162 and supply_source_type_id = 13
163 and nvl(Staged_flag,'N') <> 'Y'
164 and nvl(detailed_quantity,0) = 0
165 order by primary_reservation_quantity;
166 Commented out for 3237610*/
167
168
169 l_rsv_rec_tbl inv_reservation_global.mtl_reservation_tbl_type ;
170 l_rsv_rec_ret_tbl inv_reservation_global.mtl_reservation_tbl_type ;
171 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
172 l_rsv_rec_param inv_reservation_global.mtl_reservation_rec_type; --Added for bug3237610
173 l_return_status VARCHAR2(1);
174 l_loop_index NUMBER;
175 l_loop_status NUMBER;
176 l_line_index NUMBER;
177 l_delivery_detail_id NUMBER;
178 l_source_type_id NUMBER;
179 l_source_line_id NUMBER;
180 l_debug NUMBER;
181 is_debug BOOLEAN;
182 l_ship_set_start_index NUMBER;
183 l_ship_set_start_status NUMBER;
184 l_cur_ship_set_id NUMBER;
185 l_rsv_qty NUMBER;
186 l_dd_qty NUMBER;
187 l_error_code NUMBER; --Bug3237610 added code
188 l_counter NUMBER; -- Added for Bug3237610
189 l_rsv_count NUMBER;
190 l_dtl_rsv_count NUMBER;
191 l_mo_line_count NUMBER;
192 l_api_version CONSTANT NUMBER := 1.0;
193 l_api_name CONSTANT VARCHAR2(30) := 'Express_Pick_Release';
194 l_staged_flag VARCHAR2(2);
195 l_rsv_start NUMBER; -- Added for bug 3946186
196
197 BEGIN
198
199 -- because the debug profile rarely changes, only check it once per
200 -- session, instead of once per batch
201 IF is_debug IS NULL THEN
202 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
203 if l_debug = 1 then
204 is_debug := TRUE;
205 else
206 is_debug := FALSE;
207 end if;
208 END IF;
209
210 -- Set savepoint for this API
211 If is_debug then
212 print_debug('Inside Express Pick_Release', 'INV_Express_Pick_Pub.Pick_Release');
213 End If;
214
215 SAVEPOINT EXPRESS_PICK_RELEASE;
216
217 -- Standard Call to check for call compatibility
218 IF NOT fnd_api.Compatible_API_Call(l_api_version , p_api_version ,
219 l_api_name , G_PKG_NAME) THEN
220 If is_debug then
221 print_debug('Fnd_APi not compatible','INV_Express_Pick_Pub.Pick_Release');
222 End If;
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225
226
227 -- Initialize message list if p_init_msg_list is set to true
228 IF fnd_api.to_Boolean(p_init_msg_list) THEN
229 fnd_msg_pub.initialize;
230 END IF;
231
232 -- Initialize API return status to success
233 x_return_status := fnd_api.g_ret_sts_success;
234
235 -- Validate parameters
236
237 -- First determine whether the table of move order lines in p_mo_line_tbl has
238 -- any records
239 l_mo_line_count := p_mo_line_tbl.COUNT;
240 IF l_mo_line_count = 0 THEN
241 If is_debug then
242 print_debug('No Lines to pick', 'INV_Express_Pick_Pub.Pick_Release');
243 End If;
244
245 ROLLBACK TO EXPRESS_PICK_RELEASE;
246 FND_MESSAGE.SET_NAME('INV','INV_NO_LINES_TO_PICK');
247 FND_MSG_PUB.Add;
248 RAISE fnd_api.g_exc_unexpected_error;
249 END IF;
250
251 -- Validate parameter for allowing partial pick release
252 IF p_allow_partial_pick <> fnd_api.g_true AND
253 p_allow_partial_pick <> fnd_api.g_false THEN
254
255 If is_debug then
256 print_debug('Error: invalid partial pick parameter',
257 'INV_Express_Pick_Pub.Pick_Release');
258 End If;
259 ROLLBACK TO Express_Pick_Release;
260 FND_MESSAGE.SET_NAME('INV','INV_INVALID_PARTIAL_PICK_PARAM');
261 FND_MSG_PUB.Add;
262 RAISE fnd_api.g_exc_unexpected_error;
263 END IF;
264
265
266 /*Start with first delivery detail in the list */
267 l_loop_index := p_mo_line_tbl.first;
268 g_pr_status_cntr := 1;
269
270 LOOP
271 l_line_index := l_loop_index;
272 /* Use this to reset line_index whether staying on current line or skipping
273 to end of failed shipset */
274 l_loop_index := l_line_index + 1;
275 If is_debug then
276 print_debug('Loop through delivery details',
277 'Inv_Express_Pick_Pub.Pick_Release');
278 End If;
279 IF p_mo_line_tbl(l_line_index).ship_set_id IS NOT NULL AND
280 (l_cur_ship_set_id IS NULL OR
281 l_cur_ship_set_id <> p_mo_line_tbl(l_line_index).ship_set_id) THEN
282
283 SAVEPOINT SHIPSET;
284 l_cur_ship_set_id := p_mo_line_tbl(l_line_index).ship_set_id;
285 l_ship_set_start_index := l_line_index;
286 l_ship_set_start_status := g_pr_status_cntr;
287 If is_debug then
288 print_debug('Start Shipset :' || l_cur_ship_set_id,
289 'Inv_Express_Pick_Pub.Pick_Release');
290 End If;
291 ELSIF l_cur_ship_set_id IS NOT NULL AND
292 p_mo_line_tbl(l_line_index).ship_set_id IS NULL THEN
293 If is_debug then
294 print_debug('End of Shipset :' || l_cur_ship_set_id,
295 'Inv_Express_Pick_Pub.Pick_Release');
296 End If;
297 l_cur_ship_set_id := NULL;
298 l_ship_set_start_index := NULL;
299 l_ship_set_start_status := NULL;
300 END IF;
301
302 If is_debug then
303 print_debug('Current positions - line_index : ' || l_line_index ||
304 ' g_pr_status_cntr : ' || g_pr_status_cntr,
305 'Inv_Express_Pick_Pub.Pick_Release');
306 End If;
307 l_delivery_detail_id:= p_mo_line_tbl(l_line_index).txn_source_line_detail_id;
308 p_pick_release_status_tbl(g_pr_status_cntr).delivery_detail_id := l_delivery_detail_id;
309
310 IF NOT INV_CACHE.set_mtt_rec(p_mo_line_tbl(l_line_index).transaction_type_id) THEN
311 print_debug('Inventory Cache Set Transaction Type Rec Failed', 'INV_Express_Pick_Pub.Pick_Release');
312 RAISE fnd_api.g_exc_unexpected_error;
313 END IF;
314
315 l_source_type_id := inv_cache.mtt_rec.transaction_source_type_id;
316 l_source_line_id := p_mo_line_tbl(l_line_index).TXN_source_line_id;
317
318 If is_debug then
319 print_debug('Checking reservations for Delivery Detail : ' || l_delivery_detail_id,
320 'Inv_Express_Pick_Pub.Pick_Release');
321 End If;
322 /* Get All Unstaged reservations for SALES ORDER LINE */
323 /* and ignore all unstaged or non detailed reservations */
324 l_rsv_count := 0;
325 l_dtl_rsv_count := 0;
326 l_rsv_qty := 0;
327 l_rsv_rec_tbl.delete;
328
329 /* Added for bug3237610*/
330 --l_rsv_rec_param.staged_flag := 'N';
331 l_rsv_rec_param.demand_source_type_id := l_source_type_id;
332 l_rsv_rec_param.demand_source_line_id := l_source_line_id;
333 l_rsv_rec_param.supply_source_type_id := 13;
334
335 inv_reservation_pub.query_reservation(
336 p_api_version_number => p_api_version
337 , x_return_status => x_return_status
338 , x_msg_count => x_msg_count
339 , x_msg_data => x_msg_data
340 , p_query_input => l_rsv_rec_param
341 , x_mtl_reservation_tbl => l_rsv_rec_ret_tbl
342 , x_mtl_reservation_tbl_count => l_rsv_count
343 , x_error_code => l_error_code
344 );
345
346 IF is_debug then
347 print_debug('l_return_status from query_reservation is '
348 || x_return_status, 'Inv_Express_Pick_Pub.Pick_Release');
349 End If;
350
351 IF x_return_status = fnd_api.g_ret_sts_error THEN
352 IF is_debug then
353 print_debug('Error from INV_QUANTITY_TREE_PVT.Lock_Tree',
354 'Inv_Express_Pick_Pub.Pick_release');
355 END IF;
356 RAISE fnd_api.g_exc_error ;
357 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
358 If is_debug then
359 print_debug('Unexpected error from INV_QUANTITY_TREE_PVT.Lock_Tree',
360 'Inv_Express_Pick_Pub.Pick_release');
361 End If;
362 RAISE fnd_api.g_exc_unexpected_error;
363 END IF;
364 /*Bug3237610 ends*/
365
366 If is_debug then
367 print_debug('Number of reservations ' || l_rsv_count, 'Inv_Express_Pick_Pub.Pick_Release');
368 End If;
369 l_rsv_start := 1;
370 --l_rsv_count := l_rsv_rec_tbl.count;
371 FOR l_counter IN l_rsv_start..l_rsv_count LOOP
372 --l_rsv_rec := l_rsv_rec_tbl(l_counter);
373
374 --Added bug3237610
375
376 If is_debug then
377 print_debug('Check whether reservation detailed rsv id : ' || l_rsv_rec_ret_tbl(l_counter).reservation_id, 'Inv_Express_Pick_Pub.Pick_Release');
378 End If;
379 inv_staged_reservation_util.query_staged_flag
380 (x_return_status => x_return_status
381 ,x_msg_count => x_msg_count
382 ,x_msg_data => x_msg_data
383 ,x_staged_flag => l_staged_flag
384 ,p_reservation_id => l_rsv_rec_ret_tbl(l_counter).reservation_id);
385
386 IF is_debug then
387 print_debug('l_return_status from query_staged_flag is '
388 || x_return_status, 'Inv_Express_Pick_Pub.Pick_Release');
389 End If;
390
391 IF x_return_status = fnd_api.g_ret_sts_error THEN
392 IF is_debug then
393 print_debug('Error from INV_QUANTITY_TREE_PVT.Lock_Tree',
394 'Inv_Express_Pick_Pub.Pick_release');
395 END IF;
396 RAISE fnd_api.g_exc_error ;
397 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
398 If is_debug then
399 print_debug('Unexpected error from INV_QUANTITY_TREE_PVT.Lock_Tree',
400 'Inv_Express_Pick_Pub.Pick_release');
401 End If;
402 RAISE fnd_api.g_exc_unexpected_error;
403 END IF;
404 --End bug3237610
405
406 IF nvl(l_staged_flag,'N') <> 'Y' then
407 IF check_detailed_rsv(p_mo_line_tbl(l_line_index), l_rsv_rec_ret_tbl(l_counter)) THEN
408 l_dtl_rsv_count := l_dtl_rsv_count +1;
409 l_rsv_rec_tbl(l_dtl_rsv_count):= l_rsv_rec_ret_tbl(l_counter);
410 l_rsv_qty := l_rsv_qty+l_rsv_rec_ret_tbl(l_counter).primary_reservation_quantity;
411 If is_debug then
412 print_debug('Reservation is Detailed' || l_dtl_rsv_count, 'Inv_Express_Pick_Pub.Pick_Release');
413 End If;
414 END IF;
415 END IF;
416 END LOOP;
417
418 l_dd_qty := p_mo_line_tbl(l_line_index).quantity ;
419 /* This is expected quantity to Pick release */
420 If is_debug then
421 print_debug('Quantity required to be Detailed ' || l_dd_qty, 'Inv_Express_Pick_Pub.Pick_Release');
422 print_debug('Quantity in detailed reservations' || l_rsv_qty, 'Inv_Express_Pick_Pub.Pick_Release');
423 print_debug('Current Ship Set ' || l_cur_ship_set_id, 'Inv_Express_Pick_Pub.Pick_Release');
424 End If;
425
426
427 IF (l_rsv_qty > 0) AND
428 ((l_rsv_qty >= l_dd_qty) OR
429 ((l_rsv_qty < l_dd_qty) AND (p_allow_partial_pick = g_true) AND (l_cur_ship_set_id IS NULL)))
430 THEN
431 --Lock item/org comobo so that no Pick release process could not release them concurrently.
432
433 INV_QUANTITY_TREE_PVT.Lock_Tree(p_api_version_number => l_api_version
434 , p_init_msg_lst => fnd_api.g_false
435 , x_return_status => x_return_status
436 , x_msg_count => x_msg_count
437 , x_msg_data => x_msg_data
438 , p_organization_id => p_mo_line_tbl(l_line_index).organization_id
439 , p_inventory_item_id => p_mo_line_tbl(l_line_index).inventory_item_id);
440
441 IF is_debug then
442 print_debug('l_return_status from lock_tree is '
443 || x_return_status, 'Inv_Express_Pick_Pub.Pick_Release');
444 End If;
445
446 IF x_return_status = fnd_api.g_ret_sts_error THEN
447 IF is_debug then
448 print_debug('Error from INV_QUANTITY_TREE_PVT.Lock_Tree',
449 'Inv_Express_Pick_Pub.Pick_release');
450 END IF;
451 RAISE fnd_api.g_exc_error ;
452 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
453 If is_debug then
454 print_debug('Unexpected error from INV_QUANTITY_TREE_PVT.Lock_Tree',
455 'Inv_Express_Pick_Pub.Pick_release');
456 End If;
457 RAISE fnd_api.g_exc_unexpected_error;
458 END IF;
459
460 stage_dd_rsv(p_mo_line_rec => P_MO_LINE_TBL(l_line_index)
461 , p_reservation_tbl => L_RSV_REC_TBL
462 , p_pick_release_status_tbl => p_pick_release_status_tbl
463 , x_return_status => x_return_status
464 , x_msg_count => x_msg_count
465 , x_msg_data => x_msg_data);
466
467 IF x_return_status = fnd_api.g_ret_sts_error THEN
468 IF is_debug then
469 print_debug('Error from Stage_DD_RSV',
470 'Inv_Express_Pick_Pub.Pick_release');
471 END IF;
472 RAISE fnd_api.g_exc_error ;
473 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
474 If is_debug then
475 print_debug('Unexpected error from Stage DD RSV',
476 'Inv_Express_Pick_Pub.Pick_release');
477 End If;
478 RAISE fnd_api.g_exc_unexpected_error;
479 END IF;
480
481
482 ELSE -- l_rsv_qty =0 OR partial not allowed
483
484 -- For shipsets, if any of the lines fail to allocate completely,
485 -- rollback all allocations and then Skip all delivery details in Ship Set
486 -- Report skipped status to shipping
487 If is_debug then
488 print_debug('Update shipping that ship set detailing failed',
489 'Inv_Express_Pick_Pub.Pick_Release');
490 End If;
491
492 If l_cur_ship_set_id is not null then
493 If is_debug then
494 print_debug('Rollback for shipset :' || l_cur_ship_set_id,
495 'Inv_Express_Pick_Pub.Pick_Release');
496 End If;
497
498 ROLLBACK TO SHIPSET;
499 l_loop_index :=l_ship_set_start_index;
500 l_loop_status := l_ship_set_start_status;
501 LOOP
502 p_pick_release_status_tbl(l_loop_status).Pick_status:='I';
503 IF p_pick_release_status_tbl(l_loop_status).delivery_detail_id IS NULL THEN
504 p_pick_release_status_tbl(l_loop_status).delivery_detail_id := p_mo_line_tbl(l_loop_index).txn_source_line_detail_id;
505 END IF;
506
507 EXIT WHEN p_mo_line_tbl.LAST = l_loop_index;
508
509 l_loop_status :=l_loop_status + 1;
510
511 IF (l_loop_status > p_pick_release_status_tbl.LAST) OR
512 (p_pick_release_status_tbl(l_loop_status).delivery_detail_id <> p_mo_line_tbl(l_loop_index).txn_source_line_detail_id) THEN
513 l_loop_index :=l_loop_index + 1;
514 END IF;
515
516 Exit when l_cur_ship_set_id <> p_mo_line_tbl(l_loop_index).ship_set_id;
517 END LOOP;
518 -- If loop reaches end of deliveries then set line_index to loop_index to force
519 -- exit from outer loop processing deliveries
520 IF (p_mo_line_tbl.LAST = l_loop_index) THEN
521 l_line_index := l_loop_index;
522 END IF;
523 END IF;
524
525 p_pick_release_status_tbl(g_pr_status_cntr).Pick_status:='F';
526 g_pr_status_cntr := p_pick_release_status_tbl.LAST + 1;
527 END IF;
528
529 EXIT WHEN l_line_index = p_mo_line_tbl.last;
530 END LOOP; ---Main Loop
531 EXCEPTION
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533 ROLLBACK TO EXPRESS_PICK_RELEASE;
534 --dbms_output.put_line('SQLERRM'||SQLERRM);
535 print_debug('SQLERRM'||SQLERRM, 'Inv_Express_Pick_Pub.Pick_Release');
536 --
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 --
539 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
540 , p_data => x_msg_data);
541 --
542 WHEN OTHERS THEN
543 ROLLBACK TO EXPRESS_PICK_RELEASE;
544 --
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
546 --
547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
548 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
549 END IF;
550 --
551 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
552 , p_data => x_msg_data);
553
554 END PICK_RELEASE;
555
556
557 PROCEDURE STAGE_DD_RSV(P_mo_line_REC IN INV_Move_Order_PUB.Trolin_Rec_Type
558 ,p_Reservation_tbl IN inv_reservation_global.mtl_reservation_tbl_type
559 ,p_pick_release_status_tbl IN OUT NOCOPY INV_EXPRESS_PICK_PUB.p_pick_release_status_tbl
560 , x_return_status OUT NOCOPY VARCHAR2
561 , x_msg_count OUT NOCOPY NUMBER
562 , x_msg_data OUT NOCOPY VARCHAR2) IS
563 /*This API stage a Delivery detail if reservations are detailed
564 and also mark serials and update reservations.
565 This require Digital enhancement as pre-req to work correctly if items are serial controlled
566 since we are not exploding serialized items.
567
568 Kalyan
569 */
570
571 TYPE inv_staged_rsv_id_rec IS RECORD
572 ( delivery_detail_id NUMBER
573 ,Split_delivery_detail_id NUMBER
574 ,Reservation_id NUMBER
575 ,Transaction_temp_id NUMBER
576 ,l_serial_index NUMBER
577 ,staged_quantity NUMBER
578 ,staged_secondary_quantity NUMBER --INVCONV kkillams
579 );
580 TYPE inv_staged_rsv_id_tbl is TABLE of inv_staged_rsv_id_rec
581 INDEX BY BINARY_INTEGER;
582 L_API_NAME VARCHAR2(20):='STAGED_DD_RSV';
583 l_rsv_rec_tbl inv_reservation_global.mtl_reservation_tbl_type ;
584 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
585 l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
586 l_new_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
587 l_orig_delivery_detail_id NUMBER;
588 l_staged_rsv_id_tbl inv_staged_rsv_id_tbl;
589 l_shipping_attr wsh_interface.changedattributetabtype;
590 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
591
592 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
593 l_rsv_index NUMBER :=0; --Split rsv counter
594 l_remain_dd_qty NUMBER :=0;
595 l_new_dd_id NUMBER;
596 l_qty2 NUMBER;
597 l_rsv_temp_rec inv_reservation_global.mtl_reservation_rec_type;
598 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type ;
599 l_mtl_reservation_count NUMBER;
600
601 l_temp_index NUMBER :=0;
602 l_delivery_detail_id NUMBER;
603 l_source_header_id NUMBER;
604 l_source_line_id NUMBER;
605 l_orig_dd_req_qty NUMBER;
606 l_rsv_count NUMBER;
607 l_reserved_qty NUMBER;
608
609 L_NEW_RESERVATION_ID NUMBER;
610 DELIVERY_DETAIL_ID NUMBER;
611 L_TRANSACTION_TEMP_ID NUMBER;
612 l_reservation_id NUMBER;
613 l_msg_count NUMBER;
614 L_MSG_DATA VARCHAR2(100);
615 L_ERROR_CODE VARCHAR2(100);
616 l_index NUMBER;
617 l_res_index NUMBER; --Split wdd counter
618 l_to_serial_number inv_reservation_global.serial_number_tbl_type;
619 l_last_rsv BOOLEAN:=false;
620 l_rsv_last_index NUMBER:=0;
621
622 l_serial_number_control_code NUMBER:=0;
623 is_serial_controlled Boolean :=false;
624 l_serial_number VARCHAR2(30);
625 x_available_sl_qty NUMBER:=0;
626 x_serial_index NUMBER;
627 x_serial_number VARCHAR2(30);
628 g_transaction_temp_id NUMBER;
629 l_transferred_rsv_qty NUMBER :=0;
630 l_partial_pick BOOLEAN :=false;
631 l_InvPCInRecType wsh_integration.invpcinrectype;
632
633 --INVCONV kkillams
634 l_orig_sec_req_qty NUMBER;
635 l_remain_sec_qty NUMBER;
636 l_reserved_sec_qty NUMBER;
637 l_available_sec_qty NUMBER := 0;
638 l_transferred_rsv_sec_qty NUMBER := 0;
639 l_preferred_grade VARCHAR2(150);
640 --END INVCONV kkillams
641
642
643 BEGIN
644 x_return_status :=fnd_api.g_ret_sts_success;
645 IF NOT INV_CACHE.set_item_rec(p_mo_line_rec.organization_id, p_mo_line_rec.inventory_item_id) THEN
646 print_debug('Inventory Cache Set Item Rec Failed', 'INV_Express_Pick_Pub.Pick_Release');
647 RAISE fnd_api.g_exc_unexpected_error;
648 END IF;
649 IF inv_cache.item_rec.serial_number_control_code NOT IN (1,6) then
650 print_debug('Item is Serialized ','INV_Express_Pick_Pub.Pick_Release');
651 is_serial_controlled :=true;
652 END IF;
653
654 l_rsv_rec_tbl :=p_Reservation_tbl;
655 l_orig_delivery_detail_id := P_mo_line_rec.txn_source_Line_detail_id;
656 l_orig_dd_req_qty :=P_MO_LINE_REC.quantity;
657 l_remain_dd_qty := l_orig_dd_req_qty;
658
659 --INVCONV kkillams
660 l_orig_sec_req_qty := p_mo_line_rec.secondary_quantity;
661 l_remain_sec_qty := p_mo_line_rec.secondary_quantity;
662 --END INVCONV kkillams
663
664 l_rsv_index := l_rsv_rec_tbl.FIRST;
665 l_rsv_count :=l_rsv_rec_tbl.count;
666 l_rsv_last_index :=l_rsv_rec_tbl.LAST;
667 print_debug(' l_rsv_last_index '||l_rsv_last_index,'EXPRESS_PICK');
668 print_debug('Reservation count '||l_rsv_count,'INV_Express_Pick_Pub.Pick_Release');
669 LOOP
670 l_original_rsv_rec := l_rsv_rec_tbl(l_rsv_index);
671 l_new_rsv_rec :=l_original_rsv_rec;
672 l_new_rsv_rec.reservation_id:=NULL;
673 l_reserved_qty := l_original_rsv_rec.primary_reservation_quantity;
674 l_reserved_sec_qty := l_original_rsv_rec.secondary_reservation_quantity; --INVCONV KKILLAMS
675 print_debug('l_current_reserved_qty '||l_reserved_qty,'INV_Express_Pick_Pub.Pick_Release');
676 print_debug('l_remain_dd_qty 1 '||l_remain_dd_qty,'INV_Express_Pick_Pub.Pick_Release');
677 IF l_remain_dd_qty >l_reserved_qty THEN
678 l_new_rsv_rec.primary_reservation_quantity :=l_reserved_qty;
679 l_new_rsv_rec.reservation_quantity :=NULL;
680 l_remain_dd_qty :=l_remain_dd_qty -l_reserved_qty;
681 --INVCONV kkillams
682 l_new_rsv_rec.secondary_reservation_quantity := l_reserved_sec_qty;
683 l_remain_sec_qty :=NVL(l_remain_sec_qty,0) -NVL(l_reserved_sec_qty,0);
684 IF l_remain_sec_qty = 0 THEN
685 l_remain_sec_qty := NULL;
686 END IF;
687 --END INVCONV kkillams
688 ELSE
689 l_new_rsv_rec.primary_reservation_quantity := l_remain_dd_qty;
690 l_remain_dd_qty :=0;
691 --INVCONV kkillams
692 l_new_rsv_rec.secondary_reservation_quantity := l_remain_sec_qty;
693
694 IF l_remain_sec_qty IS NOT NULL THEN
695 l_remain_sec_qty :=0;
696 END IF;
697 --END INVCONV kkillams
698 END IF;--Remain_dd_qty
699
700 IF l_new_rsv_rec.secondary_reservation_quantity = 0 THEN
701 l_new_rsv_rec.secondary_reservation_quantity := NULL;
702 END IF;
703 print_debug('modified l_remain_dd_qty 1 '||l_remain_dd_qty,'INV_Express_Pick_Pub.Pick_Release');
704 IF IS_SERIAL_CONTROLLED THEN
705 print_debug('Calling Pick serial Numbers ','INV_Express_Pick_Pub.Pick_Release');
706 PICK_SERIAL_NUMBERS(
707 p_inventory_item_id =>l_new_rsv_rec .inventory_item_id
708 , p_organization_id => l_new_rsv_rec.organization_id
709 , p_revision => l_new_rsv_rec.revision
710 , p_lot_number => l_new_rsv_rec.lot_number
711 ,p_subinventory_code => l_new_rsv_rec.subinventory_code
712 , p_locator_id => l_new_rsv_rec.locator_id
713 , p_required_sl_qty => l_new_rsv_rec.primary_reservation_quantity
714 , p_unit_number => null
715 , p_reservation_id => l_original_rsv_rec.reservation_id -- Bug 5517498
716 , x_available_sl_qty => x_available_sl_qty
717 , g_transaction_temp_id => g_transaction_temp_id
718 , x_serial_index => x_serial_index
719 , x_return_status => l_return_status
720 , x_msg_count => l_msg_count
721 , x_msg_data => l_msg_data
722 , x_serial_number => x_serial_number -- Bug 5517498
723 );
724 print_debug('return status '||l_return_status,'INV_Express_Pick_Pub.Pick_Release');
725 print_debug('g_transaction_temp_id'||g_transaction_temp_id,'INV_Express_Pick_Pub.Pick_Release');
726 print_debug('Available Serial qty'||x_available_sl_qty,'INV_Express_Pick_Pub.Pick_Release');
727 print_debug('x_serial_index'||x_serial_index,'INV_Express_Pick_Pub.Pick_Release');
728 IF l_return_status = fnd_api.g_ret_sts_error THEN
729 RAISE fnd_api.g_exc_error;
730 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
731 RAISE fnd_api.g_exc_unexpected_error;
732 END IF;
733 IF x_available_sl_qty < l_new_rsv_rec.primary_reservation_quantity THEN
734 l_remain_dd_qty := l_remain_dd_qty + (l_new_rsv_rec.primary_reservation_quantity- x_available_sl_qty);
735 l_new_rsv_rec.primary_reservation_quantity := x_available_sl_qty;
736 --INVCONV kkillams
737 IF l_new_rsv_rec.secondary_uom_code IS NOT NULL THEN
738 l_available_sec_qty:= inv_convert.inv_um_convert(
739 ITEM_ID => l_new_rsv_rec.inventory_item_id,
740 LOT_NUMBER => l_new_rsv_rec.lot_number,
741 ORGANIZATION_ID => l_new_rsv_rec.organization_id,
742 PRECISION => NULL,
743 FROM_QUANTITY => x_available_sl_qty,
744 FROM_UNIT => l_new_rsv_rec.primary_uom_code,
745 TO_UNIT => l_new_rsv_rec.secondary_uom_code,
746 FROM_NAME => NULL,
747 TO_NAME => NULL);
748 l_remain_sec_qty := NVL(l_remain_sec_qty,0) + (NVL(l_new_rsv_rec.secondary_reservation_quantity,0) - NVL(l_available_sec_qty,0));
749 IF l_remain_sec_qty =0 THEN
750 l_remain_sec_qty := NULL;
751 END IF;
752 l_new_rsv_rec.secondary_reservation_quantity := l_available_sec_qty;
753
754 END IF;
755 --END INVCONV kkillams
756 END IF;
757
758
759 -- Bug 5517498 Passing serial_reservation_quantity and serial_number to the new reservation record
760 l_new_rsv_rec.serial_reservation_quantity := x_available_sl_qty;
761 IF x_available_sl_qty = 1 THEN
762 l_new_rsv_rec.serial_number := x_serial_number;
763 END IF;
764 END IF;
765 l_transferred_rsv_qty :=l_transferred_rsv_qty + l_new_rsv_rec.primary_reservation_quantity;
766 -- l_new_rsv_rec.staged_flag := 'Y';
767 l_new_rsv_rec.detailed_quantity :=0;
768 l_new_rsv_rec.ship_ready_flag := 1;
769 l_new_rsv_rec.requirement_date :=SYSDATE;
770 --dbms_output.put_line('about to call transfer_reservation');
771 inv_reservation_pub.transfer_reservation(
772 p_api_version_number => 1.0
773 , p_init_msg_lst => fnd_api.g_false
774 , x_return_status => l_return_status
775 , x_msg_count => l_msg_count
776 , x_msg_data => l_msg_data
777 , p_original_rsv_rec => l_original_rsv_rec
778 , p_to_rsv_rec => l_new_rsv_rec
779 , p_original_serial_number => l_to_serial_number
780 , p_to_serial_number => l_to_serial_number
781 , p_validation_flag => fnd_api.g_false
782 , x_to_reservation_id => l_reservation_id
783 );
784 IF l_return_status = fnd_api.g_ret_sts_error THEN
785 RAISE fnd_api.g_exc_error;
786 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
787 RAISE fnd_api.g_exc_unexpected_error;
788 END IF;
789
790
791 --dbms_output.put_line('l_return_status '||l_return_status);
792 print_debug('msg from Transfer rsv '||substr(l_msg_data,1,100),'INV_Express_pick.Pick_release');
793 inv_staged_reservation_util.update_staged_flag(x_return_status => l_return_status,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data,
796 p_reservation_id => l_reservation_id,
797 p_staged_flag => 'Y');
798 IF l_return_status = fnd_api.g_ret_sts_error THEN
799 RAISE fnd_api.g_exc_error;
800 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
801 RAISE fnd_api.g_exc_unexpected_error;
802 END IF;
803 if l_remain_dd_qty >0 then
804 print_debug('Calling Split line','INV_Express_pick.Pick_release ');
805
806 WSH_DELIVERY_DETAILS_PUB.split_line(
807 p_api_version => 1.0
808 , x_return_status => l_return_status
809 , x_msg_count => l_msg_count
810 , x_msg_data => l_msg_data
811 ,p_from_detail_id => l_orig_delivery_detail_id
812 ,x_new_detail_id => l_new_dd_id
813 ,x_split_quantity => l_new_rsv_rec.primary_reservation_quantity
814 ,x_split_quantity2 => l_new_rsv_rec.secondary_reservation_quantity);
815 print_debug('Return status '||l_return_status,'INV_Express_pick.Pick_release ');
816 print_debug('l_new_dd_id '||l_new_dd_id,'INV_Express_pick.Pick_release ');
817 IF l_return_status = fnd_api.g_ret_sts_error THEN
818 RAISE fnd_api.g_exc_error;
819 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
820 RAISE fnd_api.g_exc_unexpected_error;
821 END IF;
822 end if; --l_remain_dd_qty >0
823 l_temp_index :=l_temp_index+1;
824 l_staged_rsv_id_tbl(l_temp_index).reservation_id :=l_reservation_id;
825 l_staged_rsv_id_tbl(l_temp_index).staged_quantity := l_new_rsv_rec.primary_reservation_quantity;
826 l_staged_rsv_id_tbl(l_temp_index).staged_secondary_quantity := l_new_rsv_rec.secondary_reservation_quantity; --INVCONV kkillams
827 l_staged_rsv_id_tbl(l_temp_index).delivery_detail_id :=l_orig_delivery_detail_id;
828 if l_new_dd_id is NOT NULL then
829 l_staged_rsv_id_tbl(l_temp_index).Split_delivery_detail_id:=l_new_dd_id;
830 end if;
831 l_new_dd_id :=NULL;
832 l_staged_rsv_id_tbl(l_temp_index).Transaction_temp_id :=g_transaction_temp_id;
833 l_staged_rsv_id_tbl(l_temp_index).l_serial_index :=x_serial_index;
834 EXIT WHEN l_rsv_index =l_rsv_rec_tbl.LAST OR l_remain_dd_qty =0 ;
835 l_rsv_index :=l_rsv_rec_tbl.NEXT(l_rsv_index);
836 END LOOP;
837 if l_orig_dd_req_qty > l_transferred_rsv_qty then
838 print_debug('Partial Pick Total Staged'||l_transferred_rsv_qty, 'INV_Express_pick.Pick_release ');
839 l_partial_pick :=true;
840 end if;
841
842 --Now split wdd for each reservation record and call shipping to stage them
843 FOR l_index IN 1..l_staged_rsv_id_tbl.count LOOP
844 l_res_index := l_index;
845 l_rsv_temp_rec :=NULL;
846 l_rsv_temp_rec.reservation_id :=l_staged_rsv_id_tbl(l_res_index).reservation_id;
847 print_debug('Current reservation_id '||l_rsv_temp_rec.reservation_id,
848 'INV_Express_pick.Pick_release ');
849 inv_reservation_pub.query_reservation(
850 p_api_version_number => 1.0
851 , p_init_msg_lst => fnd_api.g_true
852 , x_return_status => l_return_status
853 , x_msg_count => l_msg_count
854 , x_msg_data => l_msg_data
855 , p_query_input => l_rsv_temp_rec
856 , x_mtl_reservation_tbl => l_mtl_reservation
857 , x_mtl_reservation_tbl_count => l_mtl_reservation_count
858 , x_error_code => l_error_code
859 );
860 IF l_return_status = fnd_api.g_ret_sts_error THEN
861 RAISE fnd_api.g_exc_error;
862 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
863 RAISE fnd_api.g_exc_unexpected_error;
864 END IF;
865
866 print_debug('Split WDD'||l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id
867 ,'INV_Express_pick.Pick_release ');
868
869 print_debug('Original WDD '||l_staged_rsv_id_tbl(l_res_index).delivery_detail_id
870 ,'INV_Express_pick.Pick_release ');
871 --Lock newly created WDD and original also.
872 BEGIN
873 SELECT delivery_detail_id
874 , source_header_id
875 , source_line_id
876 , preferred_grade --INVCONV kkillams
877 INTO l_delivery_detail_id
878 , l_source_header_id
879 , l_source_line_id
880 , l_preferred_grade --INVCONV kkillams
881 FROM wsh_delivery_details
882 WHERE delivery_detail_id =
883 nvl(l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id,
884 l_staged_rsv_id_tbl(l_res_index).delivery_detail_id)
885 FOR UPDATE NOWAIT;
886 EXCEPTION
887 WHEN OTHERS THEN
888 fnd_message.set_name('INV', 'INV_DELIV_INFO_MISSING');
889 fnd_msg_pub.ADD;
890 RAISE fnd_api.g_exc_error;
891 END;
892 l_shipping_attr(1).preferred_grade := l_preferred_grade; --INVCONV kkillams
893 l_shipping_attr(1).source_header_id := l_source_header_id;
894 l_shipping_attr(1).source_line_id := l_source_line_id;
895 l_shipping_attr(1).ship_from_org_id := l_mtl_reservation(1).organization_id;
896 l_shipping_attr(1).subinventory := l_mtl_reservation(1).subinventory_code;
897 l_shipping_attr(1).revision := l_mtl_reservation(1).revision;
898 l_shipping_attr(1).lot_number := l_mtl_reservation(1).lot_number;
899 l_shipping_attr(1).locator_id := l_mtl_reservation(1).locator_id;
900 l_shipping_attr(1).Picked_quantity := l_staged_rsv_id_tbl(l_res_index).staged_quantity;
901 l_shipping_attr(1).Picked_quantity2 := l_staged_rsv_id_tbl(l_res_index).staged_secondary_quantity; ---INVCONV kkillams
902 l_shipping_attr(1).released_status := 'Y';
903 l_shipping_attr(1).delivery_detail_id := nvl(l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id,
904 l_staged_rsv_id_tbl(l_res_index).delivery_detail_id);
905 -- BUG 3604139
906 -- For each reservation reset the transaction_temp_id passed to shipping. This caused a problem
907 -- when moving from passing in a single serial number to many serial numbers, as shipping
908 -- expects either the single serial or the transaction_temp_id not both.
909 l_InvPCInRecType.transaction_temp_id := NULL;
910 l_shipping_attr(1).serial_number := NULL;
911 IF l_staged_rsv_id_tbl(l_res_index).transaction_temp_id IS NOT NULL THEN
912 IF l_staged_rsv_id_tbl(l_res_index).l_serial_index > 1 THEN
913 l_InvPCInRecType.transaction_temp_id :=l_staged_rsv_id_tbl(l_res_index).transaction_temp_id;
914
915 ELSE
916 SELECT fm_serial_number INTO l_serial_number
917 FROM mtl_serial_numbers_temp WHERE
918 transaction_temp_id = l_staged_rsv_id_tbl(l_res_index).transaction_temp_id;
919 print_debug('fm_serial_number '||l_serial_number,'Inv_Express_Pick.Pick_release');
920 l_shipping_attr(1).serial_number :=l_serial_number;
921 END IF;
922 END IF;
923
924
925 l_InvPCInRecType.source_code :='INV';
926 l_InvPCInRecType.api_version_number :=1.0;
927 WSH_INTEGRATION.Set_Inv_PC_Attributes
928 ( p_in_attributes => l_InvPCInRecType,
929 x_return_status => l_return_status,
930 x_msg_count => l_msg_count,
931 x_msg_data => l_msg_data );
932 print_debug('after Set_Inv_PC_Attributes Ret status'||l_return_status, 'Inv_Express_Pick.Pick_release');
933
934 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
935 print_debug('return error E from Set_Inv_PC_Attributes', 'Inv_Express_Pick.Pick_release');
936 RAISE fnd_api.g_exc_error;
937 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
938 print_debug('return error U from Set_Inv_PC_Attributes', 'Inv_Express_Pick.Pick_release');
939 RAISE fnd_api.g_exc_unexpected_error;
940 END IF;
941
942 l_shipping_attr(1).action_flag:='U';
943 wsh_interface.update_shipping_attributes(p_source_code => 'INV',
944 p_changed_attributes => l_shipping_attr,
945 x_return_status => l_return_status);
946 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
947 RAISE fnd_api.g_exc_error;
948 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
949 RAISE fnd_api.g_exc_unexpected_error;
950 END IF;
951 p_pick_release_status_tbl(g_pr_status_cntr).delivery_detail_id := l_staged_rsv_id_tbl(l_res_index).delivery_detail_id;
952 p_pick_release_status_tbl(g_pr_status_cntr).Pick_status :='S';
953 if l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id is not null then
954 p_pick_release_status_tbl(g_pr_status_cntr).split_delivery_id := l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id;
955 end if;
956 g_pr_status_cntr :=g_pr_status_cntr+1;
957
958 END LOOP;
959 if l_partial_pick then --Load original WDD if partial Pick otherwise shipping do not know
960 p_pick_release_status_tbl(g_pr_status_cntr).delivery_detail_id := l_orig_delivery_detail_id;
961 p_pick_release_status_tbl(g_pr_status_cntr).Pick_status :='P';
962 g_pr_status_cntr :=g_pr_status_cntr+1;
963 end if;
964 x_return_status :=l_return_status ;
965 print_debug(' Exit status from Stage_dd_rsv'||x_return_status,'Inv_Express_pick.Pick_release' );
966
967 EXCEPTION
968 WHEN FND_API.G_EXC_ERROR THEN
969 --dbms_output.put_line('SQLERRM'||SQLERRM);
970 --
971 x_return_status := FND_API.G_RET_STS_ERROR;
972 --
973 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
974 , p_data => x_msg_data);
975 --
976 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
977 --dbms_output.put_line('SQLERRM'||SQLERRM);
978 --
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 --
981 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
982 , p_data => x_msg_data);
983 --
984 WHEN OTHERS THEN
985 --ROLLBACK TO EXPRESS_PICK_PUB;
986 --
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 --
989 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
990 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
991 END IF;
992 --
993 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
994 , p_data => x_msg_data);
995 END STAGE_DD_RSV;
996
997 PROCEDURE PICK_SERIAL_NUMBERS(
998 p_inventory_item_id IN NUMBER
999 , p_organization_id IN NUMBER
1000 , p_revision IN VARCHAR2
1001 , p_lot_number IN VARCHAR2
1002 , p_subinventory_code IN VARCHAR2
1003 , p_locator_id IN NUMBER
1004 , p_required_sl_qty IN NUMBER
1005 , p_unit_number IN NUMBER
1006 , p_reservation_id IN NUMBER -- Bug 5517498
1007 , x_available_sl_qty OUT NOCOPY NUMBER
1008 , g_transaction_temp_id OUT NOCOPY NUMBER
1009 , x_serial_index OUT NOCOPY NUMBER
1010 , x_return_status OUT NOCOPY VARCHAR2
1011 , x_msg_count OUT NOCOPY NUMBER
1012 , x_msg_data OUT NOCOPY VARCHAR2
1013 , x_serial_number OUT NOCOPY VARCHAR2 -- Bug 5517498
1014 ) IS
1015 cursor msnc IS
1016 SELECT msn.serial_number
1017 FROM mtl_serial_numbers msn
1018 WHERE msn.inventory_item_id = p_inventory_item_id
1019 AND msn.current_organization_id = p_organization_id
1020 AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
1021 AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
1022 AND nvl(msn.current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
1023 AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
1024 -- AND nvl(msn.end_item_unit_number,'@@@') = nvl(p_unit_number,'@@@')
1025 AND msn.current_status = 3
1026 AND ((msn.group_mark_id is null) or (msn.group_mark_id = -1))
1027 ORDER BY msn.serial_number;
1028 -- Added cursor for bug 5517498
1029 cursor msn_reserved IS
1030 SELECT msn.serial_number
1031 FROM mtl_serial_numbers msn
1032 WHERE msn.reservation_id = p_reservation_id
1033 ORDER BY msn.serial_number;
1034
1035 l_msnt_tbl_size NUMBER:=0;
1036 G_LOGIN_ID NUMBER;
1037 G_USER_ID NUMBER;
1038 G_LOGIN_ID NUMBER;
1039 G_PROG_APPID NUMBER;
1040 G_PROG_ID NUMBER;
1041 G_REQUEST_ID NUMBER;
1042 --g_transaction_temp_id NUMBER;
1043 l_serial_number VARCHAR2(100);
1044 BEGIN
1045 x_return_status :=fnd_api.g_ret_sts_success;
1046
1047 print_debug('Required Serialqty '||p_required_sl_qty,
1048 'INV_Express_pick.Pick_release');
1049 if G_USER_ID IS NULL OR G_REQUEST_ID IS NULL then
1050 G_USER_ID := FND_GLOBAL.user_id ;
1051 G_PROG_APPID := FND_GLOBAL.prog_appl_id ;
1052 G_PROG_ID := FND_GLOBAL.conc_program_id;
1053 G_REQUEST_ID := FND_GLOBAL.conc_request_id ;
1054 end if;
1055 g_transaction_temp_id :=NULL;
1056 x_available_sl_qty :=0;
1057 x_serial_index :=0;
1058
1059 -- Begin of Bug Fix 5517498
1060 FOR msn_rec IN msn_reserved LOOP
1061 --dbms_output.put_line('inside loop ');
1062 print_debug('getting serial numbers based on reservation_id ','INV_Express_pick.Pick_release');
1063 if g_transaction_temp_id is NULL then
1064 print_debug('get new Transaction_temp_id ','Inv_Express_pick.Pick_release');
1065 SELECT mtl_material_transactions_s.NEXTVAL into g_transaction_temp_id
1066 FROM dual;
1067 end if;
1068 IF (x_available_sl_qty >= p_required_sl_qty) THEN
1069 EXIT;
1070 END IF;
1071 print_debug('lock Serial ',
1072 'INV_Express_pick.Pick_release');
1073
1074 BEGIN
1075 SELECT serial_number into l_serial_number
1076 FROM mtl_serial_numbers
1077 WHERE inventory_item_id = p_inventory_item_id
1078 AND serial_number = msn_rec.serial_number
1079 FOR UPDATE nowait;
1080 EXCEPTION
1081 WHEN OTHERS then
1082 null;
1083 END;
1084
1085 l_msnt_tbl_size := l_msnt_tbl_size +1;
1086 INSERT INTO mtl_serial_numbers_temp
1087 (
1088 transaction_temp_id
1089 ,last_update_date
1090 ,last_updated_by
1091 ,creation_date
1092 ,created_by
1093 ,last_update_login
1094 ,request_id
1095 ,program_application_id
1096 ,program_id
1097 ,program_update_date
1098 ,vendor_serial_number
1099 ,vendor_lot_number
1100 ,fm_serial_number
1101 ,to_serial_number
1102 ,serial_prefix
1103 ,error_code
1104 ,group_header_id
1105 ,parent_serial_number
1106 ,end_item_unit_number
1107 )
1108 VALUES
1109 (
1110 g_transaction_temp_id
1111 ,sysdate
1112 ,g_user_id
1113 ,sysdate
1114 ,g_user_id
1115 ,null
1116 ,g_request_id
1117 ,g_prog_appid
1118 ,G_PROG_ID
1119 ,sysdate
1120 ,null
1121 ,null
1122 ,msn_rec.serial_number
1123 ,msn_rec.serial_number
1124 ,1
1125 ,null
1126 ,null
1127 ,null
1128 ,null
1129 );
1130
1131
1132 UPDATE mtl_serial_numbers
1133 SET group_mark_id = g_transaction_temp_id
1134 WHERE inventory_item_id = p_inventory_item_id
1135 AND serial_number =msn_rec.serial_number;
1136
1137 x_available_sl_qty := x_available_sl_qty + 1;
1138 x_serial_index :=x_serial_index+1;
1139 l_serial_number := msn_rec.serial_number;
1140 END LOOP;
1141 -- End of Bug Fix 5517498
1142
1143 IF x_available_sl_qty < p_required_sl_qty THEN -- Added If condition for Bug 5517498
1144 FOR msn_rec IN msnc LOOP
1145 --dbms_output.put_line('inside loop ');
1146 if g_transaction_temp_id is NULL then
1147 print_debug('get new Transaction_temp_id ','Inv_Express_pick.Pick_release');
1148 SELECT mtl_material_transactions_s.NEXTVAL into g_transaction_temp_id
1149 FROM dual;
1150 end if;
1151 IF (x_available_sl_qty >= p_required_sl_qty) THEN
1152 EXIT;
1153 END IF;
1154 print_debug('lock Serial ',
1155 'INV_Express_pick.Pick_release');
1156
1157 BEGIN
1158 SELECT serial_number into l_serial_number
1159 FROM mtl_serial_numbers
1160 WHERE inventory_item_id = p_inventory_item_id
1161 AND serial_number = msn_rec.serial_number
1162 FOR UPDATE nowait;
1163 EXCEPTION
1164 WHEN OTHERS then
1165 null;
1166 END;
1167
1168 l_msnt_tbl_size := l_msnt_tbl_size +1;
1169 INSERT INTO mtl_serial_numbers_temp
1170 (
1171 transaction_temp_id
1172 ,last_update_date
1173 ,last_updated_by
1174 ,creation_date
1175 ,created_by
1176 ,last_update_login
1177 ,request_id
1178 ,program_application_id
1179 ,program_id
1180 ,program_update_date
1181 ,vendor_serial_number
1182 ,vendor_lot_number
1183 ,fm_serial_number
1184 ,to_serial_number
1185 ,serial_prefix
1186 ,error_code
1187 ,group_header_id
1188 ,parent_serial_number
1189 ,end_item_unit_number
1190 )
1191 VALUES
1192 (
1193 g_transaction_temp_id
1194 ,sysdate
1195 ,g_user_id
1196 ,sysdate
1197 ,g_user_id
1198 ,null
1199 ,g_request_id
1200 ,g_prog_appid
1201 ,G_PROG_ID
1202 ,sysdate
1203 ,null
1204 ,null
1205 ,msn_rec.serial_number
1206 ,msn_rec.serial_number
1207 ,1
1208 ,null
1209 ,null
1210 ,null
1211 ,null
1212 );
1213
1214
1215 UPDATE mtl_serial_numbers
1216 SET group_mark_id = g_transaction_temp_id,
1217 reservation_id = p_reservation_id
1218 WHERE inventory_item_id = p_inventory_item_id
1219 AND serial_number =msn_rec.serial_number;
1220
1221 x_available_sl_qty := x_available_sl_qty + 1;
1222 x_serial_index :=x_serial_index+1;
1223 l_serial_number := msn_rec.serial_number;
1224
1225 END LOOP;
1226 END IF;
1227
1228 x_serial_index :=l_msnt_tbl_size;
1229 -- Returning x_serial_number for Bug Fix 5517498
1230 IF x_available_sl_qty = 1 THEN
1231 x_serial_number := l_serial_number;
1232 END IF;
1233 print_debug('Available Serial '||x_available_sl_qty,'INV_Express_pick.Pick_release');
1234 print_debug('return status '||x_return_status,'INV_Express_pick.Pick_release');
1235
1236
1237
1238 EXCEPTION
1239 when fnd_api.g_exc_error then
1240 x_return_status := fnd_api.g_ret_sts_error ;
1241 x_available_sl_qty := 0;
1242 x_serial_index := 0;
1243
1244 when fnd_api.g_exc_unexpected_error then
1245 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1246 x_available_sl_qty := 0;
1247 x_serial_index := 0;
1248
1249 when others then
1250 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1251 x_available_sl_qty := 0;
1252 x_serial_index := 0;
1253
1254 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
1255 fnd_msg_pub.add_exc_msg('Pick Serial Numbers', 'PICK Serial Numbers ');
1256 end if;
1257
1258 END pick_serial_numbers;
1259 END INV_EXPRESS_PICK_PUB;