[Home] [Help]
PACKAGE BODY: APPS.WMS_ATF_UTIL_APIS
Source
1 PACKAGE BODY WMS_ATF_Util_APIs AS
2 /* $Header: WMSOPUTB.pls 120.0.12020000.2 2012/07/04 06:35:08 abasheer ship $ */
3
4 g_ret_sts_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
5 g_ret_sts_unexp_error VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
6 g_ret_sts_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
7 G_ACTION_RECEIPT CONSTANT NUMBER := inv_globals.g_action_receipt ;
8 G_ACTION_INTRANSITRECEIPT CONSTANT NUMBER := inv_globals.G_ACTION_INTRANSITRECEIPT;
9 G_ACTION_SUBXFR CONSTANT NUMBER := inv_globals.g_action_subxfr;
10 G_SOURCETYPE_MOVEORDER CONSTANT NUMBER := inv_globals.g_sourcetype_moveorder;
11 G_SOURCETYPE_PURCHASEORDER CONSTANT NUMBER := inv_globals.G_SOURCETYPE_PURCHASEORDER;
12 G_SOURCETYPE_INTREQ CONSTANT NUMBER := inv_globals.G_SOURCETYPE_INTREQ;
13 G_SOURCETYPE_RMA CONSTANT NUMBER := inv_globals.G_SOURCETYPE_RMA;
14 G_SOURCETYPE_INVENTORY CONSTANT NUMBER := inv_globals.G_SOURCETYPE_INVENTORY;
15
16 PROCEDURE print_debug(p_err_msg VARCHAR2,
17 p_level NUMBER)
18 IS
19 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
20 BEGIN
21
22 inv_mobile_helper_functions.tracelog
23 (p_err_msg => p_err_msg,
24 p_module => 'WMS_ATF_Util_APIs',
25 p_level => p_level);
26
27
28 -- dbms_output.put_line(p_err_msg);
29 END print_debug;
30
31 PROCEDURE assign_operation_plan
32 (
33 p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 DEFAULT 'F',
35 p_commit IN VARCHAR2 DEFAULT 'F',
36 p_validation_level IN NUMBER DEFAULT 100,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 p_task_id IN NUMBER,
41 p_activity_type_id IN NUMBER DEFAULT NULL,
42 p_organization_id IN NUMBER DEFAULT NULL
43 )
44 IS
45 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
46 l_return_status VARCHAR2(1);
47 l_msg_count NUMBER;
48 l_msg_data VARCHAR2(400);
49 l_progress NUMBER;
50 BEGIN
51
52 x_return_status := g_ret_sts_success;
53
54
55 IF (l_debug = 1) THEN
56 print_debug('Entering assign_operation_plan ', 1);
57 print_debug('p_api_version = '||p_api_version, 1);
58 print_debug('p_init_msg_list = '||p_init_msg_list, 1);
59 print_debug('p_commit = '||p_commit, 1);
60 print_debug('p_validation_level = '||p_validation_level, 1);
61 print_debug('p_task_id = '||p_task_id, 1);
62 print_debug('p_activity_type_id = '||p_activity_type_id, 1);
63 print_debug('p_organization_id = '||p_organization_id, 1);
64
65 print_debug('Before calling wms_rule_pvt_ext_psetj.assign_operation_plan_psetj ', 1);
66
67 END IF;
68
69 l_progress := 10;
70 SAVEPOINT sp_assign_operation_plan_intf;
71
72 wms_rule_pvt_ext_psetj.assign_operation_plan_psetj
73 (
74 p_api_version => p_api_version,
75 p_init_msg_list => p_init_msg_list,
76 p_commit => p_commit,
77 p_validation_level => p_validation_level,
78 x_return_status => l_return_status,
79 x_msg_count => l_msg_count,
80 x_msg_data => l_msg_data,
81 p_task_id => p_task_id,
82 p_activity_type_id => p_activity_type_id,
83 p_organization_id => p_organization_id
84 );
85
86 l_progress := 20;
87
88 IF (l_debug = 1) THEN
89
90 print_debug('After calling wms_rule_pvt_ext_psetj.assign_operation_plan_psetj ', 1);
91
92 print_debug('x_return_status = '||l_return_status, 4);
93 print_debug('x_msg_count = '||l_msg_count, 4);
94 print_debug('x_msg_data = '||l_msg_data, 4);
95 END IF;
96
97 IF x_return_status <>FND_API.g_ret_sts_success THEN
98 IF (l_debug=1) THEN
99 print_debug('wms_rule_pvt_ext_psetj.assign_operation_plan_psetj finished with error. l_return_status = ' || l_return_status,4);
100 END IF;
101
102 RAISE FND_API.G_EXC_ERROR;
103 END IF;
104
105
106 IF (l_debug = 1) THEN
107
108 print_debug('Exiting assign_operation_plan ', 1);
109
110 END IF;
111
112
113 EXCEPTION
114 WHEN FND_API.G_EXC_ERROR THEN
115 x_return_status:=FND_API.G_RET_STS_ERROR;
116 IF (l_debug=1) THEN
117 print_debug('assign_operation_plan expected Error Obtained at'||l_progress,1);
118 END IF;
119 ROLLBACK TO sp_assign_operation_plan_intf;
120
121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
122 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
123 IF (l_debug=1) THEN
124 print_debug('assign_operation_plan Unexpected Error Obtained at'||l_progress,1);
125 END IF;
126 ROLLBACK TO sp_assign_operation_plan_intf;
127
128 WHEN OTHERS THEN
129 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
130 IF (l_debug=1) THEN
131 print_debug('assign_operation_plan OTHER Error Obtained at'||l_progress,1);
132 IF SQLCODE IS NOT NULL THEN
133 print_debug('With SQL error : ' || SQLERRM(SQLCODE), 1);
134 END IF;
135 END IF;
136 ROLLBACK TO sp_assign_operation_plan_intf;
137
138 END assign_operation_plan;
139
140
141
142 /**
143 * complete_tm_processing
144 *
145 * <p>This API conlcudes the exeuction of an operation plan.</P>
146 *
147 * <p>Inventory transaction manager should call this API:
148 * 1. After processing a transaction;
149 * 2. Before deleting the MMTT record;
150 * 3. WHen MMTT.operation_plan_ID IS NOT NULL. </P>
151 *
152 *
153 * @param x_return_status -Return Status
154 * @param x_msg_data -Returns the Error message Data
155 * @param x_msg_count -Returns the message count
156 * @param p_organization_id -Organization ID
157 * @param p_txn_header_id -MMTT.transaction_header_id (passed when TM fails to process one MMTT within a batch)
158 * @param p_txn_batch_id -MMTT.transaction_batch_id (passed when TM fails to process one MMTT within a batch)
159 * @param p_transaction_temp_id -MMTT.transaction_temp_id (passed when TM successfully processed one MMTT)
160 * @param p_tm_complete_status -Return status of TM processing: 0 - success, else failure
161 * @param p_txn_processing_mode -Mode in which TM was called: 1 - online, 2 - background, 3 - concurrent
162
163 **/
164
165 PROCEDURE complete_tm_processing
166 (
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 p_organization_id IN NUMBER,
171 p_txn_header_id IN NUMBER DEFAULT NULL,
172 p_txn_batch_id IN NUMBER DEFAULT NULL,
173 p_transaction_temp_id IN NUMBER DEFAULT NULL,
174 p_tm_complete_status IN NUMBER,
175 p_txn_processing_mode IN NUMBER
176 )
177 IS
178 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
179 l_progress NUMBER;
180 l_return_status VARCHAR2(1);
181 l_msg_count NUMBER;
182 l_msg_data VARCHAR2(400);
183 l_atf_error_code NUMBER;
184 l_proc_mode NUMBER := p_txn_processing_mode;
185 l_transaction_temp_id NUMBER;
186 l_is_deliver_txn_flag VARCHAR2(1) := 'N';
187 l_rcv_routing NUMBER;
188 l_wdt_exists NUMBER :=0;
189
190
191 CURSOR c_txn_batch_inbound_mmtts
192 IS
193 SELECT
194 transaction_temp_id,
195 transaction_action_id,
196 transaction_source_type_id,
197 parent_line_id,
198 rcv_transaction_id
199 FROM mtl_material_transactions_temp
200 WHERE transaction_header_id = p_txn_header_id
201 AND Nvl(transaction_batch_id, -999) = Nvl(p_txn_batch_id, Nvl(transaction_batch_id, -999))
202 -- AND transaction_temp_id = Nvl(p_transaction_temp_id, transaction_temp_id
203 AND wms_task_type IN (2, -1) -- putaway
204 ;
205
206 -- IMPORTANT
207 -- Need return exatly same columns as c_txn_batch_inbound_mmtts but transaction temp ID passed
208
209 CURSOR c_inbound_mmtts_tmp_id
210 IS
211 SELECT
212 transaction_temp_id,
213 transaction_action_id,
214 transaction_source_type_id,
215 parent_line_id,
216 rcv_transaction_id
217 FROM mtl_material_transactions_temp
218 -- WHERE transaction_header_id = p_txn_header_id
219 -- AND Nvl(transaction_batch_id, -999) = Nvl(p_txn_batch_id, Nvl(transaction_batch_id, -999))
220 WHERE transaction_temp_id = p_transaction_temp_id
221 AND wms_task_type IN (2, -1) -- putaway
222 ;
223
224 l_txn_batch_inbound_mmtt_rec c_txn_batch_inbound_mmtts%ROWTYPE;
225
226 CURSOR c_rcv_routing (v_rt_transaction_id NUMBER )
227 IS
228 SELECT routing_header_id,user_entered_flag
229 FROM rcv_transactions
230 WHERE transaction_id = v_rt_transaction_id;
231
232 -- For those non-ATF transactions
233 -- if the transaction happens (pack/unpack, misc. txn) to an LPN that contains mmtt belongs to operation plan
234 -- need to cancel these operation plans.
235 l_rcv_routing_rec c_rcv_routing%ROWTYPE;
236 CURSOR c_lpns_to_cancel
237 IS
238 SELECT lpn_id, content_lpn_id, transfer_lpn_id
239 FROM mtl_material_transactions_temp
240 WHERE transaction_temp_id = p_transaction_temp_id
241 AND (wms_task_type NOT IN (2, -1)
242 OR wms_task_type IS NULL);
243 l_lpns_to_cancel_rec c_lpns_to_cancel%ROWTYPE;
244
245 CURSOR c_mmtts_in_lpn(v_lpn_id NUMBER)
246 IS
247 SELECT transaction_temp_id
248 FROM mtl_material_transactions_temp
249 WHERE lpn_id = v_lpn_id
250 AND operation_plan_id IS NOT NULL
251 AND wms_task_type IN (2, -1);
252
253 l_mmtt_to_cancel_rec c_mmtts_in_lpn%ROWTYPE;
254
255
256 BEGIN
257
258 x_return_status := g_ret_sts_success;
259
260 IF (l_debug = 1) THEN
261 print_debug('Entering complete_tm_processing ', 1);
262 print_debug('p_organization_id = '||p_organization_id, 4);
263 print_debug('p_txn_header_id = '||p_txn_header_id, 4);
264 print_debug('p_txn_batch_id = '||p_txn_batch_id, 4);
265 print_debug('p_tm_complete_status = '||p_tm_complete_status, 4);
266 print_debug('p_txn_processing_mode = '||p_txn_processing_mode, 4);
267 print_debug('p_transaction_temp_id= '||p_transaction_temp_id, 4);
268 END IF;
269
270 l_progress := 10;
271 SAVEPOINT sp_complete_tm_processing;
272 l_progress := 20;
273
274
275 -- For those non-ATF transactions
276 -- if the transaction happens (pack/unpack, misc. txn) to an LPN that contains mmtts belongs to operation plan
277 -- need to cancel those operation plans.
278
279 IF p_transaction_temp_id IS NOT NULL THEN
280 OPEN c_lpns_to_cancel;
281 LOOP
282 FETCH c_lpns_to_cancel
283 INTO l_lpns_to_cancel_rec;
284 EXIT WHEN c_lpns_to_cancel%notfound;
285 l_progress := 20.001;
286 IF (l_debug = 1) THEN
287 print_debug('This is a normal MMTT, need to make sure operation plan affected by LPN being cancelled. ', 4);
288 print_debug('l_lpns_to_cancel_rec.transfer_lpn_id = '||l_lpns_to_cancel_rec.transfer_lpn_id , 4);
289 print_debug('l_lpns_to_cancel_rec.content_lpn_id = '||l_lpns_to_cancel_rec.content_lpn_id , 4);
290 print_debug('l_lpns_to_cancel_rec.lpn_id = '||l_lpns_to_cancel_rec.lpn_id , 4);
291 END IF;
292
293 IF l_lpns_to_cancel_rec.transfer_lpn_id IS NOT NULL THEN
294 OPEN c_mmtts_in_lpn(l_lpns_to_cancel_rec.transfer_lpn_id);
295 LOOP
296 FETCH c_mmtts_in_lpn
297 INTO l_mmtt_to_cancel_rec;
298 EXIT WHEN c_mmtts_in_lpn%notfound;
299 l_progress := 20.002;
300
301 IF (l_debug = 1) THEN
302 print_debug('Need to cancel plan for transfer_lpn_id '||l_lpns_to_cancel_rec.transfer_lpn_id, 4);
303 print_debug('Before calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan with following params:', 4);
304 print_debug('p_source_task_id => '|| l_mmtt_to_cancel_rec.transaction_temp_id, 4);
305 print_debug('p_activity_type_id => '|| 1, 4);
306 END IF;
307
308 wms_atf_runtime_pub_apis.cancel_operation_plan
309 (
310 x_return_status => l_return_status,
311 x_msg_data => l_msg_data,
312 x_msg_count => l_msg_count,
313 x_error_code => l_atf_error_code,
314 p_source_task_id => l_mmtt_to_cancel_rec.transaction_temp_id,
315 p_activity_type_id => 1);
316
317 IF (l_debug = 1) THEN
318 print_debug('After calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan:', 4);
319 print_debug('l_return_status => '||l_return_status, 4);
320 print_debug('l_msg_data => '||l_msg_data, 4);
321 print_debug('l_msg_count => '||l_msg_count, 4);
322 END IF;
323
324 IF l_return_status <>FND_API.g_ret_sts_success THEN
325 IF (l_debug=1) THEN
326 print_debug('wms_atf_runtime_pub_apis.Cancel_operation_Plan - transfer_lpn_id finished with error. l_return_status = ' || l_return_status, 4);
327 END IF;
328
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331 l_progress := 20.003;
332
333 END LOOP;
334 CLOSE c_mmtts_in_lpn;
335
336 END IF;
337
338 IF l_lpns_to_cancel_rec.content_lpn_id IS NOT NULL THEN
339 OPEN c_mmtts_in_lpn(l_lpns_to_cancel_rec.content_lpn_id);
340 LOOP
341 FETCH c_mmtts_in_lpn
342 INTO l_mmtt_to_cancel_rec;
343 EXIT WHEN c_mmtts_in_lpn%notfound;
344 l_progress := 20.005;
345 IF (l_debug = 1) THEN
346 print_debug('Need to cancel plan for content_lpn_id '||l_lpns_to_cancel_rec.content_lpn_id, 4);
347 print_debug('Before calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan with following params:', 4);
348 print_debug('p_source_task_id => '|| l_mmtt_to_cancel_rec.transaction_temp_id, 4);
349 print_debug('p_activity_type_id => '|| 1, 4);
350 END IF;
351
352 wms_atf_runtime_pub_apis.cancel_operation_plan
353 (
354 x_return_status => l_return_status,
355 x_msg_data => l_msg_data,
356 x_msg_count => l_msg_count,
357 x_error_code => l_atf_error_code,
358 p_source_task_id => l_mmtt_to_cancel_rec.transaction_temp_id,
359 p_activity_type_id => 1);
360
361 IF (l_debug = 1) THEN
362 print_debug('After calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan:', 4);
363 print_debug('l_return_status => '||l_return_status, 4);
364 print_debug('l_msg_data => '||l_msg_data, 4);
365 print_debug('l_msg_count => '||l_msg_count, 4);
366 END IF;
367
368 IF l_return_status <>FND_API.g_ret_sts_success THEN
369 IF (l_debug=1) THEN
370 print_debug('wms_atf_runtime_pub_apis.Cancel_operation_Plan - content_lpn_id finished with error. l_return_status = ' || l_return_status, 4);
371 END IF;
372
373 RAISE FND_API.G_EXC_ERROR;
374 END IF;
375 l_progress := 20.006;
376
377 END LOOP;
378 CLOSE c_mmtts_in_lpn;
379 END IF;
380
381 IF l_lpns_to_cancel_rec.lpn_id IS NOT NULL THEN
382 OPEN c_mmtts_in_lpn(l_lpns_to_cancel_rec.lpn_id);
383 LOOP
384 FETCH c_mmtts_in_lpn
385 INTO l_mmtt_to_cancel_rec;
386 EXIT WHEN c_mmtts_in_lpn%notfound;
387 l_progress := 20.007;
388
389 IF (l_debug = 1) THEN
390 print_debug('Need to cancel plan for lpn_id '||l_lpns_to_cancel_rec.lpn_id, 4);
391 print_debug('Before calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan with following params:', 4);
392 print_debug('p_source_task_id => '|| l_mmtt_to_cancel_rec.transaction_temp_id, 4);
393 print_debug('p_activity_type_id => '|| 1, 4);
394 END IF;
395
396 wms_atf_runtime_pub_apis.cancel_operation_plan
397 (
398 x_return_status => l_return_status,
399 x_msg_data => l_msg_data,
400 x_msg_count => l_msg_count,
401 x_error_code => l_atf_error_code,
402 p_source_task_id => l_mmtt_to_cancel_rec.transaction_temp_id,
403 p_activity_type_id => 1);
404
405 IF (l_debug = 1) THEN
406 print_debug('After calling WMS_ATF_RUNTIME_PUB_APIS.Cancel_operation_Plan:', 4);
407 print_debug('l_return_status => '||l_return_status, 4);
408 print_debug('l_msg_data => '||l_msg_data, 4);
409 print_debug('l_msg_count => '||l_msg_count, 4);
410 END IF;
411
412 IF l_return_status <>FND_API.g_ret_sts_success THEN
413 IF (l_debug=1) THEN
414 print_debug('wms_atf_runtime_pub_apis.Cancel_operation_Plan - lpn_id finished with error. l_return_status = ' || l_return_status, 4);
415 END IF;
416
417 RAISE FND_API.G_EXC_ERROR;
418 END IF;
419
420 l_progress := 20.008;
421 END LOOP;
422 CLOSE c_mmtts_in_lpn;
423 END IF;
424
425 -- if it ever enters this single LOOP, it is a non-ATF mmtt, simply return from here.
426 IF c_lpns_to_cancel%isopen THEN
427 CLOSE c_lpns_to_cancel;
428 END IF;
429 l_progress := 20.009;
430 IF (l_debug = 1) THEN
431 print_debug('This is a normal MMTT, Return without ATF processing. ', 4);
432 END IF;
433 RETURN;
434 END LOOP;
435 CLOSE c_lpns_to_cancel;
436 END IF;
437
438
439 l_proc_mode := FND_PROFILE.VALUE('TRANSACTION_PROCESS_MODE');
440 IF (l_debug = 1) THEN
441 print_debug('l_proc_mode = '||l_proc_mode, 4);
442 END IF;
443
444 -- Need to add logic to determine processing mode
445 -- As of now Prashat has not decided the approach to do so,
446 -- i.e. call API or copy/paste TM code to here.
447
448 IF l_proc_mode = 4 THEN
449 l_proc_mode := 1;
450 END IF;
451
452 IF p_transaction_temp_id IS NULL THEN
453 OPEN c_txn_batch_inbound_mmtts;
454 ELSE
455 OPEN c_inbound_mmtts_tmp_id;
456 END if;
457
458 LOOP
459 IF p_transaction_temp_id IS NULL THEN
460 FETCH c_txn_batch_inbound_mmtts
461 INTO l_txn_batch_inbound_mmtt_rec;
462
463 EXIT WHEN c_txn_batch_inbound_mmtts%notfound;
464
465 ELSE
466 FETCH c_inbound_mmtts_tmp_id
467 INTO l_txn_batch_inbound_mmtt_rec;
468
469 EXIT WHEN c_inbound_mmtts_tmp_id%notfound;
470
471 END IF;
472
473
474 IF (l_debug = 1) THEN
475 print_debug('l_txn_batch_inbound_mmtt_rec.transaction_temp_id = '||l_txn_batch_inbound_mmtt_rec.transaction_temp_id ,4);
476 print_debug('l_txn_batch_inbound_mmtt_rec.transaction_action_id = '||l_txn_batch_inbound_mmtt_rec.transaction_action_id ,4);
477 print_debug('l_txn_batch_inbound_mmtt_rec.transaction_source_type_id = '||l_txn_batch_inbound_mmtt_rec.transaction_source_type_id ,4);
478 print_debug('l_txn_batch_inbound_mmtt_rec.parent_line_id = '||l_txn_batch_inbound_mmtt_rec.parent_line_id ,4);
479 print_debug('l_txn_batch_inbound_mmtt_rec.rcv_transaction_id = '||l_txn_batch_inbound_mmtt_rec.rcv_transaction_id ,4);
480
481 END IF;
482
483
484 --Bug 3164504
485 -- Complete operation instance need not be called if WDT lines do
486 -- not exist. This is for rcv or wip transactions from the
487 -- desktop AND for all subtransfer transactions
488
489 print_debug('Before checking existence of WDT' ,4);
490
491 BEGIN
492
493 SELECT 1
494 INTO l_wdt_exists
495 FROM DUAL
496 WHERE EXISTS(SELECT 1
497 FROM wms_dispatched_tasks
498 WHERE task_type=2
499 AND (transaction_temp_id = l_txn_batch_inbound_mmtt_rec.transaction_temp_id
500 OR transaction_temp_id =
501 l_txn_batch_inbound_mmtt_rec.parent_line_id));
502
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 l_wdt_exists := 0;
506 END;
507
508 print_debug('After checking existence of WDT' ,4);
509 print_debug('l_wdt_exists='||l_wdt_exists ,4);
510
511
512 IF l_wdt_exists=0 THEN
513 print_debug('WDT does not exist. Returning' ,4);
514
515 IF c_txn_batch_inbound_mmtts%isopen THEN
516 CLOSE c_txn_batch_inbound_mmtts;
517 END IF;
518
519 IF c_inbound_mmtts_tmp_id%isopen THEN
520 CLOSE c_inbound_mmtts_tmp_id;
521 END IF;
522
523 RETURN;
524
525 END IF;
526
527 -- End Bug fix 3164504
528
529
530 IF (l_txn_batch_inbound_mmtt_rec.transaction_action_id = G_ACTION_RECEIPT
531 AND l_txn_batch_inbound_mmtt_rec.transaction_source_type_id = g_sourcetype_purchaseorder
532 -- PO receipt (1, 27)
533 ) OR
534 (l_txn_batch_inbound_mmtt_rec.transaction_action_id = G_ACTION_RECEIPT
535 AND l_txn_batch_inbound_mmtt_rec.transaction_source_type_id = g_sourcetype_rma
536 -- RMA receipt (12, 27)
537 ) OR
538 (l_txn_batch_inbound_mmtt_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
539 AND l_txn_batch_inbound_mmtt_rec.transaction_source_type_id = g_sourcetype_intreq
540 -- Internal REQ
541 )OR
542 (l_txn_batch_inbound_mmtt_rec.transaction_action_id = G_ACTION_INTRANSITRECEIPT
543 AND l_txn_batch_inbound_mmtt_rec.transaction_source_type_id = G_SOURCETYPE_INVENTORY
544 -- Intransit shipment
545 )
546 THEN
547 IF (l_debug = 1) THEN
548 print_debug('complete_tm_processing - this is a deliver transaction ', 4);
549 END IF;
550
551 OPEN c_rcv_routing (l_txn_batch_inbound_mmtt_rec.rcv_transaction_id);
552 FETCH c_rcv_routing INTO l_rcv_routing_rec;
553 CLOSE c_rcv_routing;
554
555
556 IF (l_debug = 1) THEN
557 print_debug('complete_tm_processing - routing_header_id = ' || l_rcv_routing_rec.routing_header_id, 4);
558 print_debug('complete_tm_processing - user_entered_flag = ' || l_rcv_routing_rec.user_entered_flag, 4);
559 END IF;
560
561 IF l_rcv_routing_rec.routing_header_id = 3 AND l_rcv_routing_rec.user_entered_flag = 'N' THEN -- 9949620
562
563 IF (l_debug = 1) THEN
564 print_debug('complete_tm_processing - Direct routing, simply return w/o calling complete_operation_instance ', 4);
565 END IF;
566
567 IF c_txn_batch_inbound_mmtts%isopen THEN
568 CLOSE c_txn_batch_inbound_mmtts;
569 END IF;
570
571 IF c_inbound_mmtts_tmp_id%isopen THEN
572 CLOSE c_inbound_mmtts_tmp_id;
573 END IF;
574
575 RETURN;
576 END IF;
577
578
579 l_transaction_temp_id := l_txn_batch_inbound_mmtt_rec.parent_line_id;
580 l_is_deliver_txn_flag := 'Y';
581 ELSE
582 IF (l_debug = 1) THEN
583 print_debug('complete_tm_processing - this is NOT a deliver transaction ', 4);
584 END IF;
585
586 l_transaction_temp_id := l_txn_batch_inbound_mmtt_rec.transaction_temp_id;
587 l_is_deliver_txn_flag := 'N';
588 END IF;
589
590
591 IF p_tm_complete_status = 0 THEN -- TM completes successfully
592
593
594 IF (l_debug = 1) THEN
595 print_debug('complete_tm_processing - Before calling wms_atf_runtime_pub_apis.complete_operation_instance ', 4);
596
597 print_debug('l_transaction_temp_id = '||l_transaction_temp_id, 4);
598 print_debug('p_activity_id = '||1, 4);
599 print_debug('p_operation_type_id = '||2, 4);
600 END IF;
601
602
603 wms_atf_runtime_pub_apis.complete_operation_instance
604 (
605 x_return_status => l_return_status
606 ,x_msg_data => l_msg_data
607 ,x_msg_count => l_msg_count
608 ,x_error_code => l_atf_error_code
609 ,p_source_task_id => l_transaction_temp_id
610 ,p_activity_id => 1 -- inbound
611 ,p_operation_type_id => 2 -- drop INV TM is always called for a drop operation
612 );
613
614 IF (l_debug = 1) THEN
615
616 print_debug('complete_tm_processing - After calling wms_atf_runtime_pub_apis.complete_operation_instance ', 4);
617
618 print_debug('x_return_status = '||x_return_status, 4);
619 print_debug('x_msg_count = '||x_msg_count, 4);
620 print_debug('x_msg_data = '||x_msg_data, 4);
621
622 END IF;
623
624 IF l_return_status <>FND_API.g_ret_sts_success THEN
625 IF (l_debug=1) THEN
626 print_debug('wms_atf_runtime_pub_apis.complete_operation_instance finished with error. l_return_status = ' || l_return_status, 4);
627 END IF;
628
629 RAISE FND_API.G_EXC_ERROR;
630 END IF;
631
632 ELSE -- TM failed
633 IF l_proc_mode <> 1 -- TM processing mode is NOT online
634 AND l_is_deliver_txn_flag <> 'Y' -- not receiving deliver transaction
635 THEN
636
637 -- Delivery transaction will call cleanup_operation_instance
638 -- from txn_complete of receiving code.
639 -- Because receiving TM calls inventory TM one MMTT after another,
640 -- but if any of them fails, should cleanup the entire batch.
641
642 IF (l_debug = 1) THEN
643 print_debug('complete_tm_processing - Before calling wms_atf_runtime_pub_apis.cleanup_operation_instance ', 4);
644
645 print_debug('l_transaction_temp_id = '||l_transaction_temp_id, 4);
646 print_debug('p_activity_id = '||1, 4);
647
648 END IF;
649
650 wms_atf_runtime_pub_apis.cleanup_operation_instance
651 (
652 x_return_status => l_return_status
653 , x_msg_data => l_msg_data
654 , x_msg_count => l_msg_count
655 , x_error_code => l_atf_error_code
656 , p_source_task_id => l_transaction_temp_id
657 , p_activity_type_id => 1 -- inbound
658 );
659
660 IF (l_debug = 1) THEN
661
662 print_debug('complete_tm_processing - After calling wms_atf_runtime_pub_apis.cleanup_operation_instance ', 4);
663
664 print_debug('x_return_status = '||x_return_status, 4);
665 print_debug('x_msg_count = '||x_msg_count, 4);
666 print_debug('x_msg_data = '||x_msg_data, 4);
667
668 END IF;
669
670 IF l_return_status <>FND_API.g_ret_sts_success THEN
671 IF (l_debug=1) THEN
672 print_debug('wms_atf_runtime_pub_apis.cleanup_operation_instance finished with error. l_return_status = ' || l_return_status, 4);
673 END IF;
674
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678 END IF; -- IF l_proc_mode <> 1
679
680 END IF; -- IF p_tm_complete_status = 0
681
682
683
684 END LOOP; -- c_txn_batch_inbound_mmtts loop
685
686 IF c_txn_batch_inbound_mmtts%isopen THEN
687 CLOSE c_txn_batch_inbound_mmtts;
688 END IF;
689
690 IF c_inbound_mmtts_tmp_id%isopen THEN
691 CLOSE c_inbound_mmtts_tmp_id;
692 END IF;
693
694 IF (l_debug = 1) THEN
695
696 print_debug('x_return_status = '||x_return_status, 4);
697 print_debug('x_msg_count = '||x_msg_count, 4);
698 print_debug('x_msg_data = '||x_msg_data, 4);
699
700 print_debug('Exiting complete_tm_processing ', 1);
701
702 END IF;
703
704
705 EXCEPTION
706 WHEN FND_API.G_EXC_ERROR THEN
707 x_return_status:=FND_API.G_RET_STS_ERROR;
708 IF (l_debug=1) THEN
709 print_debug('complete_tm_processing expected Error Obtained at'||l_progress,1);
710 END IF;
711 ROLLBACK TO sp_complete_tm_processing;
712
713 IF c_txn_batch_inbound_mmtts%isopen THEN
714 CLOSE c_txn_batch_inbound_mmtts;
715 END IF;
716
717 IF c_inbound_mmtts_tmp_id%isopen THEN
718 CLOSE c_inbound_mmtts_tmp_id;
719 END IF;
720
721 IF c_rcv_routing%isopen THEN
722 CLOSE c_rcv_routing;
723 END IF;
724
725 IF c_lpns_to_cancel%isopen THEN
726 CLOSE c_lpns_to_cancel;
727 END IF;
728
729 IF c_mmtts_in_lpn%isopen THEN
730 CLOSE c_mmtts_in_lpn;
731 END IF;
732
733
734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
735 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
736 IF (l_debug=1) THEN
737 print_debug('complete_tm_processing Unexpected Error Obtained at'||l_progress,1);
738 END IF;
739 ROLLBACK TO sp_complete_tm_processing;
740
741 IF c_txn_batch_inbound_mmtts%isopen THEN
742 CLOSE c_txn_batch_inbound_mmtts;
743 END IF;
744
745 IF c_inbound_mmtts_tmp_id%isopen THEN
746 CLOSE c_inbound_mmtts_tmp_id;
747 END IF;
748
749 IF c_rcv_routing%isopen THEN
750 CLOSE c_rcv_routing;
751 END IF;
752
753 IF c_lpns_to_cancel%isopen THEN
754 CLOSE c_lpns_to_cancel;
755 END IF;
756
757 IF c_mmtts_in_lpn%isopen THEN
758 CLOSE c_mmtts_in_lpn;
759 END IF;
760
761
762 WHEN OTHERS THEN
763 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
764 IF (l_debug=1) THEN
765 print_debug('complete_tm_processing OTHER Error Obtained at'||l_progress,1);
766 IF SQLCODE IS NOT NULL THEN
767 print_debug('With SQL error : ' || SQLERRM(SQLCODE), 1);
768 END IF;
769 END IF;
770 ROLLBACK TO sp_complete_tm_processing;
771
772 IF c_txn_batch_inbound_mmtts%isopen THEN
773 CLOSE c_txn_batch_inbound_mmtts;
774 END IF;
775
776 IF c_inbound_mmtts_tmp_id%isopen THEN
777 CLOSE c_inbound_mmtts_tmp_id;
778 END IF;
779
780 IF c_rcv_routing%isopen THEN
781 CLOSE c_rcv_routing;
782 END IF;
783
784 IF c_lpns_to_cancel%isopen THEN
785 CLOSE c_lpns_to_cancel;
786 END IF;
787
788 IF c_mmtts_in_lpn%isopen THEN
789 CLOSE c_mmtts_in_lpn;
790 END IF;
791
792 END complete_tm_processing;
793
794 END WMS_ATF_Util_APIs;