[Home] [Help]
PACKAGE BODY: APPS.INV_MO_ADMIN_PUB
Source
1 PACKAGE BODY INV_MO_Admin_Pub AS
2 /* $Header: INVPMOAB.pls 120.5 2008/02/21 03:41:04 satkumar ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_MO_Admin_Pub';
7
8
9
10 /*
11 Procedure : Cancel Order
12
13 This procedure should cancel the order and associated lines for
14 the header Id provided, If the order is not already closed/cancelled
15 */
16
17 Procedure Cancel_Order(
18 p_api_version In Number,
19 p_init_msg_list In varchar2 ,
20 p_commit In varchar2 ,
21 p_validation_level In varchar2 ,
22 p_header_Id In Number,
23 x_msg_count Out Nocopy Number,
24 x_msg_data Out Nocopy varchar2,
25 x_return_status Out Nocopy Varchar2
26 ) IS
27
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_api_name CONSTANT VARCHAR2(30):= 'Cancel_Order';
30 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
31 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
32 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
33 l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
34 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
35 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
36 l_msg_count Number;
37 l_msg_data Varchar2(100);
38 l_header_id Number := p_header_Id;
39
40 BEGIN
41
42 -- Standard call to check compatibility
43 IF NOT FND_API.Compatible_API_Call ( l_api_version,
44 p_api_version,
45 l_api_name,
46 G_PKG_NAME ) THEN
47 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
48 END IF;
49
50 -- Initailize message list
51 If FND_API.To_Boolean( p_init_msg_list ) Then
52 FND_MSG_PUB.initialize;
53 End If;
54
55 -- API Body
56 INV_Move_Order_Pub.Get_Move_Order(
57 p_api_version_number => 1.0,
58 p_init_msg_list => FND_API.G_TRUE,
59 x_return_status => l_return_status,
60 x_msg_count => l_msg_count,
61 x_msg_data => l_msg_data,
62 p_header_id => l_header_Id,
63 x_trohdr_rec => l_trohdr_rec,
64 x_trohdr_val_rec => l_trohdr_val_rec,
65 x_trolin_tbl => l_trolin_tbl,
66 x_trolin_val_tbl => l_trolin_val_tbl);
67
68 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
69 IF l_return_status = FND_API.G_RET_STS_ERROR then
70 RAISE FND_API.G_EXC_ERROR;
71 END IF;
72 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75 END IF;
76 -- Header Status 5 is closed, 6 is canceled
77 IF ( l_trohdr_rec.header_status = 6 ) THEN
78 x_return_status := l_return_status;
79 Return;
80 END IF;
81
82 IF ( l_trohdr_rec.header_status = 5 ) THEN
83 FND_MESSAGE.SET_NAME('INV','INV_TO_HEADER_STATUS');
84 FND_MSG_PUB.ADD;
85 RAISE FND_API.G_EXC_ERROR;
86 END IF;
87
88 for I in 1..l_trolin_tbl.count loop
89 Cancel_Line(
90 p_api_version => 1.0,
91 p_line_id => l_trolin_tbl(I).Line_Id,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data,
94 x_return_status => l_return_status
95 );
96 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 elsif l_return_status = FND_API.G_RET_STS_ERROR then
99 RAISE FND_API.G_EXC_ERROR;
100 end if;
101 end loop;
102
103
104 -- Updating the who columns for bug 3277406
105 Update MTL_TXN_REQUEST_HEADERS
106 set header_status = 6,
107 last_updated_by = fnd_global.user_id,
108 last_update_login = fnd_global.login_id,
109 last_update_date = sysdate
110 where
111 header_id = l_header_id;
112
113 x_return_status := l_return_status;
114
115 -- Std check of p_commit
116 if FND_API.To_Boolean( p_commit ) then
117 COMMIT WORK;
118 end if;
119
120 -- Call to get msg count
121 FND_MSG_PUB.Count_And_Get(
122 p_count => x_msg_count,
123 p_data => x_msg_data ) ;
124
125 EXCEPTION
126 WHEN FND_API.G_EXC_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 FND_MSG_PUB.Count_And_Get(
129 p_count => x_msg_count,
130 p_data => x_msg_data) ;
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 FND_MSG_PUB.Count_And_Get(
134 p_count => x_msg_count,
135 p_data => x_msg_data) ;
136 WHEN OTHERS THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
139 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Cancel_Order');
140 END IF;
141 FND_MSG_PUB.Count_And_Get(
142 p_count => x_msg_count,
143 p_data => x_msg_data) ;
144 END CANCEL_ORDER;
145
146
147
148 /*
149 Procedure : Close Order
150
151 This procedure should close the order associated with header Id.
152 */
153
154 PROCEDURE Close_Order(
155 p_api_version In Number,
156 p_init_msg_list In varchar2 ,
157 p_commit In varchar2 ,
158 p_validation_level In varchar2 ,
159 p_header_Id In Number,
160 x_msg_count Out Nocopy Number,
161 x_msg_data Out Nocopy varchar2,
162 x_return_status Out Nocopy Varchar2
163 ) IS
164
165 l_api_version CONSTANT NUMBER := 1.0;
166 l_api_name CONSTANT VARCHAR2(30):= 'Close_Order';
167 l_header_id Number := p_header_id;
168 l_hdr_status Number;
169 l_request_number Varchar2(25) := NULL;
170 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
171 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
172 l_line_status NUMBER := 0;
173
174 BEGIN
175
176 -- Standard call to check compatibility
177 IF NOT FND_API.Compatible_API_Call ( l_api_version,
178 p_api_version,
179 l_api_name,
180 G_PKG_NAME ) THEN
181 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
182 END IF;
183
184 -- Initailize message list
185 If FND_API.To_Boolean( p_init_msg_list ) Then
186 FND_MSG_PUB.initialize;
187 End If;
188
189 -- API Body
190 BEGIN
191 SELECT header_status, request_number
192 INTO l_hdr_status, l_request_number
193 FROM mtl_txn_request_headers
194 WHERE Header_id = l_header_id;
195 EXCEPTION
196 WHEN No_Data_Found THEN
197 RAISE FND_API.G_EXC_ERROR;
198 WHEN OTHERS THEN
199 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Close Order');
200 RAISE FND_API.G_EXC_ERROR;
201 END;
202
203 IF ( l_hdr_status = 5 ) THEN
204 x_return_status := l_return_status;
205 Return;
206 END IF;
207
208 --Bug 3666433
209 IF ( l_hdr_status in (2,4,8)) THEN
210 Begin
211 SELECT 1
212 INTO l_line_status
213 FROM mtl_txn_request_lines
214 WHERE header_id = l_header_id
215 AND line_status in (1,2,8)
216 AND ROWNUM = 1;
217 Exception
218 WHEN NO_DATA_FOUND THEN
219 NULL;
220 End;
221 END IF;
222
223 IF ( l_hdr_status = 1 OR l_line_status = 1 ) THEN
224 FND_MESSAGE.SET_NAME('INV','INV_TO_HEADER_STATUS');
225 FND_MSG_PUB.ADD;
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228
229 l_trolin_tbl := Inv_Trolin_Util.Get_Lines( l_header_id );
230 For I in 1..l_trolin_tbl.count Loop
231 Close_Line(
232 p_api_version => 1.0,
233 p_line_id => l_trolin_tbl(I).Line_Id,
234 x_msg_count => x_msg_count,
235 x_msg_data => x_msg_data,
236 x_return_status => l_return_status
237 );
238 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
239 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242 IF l_return_status = FND_API.G_RET_STS_ERROR then
243 RAISE FND_API.G_EXC_ERROR;
244 end if;
245 END IF;
246 End Loop;
247
248
249 -- Updating the who columns for bug 3277406
250 Update MTL_TXN_REQUEST_HEADERS
251 set header_status = 5,
252 last_updated_by = fnd_global.user_id,
253 last_update_login = fnd_global.login_id,
254 last_update_date = sysdate
255 where
256 header_id = l_header_id;
257
258 x_return_status := l_return_status;
259
260 -- Std check of p_commit
261 if FND_API.To_Boolean( p_commit ) then
262 COMMIT WORK;
263 end if;
264
265 -- Call to get msg count and data
266 FND_MSG_PUB.Count_And_Get(
267 p_count => x_msg_count,
268 p_data => x_msg_data ) ;
269
270 EXCEPTION
271 WHEN FND_API.G_EXC_ERROR THEN
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get(
274 p_count => x_msg_count,
275 p_data => x_msg_data) ;
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 FND_MSG_PUB.Count_And_Get(
279 p_count => x_msg_count,
280 p_data => x_msg_data) ;
281 WHEN OTHERS THEN
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
284 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Close_Order');
285 END IF;
286 FND_MSG_PUB.Count_And_Get(
287 p_count => x_msg_count,
288 p_data => x_msg_data) ;
289 END CLOSE_ORDER;
290
291
292
293 /*
294 Procedure : Purge Order
295
296 This procedure should purge the order associated with header Id.
297 */
298
299 PROCEDURE Purge_Order(
300 p_api_version In Number,
301 p_init_msg_list In varchar2 ,
302 p_commit In varchar2 ,
303 p_validation_level In varchar2 ,
304 p_header_Id In Number,
305 x_msg_count Out Nocopy Number,
306 x_msg_data Out Nocopy varchar2,
307 x_return_status Out Nocopy Varchar2
308 ) IS
309
310 l_api_version CONSTANT NUMBER := 1.0;
311 l_api_name CONSTANT VARCHAR2(30):= 'Purge_Order';
312 l_header_id Number := p_header_id;
313 l_hdr_status Number;
314 l_request_number Varchar2(25) := NULL;
315 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
316
317 BEGIN
318
319 -- Standard call to check compatibility
320 IF NOT FND_API.Compatible_API_Call ( l_api_version,
321 p_api_version,
322 l_api_name,
323 G_PKG_NAME ) THEN
324 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 -- Initailize message list
328 If FND_API.To_Boolean( p_init_msg_list ) Then
329 FND_MSG_PUB.initialize;
330 End If;
331
332 -- API Body
333 Begin
334 Select header_status, request_number
335 into l_hdr_status, l_request_number
336 from mtl_txn_request_headers
337 where
338 Header_id = l_header_id;
339 Exception
340 When No_Data_Found Then
341 RAISE FND_API.G_EXC_ERROR;
342 When OTHERS then
343 FND_MSG_PUB.Add_Exc_Msg
344 ( G_PKG_NAME
345 , 'purge Order'
346 );
347
348 RAISE FND_API.G_EXC_ERROR;
349 End;
350
351 IF ( l_hdr_status <> 5 ) THEN
352 FND_MESSAGE.SET_NAME('INV','INV_TO_HEADER_STATUS');
353 FND_MSG_PUB.ADD;
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 Delete MTL_TXN_REQUEST_LINES
358 where
359 header_id = l_header_id;
360
361 Delete MTL_TXN_REQUEST_HEADERS
362 where
363 header_id = l_header_id;
364
365 x_return_status := l_return_status;
366
367
368 -- Std check of p_commit
369 if FND_API.To_Boolean( p_commit ) then
370 COMMIT WORK;
371 end if;
372
373 -- Call to get msg count and data
374 FND_MSG_PUB.Count_And_Get(
375 p_count => x_msg_count,
376 p_data => x_msg_data ) ;
377
378 Exception
379 WHEN FND_API.G_EXC_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MSG_PUB.Count_And_Get(
382 p_count => x_msg_count,
383 p_data => x_msg_data ) ;
384
385
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389 FND_MSG_PUB.Count_And_Get(
390 p_count => x_msg_count,
391 p_data => x_msg_data ) ;
392
393
394 WHEN OTHERS THEN
395 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
396 THEN
397 FND_MSG_PUB.Add_Exc_Msg
398 ( G_PKG_NAME
399 , 'Purge_Order'
400 );
401 END IF;
402 FND_MSG_PUB.Count_And_Get(
403 p_count => x_msg_count,
404 p_data => x_msg_data ) ;
405
406 END PURGE_ORDER;
407
408
409 /*
410 Procedure : Cancel Line
411
412 This procedure should Cancel the Line associated with Line Id.
413 */
414
415 PROCEDURE Cancel_Line(
416 p_api_version In Number,
417 p_init_msg_list In varchar2 ,
418 p_commit In varchar2 ,
419 p_validation_level In varchar2 ,
420 p_line_id In Number,
421 x_msg_count Out Nocopy Number,
422 x_msg_data Out Nocopy varchar2,
423 x_return_status Out Nocopy Varchar2
424 ) IS
425
426 l_api_version CONSTANT NUMBER := 1.0;
427 l_api_name CONSTANT VARCHAR2(30):= 'Cancel_Line';
428 l_Line_Id Number := p_line_Id;
429 l_line_status Number;
430 l_request_number Varchar2(25) := NULL;
431 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
432 l_old_trolin_tbl INV_Move_Order_PUB.Trolin_tbl_Type;
433 l_new_trolin_tbl INV_Move_Order_PUB.Trolin_tbl_Type;
434 l_x_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
435 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
436 l_old_trolin_rec INV_Move_Order_PUB.Trolin_rec_Type;
437 l_msg_count Number;
438 l_msg_data Varchar2(100);
439 l_qty_del Number;
440 l_mo_type Number;
441 l_loaded_lpn_exists Number; --Added bug 3254130
442 l_delete_mmtt Varchar2(3); --Added bug 3524130
443 l_org_id Number;
444 l_wms_org_flag Boolean;
445
446 BEGIN
447
448 -- Standard call to check compatibility
449 IF NOT FND_API.Compatible_API_Call ( l_api_version,
450 p_api_version,
451 l_api_name,
452 G_PKG_NAME ) THEN
453 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456 -- Initailize message list
457 If FND_API.To_Boolean( p_init_msg_list ) Then
458 FND_MSG_PUB.initialize;
459 End If;
460
461 -- API Body
462 Begin
463 --Bug 4417695
464 --Need to lock the record while updating the MOL since any other form
465 --should not hang and should show the correct exception.
466 SELECT line_status, quantity_delivered, move_order_type, organization_id
467 INTO l_line_status, l_qty_del, l_mo_type, l_org_id
468 FROM mtl_txn_request_lines_v
469 WHERE Line_Id = l_Line_Id
470 FOR UPDATE NOWAIT;
471 EXCEPTION
472 WHEN No_Data_Found THEN
473 RAISE FND_API.G_EXC_ERROR;
474 WHEN app_exceptions.record_lock_exception THEN
475 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
476 fnd_message.set_name('ONT', 'OE_LOCK_ROW_ALREADY_LOCKED');
477 fnd_msg_pub.ADD;
478 END IF;
479 RAISE FND_API.G_EXC_ERROR;
480 WHEN OTHERS THEN
481 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Cancel Line');
482 RAISE FND_API.G_EXC_ERROR;
483 END;
484
485
486 IF ( l_Line_status = 6 ) THEN
487 x_return_status := l_return_status ;
488 Return;
489 END IF;
490
491 IF ( l_Line_status = 5 ) THEN
492 FND_MESSAGE.SET_NAME('INV','INV_TO_HEADER_STATUS');
493 FND_MSG_PUB.ADD;
494 RAISE FND_API.G_EXC_ERROR;
495 END IF;
496
497 IF ( l_mo_type <> 3 ) then /* 3 - pickwave type MO */
498 IF ( NVL(l_qty_del,0) <> 0 ) then
499
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502 END IF;
503
504 l_old_trolin_rec := Inv_Trolin_Util.Query_Row( l_line_id );
505
506 l_old_trolin_tbl(1) := l_old_trolin_rec;
507 l_new_trolin_tbl(1) := l_old_trolin_rec;
508
509 /*Added bug 3524130*/
510 l_loaded_lpn_exists := 0;
511 l_wms_org_flag := inv_install.adv_inv_installed
512 (p_organization_id => l_org_id);
513 IF ( l_wms_org_flag ) THEN
514 Begin
515 select 1
516 into l_loaded_lpn_exists
517 from mtl_material_transactions_temp mmtt,wms_dispatched_tasks wdt
518 where mmtt.transaction_temp_id = wdt.transaction_temp_id
519 and wdt.status = 4
520 and mmtt.transfer_lpn_id is not null
521 and mmtt.move_order_line_id = l_line_id
522 and rownum = 1;
523
524 if l_loaded_lpn_exists = 1 then
525 l_new_trolin_tbl(1).line_status := 9;
526 l_new_trolin_tbl(1).status_date := sysdate; --bug 5053725
527 l_delete_mmtt := 'NO';
528 else
529 l_new_trolin_tbl(1).line_status := 6;
530 l_new_trolin_tbl(1).status_date := sysdate; --bug 5053725
531 l_delete_mmtt := 'YES';
532 end if;
533 --Bug3640116
534 Exception
535 When No_Data_Found Then
536 l_new_trolin_tbl(1).line_status := 6;
537 l_new_trolin_tbl(1).status_date := sysdate; --bug 5053725
538 l_delete_mmtt := 'YES';
539 When OTHERS then
540 FND_MSG_PUB.Add_Exc_Msg
541 ( G_PKG_NAME
542 , 'Cancel Line'
543 );
544
545 RAISE FND_API.G_EXC_ERROR;
546 End;
547 ELSE
548 l_new_trolin_tbl(1).line_status := 6;
549 l_new_trolin_tbl(1).status_date := sysdate; --bug 5053725
550 l_delete_mmtt := 'YES';
551 END IF;
552
553 /*Bug fix3524130 ends*/
554 l_new_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_UPDATE;
555
556 /** call update line API **/
557 INV_Transfer_Order_PVT.Process_Transfer_Order
558 ( p_api_version_number => 1.0 ,
559 p_init_msg_list => FND_API.G_TRUE,
560 x_return_status => l_return_status,
561 x_msg_count => l_msg_count,
562 x_msg_data => l_msg_data,
563 p_trolin_tbl => l_new_trolin_tbl,
564 p_old_trolin_tbl => l_old_trolin_tbl,
565 x_trohdr_rec => l_x_trohdr_rec,
566 x_trolin_tbl => l_x_trolin_tbl,
567 p_delete_mmtt => l_delete_mmtt --Added bug3524130
568 );
569
570 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572 elsif l_return_status = FND_API.G_RET_STS_ERROR then
573 RAISE FND_API.G_EXC_ERROR;
574 end if;
575
576 x_return_status := l_return_status;
577
578
579 -- Std check of p_commit
580 if FND_API.To_Boolean( p_commit ) then
581 COMMIT WORK;
582 end if;
583
584 -- Call to get msg count and data
585 FND_MSG_PUB.Count_And_Get(
586 p_count => x_msg_count,
587 p_data => x_msg_data ) ;
588
589 EXCEPTION
590 WHEN FND_API.G_EXC_ERROR THEN
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 FND_MSG_PUB.Count_And_Get(
593 p_count => x_msg_count,
594 p_data => x_msg_data) ;
595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
597 FND_MSG_PUB.Count_And_Get(
598 p_count => x_msg_count,
599 p_data => x_msg_data) ;
600 WHEN OTHERS THEN
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
603 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Cancel line');
604 END IF;
605 FND_MSG_PUB.Count_And_Get(
606 p_count => x_msg_count,
607 p_data => x_msg_data ) ;
608 END CANCEL_LINE;
609
610
611
612
613 /*
614 Procedure : Close Line
615
616 This procedure should close the Line associated with Line Id.
617 */
618
619 PROCEDURE Close_Line(
620 p_api_version In Number,
621 p_init_msg_list In varchar2 ,
622 p_commit In varchar2 ,
623 p_validation_level In varchar2 ,
624 p_line_id In Number,
625 x_msg_count Out Nocopy Number,
626 x_msg_data Out Nocopy varchar2,
627 x_return_status Out Nocopy Varchar2
628 ) IS
629
630 l_api_version CONSTANT NUMBER := 1.0;
631 l_api_name CONSTANT VARCHAR2(30):= 'Close_Line';
632 l_Line_Id Number := p_line_Id;
633 l_line_status Number;
634 l_qty_del Number;
635 l_qty Number;
636 l_request_number Varchar2(25) := NULL;
637 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
638 l_loaded_lpn_exists Number; --Added bug3524130
639 l_org_id Number;
640 l_wms_org_flag Boolean;
641 l_wrd_exists NUMBER := 0;
642
643 BEGIN
644
645 -- Standard call to check compatibility
646 IF NOT FND_API.Compatible_API_Call ( l_api_version,
647 p_api_version,
648 l_api_name,
649 G_PKG_NAME ) THEN
650 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653 -- Initailize message list
654 If FND_API.To_Boolean( p_init_msg_list ) Then
655 FND_MSG_PUB.initialize;
656 End If;
657
658 -- API Body
659 BEGIN
660 --Bug 4417695
661 --Need to lock the record while updating the MOL since any other form
662 --should not hang and should show the correct exception.
663 SELECT line_status, quantity_delivered, quantity ,organization_id
664 INTO l_line_status, l_qty_del, l_qty, l_org_id
665 FROM mtl_txn_request_lines
666 WHERE Line_Id = l_Line_Id
667 FOR UPDATE NOWAIT;
668 EXCEPTION
669 WHEN No_Data_Found THEN
670 RAISE FND_API.G_EXC_ERROR;
671 WHEN app_exceptions.record_lock_exception THEN
672 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
673 fnd_message.set_name('ONT', 'OE_LOCK_ROW_ALREADY_LOCKED');
674 fnd_msg_pub.ADD;
675 END IF;
676 RAISE FND_API.G_EXC_ERROR;
677 WHEN OTHERS THEN
678 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Close Line');
679 RAISE FND_API.G_EXC_ERROR;
680 END;
681
682 IF ( l_Line_status = 5 ) THEN
683 x_return_status := l_return_status ;
684 Return;
685 END IF;
686
687
688 /*IF ( l_line_status in ( 3, 7 ) ) then
689 if ( nvl(l_qty_del, 0) < l_qty ) then
690 RAISE FND_API.G_EXC_ERROR;
691 end if;
692 END IF; */
693
694 IF ( l_Line_status not in (3,4,6,7,9) ) THEN
695 FND_MESSAGE.SET_NAME('INV','INV_TO_HEADER_STATUS');
696 FND_MSG_PUB.ADD;
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 /*Added bug3524130*/
701 l_loaded_lpn_exists := 0;
702
703 l_wms_org_flag := inv_install.adv_inv_installed
704 (p_organization_id => l_org_id);
705 IF ( l_wms_org_flag ) THEN
706
707 -- For R12.1 Replenishment Project (6808839/6751117) STARTS --
708 -- We can not close the replenishment MO if these are associated with
709 -- delivery details for their replenishment, EVEN if Fill Kill
710 -- profile IS SET TO YES
711 -- For Fill Kill, this proc gets called from WMSTASKB.pls WMSTRSAB.pls INVVTROB.pls
712 BEGIN
713 SELECT 1 INTO l_wrd_exists FROM dual WHERE exists
714 (SELECT 1
715 FROM wms_replenishment_details wrd
716 WHERE wrd.source_line_id = l_line_id);
717 EXCEPTION
718 WHEN OTHERS THEN
719 l_wrd_exists := 0;
720 END;
721
722 IF l_wrd_exists = 1 THEN
723 RETURN;
724 END IF;
725 -- For R12.1 Replenishment Project (6808839/6751117) ENDS --
726
727 BEGIN
728 select 1
729 into l_loaded_lpn_exists
730 from mtl_material_transactions_temp mmtt,wms_dispatched_tasks wdt
731 where mmtt.transaction_temp_id = wdt.transaction_temp_id
732 and wdt.status = 4
733 and mmtt.transfer_lpn_id is not null
734 and mmtt.move_order_line_id = l_line_id
735 and rownum = 1;
736 EXCEPTION
737 When No_Data_Found Then
738 l_loaded_lpn_exists := 0;
739 When OTHERS then
740 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
741 END;
742 END IF; -- ( l_wms_org_flag ) THEN
743
744
745 IF l_loaded_lpn_exists = 1 then
746
747 Update MTL_TXN_REQUEST_LINES
748 set line_status = 9,
749 last_updated_by = fnd_global.user_id,
750 last_update_login = fnd_global.login_id,
751 last_update_date = sysdate,
752 status_date = SYSDATE --bug 5053725
753 where line_id = l_line_id;
754
755 else /*bug fix3524130 ends*/
756 Begin
757 Delete MTL_MATERIAL_TRANSACTIONS_TEMP
758 Where
759 Move_Order_Line_Id = l_line_id;
760 Exception
761 when NO_DATA_FOUND then
762 Null;
763 when OTHERS then
764 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
765 END;
766
767
768 -- Updating the who columns for bug 3277406
769 Update MTL_TXN_REQUEST_LINES
770 set line_status = 5,
771 last_updated_by = fnd_global.user_id,
772 last_update_login = fnd_global.login_id,
773 last_update_date = sysdate,
774 status_date = SYSDATE --bug 5053725
775 where
776 line_id = l_line_id;
777 end if;
778
779 x_return_status := l_return_status;
780
781
782 -- Std check of p_commit
783 if FND_API.To_Boolean( p_commit ) then
784 COMMIT WORK;
785 end if;
786
787 -- Call to get msg coount and data
788 FND_MSG_PUB.Count_And_Get(
789 p_count => x_msg_count,
790 p_data => x_msg_data ) ;
791
792 Exception
793 WHEN FND_API.G_EXC_ERROR THEN
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 FND_MSG_PUB.Count_And_Get(
796 p_count => x_msg_count,
797 p_data => x_msg_data ) ;
798
799 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801 FND_MSG_PUB.Count_And_Get(
802 p_count => x_msg_count,
803 p_data => x_msg_data ) ;
804 WHEN OTHERS THEN
805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
806 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
807 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Close line');
808 END IF;
809 FND_MSG_PUB.Count_And_Get(
810 p_count => x_msg_count,
811 p_data => x_msg_data ) ;
812 END CLOSE_LINE;
813
814
815 END INV_MO_Admin_Pub;