[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_MOBILE_PROCESS_TXN
Source
1 PACKAGE BODY INV_RCV_MOBILE_PROCESS_TXN AS
2 /* $Header: INVRCVPB.pls 120.8 2009/06/24 07:06:54 aditshar ship $*/
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_RCV_MOBILE_PROCESS_TXN';
6
7 PROCEDURE print_debug(p_err_msg VARCHAR2,
8 p_level NUMBER)
9 IS
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 BEGIN
12 IF (l_debug = 1) THEN
13 inv_mobile_helper_functions.tracelog
14 (p_err_msg => p_err_msg,
15 p_module => 'INV_RCV_MOBILE_PROCESS_TXN',
16 p_level => p_level);
17 END IF;
18
19 -- dbms_output.put_line(p_err_msg);
20 END print_debug;
21
22
23 FUNCTION check_group_id(p_group_id IN NUMBER)
24 RETURN BOOLEAN
25 IS
26 l_rec_count NUMBER := 0;
27 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
28 BEGIN
29 SELECT COUNT(1)
30 INTO l_rec_count
31 FROM RCV_TRANSACTIONS_INTERFACE
32 WHERE group_id = p_group_id;
33
34 IF (l_rec_count = 0) THEN
35 IF (l_debug = 1) THEN
36 print_debug('check_group_id 10 Did not find the row with group id in RTI',4);
37 END IF;
38 return (FALSE);
39 ELSE
40 IF (l_debug = 1) THEN
41 print_debug('check_group_id 20 Found the row with group id in RTI',4);
42 END IF;
43 return (TRUE);
44 END IF;
45
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 IF (l_debug = 1) THEN
49 print_debug('check_group_id 30 Did not find the row with group id in RTI',4);
50 END IF;
51 RETURN(FALSE);
52 WHEN OTHERS THEN
53 NULL;
54 END check_group_id;
55
56
57 PROCEDURE rcv_print_traveller
58 IS
59 v_req_id NUMBER;
60 v_qty_precision VARCHAR2(4);
61 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
62 l_org_id NUMBER;
63 BEGIN
64
65 /*
66 ** Check if we need to print receipt traveller
67 */
68 IF Upper(inv_rcv_common_apis.g_po_startup_value.receipt_traveller) = 'Y' THEN -- ?
69 v_qty_precision := fnd_profile.value('REPORT_QUANTITY_PRECISION');
70 v_req_id :=
71 fnd_request.submit_request('PO',
72 'RCVDLPDT',
73 null,
74 null,
75 false,
76 'P_group_id=' || inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
77 'P_qty_precision='||v_qty_precision,
78 'P_org_id=' || inv_rcv_common_apis.g_po_startup_value.inv_org_id,--NULL, --fnd_char.local_chr(0),----bug 5195963
79 NULL,
80 NULL,
81 NULL,
82 NULL,
83 NULL,
84 NULL, NULL,
85 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
86 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
87 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
88 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
89 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
90
91 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
92 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
93 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
94 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
95
96 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
97 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
98 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
99 NULL, NULL, NULL, NULL, NULL, NULL);
100
101 -- This error handling is useless
102 -- if print_traveller follows commit
103 -- It will be useful if it is called as stand alone
104 -- so leave it here
105 if (v_req_id <= 0 or v_req_id is null) then
106 NULL;
107 ELSE
108 COMMIT;
109 end if;
110 END IF;
111 END rcv_print_traveller;
112
113
114 PROCEDURE rcv_immediate_transaction(x_return_status OUT nocopy VARCHAR2,
115 x_msg_data OUT nocopy VARCHAR2)
116 IS
117 v_req_id NUMBER;
118 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
119 BEGIN
120 x_return_status := fnd_api.g_ret_sts_success;
121
122 v_req_id :=
123 fnd_request.submit_request('PO',
124 'RVCTP',
125 null,
126 null,
127 false,
128 'IMMEDIATE',
129 inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
130 NULL, --fnd_char.local_chr(0), ?
131 NULL,
132 NULL,
133 NULL,
134 NULL,
135 NULL, NULL,
136 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
137 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
138 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
139 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
140 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
141
142 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
143 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
144 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
145 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
146
147 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
148 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
149 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
150 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
151
152 if (v_req_id <= 0 or v_req_id is null) then
153 x_return_status := fnd_api.g_ret_sts_error;
154 x_msg_data := FND_MESSAGE.get;
155 ELSE
156 COMMIT;
157 end if;
158 END rcv_immediate_transaction;
159
160
161 PROCEDURE rcv_online_request (x_return_status OUT nocopy VARCHAR2,
162 x_msg_data OUT nocopy VARCHAR2)
163 IS
164 rc NUMBER;
165 l_timeout NUMBER ;
166 l_outcome VARCHAR2(200) := NULL;
167 l_message VARCHAR2(2000) := NULL;
168 l_return_status VARCHAR2(5) := fnd_api.g_ret_sts_success;
169 l_msg_count NUMBER;
170 x_str varchar2(6000) := NULL;
171 DELETE_ROWS BOOLEAN := FALSE;
172 r_val1 varchar2(300) := NULL;
173 r_val2 varchar2(300) := NULL;
174 r_val3 varchar2(300) := NULL;
175 r_val4 varchar2(300) := NULL;
176 r_val5 varchar2(300) := NULL;
177 r_val6 varchar2(300) := NULL;
178 r_val7 varchar2(300) := NULL;
179 r_val8 varchar2(300) := NULL;
180 r_val9 varchar2(300) := NULL;
181 r_val10 varchar2(300) := NULL;
182 r_val11 varchar2(300) := NULL;
183 r_val12 varchar2(300) := NULL;
184 r_val13 varchar2(300) := NULL;
185 r_val14 varchar2(300) := NULL;
186 r_val15 varchar2(300) := NULL;
187 r_val16 varchar2(300) := NULL;
188 r_val17 varchar2(300) := NULL;
189 r_val18 varchar2(300) := NULL;
190 r_val19 varchar2(300) := NULL;
191 r_val20 varchar2(300) := NULL;
192 l_progress VARCHAR2(10) := '10';
193 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
194 l_group_id NUMBER := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
195 BEGIN
196 x_return_status := fnd_api.g_ret_sts_success;
197
198 -- Bug 5046328
199 -- Get the timeout value from profile
200
201 l_timeout := fnd_profile.value('INV_RPC_TIMEOUT');
202
203 IF (l_timeout is NULL) THEN
204 l_timeout := 300;
205 END IF;
206 --bug 7034252
207 IF (l_debug = 1) THEN
208 print_debug('rcv_online_request - timeout : '|| l_timeout, 1);
209 END IF;
210 Begin
211 print_debug('updating proceesing mode in rti for group id'||inv_rcv_common_apis.g_rcv_global_var.interface_group_id,1);
212 UPDATE RCV_TRANSACTIONS_INTERFACE
213 SET PROCESSING_MODE_CODE = 'ONLINE'
214 WHERE GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
215 AND PROCESSING_MODE_CODE <> 'ONLINE';
216 COMMIT;
217 EXCEPTION
218 WHEN OTHERS THEN
219 print_debug('no record found in rti which requires update ',1);
220 END;
221 --end of bug 7034252
222
223
224 rc := fnd_transaction.synchronous
225 (
226 l_timeout, l_outcome, l_message, 'PO', 'RCVTPO',
227 'ONLINE', inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
228 NULL, NULL, NULL, NULL, NULL, NULL,
229 NULL, NULL, NULL, NULL, NULL, NULL,
230 NULL, NULL, NULL, NULL, NULL, NULL);
231
232 l_progress := '20';
233
234 IF (l_debug = 1) THEN
235 print_debug('rcv_online_request :value of l_outcome:'||l_outcome|| to_char(sysdate,
236 'YYYY-MM-DD HH:DD:SS'), 1);
237 print_debug('rcv_online_request :value of rc:'||rc|| to_char(sysdate,
238 'YYYY-MM-DD HH:DD:SS'), 1);
239 END IF;
240
241 IF (rc = 0 and (l_outcome NOT IN ('WARNING', 'ERROR'))) THEN
242 l_progress := '30';
243 BEGIN
244 SELECT 'ERROR'
245 INTO l_outcome
246 FROM dual
247 WHERE EXISTS (SELECT 1
248 FROM rcv_transactions_interface
249 WHERE group_id = l_group_id
250 AND (transaction_status_code = 'ERROR' OR
251 processing_status_code = 'ERROR'));
252 EXCEPTION
253 WHEN OTHERS THEN
254 -- If no row is found, then leave l_outcome as it is returned
255 -- from fnd_transaction.synchronous
256 NULL;
257 END;
258
259 IF (l_outcome = 'ERROR') THEN
260 x_return_status := fnd_api.g_ret_sts_unexp_error;
261 IF (l_debug = 1) THEN
262 print_debug('rcv_online_request 29.99 finished with error at: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
263 END IF;
264 ELSE
265 IF (l_debug = 1) THEN
266 print_debug('rcv_online_request 30 finished without error at: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
267 END IF;
268 END IF;
269 ELSIF (rc = 1) THEN
270 l_progress := '40';
271 IF (l_debug = 1) THEN
272 print_debug('rcv_online_request 40 finished with error rc = 1 at: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
273 END IF;
274 IF (check_group_id(inv_rcv_common_apis.g_rcv_global_var.interface_group_id)) THEN
275 fnd_message.set_name('FND', 'TM-TIMEOUT');
276 --x_str := fnd_message.get;
277 --fnd_message.clear;
278 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
279 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
280 fnd_message.set_token('REQUEST', inv_rcv_common_apis.g_rcv_global_var.interface_group_id);
281 fnd_message.set_token('REASON', x_str);
282 --fnd_message.clear;
283 fnd_msg_pub.ADD;
284
285 /* See the commnets below from ATG for timeout */
286 --DELETE_ROWS := TRUE;
287
288 END IF;
289
290 l_progress := '50';
291 x_return_status := fnd_api.g_ret_sts_error;
292
293 --x_msg_data := FND_MESSAGE.get;
294
295 -- Since IF TIMEOUT Happens the Return comes back to client
296 -- But server Process (i,e TM) may be still working in the Background and
297 -- eventually commit
298 --
299 -- Update from ATG
300 /* Rolling back on the client side after a timeout will have no effect on what
301 the transaction manager is processing on the server.
302 Remember that the client side process sends the transaction to the transaction
303 manager and then waits for a response. If the response does not come within
304 the timeout period, the client returns a timeout error. However this does not
305 affect the server-side transaction, which will continue to process data until
306 it finishes.
307 If you have a heavy load and are seeing too many timeouts, you should increase
308 the timeout value you are using.
309 */
310
311 -- inv_receiving_transaction.txn_complete
312 -- (p_group_id => inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
313 -- p_txn_status => 'FALSE',
314 -- p_txn_mode => 'ONLINE',
315 -- x_return_status => l_return_status,
316 -- x_msg_data => l_message,
317 -- x_msg_count => l_msg_count);
318
319 l_progress := '60';
320
321 ELSIF (rc = 2) THEN
322 -- txn_failure_clean_up;
323 l_progress := '70';
324 IF (l_debug = 1) THEN
325 print_debug('rcv_online_request 70 finished with error rc = 2 at: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
326 END IF;
327 IF (check_group_id(inv_rcv_common_apis.g_rcv_global_var.interface_group_id)) THEN
328 fnd_message.set_name('FND', 'TM-SVC LOCK HANDLE FAILED');
329 --x_str := fnd_message.get;
330 --fnd_message.clear;
331 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
332 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
333 fnd_message.set_token('REQUEST', inv_rcv_common_apis.g_rcv_global_var.interface_group_id);
334 fnd_message.set_token('REASON', x_str);
335 --fnd_message.clear;
336 fnd_msg_pub.ADD;
337 DELETE_ROWS := TRUE;
338 END IF;
339
340 x_return_status := fnd_api.g_ret_sts_error;
341 --x_msg_data := FND_MESSAGE.get;
342 l_progress := '80';
343
344 inv_receiving_transaction.txn_complete
345 (p_group_id => inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
346 p_txn_status => 'FALSE',
347 p_txn_mode => 'ONLINE',
348 x_return_status => l_return_status,
349 x_msg_data => l_message,
350 x_msg_count => l_msg_count);
351
352 END IF;
353 /* Bug 4901912 -Modified the ELSIF condition by ending the previous IF clause
354 so that both the cases of rc=3 or l_outcome warning or error is handled.*/
355 IF (rc = 3 or (l_outcome IN ('WARNING', 'ERROR'))) THEN
356 l_progress := '90';
357 IF (l_debug = 1) THEN
358 print_debug('rcv_online_request 90 finished with error rc = 3 for:'||inv_rcv_common_apis.g_rcv_global_var.interface_group_id||' AT : '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
359 END IF;
360
361 -- txn_failure_clean_up;
362 IF (check_group_id(inv_rcv_common_apis.g_rcv_global_var.interface_group_id)) THEN
363 IF (l_debug = 1) THEN
364 print_debug('rcv_online_request 90.01 found the group id',4);
365 END IF;
366 l_progress := '95';
367 rc := fnd_transaction.get_values
368 (
369 r_val1, r_val2, r_val3, r_val4, r_val5,
370 r_val6, r_val7, r_val8, r_val9, r_val10,
371 r_val11, r_val12, r_val13, r_val14, r_val15,
372 r_val16, r_val17, r_val18, r_val19, r_val20
373 );
374
375 l_progress := '100';
376 x_str := r_val1;
377 -- FND_MESSAGE.SET_STRING(x_str);
378 --FND_MESSAGE.CLEAR;
379 IF (r_val2 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val2; END IF;
380 IF (r_val3 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val3; END IF;
381 IF (r_val4 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val4; END IF;
382 IF (r_val5 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val5; END IF;
383 IF (r_val6 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val6; END IF;
384 IF (r_val7 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val7; END IF;
385 IF (r_val8 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val8; END IF;
386 IF (r_val9 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val9; END IF;
387 l_progress := '105';
388 IF (r_val10 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val10; END IF;
389 IF (r_val11 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val11; END IF;
390 IF (r_val12 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val12; END IF;
391 IF (r_val13 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val13; END IF;
392 IF (r_val14 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val14; END IF;
393 IF (r_val15 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val15; END IF;
394 IF (r_val16 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val16; END IF;
395 IF (r_val17 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val17; END IF;
396 IF (r_val18 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val18; END IF;
397 IF (r_val19 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val19; END IF;
398 IF (r_val20 IS NOT NULL) THEN x_str := x_str || fnd_global.local_chr(10) || r_val20; END IF;
399
400 l_progress := '107';
401 IF (l_debug = 1) THEN
402 print_debug('rcv_online_request 90.1 finished with error :'||x_str, 1);
403 END IF;
404 l_progress := '108';
405
406 DELETE_ROWS := TRUE;
407 END IF;
408
409 x_return_status := fnd_api.g_ret_sts_error;
410 --x_msg_data := FND_MESSAGE.get;
411
412 l_progress := '109';
413 inv_receiving_transaction.txn_complete
414 (p_group_id => inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
415 p_txn_status => 'FALSE',
416 p_txn_mode => 'ONLINE',
417 x_return_status => l_return_status,
418 x_msg_data => l_message,
419 x_msg_count => l_msg_count);
420
421 l_progress := '110';
422
423 END IF;
424
425 IF (DELETE_ROWS) THEN
426 BEGIN
427
428 /* Bug# 6081470
429 * Commented out the below update statement as the same record set
430 * is getting deleted below
431 */
432 /* UPDATE rcv_transactions_interface
433 SET processing_status_code = 'COMPLETED'
434 , transaction_status_code = 'ERROR'
435 WHERE group_id = inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id;
436 */
437
438 --No need to delete the record in RTI. FPJ enhancement
439 --delete from rcv_transactions_interface
440 --where group_id = inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id;
441
442 /* Bug 4901912 -Deleting the errored RTIs */
443 IF (l_debug = 1) THEN
444 print_debug('INV_RCV_MOBILE_PROCESS_TXN.rcv_online_request in delete rows for group_id:'
445 || inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id,4);
446 END IF;
447
448 delete from rcv_transactions_interface
449 where group_id = inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id;
450
451 /* Bug# 6081470
452 * Added code to also delete the rcv_headers_interface records as the
453 * corresponding rcv_transactions_interface records are being deleted above.
454 * Hence this becomes an orphan RHI record and there is no use of this record.
455 */
456 delete from rcv_headers_interface
457 where group_id = inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id;
458
459 /* End of fix for Bug 4901912 */
460
461 EXCEPTION
462 WHEN OTHERS THEN NULL;
463 END;
464 END IF;
465 COMMIT;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 x_return_status := fnd_api.g_ret_sts_unexp_error ;
470 IF SQLCODE IS NOT NULL THEN
471 inv_mobile_helper_functions.sql_error('INV_RCV_MOBILE_PROCESS_TXN.rcv_online_request', l_progress, SQLCODE);
472 END IF;
473 IF (l_debug = 1) THEN
474 print_debug('INV_RCV_MOBILE_PROCESS_TXN.rcv_online_request exception:'||l_progress,4);
475 END IF;
476 END rcv_online_request;
477
478
479 PROCEDURE rcv_process_receive_txn(x_return_status OUT nocopy VARCHAR2,
480 x_msg_data OUT nocopy VARCHAR2)
481 IS
482 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
483 l_msg_count NUMBER;
484 l_msg_data VARCHAR2(400);
485 l_progress VARCHAR2(10);
486 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
487 BEGIN
488 x_return_status := fnd_api.g_ret_sts_success;
489 IF (l_debug = 1) THEN
490 print_debug('rcv_process_receive_txn 10: '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
491 END IF;
492 l_progress := '10';
493
494 -- calling lpn_pack_complete api to clear the LPN weight/volume pl/sql table
495 IF wms_install.check_install(l_return_status, l_msg_count, l_msg_data, NULL) THEN
496 IF wms_container_pub.lpn_pack_complete(0) THEN
497 NULL;
498 END IF;
499 END IF;
500
501
502 -- We will remove this later ??
503 --inv_rcv_common_apis.g_po_startup_value.transaction_mode := 'ONLINE';
504
505 IF Upper(inv_rcv_common_apis.g_po_startup_value.transaction_mode) = 'IMMEDIATE' OR
506 Upper(inv_rcv_common_apis.g_po_startup_value.transaction_mode) = 'BATCH' THEN
507
508 -- Call rma API First
509 -- This API returns without doing anything if the source is not customer
510
511 -- *****************************
512 -- This call is commented in patchsetJ as this is not needed anymore
513 -- because Receiving TM would call it for immediate and batch mode also.
514 --
515 --rcv_update_rma_info(inv_rcv_common_apis.g_rcv_global_var.interface_group_id,
516 -- x_return_status ,
517 -- x_msg_data );
518 -- l_progress := '10.1';
519
520 --IF l_return_status = FND_API.g_ret_sts_error THEN
521 -- FND_MESSAGE.SET_NAME('INV', 'INV_RCV_IMMEDIATE_TXN_FAIL');
522 -- FND_MSG_PUB.ADD;
523 -- IF (l_debug = 1) THEN
524 -- print_debug('rcv_process_receive_txn 10.1: rcv_immediate_transaction RAISE FND_API.G_EXC_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
525 -- END IF;
526 -- RAISE FND_API.G_EXC_ERROR;
527 -- END IF;
528 --
529 -- IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
530 -- FND_MESSAGE.SET_NAME('INV', 'INV_RCV_IMMEDIATE_TXN_FAIL');
531 -- FND_MSG_PUB.ADD;
532 -- IF (l_debug = 1) THEN
533 -- print_debug('rcv_process_receive_txn 10.2: rcv_immediate_transaction RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
534 -- END IF;
535 -- RAISE FND_API.g_exc_unexpected_error;
536 -- END IF;
537 -- ************************************
538
539
540 /*Bug#8293126.Need to make sure all RTIs are with mode IMMEDIATE */
541 IF ( NVL(inv_rcv_common_apis.g_rcv_global_var.interface_group_id,-999) <> -999 ) THEN
542 UPDATE RCV_TRANSACTIONS_INTERFACE
543 SET PROCESSING_MODE_CODE = 'IMMEDIATE'
544 WHERE GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
545 AND PROCESSING_MODE_CODE <> 'IMMEDIATE';
546 END IF;
547
548 rcv_immediate_transaction
549 (x_return_status => l_return_status,
550 x_msg_data => l_msg_data);
551
552 l_progress := '20';
553
554 IF l_return_status = FND_API.g_ret_sts_error THEN
555 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_IMMEDIATE_TXN_FAIL');
556 FND_MSG_PUB.ADD;
557 IF (l_debug = 1) THEN
558 print_debug('rcv_process_receive_txn 20.1: rcv_immediate_transaction RAISE FND_API.G_EXC_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
559 END IF;
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562
563
564 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
565 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_IMMEDIATE_TXN_FAIL');
566 FND_MSG_PUB.ADD;
567 IF (l_debug = 1) THEN
568 print_debug('rcv_process_receive_txn 20.2: rcv_immediate_transaction RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
569 END IF;
570 RAISE FND_API.g_exc_unexpected_error;
571 END IF;
572
573 ELSE
574 l_progress := '30';
575 COMMIT;
576 rcv_online_request(x_return_status => l_return_status,
577 x_msg_data => l_msg_data);
578
579 IF l_return_status = FND_API.g_ret_sts_error THEN
580 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_ONLINE_TXN_FAIL');
581 FND_MSG_PUB.ADD;
582 IF (l_debug = 1) THEN
583 print_debug('rcv_process_receive_txn 30.1: rcv_online_request RAISE FND_API.G_EXC_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
584 END IF;
585 RAISE FND_API.G_EXC_ERROR;
586 END IF;
587
588
589 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
590 FND_MESSAGE.SET_NAME('INV', 'INV_RCV_ONLINE_TXN_FAIL');
591 FND_MSG_PUB.ADD;
592 IF (l_debug = 1) THEN
593 print_debug('rcv_process_receive_txn 30.2: rcv_online_request RAISE FND_API.G_EXC_UNEXPECTED_ERROR;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
594 END IF;
595 RAISE FND_API.g_exc_unexpected_error;
596 END IF;
597
598 COMMIT;
599 END IF;
600
601 IF l_return_status = fnd_api.g_ret_sts_success THEN
602 rcv_print_traveller;
603 END IF;
604 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := '';
605
606 EXCEPTION
607 WHEN fnd_api.g_exc_error THEN
608 rcv_print_traveller;
609
610 x_return_status := fnd_api.g_ret_sts_error;
611
612 fnd_msg_pub.count_and_get
613 (p_encoded => FND_API.g_false,
614 p_count => l_msg_count,
615 p_data => x_msg_data
616 );
617
618 WHEN fnd_api.g_exc_unexpected_error THEN
619 rcv_print_traveller;
620
621 x_return_status := fnd_api.g_ret_sts_unexp_error ;
622
623 fnd_msg_pub.count_and_get
624 (p_encoded => FND_API.g_false,
625 p_count => l_msg_count,
626 p_data => x_msg_data
627 );
628
629 WHEN OTHERS THEN
630 rcv_print_traveller;
631
632 x_return_status := fnd_api.g_ret_sts_unexp_error ;
633
634 IF SQLCODE IS NOT NULL THEN
635 inv_mobile_helper_functions.sql_error('INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn', l_progress, SQLCODE);
636 END IF;
637 fnd_msg_pub.count_and_get
638 (p_encoded => FND_API.g_false,
639 p_count => l_msg_count,
640 p_data => x_msg_data
641 );
642
643 END rcv_process_receive_txn;
644 --start of 8539263 changes
645 Procedure lot_uom_conversion( p_org_id IN NUMBER
646 , p_itemid IN NUMBER
647 , p_from_uom_code IN VARCHAR2
648 , p_to_uom_code IN VARCHAR2
649 , p_lot_number IN VARCHAR2
650 , p_user_response IN NUMBER
651 , p_create_lot_uom_conv IN NUMBER
652 , p_conversion_rate IN NUMBER
653 , x_return_status OUT nocopy VARCHAR2,
654 x_msg_data OUT nocopy VARCHAR2
655
656 ) IS
657 l_action_type VARCHAR2(1);
658 l_create_lot_uom_conv INTEGER := 0;
659 l_from_unit_of_measure VARCHAR2(25);
660 l_from_uom_class VARCHAR2(10);
661 l_to_unit_of_measure VARCHAR2(25);
662 l_to_uom_class VARCHAR2(10);
663 l_go BOOLEAN := FALSE;
664 l_sequence NUMBER;
665 l_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE;
666 l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type;
667 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
668 l_return_status VARCHAR2(1);
669 l_msg_count NUMBER;
670 l_msg_data VARCHAR2(2000);
671 l_lot_exist NUMBER;
672 BEGIN
673 IF (l_debug = 1) THEN
674 print_debug('In create_lot_uom_conversion,calling check existing lot', 1);
675 END IF;
676
677
678 BEGIN
679 SELECT unit_of_measure_tl, uom_class
680 INTO l_from_unit_of_measure, l_from_uom_class
681 FROM MTL_UNITS_OF_MEASURE
682 WHERE UOM_CODE = p_from_uom_code;
683 EXCEPTION
684 WHEN OTHERS THEN
685 l_from_unit_of_measure := NULL;
686 l_from_uom_class := NULL;
687 END;
688 BEGIN
689 SELECT unit_of_measure_tl, uom_class
690 INTO l_to_unit_of_measure, l_to_uom_class
691 FROM MTL_UNITS_OF_MEASURE
692 WHERE UOM_CODE = p_to_uom_code;
693 EXCEPTION
694 WHEN OTHERS THEN
695 l_to_unit_of_measure := NULL;
696 l_to_uom_class := NULL;
697 END;
698
699 IF NVL(p_create_lot_uom_conv, 2 ) IN (1,3) THEN
700 IF NVL(p_create_lot_uom_conv, 2 ) = 1
701 AND l_from_uom_class <> l_to_uom_class THEN
702 l_go := TRUE;
703 ELSIF NVL(p_create_lot_uom_conv,2 ) = 3
704 AND l_from_uom_class <> l_to_uom_class THEN
705 IF p_user_response = 0 THEN
706 l_go := TRUE;
707 ELSE
708 l_go := FALSE;
709 END IF;
710 END IF;
711 ELSE
712 l_go := FALSE;
713 END IF;
714 IF l_go THEN
715 l_lot_uom_conv_rec.conversion_id := NULL;
716 l_lot_uom_conv_rec.lot_number := p_lot_number;
717 l_lot_uom_conv_rec.organization_id := p_org_id;
718 l_lot_uom_conv_rec.inventory_item_id := p_itemid;
719 l_lot_uom_conv_rec.from_unit_of_measure := l_from_unit_of_measure;
720 l_lot_uom_conv_rec.from_uom_code := p_from_uom_code;
721 l_lot_uom_conv_rec.from_uom_class := l_from_uom_class;
722 l_lot_uom_conv_rec.to_unit_of_measure := l_to_unit_of_measure;
723 l_lot_uom_conv_rec.to_uom_code := p_to_uom_code;
724 l_lot_uom_conv_rec.to_uom_class := l_to_uom_class;
725 l_lot_uom_conv_rec.conversion_rate := p_conversion_rate;
726 l_lot_uom_conv_rec.disable_date := NULL;
727 l_lot_uom_conv_rec.event_spec_disp_id := NULL;
728 l_lot_uom_conv_rec.created_by := FND_GLOBAL.user_id;
729 l_lot_uom_conv_rec.creation_date := SYSDATE;
730 l_lot_uom_conv_rec.last_updated_by := FND_GLOBAL.user_id;
731 l_lot_uom_conv_rec.last_update_date := SYSDATE;
732 l_lot_uom_conv_rec.last_update_login := FND_GLOBAL.login_id;
733 l_lot_uom_conv_rec.request_id := NULL;
734 l_lot_uom_conv_rec.program_application_id := NULL;
735 l_lot_uom_conv_rec.program_id := NULL;
736 l_lot_uom_conv_rec.program_update_date := NULL;
737 l_action_type := 'I';
738
739 MTL_LOT_UOM_CONV_PUB.CREATE_LOT_UOM_CONVERSION
740 (
741 p_api_version => 1.0
742 , p_init_msg_list => 'T'
743 , p_commit => 'F'
744 , p_validation_level => 100
745 , p_action_type => l_action_type
746 , p_update_type_indicator => 5
747 , p_reason_id => NULL
748 , p_batch_id => 0
749 , p_process_data => 'Y'
750 , p_lot_uom_conv_rec => l_lot_uom_conv_rec
751 , p_qty_update_tbl => l_qty_update_tbl
752 , x_return_status => l_return_status
753 , x_msg_count => l_msg_count
754 , x_msg_data => l_msg_data
755 , x_sequence => l_sequence
756 );
757 IF x_return_status = fnd_api.g_ret_sts_error THEN
758 RAISE fnd_api.g_exc_error;
759 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
760 RAISE fnd_api.g_exc_unexpected_error;
761 ELSE
762 l_return_status:= fnd_api.g_ret_sts_success;
763 END IF;
764 END IF;
765
766 x_return_status:= l_return_status;
767
768
769 EXCEPTION
770 WHEN fnd_api.g_exc_error THEN
771 x_return_status := fnd_api.g_ret_sts_error;
772
773 WHEN fnd_api.g_exc_unexpected_error THEN
774 x_return_status := fnd_api.g_ret_sts_unexp_error ;
775
776 END lot_uom_conversion;
777 PROCEDURE check_existing_lot
778 ( p_org_id IN NUMBER
779 , p_item_id IN NUMBER
780 , p_lot_number IN VARCHAR2
781 , x_lot_exist OUT NOCOPY NUMBER
782 , x_return_status OUT NOCOPY VARCHAR2
783 , x_msg_data OUT NOCOPY VARCHAR2
784 ) IS
785 l_exists NUMBER := 0;
786 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
787 BEGIN
788 IF (l_debug = 1) THEN
789 print_debug('In check_existing_lot', 1);
790 END IF;
791 IF p_lot_number IS NOT NULL THEN
792
793 BEGIN
794 SELECT 1
795 INTO l_exists
796 FROM mtl_lot_numbers
797 WHERE inventory_item_id = p_item_id
798 AND organization_id = p_org_id
799 AND lot_number = p_lot_number
800 AND ROWNUM = 1;
801 EXCEPTION
802 WHEN no_data_found THEN
803 BEGIN
804 SELECT 1
805 INTO l_exists
806 FROM mtl_transaction_lots_temp a
807 , mtl_material_transactions_temp b
808 WHERE b.inventory_item_id = p_item_id
809 AND a.lot_number = p_lot_number
810 AND a.transaction_temp_id = b.transaction_temp_id
811 AND rownum = 1
812 AND b.organization_id = p_org_id;
813 EXCEPTION
814 WHEN no_data_found THEN
815 BEGIN
816 SELECT 1
817 INTO l_exists
818 FROM mtl_material_transactions_temp mtl
819 WHERE mtl.inventory_item_id <> p_item_id
820 AND mtl.lot_number = p_lot_number
821 AND mtl.organization_id = p_org_id
822 AND rownum = 1;
823 EXCEPTION
824 WHEN no_data_found THEN
825 l_exists := 0;
826 END;
827 END;
828 END;
829 IF l_exists <> 0 THEN
830 x_lot_exist := 1;
831 ELSE
832 x_lot_exist := 0;
833 END IF;
834 END IF;
835 x_return_status := fnd_api.g_ret_sts_success;
836 END check_existing_lot;
837 --end of 8539263 changes
838 END INV_RCV_MOBILE_PROCESS_TXN;
839