1 PACKAGE BODY WMS_Task_Dispatch_Device AS
2 /* $Header: WMSTKDVB.pls 120.7 2005/10/09 03:42:43 simran noship $ */
3
4
5 -- Global constant holding the package name
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_Task_Dispatch_Device';
8
9
10
11 PROCEDURE mydebug(msg in varchar2)
12 IS
13 l_msg VARCHAR2(5100);
14 l_ts VARCHAR2(30);
15 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
16 BEGIN
17 -- select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
18 -- l_msg:=l_ts||' '||msg;
19
20 l_msg := msg;
21
22 inv_mobile_helper_functions.tracelog
23 (p_err_msg => l_msg,
24 p_module => 'WMS_Task_Dispatch_Device',
25 p_level => 4);
26
27 --dbms_output.put_line(l_msg);
28
29 null;
30 END;
31
32
33
34 PROCEDURE insert_device
35 (p_Employee_Id IN NUMBER,
36 p_device_id IN NUMBER,
37 p_org_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2) IS
39 l_assign_temp_id number;
40 l_device_status VARCHAR2(400);
41 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
42 l_wcs_enabled VARCHAR2(1);
43 BEGIN
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 IF (l_debug = 1) THEN
47 mydebug('In insert_device');
48 END IF;
49
50 SELECT WMS_DEVICE_ASSIGNMENT_TEMP_S.nextval
51 INTO l_assign_temp_id
52 FROM DUAL;
53
54 INSERT INTO WMS_DEVICE_ASSIGNMENT_TEMP
55 ( ASSIGNMENT_TEMP_ID,
56 EMPLOYEE_ID,
57 ORGANIZATION_ID,
58 DEVICE_ID,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN)
64 VALUES
65 ( l_assign_temp_id,
66 p_Employee_Id,
67 p_org_id,
68 p_device_id,
69 sysdate,
70 FND_GLOBAL.USER_ID,
71 sysdate,
72 FND_GLOBAL.USER_ID,
73 FND_GLOBAL.LOGIN_ID );
74
75 l_wcs_enabled := wms_devices_pkg.is_wcs_enabled(P_ORG_ID);
76
77 IF (l_wcs_enabled='Y') THEN
78 -- Call Sync_device api
79 IF (l_debug = 1) THEN
80 mydebug('Calling WMS_DEVICE_INTEGRATION_WCS.sync_device for device id : '||p_device_id);
81 END IF;
82
83 WMS_DEVICE_INTEGRATION_WCS.sync_device(p_organization_id => p_org_id,
84 p_device_id => p_device_id,
85 p_employee_id => p_employee_id,
86 p_sign_on_flag => 'Y',
87 x_status_code => x_return_status,
88 x_device_status => l_device_status);
89 ELSE
90 -- Call Sync_device api
91 IF (l_debug = 1) THEN
92 mydebug('Calling wms_device_integration_pub.sync_device for device id : '||p_device_id);
93 END IF;
94
95 wms_device_integration_pub.sync_device(p_organization_id => p_org_id,
96 p_device_id => p_device_id,
97 p_employee_id => p_employee_id,
98 p_sign_on_flag => 'Y',
99 x_status_code => x_return_status,
100 x_device_status => l_device_status);
101 END IF;
102
103 IF (l_debug = 1) THEN
104 mydebug('Status Code : '||x_return_status);
105 mydebug('Device Status : '||l_device_status);
106 END IF;
107
108 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
109 -- Have to commit it, so that the others can see it and current devices can see it
110 COMMIT;
111 END IF;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
116 END insert_device;
117
118
119
120
121 PROCEDURE cleanup_device_and_tasks
122 (p_Employee_Id IN NUMBER,
123 p_org_id IN NUMBER,
124 x_return_status OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2,
127 p_retain_dispatched_tasks IN VARCHAR2 default 'N') IS
128
129 l_device_status VARCHAR2(400);
130 l_device_id NUMBER;
131
132 CURSOR get_devices_to_cleanup IS
133 SELECT device_id
134 FROM wms_device_assignment_temp
135 WHERE Employee_Id=p_employee_id
136 AND organization_id=p_org_id;
137 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
138 l_wcs_enabled VARCHAR2(1);
139 BEGIN
140
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 IF (l_debug = 1) THEN
144 mydebug('In cleanup_device_and_tasks');
145 END IF;
146
147 l_wcs_enabled := wms_devices_pkg.is_wcs_enabled(P_ORG_ID);
148
149 -- call sync_device api
150 OPEN get_devices_to_cleanup;
151
152 LOOP
153 FETCH get_devices_to_cleanup INTO l_device_id;
154 EXIT WHEN get_devices_to_cleanup%NOTFOUND;
155
156 IF (l_wcs_enabled='Y') THEN
157 IF (l_debug = 1) THEN
158 mydebug('Calling WMS_DEVICE_INTEGRATION_WCS.sync_device for device id: '||l_device_id);
159 END IF;
160
161 WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE (p_organization_id => p_org_id,
162 p_device_id => l_device_id,
163 p_employee_id => p_employee_id,
164 p_sign_on_flag => 'N',
165 x_status_code => x_return_status,
166 x_device_status => l_device_status);
167 ELSE
168 IF (l_debug = 1) THEN
169 mydebug('Calling wms_device_integration_pub.sync_device for device id: '||l_device_id);
170 END IF;
171
172 WMS_Device_Integration_PUB.sync_device (p_organization_id => p_org_id,
173 p_device_id => l_device_id,
174 p_employee_id => p_employee_id,
175 p_sign_on_flag => 'N',
176 x_status_code => x_return_status,
177 x_device_status => l_device_status);
178 END IF;
179
180 IF (l_debug = 1) THEN
181 mydebug('Status Code : '||x_return_status);
182 mydebug('Device Status : '||l_device_status);
183 END IF;
184
185 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
186 EXIT;
187 END IF;
188
189 END LOOP;
190
191 CLOSE get_devices_to_cleanup;
192
193 -- signed off all the devices for this person
194 DELETE FROM WMS_DEVICE_ASSIGNMENT_TEMP
195 WHERE Employee_Id=p_Employee_Id
196 AND organization_id=p_org_id;
197
198 IF p_retain_dispatched_tasks ='N' or SQL%ROWCOUNT >1 THEN
199 -- signed off all the dispatched but not started tasks for this user
200 DELETE FROM WMS_DISPATCHED_TASKS
201 WHERE person_id = p_Employee_Id
202 and organization_id = p_org_id
203 and task_type IN (1, 3, 4, 5, 6)
204 and status IN (1,3,9); -- delete the Active task too, patchset I
205 ELSE
206 UPDATE WMS_DISPATCHED_TASKS
207 SET status = 3
208 WHERE status = 9
209 and person_id = p_Employee_ID
210 and organization_id = p_org_id
211 and task_type IN (1,3,4,5,6);
212 END IF;
213
214 -- Commit so that task dispatching can see it
215 IF (x_return_status = FND_API.g_ret_sts_success) THEN
216 COMMIT;
217 END IF;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 END cleanup_device_and_tasks;
223
224 /*
225 This is the overloaded procedure
226 It takes one additional parameter p_device_id
227 The call to sync_device
228 Deleting From Wms_device_assignment_temp &
229 Deleting or Updating WDT
230 will be only for that particular device
231 */
232 PROCEDURE cleanup_device_and_tasks
233 (p_Employee_Id IN NUMBER,
234 p_org_id IN NUMBER,
235 p_device_id IN NUMBER,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_retain_dispatched_tasks IN VARCHAR2 default 'N') IS
240
241 l_device_status VARCHAR2(400);
242 l_device_id NUMBER;
243
244 CURSOR get_devices_to_cleanup IS
245 SELECT device_id
246 FROM wms_device_assignment_temp
247 WHERE Employee_Id=p_employee_id
248 AND organization_id=p_org_id
249 AND device_id = p_device_id;
250 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
251 l_wcs_enabled VARCHAR2(1);
252 BEGIN
253
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255
256 IF (l_debug = 1) THEN
257 mydebug('In Overloaded cleanup_device_and_tasks');
258 END IF;
259
260 l_wcs_enabled := wms_devices_pkg.is_wcs_enabled(P_ORG_ID);
261
262 -- call sync_device api
263 OPEN get_devices_to_cleanup;
264
265 LOOP
266 FETCH get_devices_to_cleanup INTO l_device_id;
267 EXIT WHEN get_devices_to_cleanup%NOTFOUND;
268
269 IF (l_wcs_enabled='Y') THEN
270 IF (l_debug = 1) THEN
271 mydebug('Calling WMS_DEVICE_INTEGRATION_WCS.sync_device for device id: '||l_device_id);
272 END IF;
273
274 WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE (p_organization_id => p_org_id,
275 p_device_id => l_device_id,
276 p_employee_id => p_employee_id,
277 p_sign_on_flag => 'N',
278 x_status_code => x_return_status,
279 x_device_status => l_device_status);
280 ELSE
281 IF (l_debug = 1) THEN
282 mydebug('Calling wms_device_integration_pub.sync_device for device id: '||l_device_id);
283 END IF;
284
285 WMS_Device_Integration_PUB.sync_device (p_organization_id => p_org_id,
286 p_device_id => l_device_id,
287 p_employee_id => p_employee_id,
288 p_sign_on_flag => 'N',
289 x_status_code => x_return_status,
290 x_device_status => l_device_status);
291 END IF;
292
293 IF (l_debug = 1) THEN
294 mydebug('Status Code : '||x_return_status);
295 mydebug('Device Status : '||l_device_status);
296 END IF;
297
298 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
299 EXIT;
300 END IF;
301
302 END LOOP;
303
304 CLOSE get_devices_to_cleanup;
305
306 -- signed off all the devices for this person
307 DELETE FROM WMS_DEVICE_ASSIGNMENT_TEMP
308 WHERE Employee_Id=p_Employee_Id
309 AND organization_id=p_org_id
310 AND device_id = p_device_id;
311
312 IF p_retain_dispatched_tasks ='N' or SQL%ROWCOUNT >1 THEN
313 -- signed off all the dispatched but not started tasks for this user
314 DELETE FROM WMS_DISPATCHED_TASKS
315 WHERE person_id = p_Employee_Id
316 and organization_id = p_org_id
317 and device_id = p_device_id
318 and task_type IN (1, 3, 4, 5, 6)
319 and status IN (1,3,9); -- delete the Active task too, patchset I
320 ELSE
321 UPDATE WMS_DISPATCHED_TASKS
322 SET status = 3
323 WHERE status = 9
324 and person_id = p_Employee_ID
325 and organization_id = p_org_id
326 and device_id = p_device_id
327 and task_type IN (1,3,4,5,6);
328 END IF;
329
330 -- Commit so that task dispatching can see it
331 IF (x_return_status = FND_API.g_ret_sts_success) THEN
332 COMMIT;
333 END IF;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 END cleanup_device_and_tasks; -- End of overloaded procedure
339
340 --J Develop
341
342 PROCEDURE get_device_info(p_organization_id IN NUMBER,
343 p_device_name IN VARCHAR2,
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_device_id OUT NOCOPY NUMBER,
346 x_device_type OUT NOCOPY VARCHAR2,
347 x_device_desc OUT NOCOPY VARCHAR2,
348 x_subinventory OUT NOCOPY VARCHAR2,
349 p_emp_id IN NUMBER,
350 x_signed_onto_wrk_stn OUT NOCOPY VARCHAR2)
351 IS
352 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
353 l_is_device_signed NUMBER := 0;
354 l_device_type_id NUMBER := -1;
355 l_signed_onto_wrk_stn NUMBER;
356 using_asrs_without_wrk_stn EXCEPTION;
357 is_org_wcs_enabled VARCHAR2(1);
358 l_is_multi_signon_dev VARCHAR2(1);
359
360
361 /***********************************************************************************
362 Added for device Integration project. Check whether the device being singed onto
363 is already signed on by some other user. If so then do not allow the current
364 user to sign onto the device.
365 ***********************************************************************************/
366
367 /***********************************************************************************
368 Allow multiple users to sign on if MULTI_SIGN_ON is checked
369 For device_type_id=100 (Workstation) Sub will be NULL
370 ***********************************************************************************/
371
372 BEGIN
373 x_return_status := FND_API.G_RET_STS_SUCCESS;
374 x_signed_onto_wrk_stn := 'Y';
375 l_is_multi_signon_dev := wms_devices_pkg.is_device_multisignon(p_organization_id, p_device_name);
376 is_org_wcs_enabled := wms_devices_pkg.is_wcs_enabled(p_organization_id);
377 IF (l_debug = 1) THEN
378 mydebug('MHE: is_org_wcs_enabled = '||is_org_wcs_enabled);
379 END IF;
380
381 IF is_org_wcs_enabled = 'Y' THEN
382
383 SELECT nvl(device_type_id, -1)
384 INTO l_device_type_id
385 FROM wms_devices_vl wdv
386 WHERE wdv.organization_id = p_organization_id
387 AND wdv.name = p_device_name;
388
389 IF (l_debug = 1) THEN
390 mydebug('MHE: l_device_type_id = '||l_device_type_id);
391 END IF;
392
393 /*
394 If the device type is ASRS (6) then check if the employee has signed on to a
395 Work Sation (device type 100). If he has not; then set x_return_status to "E"
396 raise the user defined exception using_asrs_without_wrk_stn
397 */
398 IF l_device_type_id = 6 THEN
399 BEGIN
400 SELECT 1
401 INTO l_signed_onto_wrk_stn
402 FROM WMS_DEVICE_ASSIGNMENT_TEMP wda,
403 WMS_DEVICES_VL wvl
404 WHERE wda.device_id = wvl.device_id
405 AND wvl.organization_id = p_organization_id
406 AND wda.employee_id = p_emp_id
407 AND device_type_id = 100;
408
409 IF (l_debug = 1) THEN
410 mydebug('MHE: l_signed_onto_wrk_stn = '||l_signed_onto_wrk_stn);
411 END IF;
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 raise using_asrs_without_wrk_stn;
415 END;
416 END IF;
417 END IF;
418
419 SELECT 1
420 , wvl.description
421 INTO l_is_device_signed
422 , x_device_desc
423 FROM WMS_DEVICE_ASSIGNMENT_TEMP wda
424 , WMS_DEVICES_VL wvl
425 WHERE wda.device_id = wvl.device_id
426 AND (wvl.subinventory_code IS NOT NULL OR device_type_id = 100)
427 AND l_is_multi_signon_dev <> 'Y'
428 AND wvl.organization_id = p_organization_id
429 AND wvl.name = p_device_name
430 AND device_type_id <> 7 ;
431
432 IF l_is_device_signed = 1 THEN
433 x_return_status := 'A';
434 END IF;
435
436 EXCEPTION
437 WHEN no_data_found THEN
438 BEGIN
439 SELECT device_type
440 , description
441 , device_id
442 , subinventory_code
443 INTO x_device_type
444 , x_device_desc
445 , x_device_id
446 , x_subinventory
447 FROM WMS_DEVICES_VL
448 WHERE (subinventory_code IS NOT NULL OR device_type_id = 100)
449 AND organization_id = p_organization_id
450 AND name = p_device_name
451 AND device_type_id <> 7 ;
452 EXCEPTION
453 WHEN no_data_found THEN
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 WHEN OTHERS THEN
456 x_return_status := FND_API.g_ret_sts_unexp_error;
457 END;
458 WHEN using_asrs_without_wrk_stn THEN
459 x_return_status := FND_API.G_RET_STS_ERROR;
460 x_signed_onto_wrk_stn := 'N';
461 END get_device_info;
462
463
464 /***********************************************************************
465 * FUNCTION get_eligible_device gets the device which will be used
466 * for the User/Org/Sub and Loc combination. It will get the device
467 * based on the most restrictive combination. The decreasing order
468 * of choice of device is :
469 * 1. User Level
470 * 2. Locator Level
471 * 3. Subinventory Level
472 * 4. Organization Level
473 ********************************************************************* */
474 FUNCTION get_eligible_device(
475 p_organization_id IN NUMBER
476 , p_subinventory IN VARCHAR2
477 , p_locator_id IN NUMBER
478 ) return NUMBER
479 IS
480 l_device_id NUMBER;
481 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
482 BEGIN
483 SELECT device_id
484 INTO l_device_id
485 FROM (
486 SELECT wbed.DEVICE_ID
487 FROM wms_bus_event_devices wbed , wms_devices_b wd
488 WHERE wd.device_id = wbed.device_id
489 AND WBED.organization_id = WD.organization_id
490 AND wd.ENABLED_FLAG = 'Y'
491 AND decode (level_type, 200,wbed.subinventory_code,level_value) =
492 decode(level_type,200,p_subinventory,100,p_organization_id ,300, p_locator_id,400,
493 FND_GLOBAL.USER_ID,level_value)
494 AND Nvl(wbed.organization_id,-1) = Nvl(p_organization_id ,Nvl(wbed.organization_id ,-1))
495 --AND wbed.AUTO_ENABLED_FLAG = decode('Y','Y','Y','N')
496 AND wbed.business_event_id = wms_device_integration_pvt.wms_be_pick_load
497 ORDER BY level_type desc
498 )
499 WHERE rownum < 2;
500
501 IF (l_debug = 1) THEN
502 mydebug('Device ID Fetched: '||l_device_id);
503 END IF;
504
505 RETURN l_device_id;
506 END get_eligible_device;
507
508
509
510
511
512
513 END wms_task_dispatch_device;
514
[Home] [Help]