eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WMS_CAROUSEL_INTEGRATION_PKG

Source


1 PACKAGE BODY WMS_CAROUSEL_INTEGRATION_PKG AS
2 /* $Header: WMSCSPBB.pls 120.4 2005/10/17 03:58:36 simran noship $ */
3 
4    PROCEDURE sync_device_request (
5       p_request_id      IN              NUMBER,
6       p_device_id       IN              NUMBER,
7       p_resubmit_flag   IN              VARCHAR2,
8       x_status_code     OUT NOCOPY      VARCHAR2,
9       x_status_msg      OUT NOCOPY      VARCHAR2,
10       x_device_status   OUT NOCOPY      VARCHAR2
11    )
12    IS
13    BEGIN
14       -- Verify access
15       x_status_code := 'S';
16 
17       /* we can not really verify access here because we do not know employee_id
18       wms_carousel_integration_pvt.verify_access
19           (
20            p_device_id,
21            p_employee_id,
22            x_status_code,
23            x_device_status
24           );
25       */
26       -- Allowed ?
27       IF x_status_code = 'S'
28       THEN
29          -- Process the request
30          wms_carousel_integration_pvt.process_request (p_request_id,
31                                                        x_status_code,
32                                                        x_status_msg,
33                                                        x_device_status
34                                                       );
35       END IF;
36 
37       -- Update the WMS with the status
38       wms_device_integration_pub.update_request
39                                               (p_request_id       => p_request_id,
40                                                p_device_id        => p_device_id,
41                                                p_status_code      => x_status_code,
42                                                p_status_msg       => x_device_status
43                                               );
44    END;
45 
46    --
47    --
48    PROCEDURE sync_device (
49       p_organization_id   IN              NUMBER,
50       p_device_id         IN              NUMBER,
51       p_employee_id       IN              NUMBER,
52       p_sign_on_flag      IN              VARCHAR2,
53       x_status_code       OUT NOCOPY      VARCHAR2,
54       x_device_status     OUT NOCOPY      VARCHAR2
55    )
56    IS
57       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
58    BEGIN
59       -- Singing on or off ?
60       x_status_code := FND_API.G_RET_STS_SUCCESS;
61       IF p_sign_on_flag = 'Y'
62       THEN
63          IF (l_debug > 0) THEN
64            wms_carousel_integration_pvt.LOG (p_device_id, 'Employee (' || p_employee_id || ') logged on');
65          END IF;
66       ELSIF p_sign_on_flag = 'N'
67       THEN
68          IF (l_debug > 0) THEN
69            wms_carousel_integration_pvt.LOG (p_device_id, 'Employee (' || p_employee_id || ') logged off');
70          END IF;
71       ELSE
72          x_status_code := 'E';
73          x_device_status := 'Invalid p_sing_on_flag';
74       END IF;
75    END;
76 
77    --
78    --
79    PROCEDURE pipe_listener_loop (p_job IN NUMBER, p_zone IN VARCHAR2, p_device_id in NUMBER, p_pipe_name IN VARCHAR2)
80    IS
81       v_switch   VARCHAR2 (16);
82       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
83       PRAGMA AUTONOMOUS_TRANSACTION;
84    BEGIN
85       LOOP
86          -- Get pipe listener switch value
87          v_switch :=
88             NVL
89                (wms_carousel_integration_pvt.get_config_parameter
90                                             (p_name      => 'PIPE_LISTENER_SWITCH',
91                                              p_sequence_id      => p_device_id
92                                             ),
93                 'OFF'
94                );
95          EXIT WHEN v_switch = 'OFF';
96          IF (l_debug > 0) THEN
97            wms_carousel_integration_pvt.LOG
98              ( p_device_id, 'Calling receive_pipe_listener. p_zone='
99               || p_zone
100               || ', p_pipe_name='
101               || p_pipe_name
102               || ', p_job='
103               || p_job
104              );
105          END IF;
106          -- Call the listener
107          -- Bug# 4666748
108          -- wms_carousel_integration_pvt.receive_pipe_listener (p_zone, p_device_id, p_pipe_name);
109       END LOOP;
110       COMMIT;
111    END;
112 
113    --
114    --
115    PROCEDURE submit_pipe_listeners(p_device_id IN NUMBER)
116    IS
117       -- Cursor for receive pipes
118       CURSOR c_receive_pipes(p_primary_device IN NUMBER)
119       IS
120          SELECT d.device_id, d.subinventory_code
121            FROM wms_devices_b d
122            WHERE d.device_id = p_primary_device;
123 
124       v_job     INTEGER;
125       job_str   VARCHAR2 (1024);
126       v_pipename VARCHAR2(50);
127       l_primary_device NUMBER;
128       l_dummy NUMBER;
129       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
130       PRAGMA AUTONOMOUS_TRANSACTION;
131    BEGIN
132          v_pipename := wms_carousel_integration_pvt.get_config_parameter
133                                             (p_name      => 'PIPE_NAME',
134                                              p_sequence_id      => p_device_id
135                                             );
136 
137      IF (v_pipename IS NULL) THEN
138       -- No PIPE_NAME config parameter created for the device. Hence create a default one
139       -- 4311016
140       v_pipename := p_device_id;
141       l_primary_device := p_device_id;
142       INSERT INTO WMS_CAROUSEL_CONFIGURATION
143 	(
144 	CAROUSEL_CONFIGURATION_ID
145 	,CONFIG_NAME
146 	,CONFIG_VALUE
147 	,SEQUENCE_ID
148 	,ACTIVE_IND
149 	,LAST_UPDATE_DATE
150 	,LAST_UPDATED_BY
151 	,CREATION_DATE
152 	,CREATED_BY
153 	,LAST_UPDATE_LOGIN
154 	)
155       VALUES
156         (WMS_CAROUSEL_CONFIGURATION_S.NEXTVAL
157 	,'PIPE_NAME'
158 	,v_pipename
159 	,p_device_id
160 	,'Y'
161 	,SYSDATE
162 	,fnd_global.user_id
163 	,SYSDATE
164 	,fnd_global.user_id
165 	,fnd_global.login_id
166 	);
167         IF (l_debug > 0) THEN
168            wms_carousel_integration_pvt.LOG (p_device_id, 'No PIPE_NAME parameter defined for device ('
169 					  || p_device_id || ')! Hence created one with value='
170 					  || v_pipename
171                                           );
172         END IF;
173      ELSE
174 	-- Get the first device in the group
175 	SELECT MIN(SEQUENCE_ID) into l_primary_device FROM WMS_CAROUSEL_CONFIGURATION
176 	WHERE CONFIG_NAME = 'PIPE_NAME'
177 	AND CONFIG_VALUE = v_pipename;
178      END IF;
179 
180       --4311016
181       v_pipename := 'IN_'||v_pipename;
182 
183       -- Start up listeners
184       FOR v_cfg IN c_receive_pipes(l_primary_device)
185       LOOP
186 	BEGIN
187 		select 1 INTO l_dummy from (
188 		 SELECT job,what, (substr(what, instr(what,'''',-1,2)+1, (instr(what,'''',-1,1) - instr(what,'''',-1,2) - 1))) pipe_name
189 		   FROM user_jobs
190 		  WHERE UPPER (what) LIKE '%WMS_CAROUSEL_INTEGRATION_PKG.PIPE_LISTENER_LOOP%')
191 		where pipe_name = v_pipename;
192 		IF (l_debug > 0) THEN
193 		  WMS_CAROUSEL_INTEGRATION_PVT.LOG (v_cfg.device_id, 'Job already exists:'
194 						   || ' ID='
195 						   || v_job
196 						   || ', Pipe='
197 						   || v_pipename
198 						   || ', Subinventory='
199 						   || v_cfg.subinventory_code
200 						  );
201                 END IF;
202 	EXCEPTION
203 	WHEN NO_DATA_FOUND THEN
204 		 -- Submit a job
205 		 DBMS_JOB.submit (job            => v_job,
206 				  what           => 'begin null; end;',
207 				  next_date      => SYSDATE,
208 				  INTERVAL       => 'sysdate + (10/(24*3600))'
209 				 );
210 		 job_str :=
211 		       'WMS_CAROUSEL_INTEGRATION_PKG.PIPE_LISTENER_LOOP('
212 		    || v_job
213 		    || ',''' || v_cfg.subinventory_code || ''''
214 		    || ',' || v_cfg.device_id
215 		    || ',''' || v_pipename || ''');';
216 
217 		 DBMS_JOB.what (v_job, job_str);
218 
219 		 IF (l_debug > 0) THEN
220 		   WMS_CAROUSEL_INTEGRATION_PVT.LOG (v_cfg.device_id, 'New Job Created:'
221 						   || ' ID='
222 						   || v_job
223 						   || ', Pipe='
224 						   || v_pipename
225 						   || ', Subinventory='
226 						   || v_cfg.subinventory_code
227 						  );
228                  END IF;
229 	END;
230       END LOOP;
231       COMMIT;
232    END;
233 
234    PROCEDURE start_job(p_device_id IN NUMBER)
235    IS
236       -- Cursor for receive pipes
237       CURSOR c_pipe_listeners (p_pipe_name IN VARCHAR2)
238       IS
239         select distinct * from (
240          SELECT job,what, (substr(what, instr(what,'''',-1,2)+1, (instr(what,'''',-1,1) - instr(what,'''',-1,2) - 1))) pipe_name
241            FROM user_jobs
242           WHERE UPPER (what) LIKE '%WMS_CAROUSEL_INTEGRATION_PKG.PIPE_LISTENER_LOOP%')
243         where pipe_name = nvl(p_pipe_name, pipe_name);
244 
245       v_pipename VARCHAR2(50);
246       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
247       PRAGMA AUTONOMOUS_TRANSACTION;
248    BEGIN
249      IF (p_device_id IS NOT NULL) THEN
250          v_pipename :=
251             NVL
252                (wms_carousel_integration_pvt.get_config_parameter
253                                             (p_name      => 'PIPE_NAME',
254                                              p_sequence_id      => p_device_id
255                                             ),
256                 'PIPE_NAME_' || p_device_id
257                );
258      END IF;
259       -- Start jobs
260       FOR v_job IN c_pipe_listeners(v_pipename)
261       LOOP
262          -- Start the job
263          DBMS_JOB.broken(v_job.job,false,null);
264          IF (l_debug > 0) THEN
265            wms_carousel_integration_pvt.LOG ( p_device_id,  'Job Started:'
266                                            || ' ID='
267                                            || v_job.job
268                                            || ', Pipe='
269                                            || v_job.pipe_name
270                                           );
271          END IF;
272       END LOOP;
273 
274       COMMIT;
275    END;
276 
277    PROCEDURE stop_job(p_device_id IN NUMBER)
278    IS
279       -- Cursor for receive pipes
280       CURSOR c_pipe_listeners (p_pipe_name IN VARCHAR2)
281       IS
282         select * from (
283          SELECT job,what, (substr(what, instr(what,'''',-1,2)+1, (instr(what,'''',-1,1) - instr(what,'''',-1,2) - 1))) pipe_name
284            FROM user_jobs
285           WHERE UPPER (what) LIKE '%WMS_CAROUSEL_INTEGRATION_PKG.PIPE_LISTENER_LOOP%')
286         where pipe_name = nvl(p_pipe_name, pipe_name);
287 
288       v_pipename VARCHAR2(50) := NULL;
289       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
290       PRAGMA AUTONOMOUS_TRANSACTION;
291    BEGIN
292      IF (p_device_id IS NOT NULL) THEN
293          v_pipename :=
294             NVL
295                (wms_carousel_integration_pvt.get_config_parameter
296                                             (p_name      => 'PIPE_NAME',
297                                              p_sequence_id      => p_device_id
298                                             ),
299                 'PIPE_NAME_' || p_device_id
300                );
301      END IF;
302       -- Stop jobs
303       FOR v_job IN c_pipe_listeners(v_pipename)
304       LOOP
305          -- Stop the job
306          DBMS_JOB.broken(v_job.job,true,null);
307          IF (l_debug > 0) THEN
308            wms_carousel_integration_pvt.LOG ( p_device_id,  'Job Stopped:'
309                                            || ' ID='
310                                            || v_job.job
311                                            || ', Pipe='
312                                            || v_job.pipe_name
313                                           );
314          END IF;
315       END LOOP;
316 
317       COMMIT;
318    END;
319    --
320    --
321    PROCEDURE remove_pipe_listeners
322    IS
323       -- Cursor for receive pipes
324       CURSOR c_pipe_listeners
325       IS
326          SELECT job, what
327            FROM user_jobs
328           WHERE UPPER (what) LIKE '%WMS_CAROUSEL_INTEGRATION_PKG.PIPE_LISTENER_LOOP%';
329 
330       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
331       PRAGMA AUTONOMOUS_TRANSACTION;
332    BEGIN
333       -- Remove jobs
334       FOR v_job IN c_pipe_listeners
335       LOOP
336          -- Remove the job
337          DBMS_JOB.remove (v_job.job);
338          IF (l_debug > 0) THEN
339            wms_carousel_integration_pvt.LOG ( NULL,  'Job Removed:'
340                                            || ' ID='
341                                            || v_job.job
342                                            || ', what='
343                                            || v_job.what
344                                           );
345          END IF;
346       END LOOP;
347       COMMIT;
348    END;
349 
350    PROCEDURE recreate_pipe_listeners
351    IS
352      CURSOR C_PIPE_DEVICES IS SELECT SEQUENCE_ID FROM WMS_CAROUSEL_CONFIGURATION
353      WHERE CONFIG_NAME = 'PIPE_NAME';
354    BEGIN
355       remove_pipe_listeners;
356       FOR v_job IN C_PIPE_DEVICES
357       LOOP
358         submit_pipe_listeners(v_job.SEQUENCE_ID);
359       END LOOP;
360    END;
361 
362    --
363    --
364    PROCEDURE START_PIPE_LISTENERS(p_device_id IN NUMBER DEFAULT NULL)
365    IS
366       v_pipename VARCHAR2(50);
367       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
368       PRAGMA AUTONOMOUS_TRANSACTION;
369    BEGIN
370       IF (p_device_id IS NULL) THEN
371 	-- Switch on all listeners
372 	UPDATE WMS_CAROUSEL_CONFIGURATION c
373 	SET c.CONFIG_VALUE = 'ON'
374 	WHERE c.CONFIG_NAME = 'PIPE_LISTENER_SWITCH';
375         IF (l_debug > 0) THEN
376 	  wms_carousel_integration_pvt.LOG (null,'All Pipe Listeners Switched ON');
377         END IF;
378       ELSE
379 	v_pipename :=
380 	NVL
381 	(wms_carousel_integration_pvt.get_config_parameter
382 				    (p_name      => 'PIPE_NAME',
383 				     p_sequence_id      => p_device_id
384 				    ),
385 	'PIPE_NAME_' || p_device_id
386 	);
387 	-- Switch on
388 	UPDATE WMS_CAROUSEL_CONFIGURATION c
389 	SET c.CONFIG_VALUE = 'ON'
390 	WHERE c.CONFIG_NAME = 'PIPE_LISTENER_SWITCH'
391 	AND SEQUENCE_ID IN (SELECT SEQUENCE_ID FROM wms_carousel_configuration WHERE CONFIG_VALUE = v_pipename);
392         IF (l_debug > 0) THEN
393 	  wms_carousel_integration_pvt.LOG (p_device_id,'Pipe Listener Switched ON: Pipe=IN_' || v_pipename);
394         END IF;
395       END IF;
396       start_job(p_device_id);
397       COMMIT;
398    END;
399 
400    --
401    --
402    PROCEDURE STOP_PIPE_LISTENERS(p_device_id IN NUMBER DEFAULT NULL)
403    IS
404       v_pipename VARCHAR2(50);
405       l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
406       PRAGMA AUTONOMOUS_TRANSACTION;
407    BEGIN
408       IF (p_device_id IS NULL) THEN
409 	-- Switch off all listeners
410 	UPDATE WMS_CAROUSEL_CONFIGURATION c
411 	SET c.CONFIG_VALUE = 'OFF'
412 	WHERE c.CONFIG_NAME = 'PIPE_LISTENER_SWITCH';
413         IF (l_debug > 0) THEN
414 	  WMS_CAROUSEL_INTEGRATION_PVT.LOG (null,'All Pipe Listeners Switched OFF');
415         END IF;
416       ELSE
417 	v_pipename :=
418 	NVL
419 	(wms_carousel_integration_pvt.get_config_parameter
420 				    (p_name      => 'PIPE_NAME',
421 				     p_sequence_id      => p_device_id
422 				    ),
423 	'PIPE_NAME_' || p_device_id
424 	);
425 	-- Switch off
426 	UPDATE WMS_CAROUSEL_CONFIGURATION c
427 	SET c.CONFIG_VALUE = 'OFF'
428 	WHERE c.CONFIG_NAME = 'PIPE_LISTENER_SWITCH'
429 	AND SEQUENCE_ID IN (SELECT SEQUENCE_ID FROM WMS_CAROUSEL_CONFIGURATION WHERE CONFIG_VALUE = v_pipename);
430         IF (l_debug > 0) THEN
431 	  WMS_CAROUSEL_INTEGRATION_PVT.LOG (p_device_id,'Pipe Listener Switched OFF: Pipe=IN_' || v_pipename);
432         END IF;
433       END IF;
434       stop_job(p_device_id);
435       COMMIT;
436    END;
437 --
438    PROCEDURE SIGN_OFF_USER(p_organization_id IN NUMBER,
439 			p_device_id IN NUMBER,
440 			p_emp_id IN NUMBER,
441 			x_return_status OUT NOCOPY VARCHAR2
442    )
443    IS
444 		CURSOR c_tasks IS
445 			SELECT  1 FROM WMS_DISPATCHED_TASKS wdt
446 			WHERE	organization_id 	 = p_organization_id
447 			AND device_id			 = p_device_id
448 			AND PERSON_ID           = p_emp_id
449 			AND status = 9;
450 		l_dummy NUMBER;
451                 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
452 		PRAGMA AUTONOMOUS_TRANSACTION;
453 	BEGIN
454                 --wms_carousel_integration_pvt.LOG ( p_device_id,  'In SIGN_OFF_USER Org=' || p_organization_id || ',employee (' || p_emp_id || ') from device_id (' || p_device_id || ')');
455 		x_return_status := 'S';
456 		Open c_tasks;
457 		Fetch c_tasks into l_dummy;
458 		IF (c_tasks%NOTFOUND) THEN
459                   IF (l_debug > 0) THEN
460 	            wms_carousel_integration_pvt.LOG ( p_device_id,  'Signing off employee (' || p_emp_id || ') from device_id (' || p_device_id || ')');
461                   END IF;
462 
463 		  DELETE FROM wms_device_assignment_temp WHERE device_id = p_device_id and EMPLOYEE_ID = p_emp_id;
464   		  COMMIT;
465 		ELSE
466                   IF (l_debug > 0) THEN
467 	            wms_carousel_integration_pvt.LOG ( p_device_id,  'Failed signing off employee (' || p_emp_id || ') from device_id (' || p_device_id || ')');
468                   END IF;
469 		  x_return_status := 'A';
470 		END IF;
471 		CLOSE c_tasks;
472    END;
473 --
474 END WMS_CAROUSEL_INTEGRATION_PKG;