[Home] [Help]
PACKAGE BODY: APPS.FND_CP_GSM_IPC
Source
1 PACKAGE BODY fnd_cp_gsm_ipc AS
2 /* $Header: AFCPSMIB.pls 120.5.12020000.2 2012/12/10 22:37:28 ckclark ship $ */
3
4
5
6 --=========================================================================--
7 /* Private Functions */
8 --=========================================================================--
9 P_Applsys_Schema varchar2(30) := NULL;
10
11 Function Q_Schema return Varchar2 is
12
13 pragma AUTONOMOUS_TRANSACTION;
14
15 begin
16
17 if (P_Applsys_Schema is null) then
18 Select TEXT
19 into P_Applsys_Schema
20 from WF_RESOURCES
21 where TYPE = 'WFTKN'
22 and NAME = 'WF_SCHEMA'
23 and rownum = 1;
24 end if;
25
26 commit;
27 return(P_Applsys_Schema);
28
29 exception
30 when OTHERS then
31 rollback;
32 end;
33
34 --=========================================================================--
35 /* ICM Functions */
36 --=========================================================================--
37
38 /*--------------------------------------------------------------------------
39 procedure Unsubscribe -unsub from AQ. Null -> unsub all
40 -----------------------------------------------------------------------------*/
41
42 procedure Unsubscribe(cpid in number default null) is
43
44 sql_stmt varchar2(2000);
45
46 pragma AUTONOMOUS_TRANSACTION;
47
48 Begin
49 if cpid is not null then
50 DBMS_AQADM.REMOVE_SUBSCRIBER(queue_name =>Q_Schema||'.FND_CP_GSM_IPC_AQ',
51 subscriber => sys.aq$_agent('FNDCPGSMIPC_Cartridge_'||to_char(cpid),
52 NULL, NULL));
53
54 DBMS_AQADM.REMOVE_SUBSCRIBER(queue_name =>Q_Schema||'.FND_CP_GSM_IPC_AQ',
55 subscriber => sys.aq$_agent('FNDCPGSMIPC_Service_'||to_char(cpid),
56 NULL, NULL));
57 else
58 sql_stmt := 'declare ';
59
60 sql_stmt := sql_stmt || 'CURSOR C1 is select NAME name ';
61 sql_stmt := sql_stmt || ' from '||Q_Schema||'.aq$FND_CP_GSM_IPC_AQTBL_S ';
62 sql_stmt := sql_stmt || ' where QUEUE=''FND_CP_GSM_IPC_AQ'';';
63
64 sql_stmt := sql_stmt || ' begin ';
65 sql_stmt := sql_stmt || ' for c1rec in c1 loop ';
66
67 sql_stmt := sql_stmt || 'DBMS_AQADM.REMOVE_SUBSCRIBER(queue_name => ''';
68 sql_stmt := sql_stmt || Q_Schema || '.FND_CP_GSM_IPC_AQ'', ';
69 sql_stmt := sql_stmt || 'subscriber=>sys.aq$_agent(c1rec.name,NULL,NULL));';
70
71 sql_stmt := sql_stmt || ' end loop; ';
72 sql_stmt := sql_stmt || ' end;';
73
74 EXECUTE IMMEDIATE sql_stmt ;
75 end if;
76
77 commit;
78
79 Exception
80 when others then null;
81 rollback;
82 end;
83
84
85 --=========================================================================-
86 /* Cartridge Functions */
87 --=========================================================================--
88
89 /*--------------------------------------------------------------------------
90 procedure Init_Cartridge
91 -----------------------------------------------------------------------------*/
92
93 procedure Init_Cartridge is
94
95 Not_Done Boolean;
96 PAYLOAD system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
97 dq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
98 msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
99 msgid raw(16);
100
101 pragma AUTONOMOUS_TRANSACTION;
102
103 Begin
104 /* we should clear expired messages out of exception queue */
105 dq_opts.consumer_name := NULL;
106 dq_opts.DEQUEUE_MODE := DBMS_AQ.REMOVE;
107 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
108 dq_opts.VISIBILITY := DBMS_AQ.ON_COMMIT;
109 dq_opts.WAIT := DBMS_AQ.NO_WAIT;
110 dq_opts.MSGID := NULL;
111
112 Not_Done := TRUE;
113
114 while Not_Done LOOP
115 begin
116 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.AQ$_FND_CP_GSM_IPC_AQTBL_E',
117 DEQUEUE_OPTIONS => dq_opts,
118 MESSAGE_PROPERTIES => msg_props,
119 PAYLOAD => payload,
120 MSGID => msgid);
121 exception when others then Not_Done := FALSE;
122 end;
123 end LOOP;
124
125 commit;
126
127 exception
128 when OTHERS then
129 rollback;
130 raise;
131 End;
132
133 /*--------------------------------------------------------------------------
134 procedure Shutdown_Cartridge
135 -----------------------------------------------------------------------------*/
136
137 procedure Shutdown_Cartridge is
138
139 pragma AUTONOMOUS_TRANSACTION;
140
141 Begin
142 /* Doesn't appear that anything is necessary...but we'll keep the hook */
143 null;
144 commit;
145
146 exception
147 when OTHERS then
148 rollback;
149 raise;
150 End;
151
152 /*--------------------------------------------------------------------------
153 procedure Cartridge_Init_Service
154 -----------------------------------------------------------------------------*/
155
156 procedure Cartridge_Init_Service(cpid in Number,
157 Params in Varchar2,
158 Debug_Level in Varchar2) is
159
160 pragma AUTONOMOUS_TRANSACTION;
161
162 begin
163 DBMS_AQADM.ADD_SUBSCRIBER(queue_name =>Q_Schema||'.FND_CP_GSM_IPC_AQ',
164 subscriber => sys.aq$_agent('FNDCPGSMIPC_Cartridge_'||to_char(cpid),
165 NULL, NULL));
166
167 DBMS_AQADM.ADD_SUBSCRIBER(queue_name =>Q_Schema||'.FND_CP_GSM_IPC_AQ',
168 subscriber => sys.aq$_agent('FNDCPGSMIPC_Service_'||to_char(cpid),
169 NULL, NULL));
170
171 Send_Message(cpid,'Initialize',Params,Debug_Level);
172 Update_Status (cpid, 'Uninitialized');
173 commit;
174
175 exception
176 when OTHERS then
177 rollback;
178 raise;
179 end;
180
181
182
183 /*--------------------------------------------------------------------------
184 Procedure Send_Message:
185
186 Handle = CPID
187 Message -> Stop, Suspend, Resume, Verify, Initialize
188 Payload - Currently only used to send parameters with Verify
189 Debug_Level = One character Debug Level
190 -----------------------------------------------------------------------------*/
191
192 Procedure Send_Message (Handle in Number,
193 Message in Varchar2,
194 Parameters in Varchar2,
195 Debug_Level in Varchar2) is
196
197
198 enq_opts DBMS_AQ.enqueue_options_t;
199 msg_props DBMS_AQ.message_properties_t;
200 msg_id raw(16);
201 msg system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
202 sessionid number;
203
204 pragma AUTONOMOUS_TRANSACTION;
205
206 begin
207 msg := system.FND_CP_GSM_IPQ_AQ_PAYLOAD(
208 Handle,Message,Parameters,Debug_Level);
209
210 enq_opts.visibility := DBMS_AQ.ON_COMMIT;
211 enq_opts.sequence_deviation := NULL;
212 msg_props.delay := DBMS_AQ.NO_DELAY;
213 msg_props.expiration := 365 * 24 * 3600; -- One Year
214 msg_props.recipient_list(1) := sys.aq$_agent(
215 'FNDCPGSMIPC_Service_'||to_char(Handle),NULL, NULL);
216 msg_props.sender_id := sys.aq$_agent(
217 'FNDCPGSMIPC_Cartridge_'||to_char(Handle), NULL, NULL);
218
219 DBMS_AQ.Enqueue( queue_name => Q_Schema||'.FND_CP_GSM_IPC_AQ',
220 enqueue_options => enq_opts,
221 message_properties => msg_props,
222 Payload => msg,
223 msgid => msg_id);
224
225 commit;
226
227 exception
228 when OTHERS then
229 rollback;
230 raise;
231 end;
232
233
234 /*--------------------------------------------------------------------------
235 Function Get_Status:
236
237 Handle = CPID
238 -----------------------------------------------------------------------------*/
239
240 Function Get_Status (Handle in Number) Return Varchar2 is
241
242 Status_code Varchar2(1);
243
244 Begin
245 Select GSM_INTERNAL_STATUS
246 into Status_code
247 from fnd_concurrent_processes
248 where concurrent_process_id = handle;
249
250 return (Status_code);
251 End;
252
253
254 /* No longer used */
255 Function Obsolete_Get_Status (Handle in Number) Return Varchar2 is
256
257 Not_Done Boolean;
258 Status_Name Varchar2(30);
259 Status_code Varchar2(1);
260 More_Flag Varchar2(1);
261 PAYLOAD system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
262 dq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
263 msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
264 msgid raw(16);
265 last_msg raw(16);
266
267 pragma AUTONOMOUS_TRANSACTION;
268
269 Begin
270 Not_Done := TRUE;
271
272 while (Not_Done) LOOP -- Clear off excess messages
273
274 dq_opts.consumer_name := 'FNDCPGSMIPC_Cartridge_'||to_char(Handle);
275 dq_opts.DEQUEUE_MODE := DBMS_AQ.BROWSE;
276 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
277 dq_opts.VISIBILITY := DBMS_AQ.ON_COMMIT;
278 dq_opts.WAIT := DBMS_AQ.NO_WAIT;
279 dq_opts.MSGID := NULL;
280
281 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
282 DEQUEUE_OPTIONS => dq_opts,
283 MESSAGE_PROPERTIES => msg_props,
284 PAYLOAD => payload,
285 MSGID => msgid);
286
287 last_msg := msgid;
288 Status_Name := PAYLOAD.Message;
289
290 dq_opts.NAVIGATION := DBMS_AQ.NEXT_MESSAGE;
291
292 begin
293 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
294 DEQUEUE_OPTIONS => dq_opts,
295 MESSAGE_PROPERTIES => msg_props,
296 PAYLOAD => payload,
297 MSGID => msgid);
298 exception
299 when others then payload.Message:= null;
300 end;
301
302
303 if (payload.Message is null) then -- No later message
304 Not_Done := FALSE;
305 else -- Kill the first message
306 dq_opts.CORRELATION := Null;
307 dq_opts.MSGID := last_msg;
308 dq_opts.DEQUEUE_MODE := DBMS_AQ.REMOVE_NODATA;
309
310 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
311 DEQUEUE_OPTIONS => dq_opts,
312 MESSAGE_PROPERTIES => msg_props,
313 PAYLOAD => payload,
314 MSGID => msgid);
315 end if;
316 END LOOP;
317
318 if (UPPER(Status_Name) = 'UNINITIALIZED') then
319 Status_Code := 'Z';
320 elsif (UPPER(Status_Name) = 'SUSPENDED') then
321 Status_Code := 'P';
322 elsif (UPPER(Status_Name) = 'RUNNING') then
323 Status_Code := 'A';
324 elsif (UPPER(Status_Name) = 'STOPPED') then
325 Status_Code := 'S';
326 else -- No Perfect answer, best to assume its Running
327 Status_Code := 'A';
328 end if;
329
330 commit;
331 return (Status_code);
332
333 exception
334 when OTHERS then
335 rollback;
336 End;
337
338 --=========================================================================--
339 /* Routines called Externally */
340 --=========================================================================--
341
342
343 /*--------------------------------------------------------------------------
344 Procedure Send_Custom_Message:
345
346 Handle = CPID
347 Type - 8 characters for identifying format
348 Message - Currently only used to send parameters with Verify
349 -----------------------------------------------------------------------------*/
350
351
352 Procedure Send_Custom_Message (Handle in Number,
353 Type in varchar2,
354 Mesg in Varchar2) is
355 pragma AUTONOMOUS_TRANSACTION;
356
357 begin
358 Send_Message (Handle, 'Custom:' || substr(Type,1,8), Mesg, Null);
359 commit;
360
361 exception
362 when OTHERS then
363 rollback;
364 raise;
365 end;
366
367 --=========================================================================--
368 /* Routines called by Service */
369 --=========================================================================--
370
371 /*--------------------------------------------------------------------------
372 Procedure Init_Service:
373
374 Init_Service:
375 Handle = CPID
376 Parameters = Initial Parameter String
377 Debug_Level = One character Debug Level
378 -----------------------------------------------------------------------------*/
379
380 Procedure Init_Service (Handle in Number,
381 Parameters out NOCOPY Varchar2,
382 Debug_Level out NOCOPY Varchar2) is
383
384
385 mesg Varchar2(2048);
386 success_f Varchar2(1);
387 more_f Varchar2(1);
388
389 s_id number;
390 p_id number;
391 osp_id v$process.spid%TYPE := Null;
392 sqlnet_str varchar2(30) := Null;
393 service_name varchar2(30) := Null;
394 que_rcg varchar2(32) := Null;
395 old_rcg varchar2(32);
396 etrace varchar2(4) := Null;
397 diag_level char := Null;
398 sql_stmt VARCHAR2(200) := null;
399 PAYLOAD system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
400 dq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
401 msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
402 msgid raw(16);
403
404 pragma AUTONOMOUS_TRANSACTION;
405
406 begin
407 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
408 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
409 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
410 'Enter procedure - Handle = '||to_char(Handle));
411 end if;
412 Update_Status ( Handle, 'Running');
413
414 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
415 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
416 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
417 'Handle ' ||to_char(Handle)||': status updated to running');
418 end if;
419 dq_opts.consumer_name := 'FNDCPGSMIPC_Service_'|| to_char(Handle);
420 dq_opts.DEQUEUE_MODE := DBMS_AQ.REMOVE;
421 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
422 dq_opts.VISIBILITY := DBMS_AQ.IMMEDIATE;
423 dq_opts.WAIT := DBMS_AQ.FOREVER;
424 dq_opts.MSGID := NULL;
425
426 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
427 DEQUEUE_OPTIONS => dq_opts,
428 MESSAGE_PROPERTIES => msg_props,
429 PAYLOAD => payload,
430 MSGID => msgid);
431
432 Parameters := payload.Payload;
433 mesg := payload.Message;
434 Debug_Level := payload.Debug_Level;
435
436 SELECT PID, SPID, S.AUDSID
437 INTO p_id, osp_id, s_id
438 FROM V$PROCESS P, V$SESSION S
439 WHERE S.AUDSID = USERENV('SESSIONID')
440 AND P.Addr = S.Paddr
441 and rownum <= 1; /* Probably not necessary */
442
443 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
444 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
445 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
446 'Handle ' ||to_char(Handle)||': pid='||to_char(p_id)||', spid='||osp_id||', audsid='||to_char(s_id));
447 end if;
448
449 /* 5867853- Make sure the fcp row for services, like that of FNDSM
450 * and managers, is storing TWO_TASK into SQLNET_STRING. The value
451 * selected here is only important if PCP DB Instance failover is on,
455 */
452 * in which case TWO_TASK is required to match the instance name.
453 * Thus, the FNDSM TWO_TASK in SQLNET_STRING will be < 17 characters
454 * and could fit in db_instance column.
456
457 select decode(sign(length(sqlnet_string) - 17), -1, sqlnet_string, null)
458 INTO sqlnet_str
459 FROM FND_CONCURRENT_PROCESSES
460 WHERE MANAGER_TYPE = 6
461 AND UPPER(NODE_NAME) = (select upper(node_name)
462 from fnd_concurrent_processes
463 where concurrent_process_id = Handle)
464 AND PROCESS_STATUS_CODE = 'A';
465
466 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
467 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
468 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
469 'Handle ' ||to_char(Handle)||': sqlnet_string='|| sqlnet_str);
470 end if;
471
472 /* 5867853- Update DB_INSTANCE and SQLNET_STRING for services */
473 UPDATE FND_CONCURRENT_PROCESSES
474 set SESSION_ID = s_id,
475 ORACLE_PROCESS_ID = p_id,
476 OS_PROCESS_ID = osp_id,
477 INSTANCE_NUMBER = (Select instance_number from v$instance),
478 DB_INSTANCE = (Select instance_name from v$instance),
479 SQLNET_STRING = sqlnet_str,
480 last_update_date = sysdate,
481 last_updated_by = 4
482 where CONCURRENT_PROCESS_ID = Handle;
483
484 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
485 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
486 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
487 'Handle ' ||to_char(Handle)||': fnd_concurrent_processes updated');
488 end if;
489
490 select Concurrent_Queue_Name, Resource_Consumer_Group, Diagnostic_level
491 into service_name, que_rcg, diag_level
492 from Fnd_Concurrent_Queues Q, Fnd_Concurrent_processes P
493 WHERE Q.Application_ID = P.Queue_Application_ID
494 And Q.Concurrent_queue_ID = P.Concurrent_Queue_ID
495 And P.Concurrent_Process_Id = Handle;
496
497 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
498 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
499 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
500 'Handle ' ||to_char(Handle)||': Concurrent_Queue_Name='||service_name||', Resource_Consumer_Group='||que_rcg||', Diagnostic_level='||diag_level);
501 end if;
502
503 -- Until we add explicit support for TRACE ON/OFF
504
505 if (diag_level in ('Y', '1', '2', '3')) then
506 etrace := 'TRUE';
507 end if;
508
509 -- FND_CTL.FND_SESS_CTL(Null, Null, etrace, Null, Null, Null);
510 if etrace is not null then
511 sql_stmt := 'ALTER SESSION SET SQL_TRACE = '|| etrace;
512 EXECUTE IMMEDIATE sql_stmt ;
513 end if;
514
515 dbms_application_info.set_module(service_name,
516 'Service Management');
517
518 if que_rcg is null then
519 que_rcg := 'DEFAULT_CONSUMER_GROUP';
520 end if;
521
522 begin
523 dbms_session.switch_current_consumer_group(que_rcg, old_rcg, false);
524 exception
525 when others then null;
526 end;
527
528 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
529 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
530 'fnd.plsql.FND_CP_GSM_IPC.Init_Service',
531 'Handle ' ||to_char(Handle)||': Work completed successfully, commiting');
532 end if;
533
534 commit;
535
536 exception
537 when OTHERS then
538 rollback;
539 raise;
540 end;
541
542 /*--------------------------------------------------------------------------
543 Procedure Get_Message:
544
545 Handle = CPID
546 Message -> Stop, Suspend, Resume, Verify, Initialize (Internal)
547 Parameters - Currently only used to send parameters with Verify
548 Debug_Level - One character Debug Level
549 Blocking_Flag = Y/N do we wait?
550 Consume_Flag = Y/N do we consume message?
551 More_Flag = Y/N more messages on AQ?
552 Message_Wait_Timeout = Timeout to use when waiting on AQ for msg.
553 Used for both blocking and non blocking calls. Null= nowait
554 Blocking_Sleep_Time = Only meaningful if blocking_flag = 'Y'. How
555 many secs to sleep between looking for messages.
556 -----------------------------------------------------------------------------*/
557
558 Procedure Get_Message ( Handle in Number,
559 Message out NOCOPY Varchar2,
560 Parameters out NOCOPY Varchar2,
561 Debug_Level out NOCOPY Varchar2,
562 Blocking_Flag in Varchar2,
563 Consume_Flag in Varchar2,
564 Success_Flag out NOCOPY Varchar2,
565 More_Flag out NOCOPY Varchar2,
566 Message_Wait_Timeout in number default Null,
567 Blocking_Sleep_Time in number default 30) is
568
569 Not_Done boolean;
570 payload system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
571 dq_opts DBMS_AQ.DEQUEUE_OPTIONS_T;
572 msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
573 msgid raw(16);
574
575
576 pragma AUTONOMOUS_TRANSACTION;
577
578 Begin
579 payload := system.FND_CP_GSM_IPQ_AQ_PAYLOAD(NULL,NULL,NULL,NULL);
580
581 Not_Done := TRUE;
582
583 if (Consume_Flag = 'Y') then
584 dq_opts.DEQUEUE_MODE := DBMS_AQ.REMOVE;
585 else
586 dq_opts.DEQUEUE_MODE := DBMS_AQ.BROWSE;
587 end if;
588
589 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
590 dq_opts.VISIBILITY := DBMS_AQ.IMMEDIATE;
591
592 if (Message_Wait_Timeout is null) then
596 end if;
593 dq_opts.WAIT := DBMS_AQ.NO_WAIT;
594 else
595 dq_opts.WAIT := Message_Wait_Timeout;
597
598 dq_opts.MSGID := NULL;
599 dq_opts.consumer_name := 'FNDCPGSMIPC_Service_'||to_char(Handle);
600
601 while (Not_Done) LOOP
602 begin
603 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
604 DEQUEUE_OPTIONS => dq_opts,
605 MESSAGE_PROPERTIES => msg_props,
606 PAYLOAD => payload,
607 MSGID => msgid);
608 exception
609 when others then payload.Message:= null;
610 end;
611
612 Parameters := payload.Payload;
613 Message := payload.Message;
614 Debug_Level := payload.Debug_Level;
615
616 if (Message is null) then
617 Success_Flag := 'N';
618 else
619 Success_Flag := 'Y';
620 end if;
621
622 if (Blocking_Flag = 'N') or (Success_Flag = 'Y') then
623 Not_Done := FALSE;
624 else
625 dbms_lock.sleep(Blocking_Sleep_Time);
626 end if;
627
628 END LOOP;
629
630 /* set more flag */
631
632 dq_opts.DEQUEUE_MODE := DBMS_AQ.BROWSE;
633 dq_opts.NAVIGATION := DBMS_AQ.FIRST_MESSAGE;
634 dq_opts.VISIBILITY := DBMS_AQ.IMMEDIATE;
635 dq_opts.WAIT := DBMS_AQ.NO_WAIT;
636
637 begin
638 DBMS_AQ.DEQUEUE(QUEUE_NAME=>Q_Schema||'.FND_CP_GSM_IPC_AQ',
639 DEQUEUE_OPTIONS => dq_opts,
640 MESSAGE_PROPERTIES => msg_props,
641 PAYLOAD => payload,
642 MSGID => msgid);
643 exception
644 when others then payload.Message:= null;
645 end;
646
647 if payload.Message is null then
648 more_flag := 'N';
649 else
650 more_flag := 'Y';
651 end if;
652
653 commit;
654
655 exception
656 when OTHERS then
657 rollback;
658 raise;
659 End;
660
661 /* Messages */
662
663 Function MSG_Stop return varchar2 is begin return('Stop'); end;
664 Function MSG_Suspend return varchar2 is begin return('Suspend'); end;
665 Function MSG_Resume return varchar2 is begin return('Resume'); end;
666 Function MSG_Verify return varchar2 is begin return('Verify'); end;
667 Function MSG_Custom return varchar2 is begin return('Custom'); end;
668
669 /*--------------------------------------------------------------------------
670 Procedure Update_Status:
671
672 Handle = CPID
673 Status is one of: Running, Stopped, Suspended,
674 Uninitialized (for FND Use only)
675 -----------------------------------------------------------------------------*/
676
677 Procedure Update_Status ( Handle in Number,
678 Status in Varchar2) is
679
680 Status_Code varchar2(1);
681
682 pragma AUTONOMOUS_TRANSACTION;
683
684 Begin
685 if (UPPER(Status) = 'UNINITIALIZED') then
686 Status_Code := 'Z';
687 elsif (UPPER(Status) = 'SUSPENDED') then
688 Status_Code := 'P';
689 elsif (UPPER(Status) = 'RUNNING') then
690 Status_Code := 'A';
691 elsif (UPPER(Status) = 'STOPPED') then
692 Status_Code := 'S';
693 else -- No Perfect answer, best to assume its Running
694 Status_Code := 'A';
695 end if;
696
697 Update FND_CONCURRENT_PROCESSES
698 Set GSM_INTERNAL_STATUS = Status_Code
699 where CONCURRENT_PROCESS_ID = Handle;
700
701 commit;
702
703 exception
704 when OTHERS then
705 rollback;
706 raise;
707 end;
708
709 /*--------------------------------------------------------------------------
710 Procedure Update_Status_and_Info:
711
712 Handle = CPID
713 Status is one of: Running, Stopped, Suspended,
714 Uninitialized (for FND Use only)
715 Info is for service developer use.
716 -----------------------------------------------------------------------------*/
717
718 Procedure Update_Status_and_Info ( Handle in Number,
719 Status in Varchar2,
720 Info in Varchar2) is
721
722 Status_Code varchar2(1);
723
724 pragma AUTONOMOUS_TRANSACTION;
725
726 Begin
727 if (UPPER(Status) = 'UNINITIALIZED') then
728 Status_Code := 'Z';
729 elsif (UPPER(Status) = 'SUSPENDED') then
730 Status_Code := 'P';
731 elsif (UPPER(Status) = 'RUNNING') then
732 Status_Code := 'A';
733 elsif (UPPER(Status) = 'STOPPED') then
734 Status_Code := 'S';
735 else -- No Perfect answer, best to assume its Running
736 Status_Code := 'A';
737 end if;
738
739 Update FND_CONCURRENT_PROCESSES
740 Set GSM_INTERNAL_STATUS = Status_Code,
741 GSM_INTERNAL_INFO = Info
742 where CONCURRENT_PROCESS_ID = Handle;
743
744 commit;
745
746 exception
747 when OTHERS then
748 rollback;
749 raise;
750 end;
751
752 /* No longer used */
753 Procedure obsolete_Update_Status ( Handle in Number,
754 Status in Varchar2) is
755
756 msg_props dbms_aq.message_properties_t;
757 enq_opts dbms_aq.enqueue_options_t;
758 msg system.FND_CP_GSM_IPQ_AQ_PAYLOAD;
759 msg_id raw(16);
760
761 pragma AUTONOMOUS_TRANSACTION;
762
763 Begin
764 msg := system.FND_CP_GSM_IPQ_AQ_PAYLOAD(Handle, Status, NULL, NULL);
765
766 enq_opts.visibility := DBMS_AQ.IMMEDIATE;
770 msg_props.expiration := 365 * 24 * 3600; -- One Year
767 enq_opts.sequence_deviation := NULL;
768
769 msg_props.delay := DBMS_AQ.NO_DELAY;
771 msg_props.recipient_list(1) :=sys.aq$_agent(
772 'FNDCPGSMIPC_Cartridge_'||to_char(Handle),NULL,NULL);
773 msg_props.sender_id := sys.aq$_agent(
774 'FNDCPGSMIPC_Service_'||to_char(Handle), NULL, NULL);
775
776 DBMS_AQ.Enqueue( queue_name => Q_Schema||'.FND_CP_GSM_IPC_AQ',
777 enqueue_options => enq_opts,
778 message_properties => msg_props,
779 Payload => msg,
780 msgid => msg_id);
781
782
783 Commit;
784
785 exception
786 when OTHERS then
787 rollback;
788 End;
789
790 /* Statuses */
791
792 Function Status_Running return varchar2 is begin return('Running'); end;
793 Function Status_Stopped return varchar2 is begin return('Stopped'); end;
794 Function Status_Suspended return varchar2 is begin return('Suspended'); end;
795
796 END fnd_cp_gsm_ipc;