[Home] [Help]
PACKAGE BODY: APPS.INV_PICK_WAVE_PICK_CONFIRM_PUB
Source
1 PACKAGE BODY INV_Pick_Wave_Pick_Confirm_PUB AS
2 /* $Header: INVPCPWB.pls 120.1.12010000.2 2008/07/29 13:42:32 ptkumar ship $ */
3
4 debug_mode boolean DEFAULT TRUE;
5
6 procedure TraceLog(err_msg IN VARCHAR2, module IN VARCHAR2, p_level IN NUMBER := 9) is
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 begin
9 -- Call trace methoud from trx util.
10 IF (l_debug = 1) THEN
11 INV_LOG_UTIL.TRACE(err_msg, module, p_level);
12 END IF;
13 end TraceLog;
14
15 PROCEDURE Pick_Confirm
16 (
17 p_api_version_number IN NUMBER
18 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
19 , p_commit IN VARCHAR2 := FND_API.G_FALSE
20 , x_return_status OUT NOCOPY VARCHAR2
21 , x_msg_count OUT NOCOPY NUMBER
22 , x_msg_data OUT NOCOPY VARCHAR2
23 , p_move_order_type IN NUMBER
24 , p_transaction_mode IN NUMBER
25 , p_trolin_tbl IN INV_Move_Order_PUB.Trolin_Tbl_Type
26 , p_mold_tbl IN INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type
27 , x_mmtt_tbl IN OUT NOCOPY INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type
28 , x_trolin_tbl IN OUT NOCOPY INV_Move_Order_PUB.Trolin_Tbl_Type
29 , p_transaction_date IN DATE DEFAULT NULL
30 ) IS
31 l_api_version_number NUMBER := 1.0;
32 l_api_name VARCHAR2(80) := 'INV_PICK_WAVE_PICK_CONFIRM_PUB';
33 l_move_order_type NUMBER := p_move_order_type;
34 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type := p_trolin_tbl;
35 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
36 l_mold_tbl INV_MO_Line_Detail_Util.g_mmtt_Tbl_type := p_mold_tbl;
37 l_return_status VARCHAR2(1);
38 l_sum_trx_qty NUMBER := 0;
39 l_qty_delivered NUMBER := 0;
40 l_qty_detailed NUMBER := 0;
41 l_transaction_header_id NUMBER;
42 l_success NUMBER;
43 lot_success VARCHAR2(50);
44 l_msg_data VARCHAR2(20000);
45 l_msg_count NUMBER;
46 l_no_violation BOOLEAN;
47 p_timeout NUMBER := null;
48 l_rc_field NUMBER;
49 -- variable related to fnd_synchronous
50 l_func varchar2(240);
51 l_program VARCHAR2(240);
52 l_args VARCHAR2(240);
53 rtvl NUMBER;
54 resp_appl_id NUMBER;
55 resp_id NUMBER;
56 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
57 l_source_header_id NUMBER;
58 l_source_line_id NUMBER;
59 l_delivery_detail_id NUMBER;
60 l_quantity_reserved NUMBER;
61 l_message VARCHAR2(2000);
62 l_released_status VARCHAR2(1);
63 l_customer_item_id NUMBER;
64 l_subinventory VARCHAR2(20);
65 l_locator_id NUMBER;
66 l_lot_count NUMBER;
67 i NUMBER;
68 l_organization_id NUMBER; -- Added for bug 1992880
69 l_open_past_period BOOLEAN; -- Added for bug 1992880
70 l_period_id INTEGER; -- Added for bug 1992880
71 l_transaction_date DATE := NULL; -- Added for bug 1992880
72 l_old_tm_success BOOLEAN ; --Bug 2997177
73 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
74 l_proc_mode number := NVL(FND_PROFILE.VALUE('AUTO_PICK_CONFIRM_TXN'),1);
75 BEGIN
76
77 savepoint TO_TRX_LINE_SAVE;
78 -- Standard call to check for call compatibility
79 IF (l_debug = 1) THEN
80 TraceLog('Inside pick_wave_pick_confirm', 'Pick_confirm');
81 END IF;
82 IF NOT FND_API.Compatible_API_Call
83 ( l_api_version_number
84 , p_api_version_number
85 , l_api_name
86 , G_PKG_NAME
87 )
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 l_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- bug 2307057 Initialize message list if p_init_msg_list is set to TRUE.
95 IF FND_API.to_Boolean( p_init_msg_list ) THEN
96 FND_MSG_PUB.initialize;
97 END IF;
98
99
100 -- if( l_trolin_tbl.count = 0 OR (l_trolin_tbl.count = 0 AND
101 -- l_mold_tbl.count = 0)) then
102 -- AS:
103 -- Commented out the previous line and added the one below.
104 -- User should be able to pass either the mol or the mmtt line
105 -- Only if both are zero should it error out
106
107 if( l_trolin_tbl.count = 0 AND l_mold_tbl.count = 0) then
108
109 FND_MESSAGE.SET_NAME('INV', 'INV_NO_LINES_TO_PICK_CONFIRM');
110 FND_MSG_PUB.add;
111 l_return_status := FND_API.G_RET_STS_ERROR;
112 raise FND_API.G_EXC_ERROR;
113 end if;
114
115 select mtl_material_transactions_s.nextval
116 into l_transaction_header_id
117 from dual;
118 IF (l_debug = 1) THEN
119 TraceLog('transaction_header_id = ' || l_transaction_header_id, 'Pick_Confirm');
120 END IF;
121
122 if( l_trolin_tbl.count <> 0 ) then
123 -- if the table of mol is passed
124 IF (l_debug = 1) THEN
125 TraceLog('move orderline = ' || l_trolin_tbl.count, 'Pick_Confirm');
126 END IF;
127 i := l_trolin_tbl.FIRST;
128 while i is not null LOOP
129 IF (l_debug = 1) THEN
130 TraceLog('mo line_id is = ' || l_trolin_tbl(i).line_id, 'Pick_Confirm');
131 END IF;
132 -- only process the valid move order, fix bug 1540709.
133 IF (l_trolin_tbl(i).return_status <> FND_API.G_RET_STS_UNEXP_ERROR and
134 l_trolin_tbl(i).return_status <> FND_API.G_RET_STS_ERROR) THEN
135
136 --if( l_trolin_tbl(i) is not null ) then
137 l_trolin_rec := INV_Trolin_Util.Query_Row(l_trolin_tbl(i).line_id);
138 l_trolin_tbl(i) := l_trolin_rec;
139 l_qty_detailed := l_trolin_tbl(i).quantity_detailed;
140 l_qty_delivered := nvl(l_trolin_tbl(i).quantity_delivered, 0);
141 IF (l_debug = 1) THEN
142 TraceLog(to_char(i) || ' ' || l_trolin_rec.line_id || ' l_qty_detailed = ' || l_qty_detailed, 'Pick_Confirm');
143 TraceLog(to_char(i) || ' ' || l_trolin_rec.line_id || ' l_qty_delivered = ' || l_qty_delivered, 'Pick_Confirm');
144 END IF;
145 if( L_qty_detailed = l_qty_delivered OR l_qty_detailed = 0 ) then
146 FND_MESSAGE.SET_NAME('INV', 'INV_PICK_QTY_ERROR');
147 FND_MSG_PUB.ADD;
148 --rollback to TO_TRX_LINE_SAVE;
149 --raise FND_API.G_EXC_ERROR;
150 else
151 l_mold_tbl := INV_MO_LINE_DETAIL_UTIL.Query_Rows( p_line_id => l_trolin_tbl(i).line_id);
152 IF (l_debug = 1) THEN
153 TraceLog('mold records = ' || l_mold_tbl.count, 'Pick_Confirm');
154 END IF;
155 --end if;
156 --TraceLog('mold records = ' || l_mold_tbl.count);
157 for j in 1..l_mold_tbl.count LOOP
158 l_mold_tbl(j).transaction_status := 3;
159 l_mold_tbl(j).transaction_mode := p_transaction_mode;
160 l_mold_tbl(j).transaction_header_id := l_transaction_header_id;
161 --l_mold_tbl(j).transaction_source_id := l_trolin_tbl(i).header_id;
162 l_mold_tbl(j).source_line_id := l_trolin_tbl(i).line_id;
163
164
165 -- Bug 1992880 : Added check to check the account period ID and it is
166 -- -1 error out or else update the MMTT with this account period ID -
167 -- vipartha
168
169
170 IF l_transaction_date IS NULL OR l_transaction_date
171 <> l_mold_tbl(j).transaction_date THEN
172
173 -- Bug 3380018 while transacting mo via API transaction_date should be user defined.
174 IF p_transaction_date IS NOT NULL THEN
175 TraceLog('p_transaction_date: '||p_transaction_date, 'Pick_confirm');
176 IF p_transaction_date > sysdate THEN
177 TraceLog('Error: Transaction date cannot be a future date', 'Pick_confirm');
178 FND_MESSAGE.SET_NAME('INV', 'INV_INT_TDATEEX');
179 FND_MSG_PUB.add;
180 raise FND_API.G_EXC_ERROR;
181 END IF;
182 l_mold_tbl(j).transaction_date := p_transaction_date;
183 END IF;
184
185 l_transaction_date := l_mold_tbl(j).transaction_date;
186 l_organization_id := l_mold_tbl(j).organization_id;
187
188 invttmtx.tdatechk (
189 org_id => l_organization_id,
190 transaction_date => l_transaction_date,
191 period_id => l_period_id,
192 open_past_period =>
193 l_open_past_period);
194
195
196 TraceLog('l_period_id: '||l_period_id, 'Pick_confirm');
197 IF (l_period_id = -1 or l_period_id = 0) THEN
198 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
199 FND_MSG_PUB.add;
200 l_return_status := FND_API.G_RET_STS_ERROR;
201 raise FND_API.G_EXC_ERROR;
202 END IF;
203
204 END IF;
205 l_mold_tbl(j).acct_period_id := l_period_id;
206 -- End Bug 1992880;
207
208 select count(transaction_temp_id)
209 into l_lot_count
210 from mtl_transaction_lots_temp
211 where transaction_temp_id = l_mold_tbl(j).transaction_temp_id;
212 IF (l_debug = 1) THEN
213 TraceLog('l_lot_count is before lot_handling ' || l_lot_count, 'Pick_Confirm');
214 END IF;
215
216 if( l_lot_count > 0 and l_mold_tbl(j).lot_number is not null ) then
217 l_mold_tbl(j).lot_number := null;
218 end if;
219 IF (l_debug = 1) THEN
220 TraceLog('mold pick slip number is ' || l_mold_tbl(j).pick_slip_number, 'Pick_Confirm');
221 END IF;
222 inv_mo_line_detail_util.update_row(l_return_status, l_mold_tbl(j));
223 IF (l_debug = 1) THEN
224 TraceLog('after update transaction_status = 3', 'Pick_Confirm');
225 END IF;
226 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
227 fnd_message.set_name('INV', 'INV_COULD_NOT_UPATE_RECORD');
228 fnd_msg_pub.add;
229 IF (l_debug = 1) THEN
230 TraceLog('error in update mold', 'Pick_Confirm');
231 END IF;
232 rollback to TO_TRX_LINE_SAVE;
233 raise FND_API.G_EXC_ERROR;
234 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
235 IF (l_debug = 1) THEN
236 TraceLog('error in update mold', 'Pick_Confirm');
237 END IF;
238 fnd_message.set_name('INV', 'INV_COULD_NOT_UPATE_RECORD');
239 fnd_msg_pub.add;
240 rollback to TO_TRX_LINE_SAVE;
241 raise FND_API.G_EXC_UNEXPECTED_ERROR;
242 end if;
243 IF (l_debug = 1) THEN
244 TraceLog('end of loop of mold, still inside loop', 'Pick Confirm');
245 END IF;
246 end loop; -- mold loop
247 IF (l_debug = 1) THEN
248 TraceLog('end of loop of mold', 'Pick Confirm');
249 END IF;
250 l_trolin_tbl(i).transaction_header_id := l_transaction_header_id;
251 -- obtain program and user info
252
253 l_trolin_tbl(i).last_update_date := SYSDATE;
254
255 l_trolin_tbl(i).last_update_login := fnd_global.login_id;
256
257 if l_trolin_tbl(i).last_update_login = -1 THEN
258 l_trolin_tbl(i).last_update_login :=
259 fnd_global.conc_login_id;
260 end if;
261 l_trolin_tbl(i).last_updated_by := fnd_global.user_id;
262 l_trolin_tbl(i).program_id := fnd_global.conc_program_id;
263 l_trolin_tbl(i).program_update_date := SYSDATE;
264 l_trolin_tbl(i).request_id := fnd_global.conc_request_id;
265 l_trolin_tbl(i).program_application_id :=
266 fnd_global.prog_appl_id;
267
268 IF (l_debug = 1) THEN
269 TraceLog('calling update_row of trolin', 'Pick Confirm');
270 END IF;
271 inv_trolin_util.update_row(l_trolin_tbl(i));
272 IF (l_debug = 1) THEN
273 TraceLog('after calling update_row of trolin', 'Pick Confirm');
274 END IF;
275 end if;
276 END IF;
277 i := l_trolin_tbl.NEXT(i);
278 end loop; -- trolin loop
279 else
280 -- if only line detail records is passed
281 If( l_mold_tbl.count <> 0 ) then
282 for j in 1..l_mold_tbl.count LOOP
283 --if( l_mold_tbl(j) is not null ) then
284 l_trolin_tbl(1) := inv_trolin_util.query_row(p_line_id => l_mold_tbl(j).move_order_line_id);
285
286 l_qty_detailed := l_trolin_tbl(1).quantity_detailed;
287 l_qtY_delivered := l_trolin_tbl(1).quantity_delivered;
288 if( l_qty_detailed = l_qty_delivered OR l_qty_detailed = 0 ) then
289 FND_MESSAGE.SET_NAME('INV', 'INV_PICK_QTY_ERROR');
290 FND_MSG_PUB.ADD;
291 rollback to TO_TRX_LINE_SAVE;
292 raise FND_API.G_EXC_ERROR;
293 end if;
294 l_mold_tbl(j).transaction_status := 3;
295 l_mold_tbl(j).transaction_mode := p_transaction_mode;
296 l_mold_tbl(j).transaction_header_id := l_transaction_header_id;
297
298 -- Bug 1992880 : Added check to chekc the account period ID and it is
299 -- -1 error out or else update the MMTT with this account period ID -
300 -- vipartha
301
302 IF l_transaction_date IS NULL OR l_transaction_date
303 <> l_mold_tbl(j).transaction_date THEN
304
305 -- Bug 3380018 while transacting mo via API transaction_date should be user defined.
306 IF p_transaction_date IS NOT NULL THEN
307 TraceLog('p_transaction_date: '||p_transaction_date, 'Pick_confirm');
308 IF p_transaction_date > sysdate THEN
309 TraceLog('Error: Transaction date cannot be a future date', 'Pick_confirm');
310 FND_MESSAGE.SET_NAME('INV', 'INV_INT_TDATEEX');
311 FND_MSG_PUB.add;
312 raise FND_API.G_EXC_ERROR;
313 END IF;
314 l_mold_tbl(j).transaction_date := p_transaction_date;
315 END IF;
316
317 l_transaction_date := l_mold_tbl(j).transaction_date;
318 l_organization_id := l_mold_tbl(j).organization_id;
319
320 invttmtx.tdatechk (
321 org_id => l_organization_id,
322 transaction_date => l_transaction_date,
323 period_id => l_period_id,
324 open_past_period => l_open_past_period);
325
326 TraceLog('l_period_id: '||l_period_id, 'Pick_confirm');
327 IF (l_period_id = -1 or l_period_id = 0) THEN
328 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
329 FND_MSG_PUB.add;
330 l_return_status := FND_API.G_RET_STS_ERROR;
331 raise FND_API.G_EXC_ERROR;
332 END IF;
333
334 END IF;
335
336 l_mold_tbl(j).acct_period_id := l_period_id;
337 -- End Bug 1992880;
338
339 inv_mo_line_detail_util.update_row(l_return_status, l_mold_tbl(j));
340 IF (l_debug = 1) THEN
341 TraceLog('after update transaction_status = 3', 'Pick_Confirm');
342 END IF;
343 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
344 IF (l_debug = 1) THEN
345 TraceLog('error in update mold', 'Pick_Confirm');
346 END IF;
347 fnd_message.set_name('INV', 'INV_COULD_NOT_UPATE_RECORD');
348 fnd_msg_pub.add;
349 rollback to TO_TRX_LINE_SAVE;
350 raise FND_API.G_EXC_ERROR;
351 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
352 IF (l_debug = 1) THEN
353 TraceLog('error in update mold', 'Pick_Confirm');
354 END IF;
355 fnd_message.set_name('INV', 'INV_COULD_NOT_UPATE_RECORD');
356 fnd_msg_pub.add;
357 rollback to TO_TRX_LINE_SAVE;
358 raise FND_API.G_EXC_UNEXPECTED_ERROR;
359 end if;
360 l_trolin_tbl(1).transaction_header_id :=
361 l_transaction_header_id;
362
363 -- obtain program and user info
364
365 l_trolin_tbl(1).last_update_date := SYSDATE;
366
367 l_trolin_tbl(1).last_update_login := fnd_global.login_id;
368
369 if l_trolin_tbl(1).last_update_login = -1 THEN
370 l_trolin_tbl(1).last_update_login :=
371 fnd_global.conc_login_id;
372 end if;
373 l_trolin_tbl(1).last_updated_by := fnd_global.user_id;
374 l_trolin_tbl(1).program_id := fnd_global.conc_program_id;
375 l_trolin_tbl(1).program_update_date := SYSDATE;
376 l_trolin_tbl(1).request_id := fnd_global.conc_request_id;
377 l_trolin_tbl(1).program_application_id :=
378 fnd_global.prog_appl_id;
379 inv_trolin_util.update_row(l_trolin_tbl(1));
380 --end if;
381 end loop;
382 end if;
383 end if;
384
385 UPDATE mtl_material_transactions_temp mmtt
386 SET mmtt.transaction_quantity = -1 * ABS(Round(mmtt.transaction_quantity,5)),
387 mmtt.primary_quantity = -1 * ABS(Round(mmtt.primary_quantity,5))
388 WHERE mmtt.transaction_header_id = l_transaction_header_id
389 AND mmtt.transaction_action_id in (1, 2, 3, 21, 28, 29, 32, 34);
390
391 IF (l_debug = 1) THEN
392 TraceLog('after update sign', 'Pick_Confirm');
393 END IF;
394
395 -- copy lots form mmtt to lots_temp table
396 lot_success := 'FULL_LOT_PROCESSING' ;
397 INVTTMTX.lot_handling(l_transaction_header_id, lot_success );
398 IF (l_debug = 1) THEN
399 TraceLog('after lot handling', 'Pick_Confirm');
400 END IF;
401
402 IF ( lot_success = '-1' ) THEN
403 IF (l_debug = 1) THEN
404 TraceLog('lot success= -1', 'Pick_Confirm');
405 END IF;
406 rollback to TO_TRX_LINE_SAVE;
407 FND_Message.Set_Name('INV','INV_ORPHAN_CLEANUP_ERROR');
408 FND_MSG_PUB.add;
409 raise FND_API.G_EXC_ERROR;
410 ELSIF ( lot_success = '-2' ) THEN
411 IF (l_debug = 1) THEN
412 TraceLog('lot success= -2', 'Pick_Confirm');
413 END IF;
414 rollback to TO_TRX_LINE_SAVE;
415 FND_Message.Set_Name('INV', 'INV_LOTNULL_ERROR');
416 FND_MSG_PUB.add;
417 raise FND_API.G_EXC_ERROR;
418 ELSIF ( lot_success = '-3' ) THEN
419 IF (l_debug = 1) THEN
420 TraceLog('lot success= -3', 'Pick_Confirm');
421 END IF;
422 rollback to TO_TRX_LINE_SAVE;
423 FND_Message.Set_Name('INV', 'INV_LOTCOPY_ERROR');
424 FND_MSG_PUB.add;
425 raise FND_API.G_EXC_ERROR;
426 ELSIF ( lot_success = '-4' ) THEN
427 IF (l_debug = 1) THEN
428 TraceLog('lot success= -4', 'Pick_Confirm');
429 END IF;
430 rollback to TO_TRX_LINE_SAVE;
431 FND_Message.Set_Name('INV', 'INV_DYNAMIC_SERIAL_ERROR');
432 FND_MSG_PUB.Add;
433 raise FND_API.G_EXC_ERROR;
434 END IF;
435
436 /* Bug 1620487 - Performance
437 We no longer need to call do_check, since we lock the quantity
438 Tree during pick release. This was causing a huge performance hit.
439 INV_Quantity_Tree_Pub.Do_Check
440 (
441 p_api_version_number => 1.0,
442 p_init_msg_lst => FND_API.G_FALSE,
443 x_return_status => l_return_status,
444 x_msg_count => l_msg_count,
445 x_msg_data => l_msg_data,
446 x_no_violation => l_no_violation
447 );
448
449 IF (l_debug = 1) THEN
450 TraceLog('after quantity_tree.do_check', 'Pick_Confirm');
451 END IF;
452 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
453 rollback to TO_TRX_LINE_SAVE;
454 raise FND_API.G_EXC_ERROR;
455 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
456 rollback to TO_TRX_LINE_SAVE;
457 raise FND_API.G_EXC_UNEXPECTED_ERROR;
458 end if;
459 */
460 IF (P_MOVE_ORDER_TYPE <> 5 OR P_MOVE_ORDER_TYPE is null) then
461 -- Calling new Java transaction manager instread of pro C version
462 IF (l_debug = 1) THEN
463 TraceLog('Transaction Mode = '||l_proc_mode, 'Pick_Confirm', 1);
464 END IF;
465 l_success := INV_LPN_TRX_PUB.PROCESS_LPN_TRX( p_trx_hdr_id => l_transaction_header_id,
466 x_proc_msg => l_msg_data,
467 p_proc_mode => l_proc_mode);
468 if( l_success <> 0 ) THEN
469 IF (l_debug = 1) THEN
470 IF (l_debug = 1) THEN
471 TraceLog('not success', 'Pick_Confirm', 1);
472 TraceLog('error from inv_trx_mgr.process_trx_batch' || l_msg_data, 'Pick_Confirm', 1);
473 END IF;
474 END IF;
475 rollback to TO_TRX_LINE_SAVE;
476 /*if( l_rc_field = 1) then
477 IF (l_debug = 1) THEN
478 IF (l_debug = 1) THEN
479 TraceLog('l_rc_field = ' || l_rc_field);
480 END IF;
481 END IF;
482 FND_MESSAGE.SET_NAME('INV', 'INV_TM_TIME_OUT');
483 FND_MSG_PUB.ADD;
484 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 raise FND_API.G_EXC_UNEXPECTED_ERROR;
486 elsif l_rc_field = 2 then
487 IF (l_debug = 1) THEN
488 IF (l_debug = 1) THEN
489 TraceLog('l_rc_field = ' || l_rc_field);
490 END IF;
491 END IF;
492 FND_MESSAGE.SET_NAME('INV', 'INV_TM_MGR_NOT_AVAIL');
493 FND_MSG_PUB.ADD;
494 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
496 elsif l_rc_field <> 0 then
497 IF (l_debug = 1) THEN
498 IF (l_debug = 1) THEN
499 TraceLog('l_rc_field = ' || l_rc_field);
500 END IF;
501 END IF;
502 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
503 end if; */
504 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
505 else
506 IF (l_debug = 1) THEN
507 IF (l_debug = 1) THEN
508 TraceLog('Success from inv_trx_mgr.process_trx_batch', 'Pick_Confirm');
509 END IF;
510 END IF;
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512 end if;
513 ELSE
514 l_program := 'INXTPU';
515 l_func := l_program;
516 l_args := l_program || ' ' || 'TRANS_HEADER_ID='|| to_char(l_transaction_header_id);
517 p_timeout := 500;
518 commit; --Bug2621098
519
520 l_old_tm_success := inv_tm_launch
521 (
522 program => l_program,
523 args => l_args,
524 timeout => p_timeout,
525 rtval => l_rc_field);
526 if( not l_old_tm_success ) THEN
527 TraceLog('not success', 'Pick_Confirm');
528 TraceLog('error from inv_tm', 'Pick_Confirm');
529 TraceLog('Error from INV_TM launch', 'Pick_Confirm');
530 --rollback to TO_TRX_LINE_SAVE;
531 /*if( l_rc_field = 1) then
532 TraceLog('l_rc_field = ' || l_rc_field);
533 FND_MESSAGE.SET_NAME('INV', 'INV_TM_TIME_OUT');
534 FND_MSG_PUB.ADD;
535 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 raise FND_API.G_EXC_UNEXPECTED_ERROR;
537 elsif l_rc_field = 2 then
538 TraceLog('l_rc_field = ' || l_rc_field);
539 FND_MESSAGE.SET_NAME('INV', 'INV_TM_MGR_NOT_AVAIL');
540 FND_MSG_PUB.ADD;
541 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
543 elsif l_rc_field <> 0 then
544 TraceLog('l_rc_field = ' || l_rc_field);
545 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
546 end if;*/
547 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
548 else
549 TraceLog('Success from Old INVTM', 'Pick_Confirm');
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551 end if;
552 END IF; --Call Old tm for WIP Picking records
553
554 -- Insert_Row.Clear_Orphan_Lots_Serials(l_transaction_header_id,-999);
555 Begin
556 DELETE FROM mtl_transaction_lots_temp
557 WHERE group_header_id = l_transaction_header_id
558 AND transaction_temp_id NOT IN
559 ( SELECT mmtt.transaction_temp_id
560 FROM mtl_material_transactions_temp mmtt
561 WHERE mmtt.transaction_header_id = l_transaction_header_id
562 AND mmtt.transaction_temp_id IS NOT NULL);
563
564 DELETE FROM mtl_serial_numbers_temp
565 WHERE group_header_id = l_transaction_header_id
566 AND transaction_temp_id NOT IN
567 ( SELECT mmtt.transaction_temp_id
568 FROM mtl_material_transactions_temp mmtt
569 WHERE mmtt.transaction_header_id = l_transaction_header_id
570 AND mmtt.transaction_temp_id IS NOT NULL)
571 AND transaction_temp_id NOT IN
572 ( SELECT mtlt.serial_transaction_temp_id
573 FROM mtl_transaction_lots_temp mtlt
574 WHERE mtlt.group_header_id = l_transaction_header_id
575 AND mtlt.serial_transaction_temp_id IS NOT NULL);
576
577 -- Bug 5879916
578 /*
579 DELETE FROM mtl_serial_numbers
580 WHERE current_status = 6
581 AND group_mark_id = -1
582 AND inventory_item_id in (select inventory_item_id
583 FROM mtl_material_transactions_temp
584 WHERE transaction_header_id = l_transaction_header_id)
585 AND current_organization_id in (select organization_id
586 FROM mtl_material_transactions_temp
587 WHERE transaction_header_id = l_transaction_header_id);
588 */
589
590 DELETE /*+ INDEX(MSN MTL_SERIAL_NUMBERS_N2) */
591 FROM mtl_serial_numbers MSN
592 WHERE MSN.current_status = 6
593 AND MSN.group_mark_id = -1
594 AND (MSN.INVENTORY_ITEM_ID,MSN.CURRENT_ORGANIZATION_ID) IN
595 (SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID
596 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
597 WHERE TRANSACTION_HEADER_ID = l_transaction_header_id);
598
599 -- End of change for Bug 5879916
600
601
602
603 --2101601
604
605
606 EXCEPTION
607 WHEN OTHERS then
608 -- TraceLog('error in cleanup orphan lot serial');
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 rollback to TO_TRX_LINE_SAVE;
611 FND_MESSAGE.SET_NAME('INV', 'INV_ORPHAN_CLEANUP_ERROR');
612 FND_MSG_PUB.ADD;
613 END;
614 if( p_commit = FND_API.G_TRUE ) then
615 commit;
616 end if;
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618 EXCEPTION
619 WHEN FND_API.G_EXC_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 -- Get message count and data
622 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data =>
623 x_msg_data);
624 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
627 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data =>
628 x_msg_data);
629 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
630 WHEN OTHERS THEN
631 IF (l_debug = 1) THEN
632 TraceLog('Exception when others', 'Pick_Confirm');
633 END IF;
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 -- Get message count and data
636 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
637 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
638
639 END Pick_Confirm;
640
641 FUNCTION INV_TM_Launch( program in varchar2,
642 args in varchar2 default NULL,
643 put1 in varchar2 default NULL,
644 put2 in varchar2 default NULL,
645 put3 in varchar2 default NULL,
646 put4 in varchar2 default NULL,
647 put5 in varchar2 default NULL,
648 get1 in varchar2 default NULL,
649 get2 in varchar2 default NULL,
650 get3 in varchar2 default NULL,
651 get4 in varchar2 default NULL,
652 get5 in varchar2 default NULL,
653 timeout in number default NULL,
654 rtval out NOCOPY NUMBER) return BOOLEAN is
655
656 outcome VARCHAR(80);
657 msg VARCHAR(255);
658 rtvl NUMBER;
659 args1 VARCHAR(240);
660 args2 VARCHAR(240);
661 args3 VARCHAR(240);
662 args4 VARCHAR(240);
663 args5 VARCHAR(240);
664 args6 VARCHAR(240);
665 args7 VARCHAR(240);
666 args8 VARCHAR(240);
667 args9 VARCHAR(240);
668 args10 VARCHAR(240);
669 args11 VARCHAR(240);
670 args12 VARCHAR(240);
671 args13 VARCHAR(240);
672 args14 VARCHAR(240);
673 args15 VARCHAR(240);
674 args16 VARCHAR(240);
675 args17 VARCHAR(240);
676 args18 VARCHAR(240);
677 args19 VARCHAR(240);
678 args20 VARCHAR(240);
679 prod VARCHAR(240);
680 func VARCHAR(240);
681 m_message VARCHAR2(2000);
682 p_userid NUMBER;
683 p_respid NUMBER;
684 p_applid NUMBER;
685
686 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
687 BEGIN
688 -- load values for field gets;
689 args12 := null;
690 args13 := null;
691 args14 := null;
692 args15 := null;
693 args14 := null;
694 args15 := null;
695 args16 := null;
696
697 prod := 'INV';
698 func := program;
699 rtvl := fnd_transaction.synchronous
700 (
701 NVL(timeout,500),outcome, msg, prod,func, args,
702 put1, put2, put3, put4, put5,
703 get1, get2, get3, get4, get5,
704 args12, args13, args14, args15, args16,
705 chr(0), '', '', '');
706
707 IF (l_debug = 1) THEN
708 TraceLog('fnd_transaction.synchrous return: outcome is ' ||NVL(outcome,'NULL') || ' and rtvl is ' ||To_char(rtvl), 'Inv_TM_Launch');
709 END IF;
710 -- handle problems
711
712 --rc_field := rtvl;
713 IF rtvl = 1 THEN
714 IF (l_debug = 1) THEN
715 IF (l_debug = 1) THEN
716 TraceLog('INV_TM_TIMEOUT', 'INV_TM_LAUNCH');
717 END IF;
718 END IF;
719 fnd_message.set_name('INV', 'INV_TM_TIME_OUT');
720 fnd_msg_pub.add;
721 RETURN (FALSE);
722 ELSIF rtvl = 2 THEN
723 IF (l_debug = 1) THEN
724 IF (l_debug = 1) THEN
725 TraceLog('INV_TM_MGR_NOT_AVAIL', 'INV_TM_LAUNCH');
726 END IF;
727 END IF;
728 fnd_message.set_name('INV', 'INV_TM_MGR_NOT_AVAIL');
729 fnd_msg_pub.add;
730 RETURN (FALSE);
731 ELSIF rtvl = 3 THEN
732 IF (l_debug = 1) THEN
733 IF (l_debug = 1) THEN
734 TraceLog('CONC-DG-Inactive No Manager', 'INV_TM_LAUNCH');
735 END IF;
736 END IF;
737 fnd_message.set_name('FND','CONC-DG-Inactive No Manager');
738 fnd_msg_pub.add;
739 RETURN(FALSE);
740 ELSIF rtvl = 0 THEN
741 -- get info back from server and handle problems
742 rtvl := fnd_transaction.get_values
743 (args1, args2, args3, args4, args5,
744 args6, args7, args8, args9, args10, args11,
745 args12, args13, args14, args15,
746 args16, args17, args18, args19, args20);
747 END IF;
748
749 IF (args1 IS NOT NULL) THEN
750 --inv_debug.message(args1);
751 FND_MSG_PUB.Add_Exc_Msg(
752 p_pkg_name => 'INV_Pick_Confirm_PUB',
753 p_procedure_name => 'INV_TM_LAUNCH',
754 p_error_text => args1);
755 END IF;
756 IF (args2 IS NOT NULL) THEN
757 --inv_debug.message(args2);
758 FND_MSG_PUB.Add_Exc_Msg(
759 p_pkg_name => 'INV_Pick_Confirm_PUB',
760 p_procedure_name => 'INV_TM_LAUNCH',
761 p_error_text => args2);
762 END IF;
763 IF (args3 IS NOT NULL) THEN
764 FND_MSG_PUB.Add_Exc_Msg(
765 p_pkg_name => 'INV_Pick_Confirm_PUB',
766 p_procedure_name => 'INV_TM_LAUNCH',
767 p_error_text => args3);
768 --inv_debug.message(args3);
769 END IF;
770 IF (args4 IS NOT NULL) THEN
771 FND_MSG_PUB.Add_Exc_Msg(
772 p_pkg_name => 'INV_Pick_Confirm_PUB',
773 p_procedure_name => 'INV_TM_LAUNCH',
774 p_error_text => args4);
775 --inv_debug.message(args4);
776 END IF;
777 IF (args5 IS NOT NULL) THEN
778 FND_MSG_PUB.Add_Exc_Msg(
779 p_pkg_name => 'INV_Pick_Confirm_PUB',
780 p_procedure_name => 'INV_TM_LAUNCH',
781 p_error_text => args5);
782 --inv_debug.message(args5);
783 END IF;
784 IF (args6 IS NOT NULL) THEN
785 --inv_debug.message(args6);
786 FND_MSG_PUB.Add_Exc_Msg(
787 p_pkg_name => 'INV_Pick_Confirm_PUB',
788 p_procedure_name => 'INV_TM_LAUNCH',
789 p_error_text => args6);
790 END IF;
791 IF (args7 IS NOT NULL) THEN
792 FND_MSG_PUB.Add_Exc_Msg(
793 p_pkg_name => 'INV_Pick_Confirm_PUB',
794 p_procedure_name => 'INV_TM_LAUNCH',
795 p_error_text => args7);
796 --inv_debug.message(args7);
797 END IF;
798 IF (args8 IS NOT NULL) THEN
799 FND_MSG_PUB.Add_Exc_Msg(
800 p_pkg_name => 'INV_Pick_Confirm_PUB',
801 p_procedure_name => 'INV_TM_LAUNCH',
802 p_error_text => args8);
803 --inV_debug.message(args8);
804 END IF;
805 IF (args9 IS NOT NULL) THEN
806 FND_MSG_PUB.Add_Exc_Msg(
807 p_pkg_name => 'INV_Pick_Confirm_PUB',
808 p_procedure_name => 'INV_TM_LAUNCH',
809 p_error_text => args9);
810 --inv_debug.message(args9);
811 END IF;
812 IF (args10 IS NOT NULL) THEN
813 FND_MSG_PUB.Add_Exc_Msg(
814 p_pkg_name => 'INV_Pick_Confirm_PUB',
815 p_procedure_name => 'INV_TM_LAUNCH',
816 p_error_text => args10);
817 --inv_debug.message(args10);
818 END IF;
819
820 -- Kick back status
821 IF (outcome = 'SUCCESS' and rtvl = 0 ) THEN
822 RETURN (TRUE);
823 ELSE
824 RETURN (FALSE);
825 END IF;
826 EXCEPTION
827 When others then
828 IF (l_debug = 1) THEN
829 IF (l_debug = 1) THEN
830 TraceLog('exception ...', 'INV_TM_LAUNCH');
831 END IF;
832 END IF;
833 FND_MSG_PUB.Add_Exc_Msg(
834 p_pkg_name => 'INV_Pick_Confirm_PUB',
835 p_procedure_name => 'INV_TM_LAUNCH');
836 return (false);
837 END INV_TM_LAUNCH;
838
839
840
841 END INV_Pick_Wave_Pick_Confirm_PUB;