[Home] [Help]
PACKAGE BODY: APPS.GMI_AUTO_ALLOC_BATCH_PKG
Source
1 PACKAGE BODY GMI_AUTO_ALLOC_BATCH_PKG AS
2 /* $Header: GMIALLCB.pls 120.1 2005/06/17 15:04:28 appldev $ */
3 /* ===========================================================================
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 ===========================================================================
8 | FILENAME |
9 | GMIALLCB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains procedures relating to the Enhanced Auto |
13 | Program |
14 | -- Auto_allocate_batch |
15 | HISTORY |
16 | 19-AUG-2002 nchekuri Created |
17 | |
18 | |
19 ===========================================================================
20 API Name : GMI_AUTO_ALLOC_BATCH_PKG
21 Type : Public Package Body
22 Function : This package contains procedures relating to the Enhanced Auto
23 Allocation Engine.
24 Pre-reqs : N/A
25 Parameters: Per function
26
27 Current Vers : 1.0
28 */
29
30 /* Global variables */
31 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_AUTO_ALLOC_BATCH_PKG';
32
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_batch_id Input batch id
45 |
46 | VERSION : current version 1.0
47 | initial version 1.0
48 | COMMENT :
49 |
50 |
51 | Notes :
52 |
53 ****************************************************************************
54 | ======================================================================== */
55
56
57
58
59 PROCEDURE Auto_Allocate_Batch
60 (
61 errbuf OUT NOCOPY VARCHAR2
62 ,retcode OUT NOCOPY VARCHAR2
63 ,p_api_version IN NUMBER
64 ,p_init_msg_list IN VARCHAR2
65 ,p_commit IN VARCHAR2
66 ,p_batch_id IN NUMBER
67 ) IS
68
69 -- Standard constants to be used to check for call compatibility.
70 l_api_version CONSTANT NUMBER := 1.0;
71 l_api_name CONSTANT VARCHAR2(30):= 'allocate_opm_orders';
72
73 -- Local Variables.
74 l_msg_count NUMBER :=0;
75 l_msg_data VARCHAR2(2000);
76 l_return_status VARCHAR2(1);
77
78 x_msg_count NUMBER :=0;
79 x_msg_data VARCHAR2(2000);
80 x_return_status VARCHAR2(1);
81
82 l_wdd_rec wsh_delivery_details%rowtype;
83 l_batch_rec gmi_auto_allocation_batch%rowtype;
84 l_where_clause VARCHAR2(3000):= NULL;
85 l_number_of_rows NUMBER;
86 l_order_by VARCHAR2(3000):= NULL;
87 l_detailed_qty NUMBER;
88 l_detailed_qty2 NUMBER;
89 l_qty_um VARCHAR2(30);
90 l_qty_um2 VARCHAR2(30);
91 l_qc_grade VARCHAR2(30);
92 l_ship_to_org_id NUMBER;
93 l_inventory_item_id NUMBER;
94 l_temp BOOLEAN;
95
96
97 CURSOR get_wdd_line_cur(p_delivery_detail_id IN NUMBER) Is
98 SELECT *
99 FROM wsh_delivery_details wdd
100 WHERE wdd.delivery_detail_id = p_delivery_detail_id;
101
102 CURSOR Get_Batch_Rec_Cur IS
103 SELECT *
104 FROM gmi_auto_allocation_batch
105 WHERE batch_id = p_batch_id;
106
107 TYPE wdd_rc IS REF CURSOR;
108 wdd_cur1 wdd_rc;
109
110 Cursor get_whse_code_cur(p_organization_id IN NUMBER)
111 IS
112 Select whse_code
113 from ic_whse_mst
114 where mtl_organization_id= p_organization_id;
115
116 Cursor get_ship_to_org_cur(p_whse_code IN VARCHAR2)
117 IS
118 Select mtl_organization_id
119 From ic_whse_mst
120 Where whse_code= p_whse_code;
121
122 Cursor get_inventory_item_id_cur(p_item_id IN VARCHAR2)
123 IS
124 Select inventory_item_id
125 From ic_item_mst ic,
126 mtl_system_items mtl
127 where ic.item_id = p_item_id
128 AND mtl.segment1 = ic.item_no;
129
130
131 BEGIN
132
133 /*Int variables
134 =========================================*/
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136 retcode := x_return_status;
137
138 /* Standard begin of API savepoint
139 ===========================================*/
140 SAVEPOINT Auto_Allocate_Batch_SP;
141
142 /*Standard call to check for call compatibility.
143 ==============================================*/
144
145 IF NOT FND_API.compatible_api_call (
146 l_api_version,
147 p_api_version,
148 l_api_name,
149 G_PKG_NAME)
150 THEN
151 GMI_RESERVATION_UTIL.PrintLn('FND_API.compatible_api_call failed');
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END IF;
154
155 /* Check p_init_msg_list
156 =========================================*/
157 IF FND_API.to_boolean(p_init_msg_list)
158 THEN
159 FND_MSG_PUB.initialize;
160 END IF;
161
162 GMI_RESERVATION_UTIL.PrintLn (' After initialize IN Auto_allocation_batch');
163
164 IF( NVL(p_batch_id,0) = 0 ) THEN
165 GMI_RESERVATION_UTIL.PrintLn('Batch id is missing');
166 FND_MESSAGE.Set_Name('GMI','Missing');
167 FND_MESSAGE.Set_Token('MISSING', 'Batch_Id');
168 FND_MSG_PUB.Add;
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171
172 OPEN get_batch_rec_cur;
173 FETCH get_batch_rec_cur INTO l_batch_rec;
174
175 IF(get_batch_rec_cur%NOTFOUND) THEN
176 CLOSE get_batch_rec_cur;
177 GMI_RESERVATION_UTIL.PrintLn('No record in the batch table for batch id: '|| p_batch_id );
178 FND_MESSAGE.Set_Name('GMI','Missing');
179 FND_MESSAGE.Set_Token('Missing','Batch_id');
180 FND_MSG_PUB.Add;
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183
184 CLOSE get_batch_rec_cur;
185
186 GMI_RESERVATION_UTIL.PrintLn(' After Fetching The Batchrec');
187
188 /* The parameters available for search criteria are order_type,line_no to and from,
189 delivery_detail_id to and from , whse_code,item_no, sched_shipdate to and from.
190 */
191 /* we assume the order(s) that need to be allocated are already pick_released. If
192 to/from order_number is supplied then, delivery_detail_id range doesn't exist.
193 */
194 /* Construct the where clause to fetch the delivery records depending on the input
195 parameters
196 */
197
198
199 /* To and From order Numbers */
200
201 l_where_clause := 'Released_status = '||''''||'S'|| ''''; -- Check released_status = 'S' instead of 1 = 1 PK Bug 4025462
202
203 IF( NVL(l_batch_rec.from_order_header_no,0) <> 0 )
204 THEN
205 l_where_clause := l_where_clause || ' AND source_header_number >= ' ;
206 l_where_clause := l_where_clause ||'TO_CHAR(';
207 l_where_clause := l_where_clause ||l_batch_rec.from_order_header_no ;
208 l_where_clause := l_where_clause || ')';
209 END IF;
210
211 IF ( NVL(l_batch_rec.to_order_header_no,0) <> 0)
212 THEN
213 l_where_clause := l_where_clause || ' AND source_header_number <= ' ;
214 l_where_clause := l_where_clause ||'TO_CHAR(';
215 l_where_clause := l_where_clause ||l_batch_rec.to_order_header_no ;
216 l_where_clause := l_where_clause || ')';
217 --||to_char(l_batch_rec.to_order_header_no) ;
218 END IF;
219
220 GMI_RESERVATION_UTIL.PrintLn('1: Where Clause is : ' || l_where_clause);
221
222 /* To and from delivery_detail_ids */
223
224 IF( NVL(l_batch_rec.from_delivery_detail_id,0) <> 0)
225 THEN
226
227 l_where_clause := l_where_clause ||
228 ' AND delivery_detail_id >= ' || l_batch_rec.to_delivery_detail_id;
229 END IF;
230
231 IF ( NVL(l_batch_rec.to_delivery_detail_id,0) <> 0)
232 THEN
233 l_where_clause := l_where_clause ||
234 ' AND delivery_detail_id <= '|| l_batch_rec.to_delivery_detail_id;
235 END IF;
236
237 GMI_RESERVATION_UTIL.PrintLn('2: Where Clause is : ' || l_where_clause);
238
239 /* Order type Id */
240
241 IF ( NVL( l_batch_rec.order_type_id,0) <> 0 )
242 THEN
243 l_where_clause := l_where_clause ||
244 ' AND source_header_type_id = ' || l_batch_rec.order_type_id ;
245 END IF;
246
247 GMI_RESERVATION_UTIL.PrintLn('3: Where Clause is : ' || l_where_clause);
248
249 /* Sched Ship date */
250
251 IF ( TO_CHAR(l_batch_rec.from_sched_ship_date) <> FND_API.G_MISS_CHAR AND
252 TO_CHAR(l_batch_rec.from_sched_ship_date, 'DD-MON-YYYY') <> ' ')
253 THEN
254 l_where_clause := l_where_clause || ' AND TO_DATE(date_scheduled ';
255 l_where_clause := l_where_clause || ',';
256 l_where_clause := l_where_clause || '''';
257 l_where_clause := l_where_clause || 'DD-MON-YY';
258 l_where_clause := l_where_clause || '''';
259 l_where_clause := l_where_clause || ')';
260 l_where_clause := l_where_clause || '>=';
261 l_where_clause := l_where_clause ||'TO_DATE( ';
262 l_where_clause := l_where_clause || '''';
263 l_where_clause := l_where_clause ||l_batch_rec.from_sched_ship_date;
264 l_where_clause := l_where_clause || '''';
265 l_where_clause := l_where_clause || ',';
266 l_where_clause := l_where_clause || '''';
267 l_where_clause := l_where_clause || 'DD-MON-YY';
268 l_where_clause := l_where_clause || '''';
269 l_where_clause := l_where_clause || ')';
270 END IF;
271
272 IF ( TO_CHAR(l_batch_rec.to_sched_ship_date) <> FND_API.G_MISS_CHAR AND
273 TO_CHAR(l_batch_rec.to_sched_ship_date, 'DD-MON-YY') <> ' ')
274 THEN
275 l_where_clause := l_where_clause || ' AND TO_DATE(date_scheduled ';
276 l_where_clause := l_where_clause || ',';
277 l_where_clause := l_where_clause || '''';
278 l_where_clause := l_where_clause || 'DD-MON-YY';
279 l_where_clause := l_where_clause || '''';
280 l_where_clause := l_where_clause || ')';
281 l_where_clause := l_where_clause || '<=';
282 l_where_clause := l_where_clause ||'TO_DATE( ';
283 l_where_clause := l_where_clause || '''';
284 l_where_clause := l_where_clause ||l_batch_rec.to_sched_ship_date;
285 l_where_clause := l_where_clause || '''';
286 l_where_clause := l_where_clause || ',';
287 l_where_clause := l_where_clause || '''';
288 l_where_clause := l_where_clause || 'DD-MON-YY';
289 l_where_clause := l_where_clause || '''';
290 l_where_clause := l_where_clause || ')';
291 END IF;
292
293 GMI_RESERVATION_UTIL.PrintLn('whse_code : '|| l_batch_rec.whse_code);
294
295 /* Warehouse code ( ship_to_org_id) */
296 IF ( NVL( l_batch_rec.whse_code,' ') <> ' ' )
297 THEN
298 OPEN get_ship_to_org_cur(l_batch_rec.whse_code);
299 FETCH get_ship_to_org_cur INTO l_ship_to_org_id;
300 IF(get_ship_to_org_cur%NOTFOUND)
301 THEN
302 GMI_RESERVATION_UTIL.PrintLn('Get_ship_to_org_cur failed, No Data found');
303 CLOSE get_ship_to_org_cur;
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306 CLOSE get_ship_to_org_cur;
307
308 l_where_clause := l_where_clause || ' AND organization_id = ';
309 l_where_clause := l_where_clause || l_ship_to_org_id ;
310
311 END IF;
312
313
314 /* item (inventory_item_id) */
315
316 IF ( NVL( l_batch_rec.item_id,0) <> 0 )
317 THEN
318
319 OPEN get_inventory_item_id_cur(l_batch_rec.item_id);
320 FETCH get_inventory_item_id_cur INTO l_inventory_item_id;
321 IF(get_inventory_item_id_cur%NOTFOUND)
322 THEN
323 GMI_RESERVATION_UTIL.PrintLn('get_inventory_item_id_cur failed, No Data found');
324 CLOSE get_inventory_item_id_cur;
325 RAISE FND_API.G_EXC_ERROR;
326 END IF;
327 CLOSE get_inventory_item_id_cur;
328
329 l_where_clause := l_where_clause || ' AND inventory_item_id = ';
330 l_where_clause := l_where_clause || l_inventory_item_id ;
331
332 END IF;
333
334 GMI_RESERVATION_UTIL.PrintLn('5: Where Clause is : ' || l_where_clause);
335
336 /* fetch the delivery detail rec into l_wdd_line for the above where clause */
337
338 OPEN wdd_cur1 FOR
339 'SELECT * FROM wsh_delivery_details
340
341 WHERE ' || l_where_clause ||
342 'ORDER BY delivery_detail_id ' ;
343 LOOP
344 gmi_reservation_util.println('before fetch');
345 FETCH wdd_cur1 INTO l_wdd_rec;
346 EXIT WHEN wdd_cur1%NOTFOUND;
347
348 GMI_RESERVATION_UTIL.PrintLn('l_wdd_rec.delivery_detail_id'|| l_wdd_rec.delivery_detail_id);
349 GMI_RESERVATION_UTIL.PrintLn('In Auto_allocation_batch Before calling Auto_Alloc_Wdd_line');
350 /* Call auto_alloc_Wdd_line */
351
352 Auto_Alloc_Wdd_Line (
353 p_api_version => 1.0
354 ,p_init_msg_list => FND_API.G_FALSE
355 ,p_commit => FND_API.G_FALSE
356 ,p_wdd_rec => l_wdd_rec
357 ,p_batch_rec => l_batch_rec
358 ,x_number_of_rows => l_number_of_rows
359 ,x_qc_grade => l_qc_grade
360 ,x_detailed_qty => l_detailed_qty
361 ,x_qty_UM => l_qty_um
362 ,x_detailed_qty2 => l_detailed_qty2
363 ,x_qty_UM2 => l_qty_um2
364 ,x_return_status => l_return_status
365 ,x_msg_count => l_msg_count
366 ,x_msg_data => l_msg_data);
367
368 GMI_RESERVATION_UTIL.PrintLn('Status from auto_alloc_Wdd_line'|| l_return_status);
369
370 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
371 THEN
372 GMI_reservation_Util.PrintLn('Return_status from Auto_Alloc_Wdd_Line : '||l_return_status);
373 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS( 'WARNING', '');
374 retcode := 'C';
375
376 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
377 THEN
378 GMI_reservation_Util.PrintLn('Return_status from Auto_Alloc_Wdd_Line : '||l_return_status);
379 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS( 'WARNING', '');
380 FND_MESSAGE.Set_Name('GMI','GMI_AUTO_ALLOC_WDD_LINE_ERROR');
381 FND_MSG_PUB.Add;
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384 x_return_status := l_return_status;
385
386 /* If pick_confirm flag is set then, auto_pick_confirm */
387 GMI_RESERVATION_UTIL.PrintLn('Pick_Confirm_Flag is set to : '|| l_batch_rec.pick_confirm_flag);
388
389 IF ( l_batch_rec.pick_confirm_flag = 'Y' AND x_return_status = FND_API.G_RET_STS_SUCCESS)
390 THEN
391 GMI_RESERVATION_UTIL.PrintLn( 'pick_confirm_flag Is set to Y for batch_id : '|| p_batch_id);
392 l_return_status := FND_API.G_RET_STS_SUCCESS;
393
394 GMI_RESERVATION_UTIL.PrintLn( ' START PICK CONFIRMATION');
395
396 IF(l_wdd_rec.released_status = 'S') THEN
397
398 GMI_RESERVATION_UTIL.PrintLn( 'Before Call to Call_Pick_Confirm ');
399 Call_Pick_Confirm (
400 p_mo_line_id => l_wdd_rec.move_order_line_id
401 , p_delivery_detail_id => l_wdd_rec.delivery_detail_id
402 , p_init_msg_list => 1
403 , p_move_order_type => 3
404 , x_delivered_qty => l_detailed_qty
405 , x_qty_UM => l_qty_um
406 , x_delivered_qty2 => l_detailed_qty2
407 , x_qty_UM2 => l_qty_um2
408 , x_return_status => l_return_status
409 , x_msg_count => l_msg_count
410 , x_msg_data => l_msg_data);
411
412
413 GMI_RESERVATION_UTIL.PrintLn('Status from Call_Pick_Confirm'|| l_return_status);
414 IF(l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
415 THEN
416 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS( 'WARNING', '');
417 retcode := 'P';
418 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
419 THEN
420 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS( 'WARNING', '');
421 GMI_reservation_Util.PrintLn('Return_status from call_pick_confirm : '||x_return_status);
422 FND_MESSAGE.Set_Name('GMI','GMI_PICK_CONFIRM_ERROR');
423 FND_MSG_PUB.Add;
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426
427 ELSE
428 GMI_RESERVATION_UTIL.PrintLn('Can Not Pick Confirm, The release status for the delivery_line is: '||
429 l_wdd_rec.released_status );
430 END IF; /* IF released_status is 'S' */
431
432 ELSE /* Pick Confirm Is set to 'N' */
433 GMI_RESERVATION_UTIL.PrintLn( 'pick_confirm_flag Is set to N for batch_id : '|| p_batch_id);
434 END IF;
435 END LOOP;
436 GMI_RESERVATION_UTIL.PrintLn('ENd of auto_allocate_batch');
437
438 /* EXCEPTION HANDLING
439 ====================*/
440 EXCEPTION
441 WHEN FND_API.G_EXC_ERROR THEN
442 -- ROLLBACK TO SAVEPOINT Auto_Allocate_Batch_SP;
443 GMI_RESERVATION_UTIL.PrintLn('sqlcode : ' ||to_char(sqlcode));
444 GMI_RESERVATION_UTIL.PrintLn('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 errbuf := SUBSTRB(SQLERRM, 1, 150);
447 retcode := x_return_status;
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 Auto_Allocate_Batch_SP;
459 GMI_RESERVATION_UTIL.PrintLn('sqlcode : ' ||to_char(sqlcode));
460 GMI_RESERVATION_UTIL.PrintLn('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
461
462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
463 errbuf := SUBSTRB(SQLERRM, 1, 150);
464 retcode := x_return_status;
465
466
467 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
468 , l_api_name
469 );
470 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
471 , p_count => x_msg_count
472 , p_data => x_msg_data
473 );
474 WHEN OTHERS THEN
475 --ROLLBACK TO SAVEPOINT Auto_Allocate_Batch_SP;
476 GMI_RESERVATION_UTIL.PrintLn('sqlcode : ' ||to_char(sqlcode));
477 GMI_RESERVATION_UTIL.PrintLn('sqlerr : '|| SUBSTRB(SQLERRM, 1, 150));
478
479 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
480 , l_api_name
481 );
482
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 errbuf := SUBSTRB(SQLERRM, 1, 150);
485 retcode := x_return_status;
486
487 /* Get message count and data */
488 FND_MSG_PUB.count_and_get
489 ( p_count => x_msg_count
490 , p_data => x_msg_data
491 );
492
493
494 END Auto_Allocate_batch;
495
496 /* ========================================================================*/
497 /***************************************************************************/
498 /*
499 | PARAMETERS:
500 | p_api_version Known api version
501 | p_init_msg_list FND_API.G_TRUE to reset list
502 | p_commit Commit flag. API commits if this is set.
503 | x_return_status Return status
504 | x_msg_count Number of messages in the list
505 | x_msg_data Text of messages
506 | p_wdd_rec wsh_delivery_details%rowtype
507 | p_batch_rec gmi_auto_allocation_batch%rowtype
508 | x_number_of_rows
509 | x_qc_grade
510 | x_detailed_qty
511 | x_qty_UM
512 | x_detailed_qty2
513 | x_qty_UM2
514 |
515 |
516 | VERSION : current version 1.0
517 | initial version 1.0
518 | COMMENT :
519 |
520 |
521 | Notes :
522 |
523 ****************************************************************************
524 | ======================================================================== */
525
526 PROCEDURE Auto_Alloc_wdd_Line (
527 p_api_version IN NUMBER
528 , p_init_msg_list IN VARCHAR2
529 , p_commit IN VARCHAR2
530 , p_wdd_rec IN wsh_delivery_details%rowtype
531 , p_batch_rec IN gmi_auto_allocation_batch%rowtype
532 , x_number_of_rows OUT NOCOPY NUMBER
533 , x_qc_grade OUT NOCOPY VARCHAR2
534 , x_detailed_qty OUT NOCOPY NUMBER
535 , x_qty_UM OUT NOCOPY VARCHAR2
536 , x_detailed_qty2 OUT NOCOPY NUMBER
537 , x_qty_UM2 OUT NOCOPY VARCHAR2
538 , x_return_status OUT NOCOPY VARCHAR2
539 , x_msg_count OUT NOCOPY NUMBER
540 , x_msg_data OUT NOCOPY VARCHAR2
541 )
542 IS
543
544 -- Standard constants to be used to check for call compatibility.
545 l_api_version CONSTANT NUMBER := 1.0;
546 l_api_name CONSTANT VARCHAR2(30):= 'Auto_Alloc_Wdd_Line';
547
548 -- Local Variables.
549
550 l_msg_count NUMBER :=0;
551 l_msg_data VARCHAR2(2000);
552 l_return_status VARCHAR2(1);
553
554 l_detailed_qty NUMBER := 0;
555 l_ser_index NUMBER;
556 l_expiration_date DATE;
557 x_success NUMBER;
558 l_transfer_to_location NUMBER;
559 l_trans_id NUMBER;
560 l_lot_number NUMBER;
561 l_locator_id NUMBER;
562 l_subinventory_code VARCHAR2(30);
563 l_transaction_quantity NUMBER;
564 l_primary_quantity NUMBER;
565 l_inventory_item_id NUMBER;
566 l_message VARCHAR2(2000);
567 l_commit VARCHAR2(1);
568 l_whse_code VARCHAR2(10);
569 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
570 l_tran_row IC_TRAN_PND%ROWTYPE;
571
572
573 l_allocation_rec GMI_Auto_Allocate_PUB.gmi_allocation_rec;
574 l_ic_item_mst_rec GMI_Reservation_Util.ic_item_mst_rec;
575 --l_ic_item_mst_rec ic_item_mst%ROWTYPE;
576 l_ic_item_mst ic_item_mst%ROWTYPE;
577 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
578 l_op_alot_prm_rec op_alot_prm%rowtype;
579
580 l_ic_whse_mst_rec ic_whse_mst%rowtype;
581 l_total_qty NUMBER;
582 l_total_qty2 NUMBER;
583
584 l_IC$DEFAULT_LOCT VARCHAR2(255)DEFAULT NVL(FND_PROFILE.VALUE('IC$DEFAULT_LOCT'),' ') ;
585 l_GML$DEL_ALC_BEFORE_AUTO VARCHAR2(255) DEFAULT NVL(FND_PROFILE.VALUE('GML$DEL_AlC_BEFORE_AUTO'),' ') ;
586
587
588 Cursor get_whse_cur(p_whse_code IN VARCHAR2) IS
589 Select *
590 From ic_whse_mst
591 where whse_code=p_whse_code;
592
593 Cursor get_item_cur(p_item_id IN NUMBER) IS
594 Select *
595 From ic_item_mst
596 where item_id=p_item_id;
597
598
599 Cursor get_whse_code_cur(p_organization_id IN NUMBER)
600 IS
601 Select whse_code
602 from ic_whse_mst
603 where mtl_organization_id= p_organization_id;
604
605 CURSOR cur_txn_no_default ( p_line_id IN NUMBER,
606 p_location IN VARCHAR2,
607 p_item_id IN NUMBER,
608 p_mo_line_id IN NUMBER)
609 IS
610 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
611 FROM ic_tran_pnd
612 WHERE line_id = p_line_id
613 AND ( lot_id > 0
614 OR location <> p_location )
615 AND item_id = p_item_id
616 AND doc_type = 'OMSO'
617 AND staged_ind = 0
618 AND completed_ind = 0
619 AND delete_mark = 0
620 AND line_detail_id in
621 (Select delivery_detail_id
622 From wsh_delivery_details
623 Where move_order_line_id = p_mo_line_id
624 and released_status in ('R','S'));
625
626 /* Cursor For Existing Allocations */
627 CURSOR c_get_trans_id(p_delivery_detail_id IN NUMBER) IS
628 SELECT ic.trans_id, ic.line_id
629 FROM ic_tran_pnd ic, wsh_delivery_details wsh
630 WHERE ic.line_detail_id = p_delivery_detail_id
631 AND wsh.delivery_detail_id = ic.line_detail_id
632 AND wsh.released_status IN ('R','S')
633 AND ic.doc_type = 'OMSO'
634 AND ic.delete_mark = 0
635 AND ic.staged_ind = 0
636 AND ic.completed_ind = 0
637 AND (ic.lot_id >0 OR ic.location <> 'l_IC$DEFAULT_LOCT');
638
639 ic_tran_tbl_row c_get_trans_id%ROWTYPE;
640
641
642 BEGIN
643
644 /*Init variables
645 =========================================*/
646 x_return_status := FND_API.G_RET_STS_SUCCESS;
647
648
649 /* Standard begin of API savepoint
650 ===========================================*/
651 SAVEPOINT Auto_Alloc_Wdd_Line_SP;
652
653 /*Standard call to check for call compatibility.
654 ==============================================*/
655
656 IF NOT FND_API.compatible_api_call (
657 l_api_version,
658 p_api_version,
659 l_api_name,
660 G_PKG_NAME)
661 THEN
662 GMI_RESERVATION_UTIL.PrintLn('FND_API.compatible_api_call failed');
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 END IF;
665
666
667 /* Check p_init_msg_list
668 =========================================*/
669 IF FND_API.to_boolean(p_init_msg_list)
670 THEN
671 FND_MSG_PUB.initialize;
672 END IF;
673
674 GMI_RESERVATION_UTIL.PrintLn('In Procedure Auto_Alloc_WDD_Line After Initialize');
675
676 /* Below procedure fills in allocation record
677 TYPE gmi_allocation_rec is RECORD
678 ( doc_id IC_TRAN_PND.DOC_ID%TYPE
679 , line_id IC_TRAN_PND.LINE_ID%TYPE
680 , doc_line IC_TRAN_PND.DOC_LINE%TYPE
681 , line_detail_id IC_TRAN_PND.LINE_DETAIL_ID%TYPE
682 , item_no IC_ITEM_MST.ITEM_NO%TYPE
683 , whse_code IC_WHSE_MST.WHSE_CODE%TYPE
684 , co_code OP_CUST_MST.CO_CODE%TYPE
685 , cust_no OP_CUST_MST.CUST_NO%TYPE
686 , prefqc_grade OP_ORDR_DTL.QC_GRADE_WANTED%TYPE
687 , order_qty1 OP_ORDR_DTL.ORDER_QTY1%TYPE
688 , order_qty2 OP_ORDR_DTL.ORDER_QTY2%TYPE
689 , order_um1 OP_ORDR_DTL.ORDER_UM1%TYPE
690 , order_um2 OP_ORDR_DTL.ORDER_UM2%TYPE
691 , ship_to_org_id oe_order_lines_all.SHIP_TO_ORG_ID%TYPE
692 , of_cust_id oe_order_lines_all.sold_to_org_id%TYPE
693 , org_id oe_order_lines_all.org_id%TYPE
694 , trans_date IC_TRAN_PND.TRANS_DATE%TYPE
695 , user_id FND_USER.USER_ID%TYPE
696 , user_name FND_USER.USER_NAME%TYPE
697 );
698 */
699
700 OPEN Get_whse_code_cur(p_wdd_rec.organization_id);
701 FETCH get_whse_code_cur into l_whse_code;
702
703 IF(get_whse_code_cur%NOTFOUND)
704 THEN
705 GMI_RESERVATION_UTIL.PrintLn('Get_Whse_Code_cur Failed, No Data found');
706 CLOSE get_whse_code_cur;
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710 CLOSE get_whse_code_cur;
711
712 GMI_RESERVATION_UTIL.PrintLn('l_whse_code is '|| l_whse_code);
713 GMI_RESERVATION_UTIL.PrintLn('Wdd org_id is '|| p_wdd_rec.organization_id);
714
715 IF (NVL(p_batch_rec.delete_existing_aloc_flag,'N') = 'Y' OR
716 (p_batch_rec.delete_existing_aloc_flag IS NULL AND UPPER(l_GML$DEL_ALC_BEFORE_AUTO) = 'YES') )
717 THEN
718 GMI_RESERVATION_UTIL.println('Delete_existing_aloc_flag is Y');
719
720 OPEN c_get_trans_id(p_wdd_rec.delivery_detail_id);
721 FETCH c_get_trans_id INTO ic_tran_tbl_row;
722
723 IF (c_get_trans_id%FOUND)
724 THEN
725 GMI_RESERVATION_UTIL.println('In c_get_trans_id%FOUND');
726 GMI_RESERVATION_UTIL.println('Deleting existing Allocations');
727 WHILE c_get_trans_id%FOUND
728 LOOP
729 l_tran_rec.trans_id := ic_tran_tbl_row.trans_id;
730 GMI_Reservation_Util.PrintLn('l_tran_rec.trans_id = ' || l_tran_rec.trans_id);
731 GMI_Reservation_Util.PrintLn('Before Call to Delete Pending Transaction');
732
733 GMI_TRANS_ENGINE_PUB.delete_pending_transaction (
734 1
735 , FND_API.G_FALSE
736 , FND_API.G_FALSE
737 , FND_API.G_VALID_LEVEL_FULL
738 , l_tran_rec
739 , l_tran_row
740 , x_return_status
741 , x_msg_count
742 , x_msg_data
743 );
744 GMI_Reservation_Util.PrintLn('After Call to Delete Pending Transaction');
745 GMI_Reservation_Util.PrintLn('Return from DELETE PENDING TRANS x_return_status = ' || x_return_status);
746
747 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
748 THEN
749 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
751 END IF;
752
753 FETCH c_get_trans_id INTO ic_tran_tbl_row;
754 END LOOP;
755 CLOSE c_get_trans_id;
756 END IF;
757 END IF;
758
759 GMI_RESERVATION_UTIL.PrintLn('Before the call to Get_Allocation_Record ');
760
761 Get_Allocation_Record (
762 p_wdd_line => p_wdd_rec
763 , x_allocation_rec => l_allocation_rec
764 , x_ic_item_mst_rec => l_ic_item_mst_rec
765 , x_return_status => x_return_status
766 , x_msg_count => x_msg_count
767 , x_msg_data => x_msg_data
768 );
769
770 GMI_RESERVATION_UTIL.PrintLn('Status from Get_Allocation_Record Is : '|| x_return_status);
771
772 IF nvl(l_allocation_rec.order_qty1,0)=0 THEN
773 GMI_RESERVATION_UTIL.println('Line is fully allocated, nothing to do, RETURN');
774 RETURN;
775 END IF;
776
777 GMI_RESERVATION_UTIL.println('Will attempt to allocate '|| l_allocation_rec.order_qty1);
778
779
780 OPEN Get_Whse_Cur(l_whse_code);
781 FETCH Get_Whse_Cur Into l_ic_whse_mst_rec;
782 IF(Get_Whse_Cur%NOTFOUND)
783 THEN
784 GMI_RESERVATION_UTIL.println('Get_Whse_Cur%Notfound');
785 CLOSE Get_Whse_Cur;
786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
787 END IF;
788
789 CLOSE Get_Whse_Cur;
790
791
792 IF (l_ic_item_mst_rec.lot_ctl = 0 and l_ic_item_mst_rec.loct_ctl * l_ic_whse_mst_rec.loct_ctl = 0) THEN
793 GMI_RESERVATION_UTIL.println('NON CONTROL . Auto allocation can not be performed');
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 END IF;
796
797 IF (nvl(l_ic_item_mst_rec.alloc_class, '%#S$%') = '%#S$%' ) THEN
798 GMI_RESERVATION_UTIL.println('Alloc Class is missing from item set up.');
799 GMI_RESERVATION_UTIL.println('Auto allocation can not be performed');
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 /* find out if there is default lot already */
804 GMI_RESERVATION_UTIL.println('Before find_default_lot');
805 GMI_RESERVATION_UTIL.find_default_lot
806 ( x_return_status => x_return_status,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 x_reservation_id => l_trans_id,
810 p_line_id => p_wdd_rec.source_line_id
811
812 );
813
814 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
815 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
817 END IF;
818
819 IF nvl(l_trans_id, 0) <> 0 THEN
820 GMI_RESERVATION_UTIL.println('Default trans exists');
821 ELSE
822 GMI_RESERVATION_UTIL.println('Create default trans ');
823 GMI_RESERVATION_UTIL.create_dflt_lot_from_scratch
824 ( p_whse_code => l_whse_code
825 , p_line_id => p_wdd_rec.source_line_id
826 , p_item_id => l_ic_item_mst_rec.item_id
827 , p_qty1 => p_wdd_rec.requested_quantity
828 , p_qty2 => p_wdd_rec.requested_quantity2
829 , x_return_status => x_return_status
830 , x_msg_count => x_msg_count
831 , x_msg_data => x_msg_data
832 );
833 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
834 GMI_Reservation_Util.PrintLn('creating default lot returns error');
835 raise FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837 END IF;
838
839 GMI_RESERVATION_UTIL.println('Before Call To get allocation Parms');
840
841 GMI_ALLOCATION_RULES_PVT.GET_ALLOCATION_PARMS
842 ( p_alloc_class => l_ic_item_mst_rec.alloc_class,
843 p_org_id => l_allocation_rec.org_id,
844 p_of_cust_id => l_allocation_rec.of_cust_id,
845 p_ship_to_org_id=> l_allocation_rec.ship_to_org_id,
846 x_return_status => l_return_status,
847 x_op_alot_prm => l_op_alot_prm_rec,
848 x_msg_count => x_msg_count,
849 x_msg_data => x_msg_data
850 );
851
852
853 GMI_RESERVATION_UTIL.println('Status from GET_ALLOCATION_PARMS : ' ||l_return_status);
854
855 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
856 GMI_Reservation_Util.PrintLn('get allocation prm returns error');
857 raise FND_API.G_EXC_UNEXPECTED_ERROR;
858 END IF;
859
860 --l_commit := FND_API.G_FALSE;
861
862 OPEN get_item_cur(l_ic_item_mst_rec.item_id);
863 FETCH get_item_cur INTO l_ic_item_mst;
864
865 IF (get_item_cur%NOTFOUND)
866 THEN
867 GMI_RESERVATION_UTIL.println('Get_Item_Cur%NOTFOUND');
868 raise FND_API.G_EXC_UNEXPECTED_ERROR;
869 END IF;
870
871 CLOSE get_item_cur;
872
873 GMI_RESERVATION_UTIL.println('Before Call To GMI_ALLOCATE_INVENTORY_PVT.Allocate_line');
874
875
876 GMI_ALLOCATE_INVENTORY_PVT.ALLOCATE_LINE
877 ( p_allocation_rec => l_allocation_rec
878 , p_ic_item_mst => l_ic_item_mst
879 , p_ic_whse_mst => l_ic_whse_mst_rec
880 , p_op_alot_prm => l_op_alot_prm_rec
881 , p_batch_id => p_batch_rec.batch_id
882 , x_allocated_qty1 => x_detailed_qty
883 , x_allocated_qty2 => x_detailed_qty2
884 , x_return_status => x_return_status
885 , x_msg_count => x_msg_count
886 , x_msg_data => x_msg_data
887 );
888
889 GMI_RESERVATION_UTIL.println('After Call To GMI_ALLOCATE_INVENTORY_PVT.Allocate_line');
890 GMI_RESERVATION_UTIL.println('GMI_ALLOCATE_INVENTORY_PVT.Allocate line status '|| x_return_status);
891
892 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
893 GMI_RESERVATION_UTIL.println('Warning returned by allocate line ');
894 RETURN;
895 ELSIF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
896 GMI_RESERVATION_UTIL.println('Error returned by allocate line ');
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899
900 GMI_RESERVATION_UTIL.println('Before Call To GMI_RESERVATION_UTIL.find_default_lot');
901 GMI_RESERVATION_UTIL.find_default_lot
902 ( x_return_status => l_return_status,
903 x_msg_count => x_msg_count,
904 x_msg_data => x_msg_data,
905 x_reservation_id => l_trans_id,
906 p_line_id => p_wdd_rec.source_line_id
907 );
908 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
909 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 l_old_transaction_rec.trans_id := l_trans_id;
914 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
915 (l_old_transaction_rec, l_old_transaction_rec )
916 THEN
917 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_wdd_rec.source_line_id);
918 GMI_RESERVATION_UTIL.balance_default_lot
919 ( p_ic_default_rec => l_old_transaction_rec
920 , p_opm_item_id => l_old_transaction_rec.item_id
921 , x_return_status => l_return_status
922 , x_msg_count => x_msg_count
923 , x_msg_data => x_msg_data
924 );
925 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
926 THEN
927 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929 END IF;
930 END IF;
931
932 OPEN cur_txn_no_default
933 ( p_line_id => p_wdd_rec.source_line_id,
934 p_location => FND_PROFILE.Value('IC$DEFAULT_LOCT'),
935 p_item_id => l_ic_item_mst_rec.item_id,
936 p_mo_line_id => p_wdd_rec.move_order_line_id
937 );
938
939 FETCH cur_txn_no_default INTO l_total_qty, l_total_qty2;
940 IF cur_txn_no_default%NOTFOUND THEN
941 CLOSE cur_txn_no_default;
942 GMI_Reservation_Util.PrintLn('txn_no_default : NOTFOUND ');
943 FND_MESSAGE.SET_NAME('GMI','GMI_REQUIRED_MISSING');
944 RAISE FND_API.G_EXC_ERROR;
945 END IF;
946 CLOSE cur_txn_no_default;
947
948 GMI_RESERVATION_UTIL.PrintLn('l_total_qty '||l_total_qty);
949 GMI_RESERVATION_UTIL.PrintLn(' l_total_qty2 '||l_total_qty2);
950
951 UPDATE ic_txn_request_lines
952 SET quantity_detailed = l_total_qty
953 , secondary_quantity_detailed = l_total_qty2
954 WHERE line_id = p_wdd_rec.move_order_line_id;
955
956 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)
957 THEN
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959 END IF;
960
961 GMI_RESERVATION_UTIL.PrintLn('Exiting Procedure Auto_Alloc_Wdd_Line Successfully');
962
963 /* EXCEPTION HANDLING
964 ====================*/
965 EXCEPTION
966 WHEN FND_API.G_EXC_ERROR THEN
967 ROLLBACK TO SAVEPOINT Auto_Alloc_Wdd_Line_SP;
968
969 x_return_status := FND_API.G_RET_STS_ERROR;
970
971 GMI_RESERVATION_UTIL.PrintLn('In Exception FND_API.G_EXC_ERROR In Procedure Auto_Alloc_Wdd_Line ');
972
973 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
974 , l_api_name
975 );
976 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
977 , p_count => x_msg_count
978 , p_data => x_msg_data
979 );
980
981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
982 ROLLBACK TO SAVEPOINT Auto_Alloc_Wdd_Line_SP;
983
984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
985 GMI_RESERVATION_UTIL.PrintLn('In Exception FND_API.G_EXC_UNEXPECTED_ERROR In Procedure Auto_Alloc_Wdd_Line ');
986
987 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
988 , l_api_name
989 );
990 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
991 , p_count => x_msg_count
992 , p_data => x_msg_data
993 );
994 WHEN OTHERS THEN
995 ROLLBACK TO SAVEPOINT Auto_Alloc_Wdd_Line_SP;
996 GMI_RESERVATION_UTIL.PrintLn('In Exception OTHERS In Procedure Auto_Alloc_Wdd_Line ');
997 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
998 , l_api_name
999 );
1000
1001 /* Get message count and data */
1002 FND_MSG_PUB.count_and_get
1003 ( p_count => x_msg_count
1004 , p_data => x_msg_data
1005 );
1006
1007 END auto_alloc_wdd_line;
1008
1009
1010 PROCEDURE Call_Pick_Confirm
1011 ( p_mo_line_id IN NUMBER
1012 , p_delivery_detail_id IN NUMBER DEFAULT NULL
1013 , p_init_msg_list IN NUMBER
1014 , p_move_order_type IN NUMBER
1015 , x_delivered_qty OUT NOCOPY NUMBER
1016 , x_qty_UM OUT NOCOPY VARCHAR2
1017 , x_delivered_qty2 OUT NOCOPY NUMBER
1018 , x_qty_UM2 OUT NOCOPY VARCHAR2
1019 , x_return_status OUT NOCOPY VARCHAR2
1020 , x_msg_count OUT NOCOPY NUMBER
1021 , x_msg_data OUT NOCOPY VARCHAR2
1022 )
1023 IS
1024 l_api_version_number CONSTANT NUMBER := 1.0;
1025 l_init_msg_list VARCHAR2(255) := FND_API.G_TRUE;
1026 l_api_name CONSTANT VARCHAR2(30) := 'Line_Pick_Confirm';
1027 x_success NUMBER;
1028
1029 l_mo_hdr_rec GMI_Move_Order_Global.mo_hdr_rec;
1030 l_mo_line_tbl GMI_Move_Order_Global.mo_line_tbl;
1031 ll_mo_line_tbl GMI_Move_Order_Global.mo_line_tbl;
1032 l_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
1033 ll_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
1034
1035 l_return_status VARCHAR2(1);
1036 l_grouping_rule_id NUMBER;
1037 l_count NUMBER;
1038 l_detail_rec_count NUMBER;
1039 l_success NUMBER;
1040 l_request_number VARCHAR2(80);
1041 l_commit VARCHAR2(1);
1042 l_allowed VARCHAR2(2);
1043 l_max_qty NUMBER;
1044 l_max_qty2 NUMBER;
1045 l_transacted_qty NUMBER;
1046 l_transacted_qty2 NUMBER;
1047 l_trans_qty NUMBER;
1048 l_trans_qty2 NUMBER;
1049 l_del_trans_qty NUMBER;
1050 l_del_trans_qty2 NUMBER;
1051
1052 G_RET_STS_WARNING EXCEPTION;
1053
1054 CURSOR get_transacted_qty ( p_line_id IN NUMBER
1055 )
1056 is
1057 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
1058 FROM ic_tran_pnd
1059 WHERE line_id = p_line_id
1060 AND doc_type = 'OMSO'
1061 AND completed_ind = 0
1062 AND delete_mark = 0
1063 AND (line_detail_id is not null
1064 and line_detail_id in
1065 ( Select delivery_detail_id
1066 From wsh_delivery_details
1067 Where source_line_id = p_line_id
1068 and released_status in ('Y','C'))); -- only the staged, and completed qtys should count
1069 -- then add the current line
1070 CURSOR get_trans_qty_for_del ( p_line_id NUMBER,
1071 p_line_detail_id NUMBER)
1072 is
1073 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
1074 FROM ic_tran_pnd
1075 WHERE line_id = p_line_id
1076 AND doc_type = 'OMSO'
1077 AND completed_ind = 0
1078 AND delete_mark = 0
1079 AND line_detail_id = p_line_detail_id;
1080
1081 BEGIN
1082 /* Init status : */
1083 x_return_status := FND_API.G_RET_STS_SUCCESS;
1084
1085 IF (p_init_msg_list = 1)
1086 THEN
1087 FND_MSG_PUB.Initialize;
1088 END IF;
1089
1090 GMI_RESERVATION_UTIL.PrintLn('In Call_Pick_Confirm after initialize');
1091 /* check what is the move order type. If it's a non pick wave mo,
1092 call the pick engine, otherwise, call the pick release api
1093 call directed pick and put away api
1094 */
1095 IF ( p_move_order_type = 3 )
1096 THEN
1097 /* Get The Move Order line (1 line) */
1098 l_mo_line_tbl(1) := GMI_Move_Order_Line_Util.Query_Row( p_mo_line_id);
1099
1100 l_mo_line_rec.line_id := p_mo_line_id;
1101
1102 l_mo_line_rec := l_mo_line_tbl(1);
1103
1104 GMI_Move_Order_Line_Util.Lock_Row(
1105 p_mo_line_rec => l_mo_line_rec
1106 , x_mo_line_rec => ll_mo_line_rec
1107 , x_return_status => x_return_status);
1108
1109 IF ( x_return_status = '54' )
1110 THEN
1111 GMI_Reservation_Util.PrintLn('(Auto_alloc_batch) Call_Pick_Confirm : the MO is locked for line_id='||p_mo_line_id);
1112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1113 END IF;
1114
1115 -- for non ctl items, no allocation is made but the default trans
1116 -- qty is updated by the system, can not be over allocating
1117
1118 /* this will be checked inside the API */
1119
1120 OPEN get_transacted_qty (l_mo_line_rec.txn_source_line_id);
1121 FETCH get_transacted_qty Into l_transacted_qty, l_transacted_qty2;
1122 IF(get_transacted_qty%NOTFOUND)
1123 THEN
1124 GMI_Reservation_Util.PrintLn('get_transacted_qty%NOTFOUND');
1125 CLOSE get_transacted_qty;
1126 END IF;
1127 CLOSE get_transacted_qty;
1128
1129 GMI_Reservation_Util.PrintLn('Total qtys have been transacted for source line '
1130 || l_mo_line_rec.txn_source_line_id);
1131 GMI_Reservation_Util.PrintLn(' total qty1 '|| l_transacted_qty);
1132 GMI_Reservation_Util.PrintLn(' total qty2 '|| l_transacted_qty2);
1133
1134 OPEN get_trans_qty_for_del(l_mo_line_rec.txn_source_line_id, p_delivery_detail_id);
1135 FETCH get_trans_qty_for_del Into l_del_trans_qty, l_del_trans_qty2;
1136 IF(get_trans_qty_for_del%NOTFOUND )
1137 THEN
1138 CLOSE get_trans_qty_for_del;
1139 END IF;
1140 CLOSE get_trans_qty_for_del;
1141
1142 GMI_Reservation_Util.PrintLn('Total qty1 being transacted '|| l_del_trans_qty);
1143 GMI_Reservation_Util.PrintLn('Total qty2 being transacted '|| l_del_trans_qty2);
1144
1145 l_trans_qty := nvl(l_del_trans_qty,0);
1146 l_trans_qty2 := nvl(l_del_trans_qty2,0);
1147
1148 GMI_RESERVATION_UTIL.PrintLn('Before Calling GMI_Pick_Wave_Confirm_PVT.Check_Shipping_Tolerances');
1149
1150 GMI_Pick_Wave_Confirm_PVT.Check_Shipping_Tolerances
1151 ( x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data,
1154 x_allowed => l_allowed,
1155 x_max_quantity => l_max_qty,
1156 x_max_quantity2 => l_max_qty2,
1157 p_line_id => l_mo_line_rec.line_id,
1158 p_quantity => l_trans_qty,
1159 p_quantity2 => l_trans_qty2
1160 );
1161
1162 GMI_RESERVATION_UTIL.PrintLn('After Calling GMI_Pick_Wave_Confirm_PVT.Check_Shipping_Tolerances');
1163 GMI_RESERVATION_UTIL.PrintLn('Status from Check_Shipping_Tolerances : '||x_return_status);
1164 IF x_return_status <> 'S' THEN
1165 fnd_message.set_name('INV', 'INV_CHECK_TOLERANCE_ERROR');
1166 RAISE FND_API.G_EXC_ERROR;
1167 ELSE
1168 IF l_allowed = 'N' THEN
1169 GMI_Reservation_Util.PrintLn('WARNING!');
1170 GMI_Reservation_Util.PrintLn('MOVE ORDER line : line_id ='||l_mo_line_rec.line_id ||
1171 ' can not be transacted because picked qty exceeds over shippment tolerance. '||
1172 ' The allocated quantity is '|| l_mo_line_rec.quantity_detailed
1173 ||' but the max allowed quantity is '||
1174 l_max_qty || ' Please reduce allocation quantity ');
1175 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1176 RETURN;
1177 END IF ;
1178 END IF;
1179
1180 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_header_id ='||l_mo_line_tbl(1).header_id);
1181 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_line_tbl.COUNT ='||l_mo_line_tbl.COUNT);
1182
1183 SAVEPOINT GMI_Before_Pick_Confirm;
1184 l_commit := FND_API.G_FALSE;
1185
1186 GMI_Reservation_Util.PrintLn('(opm_dbg) Before calling Pick_Confirm ='||l_mo_line_tbl(1).header_id);
1187 GMI_Pick_Wave_Confirm_PVT.Pick_Confirm(
1188 p_api_version_number => 1.0,
1189 p_init_msg_lst => FND_API.G_FALSE,
1190 p_validation_flag => FND_API.G_VALID_LEVEL_FULL,
1191 p_commit => l_commit,
1192 p_delivery_detail_id => p_delivery_detail_id,
1193 p_mo_line_tbl => l_mo_line_tbl,
1194 x_mo_line_tbl => ll_mo_line_tbl,
1195 x_return_status => l_return_status,
1196 x_msg_data => x_msg_data,
1197 x_msg_count => x_msg_count);
1198
1199 GMI_Reservation_Util.PrintLn('(opm_dbg) l_return_status from GMI_pick_wave_Confirm_pub.Pick_Confirm is ' || l_return_status);
1200 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_line.count=' || ll_mo_line_tbl.count);
1201 /* Message('l_return_status from GMI_pick_release_pub.Auto_detail is ' || l_return_status); */
1202
1203 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1204 THEN
1205 GMI_Reservation_Util.PrintLn('return error');
1206 FND_MESSAGE.Set_Name('GMI','PICK_CONFIRM_ERROR');
1207 FND_MESSAGE.Set_Token('WHERE', 'AFTER_CALL_PICK_CONFIRM');
1208 FND_MESSAGE.Set_Token('WHAT', 'UnexpectedError');
1209 FND_MSG_PUB.Add;
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211 END IF;
1212
1213 IF ( ll_mo_line_tbl.count = 0 )
1214 THEN
1215 GMI_Reservation_Util.PrintLn('return error');
1216 FND_MESSAGE.Set_Name('GMI','PICK_CONFIRM_ERROR');
1217 FND_MESSAGE.Set_Token('WHERE', 'MO_LINE_COUNT_0');
1218 FND_MESSAGE.Set_Token('WHAT', 'UnexpectedError');
1219 FND_MSG_PUB.Add;
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222
1223 END IF;
1224
1225
1226 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value');
1227 /* ==================================================================
1228 Set the Returned values from the GMI_Reservation_Util.ic_tran_rec_tbl
1229 ================================================================== */
1230 x_delivered_qty := ll_mo_line_tbl(1).quantity_delivered;
1231
1232 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 1');
1233 x_qty_UM := ll_mo_line_tbl(1).uom_code;
1234
1235 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 2');
1236 x_delivered_qty2 := ll_mo_line_tbl(1).secondary_quantity_delivered;
1237
1238 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 3');
1239 x_qty_UM2 := ll_mo_line_tbl(1).secondary_uom_code;
1240
1241 GMI_Reservation_Util.PrintLn('(opm_dbg) End of GMI_auto_allocation_batch_pkg.Call_Pick_Confirm, l_return_status is '
1242 || l_return_status);
1243
1244 EXCEPTION
1245 WHEN FND_API.G_EXC_ERROR THEN
1246 /* ROLLBACK TO SAVEPOINT GMI_Before_Pick_Confirm; */
1247
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1250
1251 WHEN G_RET_STS_WARNING THEN
1252
1253 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1254
1255
1256 WHEN OTHERS THEN
1257 /* ROLLBACK TO SAVEPOINT GMI_Before_Pick_Confirm; */
1258
1259 IF (x_return_status = '54')
1260 THEN
1261 x_return_status := '54' ;
1262 ELSE
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 END IF;
1265
1266 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1267 THEN
1268 FND_MSG_PUB.Add_Exc_Msg
1269 ( G_PKG_NAME
1270 , 'Call_Pick_Confirm');
1271 END IF;
1272 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1273
1274 END Call_Pick_Confirm;
1275
1276 PROCEDURE get_allocation_record
1277 ( p_wdd_line IN wsh_delivery_details%rowtype
1278 , x_allocation_rec OUT NOCOPY GMI_Auto_Allocate_PUB.gmi_allocation_rec
1279 , x_ic_item_mst_rec OUT NOCOPY GMI_Reservation_Util.ic_item_mst_rec
1280 , x_return_status OUT NOCOPY VARCHAR2
1281 , x_msg_count OUT NOCOPY NUMBER
1282 , x_msg_data OUT NOCOPY VARCHAR2
1283 ) IS
1284
1285 l_api_name CONSTANT VARCHAR2 (30) := 'Get_Allocation_Record';
1286 l_tmp_qty NUMBER;
1287 l_header_id NUMBER;
1288 l_co_code VARCHAR2(10);
1289 x_orgn_code VARCHAR2(10);
1290 l_del_qty NUMBER;
1291 l_del_qty2 NUMBER;
1292
1293 /* ==== Cursors ============================================================== */
1294 /* removed from this cursor : */
1295 /* oe_order_header_all oeh, */
1296 /* AND oeh.header_id = oel.header_id */
1297
1298 CURSOR c_customer_and_so_info (oe_line_id IN NUMBER) IS
1299 SELECT oel.sold_to_org_id
1300 , oel.ship_to_org_id
1301 , oel.line_number + (oel.shipment_number / 10)
1302 , oel.org_id
1303 , oel.schedule_ship_date
1304 , oel.header_id
1305 FROM oe_order_lines_all oel
1306 WHERE oel.line_id = p_wdd_line.source_line_id;
1307
1308 l_oe_line c_customer_and_so_info%rowtype;
1309 CURSOR c_user IS
1310 SELECT user_id,
1311 user_name
1312 FROM fnd_user
1313 WHERE user_id = FND_GLOBAL.USER_ID;
1314
1315 Cursor get_whse_code(p_organization_id IN NUMBER)
1316 IS
1317 Select whse_code
1318 from ic_whse_mst
1319 where mtl_organization_id= p_organization_id;
1320 Cursor get_allocated_qty (p_line_id IN NUMBER
1321 , p_line_detail_id IN NUMBER)
1322 IS
1323 Select abs(sum(nvl(trans_qty,0))), abs(sum(nvl(trans_qty2,0)))
1324 From ic_tran_pnd
1325 where line_id = p_line_id
1326 and line_detail_id = p_line_detail_id
1327 and doc_type = 'OMSO'
1328 and delete_mark = 0;
1329 -- and completed_ind = 0; PK Bug 4025462 Do not check this.
1330
1331 Cursor Cur_get_process_org
1332 ( p_organization_id IN NUMBER)
1333 IS
1334 SELECT w.whse_code,
1335 s.co_code,
1336 s.orgn_code
1337 FROM mtl_parameters p,
1338 ic_whse_mst w,
1339 sy_orgn_mst s
1340 WHERE
1341 w.mtl_organization_id = p.organization_id
1342 AND p.ORGANIZATION_ID = p_organization_id
1343 AND s.orgn_code = w.orgn_code
1344 AND s.orgn_code = p.process_orgn_code
1345 AND p.process_enabled_flag ='Y'
1346 AND s.delete_mark = 0
1347 AND w.delete_mark = 0;
1348
1349 BEGIN
1350
1351 GMI_RESERVATION_UTIL.PrintLn('Beginning of Get_Allocation_Record ');
1352
1353 /* ======================================================================= */
1354 /* Init variables */
1355 /* ======================================================================= */
1356 x_return_status := FND_API.G_RET_STS_SUCCESS;
1357
1358 OPEN c_customer_and_so_info(x_allocation_rec.line_id);
1359 FETCH c_customer_and_so_info
1360 INTO x_allocation_rec.of_cust_id
1361 , x_allocation_rec.ship_to_org_id
1362 , x_allocation_rec.doc_line
1363 , x_allocation_rec.org_id
1364 , x_allocation_rec.trans_date
1365 , l_header_id
1366 ;
1367
1368 IF (c_customer_and_so_info%NOTFOUND) THEN
1369 CLOSE c_customer_and_so_info;
1370 GMI_reservation_Util.PrintLn('Customer info not found');
1371 GMI_reservation_Util.PrintLn('in Util v: cust_no=NOTFOUND');
1372 FND_MESSAGE.Set_Name('GMI','GMI_CUST_INFO');
1373 FND_MESSAGE.Set_Token('SO_LINE_ID', x_allocation_rec.line_id);
1374 FND_MSG_PUB.Add;
1375 RAISE FND_API.G_EXC_ERROR;
1376 ELSE
1377 GMI_reservation_Util.PrintLn(' in Util v: cust_id='||x_allocation_rec.of_cust_id||',
1378 doc_line='||x_allocation_rec.doc_line);
1379 END IF;
1380 CLOSE c_customer_and_so_info;
1381
1382 /* ============================================================================================= */
1383 /* Initialize the allocation record type */
1384 /* Note that the Qty are not converted (only the Apps/OPM UOM) */
1385 /* ============================================================================================= */
1386
1387 GMI_RESERVATION_UTIL.PrintLn('Set up Trans rec');
1388 x_allocation_rec.doc_id := INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(l_header_id);
1389
1390 IF ( x_allocation_rec.doc_id IS NULL ) THEN
1391 FND_MESSAGE.SET_NAME('GMI','INV_COULD_NOT_GET_MSO_HEADER');
1392 FND_MESSAGE.Set_Token('OE_HEADER_ID', l_header_id);
1393 FND_MSG_PUB.Add;
1394 RAISE FND_API.G_EXC_ERROR;
1395 END IF;
1396
1397 OPEN get_allocated_qty (p_wdd_line.source_line_id, p_wdd_line.delivery_detail_id);
1398 FETCH get_allocated_qty Into l_del_qty, l_del_qty2;
1399 IF(get_allocated_qty%NOTFOUND)
1400 THEN
1401 GMI_RESERVATION_UTIL.PrintLn('get_allocated_qty%NOTFOUND');
1402 CLOSE get_allocated_qty;
1403 END IF;
1404 CLOSE get_allocated_qty;
1405
1406
1407 GMI_reservation_Util.PrintLn(' allocated qty for delivery '||l_del_qty);
1408 GMI_reservation_Util.PrintLn(' allocated qty2 for delivery '||l_del_qty2);
1409
1410 x_allocation_rec.line_id := p_wdd_line.source_line_id;
1411 x_allocation_rec.prefqc_grade := p_wdd_line.preferred_grade;
1412 x_allocation_rec.order_qty1 := p_wdd_line.requested_quantity - nvl(l_del_qty,0);
1413
1414 GMI_reservation_Util.PrintLn(' requested qty for delivery '||p_wdd_line.requested_quantity);
1415
1416 IF x_allocation_rec.order_qty1 < 0 THEN
1417 x_allocation_rec.order_qty1 := 0;
1418 END IF;
1419
1420 x_allocation_rec.line_detail_id := p_wdd_line.delivery_detail_id;
1421
1422 OPEN get_whse_code(p_wdd_line.organization_id);
1423 FETCH get_whse_code INTO x_allocation_rec.whse_code;
1424 IF(get_whse_code%NOTFOUND)
1425 THEN
1426 GMI_RESERVATION_UTIL.PrintLn('get_whse_code%NOTFOUND');
1427 CLOSE get_whse_code;
1428 END IF;
1429
1430 CLOSE get_whse_code;
1431
1432
1433 x_allocation_rec.user_id := FND_GLOBAL.user_id;
1434
1435
1436 /* ============================================================================================= */
1437 /* Check Source Type */
1438 /* ============================================================================================= */
1439
1440 /* ============================================================================================= */
1441 /* Get whse, and organization code from Process. */
1442 /* ============================================================================================= */
1443
1444 GMI_RESERVATION_UTIL.PrintLn('Before Opening cursor Cur_get_process_org');
1445
1446 OPEN cur_get_process_org(p_wdd_line.organization_id);
1447 FETCH cur_get_process_org INTO x_allocation_rec.whse_code,l_co_code,x_orgn_code;
1448 IF(Cur_get_process_org%NOTFOUND) THEN
1449 CLOSE Cur_get_process_org;
1450 GMI_reservation_Util.PrintLn(' in end of Get_Allocation__Record ERROR:No rows found for cur_get_process_org.');
1451 FND_MESSAGE.Set_Name('GMI','GMI_GET_PROCESS_ORG');
1452 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_wdd_line.organization_id);
1453 FND_MSG_PUB.Add;
1454 RAISE FND_API.G_EXC_ERROR;
1455 ELSE
1456 CLOSE Cur_get_process_org;
1457 END IF;
1458
1459 x_allocation_rec.co_code := l_co_code;
1460
1461 /* ============================================================================================= */
1462 /* Get Item details */
1463 /* ============================================================================================= */
1464 GMI_RESERVATION_UTIL.PrintLn('Before get opm item from apps');
1465
1466 GMI_RESERVATION_UTIL.Get_OPM_item_from_Apps(
1467 p_organization_id => p_wdd_line.organization_id
1468 , p_inventory_item_id => p_wdd_line.inventory_item_id
1469 , x_ic_item_mst_rec => x_ic_item_mst_rec
1470 , x_return_status => x_return_status
1471 , x_msg_count => x_msg_count
1472 , x_msg_data => x_msg_data);
1473
1474
1475 GMI_reservation_Util.PrintLn(' in Util v: item_no='||x_ic_item_mst_rec.item_no);
1476
1477 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1478 THEN
1479 GMI_RESERVATION_UTIL.PrintLn('Status get opm item from apps '|| x_return_status);
1480 GMI_reservation_Util.PrintLn(' in end of GMI_Reservation_Util.Get_Allocation_Record
1481 ERROR:Returned by Get_OPM_item_from_Apps.');
1482 FND_MESSAGE.Set_Name('GMI','GMI_OPM_ITEM');
1483 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_wdd_line.organization_id);
1484 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_wdd_line.inventory_item_id);
1485 FND_MSG_PUB.Add;
1486 RAISE FND_API.G_EXC_ERROR;
1487 ELSE
1488 x_allocation_rec.item_no := x_ic_item_mst_rec.item_no;
1489 END IF;
1490
1491 /*
1492 IN A NEXT VERSION : CUST_ID is going to be returned
1493 */
1494
1495 IF (x_ic_item_mst_rec.dualum_ind > 0) THEN
1496 x_allocation_rec.order_qty2 := p_wdd_line.requested_quantity2 - nvl(l_del_qty2,0);
1497 x_allocation_rec.order_um2 := x_ic_item_mst_rec.item_um2;
1498
1499 IF x_allocation_rec.order_qty1 <= 0 THEN
1500 x_allocation_rec.order_qty2 := 0;
1501 END IF;
1502 ELSE
1503 x_allocation_rec.order_qty2 := NULL;
1504 x_allocation_rec.order_um2 := NULL;
1505 END IF;
1506
1507 /* ============================================================================================= */
1508 /* Get User details not needed */
1509 /* ============================================================================================= */
1510
1511 GMI_reservation_Util.PrintLn(' Exiting Util Get_Allocation_Record:');
1512
1513 EXCEPTION
1514 WHEN FND_API.G_EXC_ERROR THEN
1515 GMI_Reservation_Util.PrintLn('Exiting Util Get_Allocation_Record: Error');
1516 x_return_status := FND_API.G_RET_STS_ERROR;
1517
1518 /* Get message count and data */
1519 FND_MSG_PUB.count_and_get
1520 ( p_count => x_msg_count
1521 , p_data => x_msg_data
1522 );
1523
1524 WHEN OTHERS THEN
1525 GMI_Reservation_Util.PrintLn('Exiting Util Get_Allocation_Record: ErrorOther');
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527
1528 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1529 , l_api_name
1530 );
1531
1532 /* Get message count and data */
1533 FND_MSG_PUB.count_and_get
1534 ( p_count => x_msg_count
1535 , p_data => x_msg_data
1536 );
1537
1538
1539 END Get_Allocation_Record;
1540
1541
1542 PROCEDURE Insert_Row (
1543 p_auto_alloc_batch_rec IN gmi_auto_allocation_batch%ROWTYPE )
1544 IS
1545 l_err_no NUMBER;
1546 l_err_msg VARCHAR2(100);
1547
1548 BEGIN
1549 INSERT INTO GMI_AUTO_ALLOCATION_BATCH (
1550 BATCH_ID
1551 ,NAME
1552 ,FROM_ORDER_HEADER_NO
1553 ,TO_ORDER_HEADER_NO
1554 ,FROM_SCHED_SHIP_DATE
1555 ,TO_SCHED_SHIP_DATE
1556 ,FROM_LOT_NO
1557 ,TO_LOT_NO
1558 ,FROM_SUBLOT_NO
1559 ,TO_SUBLOT_NO
1560 ,ORDER_TYPE_ID
1561 ,FROM_EXPIRATION_DATE
1562 ,TO_EXPIRATION_DATE
1563 ,FROM_CREATION_DATE
1564 ,TO_CREATION_DATE
1565 ,LOCATION
1566 ,ITEM_ID
1567 ,WHSE_CODE
1568 ,LOT_STATUS
1569 ,TO_DELIVERY_DETAIL_ID
1570 ,PICK_CONFIRM_FLAG
1571 ,ALLOC_ALL_LOT_FLAG
1572 ,LOTS_INDIVISIBLE_FLAG
1573 ,OVERRIDE_RULES
1574 ,DELETE_EXISTING_ALOC_FLAG
1575 ,ATTRIBUTE_CATEGORY
1576 ,ATTRIBUTE1
1577 ,ATTRIBUTE2
1578 ,ATTRIBUTE3
1579 ,ATTRIBUTE4
1580 ,ATTRIBUTE5
1581 ,ATTRIBUTE6
1582 ,ATTRIBUTE7
1583 ,ATTRIBUTE8
1584 ,ATTRIBUTE9
1585 ,ATTRIBUTE11
1586 ,ATTRIBUTE12
1587 ,ATTRIBUTE13
1588 ,ATTRIBUTE14
1589 ,ATTRIBUTE15
1590 ,CREATION_DATE
1591 ,CREATED_BY
1592 ,LAST_UPDATE_DATE
1593 ,LAST_UPDATED_BY
1594 ,LAST_UPDATE_LOGIN
1595 ,PROGRAM_APPLICATION_ID
1596 ,PROGRAM_ID
1597 ,PROGRAM_UPDATE_DATE )
1598
1599 VALUES (
1600 p_auto_alloc_batch_rec.BATCH_ID
1601 ,p_auto_alloc_batch_rec.NAME
1602 ,p_auto_alloc_batch_rec.FROM_ORDER_HEADER_NO
1603 ,p_auto_alloc_batch_rec.TO_ORDER_HEADER_NO
1604 ,p_auto_alloc_batch_rec.FROM_SCHED_SHIP_DATE
1605 ,p_auto_alloc_batch_rec.TO_SCHED_SHIP_DATE
1606 ,p_auto_alloc_batch_rec.FROM_LOT_NO
1607 ,p_auto_alloc_batch_rec.TO_LOT_NO
1608 ,p_auto_alloc_batch_rec.FROM_SUBLOT_NO
1609 ,p_auto_alloc_batch_rec.TO_SUBLOT_NO
1610 ,p_auto_alloc_batch_rec.ORDER_TYPE_ID
1611 ,p_auto_alloc_batch_rec.FROM_EXPIRATION_DATE
1612 ,p_auto_alloc_batch_rec.TO_EXPIRATION_DATE
1613 ,p_auto_alloc_batch_rec.FROM_CREATION_DATE
1614 ,p_auto_alloc_batch_rec.TO_CREATION_DATE
1615 ,p_auto_alloc_batch_rec.LOCATION
1616 ,p_auto_alloc_batch_rec.ITEM_ID
1617 ,p_auto_alloc_batch_rec.WHSE_CODE
1618 ,p_auto_alloc_batch_rec.LOT_STATUS
1619 ,p_auto_alloc_batch_rec.TO_DELIVERY_DETAIL_ID
1620 ,p_auto_alloc_batch_rec.PICK_CONFIRM_FLAG
1621 ,p_auto_alloc_batch_rec.ALLOC_ALL_LOT_FLAG
1622 ,p_auto_alloc_batch_rec.LOTS_INDIVISIBLE_FLAG
1623 ,p_auto_alloc_batch_rec.OVERRIDE_RULES
1624 ,p_auto_alloc_batch_rec.DELETE_EXISTING_ALOC_FLAG
1625 ,p_auto_alloc_batch_rec.ATTRIBUTE_CATEGORY
1626 ,p_auto_alloc_batch_rec.ATTRIBUTE1
1627 ,p_auto_alloc_batch_rec.ATTRIBUTE2
1628 ,p_auto_alloc_batch_rec.ATTRIBUTE3
1629 ,p_auto_alloc_batch_rec.ATTRIBUTE4
1630 ,p_auto_alloc_batch_rec.ATTRIBUTE5
1631 ,p_auto_alloc_batch_rec.ATTRIBUTE6
1632 ,p_auto_alloc_batch_rec.ATTRIBUTE7
1633 ,p_auto_alloc_batch_rec.ATTRIBUTE8
1634 ,p_auto_alloc_batch_rec.ATTRIBUTE9
1635 ,p_auto_alloc_batch_rec.ATTRIBUTE11
1636 ,p_auto_alloc_batch_rec.ATTRIBUTE12
1637 ,p_auto_alloc_batch_rec.ATTRIBUTE13
1638 ,p_auto_alloc_batch_rec.ATTRIBUTE14
1639 ,p_auto_alloc_batch_rec.ATTRIBUTE15
1640 ,p_auto_alloc_batch_rec.CREATION_DATE
1641 ,p_auto_alloc_batch_rec.CREATED_BY
1642 ,p_auto_alloc_batch_rec.LAST_UPDATE_DATE
1643 ,p_auto_alloc_batch_rec.LAST_UPDATED_BY
1644 ,p_auto_alloc_batch_rec.LAST_UPDATE_LOGIN
1645 ,p_auto_alloc_batch_rec.PROGRAM_APPLICATION_ID
1646 ,p_auto_alloc_batch_rec.PROGRAM_ID
1647 ,p_auto_alloc_batch_rec.PROGRAM_UPDATE_DATE);
1648
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651
1652 l_err_no :=SQLCODE;
1653 l_err_msg :=SUBSTR(SQLERRM,1 ,100);
1654
1655 WSH_UTIL_CORE.Println(' Line Insert Error => ' || l_err_no || l_err_msg);
1656 gmi_reservation_util.Println(' Line Insert Error => ' || l_err_no ||
1657 l_err_msg );
1658
1659 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1660 THEN
1661 FND_MSG_PUB.Add_Exc_Msg
1662 ( G_PKG_NAME
1663 , 'Insert_Row'
1664 );
1665 END IF;
1666
1667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1668
1669
1670 END Insert_Row;
1671
1672
1673 FUNCTION Submit_Allocation_Request(P_Batch_Id IN NUMBER)
1674 RETURN NUMBER IS
1675
1676 L_Request_Id NUMBER;
1677 l_msg VARCHAR2(2000);
1678 l_count NUMBER;
1679 l_status VARCHAR2(1) ;
1680 l_version NUMBER ;
1681
1682 BEGIN
1683 l_version := 1.0;
1684 GMI_RESERVATION_UTIL.println('before submit_request');
1685 l_Request_Id := FND_REQUEST.Submit_Request('GML','GMIALLOC','','',FALSE,1.0,
1686 FND_API.G_FALSE,FND_API.G_TRUE,p_batch_id);
1687 /*
1688
1689 '','','','','','','','','','',
1690 '','','','','','','','','','',
1691 '','','','','','','','','','',
1692 '','','','','','','','','','',
1693 '','','','','','','','','','',
1694 '','','','','','','','','','',
1695 '','','','','','','','','','',
1696 '','','','','','','','','','',
1697 '','','','','','','','','','',
1698 '','','','','','','','','','');
1699 */
1700 IF(l_request_id = 0 )THEN
1701 FND_MESSAGE.RETRIEVE(l_msg);
1702 GMI_RESERVATION_UTIL.println('l_msg is :'|| l_msg);
1703 END IF;
1704
1705 GMI_RESERVATION_UTIL.println('request_id is : '||l_request_id);
1706 Return L_Request_Id;
1707
1708 END Submit_allocation_request;
1709
1710
1711 END GMI_AUTO_ALLOC_BATCH_PKG;