[Home] [Help]
PACKAGE BODY: APPS.WMS_CAROUSEL_INTEGRATION_PVT
Source
1 PACKAGE BODY WMS_CAROUSEL_INTEGRATION_PVT AS
2 /* $Header: WMSCSPVB.pls 120.22 2005/12/06 07:06:53 simran noship $ */
3
4 --
5 -- Private Procedure to slow down the application if the hardware is not able to keep pace
6 -- The duration of the wait will be controlled by a configurable paramter 'DIRECTIVE_PAUSE_DELAY'
7 --
8 PROCEDURE pause_directive(
9 p_device_id IN NUMBER,
10 p_delay_in_seconds IN NUMBER
11 );
12
13 FUNCTION get_config_parameter (
14 p_name IN VARCHAR2,
15 p_device_type_id IN NUMBER DEFAULT NULL,
16 p_business_event_id IN NUMBER DEFAULT NULL,
17 p_sequence_id IN NUMBER DEFAULT NULL
18 )
19 RETURN VARCHAR2
20 IS
21 -- Cursor for selecting the parameter value
22 CURSOR c_config_parameter (
23 p_device_type_id IN NUMBER,
24 p_business_event_id IN NUMBER,
25 p_sequence_id IN NUMBER,
26 p_name IN VARCHAR2
27 )
28 IS
29 SELECT CONFIG_VALUE
30 FROM wms_carousel_configuration
31 WHERE CONFIG_NAME = p_name
32 AND NVL (NVL (device_type_id, p_device_type_id), 0) = NVL (p_device_type_id, 0)
33 AND NVL (NVL (business_event_id, p_business_event_id), 0) = NVL (p_business_event_id, 0)
34 AND NVL (NVL (sequence_id, p_sequence_id), 0) = NVL (p_sequence_id, 0)
35 AND active_ind = 'Y'
36 ORDER BY device_type_id, business_event_id, sequence_id;
37
38 v_value VARCHAR2 (4000) := NULL;
39 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
40 BEGIN
41 -- Get it out of the configuration table
42 OPEN c_config_parameter (p_device_type_id,
43 p_business_event_id,
44 p_sequence_id,
45 p_name
46 );
47
48 FETCH c_config_parameter
49 INTO v_value;
50
51 IF (C_CONFIG_PARAMETER%NOTFOUND) THEN
52 IF (l_debug > 0) THEN
53 LOG (NULL, 'Warning: Configuration not found for (' || P_NAME || ')');
54 END IF;
55 END IF;
56
57 CLOSE c_config_parameter;
58
59 RETURN v_value;
60 END;
61
62
63 FUNCTION GET_CONFIG_PARAMETER_INT (
64 P_NAME IN VARCHAR2,
65 P_DEVICE_TYPE_ID IN NUMBER DEFAULT NULL,
66 P_BUSINESS_EVENT_ID IN NUMBER DEFAULT NULL,
67 P_SEQUENCE_ID IN NUMBER DEFAULT NULL
68 )
69 RETURN VARCHAR2
70 IS
71 -- CURSOR FOR SELECTING THE PARAMETER VALUE
72 CURSOR C_CONFIG_PARAMETER (
73 P_DEVICE_TYPE_ID IN NUMBER,
74 P_BUSINESS_EVENT_ID IN NUMBER,
75 P_SEQUENCE_ID IN NUMBER,
76 P_NAME IN VARCHAR2
77 )
78 IS
79 SELECT CONFIG_VALUE
80 FROM WMS_CAROUSEL_CONFIGURATION
81 WHERE CONFIG_NAME = P_NAME
82 AND DEVICE_TYPE_ID = P_DEVICE_TYPE_ID
83 AND BUSINESS_EVENT_ID = P_BUSINESS_EVENT_ID
84 AND SEQUENCE_ID = P_SEQUENCE_ID
85 AND ACTIVE_IND = 'Y'
86 ORDER BY DEVICE_TYPE_ID, BUSINESS_EVENT_ID, SEQUENCE_ID;
87
88 V_VALUE VARCHAR2 (4000) := NULL;
89 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
90 BEGIN
91 -- GET IT OUT OF THE CONFIGURATION TABLE
92 OPEN C_CONFIG_PARAMETER (P_DEVICE_TYPE_ID,
93 P_BUSINESS_EVENT_ID,
94 P_SEQUENCE_ID,
95 P_NAME
96 );
97
98 FETCH C_CONFIG_PARAMETER
99 INTO V_VALUE;
100
101 IF (C_CONFIG_PARAMETER%NOTFOUND) THEN
102 IF (l_debug > 0) THEN
103 LOG (NULL, 'Warning: Configuration not found for (' || P_NAME || ')');
104 END IF;
105 END IF;
106 CLOSE C_CONFIG_PARAMETER;
107
108 RETURN V_VALUE;
109 END;
110
111 --
112 --
113 PROCEDURE process_request (
114 p_request_id IN NUMBER,
115 x_status_code OUT NOCOPY VARCHAR2,
116 x_status_msg OUT NOCOPY VARCHAR2,
117 x_device_status OUT NOCOPY VARCHAR2
118 )
119 IS
120 v_task_group VARCHAR2 (128);
121 v_group_skip BOOLEAN;
122 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
123 BEGIN
124 COMMIT;
125 -- Process request tasks
126 v_group_skip := FALSE;
127
128 FOR v_task IN c_request_tasks (p_request_id)
129 LOOP
130 IF (l_debug > 0) THEN
131 LOG (v_task.device_id, 'Processing request: request_id=' || p_request_id);
132 END IF;
133 BEGIN
134 -- Skip ?
135 -- because the task is a member of a task group already processed
136 IF NOT v_group_skip
137 THEN
138 -- Is it a task complete ?
139 IF v_task.business_event_id = WMS_DEVICE_INTEGRATION_PVT.WMS_BE_TASK_COMPLETE
140 THEN
141 complete_task (v_task.relation_id, v_task.device_id, null);
142 -- Is it a task skip ?
143 ELSIF v_task.business_event_id = WMS_DEVICE_INTEGRATION_PVT.WMS_BE_TASK_SKIP
144 THEN
145 skip_task (v_task.relation_id, v_task.device_id, null);
146 -- Is it a task cancel ?
147 ELSIF v_task.business_event_id = WMS_DEVICE_INTEGRATION_PVT.WMS_BE_TASK_CANCEL
148 THEN
149 cancel_task (v_task.relation_id, v_task.device_id, null);
150 END IF;
151 -- Add task directives to the directive queue
152 add_task_directives (p_task => v_task);
153 -- Get TASK_GROUP parameter
154 v_task_group :=
155 NVL
156 (get_config_parameter
157 (p_name => 'TASK_GROUP',
158 p_device_type_id => v_task.device_type_id,
159 p_business_event_id => v_task.business_event_id
160 ),
161 'F'
162 );
163
164 -- Are the tasks groupped ?
165 IF UPPER (SUBSTR (v_task_group, 1, 1)) = 'T'
166 THEN
167 -- Skip the other tasks of the request
168 v_group_skip := TRUE;
169 END IF;
170
171 END IF;
172 EXCEPTION WHEN OTHERS THEN
173 IF (l_debug > 0) THEN
174 LOG (v_task.device_id, 'Error Processing request: request_id=' || p_request_id || ', Error=' || sqlerrm);
175 END IF;
176 END;
177 END LOOP;
178
179 -- Process the directive queue for the new tasks
180 process_directive_queue;
181 -- Status
182 x_status_code := 'S';
183 x_status_msg := 'S';
184 x_device_status := 'S';
185 END process_request;
186 --
187 -- Bug# 4666748
188 PROCEDURE response_event_handler (
189 p_device_id IN VARCHAR2,
190 p_message IN VARCHAR2,
191 x_message_code OUT NOCOPY NUMBER,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2
195 )
196 IS
197 v_response VARCHAR2 (4000);
198 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
199 BEGIN
200 if l_debug > 0 then
201 LOG(p_device_id, 'Reached response_event_handler with p_message='||p_message);
202 end if;
203 v_response := p_message;
204
205 -- Process the response
206 process_response (
207 p_device_id => p_device_id,
208 p_response => v_response
209 );
210 -- Process the directive queue for any new directives to send
211 process_directive_queue;
212 EXCEPTION
213 WHEN OTHERS
214 THEN
215 IF (l_debug > 0) THEN
216 LOG (p_device_id,'Error in receive_listener: ' || SQLERRM);
217 END IF;
218 END response_event_handler;
219 -- Bug# 4666748
220 -- API without the pipe name
221 PROCEDURE process_response (
222 p_device_id IN NUMBER,
223 p_response IN VARCHAR2
224 )
225 IS
226 -- Cursor for ERROR/FULL directives
227 CURSOR c_error_directive (
228 p_device_id IN NUMBER,
229 p_response IN VARCHAR2
230 )
231 IS
232 SELECT *
233 FROM wms_carousel_directive_queue
234 WHERE status = 'C'
235 AND NVL (NVL (device_id, p_device_id), -1) = NVL (p_device_id, -1)
236 AND NVL (p_response, 'null') LIKE NVL (response, 'null')
237 FOR UPDATE;
238
239 -- Cursor for corresponding directive
240 CURSOR c_corresponding_directive (
241 p_device_id IN NUMBER,
242 p_response IN VARCHAR2
243 )
244 IS
245 SELECT *
246 FROM wms_carousel_directive_queue
247 WHERE status = 'C'
248 AND NVL (NVL (device_id, p_device_id), -1) = NVL (p_device_id, -1)
249 AND NVL (p_response, 'null') LIKE NVL (response, 'null')
250 FOR UPDATE;
251
252 v_response VARCHAR2 (4000);
253 v_replace_response VARCHAR2 (4000);
254 v_stx VARCHAR2 (3);
255 v_etx VARCHAR2 (3);
256 l_pos NUMBER;
257 l_msg_template_id NUMBER;
258 l_return_status VARCHAR2(10);
259 l_msg_count NUMBER;
260 l_msg_data VARCHAR2(4000);
261 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
262 l_directive_cnt number := 0;
263 PRAGMA AUTONOMOUS_TRANSACTION;
264 BEGIN
265 v_response := p_response;
266 IF (l_debug > 0) THEN
267 LOG ( p_device_id, 'Reached process_response with p_response='||p_response);
268 END IF;
269
270 IF ((INSTR(p_response, 'ERROR') > 0) OR (INSTR(p_response, 'FULL') > 0)) THEN
271 IF (l_debug > 0) THEN
272 LOG ( p_device_id, 'Found an ERROR or FULL in the response '||p_response);
273 END IF;
274
275 v_replace_response := v_response;
276 --v_replace_response := replace(v_replace_response,'ERROR','DONE');
277 v_replace_response := replace(v_replace_response,'ERROR','DONE0');
278 v_replace_response := replace(v_replace_response,'FULL','DONE');
279
280 -- Mark the status of the corresponding directive as failure
281 FOR v_error_directive IN c_error_directive (
282 p_device_id => p_device_id,
283 p_response => v_replace_response
284 )
285 LOOP
286 BEGIN
287 IF (l_debug > 0) THEN
288 LOG (v_error_directive.device_id, 'Marking directive as failure, id='
289 || v_error_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
290 || ', request_id='
291 || v_error_directive.request_id
292 || ', task_id='
293 || v_error_directive.task_id
294 || ', directive='
295 || v_error_directive.directive
296 || ', request='
297 || v_error_directive.request
298 || ', zone='
299 || v_error_directive.subinventory
300 || ', attempt='
301 || (NVL (v_error_directive.attempts, 0) + 1)
302 || ', send_pipe='
303 || v_error_directive.send_pipe
304 || ', v_response='
305 || v_response
306 || ', v_replace_response='
307 || v_replace_response
308 );
309 END IF;
310 UPDATE wms_carousel_directive_queue
311 SET status = 'F'
312 WHERE CURRENT OF c_error_directive;
313
314 IF l_debug > 0 THEN
315 LOG (v_error_directive.device_id, 'After updating to Failure, CAROUSEL_DIRECTIVE_QUEUE_ID='
319 || ', task_id='
316 || v_error_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
317 || ', request_id='
318 || v_error_directive.request_id
320 || v_error_directive.task_id
321 || ', directive='
322 || v_error_directive.directive
323 || ', v_response ='
324 || v_response
325 || ', zone='
326 || v_error_directive.subinventory
327 || ', send_pipe='
328 || v_error_directive.send_pipe
329 );
330 END IF;
331
332 --
333 IF (INSTR(p_response, 'ERROR') > 0) THEN
334 --Mark the dependent directives as 'Cancelled'
335 UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
336 SET STATUS = 'X', LAST_UPDATE_DATE = SYSDATE
337 WHERE REQUEST_ID = NVL(v_error_directive.REQUEST_ID,0)
338 AND SEQUENCE_ID > NVL(v_error_directive.SEQUENCE_ID,SEQUENCE_ID);
339 ELSIF (INSTR(p_response, 'FULL') > 0) THEN
340 --Mark the dependent directives as 'Pending' for PAUSE processing
341 UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
342 SET STATUS = 'P', LAST_UPDATE_DATE = SYSDATE
343 WHERE REQUEST_ID = NVL(v_error_directive.REQUEST_ID,0)
344 AND SEQUENCE_ID > NVL(v_error_directive.SEQUENCE_ID,SEQUENCE_ID);
345 END IF;
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF (l_debug > 0) THEN
349 LOG (p_device_id, 'Error Processing response: '
350 || ', v_replace_response='
351 || v_replace_response
352 || ', v_response'
353 || v_response
354 || ', Error='
355 || sqlerrm);
356 END IF;
357 END;
358 END LOOP;
359
360 COMMIT;
361 RETURN;
362 END IF;
363
364 IF (l_debug > 0) THEN
365 LOG ( p_device_id, 'There was no ERROR/FULL in the response:'
366 ||p_response);
367 END IF;
368
369 --Resolve the message template id from the device id
370 BEGIN
371 SELECT message_template_id
372 INTO l_msg_template_id
373 FROM wms_devices_b
374 WHERE device_id = p_device_id;
375 IF l_debug > 0 THEN
376 LOG(p_device_id, 'Message template ID for device ID '
377 ||p_device_id
378 ||' is '
379 ||l_msg_template_id);
380 END IF;
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 IF l_debug > 0 THEN
384 LOG(p_device_id, 'No message template defined for device id '
385 ||p_device_id
386 ||'. '
387 ||SQLERRM);
388 END IF;
389 WHEN OTHERS THEN
390 IF l_debug > 0 THEN
391 LOG(p_device_id, SQLERRM);
392 END IF;
393 END;
394
395
396 -- Mark the status of the corresponding directive as success
397 FOR v_directive IN c_corresponding_directive (
398 p_device_id => p_device_id,
399 p_response => v_response
400 )
401 LOOP
402 l_directive_cnt := l_directive_cnt + 1;
403 IF l_debug > 0 THEN
404 LOG(v_directive.device_id, 'Calling parse Device Response with params device_id='
405 ||v_directive.device_id
406 ||', p_request_id='
407 ||v_directive.request_id
408 ||', msg_template_id='
409 ||l_msg_template_id
410 ||', response='
411 ||v_directive.response);
412 END IF;
413 parse_device_response (
414 p_device_id => v_directive.device_id,
415 p_request_id => v_directive.request_id,
416 p_msg => v_directive.response,
417 p_template_id => l_msg_template_id,
418 x_return_status => l_return_status,
419 x_msg_count => l_msg_count,
420 x_msg_data => l_msg_data
421 );
422
423 IF (l_debug > 0) THEN
424 LOG(v_directive.device_id, 'After calling parse_device_response. l_return_status is '||l_return_status);
425 END IF;
426
427 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
428 IF (l_debug > 0) THEN
429 LOG(v_directive.device_id, 'Error calling parse_device_response. '||SQLERRM);
430 END IF;
431 RAISE fnd_api.g_exc_unexpected_error;
432 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
433 IF (l_debug > 0) THEN
434 LOG(v_directive.device_id, 'Error calling parse_device_response. '||SQLERRM);
435 END IF;
436 RAISE fnd_api.g_exc_error;
437 END IF;
438
439 IF (l_debug > 0) THEN
440 LOG (v_directive.device_id, 'Marking directive as success, id='
441 || v_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
442 || ', request_id='
443 || v_directive.request_id
444 || ', task_id='
445 || v_directive.task_id
446 || ', directive='
450 || ', zone='
447 || v_directive.directive
448 || ', request='
449 || v_directive.request
451 || v_directive.subinventory
452 || ', attempt='
453 || (NVL (v_directive.attempts, 0) + 1)
454 || ', send_pipe='
455 || v_directive.send_pipe
456 );
457 END IF;
458 UPDATE wms_carousel_directive_queue
459 SET status = 'S'
460 WHERE CURRENT OF c_corresponding_directive;
461 END LOOP;
462
463 -- If no corresponding directives were found, we still need to process the response
464 IF l_directive_cnt = 0 THEN
465 IF l_debug > 0 THEN
466 LOG(v_directive.device_id, 'No matching directives. Calling parse Dev Resp with params dev_id='
467 ||p_device_id
468 ||', msg_template_id='
469 ||l_msg_template_id
470 ||', v_response='
471 ||v_response
472 ||', p_response='
473 ||p_response);
474 END IF;
475
476 parse_device_response (
477 p_device_id => p_device_id,
478 p_request_id => null,
479 p_msg => v_response,
480 p_template_id => l_msg_template_id,
481 x_return_status => l_return_status,
482 x_msg_count => l_msg_count,
483 x_msg_data => l_msg_data
484 );
485
486 END IF;
487
488 COMMIT;
489 EXCEPTION
490 WHEN OTHERS THEN
491 IF (l_debug > 0) THEN
492 LOG (p_device_id, '*Error in process_response: '
493 || SQLERRM);
494 END IF;
495 END process_response;
496 --
497 --
498 PROCEDURE add_task_directives (p_task IN c_request_tasks%ROWTYPE)
499 IS
500 -- Cursor for sequence id's
501 CURSOR c_sequence_ids (p_device_type_id IN NUMBER, P_BUSINESS_EVENT_ID IN NUMBER)
502 IS
503 SELECT sequence_id, config_value
504 FROM wms_carousel_configuration
505 WHERE CONFIG_NAME = 'DIRECTIVE'
506 AND device_type_id = p_device_type_id
507 AND BUSINESS_EVENT_ID = P_BUSINESS_EVENT_ID
508 AND active_ind = 'Y'
509 ORDER BY sequence_id;
510
511 v_directive wms_carousel_directive_queue%ROWTYPE;
512 v_dir_dep_seg_id VARCHAR2 (64) := NULL;
513 v_dir_dep_seq_id NUMBER := NULL;
514 v_query VARCHAR2 (1024);
515 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
516 l_directive_count NUMBER := 0;
517 PRAGMA AUTONOMOUS_TRANSACTION;
518 BEGIN
519 IF (l_debug > 0) THEN
520 LOG (p_task.device_id, 'Adding task to directive queue: request_id='
521 || p_task.request_id
522 || ', task_id='
523 || p_task.task_id
524 || ', device_type_id='
525 || p_task.device_type_id
526 || ', device_id='
527 || p_task.device_id
528 || ', BUSINESS_EVENT_ID='
529 || p_task.BUSINESS_EVENT_ID
530 || ', sequence_id='
531 || p_task.sequence_id
532 || ', Sub='
533 || p_task.SUBINVENTORY_CODE
534 || ', Loc='
535 || p_task.LOCATOR
536 || ', quantity='
537 || p_task.quantity
538 );
539 END IF;
540 -- Get task zone
541 v_query :=
542 get_config_parameter (p_name => 'TASK_ZONE',
543 p_device_type_id => p_task.device_type_id,
544 p_business_event_id => p_task.business_event_id
545 );
546 build_directive_string -- We can use this function
547 (p_task => p_task,
548 p_query => v_query,
549 p_result => v_directive.SUBINVENTORY
550 );
551 -- Get task addr
552 v_query :=
553 get_config_parameter (p_name => 'TASK_ADDR',
554 p_device_type_id => p_task.device_type_id,
555 p_business_event_id => p_task.business_event_id
556 );
557 build_directive_string -- We can use this function
558 (p_task => p_task,
559 p_query => v_query,
560 p_result => v_directive.addr
561 );
562 v_directive.addr := NVL (v_directive.addr, '1');
563 -- Assign segments
564 v_directive.segment1 := p_task.segment1;
565 v_directive.segment2 := p_task.segment2;
566 v_directive.segment3 := p_task.segment3;
567 v_directive.segment4 := p_task.segment4;
568 v_directive.segment5 := p_task.segment5;
569 v_directive.segment6 := p_task.segment6;
570 v_directive.segment7 := p_task.segment7;
571 v_directive.segment8 := p_task.segment8;
572 v_directive.segment9 := p_task.segment9;
573 v_directive.segment10 := p_task.segment10;
574 -- Loop for directives in the configuration table
575 v_directive.sequence_id := 0;
576 v_directive.device_id := p_task.device_id;
580
577 v_directive.subinventory := p_task.subinventory_code;
578 v_directive.device_type_id := p_task.device_type_id;
579 v_directive.business_event_id := p_task.business_event_id;
581 FOR v_seq_id IN c_sequence_ids (v_directive.device_type_id,
582 v_directive.business_event_id
583 )
584 LOOP
585 l_directive_count := l_directive_count + 1;
586 v_directive.sequence_id := v_seq_id.sequence_id;
587 -- Get next directive
588
589 v_directive.directive := v_seq_id.config_value;
590
591 /*
592 v_directive.directive :=
593 get_config_parameter (p_name => 'DIRECTIVE',
594 p_device_type_id => p_task.device_type_id,
595 p_zone => v_directive.subinventory,
596 p_sequence_id => v_directive.sequence_id
597 );
598 */
599
600 -- Get directive configuration parameters
601 get_directive_config (p_task => p_task,
602 p_directive => v_directive,
603 p_dir_dep_seg_id => v_dir_dep_seg_id,
604 p_dir_dep_seq_id => v_dir_dep_seq_id
605 );
606 -- Add the directive to the queue
607 add_directive_to_queue (p_task => p_task,
608 p_directive => v_directive,
609 p_dir_dep_seg_id => v_dir_dep_seg_id,
610 p_dir_dep_seq_id => v_dir_dep_seq_id
611 );
612 END LOOP;
613 IF (l_debug > 0 AND l_directive_count = 0) THEN
614 LOG (p_task.device_id, 'Warning: No directives defined for Device Type ID=' || v_directive.device_type_id || ', Buss Event ID='
615 || v_directive.business_event_id);
616 END IF;
617 COMMIT;
618 END;
619
620 --
621 --
622 PROCEDURE get_directive_config (
623 p_task IN wms_device_requests_wcsv%ROWTYPE,
624 p_directive IN OUT NOCOPY wms_carousel_directive_queue%ROWTYPE,
625 p_dir_dep_seg_id OUT NOCOPY VARCHAR2,
626 p_dir_dep_seq_id OUT NOCOPY NUMBER
627 )
628 IS
629 v_pipe varchar2(40);
630 BEGIN
631 -- Get directive dependency segment id
632 p_dir_dep_seg_id :=
633 get_config_parameter_int (p_name => 'DIRECTIVE_DEPENDENCY_SEG_ID',
634 p_device_type_id => p_task.device_type_id,
635 p_business_event_id => p_task.business_event_id,
636 p_sequence_id => p_directive.sequence_id
637 );
638 -- Get directive dependency sequence id
639 p_dir_dep_seq_id :=
640 get_config_parameter_int (p_name => 'DIRECTIVE_DEPENDENCY_SEQ_ID',
641 p_device_type_id => p_task.device_type_id,
642 p_business_event_id => p_task.business_event_id,
643 p_sequence_id => p_directive.sequence_id
644 );
645 -- Get directive request query
646 p_directive.request :=
647 get_config_parameter_int (p_name => 'DIRECTIVE_REQUEST_QUERY',
648 p_device_type_id => p_task.device_type_id,
649 p_business_event_id => p_task.business_event_id,
650 p_sequence_id => p_directive.sequence_id
651 );
652 -- Get directive response query
653 p_directive.response :=
654 get_config_parameter_int (p_name => 'DIRECTIVE_RESPONSE_QUERY',
655 p_device_type_id => p_task.device_type_id,
656 p_business_event_id => p_task.business_event_id,
657 p_sequence_id => p_directive.sequence_id
658 );
659 -- Get directive response timeout
660 p_directive.response_timeout :=
661 NVL
662 (get_config_parameter_int (p_name => 'DIRECTIVE_RESPONSE_TIMEOUT',
663 p_device_type_id => p_task.device_type_id,
664 p_business_event_id => p_task.business_event_id,
665 p_sequence_id => p_directive.sequence_id
666 ),
667 20
668 );
669 -- Get directive attempts
670 p_directive.max_attempts :=
671 NVL
672 (get_config_parameter_int (p_name => 'DIRECTIVE_MAX_ATTEMPTS',
673 p_device_type_id => p_task.device_type_id,
674 p_business_event_id => p_task.business_event_id,
675 p_sequence_id => p_directive.sequence_id
676 ),
677 3
678 );
679
680 --Bug# 4311016
681 v_pipe :=
682 NVL (get_config_parameter (p_name => 'DIRECTIVE_PIPE_NAME',
683 p_device_type_id => p_task.device_type_id,
684 p_business_event_id => p_task.business_event_id,
685 p_sequence_id => p_directive.sequence_id),
686 NVL (get_config_parameter (p_name => 'PIPE_NAME',
687 p_sequence_id => p_task.device_id ),
691 p_directive.send_pipe := 'OUT_' || v_pipe;
688 p_task.device_id )
689 );
690
692 p_directive.receive_pipe := 'IN_' || v_pipe;
693
694 --Bug# 4311016
695 -- This allows for the directive_queue table to have a different Pipe_Name and Device_id in sequence.
696 -- 'add_directive_to_queue' uses the device_id from the WDR record structure to insert into the queue table
697 -- 'receive_pipe_listener' calls the 'process_response' procedure with the related device_id of the pipe
698 -- which will find the current record in the queue table.
699
700 p_directive.device_id := NVL (get_config_parameter (
701 p_name => 'DIRECTIVE_DEVICE_ID',
702 p_device_type_id => p_task.device_type_id,
703 p_business_event_id => p_task.business_event_id,
704 p_sequence_id => p_directive.sequence_id ),
705 p_task.device_id
706 );
707
708 -- Get pipe timeout
709 p_directive.pipe_timeout :=
710 NVL
711 (get_config_parameter (p_name => 'PIPE_TIMEOUT',
712 p_device_type_id => p_task.device_type_id
713 ),
714 5
715 );
716 END;
717
718 PROCEDURE add_directive_to_queue (
719 p_task IN wms_device_requests_wcsv%ROWTYPE,
720 p_directive IN OUT NOCOPY wms_carousel_directive_queue%ROWTYPE,
721 p_dir_dep_seg_id IN VARCHAR2,
722 p_dir_dep_seq_id IN NUMBER
723 )
724 IS
725 v_query VARCHAR2(4000);
726 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
727 l_directive_queue_status VARCHAR2 (3);
728 BEGIN
729 IF (l_debug > 0) THEN
730 LOG (p_task.device_id, 'Building directive strings: request_id='
731 || p_task.request_id
732 || ', task_id='
733 || p_task.task_id
734 || ', directive='
735 || p_directive.directive
736 );
737 END IF;
738 -- Build request and response strings
739 v_query := p_directive.request;
740 build_directive_string (p_task => p_task,
741 p_query => v_query,
742 p_result => p_directive.request
743 );
744 v_query := p_directive.response;
745 build_directive_string (p_task => p_task,
746 p_query => v_query,
747 p_result => p_directive.response
748 );
749 -- Get dependency id
750 p_directive.prev_id :=
751 get_dependency_id (p_directive => p_directive,
752 p_dir_dep_seg_id => p_dir_dep_seg_id,
753 p_dir_dep_seq_id => p_dir_dep_seq_id
754 );
755 l_directive_queue_status :=
756 NVL
757 (get_config_parameter(
758 p_name => 'DIRECTIVE_QUEUE_STATUS',
759 p_device_type_id => p_directive.device_type_id,
760 p_business_event_id => p_directive.business_event_id,
761 p_sequence_id => p_directive.sequence_id
762 ),
763 'P'
764 );
765
766 IF (l_debug > 0) THEN
767 LOG ( p_task.device_id, 'Adding directive to queue: request_id='
768 || p_task.request_id
769 || ', task_id='
770 || p_task.task_id
771 || ', directive='
772 || p_directive.directive
773 || ', request='
774 || p_directive.request
775 || ', response='
776 || p_directive.response
777 || ', l_directive_queue_status='
778 || l_directive_queue_status
779 );
780 END IF;
781 INSERT INTO wms_carousel_directive_queue
782 (CAROUSEL_DIRECTIVE_QUEUE_ID , request_id, task_id,
783 sequence_id, SUBINVENTORY,
784 directive, prev_id,
785 request, response,
786 response_timeout, max_attempts,
787 status, send_pipe, pipe_timeout,
788 receive_pipe, device_id,
789 device_type_id, addr,
790 segment1, segment2,
791 segment3, segment4,
792 segment5, segment6,
793 segment7, segment8,
794 segment9, segment10
795 ,LAST_UPDATE_DATE
796 ,LAST_UPDATED_BY
797 ,CREATION_DATE
798 ,CREATED_BY
799 ,LAST_UPDATE_LOGIN
800 ,business_event_id
801 )
802 VALUES (WMS_CAROUSEL_DIRECTIVE_QUEUE_S.NEXTVAL, p_task.request_id, p_task.task_id,
803 p_directive.sequence_id, p_directive.subinventory,
804 p_directive.directive, p_directive.prev_id,
805 p_directive.request, p_directive.response,
806 p_directive.response_timeout, p_directive.max_attempts,
807 l_directive_queue_status, p_directive.send_pipe, p_directive.pipe_timeout,
808 p_directive.receive_pipe, p_directive.device_id,
809 p_directive.device_type_id, p_directive.addr,
810 p_directive.segment1, p_directive.segment2,
811 p_directive.segment3, p_directive.segment4,
812 p_directive.segment5, p_directive.segment6,
816 ,fnd_global.user_id
813 p_directive.segment7, p_directive.segment8,
814 p_directive.segment9, p_directive.segment10
815 ,SYSDATE
817 ,SYSDATE
818 ,fnd_global.user_id
819 ,fnd_global.login_id
820 ,p_directive.business_event_id
821 );
822 END;
823
824 --
825 --
826 PROCEDURE build_directive_string (
827 p_task IN wms_device_requests_wcsv%ROWTYPE,
828 p_query IN VARCHAR2,
829 p_result OUT NOCOPY VARCHAR2
830 )
831 IS
832 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
833 BEGIN
834 -- Do nothing if empty
835 IF (p_query is NULL) --NVL (p_query, 'null') = 'null'
836 THEN
837 IF (l_debug > 0) THEN
838 LOG (p_task.device_id, 'NULL Query and hence doing nothing');
839 END IF;
840 p_result := NULL;
841 RETURN;
842 END IF;
843 IF (UPPER(SUBSTR(LTRIM(P_QUERY),1,6)) = 'SELECT') THEN
844 IF (l_debug > 0) THEN
845 LOG (p_task.device_id, 'Executing dynamic query: ' || p_query || ',using: ' || p_task.request_id ||','|| p_task.task_id);
846 END IF;
847 EXECUTE IMMEDIATE p_query
848 INTO p_result
849 USING p_task.request_id, p_task.task_id;
850 ELSE
851 IF (l_debug > 0) THEN
852 LOG (p_task.device_id, '(' || p_query || ') is not a query, hence returning the same.');
853 END IF;
854 p_result := p_query;
855 END IF;
856 -- Execute the dynamic SQL query
857 EXCEPTION
858 WHEN OTHERS
859 THEN
860 IF (l_debug > 0) THEN
861 LOG (p_task.device_id, 'Error executing query: ' || SQLERRM);
862 END IF;
863 END;
864
865 --
866 --
867 FUNCTION get_dependency_id (
868 p_directive IN wms_carousel_directive_queue%ROWTYPE,
869 p_dir_dep_seg_id IN VARCHAR2,
870 p_dir_dep_seq_id IN NUMBER
871 )
872 RETURN NUMBER
873 IS
874 v_segment VARCHAR2 (64);
875 v_segment_formula VARCHAR2 (128);
876 v_seg_id NUMBER;
877 v_query VARCHAR2 (1024);
878 v_dep_id NUMBER;
879 i NUMBER;
880 v_pos1 NUMBER;
881 v_pos2 NUMBER;
882 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
883 BEGIN
884 -- Any dependency speficied ?
885 IF NVL (p_dir_dep_seg_id, 'null') = 'null'
886 OR NVL (p_dir_dep_seq_id, 0) = 0
887 THEN
888 RETURN NULL;
889 END IF;
890
891 -- Build the padded segment value
892 i := 0;
893 v_pos1 := 0;
894 v_segment := '';
895 v_segment_formula := '';
896
897 LOOP
898 -- Get a single segment id
899 v_pos2 := INSTR (p_dir_dep_seg_id || ',', ',', v_pos1 + 1);
900 EXIT WHEN NVL (v_pos2, 0) = 0;
901 v_seg_id :=
902 SUBSTR (p_dir_dep_seg_id, v_pos1 + 1, v_pos2 - v_pos1 - 1);
903 v_pos1 := v_pos2;
904 i := i + 1;
905 -- Pad segment value
906 v_segment_formula :=
907 v_segment_formula || '||''.''||segment' || v_seg_id;
908
909 IF v_seg_id = 1
910 THEN
911 v_segment := v_segment || '.' || p_directive.segment1;
912 ELSIF v_seg_id = 2
913 THEN
914 v_segment := v_segment || '.' || p_directive.segment2;
915 ELSIF v_seg_id = 3
916 THEN
917 v_segment := v_segment || '.' || p_directive.segment3;
918 ELSIF v_seg_id = 4
919 THEN
920 v_segment := v_segment || '.' || p_directive.segment4;
921 ELSIF v_seg_id = 5
922 THEN
923 v_segment := v_segment || '.' || p_directive.segment5;
924 ELSIF v_seg_id = 6
925 THEN
926 v_segment := v_segment || '.' || p_directive.segment6;
927 ELSIF v_seg_id = 7
928 THEN
929 v_segment := v_segment || '.' || p_directive.segment7;
930 ELSIF v_seg_id = 8
931 THEN
932 v_segment := v_segment || '.' || p_directive.segment8;
933 ELSIF v_seg_id = 9
934 THEN
935 v_segment := v_segment || '.' || p_directive.segment9;
936 ELSIF v_seg_id = 10
937 THEN
938 v_segment := v_segment || '.' || p_directive.segment10;
939 END IF;
940 END LOOP;
941
942 -- No segment value - no dependency
943 IF NVL (v_segment, 'null') = 'null'
944 OR NVL (v_segment_formula, 'null') = 'null'
945 THEN
946 RETURN NULL;
947 END IF;
948
949 -- Remove extra || from the formula and . from segment
950 v_segment_formula := SUBSTR (v_segment_formula, 8);
951 v_segment := SUBSTR (v_segment, 2);
952 -- Obtain dependency id
953 IF (l_debug > 0) THEN
954 LOG (p_directive.device_id, 'Dependency lookup, dep_seg_id='
955 || p_dir_dep_seg_id
956 || ', dep_seq_id='
957 || p_dir_dep_seq_id
958 || ', padded segment='
959 || v_segment
960 || ', segment formula='
961 || v_segment_formula
962 );
963 END IF;
964 v_query :=
965 'select max(CAROUSEL_DIRECTIVE_QUEUE_ID) '
969 || ' and '
966 || ' from wms_carousel_directive_queue'
967 || ' where sequence_id='
968 || ':dir_dep_seq_id'
970 || ':seg_formula'
971 || '='
972 || ':seg';
973 IF (l_debug > 0) THEN
974 LOG (p_directive.device_id, 'Executing dynamic query: ' || v_query);
975 END IF;
976
977 EXECUTE IMMEDIATE v_query
978 INTO v_dep_id
979 USING p_dir_dep_seq_id, v_segment_formula, v_segment;
980
981 IF (l_debug > 0) THEN
982 LOG (p_directive.device_id, 'Dependency lookup, dependency_id=' || v_dep_id);
983 END IF;
984 RETURN v_dep_id;
985 END;
986
987 --
988 --
989 PROCEDURE process_directive_queue
990 IS
991 -- Cursor for directives to process
992 CURSOR c_directives_to_process
993 IS
994 SELECT *
995 FROM wms_carousel_directive_queue a
996 WHERE ( -- this set of clauses is for timed-out directives
997 status = 'C' -- has to be current
998 AND (SYSDATE - last_attempt) * 24 * 60 * 60 >= response_timeout
999 )
1000 OR ( -- this set of clauses is for independent directives
1001 NVL(status,'P') = 'P' -- has to be a new directive
1002 AND CAROUSEL_DIRECTIVE_QUEUE_ID = -- has to be the first of such by sequence
1003 (SELECT MIN (CAROUSEL_DIRECTIVE_QUEUE_ID)
1004 FROM wms_carousel_directive_queue b
1005 WHERE b.request_id = a.request_id
1006 -- same request
1007 AND b.task_id = a.task_id -- same task
1008 AND NVL (b.status, 'P') in ('P','C')
1009 -- include current ones
1010 )
1011 AND ( -- has to be independent
1012 prev_id IS NULL -- either independent by itself
1013 OR NOT EXISTS -- or the predecessor is finished
1014 (
1015 SELECT *
1016 FROM wms_carousel_directive_queue c
1017 WHERE c.CAROUSEL_DIRECTIVE_QUEUE_ID = a.prev_id -- dependency
1018 AND NVL (c.status, 'P') in ('P','C')
1019 -- include current ones
1020 )
1021 )
1022 )
1023 ORDER BY CAROUSEL_DIRECTIVE_QUEUE_ID
1024 FOR UPDATE;
1025
1026 v_status wms_carousel_directive_queue.status%TYPE;
1027 v_count NUMBER := 1;
1028 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1029 l_exec_action VARCHAR2 (4000);
1030 l_directive_pause_delay NUMBER;
1031 l_directive_cascade_failure VARCHAR2 (4000);
1032 PRAGMA AUTONOMOUS_TRANSACTION;
1033 BEGIN
1034 WHILE v_count > 0
1035 LOOP
1036 v_count := 0;
1037
1038 -- Process all independent directives
1039 FOR v_directive IN c_directives_to_process
1040 LOOP
1041 v_count := v_count + 1;
1042
1043 -- Check the number of attempts
1044 IF NVL (v_directive.attempts, 0) >= v_directive.max_attempts
1045 THEN
1046 --
1047 --
1048 --
1049 l_directive_cascade_failure :=
1050 NVL
1051 (get_config_parameter(
1052 p_name => 'DIRECTIVE_CASCADE_FAILURE',
1053 p_device_type_id => v_directive.device_type_id,
1054 p_business_event_id => v_directive.business_event_id,
1055 p_sequence_id => v_directive.sequence_id
1056 ),
1057 'N'
1058 );
1059
1060 IF l_directive_cascade_failure = 'Y' THEN
1061 -- Mark as Failure
1062 -- The current directive as well as all the rest of them in sequence
1063 UPDATE wms_carousel_directive_queue
1064 SET status = 'F', last_attempt = SYSDATE
1065 WHERE request_id = v_directive.request_id
1066 AND task_id = v_directive.task_id
1067 AND sequence_id >= v_directive.sequence_id;
1068 ELSE
1069 -- Mark as Failure
1070 -- Only the current directive
1071 UPDATE wms_carousel_directive_queue
1072 SET status = 'F', last_attempt = SYSDATE
1073 WHERE request_id = v_directive.request_id
1074 AND task_id = v_directive.task_id
1075 AND directive = v_directive.directive;
1076 END IF;
1077
1078 -- Notify the WMS of the failure
1079 notify_failure_to_wms (v_directive.request_id,
1080 v_directive.task_id
1081 );
1082 IF (l_debug > 0) THEN
1083 LOG
1084 (v_directive.device_id, 'Maximum timeout attempts reached, marking as failure, id='
1085 || v_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
1086 || ', request_id='
1087 || v_directive.request_id
1088 || ', task_id='
1089 || v_directive.task_id
1093 || v_directive.subinventory
1090 || ', directive='
1091 || v_directive.directive
1092 || ', zone='
1094 || ', attempt='
1095 || (NVL (v_directive.attempts, 0) + 1)
1096 );
1097 END IF;
1098 ELSE
1099 -- Empty request = no request to send
1100 IF NVL (v_directive.request, 'null') = 'null'
1101 THEN
1102 IF (l_debug > 0) THEN
1103 LOG (v_directive.device_id, 'No request to be sent, id='
1104 || v_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
1105 || ', request_id='
1106 || v_directive.request_id
1107 || ', task_id='
1108 || v_directive.task_id
1109 || ', directive='
1110 || v_directive.directive
1111 || ', zone='
1112 || v_directive.subinventory
1113 );
1114 END IF;
1115 ELSE
1116 -- Bug# 4311016
1117 -- Query if the directive has a pause delay associated in the config table
1118 -- If yes then call a private method to wait for the specified duration
1119 -- If there is no directive then the default value is 0 (no delay)
1120 l_directive_pause_delay :=
1121 NVL
1122 (get_config_parameter_int(
1123 p_name => 'DIRECTIVE_PAUSE_DELAY',
1124 p_device_type_id => v_directive.device_type_id,
1125 p_business_event_id => v_directive.business_event_id,
1126 p_sequence_id => v_directive.sequence_id
1127 ),
1128 0
1129 );
1130
1131 IF (l_debug > 0) THEN
1132 LOG (v_directive.device_id, 'DIRECTIVE_PAUSE_DELAY set to: '
1133 ||l_directive_pause_delay
1134 || ' seconds');
1135 END IF;
1136
1137 IF l_directive_pause_delay > 0 THEN
1138 IF (l_debug > 0) THEN
1139 LOG (v_directive.device_id, 'Calling pause_directive to wait for '
1140 ||l_directive_pause_delay
1141 ||' seconds');
1142 END IF;
1143 pause_directive(
1144 p_device_id => v_directive.device_id,
1145 p_delay_in_seconds => l_directive_pause_delay
1146 );
1147 END IF;
1148
1149 -- place the 'DIRECTIVE_EXECUTE_ACTION'
1150 -- we have an entry in the request column, however, since there is an associated ACTION
1151 -- we DO NOT send to the PIPE, we execute
1152 l_exec_action :=
1153 NVL
1154 (get_config_parameter(
1155 p_name => 'DIRECTIVE_EXECUTE_ACTION',
1156 p_device_type_id => v_directive.device_type_id,
1157 p_business_event_id => v_directive.business_event_id,
1158 p_sequence_id => v_directive.sequence_id
1159 ),
1160 'N'
1161 );
1162 IF (l_exec_action = 'Y')
1163 THEN
1164 IF (l_debug > 0) THEN
1165 LOG (v_directive.device_id, 'EXECUTE ACTION enabled, executing: '
1166 ||v_directive.request
1167 || 'USING request_id='
1168 || v_directive.request_id
1169 || ', task_id='
1170 || v_directive.task_id);
1171 END IF;
1172 EXECUTE IMMEDIATE v_directive.request
1173 USING v_directive.request_id, v_directive.task_id;
1174 ELSE
1175 IF (l_debug > 0) THEN
1176 LOG (v_directive.device_id, 'Sending current directive, id='
1177 || v_directive.CAROUSEL_DIRECTIVE_QUEUE_ID
1178 || ', request_id='
1179 || v_directive.request_id
1180 || ', task_id='
1181 || v_directive.task_id
1182 || ', directive='
1183 || v_directive.directive
1184 || ', request='
1185 || v_directive.request
1186 || ', zone='
1187 || v_directive.subinventory
1188 || ', attempt='
1189 || (NVL (v_directive.attempts, 0) + 1)
1190 || ', send_pipe='
1191 || v_directive.send_pipe
1192 );
1193 END IF;
1194 -- Push the directive into the pipe
1195 send_directive (v_directive.device_id,
1196 v_directive.send_pipe,
1197 v_directive.addr,
1198 v_directive.request,
1199 v_directive.pipe_timeout
1200 );
1201 END IF;
1202 END IF;
1203
1204 -- Empty response = no need to wait for response
1205 IF NVL (v_directive.response, 'null') = 'null'
1206 THEN
1207 -- Mark as success right away
1208 v_status := 'S';
1209 ELSE
1210 -- Current, wait for response
1211 v_status := 'C';
1212 END IF;
1213
1214 -- Update the directive status, set attempts and last_attempt
1215 UPDATE wms_carousel_directive_queue
1216 SET status = v_status,
1217 attempts = NVL (v_directive.attempts, 0) + 1,
1218 last_attempt = SYSDATE
1219 WHERE CURRENT OF c_directives_to_process;
1220 END IF;
1221 END LOOP;
1222 END LOOP;
1223
1227 purge_queue;
1224 -- Commit the changes
1225 COMMIT;
1226 -- Purge the directive queue
1228
1229 EXCEPTION
1230 WHEN OTHERS
1231 THEN
1232 IF (l_debug > 0) THEN
1233 LOG (v_directive.device_id, 'Error executing query: ' || SQLERRM);
1234 END IF;
1235 END;
1236
1237 --
1238 --
1239 PROCEDURE send_directive (
1240 p_device_id IN NUMBER,
1241 p_pipe_name IN VARCHAR2,
1242 p_addr IN VARCHAR2,
1243 p_directive IN VARCHAR2,
1244 p_time_out IN NUMBER
1245 )
1246 IS
1247 v_pipe_size INTEGER := 1048576; -- size of the pipe in bytes
1248 BEGIN
1249 -- pack the data
1250 DBMS_PIPE.reset_buffer;
1251 DBMS_PIPE.pack_message (NVL (p_addr, '1'));
1252 DBMS_PIPE.pack_message (NVL (p_directive, 'empty'));
1253
1254 -- push it into the pipe
1255 IF (DBMS_PIPE.send_message (p_pipe_name, p_time_out, v_pipe_size) <> 0)
1256 THEN
1257 RAISE send_pipe_exception;
1258 END IF;
1259 END;
1260
1261 --
1262 --
1263 FUNCTION checksum (p_data IN VARCHAR2)
1264 RETURN NUMBER
1265 IS
1266 v_csum NUMBER := 0;
1267 BEGIN
1268 -- Sum up ascii values
1269 FOR i IN 1 .. LENGTH (p_data)
1270 LOOP
1271 v_csum := v_csum + ASCII (SUBSTR (p_data, i, 1));
1272 END LOOP;
1273
1274 -- Modulus (same as double amp 0x7F, the spec has redundant steps)
1275 -- v_csum := MOD (v_csum, 128);
1276
1277 -- Or 0x40, means set the 2^6 bit
1278 -- At this point means add 64, if it is less then 64
1279 IF v_csum < 64
1280 THEN
1281 v_csum := v_csum + 64;
1282 END IF;
1283
1284 -- Return it
1285 RETURN v_csum;
1286 END;
1287
1288 --
1289 --
1290 FUNCTION hex (p_data IN NUMBER)
1291 RETURN VARCHAR2
1292 IS
1293 TYPE hex_map IS VARRAY (16) OF VARCHAR2 (1);
1294
1295 v_map hex_map
1296 := hex_map ('0',
1297 '1',
1298 '2',
1299 '3',
1300 '4',
1301 '5',
1302 '6',
1303 '7',
1304 '8',
1305 '9',
1306 'A',
1307 'B',
1308 'C',
1309 'D',
1310 'E',
1311 'F'
1312 );
1313 v_hex VARCHAR2 (8) := '';
1314 v_dgt NUMBER;
1315 v_pow NUMBER := 1;
1316 BEGIN
1317 -- Translate by digit
1318 LOOP
1319 EXIT WHEN v_pow > p_data;
1320 v_dgt := MOD (TRUNC (p_data / v_pow), 16);
1321 v_hex := v_map (v_dgt + 1) || v_hex;
1322 v_pow := v_pow * 16;
1323 END LOOP;
1324
1325 -- Return it
1326 RETURN v_hex;
1327 END;
1328
1329 --
1330 --
1331 PROCEDURE LOG (p_device_id in number, p_data IN VARCHAR2)
1332 IS
1333 cnt NUMBER;
1334 -- PRAGMA AUTONOMOUS_TRANSACTION;
1335 BEGIN
1336 inv_log_util.trace('MHP:Device='
1337 || p_device_id
1338 || ':'
1339 || p_data, 'WMS_CAROUSEL_INTEGRATION_PVT', 9);
1340 /*
1341 Commented out for Bug# 4624894
1342
1343 INSERT INTO wms_carousel_log
1344 (CAROUSEL_LOG_ID
1345 ,text
1346 ,device_id
1347 ,LAST_UPDATE_DATE
1348 ,LAST_UPDATED_BY
1349 ,CREATION_DATE
1350 ,CREATED_BY
1351 ,LAST_UPDATE_LOGIN
1352 )
1353 VALUES (wms_carousel_log_s.NEXTVAL
1354 ,p_data
1355 ,p_device_id
1356 ,SYSDATE
1357 ,fnd_global.user_id
1358 ,SYSDATE
1359 ,fnd_global.user_id
1360 ,fnd_global.login_id
1361 );
1362
1363 COMMIT;
1364 */
1365 END;
1366
1367 --
1368 --
1369 FUNCTION get_carousel_number (p_locator IN VARCHAR2)
1370 RETURN NUMBER
1371 IS
1372 v_pos1 NUMBER;
1373 v_pos2 NUMBER;
1374 BEGIN
1375 -- Retrieve carousel from the locator (...carousel.bin.v_pos.h_pos.depth)
1376 v_pos1 := NVL (INSTR (p_locator, '.', -1, 5), 0);
1377 v_pos2 := NVL (INSTR (p_locator, '.', -1, 4), 0);
1378
1379 IF v_pos2 = 0
1380 THEN
1381 RETURN NULL;
1382 END IF;
1383
1384 RETURN SUBSTR (p_locator, v_pos1 + 1, v_pos2 - v_pos1 - 1);
1385 END;
1386 --
1387 --
1388 PROCEDURE notify_failure_to_wms (p_request_id IN NUMBER, p_task_id IN NUMBER)
1389 IS
1390 BEGIN
1391 NULL;
1392 END;
1393
1394 --
1395 --
1396 PROCEDURE update_queue (p_request_id IN NUMBER, p_device_id IN NUMBER, P_STATUS IN VARCHAR2, p_config_name IN VARCHAR2, p_task_id IN NUMBER DEFAULT NULL)
1397 IS
1398 CURSOR c_current_msg (p_request_id IN NUMBER,
1399 p_config_name IN VARCHAR2,
1400 p_task_id IN NUMBER)
1401 IS
1402 SELECT *
1403 FROM WMS_CAROUSEL_DIRECTIVE_QUEUE q
1404 WHERE q.REQUEST_ID = P_REQUEST_ID
1408 (SELECT CONFIG_VALUE
1405 AND NVL(q.TASK_ID,0) = NVL(P_TASK_ID,NVL(q.TASK_ID,0))
1406 AND NVL (q.STATUS, 'P') IN ('C', 'P')
1407 AND q.DIRECTIVE IN
1409 FROM WMS_CAROUSEL_CONFIGURATION
1410 WHERE CONFIG_NAME = NVL(p_config_name,'DIRECTIVE_QUEUE_UPDATE')
1411 AND DEVICE_TYPE_ID = q.DEVICE_TYPE_ID
1412 AND BUSINESS_EVENT_ID = q.BUSINESS_EVENT_ID
1413 AND SEQUENCE_ID = q.SEQUENCE_ID
1414 AND ACTIVE_IND = 'Y')
1415 ORDER BY q.REQUEST_ID, q.DEVICE_TYPE_ID, q.BUSINESS_EVENT_ID, q.SEQUENCE_ID
1416 FOR UPDATE;
1417 PRAGMA AUTONOMOUS_TRANSACTION;
1418 BEGIN
1419 -- Update the queue table and mark the corresponding directive status
1420 FOR v_current_msg IN c_current_msg (p_request_id => p_request_id,
1421 p_config_name => p_config_name,
1422 p_task_id => p_task_id)
1423 LOOP
1424 UPDATE WMS_CAROUSEL_DIRECTIVE_QUEUE Q
1425 SET STATUS = P_STATUS,
1426 LAST_UPDATE_DATE = SYSDATE
1427 WHERE CURRENT OF c_current_msg;
1428 END LOOP;
1429 COMMIT;
1430 END;
1431
1432 --
1433 PROCEDURE cancel_task (p_request_id IN NUMBER, p_device_id IN NUMBER, p_task_id IN NUMBER DEFAULT NULL)
1434 IS
1435 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1436 BEGIN
1437 IF (l_debug > 0) THEN
1438 LOG (p_device_id, 'Cancelling task at WMS request, request_id='
1439 || p_request_id
1440 || ', task_id='
1441 || p_task_id
1442 );
1443 END IF;
1444 update_queue (p_request_id, p_device_id, 'X', 'DIRECTIVE_CANCEL_TASK', p_task_id);
1445 END;
1446
1447 --
1448 PROCEDURE skip_task (p_request_id IN NUMBER, p_device_id IN NUMBER, p_task_id IN NUMBER DEFAULT NULL)
1449 IS
1450 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1451 BEGIN
1452 IF (l_debug > 0) THEN
1453 LOG (p_device_id, 'Skipping task at WMS request, request_id='
1454 || p_request_id
1455 || ', task_id='
1456 || p_task_id
1457 );
1458 END IF;
1459 update_queue (p_request_id, p_device_id, 'X', 'DIRECTIVE_SKIP_TASK', p_task_id);
1460 END;
1461
1462 --
1463 --
1464 PROCEDURE complete_task (p_request_id IN NUMBER, p_device_id IN NUMBER, p_task_id IN NUMBER DEFAULT NULL)
1465 IS
1466 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1467 BEGIN
1468 IF (l_debug > 0) THEN
1469 LOG (p_device_id, 'Completing task at WMS request, request_id='
1470 || p_request_id
1471 || ', task_id='
1472 || p_task_id
1473 );
1474 END IF;
1475 update_queue (p_request_id, p_device_id, 'S', 'DIRECTIVE_COMPLETE_TASK', p_task_id);
1476 END;
1477
1478 --
1479 --
1480 PROCEDURE purge_queue
1481 IS
1482 v_purge_interval NUMBER;
1483 PRAGMA AUTONOMOUS_TRANSACTION;
1484 BEGIN
1485 v_purge_interval :=
1486 NVL (get_config_parameter ('QUEUE_PURGE_INTERVAL'), 3600);
1487
1488 DELETE FROM wms_carousel_directive_queue
1489 WHERE status IN ('S', 'F', 'X') -- Success, failure, or cancelled
1490 AND (SYSDATE - nvl(last_attempt,LAST_UPDATE_DATE)) * 24 * 60 * 60 >= v_purge_interval;
1491
1492 /*
1493 Commented out for Bug# 4624894
1494
1495 DELETE FROM wms_carousel_log
1496 WHERE SYSDATE - LAST_UPDATE_DATE > 1;
1497 */
1498
1499 COMMIT;
1500 END;
1501
1502 --
1503 --
1504 FUNCTION get_device_type_id (p_device_id IN NUMBER)
1505 RETURN NUMBER
1506 IS
1507 v_device_type_id NUMBER := NULL;
1508 BEGIN
1509 -- Obtain device_type_id
1510 SELECT device_type_id
1511 INTO v_device_type_id
1512 FROM wms_devices_b
1513 WHERE device_id = p_device_id;
1514
1515 RETURN v_device_type_id;
1516 EXCEPTION
1517 WHEN OTHERS
1518 THEN
1519 RETURN NULL;
1520 END;
1521
1522 PROCEDURE read_pipe_content(
1523 p_device_id IN VARCHAR2,
1524 x_pipe_name OUT NOCOPY VARCHAR2,
1525 x_message_code OUT NOCOPY NUMBER,
1526 x_message OUT NOCOPY VARCHAR2,
1527 x_return_status OUT NOCOPY VARCHAR2,
1528 x_msg_count OUT NOCOPY NUMBER,
1529 x_msg_data OUT NOCOPY VARCHAR2
1530 )
1531 IS
1532 l_message_received NUMBER;
1533 /*
1534 0 - no error
1535 1 - pipe timed out
1536 2 - record in pipe too large for buffer
1537 3 - interrupt occurred
1538 ORA-23322 - insufficient privileges to write to the pipe
1539 */
1540 v_addr VARCHAR2 (64);
1541 l_got_msg VARCHAR2 (2000);
1542 l_pipe_removed NUMBER;
1543 l_reading_pipe VARCHAR2(40);
1544 l_device_pause_delay NUMBER;
1545 --4311016
1546 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1547 PRAGMA AUTONOMOUS_TRANSACTION;
1548 BEGIN
1549 x_return_status := fnd_api.g_ret_sts_success;
1550 -- Get pipe NAME
1551 l_reading_pipe :=
1552 NVL (get_config_parameter (p_name => 'PIPE_NAME',
1553 p_sequence_id => p_device_id),
1554 p_device_id
1555 );
1556 l_reading_pipe := 'OUT_' || l_reading_pipe;
1557
1558 --Receive the message from the SGA into the local buffer of session 2
1559 l_message_received := DBMS_PIPE.receive_message (l_reading_pipe);
1563 IF (l_debug > 0) THEN
1560 DBMS_PIPE.unpack_message (v_addr);
1561 DBMS_PIPE.unpack_message (l_got_msg);
1562 ---4311016
1564 LOG ( p_device_id, 'In read_pipe_content. l_reading_pipe='
1565 || l_reading_pipe
1566 || ', l_got_msg='
1567 || l_got_msg
1568 );
1569 END IF;
1570 x_pipe_name := l_reading_pipe;
1571 x_message_code := l_message_received;
1572 x_message := l_got_msg;
1573
1574 -- Query if the device has a pause delay associated in the config table
1575 -- If yes then call a private method to wait for the specified duration
1576 -- If there is no directive then the default value is 0 (no delay)
1577 l_device_pause_delay :=
1578 NVL(get_config_parameter(p_name => 'DEVICE_SEND_DELAY',
1579 p_sequence_id => p_device_id),0);
1580 IF l_device_pause_delay > 0 THEN
1581 IF (l_debug > 0) THEN
1582 LOG (p_device_id, 'DEVICE_SEND_DELAY set to: '
1583 ||l_device_pause_delay
1584 || ' seconds');
1585 LOG (p_device_id, 'Calling pause_directive to wait for '
1586 ||l_device_pause_delay
1587 ||' seconds');
1588 END IF;
1589 pause_directive(p_device_id => p_device_id,
1590 p_delay_in_seconds => l_device_pause_delay);
1591 END IF;
1592
1593 COMMIT;
1594 RETURN;
1595 EXCEPTION
1596 WHEN OTHERS
1597 THEN
1598 x_return_status := fnd_api.G_RET_STS_ERROR;
1599 x_msg_data := SQLERRM;
1600 END read_pipe_content;
1601 --
1602 --
1603 FUNCTION ascii_csv_to_string (
1604 p_ascii_csv IN VARCHAR2
1605 )
1606 RETURN VARCHAR2 IS
1607 l_string VARCHAR2 (4000) := '';
1608 l_start_position NUMBER := 1;
1609 l_instr_output NUMBER := 1;
1610 l_char_count NUMBER := 0;
1611 l_ascii_value NUMBER;
1612 l_begin VARCHAR2 (4000);
1613 l_end VARCHAR2 (4000);
1614 l_output VARCHAR2 (4000) := '';
1615 PRAGMA AUTONOMOUS_TRANSACTION;
1616 BEGIN
1617 l_string := p_ascii_csv;
1618 /*
1619 Check if the first and last character of the input string is '
1620 If yes then trim the ' from either side of the input string and return that as output
1621 With this change, at the time of setting up the message template start and end delimiters
1622 the WCS administrator can enter comma separated ASCII values like 65,66 OR just enclose
1623 a literal string like 'START' or 'END' for the runtime message parsing to happen correctly
1624 */
1625 SELECT SUBSTR (l_string, 1, 1), SUBSTR (l_string, -1, 1)
1626 INTO l_begin, l_end
1627 FROM DUAL;
1628 IF (l_begin = '''' AND l_end = '''')
1629 THEN
1630 l_output := RTRIM (LTRIM (l_string, ''''), '''');
1631 --DBMS_OUTPUT.put_line ('l_output=' || l_output);
1632 RETURN l_output;
1633 END IF;
1634 l_string := REPLACE (l_string, ' ', '');
1635 WHILE l_instr_output <> 0
1636 LOOP
1637 l_char_count := l_char_count + 1;
1638 --We assume that , will be the delimiter always here
1639 SELECT INSTR (l_string, ',', 1, l_char_count)
1640 INTO l_instr_output
1641 FROM DUAL;
1642 SELECT SUBSTR (l_string,
1643 l_start_position,
1644 ( DECODE (l_instr_output,
1645 0, LENGTH (l_string)+1,
1646 l_instr_output
1647 )
1648 - l_start_position
1649 )
1650 )
1651 INTO l_ascii_value
1652 FROM DUAL;
1653 l_output := l_output || fnd_global.local_chr (l_ascii_value);
1654 l_start_position := l_instr_output + 1;
1655 END LOOP;
1656 RETURN l_output;
1657 EXCEPTION
1658 WHEN OTHERS
1659 THEN
1660 NULL;
1661 END ascii_csv_to_string;
1662
1663 PROCEDURE get_component_details (
1664 p_device_id IN NUMBER,
1665 p_template_id IN NUMBER,
1666 p_component_no IN NUMBER,
1667 x_component_code OUT NOCOPY NUMBER,
1668 x_component_meaning OUT NOCOPY VARCHAR2,
1669 x_start_comp_delimiter OUT NOCOPY VARCHAR2,
1670 x_end_comp_delimiter OUT NOCOPY VARCHAR2,
1671 x_return_status OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER,
1673 x_msg_data OUT NOCOPY VARCHAR2
1674 )
1675 IS
1676 CURSOR msg_components (p_templ_id NUMBER, p_comp_no NUMBER)
1677 IS
1678 SELECT template_id, sequence_id, component, component_length,
1679 left_or_right_padded, padding_character, start_component_delimiter,
1680 end_component_delimiter
1681 FROM (SELECT wmc.*, ROWNUM rnum
1682 FROM (SELECT *
1683 FROM wms_msg_components
1684 WHERE template_id = p_templ_id
1685 ORDER BY sequence_id) wmc
1686 WHERE ROWNUM <= p_comp_no)
1687 WHERE rnum >= p_comp_no;
1688 l_msg_component msg_components%ROWTYPE;
1689 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1690 PRAGMA AUTONOMOUS_TRANSACTION;
1691 BEGIN
1692 x_return_status := fnd_api.g_ret_sts_success;
1693 OPEN msg_components (p_template_id, p_component_no);
1694 FETCH msg_components
1695 INTO l_msg_component;
1696
1697 SELECT lookup_code, meaning
1698 INTO x_component_code, x_component_meaning
1699 FROM mfg_lookups
1700 WHERE lookup_type = 'WMS_DEVICE_MSG_COMPONENTS'
1704 SELECT lookup_code, meaning
1701 AND lookup_code = l_msg_component.component;
1702
1703 /*
1705 INTO x_datatype_code, x_datatype_meaning
1706 FROM mfg_lookups
1707 WHERE lookup_type = 'WMS_DATA_TYPE'
1708 AND lookup_code = l_msg_component.datatype;
1709 */
1710
1711 x_start_comp_delimiter := l_msg_component.start_component_delimiter;
1712 x_end_comp_delimiter := l_msg_component.end_component_delimiter;
1713 CLOSE msg_components;
1714 EXCEPTION
1715 WHEN OTHERS
1716 THEN
1717 x_return_status := fnd_api.g_ret_sts_error;
1718 x_msg_data := SQLERRM;
1719 END get_component_details;
1720
1721 /*
1722 API name parse_device_response
1723 */
1724
1725 PROCEDURE parse_device_response (
1726 p_device_id IN NUMBER,
1727 p_request_id IN NUMBER,
1728 p_msg IN VARCHAR2,
1729 p_template_id IN NUMBER,
1730 x_return_status OUT NOCOPY VARCHAR2,
1731 x_msg_count OUT NOCOPY NUMBER,
1732 x_msg_data OUT NOCOPY VARCHAR2
1733 )
1734 IS
1735 -- The following is a typical test data set, 1 for each message type
1736 --
1737 /*
1738 p_device_id NUMBER := 441;
1739 p_msg VARCHAR2 (4000) := 'ABCERROR:PLT102:W1:BULKDEF';
1740 p_template_id NUMBER := 1;
1741 */
1742 /*
1743 p_device_id NUMBER := 442;
1744 p_msg VARCHAR2 (4000) := 'MNprTaskLUTPicked(WCS,PLT101M,54,"25-JAN-2005")PQ';
1745 p_template_id NUMBER := 2;
1746 */
1747 /*
1748 p_device_id NUMBER := 443;
1749 p_msg VARCHAR2 (4000)
1750 := 'GHIW1PLT100 0000005600DOZEN LPN128A PQ';
1751 p_template_id NUMBER := 3;
1752 */
1753 /*
1754 p_device_id NUMBER := 444;
1755 p_msg VARCHAR2 (4000)
1756 := 'GHI<?xml version="1.0" ?><MESSAGE><ORG>W1</ORG><ITEM>EMM100</ITEM><QTY>500</QTY><ACTION>SUCCESS</ACTION><LPN>LPN1023A</LPN></MESSAGE>PQ';
1757 p_template_id NUMBER := 4;
1758 */
1759 l_msg VARCHAR2 (4000);
1760 l_param_delimiter VARCHAR2 (1);
1761 --l_msg_delimiter VARCHAR2 (1);
1762 l_start_delimiter VARCHAR2 (4000);
1763 l_end_delimiter VARCHAR2 (4000);
1764 l_msg_type NUMBER;
1765 --Assumption: This is different from the param_delimiter
1766 l_no_of_msg_comps NUMBER := 0;
1767 l_instr_output NUMBER := 1;
1768 l_substr_output VARCHAR2 (4000);
1769 l_start_position NUMBER := 1;
1770 --Assumption: First character is msg_delimiter
1771 l_end_position NUMBER;
1772 l_msg_component VARCHAR2 (200);
1773 TYPE output_record IS RECORD (
1774 component_no NUMBER,
1775 component_meaning VARCHAR2 (80),
1776 component_value VARCHAR2 (240),
1777 start_comp_delimiter VARCHAR2 (80),
1778 end_comp_delimiter VARCHAR2 (80)
1779 );
1780 TYPE output_type IS TABLE OF output_record
1781 INDEX BY BINARY_INTEGER;
1782 output_table output_type;
1783 l_template_id NUMBER := 1;
1784 l_index NUMBER;
1785 CURSOR msg_template_record (p_templ_id NUMBER)
1786 IS
1787 SELECT *
1788 FROM wms_msg_templates
1789 WHERE template_id = p_templ_id;
1790 CURSOR msg_component_records (p_templ_id NUMBER)
1791 IS
1792 SELECT *
1793 FROM wms_msg_components
1794 WHERE template_id = p_templ_id
1795 ORDER BY sequence_id;
1796 l_template_record msg_template_record%ROWTYPE;
1797 l_component_record wms_msg_components%ROWTYPE;
1798 l_return_status VARCHAR2 (1);
1799 l_msg_count NUMBER;
1800 l_msg_data VARCHAR2 (240);
1801 l_device_id NUMBER;
1802 l_delimiter VARCHAR2 (1) := '(';
1803 l_occurrence NUMBER := 1;
1804 msg_component_exception EXCEPTION;
1805 -- Variables for xml message parsing
1806 l_parser xmlparser.parser;
1807 l_doc xmldom.domdocument;
1808 l_named_node_map xmldom.domnamednodemap;
1809 l_attribute_name VARCHAR2 (100);
1810 l_actual_attribute_name VARCHAR2 (100);
1811 l_attribute_value VARCHAR2 (100);
1812 l_nodelist_length NUMBER;
1813 l_dom_node xmldom.domnode;
1814 l_nodelist xmldom.domnodelist;
1815 l_attributes_buffer VARCHAR2 (4000);
1816 l_response_record WMS_WCS_DEVICE_GRP.MSG_COMPONENT_LOOKUP_TYPE;
1817 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1818 PRAGMA AUTONOMOUS_TRANSACTION;
1819 BEGIN
1820 x_return_status := fnd_api.g_ret_sts_success;
1821 l_template_id := p_template_id;
1822 l_device_id := p_device_id;
1823 OPEN msg_template_record (l_template_id);
1824 FETCH msg_template_record
1825 INTO l_template_record;
1826 l_param_delimiter :=
1827 fnd_global.local_chr (l_template_record.parameter_delimiter);
1828 --Convert the ascii csv to a string representation
1829 l_start_delimiter :=
1830 wms_carousel_integration_pvt.ascii_csv_to_string
1831 (l_template_record.start_message_delimiter);
1832 l_end_delimiter :=
1836 --l_start_delimiter := l_template_record.start_message_delimiter;
1833 wms_carousel_integration_pvt.ascii_csv_to_string
1834 (l_template_record.end_message_delimiter);
1835
1837 --l_end_delimiter := l_template_record.end_message_delimiter;
1838
1839 l_msg_type := l_template_record.wms_msg_template_type;
1840 l_msg := p_msg;
1841 IF l_debug > 0 THEN
1842 LOG (p_device_id, '****************************Start****************************');
1843 LOG (p_device_id, 'Device id is ' || p_device_id);
1844 LOG (p_device_id, 'Request id is ' || p_request_id);
1845 LOG (p_device_id, 'Template id is ' || p_template_id);
1846 LOG (p_device_id, 'Message is ' || l_msg);
1847 LOG (p_device_id, 'Message type is ' || l_msg_type);
1848 LOG (p_device_id, 'Start delimiter is ''' || l_start_delimiter||'''');
1849 LOG (p_device_id, 'End delimiter is ''' || l_end_delimiter||'''');
1850 LOG (p_device_id, 'Param delimiter is ' || l_param_delimiter);
1851 END IF;
1852
1853 l_msg := LTRIM (l_msg, l_start_delimiter);
1854 l_msg := RTRIM (l_msg);
1855 l_msg := RTRIM (l_msg, l_end_delimiter);
1856
1857 IF l_debug > 0 THEN
1858 LOG
1859 (p_device_id, 'Message after stripping start and end delimiters is '
1860 || l_msg
1861 );
1862 END IF;
1863 IF l_msg_type = wms_carousel_integration_pvt.v_xml_msg
1864 THEN
1865 FOR l_comp_record IN msg_component_records (l_template_id)
1866 LOOP
1867 --l_no_of_msg_comps := l_no_of_msg_comps + 1;
1868 l_attributes_buffer :=
1869 l_attributes_buffer || l_comp_record.xml_tag_name;
1870 END LOOP;
1871 --LOG (p_device_id, 'l_attributes_buffer = '||l_attributes_buffer);
1872 l_parser := xmlparser.newparser;
1873 xmlparser.parsebuffer (l_parser, l_msg);
1874 l_doc := xmlparser.getdocument (l_parser);
1875 -- get all elements
1876 l_nodelist := xmldom.getelementsbytagname (l_doc, '*');
1877 l_nodelist_length := xmldom.getlength (l_nodelist);
1878 -- loop through elements
1879 FOR i IN 0 .. l_nodelist_length - 1
1880 LOOP
1881 l_dom_node := xmldom.item (l_nodelist, i);
1882 --LOG (p_device_id, xmldom.getnodename (l_dom_node) || ' ');
1883 --LOG (p_device_id, '--------------');
1884 l_actual_attribute_name := xmldom.getnodename (l_dom_node);
1885 --This will come from the new component table
1886 /*
1887 IF l_actual_attribute_name IN
1888 ('ORG', 'ITEM', 'QTY', 'ACTION', 'LPN', 'DEVCOMP1')
1889 */
1890 IF (INSTR (l_attributes_buffer, l_actual_attribute_name) IS NOT NULL
1891 )
1892 THEN
1893 --LOG (p_device_id, 'i=' || TO_CHAR (i));
1894 -- get all attributes of element
1895 l_named_node_map := xmldom.getattributes (l_dom_node);
1896 l_dom_node := xmldom.getfirstchild (l_dom_node);
1897 IF xmldom.isnull (l_dom_node) = FALSE
1898 THEN
1899 l_attribute_name := xmldom.getnodename (l_dom_node);
1900 IF xmldom.getnodetype (l_dom_node) = xmldom.text_node
1901 THEN
1902 l_no_of_msg_comps := l_no_of_msg_comps + 1;
1903 --LOG(p_device_id, 'l_no_of_msg_comps'||l_no_of_msg_comps);
1904 l_attribute_value := xmldom.getnodevalue (l_dom_node);
1905 IF l_debug > 0 THEN
1906 LOG (p_device_id, 'l_attribute_name= '
1907 || l_actual_attribute_name
1908 || ' val= '
1909 || l_attribute_value
1910 );
1911 END IF;
1912 --
1913 --
1914 DECLARE
1915 BEGIN
1916 wms_carousel_integration_pvt.get_component_details
1917 (p_device_id => l_device_id,
1918 p_template_id => l_template_id,
1919 p_component_no => l_no_of_msg_comps,
1920 x_component_code => output_table
1921 (l_no_of_msg_comps).component_no,
1922 x_component_meaning => output_table
1923 (l_no_of_msg_comps).component_meaning,
1924 x_start_comp_delimiter => output_table
1925 (l_no_of_msg_comps).start_comp_delimiter,
1926 x_end_comp_delimiter => output_table
1927 (l_no_of_msg_comps).end_comp_delimiter,
1928 x_return_status => l_return_status,
1929 x_msg_count => l_msg_count,
1930 x_msg_data => l_msg_data
1931 );
1932 IF l_return_status <> fnd_api.g_ret_sts_success
1933 THEN
1934 RAISE msg_component_exception;
1935 END IF;
1936 --output_table (l_no_of_msg_comps).component_value := l_attribute_value;
1937 EXCEPTION
1938 WHEN msg_component_exception
1939 THEN
1940 x_return_status := fnd_api.g_ret_sts_error;
1941 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1942 p_data => x_msg_data
1943 );
1947 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1944 WHEN OTHERS
1945 THEN
1946 x_return_status := fnd_api.g_ret_sts_unexp_error;
1948 p_data => x_msg_data
1949 );
1950 END;
1951 --
1952 --
1953 output_table (l_no_of_msg_comps).component_value :=
1954 l_attribute_value;
1955 wms_carousel_integration_pvt.populate_response_record
1956 (
1957 p_device_id => p_device_id,
1958 p_component_code => output_table (l_no_of_msg_comps).component_no,
1959 p_msg_component => l_attribute_value,
1960 p_response_record => l_response_record
1961 );
1962 END IF;
1963 ELSE
1964 IF l_debug > 0 THEN
1965 LOG (p_device_id, 'l_attribute_name= '
1966 || l_actual_attribute_name
1967 || ' val= '
1968 || NULL
1969 );
1970 END IF;
1971 END IF;
1972 END IF;
1973 END LOOP;
1974 xmlparser.freeparser (l_parser);
1975 xmldom.freedocument (l_doc);
1976 ELSIF l_msg_type = wms_carousel_integration_pvt.v_msg_with_delimiter
1977 THEN
1978 --This is the start of parsing logic for message of type 2
1979 --Message with delimiter
1980 WHILE l_instr_output <> 0
1981 LOOP
1982 l_no_of_msg_comps := l_no_of_msg_comps + 1;
1983 SELECT INSTR (l_msg, l_param_delimiter, 1, l_no_of_msg_comps)
1984 INTO l_instr_output
1985 FROM DUAL;
1986 SELECT SUBSTR (l_msg,
1987 l_start_position,
1988 ( DECODE (l_instr_output,
1989 0, LENGTH (l_msg) + 1,
1990 l_instr_output
1991 )
1992 - l_start_position
1993 )
1994 )
1995 INTO l_msg_component
1996 FROM DUAL;
1997 DECLARE
1998 BEGIN
1999 wms_carousel_integration_pvt.get_component_details
2000 (p_device_id => l_device_id,
2001 p_template_id => l_template_id,
2002 p_component_no => l_no_of_msg_comps,
2003 x_component_code => output_table (l_no_of_msg_comps).component_no,
2004 x_component_meaning => output_table (l_no_of_msg_comps).component_meaning,
2005 x_start_comp_delimiter => output_table (l_no_of_msg_comps).start_comp_delimiter,
2006 x_end_comp_delimiter => output_table (l_no_of_msg_comps).end_comp_delimiter,
2007 x_return_status => l_return_status,
2008 x_msg_count => l_msg_count,
2009 x_msg_data => l_msg_data
2010 );
2011 IF l_return_status <> fnd_api.g_ret_sts_success
2012 THEN
2013 RAISE msg_component_exception;
2014 END IF;
2015 output_table (l_no_of_msg_comps).component_value :=
2016 l_msg_component;
2017 wms_carousel_integration_pvt.populate_response_record
2018 (
2019 p_device_id => p_device_id,
2020 p_component_code => output_table (l_no_of_msg_comps).component_no,
2021 p_msg_component => l_msg_component,
2022 p_response_record => l_response_record
2023 );
2024 EXCEPTION
2025 WHEN msg_component_exception
2026 THEN
2027 x_return_status := fnd_api.g_ret_sts_error;
2028 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2029 p_data => x_msg_data
2030 );
2031 WHEN OTHERS
2032 THEN
2033 x_return_status := fnd_api.g_ret_sts_unexp_error;
2034 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2035 p_data => x_msg_data
2036 );
2037 END;
2038 l_start_position := l_instr_output + 1;
2039 IF l_debug > 0 THEN
2040 LOG (p_device_id, 'Msg Component '
2041 || l_no_of_msg_comps
2042 || ' ---> '
2043 || l_msg_component
2044 );
2045 END IF;
2046 END LOOP;
2047 ELSIF l_msg_type = wms_carousel_integration_pvt.v_msg_without_delimiter
2048 THEN
2049 FOR l_comp_record IN msg_component_records (l_template_id)
2050 LOOP
2051 l_no_of_msg_comps := l_no_of_msg_comps + 1;
2052 SELECT SUBSTR (l_msg,
2053 l_start_position,
2054 l_comp_record.component_length
2055 )
2056 INTO l_substr_output
2057 FROM DUAL;
2058 /*
2059 LOG (p_device_id, 'l_substr_output: ''' || l_substr_output
2060 || ''''
2061 );
2062 LOG (p_device_id, 'left_or_right_padded: '
2063 || l_comp_record.left_or_right_padded
2064 );
2065 */
2069 LTRIM (l_substr_output,
2066 IF l_comp_record.left_or_right_padded = 'L'
2067 THEN
2068 l_msg_component :=
2070 fnd_global.local_chr (l_comp_record.padding_character)
2071 );
2072 ELSIF l_comp_record.left_or_right_padded = 'R'
2073 THEN
2074 l_msg_component :=
2075 RTRIM (l_substr_output,
2076 fnd_global.local_chr (l_comp_record.padding_character)
2077 );
2078 END IF;
2079 BEGIN
2080 wms_carousel_integration_pvt.get_component_details
2081 (p_device_id => l_device_id,
2082 p_template_id => l_template_id,
2083 p_component_no => l_no_of_msg_comps,
2084 x_component_code => output_table (l_no_of_msg_comps).component_no,
2085 x_component_meaning => output_table (l_no_of_msg_comps).component_meaning,
2086 x_start_comp_delimiter => output_table (l_no_of_msg_comps).start_comp_delimiter,
2087 x_end_comp_delimiter => output_table (l_no_of_msg_comps).end_comp_delimiter,
2088 x_return_status => l_return_status,
2089 x_msg_count => l_msg_count,
2090 x_msg_data => l_msg_data
2091 );
2092 IF l_return_status <> fnd_api.g_ret_sts_success
2093 THEN
2094 RAISE msg_component_exception;
2095 END IF;
2096 output_table (l_no_of_msg_comps).component_value :=
2097 l_msg_component;
2098 wms_carousel_integration_pvt.populate_response_record
2099 (
2100 p_device_id => p_device_id,
2101 p_component_code => output_table (l_no_of_msg_comps).component_no,
2102 p_msg_component => l_msg_component,
2103 p_response_record => l_response_record
2104 );
2105 EXCEPTION
2106 WHEN msg_component_exception
2107 THEN
2108 x_return_status := fnd_api.g_ret_sts_error;
2109 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2110 p_data => x_msg_data
2111 );
2112 WHEN OTHERS
2113 THEN
2114 x_return_status := fnd_api.g_ret_sts_unexp_error;
2115 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2116 p_data => x_msg_data
2117 );
2118 END;
2119 --
2120 --
2121 l_start_position := l_start_position + l_comp_record.component_length;
2122 END LOOP;
2123 ELSIF l_msg_type = wms_carousel_integration_pvt.v_vocollect_msg
2124 THEN
2125 WHILE l_instr_output <> 0
2126 LOOP
2127 l_no_of_msg_comps := l_no_of_msg_comps + 1;
2128 --LOG(p_device_id, 'l_no_of_msg_comps: '||l_no_of_msg_comps);
2129 --LOG(p_device_id, 'l_start_position: '||l_start_position);
2130 --Treat the first component as a special case
2131 IF l_no_of_msg_comps <> 1
2132 THEN
2133 l_delimiter := ',';
2134 l_occurrence := l_no_of_msg_comps - 1;
2135 END IF;
2136 --LOG(p_device_id, 'l_delimiter: '||l_delimiter);
2137 --LOG(p_device_id, 'l_occurrence: '||l_occurrence);
2138 SELECT INSTR (l_msg, l_delimiter, 1, l_occurrence)
2139 INTO l_instr_output
2140 FROM DUAL;
2141 --LOG(p_device_id, 'l_instr_output: '||l_instr_output);
2142 SELECT SUBSTR (l_msg,
2143 l_start_position,
2144 ( DECODE (l_instr_output,
2145 0, LENGTH (l_msg),
2146 l_instr_output
2147 )
2148 - l_start_position
2149 )
2150 )
2151 INTO l_msg_component
2152 FROM DUAL;
2153 --LOG('p_device_id, l_msg_component: '||l_msg_component);
2154 BEGIN
2155 wms_carousel_integration_pvt.get_component_details
2156 (p_device_id => l_device_id,
2157 p_template_id => l_template_id,
2158 p_component_no => l_no_of_msg_comps,
2159 x_component_code => output_table (l_no_of_msg_comps).component_no,
2160 x_component_meaning => output_table (l_no_of_msg_comps).component_meaning,
2161 x_start_comp_delimiter => output_table (l_no_of_msg_comps).start_comp_delimiter,
2162 x_end_comp_delimiter => output_table (l_no_of_msg_comps).end_comp_delimiter,
2163 x_return_status => l_return_status,
2164 x_msg_count => l_msg_count,
2165 x_msg_data => l_msg_data
2166 );
2167 IF l_return_status <> fnd_api.g_ret_sts_success
2168 THEN
2169 RAISE msg_component_exception;
2170 END IF;
2171 --LOG(p_device_id, 'output_table (l_no_of_msg_comps).start_comp_delimiter: '||output_table (l_no_of_msg_comps).start_comp_delimiter);
2172 --LOG(p_device_id, 'output_table (l_no_of_msg_comps).end_comp_delimiter: '||output_table (l_no_of_msg_comps).end_comp_delimiter);
2173 IF output_table (l_no_of_msg_comps).start_comp_delimiter IS NOT NULL
2174 THEN
2175 l_msg_component :=
2176 LTRIM (l_msg_component,
2177 output_table (l_no_of_msg_comps).start_comp_delimiter
2178 );
2182 l_msg_component :=
2179 END IF;
2180 IF output_table (l_no_of_msg_comps).end_comp_delimiter IS NOT NULL
2181 THEN
2183 RTRIM (l_msg_component,
2184 output_table (l_no_of_msg_comps).end_comp_delimiter
2185 );
2186 END IF;
2187 output_table (l_no_of_msg_comps).component_value :=
2188 l_msg_component;
2189 wms_carousel_integration_pvt.populate_response_record
2190 (
2191 p_device_id => p_device_id,
2192 p_component_code => output_table (l_no_of_msg_comps).component_no,
2193 p_msg_component => l_msg_component,
2194 p_response_record => l_response_record
2195 );
2196 EXCEPTION
2197 WHEN msg_component_exception
2198 THEN
2199 x_return_status := fnd_api.g_ret_sts_error;
2200 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2201 p_data => x_msg_data
2202 );
2203 WHEN OTHERS
2204 THEN
2205 x_return_status := fnd_api.g_ret_sts_unexp_error;
2206 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2207 p_data => x_msg_data
2208 );
2209 END;
2210 --l_msg := LTRIM(l_msg, l_msg_component);
2211 --LOG(p_device_id, 'Message after stripping API: '||l_msg);
2212 --Now remove the parantheses
2213 --l_msg := RTRIM(LTRIM (l_msg, '('), ')');
2214 l_start_position := l_instr_output + 1;
2215 --LOG(p_device_id, 'Message after stripping ( ): '||l_msg);
2216 END LOOP;
2217 END IF;
2218
2219 IF l_debug > 0 THEN
2220 LOG (p_device_id, 'There are '
2221 || l_no_of_msg_comps
2222 || ' components in msg '
2223 || l_msg
2224 );
2225 LOG
2226 (p_device_id, '---------------------------------------------------------------------------------------------'
2227 );
2228 LOG
2229 (p_device_id, 'CompCode| Component Meaning | ComponentValue | CStrtDelim | CEndDelim'
2230 );
2231 LOG
2232 (p_device_id, '---------------------------------------------------------------------------------------------'
2233 );
2234 END IF;
2235
2236 FOR i IN output_table.FIRST .. output_table.LAST
2237 LOOP
2238 IF l_debug > 0 THEN
2239 LOG (p_device_id, LPAD (output_table (i).component_no, 4)
2240 || ' |'
2241 || LPAD (output_table (i).component_meaning, 26)
2242 || '|'
2243 || LPAD (output_table (i).component_value, 25)
2244 || '|'
2245 || LPAD (output_table (i).start_comp_delimiter,
2246 12)
2247 || '|'
2248 || LPAD (output_table (i).end_comp_delimiter, 12)
2249 );
2250 END IF;
2251 END LOOP;
2252
2253 IF l_debug > 0 THEN
2254 LOG (p_device_id, '---------------------------------------------------------------------------------------------'
2255 );
2256 LOG (p_device_id, 'Calling log_response_record... l_debug is '
2257 || l_debug
2258 );
2259 END IF;
2260
2261 wms_carousel_integration_pvt.log_response_record
2262 (
2263 p_device_id => p_device_id,
2264 p_response_record => l_response_record
2265 );
2266
2267 IF l_debug > 0 THEN
2268 LOG (p_device_id, 'Calling wms_wcs_device_grp.process_response '
2269 || l_debug
2270 );
2271 END IF;
2272 wms_wcs_device_grp.process_response
2273 (
2274 p_device_id => p_device_id,
2275 p_request_id => p_request_id,
2276 p_param_values_record => l_response_record,
2277 x_return_status => x_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data
2280 );
2281 LOG (p_device_id, '****************************End****************************');
2282 CLOSE msg_template_record;
2283 COMMIT;
2284 EXCEPTION
2285 WHEN OTHERS
2286 THEN
2287 IF l_debug > 0 THEN
2288 LOG (p_device_id, 'Unexpected error in parse_device_response for p_request_id='
2289 || p_request_id
2290 );
2291 END IF;
2292 x_return_status := fnd_api.g_ret_sts_unexp_error;
2293 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2294 p_data => x_msg_data);
2295 END parse_device_response;
2296 --
2297 --
2298 PROCEDURE populate_response_record (
2299 p_device_id IN NUMBER,
2300 p_component_code IN NUMBER,
2301 p_msg_component IN VARCHAR2,
2302 p_response_record IN OUT NOCOPY WMS_WCS_DEVICE_GRP.MSG_COMPONENT_LOOKUP_TYPE
2303 )
2304 IS
2305 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2306 BEGIN
2307 IF l_debug > 0 THEN
2308 log (p_device_id, 'In Populate_response_record. p_component_code='
2309 || p_component_code
2310 || ', p_msg_component='
2311 || p_msg_component);
2312 END IF;
2313 IF p_component_code = 1
2314 THEN
2318 p_response_record.order_number := p_msg_component;
2315 p_response_record.ORGANIZATION := p_msg_component;
2316 ELSIF p_component_code = 2
2317 THEN
2319 ELSIF p_component_code = 3
2320 THEN
2321 p_response_record.item := p_msg_component;
2322 ELSIF p_component_code = 4
2323 THEN
2324 p_response_record.business_event := p_msg_component;
2325 ELSIF p_component_code = 5
2326 THEN
2327 p_response_record.action := p_msg_component;
2328 ELSIF p_component_code = 6
2329 THEN
2330 p_response_record.device_id := p_msg_component;
2331 ELSIF p_component_code = 7
2332 THEN
2333 p_response_record.host_id := p_msg_component;
2334 ELSIF p_component_code = 8
2335 THEN
2336 p_response_record.subinventory := p_msg_component;
2337 ELSIF p_component_code = 9
2338 THEN
2339 p_response_record.LOCATOR := p_msg_component;
2340 ELSIF p_component_code = 10
2341 THEN
2342 p_response_record.lpn := p_msg_component;
2343 ELSIF p_component_code = 11
2344 THEN
2345 p_response_record.lot := p_msg_component;
2346 ELSIF p_component_code = 12
2347 THEN
2348 p_response_record.uom := p_msg_component;
2349 ELSIF p_component_code = 13
2350 THEN
2351 p_response_record.cycle_count_id := p_msg_component;
2352 ELSIF p_component_code = 14
2353 THEN
2354 p_response_record.quantity := p_msg_component;
2355 ELSIF p_component_code = 15
2356 THEN
2357 p_response_record.requested_quantity := p_msg_component;
2358 ELSIF p_component_code = 16
2359 THEN
2360 p_response_record.weight := p_msg_component;
2361 ELSIF p_component_code = 17
2362 THEN
2363 p_response_record.weight_uom_code := p_msg_component;
2364 ELSIF p_component_code = 18
2365 THEN
2366 p_response_record.volume := p_msg_component;
2367 ELSIF p_component_code = 19
2368 THEN
2369 p_response_record.volume_uom_code := p_msg_component;
2370 ELSIF p_component_code = 20
2371 THEN
2372 p_response_record.LENGTH := p_msg_component;
2373 ELSIF p_component_code = 21
2374 THEN
2375 p_response_record.width := p_msg_component;
2376 ELSIF p_component_code = 22
2377 THEN
2378 p_response_record.height := p_msg_component;
2379 ELSIF p_component_code = 23
2380 THEN
2381 p_response_record.dimensional_weight := p_msg_component;
2382 ELSIF p_component_code = 24
2383 THEN
2384 p_response_record.dimensional_weight_factor := p_msg_component;
2385 ELSIF p_component_code = 25
2386 THEN
2387 p_response_record.net_weight := p_msg_component;
2388 ELSIF p_component_code = 26
2389 THEN
2390 p_response_record.received_request_date_and_time := p_msg_component;
2391 ELSIF p_component_code = 27
2392 THEN
2393 p_response_record.measurement_date_and_time := p_msg_component;
2394 ELSIF p_component_code = 28
2395 THEN
2396 p_response_record.response_date_and_time := p_msg_component;
2397 ELSIF p_component_code = 29
2398 THEN
2399 p_response_record.temperature := p_msg_component;
2400 ELSIF p_component_code = 30
2401 THEN
2402 p_response_record.temperature_uom := p_msg_component;
2403 ELSIF p_component_code = 31
2404 THEN
2405 p_response_record.reason_id := p_msg_component;
2406 ELSIF p_component_code = 32
2407 THEN
2408 p_response_record.reason_type := p_msg_component;
2409 ELSIF p_component_code = 33
2410 THEN
2411 p_response_record.sensor_measurement_type := p_msg_component;
2412 ELSIF p_component_code = 34
2413 THEN
2414 p_response_record.VALUE := p_msg_component;
2415 ELSIF p_component_code = 35
2416 THEN
2417 p_response_record.quality := p_msg_component;
2418 ELSIF p_component_code = 36
2419 THEN
2420 p_response_record.opc_variant_code := p_msg_component;
2421 ELSIF p_component_code = 37
2422 THEN
2423 p_response_record.epc := p_msg_component;
2424 ELSIF p_component_code = 38
2425 THEN
2426 p_response_record.UNUSED := p_msg_component;
2427 ELSIF p_component_code = 39
2428 THEN
2429 p_response_record.batch := p_msg_component;
2430 ELSIF p_component_code = 40
2431 THEN
2432 p_response_record.device_component_1 := p_msg_component;
2433 ELSIF p_component_code = 41
2434 THEN
2435 p_response_record.device_component_2 := p_msg_component;
2436 ELSIF p_component_code = 42
2437 THEN
2438 p_response_record.device_component_3 := p_msg_component;
2439 ELSIF p_component_code = 43
2440 THEN
2441 p_response_record.device_component_4 := p_msg_component;
2442 ELSIF p_component_code = 44
2443 THEN
2444 p_response_record.device_component_5 := p_msg_component;
2445 ELSIF p_component_code = 45
2446 THEN
2447 p_response_record.device_component_6 := p_msg_component;
2448 ELSIF p_component_code = 46
2449 THEN
2450 p_response_record.device_component_7 := p_msg_component;
2451 ELSIF p_component_code = 47
2452 THEN
2453 p_response_record.device_component_8 := p_msg_component;
2454 ELSIF p_component_code = 48
2455 THEN
2456 p_response_record.device_component_9 := p_msg_component;
2457 ELSIF p_component_code = 49
2458 THEN
2459 p_response_record.device_component_10 := p_msg_component;
2460 ELSIF p_component_code = 50
2461 THEN
2462 p_response_record.relation_id := p_msg_component;
2463 ELSIF p_component_code = 51
2464 THEN
2465 p_response_record.task_id := p_msg_component;
2466 ELSIF p_component_code = 52
2467 THEN
2468 p_response_record.task_summary := p_msg_component;
2472 ELSIF p_component_code = 54
2469 ELSIF p_component_code = 53
2470 THEN
2471 p_response_record.organization_id := p_msg_component;
2473 THEN
2474 p_response_record.inventory_item_id := p_msg_component;
2475 ELSIF p_component_code = 55
2476 THEN
2477 p_response_record.device_status := p_msg_component;
2478 ELSIF p_component_code = 56
2479 THEN
2480 p_response_record.transfer_lpn_id := p_msg_component;
2481 ELSIF p_component_code = 57
2482 THEN
2483 p_response_record.destination_subinventory := p_msg_component;
2484 ELSIF p_component_code = 58
2485 THEN
2486 p_response_record.destination_locator_id := p_msg_component;
2487 ELSIF p_component_code = 59
2488 THEN
2489 p_response_record.source_locator_id := p_msg_component;
2490 ELSIF (l_debug > 0)
2491 THEN
2492 log (p_device_id, 'In populate_response_record. Invalid p_component_code:'
2493 ||p_component_code);
2494 END IF;
2495 EXCEPTION
2496 WHEN OTHERS
2497 THEN
2498 IF (l_debug > 0)
2499 THEN
2500 LOG (p_device_id, 'Exception in populate_response_record:' || SQLERRM);
2501 END IF;
2502 END populate_response_record;
2503 --
2504 --
2505 PROCEDURE log_response_record (
2506 p_device_id IN NUMBER,
2507 p_response_record IN WMS_WCS_DEVICE_GRP.MSG_COMPONENT_LOOKUP_TYPE
2508 )
2509 IS
2510 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2511 BEGIN
2512 IF (l_debug > 0) THEN
2513 log
2514 (p_device_id, 'In log_response_record. Contents of the response record are--->');
2515 log (p_device_id, 'organization=' || p_response_record.organization);
2516 log (p_device_id, ', order_number=' || p_response_record.order_number);
2517 log (p_device_id, ', item=' || p_response_record.item);
2518 log (p_device_id, ', business_event=' || p_response_record.business_event);
2519 log (p_device_id, ', action=' || p_response_record.action);
2520 log (p_device_id, ', device_id=' || p_response_record.device_id);
2521 log (p_device_id, ', host_id=' || p_response_record.host_id);
2522 log (p_device_id, ', subinventory=' || p_response_record.subinventory);
2523 log (p_device_id, ', LOCATOR=' || p_response_record.LOCATOR);
2524 log (p_device_id, ', lpn=' || p_response_record.lpn);
2525 log (p_device_id, ', lot=' || p_response_record.lot);
2526 log (p_device_id, ', uom=' || p_response_record.uom);
2527 log (p_device_id, ', cycle_count_id=' || p_response_record.cycle_count_id);
2528 log (p_device_id, ', quantity=' || p_response_record.quantity);
2529 log (p_device_id, ', requested_quantity=' || p_response_record.requested_quantity);
2530 log (p_device_id, ', weight=' || p_response_record.weight);
2531 log (p_device_id, ', weight_uom_code=' || p_response_record.weight_uom_code);
2532 log (p_device_id, ', volume=' || p_response_record.volume);
2533 log (p_device_id, ', volume_uom_code=' || p_response_record.volume_uom_code);
2534 log (p_device_id, ', LENGTH=' || p_response_record.LENGTH);
2535 log (p_device_id, ', width=' || p_response_record.width);
2536 log (p_device_id, ', height=' || p_response_record.height);
2537 log (p_device_id, ', dimensional_weight=' || p_response_record.dimensional_weight);
2538 log (p_device_id, ', dimensional_weight_factor=' || p_response_record.dimensional_weight_factor);
2539 log (p_device_id, ', net_weight=' || p_response_record.net_weight);
2540 log (p_device_id, ', received_request_date_and_time=' || p_response_record.received_request_date_and_time);
2541 log (p_device_id, ', measurement_date_and_time=' || p_response_record.measurement_date_and_time);
2542 log (p_device_id, ', response_date_and_time=' || p_response_record.response_date_and_time);
2543 log (p_device_id, ', temperature=' || p_response_record.temperature);
2544 log (p_device_id, ', temperature_uom=' || p_response_record.temperature_uom);
2545 log (p_device_id, ', reason_id=' || p_response_record.reason_id);
2546 log (p_device_id, ', reason_type=' || p_response_record.reason_type);
2547 log (p_device_id, ', sensor_measurement_type=' || p_response_record.sensor_measurement_type);
2548 log (p_device_id, ', VALUE=' || p_response_record.VALUE);
2549 log (p_device_id, ', quality=' || p_response_record.quality);
2550 log (p_device_id, ', opc_variant_code=' || p_response_record.opc_variant_code);
2551 log (p_device_id, ', epc=' || p_response_record.epc);
2552 log (p_device_id, ', UNUSED=' || p_response_record.UNUSED);
2553 log (p_device_id, ', batch=' || p_response_record.batch);
2554 log (p_device_id, ', device_component_1=' || p_response_record.device_component_1);
2555 log (p_device_id, ', device_component_2=' || p_response_record.device_component_2);
2556 log (p_device_id, ', device_component_3=' || p_response_record.device_component_3);
2557 log (p_device_id, ', device_component_4=' || p_response_record.device_component_4);
2558 log (p_device_id, ', device_component_5=' || p_response_record.device_component_5);
2559 log (p_device_id, ', device_component_6=' || p_response_record.device_component_6);
2560 log (p_device_id, ', device_component_7=' || p_response_record.device_component_7);
2561 log (p_device_id, ', device_component_8=' || p_response_record.device_component_8);
2562 log (p_device_id, ', device_component_9=' || p_response_record.device_component_9);
2563 log (p_device_id, ', device_component_10=' || p_response_record.device_component_10);
2564 log (p_device_id, ', relation_id=' || p_response_record.relation_id);
2565 log (p_device_id, ', task_id=' || p_response_record.task_id);
2566 log (p_device_id, ', task_summary=' || p_response_record.task_summary);
2567 log (p_device_id, ', organization_id=' || p_response_record.organization_id);
2568 log (p_device_id, ', inventory_item_id=' || p_response_record.inventory_item_id);
2569 log (p_device_id, ', device_status=' || p_response_record.device_status);
2570 log (p_device_id, ', transfer_lpn_id=' || p_response_record.transfer_lpn_id);
2571 log (p_device_id, ', destination_subinventory=' || p_response_record.destination_subinventory);
2572 log (p_device_id, ', destination_locator_id=' || p_response_record.destination_locator_id);
2573 log (p_device_id, ', source_locator_id=' || p_response_record.source_locator_id);
2574 END IF;
2575 EXCEPTION
2576 WHEN OTHERS
2577 THEN
2578 IF (l_debug > 0)
2579 THEN
2580 LOG (p_device_id, 'Exception in log_response_record:' || SQLERRM);
2581 END IF;
2582 END log_response_record;
2583
2584 --
2585 -- Private Procedure to slow down the application if the hardware is not able to keep pace
2586 -- The duration of the wait will be controlled by a configurable paramter 'DIRECTIVE_PAUSE_DELAY'
2587 --
2588 PROCEDURE pause_directive(
2589 p_device_id IN NUMBER,
2590 p_delay_in_seconds IN NUMBER
2591 )
2592 IS
2593 l_count NUMBER := 0;
2594 l_time DATE;
2595 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2596 BEGIN
2597 l_time := SYSDATE + (p_delay_in_seconds/(86400));
2598 WHILE l_time > SYSDATE LOOP
2599 l_count := l_count + 1;
2600 END LOOP ;
2601 IF (l_debug > 0) THEN
2602 LOG(p_device_id, 'In pause_directive. Done waiting for '
2603 || p_delay_in_seconds
2604 || 'seconds. l_count='
2605 || l_count);
2606 END IF;
2607 EXCEPTION
2608 WHEN OTHERS THEN
2609 IF (l_debug > 0) THEN
2610 LOG(p_device_id, 'Exception in pause_directive:'||SQLERRM);
2611 END IF;
2612 END pause_directive;
2613
2614 END wms_carousel_integration_pvt;