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