[Home] [Help]
PACKAGE BODY: APPS.GMI_PICK_CONFIRM_PUB
Source
1 PACKAGE BODY GMI_PICK_CONFIRM_PUB AS
2 /* $Header: GMIPCAPB.pls 115.6 2004/03/11 19:23:36 nchekuri noship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIPPWCB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains public procedures relating to GMI |
13 | Pick Confirmation. |
14 | |
15 | |
16 | HISTORY |
17 | 20-NOV-2002 nchekuri Created |
18 | |
19 | |
20 +=========================================================================+
21
22 API Name : GMI_PICK_CONFIRM_PUB
23 Type : Public Package Body
24 Function : This package contains Public Utilities used for
25 the Pick Confirm process.
26 Pre-reqs : N/A
27 Parameters: Per function
28
29 Current Vers : 1.0
30 */
31
32 /* Global variables */
33 G_PKG_NAME CONSTANT VARCHAR2(30) :='GMI_PICK_CONFIRM_PUB';
34 G_DEBUG_FILE_LOCATION VARCHAR2(255):= NULL;
35
36
37 /* ========================================================================*/
38 /***************************************************************************/
39 /*
40 | PARAMETERS:
41 | p_api_version Known api version
42 | p_init_msg_list FND_API.G_TRUE to reset list
43 | p_commit Commit flag. API commits if this is set.
44 | p_mo_line_id Move order line id to pick confirm
45 | p_delivery_detail_id Delivery detail id to pick confirm
46 | x_return_status Return status
47 | x_msg_count Number of messages in the list
48 | x_msg_data Text of messages
49 |
50 | VERSION : current version 1.0
51 | initial version 1.0
52 | COMMENT : Pick confirms a Move order line or a delivery detail line.
53
54 |
55 | Notes :
56 | -- If move order line is passed all the delivery details that belong
57 | to the move order are pick confirmed.
58 | -- If a delivery detail line is passed only that delivery detail is
59 | pick confirmed.
60 |
61 ****************************************************************************
62 | ======================================================================== */
63
64
65 PROCEDURE PICK_CONFIRM
66 (
67 p_api_version IN NUMBER
68 , p_init_msg_list IN VARCHAR2
69 , p_commit IN VARCHAR2
70 , p_mo_line_id IN NUMBER
71 , p_delivery_detail_id IN NUMBER
72 , p_bk_ordr_if_no_alloc IN VARCHAR2 -- Bug 3274586
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_count OUT NOCOPY NUMBER
75 , x_msg_data OUT NOCOPY VARCHAR2
76 )
77 IS
78
79
80 -- Standard constants to be used to check for call compatibility.
81 l_api_version CONSTANT NUMBER := 1.0;
82 l_api_name CONSTANT VARCHAR2(30):= 'PICK_CONFIRM';
83
84
85 -- Local Variables.
86
87 l_mo_line_row ic_txn_request_lines%ROWTYPE;
88 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
89 ll_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
90 l_mo_line_tbl GMI_MOVE_ORDER_GLOBAL.mo_line_tbl;
91 -- HW BUG#:3142323
92 ll_mo_line_tbl GMI_MOVE_ORDER_GLOBAL.mo_line_tbl;
93
94 l_count NUMBER := 0;
95
96 l_ship_from_org_id NUMBER;
97 l_inventory_item_id NUMBER;
98 l_default_lot VARCHAR2(32);
99 l_default_loct VARCHAR2(32);
100 l_ic_item_mst_rec ic_item_mst_b%ROWTYPE;
101 l_ic_whse_mst_rec ic_whse_mst%ROWTYPE;
102
103 l_loct_ctl NUMBER;
104
105 -- Exceptions
106 l_warning EXCEPTION;
107
108 CURSOR move_order_line_cur(p_mo_line_id NUMBER) IS
109 SELECT *
110 FROM ic_txn_request_lines
111 WHERE line_id = p_mo_line_id;
112
113 CURSOR move_order_line_cur2(p_delivery_detail_id NUMBER) IS
114 SELECT ic.*
115 FROM wsh_delivery_details wsh,
116 ic_txn_request_lines ic
117 WHERE ic.line_id = wsh.move_order_line_id
118 AND wsh.delivery_detail_id = p_delivery_detail_id;
119
120 -- Bug 3274586
121 CURSOR get_whse_code_dtl (p_organization_id IN NUMBER) IS
122 SELECT *
123 FROM ic_whse_mst
124 WHERE mtl_organization_id= p_organization_id;
125
126 CURSOR trans_with_no_default_cur(p_line_id NUMBER
127 ,p_mo_line_id NUMBER) IS
128 SELECT count(*)
129 FROM ic_tran_pnd
130 WHERE delete_mark = 0
131 AND completed_ind = 0
132 AND staged_ind = 0
133 AND (lot_id <> 0 OR location <> l_default_loct)
134 AND doc_type = 'OMSO'
135 AND line_id = p_line_id
136 AND line_detail_id in (
137 SELECT delivery_detail_id
138 FROM wsh_delivery_details
139 WHERE move_order_line_id = p_mo_line_id);
140
141 CURSOR trans_with_default_cur(p_line_id NUMBER)IS
142 SELECT count(*)
143 FROM ic_tran_pnd
144 WHERE delete_mark = 0
145 AND completed_ind = 0
146 AND staged_ind = 0
147 AND doc_type = 'OMSO'
148 AND line_id = p_line_id;
149
150 BEGIN
151 /*Init variables
152 =========================================*/
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154 l_default_lot := FND_PROFILE.VALUE('IC$DEFAULT_LOT');
155 l_default_loct := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
156
157
158 /* Standard begin of API savepoint
159 ===========================================*/
160 SAVEPOINT Pick_Confirm_SP;
161
162
163 /*Standard call to check for call compatibility.
164 ==============================================*/
165
166 IF NOT FND_API.compatible_api_call (
167 l_api_version,
168 p_api_version,
169 l_api_name,
170 G_PKG_NAME)
171 THEN
172 PrintMsg('FND_API.compatible_api_call failed');
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174 END IF;
175
176 /* Check p_init_msg_list
177 =========================================*/
178 IF FND_API.to_boolean(p_init_msg_list)
179 THEN
180 FND_MSG_PUB.initialize;
181 END IF;
182
183
184
185 /* Print the input parameters to the Debug File
186 ==============================================*/
187
188 PrintMsg('The Input Parameters are : ');
189 PrintMsg('========================= ');
190 PrintMsg(' p_api_version : '||p_api_version);
191 PrintMsg(' p_init_msg_list : '||p_init_msg_list);
192 PrintMsg(' p_commit : '||p_commit);
193 PrintMsg(' p_mo_line_id : '||p_mo_line_id);
194 PrintMsg(' p_delivery_detail_id : '||p_delivery_detail_id);
195 PrintMsg('========================= ');
196 PrintMsg(' ');
197
198 /*====================================
199 Validations
200 ======================================*/
201
202 /* Mo_line_id
203 ==============*/
204 /* If Move Order Line Id is passed then See if there's atleast one
205 delivery that has the status 'S'- 'Released to Warehouse' */
206
207 IF( NVL(p_mo_line_id,0) <> 0 )
208 THEN
209
210 OPEN move_order_line_cur(p_mo_line_id);
211 FETCH move_order_line_cur INTO l_mo_line_row;
212
213 IF(move_order_line_cur%NOTFOUND )
214 THEN
215 CLOSE move_order_line_cur;
216 PrintMsg('No move order line found for this mo_line_id : '||
217 p_mo_line_id);
218 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_MO_LINE_ID');
219 FND_MESSAGE.SET_TOKEN('MO_LINE_ID ',p_mo_line_id );
220 FND_MSG_PUB.Add;
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223
224 CLOSE move_order_line_cur;
225 /* Else see if delivery line is passed */
226 ELSIF( NVL(p_delivery_detail_id,0) <> 0)
227 THEN
228
229 OPEN move_order_line_cur2(p_delivery_detail_id);
230 FETCH move_order_line_cur2 INTO l_mo_line_row;
231
232 IF(move_order_line_cur2%NOTFOUND )
233 THEN
234 PrintMsg('ERROR: No move order line found for this delivery_detail_id : '||
235 p_delivery_detail_id);
236 CLOSE move_order_line_cur2;
237 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_DEL_DETAIL_ID');
238 FND_MESSAGE.SET_TOKEN('DEL_DETAIL_ID',p_delivery_detail_id );
239 FND_MSG_PUB.Add;
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242 CLOSE move_order_line_cur2;
243 /* Neither the move order nor the delivery detail is passed */
244 ELSE
245 PrintMsg('ERROR: No move order line or delivery delivery detail line is passed ');
246 FND_MESSAGE.SET_NAME('GMI','GMI_API_NO_INPUT_LINE_ID');
247 FND_MSG_PUB.Add;
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250
251
252 -- Get OPM Item Id from Inventory_item_id
253
254 GMI_OM_ALLOC_API_PUB.Get_Item_Details(
255 p_organization_id => l_mo_line_row.organization_id
256 , p_inventory_item_id => l_mo_line_row.inventory_item_id
257 , x_ic_item_mst_rec => l_ic_item_mst_rec
258 , x_return_status => x_return_status
259 , x_msg_count => x_msg_count
260 , x_msg_data => x_msg_data) ;
261
262
263 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
264 THEN
265 PrintMsg('ERROR - Get Item Details returned Error ');
266 FND_MESSAGE.Set_Name('GMI','GMI_API_ITEM_NOT_FOUND');
267 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', l_mo_line_row.organization_id);
268 FND_MESSAGE.Set_Token('ORGANIZATION_ID', l_mo_line_row.inventory_item_id);
269 FND_MESSAGE.Set_Token('LINE_ID', l_mo_line_row.line_id);
270 FND_MSG_PUB.Add;
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273
274 /* Print the Item Details */
275
276 PrintMsg('The Item Details : ' );
277 PrintMsg('========================= ');
278 PrintMsg(' item_no : '||l_ic_item_mst_rec.item_no);
279 PrintMsg(' item_id : '||l_ic_item_mst_rec.item_id);
280 PrintMsg(' lot_ctl : '||l_ic_item_mst_rec.lot_ctl);
281 PrintMsg(' loct_ctl : '||l_ic_item_mst_rec.loct_ctl);
282 PrintMsg('========================= ');
283 PrintMsg(' ');
284
285 -- Bug 3274586
286 OPEN get_whse_code_dtl (l_mo_line_row.organization_id);
287 FETCH get_whse_code_dtl into l_ic_whse_mst_rec;
288 IF( get_whse_code_dtl%NOTFOUND )
289 THEN
290 CLOSE get_whse_code_dtl;
291 PrintMsg('ERROR - Warehouse does not exist ');
292 RAISE FND_API.G_EXC_ERROR;
293 END IF;
294 CLOSE get_whse_code_dtl;
295
296 PrintMsg('The Warehouse Details : ' );
297 PrintMsg('========================= ');
298 PrintMsg(' whse_code : '||l_ic_whse_mst_rec.whse_code);
299 PrintMsg(' loct_ctl : '||l_ic_whse_mst_rec.loct_ctl);
300 PrintMsg('========================= ');
301 PrintMsg(' ');
302
303 l_loct_ctl := nvl(l_ic_item_mst_rec.loct_ctl,0) * nvl(l_ic_whse_mst_rec.loct_ctl,0);
304
305 PrintMsg('Lot ctl: '|| l_ic_item_mst_rec.lot_ctl ||' Effective location ctl: '||l_loct_ctl);
306
307 IF (l_ic_item_mst_rec.lot_ctl = 0 AND l_loct_ctl = 0)
308 THEN
309 PrintMsg('No control or non inventory item situation');
310 OPEN trans_with_default_cur(l_mo_line_row.txn_source_line_id);
311 FETCH trans_with_default_cur INTO l_count;
312
313 IF( trans_with_default_cur%NOTFOUND )
314 THEN
315 CLOSE trans_with_default_cur;
316 PrintMsg('ERROR - trans_with_default_cur failed ');
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319 CLOSE trans_with_default_cur;
320
321 ELSE
322 PrintMsg('Either lot control or location control situation');
323 /* Either lot or location controlled condition */
324 OPEN trans_with_no_default_cur(l_mo_line_row.txn_source_line_id,l_mo_line_row.line_id);
325 FETCH trans_with_no_default_cur INTO l_count;
326
327 IF( trans_with_no_default_cur%NOTFOUND )
328 THEN
329 CLOSE trans_with_no_default_cur;
330 PrintMsg('ERROR - trans_with_no_default_cur failed ');
331 RAISE FND_API.G_EXC_ERROR;
332 END IF;
333 CLOSE trans_with_no_default_cur;
334
335 END IF;
336
337 /* If there are no allocations, Nothing to pick confirm */
338
339 PrintMsg('Number of applicable allocations: '||l_count);
340
341 -- Bug 3274586 - Use the parameter value p_bk_ordr_if_no_alloc to bypass the allocations
342 -- exist check.
343 PrintMsg('p_bk_ordr_if_no_alloc: '||p_bk_ordr_if_no_alloc);
344
345 IF( l_count = 0) AND (UPPER(p_bk_ordr_if_no_alloc) <> 'Y')
346 THEN
347 PrintMsg('ERROR - No applicable allocations for mo_line_id : '||l_mo_line_row.line_id
348 ||' and delivery_detail_id : '||p_delivery_detail_id );
349 FND_MESSAGE.Set_Name('GMI','GMI_API_NO_ALLOCATIONS');
350 FND_MESSAGE.Set_Token('MO_LINE_ID', l_mo_line_row.line_id);
351 FND_MESSAGE.Set_Token('DELIVERY_DETAIL_ID', p_delivery_detail_id);
352 FND_MSG_PUB.Add;
353 RAISE FND_API.G_EXC_ERROR;
354 END IF;
355
356 /* Get The Move Order line (1 line) in to l_mo_line_tbl(1)
357 This has to be done 'coz the pvt expects mo_line_tbl */
358
359 PrintMsg('Before - Query the move order line');
360
361 l_mo_line_tbl(1) := GMI_MOVE_ORDER_LINE_UTIL.Query_Row( l_mo_line_row.line_id);
362
363 PrintMsg('Before lock move order row');
364
365 GMI_MOVE_ORDER_LINE_UTIL.Lock_Row(
366 p_mo_line_rec => l_mo_line_tbl(1)
367 , x_mo_line_rec => ll_mo_line_rec
368 , x_return_status => x_return_status);
369
370 PrintMsg('status from lock row'||x_return_status);
371
372 IF ( x_return_status = '54' )
373 THEN
374 PrintMsg('ERROR : Pick_Confirm : the MO is locked for line_id= '|| l_mo_line_row.line_id);
375 FND_MESSAGE.Set_Name('GMI','GMI_API_MO_LINE_LOCKED');
376 FND_MESSAGE.Set_Token('MO_LINE_ID', l_mo_line_row.line_id);
377 FND_MSG_PUB.Add;
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380
381
382 /* Check That we have at least one Move Order Line Rec */
383
384 IF ( l_mo_line_tbl.count = 0 )
385 THEN
386 PrintMsg('No rows to pick confirm');
387 FND_MESSAGE.SET_NAME('GMI', 'GMI_API_NO_LINES_TO_PICK_CONF');
388 FND_MESSAGE.Set_Token('MO_LINE_ID', l_mo_line_row.line_id);
389 FND_MESSAGE.Set_Token('DELIVERY_DETAIL_ID', p_delivery_detail_id);
390 FND_MSG_PUB.Add;
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 PrintMsg('Before calling GMI_PICK_WAVE_CONFIRM_PVT.PICK_CONFIRM');
395
396 -- HW BUG#:3142323 changed l_mo_line_tbl to ll_mo_line_tbl
397 -- NC Bug#3483078 send p_commit as FALSE and commit after the call depending on the return status
398 GMI_PICK_WAVE_CONFIRM_PVT.PICK_CONFIRM
399 (
400 p_api_version_number => p_api_version
401 , p_init_msg_lst => p_init_msg_list
402 , p_commit => FND_API.G_FALSE
403 , p_delivery_detail_id => p_delivery_detail_id
404 , p_mo_LINE_tbl => l_mo_line_tbl
405 , x_mo_LINE_tbl => ll_mo_line_tbl
406 , x_return_status => x_return_status
407 , x_msg_count => x_msg_count
408 , x_msg_data => x_msg_data
409 );
410
411 /* Commit if p_commit is set to true and return_status is not error */
412 IF(p_commit = FND_API.G_TRUE AND x_return_status <> FND_API.G_RET_STS_ERROR) THEN
413 COMMIT;
414 END IF;
415
416 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
417 THEN
418 PrintMsg('WARNING : Returning from Pick_Confirm_Pvt with Warning');
419 FND_MESSAGE.SET_NAME('GMI','GMI_API_PICK_CONFIRM_WARNING');
420 FND_MESSAGE.SET_TOKEN('MO_LINE_ID',l_mo_line_rec.line_id);
421 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',p_delivery_detail_id);
422 FND_MSG_PUB.Add;
423 RAISE L_WARNING;
424
425 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
426 THEN
427 PrintMsg('ERROR : Returning from Pick_Confirm_Pvt with error');
428 FND_MESSAGE.SET_NAME('GMI','GMI_API_PICK_CONFIRM_ERROR');
429 FND_MESSAGE.SET_TOKEN('MO_LINE_ID',l_mo_line_rec.line_id);
430 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',p_delivery_detail_id);
431 FND_MSG_PUB.Add;
432 RAISE FND_API.G_EXC_ERROR;
433 ELSE
434 PrintMsg('SUCCESS : Returning from Pick_Confirm_Pvt with success');
435 END IF;
436
437
438
439 /* EXCEPTION HANDLING
440 ====================*/
441 EXCEPTION
442 WHEN FND_API.G_EXC_ERROR THEN
443
444 --ROLLBACK TO SAVEPOINT Pick_Confirm_SP;
445
446
447 x_return_status := FND_API.G_RET_STS_ERROR;
448
449 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
450 , l_api_name
451 );
452 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
453 , p_count => x_msg_count
454 , p_data => x_msg_data
455 );
456
457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
458 --ROLLBACK TO SAVEPOINT Pick_Confirm_SP;
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
460
461 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
462 , l_api_name
463 );
464 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
465 , p_count => x_msg_count
466 , p_data => x_msg_data
467 );
468 WHEN L_WARNING THEN
469 --ROLLBACK TO SAVEPOINT Pick_Confirm_SP;
470 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
471
472 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
473 , l_api_name
474 );
475 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
476 , p_count => x_msg_count
477 , p_data => x_msg_data);
478
479 WHEN OTHERS THEN
480 --ROLLBACK TO SAVEPOINT Pick_Confirm_SP;
481 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
482 , l_api_name
483 );
484
485 /* Get message count and data */
486 FND_MSG_PUB.count_and_get
487 ( p_count => x_msg_count
488 , p_data => x_msg_data
489 );
490 END Pick_Confirm;
491
492 /*--+=========================================================================+
493 --| PROCEDURE NAME |
494 --| PrintMsg |
495 --| |
496 --| USAGE |
497 --| |
498 --| DESCRIPTION |
499 --| Used Print Debug messages in a log file |
500 --| |
501 --| PARAMETERS |
502 --| p_msg VARCHAR2 Message text |
503 --| p_file_name VARCHAR2 File Name |
504 --| |
505 --| HISTORY |
506 --| 29-SEP-2002 NC Created |
507 ============================================================================*/
508
509 PROCEDURE PrintMsg
510 ( p_msg IN VARCHAR2
511 , p_file_name IN VARCHAR2
512 ) IS
513
514 CURSOR get_log_file_location IS
515 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
516 FROM v$parameter
517 WHERE name = 'utl_file_dir';
518
519 l_log UTL_FILE.file_type;
520 l_file_name VARCHAR2(80);
521
522 BEGIN
523
524 IF (p_file_name = '0')
525 THEN
526 l_file_name := 'PickConfirm';
527 ELSE
528 l_file_name := p_file_name;
529 END IF;
530
531 l_file_name := l_file_name|| USERENV('SESSIONID');
532
533 IF (G_DEBUG_FILE_LOCATION IS NULL) THEN
534 OPEN get_log_file_location;
535 FETCH get_log_file_location into G_DEBUG_FILE_LOCATION;
536 CLOSE get_log_file_location;
537 END IF;
538
539 l_log := UTL_FILE.fopen(G_DEBUG_FILE_LOCATION, l_file_name, 'a');
540
541 IF UTL_FILE.IS_OPEN(l_log) THEN
542 UTL_FILE.put_line(l_log, p_msg);
543 UTL_FILE.fflush(l_log);
544 UTL_FILE.fclose(l_log);
545 END IF;
546
547
548 EXCEPTION
549
550 WHEN OTHERS THEN
551 NULL;
552
553 END PrintMsg;
554
555 END GMI_PICK_CONFIRM_PUB;