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