[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.4 2009/09/15 11:03:27 skommine 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 --bug 8841933 added secondary_qty
386 UPDATE mtl_material_transactions_temp mmtt
387 SET mmtt.transaction_quantity = -1 * ABS(Round(mmtt.transaction_quantity,5)),
388 mmtt.primary_quantity = -1 * ABS(Round(mmtt.primary_quantity,5)),
389 mmtt.secondary_transaction_quantity = -1 * ABS(mmtt.secondary_transaction_quantity)
390 WHERE mmtt.transaction_header_id = l_transaction_header_id
391 AND mmtt.transaction_action_id in (1, 2, 3, 21, 28, 29, 32, 34);
392
393 IF (l_debug = 1) THEN
394 TraceLog('after update sign', 'Pick_Confirm');
395 END IF;
396
397 -- copy lots form mmtt to lots_temp table
398 lot_success := 'FULL_LOT_PROCESSING' ;
399 INVTTMTX.lot_handling(l_transaction_header_id, lot_success );
400 IF (l_debug = 1) THEN
401 TraceLog('after lot handling', 'Pick_Confirm');
402 END IF;
403
404 IF ( lot_success = '-1' ) THEN
405 IF (l_debug = 1) THEN
406 TraceLog('lot success= -1', 'Pick_Confirm');
407 END IF;
408 rollback to TO_TRX_LINE_SAVE;
409 FND_Message.Set_Name('INV','INV_ORPHAN_CLEANUP_ERROR');
410 FND_MSG_PUB.add;
411 raise FND_API.G_EXC_ERROR;
412 ELSIF ( lot_success = '-2' ) THEN
413 IF (l_debug = 1) THEN
414 TraceLog('lot success= -2', 'Pick_Confirm');
415 END IF;
416 rollback to TO_TRX_LINE_SAVE;
417 FND_Message.Set_Name('INV', 'INV_LOTNULL_ERROR');
418 FND_MSG_PUB.add;
419 raise FND_API.G_EXC_ERROR;
420 ELSIF ( lot_success = '-3' ) THEN
421 IF (l_debug = 1) THEN
422 TraceLog('lot success= -3', 'Pick_Confirm');
423 END IF;
424 rollback to TO_TRX_LINE_SAVE;
425 FND_Message.Set_Name('INV', 'INV_LOTCOPY_ERROR');
426 FND_MSG_PUB.add;
427 raise FND_API.G_EXC_ERROR;
428 ELSIF ( lot_success = '-4' ) THEN
429 IF (l_debug = 1) THEN
430 TraceLog('lot success= -4', 'Pick_Confirm');
431 END IF;
432 rollback to TO_TRX_LINE_SAVE;
433 FND_Message.Set_Name('INV', 'INV_DYNAMIC_SERIAL_ERROR');
434 FND_MSG_PUB.Add;
435 raise FND_API.G_EXC_ERROR;
436 END IF;
437
438 /* Bug 1620487 - Performance
439 We no longer need to call do_check, since we lock the quantity
440 Tree during pick release. This was causing a huge performance hit.
441 INV_Quantity_Tree_Pub.Do_Check
442 (
443 p_api_version_number => 1.0,
444 p_init_msg_lst => FND_API.G_FALSE,
445 x_return_status => l_return_status,
446 x_msg_count => l_msg_count,
447 x_msg_data => l_msg_data,
448 x_no_violation => l_no_violation
449 );
450
451 IF (l_debug = 1) THEN
452 TraceLog('after quantity_tree.do_check', 'Pick_Confirm');
453 END IF;
454 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
455 rollback to TO_TRX_LINE_SAVE;
456 raise FND_API.G_EXC_ERROR;
457 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
458 rollback to TO_TRX_LINE_SAVE;
459 raise FND_API.G_EXC_UNEXPECTED_ERROR;
460 end if;
461 */
462 IF (P_MOVE_ORDER_TYPE <> 5 OR P_MOVE_ORDER_TYPE is null) then
463 -- Calling new Java transaction manager instread of pro C version
464 IF (l_debug = 1) THEN
465 TraceLog('Transaction Mode = '||l_proc_mode, 'Pick_Confirm', 1);
466 END IF;
467 l_success := INV_LPN_TRX_PUB.PROCESS_LPN_TRX( p_trx_hdr_id => l_transaction_header_id,
468 x_proc_msg => l_msg_data,
469 p_proc_mode => l_proc_mode);
470 if( l_success <> 0 ) THEN
471 IF (l_debug = 1) THEN
472 IF (l_debug = 1) THEN
473 TraceLog('not success', 'Pick_Confirm', 1);
474 TraceLog('error from inv_trx_mgr.process_trx_batch' || l_msg_data, 'Pick_Confirm', 1);
475 END IF;
476 END IF;
477 rollback to TO_TRX_LINE_SAVE;
478 /*if( l_rc_field = 1) then
479 IF (l_debug = 1) THEN
480 IF (l_debug = 1) THEN
481 TraceLog('l_rc_field = ' || l_rc_field);
482 END IF;
483 END IF;
484 FND_MESSAGE.SET_NAME('INV', 'INV_TM_TIME_OUT');
485 FND_MSG_PUB.ADD;
486 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
487 raise FND_API.G_EXC_UNEXPECTED_ERROR;
488 elsif l_rc_field = 2 then
489 IF (l_debug = 1) THEN
490 IF (l_debug = 1) THEN
491 TraceLog('l_rc_field = ' || l_rc_field);
492 END IF;
493 END IF;
494 FND_MESSAGE.SET_NAME('INV', 'INV_TM_MGR_NOT_AVAIL');
495 FND_MSG_PUB.ADD;
496 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
498 elsif l_rc_field <> 0 then
499 IF (l_debug = 1) THEN
500 IF (l_debug = 1) THEN
501 TraceLog('l_rc_field = ' || l_rc_field);
502 END IF;
503 END IF;
504 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
505 end if; */
506 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
507 else
508 IF (l_debug = 1) THEN
509 IF (l_debug = 1) THEN
510 TraceLog('Success from inv_trx_mgr.process_trx_batch', 'Pick_Confirm');
511 END IF;
512 END IF;
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 end if;
515 ELSE
516 l_program := 'INXTPU';
517 l_func := l_program;
518 l_args := l_program || ' ' || 'TRANS_HEADER_ID='|| to_char(l_transaction_header_id);
519 p_timeout := 500;
520 commit; --Bug2621098
521
522 l_old_tm_success := inv_tm_launch
523 (
524 program => l_program,
525 args => l_args,
526 timeout => p_timeout,
527 rtval => l_rc_field);
528 if( not l_old_tm_success ) THEN
529 TraceLog('not success', 'Pick_Confirm');
530 TraceLog('error from inv_tm', 'Pick_Confirm');
531 TraceLog('Error from INV_TM launch', 'Pick_Confirm');
532 --rollback to TO_TRX_LINE_SAVE;
533 /*if( l_rc_field = 1) then
534 TraceLog('l_rc_field = ' || l_rc_field);
535 FND_MESSAGE.SET_NAME('INV', 'INV_TM_TIME_OUT');
536 FND_MSG_PUB.ADD;
537 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 raise FND_API.G_EXC_UNEXPECTED_ERROR;
539 elsif l_rc_field = 2 then
540 TraceLog('l_rc_field = ' || l_rc_field);
541 FND_MESSAGE.SET_NAME('INV', 'INV_TM_MGR_NOT_AVAIL');
542 FND_MSG_PUB.ADD;
543 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
545 elsif l_rc_field <> 0 then
546 TraceLog('l_rc_field = ' || l_rc_field);
547 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
548 end if;*/
549 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
550 else
551 TraceLog('Success from Old INVTM', 'Pick_Confirm');
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553 end if;
554 END IF; --Call Old tm for WIP Picking records
555
556 -- Insert_Row.Clear_Orphan_Lots_Serials(l_transaction_header_id,-999);
557 Begin
558 DELETE FROM mtl_transaction_lots_temp
559 WHERE group_header_id = l_transaction_header_id
560 AND transaction_temp_id NOT IN
561 ( SELECT mmtt.transaction_temp_id
562 FROM mtl_material_transactions_temp mmtt
563 WHERE mmtt.transaction_header_id = l_transaction_header_id
564 AND mmtt.transaction_temp_id IS NOT NULL);
565
566 DELETE FROM mtl_serial_numbers_temp
567 WHERE group_header_id = l_transaction_header_id
568 AND transaction_temp_id NOT IN
569 ( SELECT mmtt.transaction_temp_id
570 FROM mtl_material_transactions_temp mmtt
571 WHERE mmtt.transaction_header_id = l_transaction_header_id
572 AND mmtt.transaction_temp_id IS NOT NULL)
573 AND transaction_temp_id NOT IN
574 ( SELECT mtlt.serial_transaction_temp_id
575 FROM mtl_transaction_lots_temp mtlt
576 WHERE mtlt.group_header_id = l_transaction_header_id
577 AND mtlt.serial_transaction_temp_id IS NOT NULL);
578
579 -- Bug 5879916
580 /*
581 DELETE FROM mtl_serial_numbers
582 WHERE current_status = 6
583 AND group_mark_id = -1
584 AND inventory_item_id in (select inventory_item_id
585 FROM mtl_material_transactions_temp
586 WHERE transaction_header_id = l_transaction_header_id)
587 AND current_organization_id in (select organization_id
588 FROM mtl_material_transactions_temp
589 WHERE transaction_header_id = l_transaction_header_id);
590 */
591
592 DELETE /*+ INDEX(MSN MTL_SERIAL_NUMBERS_N2) */
593 FROM mtl_serial_numbers MSN
594 WHERE MSN.current_status = 6
595 AND MSN.group_mark_id = -1
596 AND (MSN.INVENTORY_ITEM_ID,MSN.CURRENT_ORGANIZATION_ID) IN
597 (SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID
598 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
599 WHERE TRANSACTION_HEADER_ID = l_transaction_header_id);
600
601 -- End of change for Bug 5879916
602
603
604
605 --2101601
606
607
608 EXCEPTION
609 WHEN OTHERS then
610 -- TraceLog('error in cleanup orphan lot serial');
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 rollback to TO_TRX_LINE_SAVE;
613 FND_MESSAGE.SET_NAME('INV', 'INV_ORPHAN_CLEANUP_ERROR');
614 FND_MSG_PUB.ADD;
615 END;
616 if( p_commit = FND_API.G_TRUE ) then
617 commit;
618 end if;
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 EXCEPTION
621 WHEN FND_API.G_EXC_ERROR THEN
622 x_return_status := FND_API.G_RET_STS_ERROR;
623 -- Get message count and data
624 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data =>
625 x_msg_data);
626 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
629 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data =>
630 x_msg_data);
631 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
632 WHEN OTHERS THEN
633 IF (l_debug = 1) THEN
634 TraceLog('Exception when others', 'Pick_Confirm');
635 END IF;
636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
637 -- Get message count and data
638 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
639 ROLLBACK TO TO_TRX_LINE_SAVE; /* Bug 7014473 */
640
641 END Pick_Confirm;
642
643 FUNCTION INV_TM_Launch( program in varchar2,
644 args in varchar2 default NULL,
645 put1 in varchar2 default NULL,
646 put2 in varchar2 default NULL,
647 put3 in varchar2 default NULL,
648 put4 in varchar2 default NULL,
649 put5 in varchar2 default NULL,
650 get1 in varchar2 default NULL,
651 get2 in varchar2 default NULL,
652 get3 in varchar2 default NULL,
653 get4 in varchar2 default NULL,
654 get5 in varchar2 default NULL,
655 timeout in number default NULL,
656 rtval out NOCOPY NUMBER) return BOOLEAN is
657
658 outcome VARCHAR(80);
659 msg VARCHAR(255);
660 rtvl NUMBER;
661 args1 VARCHAR(240);
662 args2 VARCHAR(240);
663 args3 VARCHAR(240);
664 args4 VARCHAR(240);
665 args5 VARCHAR(240);
666 args6 VARCHAR(240);
667 args7 VARCHAR(240);
668 args8 VARCHAR(240);
669 args9 VARCHAR(240);
670 args10 VARCHAR(240);
671 args11 VARCHAR(240);
672 args12 VARCHAR(240);
673 args13 VARCHAR(240);
674 args14 VARCHAR(240);
675 args15 VARCHAR(240);
676 args16 VARCHAR(240);
677 args17 VARCHAR(240);
678 args18 VARCHAR(240);
679 args19 VARCHAR(240);
680 args20 VARCHAR(240);
681 prod VARCHAR(240);
682 func VARCHAR(240);
683 m_message VARCHAR2(2000);
684 p_userid NUMBER;
685 p_respid NUMBER;
686 p_applid NUMBER;
687
688 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
689 BEGIN
690 -- load values for field gets;
691 args12 := null;
692 args13 := null;
693 args14 := null;
694 args15 := null;
695 args14 := null;
696 args15 := null;
697 args16 := null;
698
699 prod := 'INV';
700 func := program;
701 rtvl := fnd_transaction.synchronous
702 (
703 NVL(timeout,500),outcome, msg, prod,func, args,
704 put1, put2, put3, put4, put5,
705 get1, get2, get3, get4, get5,
706 args12, args13, args14, args15, args16,
707 chr(0), '', '', '');
708
709 IF (l_debug = 1) THEN
710 TraceLog('fnd_transaction.synchrous return: outcome is ' ||NVL(outcome,'NULL') || ' and rtvl is ' ||To_char(rtvl), 'Inv_TM_Launch');
711 END IF;
712 -- handle problems
713
714 --rc_field := rtvl;
715 IF rtvl = 1 THEN
716 IF (l_debug = 1) THEN
717 IF (l_debug = 1) THEN
718 TraceLog('INV_TM_TIMEOUT', 'INV_TM_LAUNCH');
719 END IF;
720 END IF;
721 fnd_message.set_name('INV', 'INV_TM_TIME_OUT');
722 fnd_msg_pub.add;
723 RETURN (FALSE);
724 ELSIF rtvl = 2 THEN
725 IF (l_debug = 1) THEN
726 IF (l_debug = 1) THEN
727 TraceLog('INV_TM_MGR_NOT_AVAIL', 'INV_TM_LAUNCH');
728 END IF;
729 END IF;
730 fnd_message.set_name('INV', 'INV_TM_MGR_NOT_AVAIL');
731 fnd_msg_pub.add;
732 RETURN (FALSE);
733 ELSIF rtvl = 3 THEN
734 IF (l_debug = 1) THEN
735 IF (l_debug = 1) THEN
736 TraceLog('CONC-DG-Inactive No Manager', 'INV_TM_LAUNCH');
737 END IF;
738 END IF;
739 fnd_message.set_name('FND','CONC-DG-Inactive No Manager');
740 fnd_msg_pub.add;
741 RETURN(FALSE);
742 ELSIF rtvl = 0 THEN
743 -- get info back from server and handle problems
744 rtvl := fnd_transaction.get_values
745 (args1, args2, args3, args4, args5,
746 args6, args7, args8, args9, args10, args11,
747 args12, args13, args14, args15,
748 args16, args17, args18, args19, args20);
749 END IF;
750
751 IF (args1 IS NOT NULL) THEN
752 --inv_debug.message(args1);
753 FND_MSG_PUB.Add_Exc_Msg(
754 p_pkg_name => 'INV_Pick_Confirm_PUB',
755 p_procedure_name => 'INV_TM_LAUNCH',
756 p_error_text => args1);
757 END IF;
758 IF (args2 IS NOT NULL) THEN
759 --inv_debug.message(args2);
760 FND_MSG_PUB.Add_Exc_Msg(
761 p_pkg_name => 'INV_Pick_Confirm_PUB',
762 p_procedure_name => 'INV_TM_LAUNCH',
763 p_error_text => args2);
764 END IF;
765 IF (args3 IS NOT NULL) THEN
766 FND_MSG_PUB.Add_Exc_Msg(
767 p_pkg_name => 'INV_Pick_Confirm_PUB',
768 p_procedure_name => 'INV_TM_LAUNCH',
769 p_error_text => args3);
770 --inv_debug.message(args3);
771 END IF;
772 IF (args4 IS NOT NULL) THEN
773 FND_MSG_PUB.Add_Exc_Msg(
774 p_pkg_name => 'INV_Pick_Confirm_PUB',
775 p_procedure_name => 'INV_TM_LAUNCH',
776 p_error_text => args4);
777 --inv_debug.message(args4);
778 END IF;
779 IF (args5 IS NOT NULL) THEN
780 FND_MSG_PUB.Add_Exc_Msg(
781 p_pkg_name => 'INV_Pick_Confirm_PUB',
782 p_procedure_name => 'INV_TM_LAUNCH',
783 p_error_text => args5);
784 --inv_debug.message(args5);
785 END IF;
786 IF (args6 IS NOT NULL) THEN
787 --inv_debug.message(args6);
788 FND_MSG_PUB.Add_Exc_Msg(
789 p_pkg_name => 'INV_Pick_Confirm_PUB',
790 p_procedure_name => 'INV_TM_LAUNCH',
791 p_error_text => args6);
792 END IF;
793 IF (args7 IS NOT NULL) THEN
794 FND_MSG_PUB.Add_Exc_Msg(
795 p_pkg_name => 'INV_Pick_Confirm_PUB',
796 p_procedure_name => 'INV_TM_LAUNCH',
797 p_error_text => args7);
798 --inv_debug.message(args7);
799 END IF;
800 IF (args8 IS NOT NULL) THEN
801 FND_MSG_PUB.Add_Exc_Msg(
802 p_pkg_name => 'INV_Pick_Confirm_PUB',
803 p_procedure_name => 'INV_TM_LAUNCH',
804 p_error_text => args8);
805 --inV_debug.message(args8);
806 END IF;
807 IF (args9 IS NOT NULL) THEN
808 FND_MSG_PUB.Add_Exc_Msg(
809 p_pkg_name => 'INV_Pick_Confirm_PUB',
810 p_procedure_name => 'INV_TM_LAUNCH',
811 p_error_text => args9);
812 --inv_debug.message(args9);
813 END IF;
814 IF (args10 IS NOT NULL) THEN
815 FND_MSG_PUB.Add_Exc_Msg(
816 p_pkg_name => 'INV_Pick_Confirm_PUB',
817 p_procedure_name => 'INV_TM_LAUNCH',
818 p_error_text => args10);
819 --inv_debug.message(args10);
820 END IF;
821
822 -- Kick back status
823 IF (outcome = 'SUCCESS' and rtvl = 0 ) THEN
824 RETURN (TRUE);
825 ELSE
826 RETURN (FALSE);
827 END IF;
828 EXCEPTION
829 When others then
830 IF (l_debug = 1) THEN
831 IF (l_debug = 1) THEN
832 TraceLog('exception ...', 'INV_TM_LAUNCH');
833 END IF;
834 END IF;
835 FND_MSG_PUB.Add_Exc_Msg(
836 p_pkg_name => 'INV_Pick_Confirm_PUB',
837 p_procedure_name => 'INV_TM_LAUNCH');
838 return (false);
839 END INV_TM_LAUNCH;
840
841
842
843 END INV_Pick_Wave_Pick_Confirm_PUB;