[Home] [Help]
PACKAGE BODY: APPS.GMI_OM_ALLOC_API_PUB
Source
1 PACKAGE BODY GMI_OM_ALLOC_API_PUB AS
2 /* $Header: GMIOMAPB.pls 120.0 2005/05/25 15:51:25 appldev noship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2002 Oracle Corporation |
5 | All righTs reserved |
6 +=========================================================================+
7 | FILENAME |
8 | GMIOMAPB.pls |
9 | |
10 | DESCRIPTION |
11 | This package contains PUBLIC utilities relating to OPM |
12 | reservation. |
13 | |
14 | -- Allocate_OPM_Orders |
15 | |
16 | HISTORY |
17 | 19-AUG-2002 nchekuri Created |
18 | |
19 +=========================================================================+
20 API Name : GMI_OM_ALLOC_API_PUB
21 Type : Public Package Body
22 Function : This package contains Public Utilities procedures used to
23 OPM reservation process.
24 Pre-reqs : N/A
25 Parameters: Per function
26
27 Current Vers : 1.0
28 */
29
30
31 /* Global variables */
32 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_OM_ALLOC_API_PUB';
33
34 /* ========================================================================*/
35 /***************************************************************************/
36 /*
37 | PARAMETERS:
38 | p_api_version Known api version
39 | p_init_msg_list FND_API.G_TRUE to reset list
40 | p_commit Commit flag. API commits if this is set.
41 | x_return_status Return status
42 | x_msg_count Number of messages in the list
43 | x_msg_data Text of messages
44 | p_tran_rec Input transaction record
45 |
46 | VERSION : current version 1.0
47 | initial version 1.0
48 | COMMENT : Creates,updates or deletes an opm reservation in ic_tran_pnd
49 | table table with information specified in p_tran_rec.
50 |
51 | Notes :
52 | -- The passed qties are positive
53 | -- if trans_id > 0, then the action code would be either update
54 | or delete.
55 |
56 | -- if trans_id = 0, then ureate a new transaction.
57 |
58 | -- line_id is mandatory in any case.
59 |
60 | -- Values for action_code are 'INSERT','UPDATE' and 'DELETE'
61 |
62 | -- Note that each UOM will be in passed in p_tran_rec as
63 | AppsUOM (3char). Need to be converted back to OPMUOM.
64 | -- Update is for quantities only.
65 |
66 ****************************************************************************
67 | ======================================================================== */
68
69 PROCEDURE Allocate_OPM_Orders(
70 p_api_version IN NUMBER
71 ,p_init_msg_list IN VARCHAR2
72 ,p_commit IN VARCHAR2
73 ,p_tran_rec IN IC_TRAN_REC_TYPE
74 ,x_return_status OUT NOCOPY VARCHAR2
75 ,x_msg_count OUT NOCOPY NUMBER
76 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
77
78
79 -- Standard constants to be used to check for call compatibility.
80 l_api_version CONSTANT NUMBER := 1.0;
81 l_api_name CONSTANT VARCHAR2(30):= 'allocate_opm_orders';
82
83 -- Local Variables.
84
85 l_tran_rec IC_TRAN_REC_TYPE;
86 l_pick_lots_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
87 l_line_status VARCHAR2(30);
88 l_count NUMBER;
89 l_line_detail_id NUMBER;
90 l_mo_line_id NUMBER DEFAULT NULL;
91 l_inventory_item_id NUMBER;
92 l_schedule_ship_date DATE;
93 l_ordered_quantity_uom VARCHAR2(3);
94 l_ordered_quantity_uom2 VARCHAR2(3);
95 l_ordered_quantity NUMBER;
96 l_ordered_quantity2 NUMBER;
97 l_requested_quantity NUMBER;
98 l_requested_quantity2 NUMBER;
99 l_line_id NUMBER;
100 l_default_lot VARCHAR2(32);
101 l_default_location VARCHAR2(32);
102 l_tmp_qty NUMBER;
103 l_tmp_qty2 NUMBER;
104 l_opm_um VARCHAR2(4);
105 --l_order_um VARCHAR2(3);
106 l_apps_um VARCHAR2(3);
107 l_order_um2 VARCHAR2(3);
108 l_loc_inactive NUMBER;
109 l_ship_from_org_id NUMBER;
110 l_ic_item_mst_rec ic_item_mst_b%ROWTYPE;
111 l_ic_whse_mst_rec ic_whse_mst%ROWTYPE;
112 l_ic_lots_mst_rec ic_lots_mst%ROWTYPE;
113 l_message VARCHAR2(1000);
114 i NUMBER;
115 l_epsilon NUMBER;
116 l_error_flag VARCHAR2(1) DEFAULT FND_API.G_FALSE;
117 l_return_val NUMBER:=0;
118 n NUMBER;
119
120
121 l_allow_negative_inv NUMBER;
122 l_overpick_enabled VARCHAR2(2);
123 l_error_code NUMBER;
124 l_delete_mark NUMBER;
125 l_completed_ind NUMBER;
126 l_staged_ind NUMBER;
127 l_reason_code VARCHAR2(4) := NULL; /* Bug 3700211 */
128
129
130 CURSOR oe_order_lines_cur(p_line_id IN NUMBER) IS
131 SELECT flow_status_code
132 ,inventory_item_id
133 ,ship_from_org_id
134 ,schedule_ship_date
135 ,order_quantity_uom
136 ,ordered_quantity_uom2
137 ,ordered_quantity
138 ,ordered_quantity2
139 FROM oe_order_lines_all
140 WHERE line_id = p_line_id;
141
142 CURSOR reas_code_cur(p_reas_code IN VARCHAR2) IS
143 SELECT reason_code
144 FROM sy_reas_cds
145 WHERE reason_code = p_reas_code
146 AND delete_mark <> 1;
147
148 CURSOR line_detail_id_Cur(p_line_id IN NUMBER) IS
149 SELECT delivery_detail_id,move_order_line_id
150 FROM wsh_delivery_details
151 WHERE source_line_id = p_line_id;
152
153 CURSOR source_line_id_Cur(p_line_detail_id IN NUMBER) IS
154 SELECT source_line_id
155 FROM wsh_delivery_details
156 WHERE delivery_detail_id = p_line_detail_id;
157
158 CURSOR ic_whse_mst_cur(p_organization_id IN NUMBER ) IS
159 SELECT *
160 FROM IC_WHSE_MST
161 WHERE mtl_organization_id = p_organization_id;
162
163 CURSOR get_loct_inv_dtls_cur(p_item_id IN NUMBER
164 ,p_lot_id IN NUMBER
165 ,p_whse_code IN VARCHAR2
166 ,p_location IN VARCHAR) IS
167 SELECT loct_onhand
168 ,loct_onhand2
169 ,lot_status
170 ,delete_mark
171 FROM ic_loct_inv
172 WHERE item_id = p_item_id
173 AND lot_id = NVL(p_lot_id ,0)
174 AND whse_code = p_whse_code
175 AND location = NVL(p_location,l_default_location);
176
177 CURSOR get_loct_inv_dtls_cur2(p_item_id IN NUMBER
178 ,p_lot_id IN NUMBER
179 ,p_whse_code IN VARCHAR2) IS
180 SELECT loct_onhand
181 ,loct_onhand2
182 ,lot_status
183 ,delete_mark
184 FROM ic_loct_inv
185 WHERE item_id = p_item_id
186 AND lot_id = NVL(p_lot_id ,0)
187 AND whse_code = p_whse_code;
188
189 CURSOR lot_status_cur(p_lot_status VARCHAR2) IS
190 SELECT nettable_ind
191 ,order_proc_ind
192 ,rejected_ind
193 FROM ic_lots_sts
194 WHERE lot_status = p_lot_status;
195
196 CURSOR ic_loct_mst_cur(p_location IN VARCHAR2
197 ,p_whse_code IN VARCHAR2) IS
198 SELECT delete_mark
199 FROM ic_loct_mst
200 WHERE location = p_location
201 AND whse_code = p_whse_code;
202
203 CURSOR get_commited_qty_cur IS
204 SELECT NVL(ABS(SUM(trans_qty)),0)
205 , NVL(ABS(SUM(trans_qty2)),0)
206 FROM IC_TRAN_PND a
207 WHERE a.lot_id = l_ic_lots_mst_rec.lot_id
208 AND a.item_id = l_ic_item_mst_rec.item_id
209 AND a.location = NVL(l_tran_rec.location ,l_default_location)
210 AND a.whse_code = l_ic_whse_mst_rec.whse_code
211 AND a.trans_id <> NVL(l_tran_rec.trans_id,0)
212 AND a.delete_mark = 0
213 AND a.completed_ind = 0
214 AND a.trans_qty < 0;
215
216 -- BEGIN - BUG 2789268 Pushkar Upakare - Added p_line_id to following cursor.
217 CURSOR get_alloc_qty_for_ddl_cur(p_line_id IN NUMBER, p_line_detail_id IN NUMBER) IS
218 SELECT NVL(ABS(SUM(trans_qty)),0)
219 ,NVL(ABS(SUM(trans_qty2)),0)
220 FROM IC_TRAN_PND
221 WHERE line_id = p_line_id
222 AND line_detail_id = p_line_detail_id
223 AND doc_type = 'OMSO'
224 AND delete_mark = 0;
225 -- END BUG 2789268
226
227 CURSOR get_alloc_qty_for_line_cur(p_line_id IN NUMBER) IS
228 SELECT NVL(ABS(SUM(trans_qty)),0)
229 ,NVL(ABS(SUM(trans_qty2)),0)
230 FROM IC_TRAN_PND
231 WHERE line_id = p_line_id
232 AND doc_type = 'OMSO'
233 AND delete_mark = 0;
234
235 CURSOR validate_trans_id_cur(p_trans_id IN NUMBER) IS
236 SELECT delete_mark,completed_ind,staged_ind
237 FROM ic_tran_pnd
238 WHERE trans_id = p_trans_id;
239
240 CURSOR requested_qty_cur(p_line_detail_id IN NUMBER) IS
241 SELECT requested_quantity,requested_quantity2
242 FROM wsh_delivery_details
243 WHERE delivery_detail_id = p_line_detail_id;
244
245 CURSOR mo_line_id_cur(p_line_detail_id IN NUMBER) IS
246 SELECT move_order_line_id
247 FROM wsh_delivery_details
248 WHERE delivery_detail_id = p_line_detail_id;
249
250 /* Some More Variables */
251
252 l_loct_inv_rec get_loct_inv_dtls_cur%ROWTYPE;
253 l_lot_status_rec lot_status_cur%ROWTYPE;
254 l_commit_qty NUMBER DEFAULT 0;
255 l_commit_qty2 NUMBER DEFAULT 0;
256 l_onhand_qty NUMBER DEFAULT 0;
257 l_onhand_qty2 NUMBER DEFAULT 0;
258 l_picked_qty NUMBER DEFAULT 0;
259 l_picked_qty2 NUMBER DEFAULT 0;
260 l_available_qty NUMBER DEFAULT 0;
261 l_available_qty2 NUMBER DEFAULT 0;
262
263 BEGIN
264
265 /*Init variables
266 =========================================*/
267 x_return_status := FND_API.G_RET_STS_SUCCESS;
268 l_default_lot := FND_PROFILE.VALUE('IC$DEFAULT_LOT');
269 l_default_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
270 l_allow_negative_inv := FND_PROFILE.VALUE('IC$ALLOWNEGINV');
271 l_overpick_enabled := FND_PROFILE.VALUE('WSH_OVERPICK_ENABLED');
272
273 --
274 -- BUG 3581429 Added the following anonymous block
275 --
276 BEGIN
277 l_epsilon := to_number(NVL(FND_PROFILE.VALUE('IC$EPSILON'),0)) ;
278 n := (-1) * round(log(10,l_epsilon));
279 EXCEPTION
280 WHEN OTHERS THEN
281 n := 9;
282 END;
283
284 /* Standard begin of API savepoint
285 ===========================================*/
286 SAVEPOINT Allocate_OPM_Orders_SP;
287
288 /*Standard call to check for call compatibility.
289 ==============================================*/
290
291 IF NOT FND_API.compatible_api_call (
292 l_api_version,
293 p_api_version,
294 l_api_name,
295 G_PKG_NAME)
296 THEN
297 PrintMsg('FND_API.compatible_api_call failed');
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301
302
303 /* Check p_init_msg_list
304 =========================================*/
305 IF FND_API.to_boolean(p_init_msg_list)
306 THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309
310
311 /*Move allocation record to local variable
312 ========================================*/
313 l_tran_rec := p_tran_rec;
314 l_tran_rec.location := UPPER(l_tran_rec.location);
315
316 /* Print the input parameters to the Debug File
317 ==============================================*/
318
319 PrintMsg('The Input Parameters are : ');
320 PrintMsg('========================= ');
321 PrintMsg(' p_api_version : '||p_api_version);
322 PrintMsg(' p_init_msg_list : '||p_init_msg_list);
323 PrintMsg(' p_commit : '||p_commit);
324 PrintMsg(' p_tran_rec.trans_id : '||p_tran_rec.trans_id);
325 PrintMsg(' p_tran_rec.line_id : '||p_tran_rec.line_id);
326 PrintMsg(' p_tran_rec.line_detail_id :'||p_tran_rec.line_detail_id);
327 PrintMsg(' p_tran_rec.lot_id : '||p_tran_rec.lot_id);
328 PrintMsg(' p_tran_rec.lot_no : '||p_tran_rec.lot_no);
329 PrintMsg(' p_tran_rec.sublot_no : '||p_tran_rec.sublot_no);
330 PrintMsg(' p_tran_rec.location : '||p_tran_rec.location);
331 PrintMsg(' p_tran_rec.trans_qty : '||p_tran_rec.trans_qty);
332 PrintMsg(' p_tran_rec.trans_qty2 : '||p_tran_rec.trans_qty2);
333 PrintMsg(' p_tran_rec.trans_um : '||p_tran_rec.trans_um);
334 PrintMsg(' p_tran_rec.trans_date : '||p_tran_rec.trans_date);
335 PrintMsg(' p_tran_rec.reason_code : '||p_tran_rec.reason_code);
336 PrintMsg(' p_tran_rec.action_code : '||p_tran_rec.action_code);
337 PrintMsg('========================= ');
338 PrintMsg(' ');
339 PrintMsg(' ');
340
341 PrintMsg('PROFILE VALUES :');
342 PrintMsg('========================== ');
343 PrintMsg('IC$ALLOWNEGINV : '||l_allow_negative_inv);
344 PrintMsg('WSH_OVERPICK_ENABLED : '||l_overpick_enabled);
345 PrintMsg('IC$DEFAULT_LOT : '||l_default_lot);
346 PrintMsg('IC$DEFAULT_LOCT : '||l_default_location);
347 PrintMsg('ROUNDING NUMBER : '||n);
348 PrintMsg(' ');
349
350
351 /* Check action_code
352 =======================================*/
353
354 IF( UPPER(NVL(l_tran_rec.action_code, 'N')) not in ('INSERT', 'UPDATE', 'DELETE'))
355 THEN
356 PrintMsg('ERROR - Validation failed on action_code Only ');
357 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_ACTION_CODE');
358 FND_MESSAGE.SET_TOKEN('ACTION_CODE ',l_tran_rec.action_code );
359 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
360 FND_MSG_PUB.Add;
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 /*====================================
365 Validations
366 ======================================*/
367
368
369 /* Trans_id
370 ==============*/
371 IF( UPPER(NVL(l_tran_rec.action_code,'N')) IN ('DELETE','UPDATE'))
372 THEN
373 IF( NVL(l_tran_rec.trans_id,0) = 0 )
374 THEN
375 PrintMsg('ERROR - Validation failed for trans_id');
376 l_error_flag := FND_API.G_TRUE;
377 FND_MESSAGE.SET_NAME('GML','GMI_API_TRANS_ID_REQUIRED');
378 FND_MESSAGE.SET_TOKEN('ACTION_CODE ',l_tran_rec.action_code );
379 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
380 FND_MSG_PUB.Add;
381 RAISE FND_API.G_EXC_ERROR;
382 END IF;
383 ELSIF( NVL(l_tran_rec.trans_id,0) <> 0)
384 THEN
385 PrintMsg('ERROR - Trans_id not required for INSERT');
386 l_error_flag := FND_API.G_TRUE;
387 FND_MESSAGE.SET_NAME('GMI','GMI_API_TRANS_ID_NOT_REQUIRED');
388 FND_MESSAGE.SET_TOKEN('ACTION_CODE ',l_tran_rec.action_code );
389 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
390 FND_MSG_PUB.Add;
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 IF( UPPER(NVL(l_tran_rec.action_code,'N')) IN ('DELETE','UPDATE') AND
395 NVL(l_tran_rec.trans_id,0) <> 0 )
396 THEN
397
398 OPEN validate_trans_id_cur(l_tran_rec.trans_id);
399 FETCH validate_trans_id_cur INTO l_delete_mark,l_completed_ind,l_staged_ind;
400
401 IF( validate_trans_id_cur%NOTFOUND)
402 THEN
403 CLOSE validate_trans_id_cur;
404 PrintMsg('ERROR - Invalid Trans_id for action code : '||l_tran_rec.action_code);
405 l_error_flag := FND_API.G_TRUE;
406 FND_MESSAGE.SET_NAME('GML','GMI_API_INVALID_TRANS_ID');
407 FND_MESSAGE.SET_TOKEN('TRANS_ID',l_tran_rec.trans_id );
408 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.LINE_ID );
412 THEN
409 FND_MSG_PUB.Add;
410 RAISE FND_API.G_EXC_ERROR;
411 ELSIF (l_delete_mark = 1)
413 CLOSE validate_trans_id_cur;
414 PrintMsg('ERROR - Invalid Trans_id Transaction is already deleted : '||l_tran_rec.trans_id);
415 l_error_flag := FND_API.G_TRUE;
416 FND_MESSAGE.SET_NAME('GML','GMI_API_TRANS_DELETED');
417 FND_MESSAGE.SET_TOKEN('TRANS_ID ',l_tran_rec.trans_id );
418 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
419 FND_MSG_PUB.Add;
420 RAISE FND_API.G_EXC_ERROR;
421 ELSIF (l_completed_ind = 1)
422 THEN
423 CLOSE validate_trans_id_cur;
424 PrintMsg('ERROR - Invalid Trans_id Transaction is already completed : '||l_tran_rec.trans_id);
425 l_error_flag := FND_API.G_TRUE;
426 FND_MESSAGE.SET_NAME('GML','GMI_API_TRANS_COMPLETED');
427 FND_MESSAGE.SET_TOKEN('TRANS_ID ',l_tran_rec.trans_id );
428 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
429 FND_MSG_PUB.Add;
430 RAISE FND_API.G_EXC_ERROR;
431 ELSIF (l_staged_ind = 1)
432 THEN
433 CLOSE validate_trans_id_cur;
434 PrintMsg('ERROR - Invalid Trans_id Transaction is already staged : '||l_tran_rec.trans_id);
435 l_error_flag := FND_API.G_TRUE;
436 FND_MESSAGE.SET_NAME('GML','GMI_API_TRANS_STAGED');
437 FND_MESSAGE.SET_TOKEN('TRANS_ID ',l_tran_rec.trans_id );
438 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id );
439 FND_MSG_PUB.Add;
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443
444 END IF; /* action code 'DELETE' OR 'UPDATE' and trans_id <>0) */
445
446 l_pick_lots_rec.trans_id := l_tran_rec.trans_id;
447
448 PrintMsg('l_pick_lots_rec.trans_id : '||l_pick_lots_rec.trans_id);
449
450 /* Line_id
451 ==============*/
452 IF (NVL(l_tran_rec.line_id,0) = 0 )
453 THEN
454 PrintMsg('ERROR - validation failed on line id');
455 l_error_flag := FND_API.G_TRUE;
456 FND_MESSAGE.SET_NAME('GMI','MISSING');
457 FND_MESSAGE.SET_TOKEN('MISSING','line_id');
458 FND_MSG_PUB.Add;
459 RAISE FND_API.G_EXC_ERROR;
460 ELSE
461
462 OPEN oe_order_lines_cur(l_tran_rec.line_id);
463 FETCH oe_order_lines_cur INTO l_line_status,l_inventory_item_id,
464 l_ship_from_org_id,l_schedule_ship_date,l_ordered_quantity_uom,
465 l_ordered_quantity_uom2,l_ordered_quantity,l_ordered_quantity2;
466
467 IF(oe_order_lines_cur%NOTFOUND)
468 THEN
469 PrintMsg('ERROR - in oe_order_lines_cur%NOTFOUND : Invalid line_id');
470 l_error_flag := FND_API.G_TRUE;
471 CLOSE oe_order_lines_cur;
472 FND_MESSAGE.SET_NAME('GML','GMI_API_INVALID_LINE_ID');
473 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id);
474 FND_MSG_PUB.Add;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477
478 PrintMsg('Quantity1 on the Sales Order Line is : '|| l_ordered_quantity);
479 PrintMsg('Quantity2 on the Sales Order Line is : '|| l_ordered_quantity2);
480 PrintMsg('Uom1 on the Sales Order Line is : '|| l_ordered_quantity_uom);
481 PrintMsg('Uom2 on the Sales Order Line is : '|| l_ordered_quantity_uom2);
482 PrintMsg('Sales Order Line Status is : '|| l_line_status);
483 PrintMsg('Sched Ship Date on the Sales Order Line is : '|| l_schedule_ship_date);
484
485
486 IF (oe_order_lines_cur%ISOPEN )
487 THEN
488 CLOSE oe_order_lines_cur;
489 END IF;
490
491 IF (l_line_status = 'CLOSED' OR l_line_status = 'CANCELLED' OR
492 l_line_status = 'FULFILLED' OR l_line_status = 'SHIPPED')
493 THEN
494
495 PrintMsg('ERROR - Invalid line_status : '||l_line_status);
496 l_error_flag := FND_API.G_TRUE;
497 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_LINE_STATUS');
498 FND_MESSAGE.SET_TOKEN('LINE_STATUS ',l_line_status);
499 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id);
500 FND_MSG_PUB.Add;
501 RAISE FND_API.G_EXC_ERROR;
502
503 END IF;
504
505 l_pick_lots_rec.line_id := l_tran_rec.line_id;
506 l_pick_lots_rec.trans_um2 := l_ordered_quantity_uom2;
507
508 PrintMsg('l_pick_lots_rec.line_id : '||l_pick_lots_rec.line_id);
509 PrintMsg('l_pick_lots_rec.trans_um2 : '||l_pick_lots_rec.trans_um2);
510
511
512 END IF; -- Line_id;
513
514 /* Line_detail_id
515 ====================*/
516
517 /* If the line_detail_id is passed, see if it belongs to the line */
518 IF ( NVL(l_tran_rec.line_detail_id,0) <> 0 )
519 THEN
520 OPEN source_line_id_cur(l_tran_rec.line_detail_id);
521 FETCH source_line_id_cur INTO l_line_id;
522
523 IF (source_line_id_cur%NOTFOUND)
524 THEN
525 PrintMsg('ERROR - source_line_id_cur%NOTFOUND, line_detail_id :'||
526 l_tran_rec.line_detail_id);
527 CLOSE source_line_id_cur;
528 END IF;
529
530 IF(source_line_id_cur%ISOPEN)
531 THEN
532 CLOSE source_line_id_cur;
533 END IF;
534
535
536 IF (l_line_id <> l_tran_rec.line_id)
537 THEN
541 FND_MESSAGE.SET_TOKEN('LINE_DETAIL_ID ',l_tran_rec.line_detail_id);
538 PrintMsg('ERROR - Invalid line_detail_id : '||l_line_detail_id);
539 l_error_flag := FND_API.G_TRUE;
540 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_LINE_DETAIL_ID');
542 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id);
543 FND_MSG_PUB.Add; /**** Define this message ****/
544 RAISE FND_API.G_EXC_ERROR;
545 END IF;
546
547 l_pick_lots_rec.line_detail_id := l_tran_rec.line_detail_id;
548
549
550 /* Fetch the mo_line_id if there's one */
551
552 OPEN mo_line_id_cur(l_tran_rec.line_detail_id);
553 FETCH mo_line_id_cur INTO l_mo_line_id;
554 IF (mo_line_id_cur%NOTFOUND)
555 THEN
556 l_mo_line_id := NULL;
557 PrintMsg('No move order line for this delivery line. line_detail_id :'||
558 l_tran_rec.line_detail_id);
559 CLOSE mo_line_id_cur;
560 END IF;
561
562 IF(mo_line_id_cur%ISOPEN)
563 THEN
564 CLOSE mo_line_id_cur;
565 END IF;
566
567 PrintMsg('l_mo_line_id : '||l_mo_line_id);
568
569 ELSE /*If line_detail_id is not passed fetch it from wsh_delivery_details*/
570
571 SELECT count(*) into l_count
572 FROM wsh_delivery_details
573 WHERE source_line_id = l_tran_rec.line_id;
574
575 /* if there there's more then one line_detail_id */
576 IF ( l_count > 1 )
577 THEN
578 PrintMsg(' There are more than one delivery lines for this line_id : '||
579 l_tran_rec.line_id || 'Please specify one');
580 l_error_flag := FND_API.G_TRUE;
581 FND_MESSAGE.SET_NAME('GMI','MISSING');
582 FND_MESSAGE.SET_TOKEN('MISSING','Line_detail_id');
583 FND_MSG_PUB.Add;
584 RAISE FND_API.G_EXC_ERROR;
585 ELSIF (l_count = 1)
586 THEN
587 OPEN line_detail_id_cur(l_tran_rec.line_id);
588 FETCH line_detail_id_cur INTO l_line_detail_id,l_mo_line_id;
589
590 IF(line_detail_id_cur%NOTFOUND)
591 THEN
592 PrintMsg('ERROR - line_detail_id_cur%NOTFOUND for line_id'||
593 l_tran_rec.line_id);
594 l_error_flag := FND_API.G_TRUE;
595 CLOSE line_detail_id_cur;
596 FND_MESSAGE.SET_NAME('GMI','MISSING');
597 FND_MESSAGE.SET_TOKEN('MISSING','Line_detail_id');
598 FND_MSG_PUB.Add;
599 RAISE FND_API.G_EXC_ERROR;
600
601 END IF;
602
603 IF(line_detail_id_cur%ISOPEN)
604 THEN
605 CLOSE line_detail_id_cur;
606 END IF;
607
608 l_tran_rec.line_detail_id := l_line_detail_id;
609 l_pick_lots_rec.line_detail_id := l_line_detail_id;
610
611
612 ELSE
613 l_tran_rec.line_detail_id := NULL;
614 l_pick_lots_rec.line_detail_id := NULL;
615 END IF;
616
617
618 END IF; /* If line_detail_id is not passed */
619
620 PrintMsg('l_pick_lots_rec.line_detail_id : '||l_pick_lots_rec.line_detail_id);
621
622 /* Reason Code
623 =============*/
624 IF (l_tran_rec.reason_code IS NOT NULL) /* Bug 3700211 - Changed the in condition */
625 THEN
626 OPEN reas_code_cur(l_tran_rec.reason_code);
627 FETCH reas_code_cur INTO l_reason_code;
628 IF (reas_code_cur%NOTFOUND)
629 THEN
630 PrintMsg('ERROR - Invalid reason_ code : '||l_tran_rec.reason_code);
631 CLOSE reas_code_cur;
632 l_error_flag := FND_API.G_TRUE;
633 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_REASON_CODE');
634 FND_MESSAGE.SET_TOKEN('REASON_CODE ',l_tran_rec.reason_code);
635 FND_MESSAGE.SET_TOKEN('LINE_ID ',l_tran_rec.line_id);
636 FND_MESSAGE.SET_TOKEN('TRANS_ID ',l_tran_rec.trans_id);
637 FND_MESSAGE.SET_TOKEN('LINE_DETAIL_ID ',l_tran_rec.line_detail_id);
638 FND_MSG_PUB.Add;
639 RAISE FND_API.G_EXC_ERROR;
640 END IF;
641
642 IF(reas_code_cur%ISOPEN)
643 THEN
644 CLOSE reas_code_cur;
645 END IF;
646
647 l_pick_lots_rec.reason_code := l_tran_rec.reason_code;
648 ELSE
649 l_pick_lots_rec.reason_code := NULL;
650 END IF;
651
652 PrintMsg('l_pick_lots_rec.reason_code : '||l_pick_lots_rec.reason_code);
653
654 /* Warehouse
655 =============*/
656
657 OPEN ic_whse_mst_cur(l_ship_from_org_id);
658 FETCH ic_whse_mst_cur INTO l_ic_whse_mst_rec;
659
660 IF(ic_whse_mst_cur%NOTFOUND)
661 THEN
662 PrintMsg('ic_whse_mst_cur%NOTFOUND for Organization '||l_ship_from_org_id);
663 CLOSE ic_whse_mst_cur;
664 l_error_flag := FND_API.G_TRUE;
665 FND_MESSAGE.SET_NAME('GMI','GMI_API_WHSE_NOT_FOUND');
666 FND_MESSAGE.SET_TOKEN('ORG', l_ship_from_org_id);
667 FND_MESSAGE.SET_TOKEN('LINE_ID', l_tran_rec.line_id);
668 FND_MSG_PUB.Add;
669 RAISE FND_API.G_EXC_ERROR;
670 ELSE
671 CLOSE ic_whse_mst_cur;
672 END IF;
673
674
675 l_pick_lots_rec.whse_code := l_ic_whse_mst_rec.whse_code;
676
677 PrintMsg('l_pick_lots_rec.whse_code : '||l_pick_lots_rec.whse_code);
681 ========== */
678 PrintMsg('l_whse_mst_rec.loct_ctl : '||l_ic_whse_mst_rec.loct_ctl);
679
680 /* Item
682
683 -- Get OPM Item Id from Inventory_item_id
684 Get_Item_Details(
685 p_organization_id => l_ship_from_org_id
686 , p_inventory_item_id => l_inventory_item_id
687 , x_ic_item_mst_rec => l_ic_item_mst_rec
688 , x_return_status => x_return_status
689 , x_msg_count => x_msg_count
690 , x_msg_data => x_msg_data) ;
691
692
693 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
694 THEN
695 PrintMsg('ERROR - Get Item Details returned Error ');
696 l_error_flag := FND_API.G_TRUE;
697 FND_MESSAGE.Set_Name('GMI','GMI_API_ITEM_NOT_FOUND');
698 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', l_inventory_item_id);
699 FND_MESSAGE.Set_Token('ORGANIZATION_ID', l_ship_from_org_id);
700 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
701 FND_MSG_PUB.Add;
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704
705 PrintMsg('l_ic_item_mst_rec.dualum_ind : '|| l_ic_item_mst_rec.dualum_ind);
706 PrintMsg('l_ic_item_mst_rec.loct_ctl : '|| l_ic_item_mst_rec.loct_ctl);
707 PrintMsg('l_ic_item_mst_rec.lot_ctl : '|| l_ic_item_mst_rec.lot_ctl);
708 PrintMsg('l_ic_item_mst_rec.sublot_ctl : '|| l_ic_item_mst_rec.sublot_ctl);
709 PrintMsg('l_ic_item_mst_rec.status_ctl : '|| l_ic_item_mst_rec.status_ctl);
710 PrintMsg('l_ic_item_mst_rec.grade_ctl : '|| l_ic_item_mst_rec.grade_ctl); /* BUG 2966077 */
711
712 /* Must not be a non inventory item */
713 IF (l_ic_item_mst_rec.noninv_ind = 1)
714 THEN
715 PrintMsg('ERROR - Item is a non inventory item :'|| l_ic_item_mst_rec.item_no);
716 l_error_flag := FND_API.G_TRUE;
717 FND_MESSAGE.SET_NAME('GMI','GMI_API_NONINV_ITEM');
718 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_ic_item_mst_rec.item_no);
719 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
720 FND_MSG_PUB.Add;
721 RAISE FND_API.G_EXC_ERROR;
722
723 /* Must be active */
724 ELSIF (l_ic_item_mst_rec.inactive_ind = 1)
725 THEN
726 PrintMsg('ERROR - Inactive item :'|| l_ic_item_mst_rec.item_no);
727 l_error_flag := FND_API.G_TRUE;
728 FND_MESSAGE.SET_NAME('GMI','GMI_API_INACTIVE_ITEM');
729 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_ic_item_mst_rec.item_no);
730 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
731 FND_MSG_PUB.Add;
732 RAISE FND_API.G_EXC_ERROR;
733
734 /* Item should not be a no control item */
735 ELSIF (l_ic_item_mst_rec.lot_ctl = 0 AND (l_ic_item_mst_rec.loct_ctl = 0) )
736 THEN
737 PrintMsg('ERROR - No Controls item :'|| l_ic_item_mst_rec.item_no);
738 l_error_flag := FND_API.G_TRUE;
739 FND_MESSAGE.SET_NAME('GMI','GMI_API_NO_CONTROL_ITEM');
740 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_ic_item_mst_rec.item_no);
741 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
742 FND_MSG_PUB.Add;
743 RAISE FND_API.G_EXC_ERROR;
744
745 END IF;
746
747 /* See if either item is or the warehouse is not location controlled */
748 IF( (l_ic_item_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl ) = 0 AND
749 NVL(l_tran_rec.location,' ') = ' ')
750 THEN
751 PrintMsg('Either the item or the warehouse is not location controlled,
752 default location will be used');
753 l_tran_rec.location := l_default_location;
754
755 END IF;
756
757
758 /* Verify that item is lot/sublot controlled if lot/sublot or
759 lot id are specified */
760
761 IF(( (NVL(p_tran_rec.lot_no,' ') <> ' ' AND p_tran_rec.lot_no <> l_default_lot)
762 OR NVL(p_tran_rec.lot_id,0) <> 0) AND
763 l_ic_item_mst_rec.lot_ctl <> 1 )
764 THEN
765 PrintMsg('ERROR - lot is specified but item is
766 not lot controlled'|| l_ic_item_mst_rec.item_no);
767 l_error_flag := FND_API.G_TRUE;
768 FND_MESSAGE.SET_NAME('GMI','GMI_API_NOT_LOT_CONTROL_ITEM');
769 FND_MESSAGE.SET_TOKEN('ITEM_NO_',l_ic_item_mst_rec.item_no);
770 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
771 FND_MSG_PUB.Add;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774
775 IF( NVL(p_tran_rec.sublot_no,' ') <> ' ' AND
776 l_ic_item_mst_rec.sublot_ctl <> 1)
777 THEN
778 PrintMsg('ERROR - sublot is specified but item is not sublot controlled , Item_no : '||
779 l_ic_item_mst_rec.item_no ||' Sublot_no : '|| p_tran_rec.sublot_no);
780 l_error_flag := FND_API.G_TRUE;
781 FND_MESSAGE.SET_NAME('GMI','GMI_API_SUBLOT_NOT_REQUIRED');
782 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_ic_item_mst_rec.item_no);
783 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
784 FND_MSG_PUB.Add;
785 RAISE FND_API.G_EXC_ERROR;
786 END IF;
787
788 l_pick_lots_rec.item_id := l_ic_item_mst_rec.item_id;
789 PrintMsg('l_pick_lots_rec.item_id : '|| l_pick_lots_rec.item_id);
790
791 /* Location
792 ============ */
793
794 /* If a location is specified verify that the item is location controlled
795 and the warehouse is location controlled */
796
797 IF ( NVL(l_tran_rec.location,' ') <> ' ' AND
798 l_tran_rec.location <> l_default_location AND
802 || l_ic_item_mst_rec.item_no);
799 (l_ic_item_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl) = 0)
800 THEN
801 PrintMsg('location is specified but either the item or the warehouse not location controlled :'
803 l_error_flag := FND_API.G_TRUE;
804 FND_MESSAGE.SET_NAME('GMI','GMI_API_NON_LOCT_CTL_ITEM_WHSE');
805 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_ic_item_mst_rec.item_no);
806 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_ic_whse_mst_rec.whse_code);
807 FND_MESSAGE.SET_TOKEN('LOCATION',l_tran_rec.location);
808 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
809 FND_MSG_PUB.Add;
810 RAISE FND_API.G_EXC_ERROR;
811 END IF;
812
813
814 /* If a location is not specified and the item is location controlled */
815 IF( NVL(l_tran_rec.location,' ') = ' ' AND
816 (l_ic_item_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl) <> 0 )
817 THEN
818 PrintMsg('location is NOT specified but item and warehouse are location controlled :'||
819 l_ic_item_mst_rec.item_no);
820 l_error_flag := FND_API.G_TRUE;
821 FND_MESSAGE.SET_NAME('GMI','GMI_API_LOCATION_REQUIRED');
822 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_ic_item_mst_rec.item_no);
823 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_ic_whse_mst_rec.whse_code);
824 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
825 FND_MSG_PUB.Add;
826 RAISE FND_API.G_EXC_ERROR;
827 END IF;
828
829
830 /* If whse is non validated location controlled, do not validate location but verify that
831 the length is <= 16 */
832
833 IF ( l_ic_whse_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl > 1)
834 THEN
835 IF( LENGTH(l_tran_rec.location) > 16 )
836 THEN
837 PrintMsg('Non validated location length can not be > 16 : '||
838 l_ic_item_mst_rec.item_no);
839 l_error_flag := FND_API.G_TRUE;
840 FND_MESSAGE.SET_NAME('GMI','GMI_API_LOCT_INVALID_LENGTH');
841 FND_MESSAGE.SET_TOKEN('LOCATION',l_tran_rec.location);
842 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
843 FND_MSG_PUB.Add;
844 RAISE FND_API.G_EXC_ERROR;
845 END IF;
846
847 /* This is not a non validated location */
848
849 ELSIF(l_ic_whse_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl = 1 AND NVL(l_tran_rec.location,' ') <> ' ')
850 THEN
851 /* In not non validated location */
852 /* Check that the location is active */
853
854 OPEN ic_loct_mst_cur( l_tran_rec.location,l_ic_whse_mst_rec.whse_code);
855 FETCH ic_loct_mst_cur INTO l_loc_inactive;
856
857 IF ic_loct_mst_cur%NOTFOUND
858 THEN
859 close ic_loct_mst_cur;
860 PrintMsg('ic_loct_mst_cur%NOTFOUND - Location is not found : '|| l_tran_rec.location);
861 l_error_flag := FND_API.G_TRUE;
862 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVALID_LOCATION');
863 FND_MESSAGE.SET_TOKEN('LOCATION',l_tran_rec.location);
864 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
865 FND_MSG_PUB.Add;
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868
869 IF (l_loc_inactive = 1)
870 THEN
871 PrintMsg('Location Specified is NOT Active : '|| l_tran_rec.location);
872 l_error_flag := FND_API.G_TRUE;
873 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_LOCATION');
874 FND_MESSAGE.SET_TOKEN('LOCATION',l_tran_rec.location);
875 FND_MESSAGE.SET_TOKEN('LINE_ID',l_tran_rec.line_id);
876 FND_MSG_PUB.Add;
877 RAISE FND_API.G_EXC_ERROR;
878 END IF;
879
880 END IF; /* non validated location */
881
882 l_pick_lots_rec.location := UPPER(l_tran_rec.location);
883
884 PrintMsg('l_pick_lots_rec.location : '|| l_pick_lots_rec.location);
885
886 /* Lot/Sublot - Lot_id
887 ======================== */
888
889 /* Get lot details from ic_lots_mst */
890
891 IF (l_ic_item_mst_rec.lot_ctl <> 0 )
892 THEN
893
894 Get_Lot_Details(
895 l_ic_item_mst_rec.item_id
896 ,l_tran_rec.lot_no
897 ,l_tran_rec.sublot_no
898 ,l_tran_rec.lot_id
899 ,l_ic_lots_mst_rec
900 ,x_return_status
901 ,x_msg_count
902 ,x_msg_data);
903
904 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
905 THEN
906 PrintMsg(' Get_Lot_Details failed, Lot_no :'||l_tran_rec.lot_no||
907 ' lot_id : '||l_tran_rec.lot_id);
908 l_error_flag := FND_API.G_TRUE;
909 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_NOT_FOUND');
910 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
911 FND_MESSAGE.Set_Token('SUBLOT_NO', l_tran_rec.sublot_no);
912 FND_MESSAGE.Set_Token('LOT_ID', l_tran_rec.lot_id);
913 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
914 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
915 FND_MSG_PUB.Add;
916 RAISE FND_API.G_EXC_ERROR;
917 END IF;
918
919 l_tran_rec.lot_no := l_ic_lots_mst_rec.lot_no;
920
921 /* Make sure the lot is active */
922 IF( l_ic_lots_mst_rec.inactive_ind = 1)
923 THEN
924 PrintMsg(' ERROR : Lot is not active , Lot_no : '||l_tran_rec.lot_no||
925 ' lot_id : '||l_tran_rec.lot_id);
926 l_error_flag := FND_API.G_TRUE;
930 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
927 FND_MESSAGE.Set_Name('GMI','GMI_API_INACTIVE_LOT');
928 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
929 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
931 FND_MSG_PUB.Add;
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 /* Is it expired? */
936 IF( l_ic_lots_mst_rec.expire_date < SYSDATE)
937 THEN
938 PrintMsg(' Lot is Expired , Lot_no '||l_tran_rec.lot_no||
939 ', lot_id : '||l_tran_rec.lot_id);
940 l_error_flag := FND_API.G_TRUE;
941 FND_MESSAGE.Set_Name('GMI','GMI_API_EXPIRED_LOT');
942 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
943 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
944 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
945 FND_MSG_PUB.Add;
946 RAISE FND_API.G_EXC_ERROR;
947 END IF;
948
949 /* Is it Deleted? */
950 IF( l_ic_lots_mst_rec.delete_mark = 1)
951 THEN
952 PrintMsg(' Lot is Deleted , Lot_no '||l_tran_rec.lot_no||
953 'lot_id : '||l_tran_rec.lot_id);
954 l_error_flag := FND_API.G_TRUE;
955 FND_MESSAGE.Set_Name('GMI','GMI_API_DELETED_LOT');
956 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
957 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
958 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
959 FND_MSG_PUB.Add;
960 RAISE FND_API.G_EXC_ERROR;
961 END IF;
962
963 -- BEGIN BUG 2966077
964 -- Is GRADE null for grade controlled item?
965 IF( NVL(l_ic_item_mst_rec.grade_ctl, 0) > 0 AND l_ic_lots_mst_rec.qc_grade IS NULL)
966 THEN
967 PrintMsg(' GRADE for LOT '||l_tran_rec.lot_no ||' IS NOT VALID');
968 l_error_flag := FND_API.G_TRUE;
969 FND_MESSAGE.Set_Name('GMI','IC_INVALID_QC_GRADE');
970 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
971 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
972 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
973 FND_MSG_PUB.Add;
974 RAISE FND_API.G_EXC_ERROR;
975 END IF;
976 --END BUG 2966077
977
978 ELSE /* item is not lot controlled */
979
980 l_ic_lots_mst_rec.lot_id := 0;
981
982 END IF; /* if item is lot_ctl */
983
984 /* Get lot_status from ic_loct_inv */
985
986 -- IF( l_ic_item_mst_rec.status_ctl > 0 ) /* check */
987 -- THEN
988
989 PrintMsg (' ** Item_id : '|| l_ic_item_mst_rec.item_id);
990 PrintMsg (' ** lot_id : '|| l_ic_lots_mst_rec.lot_id);
991 PrintMsg (' ** Whse_code : '|| l_ic_whse_mst_rec.whse_code);
992 PrintMsg (' ** location : '|| l_tran_rec.location);
993
994 OPEN get_loct_inv_dtls_cur(l_ic_item_mst_rec.item_id
995 ,l_ic_lots_mst_rec.lot_id
996 ,l_ic_whse_mst_rec.whse_code
997 ,l_tran_rec.location);
998
999 FETCH get_loct_inv_dtls_cur INTO l_loct_inv_rec;
1000
1001 IF (get_loct_inv_dtls_cur%NOTFOUND)
1002 THEN
1003
1004 /* What if the warehouse is non location controlled? */
1005
1006 IF( l_ic_whse_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl > 1)
1007 THEN
1008 OPEN get_loct_inv_dtls_cur2(l_ic_item_mst_rec.item_id
1009 ,l_ic_lots_mst_rec.lot_id
1010 ,l_ic_whse_mst_rec.whse_code);
1011
1012 FETCH get_loct_inv_dtls_cur2 INTO l_loct_inv_rec;
1013
1014 IF(get_loct_inv_dtls_cur2%NOTFOUND)
1015 THEN
1016 CLOSE get_loct_inv_dtls_cur2;
1017 PrintMsg('No inventory in ic_loct_inv for the item, whse,lot combination ');
1018 --l_error_flag := FND_API.G_TRUE;
1019 --FND_MESSAGE.Set_Name('GMI','GMI_API_NO_INVENTORY');
1020 --FND_MESSAGE.Set_Token('item_no', l_ic_item_mst_rec.item_no);
1021 --FND_MESSAGE.Set_Token('lot_no', l_tran_rec.lot_no);
1022 --FND_MSG_PUB.Add;
1023 --RAISE FND_API.G_EXC_ERROR;
1024 ELSE
1025 CLOSE get_loct_inv_dtls_cur2;
1026 END IF;
1027
1028 ELSE /* Else print that the row is not found in ic_loct_inv */
1029
1030 CLOSE get_loct_inv_dtls_cur;
1031 PrintMsg('No inventory in ic_loct_inv for the item, whse,lot,location combination ');
1032 --l_error_flag := FND_API.G_TRUE;
1033 --FND_MESSAGE.Set_Name('GMI','GMI_API_NO_INVENTORY');
1034 --FND_MESSAGE.Set_Token('item_no', l_ic_item_mst_rec.item_no);
1035 --FND_MESSAGE.Set_Token('lot_no', l_tran_rec.lot_no);
1036 --FND_MSG_PUB.Add;
1037 --RAISE FND_API.G_EXC_ERROR;
1038 END IF;
1039
1040 ELSE
1041 CLOSE get_loct_inv_dtls_cur;
1042 END IF;
1043
1044 l_onhand_qty := l_loct_inv_rec.loct_onhand;
1045 l_onhand_qty2 := l_loct_inv_rec.loct_onhand2;
1046
1047 PrintMsg('l_onhand_qty from ic_loct_inv : '|| l_onhand_qty);
1048 PrintMsg('l_onhand_qty2 from ic_loct_inv : '|| l_onhand_qty2);
1049
1050
1051 IF( l_ic_item_mst_rec.status_ctl > 0 )
1052 THEN
1053
1054 OPEN lot_status_cur(l_loct_inv_rec.lot_status);
1055 FETCH lot_status_cur INTO l_lot_status_rec;
1056
1057 IF (lot_status_cur%NOTFOUND)
1061 l_error_flag := FND_API.G_TRUE;
1058 THEN
1059 CLOSE lot_status_cur;
1060 PrintMsg('ERROR - No row in ic_lots_sts');
1062 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_STATUS_NOT_FOUND');
1063 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
1064 FND_MESSAGE.Set_Token('LOT_STATUS', l_loct_inv_rec.lot_status);
1065 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1066 FND_MSG_PUB.Add;
1067 RAISE FND_API.G_EXC_ERROR;
1068
1069 ELSE
1070 CLOSE lot_status_cur;
1071 END IF;
1072
1073 IF( l_lot_status_rec.rejected_ind = 1 )
1074 THEN
1075 PrintMsg('Rejected ind is 1 - unusable lot'|| l_ic_lots_mst_rec.lot_no);
1076 l_error_flag := FND_API.G_TRUE;
1077 FND_MESSAGE.SET_NAME('GMI','GMI_API_REJECTED_LOT');
1078 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
1079 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1080 FND_MSG_PUB.Add;
1081 RAISE FND_API.G_EXC_ERROR;
1082
1083 END IF;
1084
1085 IF(l_lot_status_rec.order_proc_ind = 0)
1086 THEN
1087 PrintMsg('Order_proc_ind is 0 - unusable lot'|| l_ic_lots_mst_rec.lot_no);
1088 l_error_flag := FND_API.G_TRUE;
1089 FND_MESSAGE.SET_NAME('GMI', 'GMI_API_LOT_NOT_ORDERABLE');
1090 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
1091 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1092 FND_MSG_PUB.Add;
1093 RAISE FND_API.G_EXC_ERROR;
1094 END IF;
1095
1096 IF(l_lot_status_rec.nettable_ind <> 1)
1097 THEN
1098 PrintMsg('Lot is not Nettable Lot_no :'||l_ic_lots_mst_rec.lot_no);
1099 l_error_flag := FND_API.G_TRUE;
1100 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_NOT_NETTABLE');
1101 FND_MESSAGE.Set_Token('LOT_NO', l_tran_rec.lot_no);
1102 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1103 FND_MSG_PUB.Add;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107 END IF; /* Status_ctl >0 */
1108
1109 l_pick_lots_rec.lot_id := l_ic_lots_mst_rec.lot_id;
1110 l_pick_lots_rec.lot_status := l_loct_inv_rec.lot_status;
1111
1112 PrintMsg('l_pick_lots_rec.lot_id : '||l_pick_lots_rec.lot_id);
1113
1114 -- BEGIN BUG 2966077
1115 IF ( NVL(l_ic_item_mst_rec.grade_ctl, 0) > 0) THEN
1116 l_pick_lots_rec.qc_grade := l_ic_lots_mst_rec.qc_grade;
1117 PrintMsg('l_pick_lots_rec.qc_grade : '||l_pick_lots_rec.qc_grade);
1118 END IF;
1119 --END BUG 2966077
1120
1121 /* Trans_uom
1122 ===============*/
1123
1124 /* The passed uom is always 3 char apps UOM.
1125 If passed check if it's different from item's primary,if so see if
1126 conversion exists */
1127
1128 IF ( NVL(l_tran_rec.trans_um,' ') = ' ')
1129 THEN
1130 PrintMsg('ERROR - Trans_um is not passed ');
1131 l_error_flag := FND_API.G_TRUE;
1132 FND_MESSAGE.Set_Name('GMI','GMI_API_UOM_REQUIRED');
1133 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1134 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1135 FND_MSG_PUB.Add;
1136 RAISE FND_API.G_EXC_ERROR;
1137
1138 ELSE /* l_tran_rec.trans_um is NOT NULL */
1139
1140 gmi_reservation_util.Get_AppsUOM_from_OpmUOM(
1141 p_OPM_UOM => l_ic_item_mst_rec.item_um
1142 , x_Apps_UOM => l_apps_um
1143 , x_return_status => x_return_status
1144 , x_msg_count => x_msg_count
1145 , x_msg_data => x_msg_data);
1146
1147 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1148 THEN
1149 PrintMsg('ERROR - uom conversion does not exist for: '||l_ic_item_mst_rec.item_um);
1150 l_error_flag := FND_API.G_TRUE;
1151 FND_MESSAGE.Set_Name('GMI','GMI_API_APPS_UOM_NOT_FOUND');
1152 FND_MESSAGE.Set_Token('OPM_UOM',l_ic_item_mst_rec.item_um);
1153 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1154 FND_MSG_PUB.Add;
1155 RAISE FND_API.G_EXC_ERROR;
1156 END IF;
1157
1158
1159 IF ( l_tran_rec.trans_um <> l_apps_um )
1160 THEN
1161
1162 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
1163 p_apps_uom =>l_tran_rec.trans_um,
1164 x_opm_uom =>l_opm_um,
1165 x_return_status =>x_return_status,
1166 x_msg_count =>x_msg_count,
1167 x_msg_data =>x_msg_data);
1168
1169 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1170 PrintMsg('ERROR: OPM UOM not found for apps uom : '|| l_tran_rec.trans_um);
1171 l_error_flag := FND_API.G_TRUE;
1172 FND_MESSAGE.Set_Name('GMI','GMI_API_OPM_UOM_NOT_FOUND');
1173 FND_MESSAGE.Set_Token('APPS_UOM', l_tran_rec.trans_um);
1174 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1175 FND_MSG_PUB.Add;
1176 RAISE FND_API.G_EXC_ERROR;
1177 END IF;
1178 PrintMsg('to uom (OPM) '||l_opm_um);
1179
1180 GMICUOM.icuomcv(
1181 pitem_id =>l_ic_item_mst_rec.item_id,
1182 plot_id =>l_ic_lots_mst_rec.lot_id,
1183 pcur_qty =>l_tran_rec.trans_qty,
1187
1184 pcur_uom =>l_opm_um,
1185 pnew_uom =>l_ic_item_mst_rec.item_um,
1186 onew_qty =>l_tmp_qty);
1188
1189 PrintMsg('converted_qty l_tmp_qty before rounding : '|| l_tmp_qty);
1190
1191 l_tmp_qty:=round(l_tmp_qty, n);
1192
1193 PrintMsg('converted_qty l_tmp_qty after rounding : '|| l_tmp_qty);
1194
1195
1196 IF( NVL(l_tmp_qty, -1) < 0 )
1197 --IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1198 THEN
1199 PrintMsg('ERROR - from GMICUOM.icuomcv, pcur_uom : ' || l_opm_um || 'pnew_uom : '||l_ic_item_mst_rec.item_um);
1200 l_error_flag := FND_API.G_TRUE;
1201 FND_MESSAGE.Set_Name('GMI','GMI_API_UOM_CONV_NOT_FOUND');
1202 FND_MESSAGE.Set_Token('FROM_UOM', l_opm_um);
1203 FND_MESSAGE.Set_Token('TO_UOM', l_ic_item_mst_rec.item_um);
1204 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1205 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1206 FND_MSG_PUB.Add;
1207 RAISE FND_API.G_EXC_ERROR;
1208
1209 ELSE
1210 l_pick_lots_rec.trans_qty := l_tmp_qty;
1211 -- l_pick_lots_rec.trans_um := l_ic_item_mst_rec.item_um;
1212 l_pick_lots_rec.trans_um := l_apps_um;
1213 END IF;
1214
1215 ELSE /* l_tran_rec.trans_um = l_apps_um */
1216
1217
1218 l_pick_lots_rec.trans_qty := l_tran_rec.trans_qty;
1219 -- l_pick_lots_rec.trans_um := l_ic_item_mst_rec.item_um;
1220 l_pick_lots_rec.trans_um := l_apps_um;
1221 l_tmp_qty := l_tran_rec.trans_qty;
1222 END IF;
1223
1224 PrintMsg('l_pick_lots_rec.trans_um : '||l_pick_lots_rec.trans_um);
1225 PrintMsg('l_pick_lots_rec.trans_qty : '||l_pick_lots_rec.trans_qty);
1226
1227 END IF; /* IF ( NVL(l_tran_rec.trans_um,' ') = ' ') */
1228
1229
1230 /*Trans_qty2
1231 ===============*/
1232
1233
1234 /* if the action_code is Insert or Update and Item is dual controlled then
1235 this should be > 0. If not supplied will be defaulted for dual1 and dual2. */
1236
1237 IF( UPPER(NVL(l_tran_rec.action_code, 'N')) in('INSERT', 'UPDATE')
1238 AND l_ic_item_mst_rec.dualum_ind > 0 )
1239 THEN
1240
1241 GMICUOM.icuomcv( pitem_id => l_ic_item_mst_rec.item_id,
1242 plot_id => l_ic_lots_mst_rec.lot_id,
1243 pcur_qty => l_tmp_qty,
1244 pcur_uom => l_ic_item_mst_rec.item_um,
1245 pnew_uom => l_ic_item_mst_rec.item_um2,
1246 onew_qty => l_tmp_qty2);
1247
1248 PrintMsg('converted_qty l_tmp_qty2 before rounding'|| l_tmp_qty2);
1249 l_tmp_qty2:=round(l_tmp_qty2, n);
1250 PrintMsg('converted_qty l_tmp_qty2 after rounding'|| l_tmp_qty2);
1251
1252
1253 IF( NVL(l_tran_rec.trans_qty2,0)= 0 )
1254 THEN
1255 IF ( l_ic_item_mst_rec.dualum_ind in (1,2))
1256 THEN
1257 l_pick_lots_rec.trans_qty2 := l_tmp_qty2;
1258
1259 ELSIF(l_ic_item_mst_rec.dualum_ind = 3)
1260 THEN
1261 PrintMsg('ERROR - Trans_qty2 for a dual3 item Can not be NULL. Please provide
1262 a valid trans_qty2 ');
1263 l_error_flag := FND_API.G_TRUE;
1264 FND_MESSAGE.Set_Name('GMI','GMI_API_QTY2_REQUIRED_DUAL3');
1265 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1266 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1267 FND_MSG_PUB.Add;
1268 RAISE FND_API.G_EXC_ERROR;
1269 END IF;
1270
1271 ELSE /* trans_qty2 is not 0,in this case check deviation */
1272
1273 IF (nvl(l_ic_item_mst_rec.deviation_hi,0) <> 0 AND
1274 l_tran_rec.trans_qty2 > l_tmp_qty2 *(1 + l_ic_item_mst_rec.deviation_hi)) THEN
1275 PrintMsg('ERROR - Trans_qty2 Deviation Hi error ');
1276 l_error_flag := FND_API.G_TRUE;
1277 FND_MESSAGE.SET_NAME('GMI','GMI_API_DEVIATION_ERR');
1278 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1279 FND_MESSAGE.Set_Token('QTY', l_pick_lots_rec.trans_qty);
1280 FND_MESSAGE.Set_Token('QTY2', l_tran_rec.trans_qty2);
1281 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1282 FND_MSG_PUB.Add;
1283 RAISE FND_API.G_EXC_ERROR;
1284
1285 ELSIF (nvl(l_ic_item_mst_rec.deviation_lo,0)<>0 AND
1286 l_tran_rec.trans_qty2 < l_tmp_qty2 * (1-l_ic_item_mst_rec.deviation_lo)) THEN
1287 PrintMsg('ERROR - Trans_qty2 Deviation Lo error ');
1288 l_error_flag := FND_API.G_TRUE;
1289 FND_MESSAGE.SET_NAME('GMI','GMI_API_DEVIATION_ERR');
1290 FND_MESSAGE.Set_Token('QTY', l_pick_lots_rec.trans_qty);
1291 FND_MESSAGE.Set_Token('QTY2', l_tran_rec.trans_qty2);
1292 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1293 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1294 FND_MSG_PUB.Add;
1295 RAISE FND_API.G_EXC_ERROR;
1296
1297 END IF;
1298
1299 l_pick_lots_rec.trans_qty2 := NVL(l_tran_rec.trans_qty2,0);
1300
1301
1302 END IF; /* If trans_qty2 = 0) */
1303
1304 PrintMsg('l_pick_lots_rec.trans_qty2 : '||l_pick_lots_rec.trans_qty2);
1305
1306 END IF; /* Action code */
1307
1308
1312 IF (UPPER(NVL(l_tran_rec.action_code, 'N'))in ('INSERT', 'UPDATE'))
1309 /*Trans_qty
1310 ===============*/
1311 /* For action code Insert or Update trans_qty must be > 0) */
1313 THEN
1314 IF( NVL(l_tran_rec.trans_qty,0) <= 0 )
1315 THEN
1316 PrintMsg('ERROR - trans_qty Has to be > 0 For actions INSERT and Update ');
1317 l_error_flag := FND_API.G_TRUE;
1318 FND_MESSAGE.Set_Name('GMI','GMI_API_QTY_REQ_FOR_ACT_CODE');
1319 FND_MESSAGE.Set_Token('ACTION_CODE', l_tran_rec.action_code);
1320 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1321 FND_MSG_PUB.Add;
1322 RAISE FND_API.G_EXC_ERROR;
1323 END IF;
1324
1325 OPEN get_commited_qty_cur;
1326 FETCH get_commited_qty_cur
1327 INTO l_commit_qty
1328 ,l_commit_qty2;
1329 IF(get_commited_qty_cur%NOTFOUND)
1330 THEN
1331 PrintMsg('ERROR - get_commited_qty_cur%NOTFOUND ');
1332 CLOSE get_commited_qty_cur;
1333 RAISE FND_API.G_EXC_ERROR;
1334 ELSE
1335 CLOSE get_commited_qty_cur;
1336 END IF;
1337
1338 PrintMsg('l_commit_qty : '|| l_commit_qty);
1339 PrintMsg('l_commit_qty2 : '|| l_commit_qty2);
1340
1341 l_commit_qty2 := round(l_commit_qty2, n);
1342 PrintMsg('l_commit_qty2 after rounding : '|| l_commit_qty2);
1343
1344
1345 l_available_qty := l_onhand_qty - l_commit_qty;
1346 l_available_qty2 := l_onhand_qty2 - l_commit_qty2;
1347
1348 PrintMsg('l_available_qty (onhand - commited) : '|| l_available_qty);
1349 PrintMsg('l_available_qty2 : '|| l_available_qty2);
1350 PrintMsg(' ');
1351
1352 /* Check if -ve inventory is allowed */
1353
1354 IF(l_allow_negative_inv <> 1) THEN
1355 IF ( (l_pick_lots_rec.trans_qty > l_available_qty) OR
1356 ( NVL(l_pick_lots_rec.trans_qty2,0) > NVL(l_available_qty2,0)) )
1357 THEN
1358 IF(l_allow_negative_inv = 0) THEN
1359 PrintMsg('ERROR - -ve inventory is not allowed. This allocation would drive the inventory -ve. ');
1360 l_error_flag := FND_API.G_TRUE;
1361 FND_MESSAGE.SET_NAME('GMI','GMI_API_INVQTYNEG');
1362 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1363 FND_MSG_PUB.Add;
1364 RAISE FND_API.G_EXC_ERROR;
1365
1366 ELSIF(l_allow_negative_inv = 2) THEN
1367 PrintMsg('Warning - This allocation would drive the inventory -ve. ');
1368 FND_MESSAGE.SET_NAME('GMI','GMI_API_WARNINVQTYNEG');
1369 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1370 FND_MSG_PUB.Add;
1371 END IF;
1372 END IF;
1373 END IF; /* Check -ve inventory */
1374
1375
1376 IF (NVL(l_tran_rec.line_detail_id,0) <> 0 )
1377 THEN
1378 -- BEGIN - BUG 2789268 Pushkar Upakare - Passed p_line_id to get_alloc_qty_for_ddl_cur cursor.
1379 OPEN get_alloc_qty_for_ddl_cur(l_tran_rec.line_id, l_tran_rec.line_detail_id);
1380 -- END - BUG 2789268
1381
1382 FETCH get_alloc_qty_for_ddl_cur INTO
1383 l_picked_qty,l_picked_qty2;
1384
1385 IF(get_alloc_qty_for_ddl_cur%NOTFOUND)
1386 THEN
1387 PrintMsg('ERROR - get_alloc_qty_for_ddl_cur%NOTFOUND');
1388 CLOSE get_alloc_qty_for_ddl_cur;
1389 END IF;
1390
1391 IF(get_alloc_qty_for_ddl_cur%ISOPEN )
1392 THEN
1393 CLOSE get_alloc_qty_for_ddl_cur;
1394 END IF;
1395
1396 PrintMsg('Picked quantities for Delivery Detail are : ');
1397 PrintMsg('---------------------------------------------');
1398 PrintMsg('l_picked_qty : '|| l_picked_qty);
1399 PrintMsg('l_picked_qty2 : '|| l_picked_qty2);
1400 PrintMsg('---------------------------------------------');
1401 PrintMsg(' ');
1402
1403 ELSE
1404
1405 OPEN get_alloc_qty_for_line_cur(l_tran_rec.line_id);
1406 FETCH get_alloc_qty_for_line_cur INTO
1407 l_picked_qty ,l_picked_qty2;
1408
1409 IF(get_alloc_qty_for_line_cur%NOTFOUND)
1410 THEN
1411 PrintMsg('ERROR - get_alloc_qty_for_line_cur%NOTFOUND');
1412 CLOSE get_alloc_qty_for_line_cur;
1413 END IF;
1414
1415 IF(get_alloc_qty_for_line_cur%ISOPEN )
1416 THEN
1417 CLOSE get_alloc_qty_for_line_cur;
1418 END IF;
1419
1420 PrintMsg('Picked quantities for the line are : ');
1421 PrintMsg('---------------------------------------------');
1422 PrintMsg('l_picked_qty : '|| l_picked_qty);
1423 PrintMsg('l_picked_qty2 : '|| l_picked_qty2);
1424 PrintMsg('---------------------------------------------');
1425 PrintMsg(' ');
1426
1427 END IF;
1428
1429 /* Check if lot is indivisible */
1430
1431 IF(l_ic_item_mst_rec.lot_indivisible = 1)
1432 THEN
1433
1434 IF ( (l_pick_lots_rec.trans_qty < l_available_qty) OR
1435 ( NVL(l_pick_lots_rec.trans_qty2,0) < NVL(l_available_qty2,0) )) /* check trans_qty2 */
1436 THEN
1437 IF ( NVL(l_overpick_enabled,'N') = 'Y')
1438 THEN
1439
1440 PrintMsg('**Lot is indivisible. trans_qty is less than available qty and
1444 l_pick_lots_rec.trans_qty2 := l_available_qty2;
1441 over picking is enabled. Hence set the trans_qty = available_qty');
1442
1443 l_pick_lots_rec.trans_qty := l_available_qty;
1445
1446 PrintMsg('l_pick_lots_rec.trans_qty : '|| l_pick_lots_rec.trans_qty);
1447 PrintMsg('l_pick_lots_rec.trans_qty2 : '|| l_pick_lots_rec.trans_qty2);
1448 PrintMsg(' ');
1449
1450 ELSE /* Over Picking is not enabled and the lot is indivisible. so error */
1451 PrintMsg('ERROR - lot is indivisible. trans_qty is less than
1452 available qty and over picking is not enabled');
1453 l_error_flag := FND_API.G_TRUE;
1454 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_INDIV_NO_OVEPIC');
1455 FND_MESSAGE.Set_Token('LOT_NO', l_ic_lots_mst_rec.lot_no);
1456 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1457 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1458 FND_MSG_PUB.Add;
1459 RAISE FND_API.G_EXC_ERROR;
1460
1461 END IF;
1462 ELSIF ( (l_pick_lots_rec.trans_qty > l_available_qty) OR
1463 ( NVL(l_pick_lots_rec.trans_qty2,0) > NVL(l_available_qty2,0) ) )
1464 THEN
1465
1466 PrintMsg('trans_qty is greater than available qty and Hence set the trans_qty = available_qty');
1467
1468 l_pick_lots_rec.trans_qty := l_available_qty;
1469 l_pick_lots_rec.trans_qty2 := l_available_qty2;
1470
1471 PrintMsg('l_pick_lots_rec.trans_qty : '|| l_pick_lots_rec.trans_qty);
1472 PrintMsg('l_pick_lots_rec.trans_qty2 : '|| l_pick_lots_rec.trans_qty2);
1473 PrintMsg(' ');
1474
1475 END IF;
1476
1477 END IF; /* Lot indivisible */
1478
1479
1480 /* Also, if the over pick is not enabled then, make sure that by allocating
1481 the trans_qty we are not over-picking */
1482
1483 IF ( NVL(l_overpick_enabled,'N') = 'N')
1484 THEN
1485 IF (NVL(l_tran_rec.line_detail_id,0) <> 0 )
1486 THEN
1487
1488 /* Get the requested_quantities */
1489
1490 OPEN requested_qty_cur(l_tran_rec.line_detail_id);
1491 FETCH requested_qty_cur INTO
1492 l_requested_quantity,l_requested_quantity2;
1493
1494 IF(requested_qty_cur%NOTFOUND)
1495 THEN
1496 PrintMsg('ERROR - requested_qty_cur%NOTFOUND');
1497 CLOSE requested_qty_cur;
1498 END IF;
1499
1500 IF(requested_qty_cur%ISOPEN )
1501 THEN
1502 CLOSE requested_qty_cur;
1503 END IF;
1504
1505 PrintMsg('Requested quantities for Delivery Detail are : ');
1506 PrintMsg('---------------------------------------------');
1507 PrintMsg('l_requested_quantity : '|| l_requested_quantity);
1508 PrintMsg('l_requested_quantity2 : '|| l_requested_quantity2);
1509 PrintMsg('---------------------------------------------');
1510 PrintMsg(' ');
1511
1512 ELSE /* The ordered quantity would be the requested quantity */
1513
1514 /* Convert the Ordered_quantity to item's Primary If the ordered_quantity is not
1515 In Item's Primary Uom */
1516 IF (l_ordered_quantity_uom <> l_apps_um)
1517 THEN
1518 l_requested_quantity := 0;
1519 PrintMsg('Ordered Quantity is not in Items primary uom. So convert');
1520
1521 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
1522 p_apps_uom =>l_ordered_quantity_uom,
1523 x_opm_uom =>l_opm_um,
1524 x_return_status =>x_return_status,
1525 x_msg_count =>x_msg_count,
1526 x_msg_data =>x_msg_data);
1527
1528 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1529 PrintMsg('ERROR: OPM UOM not found for apps uom : '|| l_ordered_quantity_uom);
1530 l_error_flag := FND_API.G_TRUE;
1531 FND_MESSAGE.Set_Name('GMI','GMI_API_OPM_UOM_NOT_FOUND');
1532 FND_MESSAGE.Set_Token('APPS_UOM', l_ordered_quantity_uom);
1533 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1534 FND_MSG_PUB.Add;
1535 RAISE FND_API.G_EXC_ERROR;
1536 END IF;
1537
1538 GMICUOM.icuomcv(
1539 pitem_id =>l_ic_item_mst_rec.item_id,
1540 plot_id =>l_ic_lots_mst_rec.lot_id,
1541 pcur_qty =>l_ordered_quantity,
1542 pcur_uom =>l_opm_um,
1543 pnew_uom =>l_ic_item_mst_rec.item_um,
1544 onew_qty =>l_requested_quantity);
1545
1546 PrintMsg('converted_qty l_requested_quantity before rounding'|| l_requested_quantity);
1547
1548 l_requested_quantity:=round(l_requested_quantity, n);
1549
1550 PrintMsg('converted_qty l_requested_quantity after rounding'|| l_requested_quantity);
1551
1552
1553 ELSE /* l_ordered_quantity_uom = l_apps_um */
1554
1555 l_requested_quantity := l_ordered_quantity;
1559
1556 PrintMsg('Converted quantity( Items uom) for Sales Order line is : '
1557 || l_requested_quantity);
1558 PrintMsg(' ');
1560 END IF;
1561
1562 l_requested_quantity2 := l_ordered_quantity2;
1563
1564 PrintMsg('---------------------------------------------');
1565 PrintMsg('l_quantity : '|| l_requested_quantity);
1566 PrintMsg('l_quantity2 : '|| l_requested_quantity2);
1567 PrintMsg('---------------------------------------------');
1568 PrintMsg(' ');
1569
1570 END IF;
1571
1572 /* Check whether we would be Over Picking by allocating the Specified Quantity */
1573
1574 IF( (l_pick_lots_rec.trans_qty > (l_requested_quantity - l_picked_qty)))
1575 /* Not checking for qty2 */
1576 -- OR (NVL(l_pick_lots_rec.trans_qty2,0) >
1577 -- ( NVL(l_requested_quantity2,0) - NVL(l_picked_qty2,0)))
1578 THEN
1579
1580 PrintMsg('trans_qty : '|| l_pick_lots_rec.trans_qty);
1581 PrintMsg('requested_quantity : '|| l_requested_quantity);
1582 PrintMsg('picked_qty : '|| l_picked_qty);
1583 PrintMsg(' ');
1584 PrintMsg('trans_qty2 : '|| l_pick_lots_rec.trans_qty2);
1585 PrintMsg('requested_quantity2 : '|| l_requested_quantity2);
1586 PrintMsg('picked_qty2 : '|| l_picked_qty2);
1587 PrintMsg(' ');
1588 PrintMsg('ERROR - Over picking is not enabled');
1589 l_error_flag := FND_API.G_TRUE;
1590 FND_MESSAGE.Set_Name('GMI','GMI_API_OVERPICK_NOT_ALLOWED');
1591 FND_MESSAGE.Set_Token('ITEM_NO', l_ic_item_mst_rec.item_no);
1592 FND_MESSAGE.Set_Token('LINE_ID', l_tran_rec.line_id);
1593 FND_MSG_PUB.Add;
1594 RAISE FND_API.G_EXC_ERROR;
1595 END IF;
1596
1597 END IF; /* Over pick not enabled */
1598
1599 END IF; /* Action code */
1600
1601 IF (l_tran_rec.action_code = 'DELETE')
1602 THEN
1603 l_pick_lots_rec.trans_qty := 0;
1604 l_pick_lots_rec.trans_qty := 0;
1605
1606 PrintMsg('l_pick_lots_rec.trans_qty : '||l_pick_lots_rec.trans_qty);
1607 PrintMsg('l_pick_lots_rec.trans_qty2 : '||l_pick_lots_rec.trans_qty2);
1608
1609 END IF;
1610
1611 /*Trans_date
1612 ==============*/
1613 /* If trans date is not passed, assign schedule_ship_date */
1614
1615 IF ( l_tran_rec.trans_date IS NULL)
1616 THEN
1617 l_tran_rec.trans_date := l_schedule_ship_date;
1618 END IF;
1619
1620 /* Validate the transaction date */
1621
1622 l_return_val := GMICCAL.trans_date_validate
1623 (l_tran_rec.trans_date,
1624 l_ic_whse_mst_rec.orgn_code,
1625 l_ic_whse_mst_rec.whse_code
1626 );
1627
1628 IF l_return_val <> 0 THEN
1629
1630 IF l_return_val = GMICCAL.INVCAL_FISCALYR_ERR THEN
1631 FND_MESSAGE.SET_NAME('GMI','IC_CAL_FISCALYR_ERR');
1632 FND_MSG_PUB.Add;
1633 ELSIF l_return_val = GMICCAL.INVCAL_PERIOD_ERR THEN
1634 FND_MESSAGE.SET_NAME('GMI','IC_CAL_CLOSED_IND_ERR');
1635 FND_MSG_PUB.Add;
1636 ELSIF l_return_val = GMICCAL.INVCAL_ORGN_PARM_ERR THEN
1637 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_ORGN_ERR');
1638 FND_MSG_PUB.Add;
1639 ELSIF l_return_val = GMICCAL.INVCAL_CO_ERR THEN
1640 FND_MESSAGE.SET_NAME('GMI','IC_COCODEERR');
1641 FND_MSG_PUB.Add;
1642 ELSIF l_return_val = GMICCAL.INVCAL_WHSE_PARM_ERR THEN
1643 FND_MESSAGE.SET_NAME('GMI','IC_BLANKWHSE');
1644 FND_MSG_PUB.Add;
1645 ELSIF l_return_val = GMICCAL.INVCAL_WHSE_ERR THEN
1646 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_WHSE_CODE');
1647 FND_MESSAGE.SET_TOKEN('WHSE_CODE',l_ic_whse_mst_rec.whse_code);
1648 FND_MSG_PUB.Add;
1649 ELSIF l_return_val IN (GMICCAL.INVCAL_PERIOD_CLOSED,
1650 GMICCAL.INVCAL_WHSE_CLOSED) THEN
1651
1652 FND_MESSAGE.SET_NAME('GMI','IC_API_TXN_POST_CLOSED');
1653 FND_MESSAGE.SET_TOKEN('DATE',l_tran_rec.trans_date);
1654 FND_MESSAGE.SET_TOKEN('WAREH',l_ic_whse_mst_rec.whse_code);
1655 FND_MSG_PUB.Add;
1656 ELSE
1657 FND_MESSAGE.SET_NAME('GMI','ICCAL_GENL_ERR');
1658 FND_MSG_PUB.Add;
1659 END IF;
1660 l_error_flag := FND_API.G_TRUE;
1661 RAISE FND_API.G_EXC_ERROR;
1662 END IF;
1663
1664 l_pick_lots_rec.trans_date := l_tran_rec.trans_date;
1665
1666 IF(l_error_flag = FND_API.G_TRUE)
1667 THEN
1668 x_return_status := FND_API.G_RET_STS_ERROR;
1669 --RAISE FND_API.G_EXC_ERROR;
1670 RETURN;
1671 END IF;
1672
1673 /* Build the record for Set_Pick_Lots Private API
1674 ==================================================*/
1675 GMI_RESERVATION_UTIL.Set_Pick_Lots (
1676 p_ic_tran_rec => l_pick_lots_rec
1677 , p_mo_line_id => l_mo_line_id
1678 , p_commit => p_commit
1679 , x_return_status => x_return_status
1680 , x_msg_count => x_msg_count
1681 , x_msg_data => x_msg_data );
1682
1683
1684 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1685 THEN
1686 PrintMsg('in allocate_opm_orders.call to
1687 set_pick_lots returns error');
1691 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', l_inventory_item_id);
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 FND_MESSAGE.Set_Name('GMI','GMI_SET_PIC_LOTS_ERROR');
1690 FND_MESSAGE.Set_Token('ORGANIZATION_ID', l_ship_from_org_id);
1692 FND_MSG_PUB.Add;
1693 RAISE FND_API.G_EXC_ERROR;
1694 RETURN;
1695 ELSE
1696 PrintMsg('Successfully returning from Allocate_OPM_Orders');
1697 END IF;
1698
1699
1700 /* EXCEPTION HANDLING
1701 ====================*/
1702 EXCEPTION
1703 WHEN FND_API.G_EXC_ERROR THEN
1704 ROLLBACK TO SAVEPOINT Allocate_Opm_Orders_SP;
1705
1706
1707 x_return_status := FND_API.G_RET_STS_ERROR;
1708
1709 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1710 , l_api_name
1711 );
1712 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1713 , p_count => x_msg_count
1714 , p_data => x_msg_data
1715 );
1716
1717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1718 ROLLBACK TO SAVEPOINT Allocate_Opm_Orders_SP;
1719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1720
1721 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1722 , l_api_name
1723 );
1724 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1725 , p_count => x_msg_count
1726 , p_data => x_msg_data
1727 );
1728 WHEN OTHERS THEN
1729 ROLLBACK TO SAVEPOINT Allocate_Opm_Orders_SP;
1730 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1731 , l_api_name
1732 );
1733
1734 /* Get message count and data */
1735 FND_MSG_PUB.count_and_get
1736 ( p_count => x_msg_count
1737 , p_data => x_msg_data
1738 );
1739
1740 END Allocate_OPM_Orders;
1741
1742 /*--+=========================================================================+
1743 --| PROCEDURE NAME |
1744 --| Get_Lot_Details |
1745 --| |
1746 --| USAGE |
1747 --| Used to retrieve lot master details |
1748 --| |
1749 --| DESCRIPTION |
1750 --| This procedure is used to retrieve all details from ic_lots_mst |
1751 --| |
1752 --| PARAMETERS |
1753 --| p_item_id IN NUMBER - Item ID of lot to be retrieved |
1754 --| p_lot_no IN VARCHAR2(32) - Lot number of lot to be retrieved |
1755 --| p_sublot_no IN VARCHAR2(32) - Sublot number to be retrieved |
1756 --| p_lot_id IN NUMBER - lot id to be retrieved |
1757 --| x_ic_lots_mst OUT RECORD - Record containing ic_lots_mst |
1758 --| x_return_status OUT VARCHAR2 |
1759 --| x_msg_count OUT NUMBER |
1760 --| x_msg_data OUT VARCHAR2 |
1761 --| |
1762 --| HISTORY |
1763 --| 16-SEP-2002 NC Created |
1764 --+=========================================================================+*/
1765 PROCEDURE Get_Lot_Details
1766 ( p_item_id IN ic_lots_mst.item_id%TYPE
1767 , p_lot_no IN ic_lots_mst.lot_no%TYPE
1768 , p_sublot_no IN ic_lots_mst.sublot_no%TYPE
1769 , p_lot_id IN ic_lots_mst.lot_id%TYPE
1770 , x_ic_lots_mst OUT NOCOPY ic_lots_mst%ROWTYPE
1771 , x_return_status OUT NOCOPY VARCHAR2
1772 , x_msg_count OUT NOCOPY NUMBER
1773 , x_msg_data OUT NOCOPY VARCHAR2
1774 )
1775 IS
1776
1777 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Lot_Details';
1778
1779 CURSOR ic_lots_mst_c1 IS
1780 SELECT *
1781 FROM ic_lots_mst
1782 WHERE
1783 lot_no = p_lot_no
1784 AND ( sublot_no = p_sublot_no OR
1785 sublot_no is NULL)
1786 AND item_id = p_item_id;
1787
1788 CURSOR ic_lots_mst_c2 IS
1789 SELECT *
1790 FROM ic_lots_mst
1791 WHERE
1792 lot_id = p_lot_id
1793 AND item_id = p_item_id;
1794
1795 BEGIN
1796
1797 /* Init variables */
1798 x_return_status := FND_API.G_RET_STS_SUCCESS;
1799
1800
1801 IF ( NVL(p_lot_id,-1) <> -1) THEN
1802
1803 OPEN ic_lots_mst_c2;
1804 FETCH ic_lots_mst_c2 INTO x_ic_lots_mst;
1805
1806 IF (ic_lots_mst_c2%NOTFOUND) THEN
1807 CLOSE ic_lots_mst_c2;
1808 PrintMsg('Error : In Get_Lot_Details .Invalid Lot_id = '||
1809 p_lot_id||', item_id = '||p_item_id);
1810
1811 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_NOT_FOUND');
1812 FND_MESSAGE.Set_Token('LOT_NO', p_lot_no);
1813 FND_MESSAGE.Set_Token('SUBLOT_NO', p_sublot_no);
1817 RAISE FND_API.G_EXC_ERROR;
1814 FND_MESSAGE.Set_Token('LOT_ID', p_lot_id);
1815 FND_MSG_PUB.Add;
1816
1818
1819 END IF;
1820
1821 IF (ic_lots_mst_c2%ISOPEN)
1822 THEN
1823 CLOSE ic_lots_mst_c2;
1824 END IF;
1825
1826 ELSE
1827 OPEN ic_lots_mst_c1;
1828 FETCH ic_lots_mst_c1 INTO x_ic_lots_mst;
1829
1830 IF (ic_lots_mst_c1%NOTFOUND) THEN
1831 CLOSE ic_lots_mst_c1;
1832 PrintMsg('Error :in Get_Lot_Details .Invalid Lot_No/Sublot_no = '
1833 ||p_lot_no|| ', Sublot_no = '||p_sublot_no);
1834
1835 FND_MESSAGE.Set_Name('GMI','GMI_API_LOT_NOT_FOUND');
1836 FND_MESSAGE.Set_Token('LOT_NO', p_lot_no);
1837 FND_MESSAGE.Set_Token('SUBLOT_NO', p_sublot_no);
1838 FND_MESSAGE.Set_Token('LOT_ID', p_lot_id);
1839 FND_MSG_PUB.Add;
1840
1841 RAISE FND_API.G_EXC_ERROR;
1842
1843 END IF;
1844
1845 IF(ic_lots_mst_c1%ISOPEN)
1846 THEN
1847 CLOSE ic_lots_mst_c1;
1848 END IF;
1849 END IF;
1850
1851 EXCEPTION
1852 WHEN FND_API.G_EXC_ERROR THEN
1853 x_return_status := FND_API.G_RET_STS_ERROR;
1854 /* Get message count and data */
1855 FND_MSG_PUB.count_and_get
1856 ( p_count => x_msg_count
1857 , p_data => x_msg_data
1858 );
1859
1860 WHEN OTHERS THEN
1861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1862
1863 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1864 , l_api_name
1865 );
1866
1867 /* Get message count and data */
1868 FND_MSG_PUB.count_and_get
1869 ( p_count => x_msg_count
1870 , p_data => x_msg_data
1871 );
1872
1873 END Get_Lot_Details;
1874 /*--+=========================================================================+
1875 --| PROCEDURE NAME |
1876 --| Get_Item_Details |
1877 --| |
1878 --| USAGE |
1879 --| Used to retrieve item master details |
1880 --| |
1881 --| DESCRIPTION |
1882 --| This procedure is used to retrieve all details from ic_lots_mst |
1883 --| |
1884 --| PARAMETERS |
1885 --| p_organization_id IN NUMBER - Inventory Organization Id |
1886 --| p_inventory_item_id IN VARCHAR2(32) - Inventory Item Id |
1887 --| x_ic_item_mst_rec OUT IC_ITEM_MST%ROWTYPE Record containing |
1888 --| ic_item_mst |
1889 --| x_return_status OUT VARCHAR2 - Return Status |
1890 --| x_msg_count OUT NUMBER
1891 --| x_msg_data OUT VARCHAR2
1892 --| |
1893 --| HISTORY |
1894 --| 16-SEP-2002 NC Created |
1895 --+=========================================================================+*/
1896
1897 PROCEDURE Get_Item_Details
1898 ( p_organization_id IN NUMBER
1899 , p_inventory_item_id IN NUMBER
1900 , x_ic_item_mst_rec OUT NOCOPY IC_ITEM_MST_B%ROWTYPE
1901 , x_return_status OUT NOCOPY VARCHAR2
1902 , x_msg_count OUT NOCOPY NUMBER
1903 , x_msg_data OUT NOCOPY VARCHAR2
1904 ) IS
1905
1906 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Item_Details';
1907
1908 CURSOR ic_item_mst_cur ( discrete_org_id IN NUMBER
1909 , discrete_item_id IN NUMBER) IS
1910 SELECT ic.*
1911 FROM ic_item_mst_b ic
1912 , mtl_system_items mtl
1913 WHERE delete_mark = 0
1914 AND ic.item_no = mtl.segment1
1915 AND mtl.organization_id = discrete_org_id
1916 AND mtl.inventory_item_id = discrete_item_id;
1917
1918 BEGIN
1919
1920 /* Init variables */
1921 x_return_status := FND_API.G_RET_STS_SUCCESS;
1922
1923
1924 OPEN ic_item_mst_cur( p_organization_id , p_inventory_item_id);
1925 FETCH ic_item_mst_cur INTO x_ic_item_mst_rec;
1926
1927 IF ic_item_mst_cur%NOTFOUND THEN
1928 CLOSE ic_item_mst_cur;
1929 PrintMsg('Error ic_item_mst_cur%NOTFOUND inv_item_ id='
1930 ||p_inventory_item_id||', org_id='||p_organization_id);
1931 FND_MESSAGE.Set_Name('GMI','GMI_API_ITEM_NOT_FOUND');
1932 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
1933 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
1934
1935 FND_MSG_PUB.Add;
1936 RAISE FND_API.G_EXC_ERROR;
1937 ELSE
1938 CLOSE ic_item_mst_cur;
1939 PrintMsg(' Item_no = '|| x_ic_item_mst_rec.item_no);
1940 END IF;
1941
1942 EXCEPTION
1943 WHEN FND_API.G_EXC_ERROR THEN
1944 x_return_status := FND_API.G_RET_STS_ERROR;
1945 /* Get message count and data */
1946 FND_MSG_PUB.count_and_get
1947 ( p_count => x_msg_count
1948 , p_data => x_msg_data
1949 );
1950
1951 WHEN OTHERS THEN
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1953 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1954 , l_api_name
1955 );
1956
1957 /* Get message count and data */
1958 FND_MSG_PUB.count_and_get
1959 ( p_count => x_msg_count
1960 , p_data => x_msg_data
1961 );
1962
1963 END Get_Item_Details;
1964
1965 /*--+=========================================================================+
1966 --| PROCEDURE NAME |
1967 --| PrintMsg |
1968 --| |
1969 --| USAGE |
1970 --| |
1971 --| DESCRIPTION |
1972 --| Used Print Debug messages in a log file |
1973 --| |
1974 --| PARAMETERS |
1975 --| p_msg VARCHAR2 Message text |
1976 --| p_file_name VARCHAR2 File Name |
1977 --| |
1978 --| HISTORY |
1979 --| 29-SEP-2002 NC Created |
1980 ============================================================================*/
1981
1982 PROCEDURE PrintMsg
1983 ( p_msg IN VARCHAR2
1984 , p_file_name IN VARCHAR2
1985 ) IS
1986
1987 CURSOR get_log_file_location IS
1988 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value) location, USERENV('SESSIONID') sessionid
1989 FROM v$parameter
1990 WHERE name = 'utl_file_dir';
1991
1992 l_location VARCHAR2(255);
1993 l_log UTL_FILE.file_type;
1994 l_time VARCHAR2(10);
1995 l_file_name VARCHAR2(80);
1996 l_sessionid NUMBER := 0;
1997 l_debug_level VARCHAR2(240) := TO_NUMBER(NVL(fnd_profile.value ('ONT_DEBUG_LEVEL'),0));
1998
1999 BEGIN
2000
2001 IF (l_debug_level >= 5)
2002 THEN
2003
2004 IF (p_file_name = '0')
2005 THEN
2006 l_file_name := 'ALOPM';
2007 ELSE
2008 l_file_name := p_file_name;
2009 END IF;
2010
2011 OPEN get_log_file_location;
2012 FETCH get_log_file_location into l_location, l_sessionid;
2013 CLOSE get_log_file_location;
2014
2015 l_file_name := l_file_name || l_sessionid;
2016
2017 l_log := UTL_FILE.fopen(l_location, l_file_name, 'a');
2018
2019 IF UTL_FILE.IS_OPEN(l_log) THEN
2020 UTL_FILE.put_line(l_log, p_msg);
2021 UTL_FILE.fflush(l_log);
2022 UTL_FILE.fclose(l_log);
2023 END IF;
2024
2025 END IF;
2026
2027 EXCEPTION
2028
2029 WHEN OTHERS THEN
2030 NULL;
2031
2032 END PrintMsg;
2033
2034
2035
2036 END GMI_OM_ALLOC_API_PUB;
2037