[Home] [Help]
PACKAGE BODY: APPS.WMS_WCS_DEVICE_GRP
Source
1 PACKAGE BODY WMS_WCS_DEVICE_GRP AS
2 /* $Header: WMSWCSB.pls 120.10 2005/10/20 09:28:24 simran noship $ */
3
4 --
5 --
6 PROCEDURE LOG (p_device_id IN NUMBER, p_data IN VARCHAR2);
7
8 /*
9 * Call WMS_Task_Dispatch_Device.get_device_info
10 * And if the return status is "A" then it means that this device is already signed on.
11 * Throw an error WMS_DEVICE_ALREADY_SIGNED. And make the x_return_status of the Open API "E"
12 *
13 * If the return status is "S" then this device is valid.
14 * Call WMS_Task_Dispatch_Device. PROCEDURE insert_device
15 * And make the x_return_status of the Open API "S"
16 */
17 PROCEDURE call_workflow
18 (
19 p_device_id IN NUMBER,
20 p_response_record IN MSG_COMPONENT_LOOKUP_TYPE
21 );
22
23 PROCEDURE DEVICE_SIGN_ON
24 (p_device_id IN NUMBER,
25 p_device_name IN VARCHAR2,
26 p_employee_id IN NUMBER,
27 p_organization_id IN NUMBER,
28 x_device_type OUT NOCOPY VARCHAR2,
29 x_device_desc OUT NOCOPY VARCHAR2,
30 x_subinventory OUT NOCOPY VARCHAR2,
31 x_signon_wrk_stn OUT NOCOPY VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2)
35 IS
36 l_device_id NUMBER := -1; -- Returned by get_device_info API
37 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
38 PRAGMA AUTONOMOUS_TRANSACTION;
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_ERROR;
41
42 IF (l_debug = 1) THEN
43 log
44 (p_device_id, 'In DEVICE_SIGN_ON. Calling WMS_Task_Dispatch_Device.get_device_info with params...');
45 log
46 (p_device_id, 'p_organization_id='
47 || p_organization_id
48 || ', p_device_name='
49 || p_device_name
50 || ', p_employee_id='
51 || p_employee_id
52 );
53 END IF;
54
55 --Call WMS_Task_Dispatch_Device.get_device_info
56 WMS_Task_Dispatch_Device.get_device_info
57 (p_organization_id => p_organization_id,
58 p_device_name => p_device_name,
59 x_return_status => x_return_status,
60 x_device_id => l_device_id,
61 x_device_type => x_device_type,
62 x_device_desc => x_device_desc,
63 x_subinventory => x_subinventory,
64 p_emp_id => p_employee_id,
65 x_signed_onto_wrk_stn => x_signon_wrk_stn);
66
67 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
68 RAISE fnd_api.g_exc_unexpected_error;
69 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
70 RAISE fnd_api.g_exc_error;
71 ELSIF x_return_status = 'A' THEN
72 IF (l_debug = 1) THEN
73 log
74 (p_device_id, 'Device already signed on. l_device_id='
75 || l_device_id
76 || ', p_device_name='
77 || p_device_name
78 || ', p_employee_id='
79 || p_employee_id
80 || ', p_organization_id='
81 || p_organization_id
82 || ', x_device_type='
83 || x_device_type
84 || ', x_device_desc='
85 || x_device_desc
86 || ', x_subinventory='
87 || x_subinventory
88 || ', x_signon_wrk_stn='
89 || x_signon_wrk_stn
90 );
91 END IF;
92
93 fnd_message.set_name('WMS', 'WMS_DEVICE_ALREADY_SIGNED');
94 fnd_message.set_token('DEVICE_DESC', x_device_desc);
95 fnd_msg_pub.ADD;
96 x_return_status := fnd_api.g_ret_sts_error;
97 RAISE fnd_api.G_EXC_ERROR;
98
99 END IF;
100
101 IF (l_debug = 1) THEN
102 log
103 (p_device_id, 'Device is not signed on. Calling WMS_Task_Dispatch_Device.insert_device.');
104 END IF;
105
106 WMS_Task_Dispatch_Device.insert_device
107 (p_Employee_Id => p_employee_id,
108 p_device_id => p_device_id,
109 p_org_id => p_organization_id,
110 x_return_status => x_return_status);
111
112 IF (l_debug = 1) THEN
113 log
114 (p_device_id, 'Done inserting into temp table. p_employee_id='
115 || p_employee_id
116 || ', p_device_id='
117 || p_device_id
118 || ', p_organization_id='
119 || p_organization_id
120 || ', x_return_status='
121 || x_return_status);
122 END IF;
123
124 --Commit for Autonmous transaction
125 COMMIT;
126 EXCEPTION
127 WHEN OTHERS THEN
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129 IF (l_debug = 1) THEN
130 log
131 (p_device_id, 'Unexpected error in DEVICE_SIGN_ON : '||SQLERRM);
132 END IF;
133 END DEVICE_SIGN_ON;
134
135 --Wrapper call on WMS_Task_Dispatch_Device.cleanup_device_and_tasks
136 PROCEDURE DEVICE_SIGN_OFF
137 (p_Employee_Id IN NUMBER,
138 p_org_id IN NUMBER,
139 x_return_status OUT NOCOPY VARCHAR2,
140 x_msg_count OUT NOCOPY NUMBER,
141 x_msg_data OUT NOCOPY VARCHAR2)
142 IS
143 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
144 PRAGMA AUTONOMOUS_TRANSACTION;
145 BEGIN
146 x_return_status := FND_API.G_RET_STS_ERROR;
147
148 IF (l_debug = 1) THEN
149 log
150 (NULL, 'In DEVICE_SIGN_OFF. Calling WMS_Task_Dispatch_Device.cleanup_device_and_tasks with params...');
151 log
152 (NULL, 'p_Employee_Id='
153 || p_Employee_Id
154 || ', p_org_id='
155 || p_org_id);
156 END IF;
157
158 --Call WMS_Task_Dispatch_Device.cleanup_device_and_tasks
159 WMS_Task_Dispatch_Device.cleanup_device_and_tasks
160 (p_Employee_Id => p_Employee_Id,
161 p_org_id => p_org_id,
162 x_return_status => x_return_status,
163 x_msg_count => x_msg_count,
164 x_msg_data => x_msg_data);
165
166 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
167 RAISE fnd_api.g_exc_unexpected_error;
168 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
169 RAISE fnd_api.g_exc_error;
170 END IF;
171
172 IF (l_debug = 1) THEN
173 log
174 (NULL, 'Done cleaning up the temp table. x_return_status='
175 || x_return_status
176 );
177 END IF;
178 --Autonomous transaction commit
179 COMMIT;
180 EXCEPTION
181 WHEN OTHERS THEN
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 IF (l_debug = 1) THEN
184 log
185 (NULL, 'Unexpected error in DEVICE_SIGN_OFF : '||SQLERRM);
186 END IF;
187 END DEVICE_SIGN_OFF;
188
189
190 --Wrapper call on overloaded WMS_Task_Dispatch_Device.cleanup_device_and_tasks
191 PROCEDURE SINGLE_DEVICE_SIGN_OFF
192 (p_Employee_Id IN NUMBER,
193 p_org_id IN NUMBER,
194 p_device_id IN NUMBER,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2)
198 IS
199 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
200 PRAGMA AUTONOMOUS_TRANSACTION;
201 BEGIN
202 x_return_status := FND_API.G_RET_STS_ERROR;
203
204 IF (l_debug = 1) THEN
205 log
206 (NULL, 'In SINGLE_DEVICE_SIGN_OFF. Calling overloaded WMS_Task_Dispatch_Device.cleanup_device_and_tasks with params...');
207 log
208 (NULL, 'p_Employee_Id='
209 || p_Employee_Id
210 || ', p_org_id='
211 || p_org_id
212 || ', p_device_id='
213 || p_device_id);
214 END IF;
215
216 --Call overloaded WMS_Task_Dispatch_Device.cleanup_device_and_tasks
217 WMS_Task_Dispatch_Device.cleanup_device_and_tasks
218 (p_Employee_Id => p_Employee_Id,
219 p_org_id => p_org_id,
220 p_device_id => p_device_id,
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data);
224
225 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
226 RAISE fnd_api.g_exc_unexpected_error;
227 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
228 RAISE fnd_api.g_exc_error;
229 END IF;
230
231 IF (l_debug = 1) THEN
232 log
233 (NULL, 'Done cleaning up the temp table. x_return_status='
234 || x_return_status
235 );
236 END IF;
237 --Autonomous transaction commit
238 COMMIT;
239 EXCEPTION
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 IF (l_debug = 1) THEN
243 log
244 (NULL, 'Unexpected error in SINGLE_DEVICE_SIGN_OFF : '||SQLERRM);
245 END IF;
246 END SINGLE_DEVICE_SIGN_OFF;
247
248
249 --Wrapper call on WMS_Device_Integration_PVT.device_request - overloaded
250 --WMS-OPM
251 PROCEDURE DEVICE_REQUEST(
252 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
253 p_bus_event IN NUMBER,
254 p_call_ctx IN VARCHAR2 ,
255 p_task_trx_id IN NUMBER := NULL,
256 p_org_id IN NUMBER := NULL,
257 p_item_id IN NUMBER := NULL,
258 p_subinv IN VARCHAR2 := NULL,
259 p_locator_id IN NUMBER := NULL,
260 p_lpn_id IN NUMBER := NULL,
261 p_xfr_org_id IN NUMBER := NULL,
262 p_xfr_subinv IN VARCHAR2 := NULL,
263 p_xfr_locator_id IN NUMBER := NULL,
264 p_trx_qty IN NUMBER := NULL,
265 p_trx_uom IN VARCHAR2 := NULL,
266 p_rev IN VARCHAR2 := NULL,
267 x_request_msg OUT NOCOPY VARCHAR2,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2,
271 p_request_id IN OUT NOCOPY NUMBER,
272 p_device_id IN NUMBER
273 )
274 IS
275 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
276 BEGIN
277 x_return_status := FND_API.G_RET_STS_ERROR;
278
279 IF (l_debug = 1) THEN
280 log
281 (p_device_id, 'In DEVICE_REQUEST. Calling WMS_Device_Integration_PVT.DEVICE_REQUEST with params...');
282 log
283 (p_device_id, 'p_init_msg_list='
284 || p_init_msg_list
285 || ', p_bus_event='
286 || p_bus_event
287 || ', p_call_ctx='
288 || p_call_ctx
289 || ', p_task_trx_id='
290 || p_task_trx_id
291 || ', p_org_id='
292 || p_org_id
293 || ', p_item_id='
294 || p_item_id
295 || ', p_subinv='
296 || p_subinv
297 || ', p_locator_id='
298 || p_locator_id
299 || ', p_lpn_id='
300 || p_lpn_id
301 || ', p_xfr_org_id='
302 || p_xfr_org_id
303 || ', p_xfr_subinv='
304 || p_xfr_subinv
305 || ', p_xfr_locator_id='
306 || p_xfr_locator_id
307 || ', p_trx_qty='
308 || p_trx_qty
309 || ', p_trx_uom='
310 || p_trx_uom
311 || ', p_rev='
312 || p_rev
313 || ', p_request_id='
314 || p_request_id);
315 END IF;
316
317 wms_device_integration_pvt.device_request(p_init_msg_list => p_init_msg_list,
318 p_bus_event => p_bus_event,
319 p_call_ctx => p_call_ctx,
320 p_task_trx_id => p_task_trx_id,
321 p_org_id => p_org_id,
322 p_item_id => p_item_id,
323 p_subinv => p_subinv,
324 p_locator_id => p_locator_id,
325 p_lpn_id => p_lpn_id,
326 p_xfr_org_id => p_xfr_org_id,
327 p_xfr_subinv => p_xfr_subinv,
328 p_xfr_locator_id => p_xfr_locator_id,
329 p_trx_qty => p_trx_qty,
330 p_trx_uom => p_trx_uom,
331 p_rev => p_rev,
332 x_request_msg => x_request_msg,
333 x_return_status => x_return_status,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data,
336 p_request_id => p_request_id,
337 p_device_id => p_device_id);
338
339 IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
340 RAISE fnd_api.g_exc_unexpected_error;
341 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
342 RAISE fnd_api.g_exc_error;
343 END IF;
344
345 IF (l_debug = 1) THEN
346 log
347 (p_device_id, 'Done with wms_device_integration_pvt.device_request. x_return_status='
348 || x_return_status
349 || ', x_request_msg='
350 || x_request_msg
351 || ', x_msg_count'
352 || x_msg_count
353 || ', x_msg_data'
354 || x_msg_data
355 );
356 END IF;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 IF (l_debug = 1) THEN
362 log
363 (p_device_id, 'Unexpected error in wms_device_integration_pvt.device_request : '||SQLERRM);
364 END IF;
365 END device_request;
366
367 --Wrapper call on FUNCTION WMS_DEVICES_PKG.is_wcs_enabled
368 FUNCTION IS_WCS_ENABLED(p_org_id IN NUMBER)
369 RETURN VARCHAR2
370 IS
371 l_is_wcs_enabled VARCHAR2(1);
372 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
373 PRAGMA AUTONOMOUS_TRANSACTION;
374 BEGIN
375 l_is_wcs_enabled := (WMS_DEVICES_PKG.is_wcs_enabled(p_org_id => p_org_id));
376 IF (l_debug = 1) THEN
377 log
378 (NULL, 'Done calling WMS_DEVICES_PKG.is_wcs_enabled. p_org_id='
379 || p_org_id
380 || ', l_is_wcs_enabled='
381 || l_is_wcs_enabled);
382 END IF;
383 --Autonomous transaction commit
384 COMMIT;
385 RETURN l_is_wcs_enabled;
386 EXCEPTION
387 WHEN OTHERS THEN
388 IF (l_debug = 1) THEN
389 log
390 (NULL, 'Unexpected error in IS_WCS_ENABLED : '||SQLERRM);
391 END IF;
392 END IS_WCS_ENABLED;
393
394 --API to process the parsed device response for WMS specific business events
395 PROCEDURE PROCESS_RESPONSE
396 (p_device_id IN NUMBER,
397 p_request_id IN NUMBER,
398 p_param_values_record IN MSG_COMPONENT_LOOKUP_TYPE,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2)
402 IS
403 l_successful_row_cnt NUMBER;
404 l_request_id NUMBER;
405 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
406 BEGIN
407 x_return_status := fnd_api.g_ret_sts_success;
408 IF l_debug >= 1 THEN
409 LOG(p_device_id, 'In WMS_WCS_DEVICE_GRP.PROCESS_RESPONSE for p_request_id='
410 ||p_request_id);
411 END IF;
412
413 --Check if the reason_id is populated. If it is then call the workflow wrapper API
414 IF p_param_values_record.reason_id IS NOT NULL THEN
415 IF l_debug > 0 THEN
416 log(p_device_id, 'Reason Id in response is '
417 ||p_param_values_record.reason_id);
418 log(p_device_id, 'Call workflow wrapper API');
419 END IF;
420 call_workflow(
421 p_device_id => p_device_id,
422 p_response_record => p_param_values_record
423 );
424 END IF;
425
426 --If the business event is 18 or 19
427 --(These are OPM business events 'Process Parameter Event' and 'Process Dispensing Event')
428 --then call the OPM response API
429 IF p_param_values_record.business_event IN (18,19) THEN
430 IF l_debug >= 1 THEN
431 log(p_device_id, 'Found an OPM business event:'
432 || p_param_values_record.business_event);
433 log(p_device_id, 'Calling the OPM response API');
434 END IF;
435 WMS_OPM_INTEGRATION_GRP.process_response
436 (
437 p_device_id => p_device_id,
438 p_request_id => p_request_id,
439 p_param_values_record => p_param_values_record,
440 x_return_status => x_return_status,
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data
443 );
444 ELSIF p_param_values_record.business_event = 54 THEN --This if for TASK CONFIRM
445 --This is for backward compatibility
446 --INSERT into WDR
447 INSERT INTO wms_device_requests
448 (relation_id,
449 task_id,
450 task_summary,
451 business_event_id,
452 organization_id,
453 device_status,
454 xfer_lpn_id,
455 last_update_date,
456 last_updated_by,
457 last_update_login
458 )
459 VALUES (p_param_values_record.relation_id,
460 p_param_values_record.task_id,
461 p_param_values_record.task_summary,
462 p_param_values_record.business_event,
463 p_param_values_record.organization_id,
464 p_param_values_record.device_status,
465 p_param_values_record.transfer_lpn_id,
466 SYSDATE,
467 fnd_global.user_id,
468 fnd_global.login_id );
469
470 --Pass the relation_id as the request id of the parent WDRH record
471 l_request_id := to_number(p_param_values_record.relation_id);
472
473 --Call the 11.5.10 OPEN API
474 IF l_debug > 0 THEN
475 log(p_device_id, 'Calling TC OPEN API wms_device_confirmation_pub.device_confirmation'); log(p_device_id, 'with param: l_request_id='||l_request_id);
476 END IF;
477 wms_device_confirmation_pub.device_confirmation
478 (x_return_status => x_return_status,
479 x_msg_count => x_msg_count,
480 x_msg_data => x_msg_data,
481 p_request_id => l_request_id,
482 x_successful_row_cnt => l_successful_row_cnt
483 );
484 END IF;
485 EXCEPTION
486 WHEN OTHERS THEN
487 x_return_status := fnd_api.g_ret_sts_unexp_error;
488 fnd_msg_pub.count_and_get (p_count => x_msg_count,
489 p_data => x_msg_data);
490 END PROCESS_RESPONSE;
491
492 PROCEDURE call_workflow
493 (
494 p_device_id IN NUMBER,
495 p_response_record IN MSG_COMPONENT_LOOKUP_TYPE
496 )
497 IS
498 l_wf NUMBER;
499 l_return_status VARCHAR2 (10);
500 l_msg_count NUMBER;
501 l_msg_data VARCHAR2 (4000);
502 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
503 PRAGMA AUTONOMOUS_TRANSACTION;
504 BEGIN
505 BEGIN
506 SELECT 1
507 INTO l_wf
508 FROM mtl_transaction_reasons
509 WHERE reason_id = p_response_record.reason_id
510 AND workflow_name IS NOT NULL
511 AND workflow_name <> ' '
512 AND workflow_process IS NOT NULL
513 AND workflow_process <> ' ';
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 l_wf := 0;
517 END;
518 IF l_wf > 0 THEN
519 IF l_debug > 0 THEN
520 LOG(p_device_id, 'WF exists for this reason ID: '
521 || p_response_record.reason_id);
522 LOG(p_device_id, 'Calling wms_workflow_wrappers.wf_wrapper');
523 END IF;
524
525 wms_workflow_wrappers.wf_wrapper(
526 p_api_version => 1.0
527 , p_init_msg_list => fnd_api.g_false
528 , p_commit => fnd_api.g_false
529 , x_return_status => l_return_status
530 , x_msg_count => l_msg_count
531 , x_msg_data => l_msg_data
532 , p_org_id => p_response_record.organization_id
533 , p_rsn_id => p_response_record.reason_id
534 , p_calling_program => 'MHP: call_workflow'
535 , p_tmp_id => p_response_record.task_id
536 , p_quantity_picked => p_response_record.quantity
537 , p_dest_sub => p_response_record.destination_subinventory
538 , p_dest_loc => p_response_record.destination_locator_id
539 );
540
541 IF (l_debug > 0) THEN
542 LOG(p_device_id ,'After Calling wf_wrapper. l_return_status is '
543 ||l_return_status);
544 END IF;
545
546 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
547 IF (l_debug > 0) THEN
548 LOG(p_device_id ,'Error calling wf_wrapper');
549 END IF;
550 RAISE fnd_api.g_exc_unexpected_error;
551 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
552 IF (l_debug > 0) THEN
553 LOG(p_device_id ,'Error calling wf_wrapper');
554 END IF;
555 fnd_message.set_name('WMS', 'WMS_WORK_FLOW_FAIL');
556 fnd_msg_pub.ADD;
557 RAISE fnd_api.g_exc_error;
558 END IF;
559 END IF;
560 --Autonomous transaction commit
561 COMMIT;
562 EXCEPTION
563 WHEN OTHERS THEN
564 l_return_status := fnd_api.g_ret_sts_unexp_error;
565 IF (l_debug > 0) THEN
566 LOG(p_device_id ,'Call_Workflow failed'||SQLERRM);
567 END IF;
568 fnd_msg_pub.count_and_get(p_count => l_msg_count,
569 p_data => l_msg_data);
570 END call_workflow;
571
572 --
573 --
574 PROCEDURE LOG (p_device_id in number, p_data IN VARCHAR2)
575 IS
576 cnt NUMBER;
577 -- PRAGMA AUTONOMOUS_TRANSACTION;
578 BEGIN
579 wms_carousel_integration_pvt.LOG(p_device_id,p_data);
580 /*
581 Commented out for Bug# 4624894
582
583 INSERT INTO wms_carousel_log
584 (CAROUSEL_LOG_ID
585 ,text
586 ,device_id
587 ,LAST_UPDATE_DATE
588 ,LAST_UPDATED_BY
589 ,CREATION_DATE
590 ,CREATED_BY
591 ,LAST_UPDATE_LOGIN
592 )
593 VALUES (wms_carousel_log_s.NEXTVAL
594 ,p_data
595 ,p_device_id
596 ,SYSDATE
597 ,fnd_global.user_id
598 ,SYSDATE
599 ,fnd_global.user_id
600 ,fnd_global.login_id
601 );
602
603 COMMIT;
604 */
605 END LOG;
606
607 END WMS_WCS_DEVICE_GRP;
608