[Home] [Help]
PACKAGE BODY: APPS.GMI_PICK_RELEASE_UTIL
Source
1 PACKAGE BODY GMI_Pick_Release_Util AS
2 /* $Header: GMIUPKRB.pls 115.17 2004/01/26 20:28:22 lswamy ship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIUPKRS.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains Utilities procedures relating to GMI |
13 | Pick Release process. |
14 | |
15 | - Get_Delivery_Details |
16 | - Create_Pick_Slip_and_Print |
17 | |
18 | |
19 | HISTORY |
20 | 04-May-2000 odaboval Created |
21 | 03-06-2003 BUG#: 2837671 |
22 | Changed the call to wsh_pr_pick_slip_number |
23 | to call inv_pr_pick_slip_number if 110509 installed |
24 | This was changed per shipping recommendation since the |
25 | INV team is the owner of the pick slip package |
26 | This fix is included in patch 2694399. |
27 | |
28 +=========================================================================+
29 API Name : GMI_Pick_Release_Util
30 Type : Global
31 -
32 Pre-reqs : N/A
33 Parameters: Per function
34
35 Current Vers : 1.0
36 */
37
38
39 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_Pick_Release_Util';
40
41 PROCEDURE Get_Delivery_Details
42 ( p_mo_line_id IN NUMBER
43 , x_inv_delivery_details OUT NOCOPY WSH_INV_DELIVERY_DETAILS_V%ROWTYPE
44 , x_return_status OUT NOCOPY VARCHAR2
45 , x_msg_count OUT NOCOPY NUMBER
46 , x_msg_data OUT NOCOPY VARCHAR2
47 ) IS
48
49 /* Define local variables */
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_api_name CONSTANT VARCHAR2(30) := 'Get_Delivery_Details';
52
53
54 -- Bug 1805216, added NOT NULL in the cursor.
55 CURSOR c_delivery( mo_line_id IN NUMBER) IS
56 SELECT *
57 FROM wsh_inv_delivery_details_v
58 WHERE move_order_line_id = mo_line_id
59 AND move_order_line_id IS NOT NULL
60 AND released_status = 'S';
61
62
63 BEGIN
64
65 /* Init Return Status */
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 GMI_Reservation_Util.PrintLn('In Get_Delivery_Details mo_line_id='||p_mo_line_id);
69
70 OPEN c_delivery( p_mo_line_id);
71 FETCH c_delivery
72 INTO x_inv_delivery_details;
73
74 IF ( c_delivery%NOTFOUND )
75 THEN
76 FND_MESSAGE.SET_NAME('GMI','INV_DELIV_INFO_MISSING');
77 FND_MESSAGE.Set_Token('MO_LINE_ID',p_mo_line_id);
78 FND_MSG_PUB.Add;
79 RAISE FND_API.G_EXC_ERROR;
80 END IF;
81
82 GMI_Reservation_Util.PrintLn('In Get_Delivery_Details NO Error');
83
84 EXCEPTION
85 WHEN FND_API.G_EXC_ERROR THEN
86 x_return_status := FND_API.G_RET_STS_ERROR;
87
88 /* Get message count and data */
89 FND_MSG_PUB.count_and_get
90 ( p_count => x_msg_count
91 , p_data => x_msg_data
92 );
93
94
95 WHEN OTHERS THEN
96 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
97
98 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
99 , l_api_name
100 );
101
102 /* Get message count and data */
103 FND_MSG_Pub.Count_and_Get
104 ( p_count => x_msg_count
105 , p_data => x_msg_data
106 );
107
108
109 END Get_Delivery_Details;
110
111 PROCEDURE Create_Pick_Slip_and_Print
112 ( p_mo_line_rec IN GMI_Move_Order_Global.mo_line_rec
113 , p_inv_delivery_details IN WSH_INV_DELIVERY_DETAILS_V%ROWTYPE
114 , p_pick_slip_mode IN VARCHAR2
115 , p_grouping_rule_id IN NUMBER
116 , p_allow_partial_pick IN VARCHAR2 DEFAULT FND_API.G_TRUE
117 , x_return_status OUT NOCOPY VARCHAR2
118 , x_msg_count OUT NOCOPY NUMBER
119 , x_msg_data OUT NOCOPY VARCHAR2
120 ) IS
121
122 /* Define local variables */
123 l_api_version CONSTANT NUMBER := 1.0;
124 l_api_name CONSTANT VARCHAR2(30) := 'Create_Pick_Slip_and_Print';
125
126 l_trans_id NUMBER;
127 /* The transaction temp ID of the move order */
128 /* line detail being processed */
129 l_sub_code VARCHAR2(10);
130 /* The subinventory code for the move order */
131 /* line detail being processed */
132 l_pick_slip_mode VARCHAR2(1);
133 /* The print pick slip mode (immediate or */
134 /* deferred) that should be used */
135 l_pick_slip_number NUMBER;
136 /* The pick slip number to put on the */
137 /* Move Order Line Details for a Line. */
138 l_reservation_detailed_qty NUMBER;
139 /* The qty detailed for a reservation. */
140 l_ready_to_print VARCHAR2(1);
141 /* The flag for whether we need to */
142 /* commit and print after receiving */
143 /* the current pick slip number. */
144 l_api_error_msg VARCHAR2(100);
145 /* The error message returned by certain */
146 /* APIs called within Process_Line */
147 l_api_return_status VARCHAR2(1);
148 /* The return status of APIs called */
149 /* within the Process Line API. */
150 l_report_set_id NUMBER;
151 l_request_number VARCHAR2(80);
152 -- HW BUG#:2009229
153 l_call_mode VARCHAR2(1);
154
155 -- bug 3069040
156 IC$DEFAULT_LOCT VARCHAR2(255) := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
157
158 /* Cursors : */
159 CURSOR c_mold_crs (mo_source_line_id IN NUMBER)
160 IS
161 SELECT trans_id, whse_code
162 FROM ic_tran_pnd
163 WHERE line_id = mo_source_line_id
164 AND pick_slip_number IS NULL
165 AND (lot_id <> 0 or location <> ic$default_loct); -- added this condition for bug3069040
166
167
168 CURSOR c_doc_set (request_number IN NUMBER)
169 IS
170 SELECT document_set_id
171 FROM wsh_picking_batches
172 WHERE name = l_request_number;
173
174 CURSOR c_mo_header (mo_header_id IN NUMBER,
175 mo_organization_id IN NUMBER)
176 IS
177 SELECT request_number
178 FROM ic_txn_request_headers
179 WHERE header_id = mo_header_id
180 AND organization_id = mo_organization_id;
181
182
183 BEGIN
184 GMI_Reservation_Util.PrintLn('Entering Create_Pick_Slip_and_Print ');
185
186 SAVEPOINT Process_Pick_Slip_Number;
187
188 /* Obtain the pick slip number for each Move Order Line Detail created */
189 OPEN c_mold_crs (p_mo_line_rec.txn_source_line_id);
190 LOOP
191 /* Retrieve each Move Order Line Detail and get the pick slip number for each */
192 FETCH c_mold_crs INTO l_trans_id, l_sub_code;
193 EXIT WHEN c_mold_crs%NOTFOUND;
194
195 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print trans_id='||l_trans_id||', whse_code='||l_sub_code);
196
197 -- Bug 1717145 : Apr-2001 odaboval replaced package GMI by WSH
198 -- (Theorically, the GMI package GMI_Pr_Pick_Slip_Number becomes useless)
199 -- HW BUG#:2009229 added new parameter x_call_mode since shipping
200 -- changed the number of paramters passed to this routine
201 l_call_mode:=NULL;
202 gmi_reservation_util.println('Value of p_pick_slip_mode is '||p_pick_slip_mode);
203 gmi_reservation_util.println('Value of p_grouping_rule_id is '||p_grouping_rule_id);
204 gmi_reservation_util.println('Value of p_mo_line_rec.organization_id is '||p_mo_line_rec.organization_id);
205 gmi_reservation_util.println('Value of p_inv_delivery_details.oe_header_id is '||p_inv_delivery_details.oe_header_id);
206 gmi_reservation_util.println('Value of p_inv_delivery_details.customer_id is '||p_inv_delivery_details.customer_id);
207 gmi_reservation_util.println('Value of p_inv_delivery_details.freight_code is '||p_inv_delivery_details.freight_code);
208 gmi_reservation_util.println('Value of p_inv_delivery_details.ship_to_location is '||p_inv_delivery_details.ship_to_location);
209 gmi_reservation_util.println('Value of p_inv_delivery_details.shipment_priority_code is '||p_inv_delivery_details.shipment_priority_code);
210 gmi_reservation_util.println('Value of l_sub_code is '||l_sub_code);
211 gmi_reservation_util.println('Value of p_inv_delivery_details.trip_stop_id is '||p_inv_delivery_details.trip_stop_id);
212 gmi_reservation_util.println('Value of p_inv_delivery_details.shipping_delivery_id is '||p_inv_delivery_details.shipping_delivery_id);
213
214 -- HAW 2837671 Check if 110509 is installed and call the correct package
215 IF (wsh_code_control.get_code_release_level < '110509') THEN
216 WSH_Pr_Pick_Slip_Number.Get_Pick_Slip_Number
217 ( p_ps_mode => p_pick_slip_mode
218 , p_pick_grouping_rule_id => p_grouping_rule_id
219 , p_org_id => p_mo_line_rec.organization_id
220 , p_header_id => p_inv_delivery_details.oe_header_id
221 , p_customer_id => p_inv_delivery_details.customer_id
222 , p_ship_method_code => p_inv_delivery_details.freight_code
223 , p_ship_to_loc_id => p_inv_delivery_details.ship_to_location
224 , p_shipment_priority => p_inv_delivery_details.shipment_priority_code
225 , p_subinventory => l_sub_code
226 , p_trip_stop_id => p_inv_delivery_details.trip_stop_id
227 , p_delivery_id => p_inv_delivery_details.shipping_delivery_id
228 , x_pick_slip_number => l_pick_slip_number
229 , x_ready_to_print => l_ready_to_print
230 , x_api_status => l_api_return_status
231 , x_error_message => l_api_error_msg
232 , x_call_mode => l_call_mode);
233
234 ELSE -- Call the new INV package
235 INV_pr_Pick_Slip_number.Get_Pick_Slip_Number
236 ( p_ps_mode => p_pick_slip_mode
237 , p_pick_grouping_rule_id => p_grouping_rule_id
238 , p_org_id => p_mo_line_rec.organization_id
239 , p_header_id => p_inv_delivery_details.oe_header_id
240 , p_customer_id => p_inv_delivery_details.customer_id
241 , p_ship_method_code => p_inv_delivery_details.freight_code
242 , p_ship_to_loc_id => p_inv_delivery_details.ship_to_location
243 , p_shipment_priority => p_inv_delivery_details.shipment_priority_code
244 , p_subinventory => l_sub_code
245 , p_trip_stop_id => p_inv_delivery_details.trip_stop_id
246 , p_delivery_id => p_inv_delivery_details.shipping_delivery_id
247 , x_pick_slip_number => l_pick_slip_number
248 , x_ready_to_print => l_ready_to_print
249 , x_api_status => l_api_return_status
250 , x_error_message => l_api_error_msg
251 , x_call_mode => l_call_mode);
252
253 END IF; -- 2837671 End of checking release value
254
255 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print pick_slip_number='||l_pick_slip_number||', ret_stat='||l_api_return_status);
256 IF (l_api_return_status <> FND_API.G_RET_STS_SUCCESS
257 OR l_pick_slip_number = -1)
258 THEN
259 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print Error returned by Get_Pick_Slip_Number, status='||l_api_return_status);
260 ROLLBACK TO Process_Pick_Slip_Number;
261 FND_MESSAGE.SET_NAME('INV','INV_NO_PICK_SLIP_NUMBER');
262 FND_MSG_PUB.Add;
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266
267
268 /* Assign the pick slip number to the record in IC_TRAN_PND */
269 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print update ic_tran_pnd with pick_slip_number='||l_pick_slip_number);
270 UPDATE ic_tran_pnd
271 SET pick_slip_number = l_pick_slip_number
272 WHERE trans_id = l_trans_id;
273
274 /* If the pick slip is ready to be printed (and partial picking is allowed) commit */
275
276 /* and print at this point. */
277 -- HW BUG#:2009229 added check for l_call_mode
278 GMI_Reservation_Util.PrintLn('(opm_dbg) In Create_Pick_Slip_and_Print ready_to_prt='||l_ready_to_print||', allow_partial_pick='||p_allow_partial_pick);
279
280 -- HW BUG#:2296620 added a check for ship_set_id
281 GMI_Reservation_Util.PrintLn('(opm_dbg) In Create_Pick_Slip_and_Print ready_to_prt='||l_ready_to_print||', allow_partial_pick='||p_allow_partial_pick);
282 IF (l_ready_to_print = FND_API.G_TRUE
283 AND p_allow_partial_pick = FND_API.G_TRUE
284 AND p_mo_line_rec.ship_set_id IS NULL
285 AND l_call_mode is NULL )
286 THEN
287 /* do I have to commit ? */
288 -- COMMIT WORK;
289
290 /* ======================================================================== */
291 /* Get for more Info : */
292 /* ======================================================================== */
293 OPEN c_mo_header(p_mo_line_rec.header_id,
294 p_mo_line_rec.organization_id);
295 FETCH c_mo_header
296 INTO l_request_number;
297 IF ( c_mo_header%NOTFOUND )
298 THEN
299 GMI_Reservation_Util.PrintLn('(opm_dbg) In Create_Pick_Slip_and_Print Request_number not found for header_id='||p_mo_line_rec.header_id);
300 CLOSE c_mo_header;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303 CLOSE c_mo_header;
304
305 OPEN c_doc_set( l_request_number);
306 FETCH c_doc_set
307 INTO l_report_set_id;
308 IF (c_doc_set%NOTFOUND)
309 THEN
310 GMI_Reservation_Util.PrintLn('(opm_dbg) In Create_Pick_Slip_and_Print doc_set_id not found for request_number='||l_request_number);
311 CLOSE c_doc_set;
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314 CLOSE c_doc_set;
315
316 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print call Print_Pick_Slip report_set_id='||l_report_set_id);
317 -- Bug 1717145 : Apr-2001 odaboval replaced package GMI by WSH
318 -- (Theorically, the GMI package GMI_Pr_Pick_Slip_Number becomes useless)
319 WSH_Pr_Pick_Slip_Number.Print_Pick_Slip
320 ( p_pick_slip_number => l_pick_slip_number
321 , p_report_set_id => l_report_set_id
322 , p_organization_id => p_mo_line_rec.organization_id
323 , x_api_status => l_api_return_status
324 , x_error_message => l_api_error_msg);
325
326 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
327 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print Error returned by Print_Pick_Slip, status='||l_api_return_status);
328 ROLLBACK TO Process_Pick_Slip_Number;
329 FND_MESSAGE.SET_NAME('INV','INV_PRINT_PICK_SLIP_FAILED');
330 FND_MESSAGE.SET_TOKEN('PICK_SLIP_NUM', to_char(l_pick_slip_number));
331 FND_MSG_PUB.Add;
332 RAISE FND_API.G_EXC_ERROR;
333 END IF;
334 END IF;
335 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print before loop');
336 END LOOP;
337 CLOSE c_mold_crs;
338
339 GMI_Reservation_Util.PrintLn('In Create_Pick_Slip_and_Print NO Error');
340
341 EXCEPTION
342 WHEN FND_API.G_EXC_ERROR THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344
345 /* Get message count and data */
346 FND_MSG_PUB.count_and_get
347 ( p_count => x_msg_count
348 , p_data => x_msg_data
349 );
350
351
352 WHEN OTHERS THEN
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354
355 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
356 , l_api_name
357 );
358
359 /* Get message count and data */
360 FND_MSG_Pub.Count_and_Get
361 ( p_count => x_msg_count
362 , p_data => x_msg_data
363 );
364
365
366 END Create_Pick_Slip_and_Print;
367
368 /* The following procedure is created for enhancement 1928979 - lswamy*/
369 PROCEDURE Create_Manual_Alloc_Pickslip
370 ( p_organization_id IN NUMBER
371 , p_line_id IN NUMBER
372 , x_return_status OUT NOCOPY VARCHAR2
373 , x_msg_count OUT NOCOPY NUMBER
374 , x_msg_data OUT NOCOPY VARCHAR2
375 , x_pick_slip_number OUT NOCOPY NUMBER
376 ) IS
377
378 CURSOR pnd_txn_pickslip_CUR (mo_source_line_id IN NUMBER) IS
379 SELECT pick_slip_number
380 FROM ic_tran_pnd
381 WHERE line_id = mo_source_line_id
382 AND pick_slip_number is NOT NULL
383 AND (location <> GMI_Reservation_Util.G_DEFAULT_LOCT OR lot_id <> 0)
384 AND delete_mark = 0
385 AND completed_ind = 0
386 AND doc_type = 'OMSO';
387
388 --Bug3294071
389 CURSOR pnd_txn_pickslip_noctl_CUR (mo_source_line_id IN NUMBER) IS
390 SELECT pick_slip_number
391 FROM ic_tran_pnd
392 WHERE line_id = mo_source_line_id
393 AND pick_slip_number is NOT NULL
394 AND delete_mark = 0
395 AND completed_ind = 0
396 AND doc_type = 'OMSO';
397
398 CURSOR get_item_ctl (p_item_id IN NUMBER) IS
399 SELECT lot_ctl, loct_ctl, noninv_ind
400 FROM ic_item_mst
401 WHERE item_id = p_item_id;
402
403 CURSOR get_whse_ctl (p_whse_code IN VARCHAR2) IS
404 SELECT loct_ctl
405 FROM ic_whse_mst
406 WHERE whse_code=p_whse_code;
407 -- End Bug3294071
408
409
410 CURSOR pnd_txn_CUR (mo_source_line_id IN NUMBER) IS
411 SELECT whse_code,item_id
412 FROM ic_tran_pnd
413 WHERE line_id = mo_source_line_id;
414
415 CURSOR mo_header_CUR (p_header_id NUMBER) IS
416 SELECT grouping_rule_id
417 FROM ic_txn_request_headers
418 WHERE HEADER_ID = p_header_id;
419
420 l_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
421 l_demand_info WSH_INV_DELIVERY_DETAILS_V%ROWTYPE;
422 l_print_mode VARCHAR2(1);
423 l_grouping_rule_id NUMBER;
424 l_dummy VARCHAR2(30);
425 l_trans_id NUMBER;
426 l_sub_code VARCHAR2(10);
427 l_api_name CONSTANT VARCHAR2(30) := 'Create_Manual_Alloc_Pickslip ';
428
429 -- Bug3294071
430 l_item_id NUMBER;
431 l_lot_ctl NUMBER;
432 l_whse_loct_ctl NUMBER;
433 l_noninv_ind NUMBER;
434 l_item_loct_ctl NUMBER;
435 l_ready_to_print VARCHAR2(1);
436 l_call_mode VARCHAR2(1);
437 l_whse_code VARCHAR2(10);
438 BEGIN
439
440 GMI_Reservation_Util.PrintLn('IN create Manual alloc pickslip');
441 l_mo_line_rec := GMI_MOVE_ORDER_LINE_util.Query_Row( p_line_id );
442 -- Bug3294071
443 -- Modified and included the following code.
444 -- This ensures that if there is a pick slip number for
445 -- a noncontrol item - we return without any more processing.
446 -- This used to happen only for lot controlled item before.
447 SAVEPOINT Process_Pick_Slip_Number;
448
449 OPEN pnd_txn_CUR (l_mo_line_rec.txn_source_line_id);
450 FETCH pnd_txn_CUR INTO l_whse_code,l_item_id;
451 CLOSE pnd_txn_CUR;
452
453 OPEN get_item_ctl(l_item_id);
454 FETCH get_item_ctl INTO l_lot_ctl,l_item_loct_ctl, l_noninv_ind;
455 CLOSE get_item_ctl;
456
457 OPEN get_whse_ctl(l_whse_code);
458 FETCH get_whse_ctl INTO l_whse_loct_ctl;
459 CLOSE get_whse_ctl;
460
461 IF (l_noninv_ind = 0) THEN
462 IF (l_lot_ctl <> 0 OR (l_item_loct_ctl * l_whse_loct_ctl <> 0) ) THEN
463 OPEN pnd_txn_pickslip_CUR (l_mo_line_rec.txn_source_line_id);
464 FETCH pnd_txn_pickslip_CUR INTO x_pick_slip_number;
465 IF (pnd_txn_pickslip_CUR%FOUND) THEN
466 CLOSE pnd_txn_pickslip_CUR;
467 return;
468 END IF;
469 ELSE
470 OPEN pnd_txn_pickslip_noctl_CUR(l_mo_line_rec.txn_source_line_id);
471 FETCH pnd_txn_pickslip_noctl_CUR INTO x_pick_slip_number;
472 IF (pnd_txn_pickslip_noctl_CUR%FOUND) THEN
473 CLOSE pnd_txn_pickslip_noctl_CUR;
474 return;
475 END IF;
476 END IF;
477 END IF;
478 -- End bug3294071
479
480
481 GMI_Pick_Release_Util.Get_Delivery_Details(
482 p_mo_line_id => p_line_id
483 , x_inv_delivery_details => l_demand_info
484 , x_return_status => x_return_status
485 , x_msg_count => x_msg_count
486 , x_msg_data => x_msg_data);
487
488
489 BEGIN
490 SELECT NVL(print_pick_slip_mode, 'E')
491 INTO l_print_mode
492 FROM WSH_SHIPPING_PARAMETERS
493 WHERE organization_id = p_organization_id;
494 EXCEPTION
495 WHEN no_data_found THEN
496 GMI_Reservation_Util.PrintLn('WARNING: print_pick_slip_mode not defined for org_id='||p_organization_id);
497 l_print_mode := 'E';
498 END;
499
500 OPEN mo_header_CUR(l_mo_line_rec.header_id);
501 FETCH mo_header_CUR INTO l_grouping_rule_id;
502 CLOSE mo_header_CUR;
503
504 x_pick_slip_number := NULL;
505 -- Bug3294071(making call directly here to get the pick slip number )
506 /* GMI_Pick_Release_Util.Create_Pick_Slip_and_Print(
507 p_mo_line_rec => l_mo_line_rec
508 , p_inv_delivery_details => l_demand_info
509 , p_pick_slip_mode => l_print_mode
510 , p_grouping_rule_id => l_grouping_rule_id
511 , p_pick_slip_number => x_pick_slip_number
512 , p_sub_code => l_sub_code
513 , x_return_status => x_return_status
514 , x_msg_count => x_msg_count
515 , x_msg_data => x_msg_data);
516
517 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
518 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
519 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Pick_Release_Util.Create_Pick_Slip_and_Print');
520 FND_MESSAGE.Set_Token('WHERE', 'GMI_Pick_Release_PVT.Process_Line');
521 FND_MSG_PUB.Add;
522 RAISE FND_API.G_EXC_ERROR;
523 END IF; */
524
525 GMI_Reservation_Util.PrintLn(' Calling appropriate WSH or INV wrapper as to get the pick slip number ');
526 IF (wsh_code_control.get_code_release_level < '110509') THEN
527 WSH_Pr_Pick_Slip_Number.Get_Pick_Slip_Number
528 ( p_ps_mode => l_print_mode
529 , p_pick_grouping_rule_id => l_grouping_rule_id
530 , p_org_id => l_mo_line_rec.organization_id
531 , p_header_id => l_demand_info.oe_header_id
532 , p_customer_id => l_demand_info.customer_id
533 , p_ship_method_code => l_demand_info.freight_code
534 , p_ship_to_loc_id => l_demand_info.ship_to_location
535 , p_shipment_priority => l_demand_info.shipment_priority_code
536 , p_subinventory => l_whse_code
537 , p_trip_stop_id => l_demand_info.trip_stop_id
538 , p_delivery_id => l_demand_info.shipping_delivery_id
539 , x_pick_slip_number => x_pick_slip_number
540 , x_ready_to_print => l_ready_to_print
541 , x_api_status => x_return_status
542 , x_error_message => x_msg_data
543 , x_call_mode => l_call_mode);
544
545 ELSE -- Call the new INV package
546 INV_pr_Pick_Slip_number.Get_Pick_Slip_Number
547 ( p_ps_mode => l_print_mode
548 , p_pick_grouping_rule_id => l_grouping_rule_id
549 , p_org_id => l_mo_line_rec.organization_id
550 , p_header_id => l_demand_info.oe_header_id
551 , p_customer_id => l_demand_info.customer_id
552 , p_ship_method_code => l_demand_info.freight_code
553 , p_ship_to_loc_id => l_demand_info.ship_to_location
554 , p_shipment_priority => l_demand_info.shipment_priority_code
555 , p_subinventory => l_whse_code
556 , p_trip_stop_id => l_demand_info.trip_stop_id
557 , p_delivery_id => l_demand_info.shipping_delivery_id
558 , x_pick_slip_number => x_pick_slip_number
559 , x_ready_to_print => l_ready_to_print
560 , x_api_status => x_return_status
561 , x_error_message => x_msg_data
562 , x_call_mode => l_call_mode);
563
564 END IF; -- 2837671 End of checking release value
565
566 GMI_Reservation_Util.PrintLn('In Create_Manual_alloc_pickslip pick_slip_number='||x_pick_slip_number||', ret_stat='||x_return_status);
567 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS
568 OR x_pick_slip_number = -1)
569 THEN
570 GMI_Reservation_Util.PrintLn('In Create_Manual_alloc_pickslip Error returned by Get_Pick_Slip_Number, status='||x_return_status);
571 ROLLBACK TO Process_Pick_Slip_Number;
572 FND_MESSAGE.SET_NAME('INV','INV_NO_PICK_SLIP_NUMBER');
573 FND_MSG_PUB.Add;
574 RAISE FND_API.G_EXC_ERROR;
575 END IF;
576
577 GMI_Reservation_Util.PrintLn('In Create_Manual_alloc_pickslip NO Error');
578
579
580 EXCEPTION
581 WHEN FND_API.G_EXC_ERROR THEN
582 x_return_status := FND_API.G_RET_STS_ERROR;
583
584 /* Get message count and data */
585 FND_MSG_PUB.count_and_get
586 ( p_count => x_msg_count
587 , p_data => x_msg_data
588 );
589
590 WHEN OTHERS THEN
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
593 , l_api_name
594 );
595
596 /* Get message count and data */
597 FND_MSG_Pub.Count_and_Get
598 ( p_count => x_msg_count
599 , p_data => x_msg_data
600 );
601
602 END Create_Manual_Alloc_Pickslip;
603
604 /* Bug3294071 */
605 /* Created following procedure Lswamy*/
606
607 PROCEDURE UPDATE_TXN_WITH_PICK_SLIP
608 ( p_mo_line_rec IN GMI_Move_Order_Global.mo_line_rec
609 , p_pick_slip_number IN NUMBER
610 , x_return_status OUT NOCOPY VARCHAR2
611 , x_msg_count OUT NOCOPY NUMBER
612 , x_msg_data OUT NOCOPY VARCHAR2
613 ) IS
614
615
616 l_whse_code VARCHAR2(10);
617 l_item_id NUMBER;
618 l_lot_ctl NUMBER;
619 l_whse_loct_ctl NUMBER;
620 l_noninv_ind NUMBER;
621 l_item_loct_ctl NUMBER;
622 IC$DEFAULT_LOCT VARCHAR2(255) := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
623 l_trans_id NUMBER;
624 l_api_name CONSTANT VARCHAR2(30) := 'Update txn with Pick Slip ';
625
626 CURSOR allocations_trans_id IS
627 Select trans_id
628 From ic_tran_pnd
629 Where line_id = p_mo_line_rec.txn_source_line_id
630 and doc_type = 'OMSO'
631 and delete_mark = 0
632 and completed_ind = 0
633 and (lot_id <> 0 or location <> ic$default_loct);
634
635
636 CURSOR pnd_txn_CUR (mo_source_line_id IN NUMBER) IS
637 SELECT whse_code, item_id
638 FROM ic_tran_pnd
639 WHERE line_id = mo_source_line_id;
640
641
642 CURSOR get_item_ctl (p_item_id IN NUMBER) IS
643 SELECT lot_ctl, loct_ctl, noninv_ind
644 FROM ic_item_mst
645 WHERE item_id = p_item_id;
646
647 CURSOR get_whse_ctl (p_whse_code IN VARCHAR2) IS
648 SELECT loct_ctl
649 FROM ic_whse_mst
650 WHERE whse_code=p_whse_code;
651
652
653 BEGIN
654
655 OPEN pnd_txn_CUR (p_mo_line_rec.txn_source_line_id);
656 FETCH pnd_txn_CUR INTO l_whse_code,l_item_id;
657 CLOSE pnd_txn_CUR;
658
659 OPEN get_item_ctl(l_item_id);
660 FETCH get_item_ctl INTO l_lot_ctl,l_item_loct_ctl, l_noninv_ind;
661 CLOSE get_item_ctl;
662
663 OPEN get_whse_ctl(l_whse_code);
664 FETCH get_whse_ctl INTO l_whse_loct_ctl;
665 CLOSE get_whse_ctl;
666
667 IF (l_noninv_ind = 0) THEN
668 IF (l_lot_ctl <> 0 OR (l_item_loct_ctl * l_whse_loct_ctl <> 0) ) THEN
669 FOR transaction_ids IN allocations_trans_id LOOP
670 UPDATE ic_tran_pnd
671 SET pick_slip_number = p_pick_slip_number
672 WHERE trans_id = transaction_ids.trans_id;
673 END LOOP;
674 ELSE
675 SELECT trans_id
676 INTO l_trans_id
677 FROM ic_tran_pnd
678 WHERE line_id = p_mo_line_rec.txn_source_line_id
679 AND doc_type = 'OMSO'
680 AND delete_mark = 0
681 AND completed_ind = 0
682 AND staged_ind = 0
683 AND (lot_id = 0 and location = ic$default_loct);
684
685 UPDATE ic_tran_pnd
686 SET pick_slip_number = p_pick_slip_number
687 WHERE trans_id = l_trans_id;
688 END IF;
689 END IF;
690
691 GMI_Reservation_Util.PrintLn('In UPDATE_TXN_WITH_PICK_SLIP NO Error');
692
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 x_return_status := FND_API.G_RET_STS_ERROR;
696
697 /* Get message count and data */
698 FND_MSG_PUB.count_and_get
699 ( p_count => x_msg_count
700 , p_data => x_msg_data
701 );
702
703 WHEN OTHERS THEN
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
706 , l_api_name
707 );
708
709 /* Get message count and data */
710 FND_MSG_Pub.Count_and_Get
711 ( p_count => x_msg_count
712 , p_data => x_msg_data
713 );
714
715 END UPDATE_TXN_WITH_PICK_SLIP;
716
717 END GMI_Pick_Release_Util;