[Home] [Help]
PACKAGE BODY: APPS.XDP_ADAPTER_CORE_DB
Source
1 PACKAGE BODY XDP_ADAPTER_CORE_DB AS
2 /* $Header: XDPACODB.pls 120.1 2005/06/08 23:24:55 appldev $ */
3
4 -- Private variables
5
6 pv_ChannelTruncLength number := 4;
7 pv_ChannelLength number := 30;
8
9 -- '%' breaks Controller C code
10 -- pr_AfLogStr varchar2(500) := '-DAFLOG_ENABLED=TRUE -DAFLOG_MODULE=% -DAFLOG_LEVEL=';
11 pr_AfLogStr varchar2(500) := '-DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=';
12
13 -- Private Procedures start
14
15 Function ObtainHandle(p_ChannelName in varchar2) return varchar2;
16
17 Function GetAdapterAttributes( FeID in number) return XDP_TYPES.ORDER_PARAMETER_LIST
18 is
19 FeName varchar2(80);
20 FeAttributes XDP_TYPES.ORDER_PARAMETER_LIST;
21 begin
22
23 begin
24 FeAttributes := XDP_ENGINE.Get_FE_AttributeVal_List(p_fe_id => FeID);
25 exception
26 when no_data_found then
27 null;
28 end;
29
30 return (FeAttributes);
31
32 end GetAdapterAttributes;
33
34 -- Private Procedures End
35
36 -- Public Procedures Start
37 Procedure LoadNewAdapter( p_ChannelName in varchar2,
38 p_FeID in number,
39 p_AdapterType in varchar2,
40 p_AdapterName in varchar2,
41 p_AdapterDispName in varchar2,
42 p_AdapterStatus in varchar2,
43 p_ConcQID in number,
44 p_StartupMode in varchar2 default 'MANUAL',
45 p_UsageCode in varchar2 default 'NORMAL',
46 p_LogLevel in varchar2 default 'ERROR',
47 p_CODFlag in varchar2 default 'N',
48 p_MaxIdleTime in number default 0,
49 p_LogFileName in varchar2 default NULL,
50 p_SeqInFE in number default null,
51 p_CmdLineOpts in varchar2 default NULL,
52 p_CmdLineArgs in varchar2 default NULL)
53 is
54 begin
55
56
57 insert into xdp_adapter_reg (
58 channel_name,
59 fe_id,
60 adapter_status,
61 process_id,
62 adapter_type,
63 adapter_name,
64 adapter_display_name,
65 usage_code,
66 startup_mode,
67 service_instance_id,
68 log_level,
69 connect_on_demand_flag,
70 max_idle_time_minutes,
71 cmd_line_options,
72 cmd_line_args,
73 log_file_name,
74 seq_in_fe,
75 application_id,
76 created_by,
77 creation_date,
78 last_updated_by,
79 last_update_date,
80 last_update_login)
81 values
82 ( p_ChannelName,
83 p_FeID,
84 p_AdapterStatus,
85 -1,
86 p_AdapterType,
87 p_AdapterName,
88 p_AdapterDispName,
89 p_UsageCode,
90 p_StartupMode,
91 p_ConcQID,
92 p_LogLevel,
93 p_CODFlag,
94 p_MaxIdleTime,
95 p_CmdLineOpts,
96 p_CmdLineArgs,
97 p_LogFileName,
98 p_SeqInFE,
99 XDP_ADAPTER.pv_AppID,
100 FND_GLOBAL.USER_ID,
101 sysdate,
102 FND_GLOBAL.USER_ID,
103 sysdate,
104 FND_GLOBAL.LOGIN_ID);
105
106 end LoadNewAdapter;
107
108
109 Procedure SubmitAdapterAdminReq (p_ChannelName in varchar2,
110 p_RequestType in varchar2,
111 p_RequestDate in date default sysdate,
112 p_RequestedBy in varchar2,
113 p_Freq in number default null,
114 p_RequestID OUT NOCOPY number,
115 p_JobID OUT NOCOPY number)
116 is
117
118 begin
119 select XDP_ADAPTER_ADMIN_REQS_S.NEXTVAL into p_RequestID from dual;
120
121 XDP_CRON_UTIL.SubmitAdapterAdminJob(p_request => p_RequestID,
122 p_RunDate => SubmitAdapterAdminReq.p_RequestDate,
123 p_RunFreq => SubmitAdapterAdminReq.p_Freq,
124 p_JobNumber => p_JobID);
125
126 insert into xdp_adapter_admin_reqs
127 (request_id,
128 channel_name,
129 request_type,
130 request_date,
131 requested_by_user,
132 request_frequency,
133 job_id,
134 created_by,
135 creation_date,
136 last_updated_by,
137 last_update_date,
138 last_update_login)
139 values
140 (p_RequestID,
141 SubmitAdapterAdminReq.p_ChannelName,
142 SubmitAdapterAdminReq.p_RequestType,
143 SubmitAdapterAdminReq.p_RequestDate,
144 SubmitAdapterAdminReq.p_RequestedBy,
145 SubmitAdapterAdminReq.p_Freq,
146 p_JobID,
147 FND_GLOBAL.USER_ID,
148 sysdate,
149 FND_GLOBAL.USER_ID,
150 sysdate,
151 FND_GLOBAL.LOGIN_ID);
152
153 end SubmitAdapterAdminReq;
154
155 Procedure UpdateAdapterAdminReq(p_RequestID in number,
156 p_RequestDate in date default sysdate,
157 p_RequestedBy in varchar2,
158 p_Freq in number default null)
159 is
160 l_jobID number := -1;
161 begin
162 l_jobID := Get_Job_Id_For_Request (p_RequestID);
163
164 XDP_CRON_UTIL.UpdateDBJob(p_jobID => l_jobID,
165 p_request => p_RequestID,
166 p_ReqDate => p_RequestDate,
167 p_Freq => p_Freq);
168
169 update xdp_adapter_admin_reqs set
170 request_date = p_RequestDate,
171 requested_by_user = p_RequestedBy,
172 request_frequency = p_Freq,
173 last_update_date = sysdate,
174 last_updated_by = fnd_global.user_id,
175 last_update_login = fnd_global.login_id
176 where request_id = p_RequestID;
177
178 end UpdateAdapterAdminReq;
179
180
181 Procedure RemoveAdapterAdminReq (p_RequestID in number)
182 is
183 l_jobID number := -1;
184 begin
185 l_jobID := Get_Job_Id_For_Request (p_RequestID);
186
187 dbms_job.remove(l_jobID);
188
189 delete from xdp_adapter_admin_reqs where request_id = p_RequestID;
190
191 end RemoveAdapterAdminReq;
192
193
194 Procedure FetchAdapterAdminReqInfo (p_RequestID in number,
195 p_RequestType OUT NOCOPY varchar2,
196 p_RequestDate OUT NOCOPY date,
197 p_RequestedBy OUT NOCOPY varchar2,
198 p_Freq OUT NOCOPY number,
199 p_DBJobID OUT NOCOPY number,
200 p_ChannelName OUT NOCOPY varchar2)
201 is
202
203 cursor c_GetAdapterAdmin is
204 select xar.request_type, xar.request_date, xar.requested_by_user, xar.request_frequency,
205 xar.channel_name, xar.job_id
206 from xdp_adapter_admin_reqs xar
207 where request_id = p_RequestID;
208
209 l_Found varchar2(1) := 'N';
210 begin
211
212 for v_GetAdapterAdmin in c_GetAdapterAdmin loop
213 p_RequestType := v_GetAdapterAdmin.request_type;
214 p_RequestDate := v_GetAdapterAdmin.request_date;
215 p_RequestedBy := v_GetAdapterAdmin.requested_by_user;
216 p_Freq := v_GetAdapterAdmin.request_frequency;
217 p_DBJobID := v_GetAdapterAdmin.job_id;
218 p_ChannelName := v_GetAdapterAdmin.channel_name;
219
220 l_Found := 'Y';
221 exit;
222 end loop;
223
224 if l_Found = 'N' then
225 raise no_data_found;
226 end if;
227 end FetchAdapterAdminReqInfo;
228
229 Function DoesSystemReqAlreadyExist(p_ChannelName in varchar2,
230 p_RequestType in varchar2,
231 p_RequestDate in date) return number
232 is
233 cursor c_CheckReq is
234 select request_id, job_id
235 from xdp_adapter_admin_reqs
236 where channel_name = DoesSystemReqAlreadyExist.p_ChannelName
237 and request_type = DoesSystemReqAlreadyExist.p_RequestType
238 and requested_by_user = XDP_ADAPTER.pv_adminReqBySystem
239 and request_date <= nvl(p_RequestDate,sysdate);
240
241 -- l_RequestFound boolean := false;
242 l_JobId number := 0;
243 begin
244
245 for v_CheckReq in c_CheckReq loop
246 -- l_RequestFound := true;
247 l_JobId := v_CheckReq.job_id;
248 end loop;
249
250 return (l_JobId);
251
252 end DoesSystemReqAlreadyExist;
253
254
255 Procedure CreateNewAdapterChannel(p_FeName in varchar2, p_ChannelName OUT NOCOPY varchar2)
256 is
257
258 begin
259
260 p_ChannelName := XDP_ADAPTER_CORE_PIPE.GetUniqueChannelName(p_FeName);
261 p_ChannelName := XDP_ADAPTER_CORE_PIPE.ConstructChannelName( 'CONTROL', p_ChannelName);
262
263 end CreateNewAdapterChannel;
264
265
266 Procedure FetchAdapterInfo(p_ChannelName in varchar2,
267 p_FEID OUT NOCOPY number,
268 p_ProcessID OUT NOCOPY number,
269 p_ConcQID OUT NOCOPY number)
270 is
271
272 cursor c_GetAdapter is
273 select fe_id, process_id, service_instance_id
274 from xdp_adapter_reg
275 where channel_name = p_ChannelName;
276
277 l_Found varchar2(1) := 'N';
278 begin
279
280 for v_GetAdapter in c_GetAdapter loop
281 p_ProcessID := v_GetAdapter.process_id;
282 p_ConcQID := v_GetAdapter.service_instance_id;
283 p_FeID := v_GetAdapter.fe_id;
284
285 l_Found := 'Y';
286 exit;
287 end loop;
288
289 if l_Found = 'N' then
290 raise no_data_found;
291 end if;
292
293 end FetchAdapterInfo;
294
295 -- Fetch all the Adapter Starup Information
296 -- The possbile values for Application Mode are <PIPE> <QUEUE> <NONE>
297 -- If the Mode is <PIPE> the <Application Channel Name> will be a Pipe Name
298 -- If the Mode is <QUEUE> the <Application Channel Name> will be the Outbound Queue Name
299 -- If NONE then the <Application Channel Name> will be the string NONE
300 -- If the Inbound Flag for the adapter is set to N then the <Inbound Channel Name>
301 -- is NONE. Of the Inbound Flag is set to 'Y' then the value of the Inbound Queue is returned
302 Procedure FetchAdapterStartupInfo(p_ChannelName in varchar2,
303 p_CmdOptions OUT NOCOPY varchar2,
304 p_CmdArgs OUT NOCOPY varchar2,
305 p_ControlChannelName OUT NOCOPY varchar2,
306 p_ApplChannelName OUT NOCOPY varchar2,
307 p_ApplMode OUT NOCOPY varchar2,
308 p_FeName OUT NOCOPY varchar2,
309 p_AdapterClass OUT NOCOPY varchar2,
310 p_AdapterName OUT NOCOPY varchar2,
311 p_ConcQID OUT NOCOPY number,
312 p_InboundChannelName OUT NOCOPY varchar2,
313 p_LogFileName OUT NOCOPY varchar2)
314 is
315
316 cursor c_GetFEInfo is
317 select xfe.fulfillment_element_name,
318 xat.application_mode,
319 xat.cmd_line_options base_cmd_options,
320 xat.cmd_line_args base_cmd_args,
321 xat.adapter_class,
322 xat.inbound_required_flag,
323 xag.adapter_name,
324 xag.cmd_line_options sub_cmd_options,
325 xag.cmd_line_args sub_cmd_args,
326 xag.log_level,
327 xag.log_file_name,
328 xag.service_instance_id
329 from xdp_fes xfe,
330 xdp_adapter_types_b xat,
331 xdp_adapter_reg xag
332 where xag.channel_name = p_ChannelName
333 and xag.fe_id = xfe.fe_id
334 and xag.adapter_type = xat.adapter_type;
335
336 l_BaseCmdOptions varchar2(240);
337 l_SubCmdOptions varchar2(240);
338 l_BaseCmdArgs varchar2(240);
339 l_SubCmdArgs varchar2(240);
340 l_InboundFlag varchar2(1);
341
342 l_LogLevel varchar2(240);
343 l_Found varchar2(1) := 'N';
344
345 begin
346 for v_GetFEInfo in c_GetFEInfo loop
347 p_FeName := v_GetFEInfo.fulfillment_element_name;
348 p_ApplMode := v_GetFEInfo.application_mode;
349 l_BaseCmdOptions := v_GetFEInfo.base_cmd_options;
350 l_BaseCmdArgs := v_GetFEInfo.base_cmd_args;
351 p_AdapterClass := v_GetFEInfo.adapter_class;
352 p_AdapterName := v_GetFEInfo.adapter_name;
353 l_SubCmdOptions := v_GetFEInfo.sub_cmd_options;
354 l_SubCmdArgs := v_GetFEInfo.sub_cmd_args;
355 p_ConcQID := v_GetFEInfo.service_instance_id;
356 l_InboundFlag := v_GetFEInfo.inbound_required_flag;
357 l_LogLevel := v_GetFEInfo.log_level;
358 p_LogFileName := v_GetFEInfo.log_file_name;
359
360 l_Found := 'Y';
361 exit;
362 end loop;
363
364 if l_Found = 'N' then
365 raise no_data_found;
366 end if;
367
368 p_ControlChannelName := p_ChannelName;
369
370 if p_FeName is null or p_ConcQID is null then
371 raise no_data_found;
372 end if;
373
374 -- Lets not HC 'jre' here, as from JDK 1.2 this could be different
375 -- Refer to bug 2370475
376 -- p_CmdOptions := pv_JreCommand;
377 p_CmdOptions := null;
378
379 if l_BaseCmdOptions is not null then
380 p_CmdOptions := l_BaseCmdOptions || ' ';
381 end if;
382
383 if l_SubCmdOptions is not null then
384 p_CmdOptions := p_CmdOptions || l_SubCmdOptions || ' ';
385 end if;
386
387 if (l_LogLevel is not null) then
388 p_CmdOptions := p_CmdOptions || pr_AfLogStr || l_LogLevel;
389 end if;
390
391 if (p_CmdOptions is null) then
392 p_CmdOptions := 'NONE';
393 end if;
394
395 if (p_LogFileName is null) then
396 p_LogFileName := 'NONE';
397 end if;
398
399 p_CmdArgs := null;
400 if l_BaseCmdArgs is not null then
401 p_CmdArgs := l_BaseCmdArgs || ' ';
402 end if;
403
404 if l_SubCmdArgs is not null then
405 p_CmdArgs := p_CmdArgs || l_SubCmdArgs;
406 end if;
407
408 if p_ApplMode = 'PIPE' then
409 p_ApplChannelName := XDP_ADAPTER_CORE_PIPE.ConstructChannelName
410 ('APPL', p_ChannelName);
411 elsif p_ApplMode = 'QUEUE' then
412 p_ApplChannelName := pv_OutboundChannelName;
413 else
414 p_ApplMode := 'NONE';
415 p_ApplChannelName := 'NONE';
416 end if;
417
418 if l_InboundFlag = 'Y' then
419 p_InboundChannelName := pv_InboundChannelName;
420 else
421 l_InboundFlag := 'N';
422 p_InboundChannelName := 'NONE';
423 end if;
424
425 end FetchAdapterStartupInfo;
426
427
428 Procedure UpdateAdapter( p_ChannelName in varchar2,
429 p_Status in varchar2 default null,
430 p_ProcessId in number default null,
431 p_UsageCode in varchar2 default null,
432 p_StartupMode in varchar2 default null,
433 p_AdapterName in varchar2 default null,
434 p_AdapterDispName in varchar2 default null,
435 p_SvcInstId in number default null,
436 p_WFItemType in varchar2 default null,
437 p_WFItemKey in varchar2 default null,
438 p_WFActivityName in varchar2 default null,
439 p_CODFlag in varchar2 default null,
440 p_MaxIdleTime in number default -1,
441 p_LastVerified in date default null,
442 p_CmdLineOpts in varchar2 default 'CmdLineOpts',
443 p_CmdLineArgs in varchar2 default 'CmdLineArgs',
444 p_LogLevel in varchar2 default null,
445 p_LogFileName in varchar2 default 'LogFileName',
446 p_SeqInFE in number default -1)
447 is
448
449 l_errorCount NUMBER := 0;
450 l_Status VARCHAR2 (40) := null;
451 l_AdapterDisplayName VARCHAR2 (80) := null;
452
453 begin
454
455 l_Status := p_Status;
456
457 if ((l_Status is not null) and (l_Status = XDP_ADAPTER.pv_statusStoppedError) and
458 (Is_Adapter_Automatic(p_ChannelName))) then
459
460 l_errorCount := XDP_ERRORS_PKG.GET_ERROR_COUNT (
461 p_object_type => XDP_ADAPTER.pv_errorObjectTypeAdapter,
462 p_object_key => p_ChannelName);
463
464 l_errorCount := l_errorCount + 1;
465
466 XDP_ERRORS_PKG.UPDATE_ERROR_COUNT (
467 p_object_type => XDP_ADAPTER.pv_errorObjectTypeAdapter,
468 p_object_key => p_ChannelName,
469 p_error_count => l_errorCount);
470
471 if (l_errorCount >= GetAdapterRestartCount()) then
472
473 l_Status := XDP_ADAPTER.pv_statusDeactivatedSystem;
474
475 if (p_AdapterDispName is null) then
476 select adapter_display_name into l_AdapterDisplayName
477 from xdp_adapter_reg where channel_name = p_ChannelName;
478 else
479 l_AdapterDisplayName := p_AdapterDispName;
480 end if;
481
482 XDP_ADAPTER_CORE.NotifyAdapterSysDeactivation (l_AdapterDisplayName);
483 END IF;
484
485 END IF;
486
487 -- status_active_time is updated anytime status is updated
488
489 update xdp_adapter_reg
490 set adapter_status = nvl(l_Status, adapter_status),
491 status_active_time = decode(l_Status,
492 null, status_active_time,
493 sysdate),
494 process_id = nvl(p_ProcessId,
495 decode(l_Status,
496 XDP_ADAPTER.pv_statusStopped, -1,
497 XDP_ADAPTER.pv_statusStoppedError, -1,
498 XDP_ADAPTER.pv_statusTerminated, -1,
499 XDP_ADAPTER.pv_statusStarting, -1,
500 XDP_ADAPTER.pv_statusDeactivated, -1,
501 XDP_ADAPTER.pv_statusDeactivatedSystem, -1,
502 process_id)),
503 node = decode(l_Status,
504 XDP_ADAPTER.pv_statusStopped, null,
505 XDP_ADAPTER.pv_statusStoppedError, null,
506 XDP_ADAPTER.pv_statusTerminated, null,
507 XDP_ADAPTER.pv_statusDeactivated, null,
508 XDP_ADAPTER.pv_statusDeactivatedSystem, null,
509 node),
510 usage_code = nvl(p_UsageCode, usage_code),
511 startup_mode = nvl(p_StartupMode, startup_mode),
512 adapter_name = nvl(p_AdapterName, adapter_name),
513 adapter_display_name = nvl(p_AdapterDispName,
514 adapter_display_name),
515 service_instance_id = nvl(p_SvcInstId, service_instance_id),
516 wf_item_type = nvl(p_WFItemType, wf_item_type),
517 wf_item_key = nvl(p_WFItemKey, wf_item_key),
518 wf_activity_name = nvl(p_WFActivityName,wf_activity_name),
519 connect_on_demand_flag = nvl(p_CODFlag,connect_on_demand_flag),
520 max_idle_time_minutes = decode(p_MaxIdleTime,
521 -1, max_idle_time_minutes,
522 p_MaxIdleTime),
523 cmd_line_options = decode(p_CmdLineOpts,
524 'CmdLineOpts', cmd_line_options,
525 p_CmdLineOpts),
526 cmd_line_args = decode(p_CmdLineArgs,
527 'CmdLineArgs', cmd_line_args,
528 p_CmdLineArgs),
529 last_verified_time = nvl(p_LastVerified,
530 decode(l_Status,
531 XDP_ADAPTER.pv_statusStarting, last_verified_time,
532 XDP_ADAPTER.pv_statusStopping, last_verified_time,
533 XDP_ADAPTER.pv_statusSuspending, last_verified_time,
534 XDP_ADAPTER.pv_statusResuming, last_verified_time,
535 XDP_ADAPTER.pv_statusConnecting, last_verified_time,
536 XDP_ADAPTER.pv_statusDisconnecting, last_verified_time,
537 XDP_ADAPTER.pv_statusTerminating, last_verified_time,
538 sysdate)),
539 log_level = nvl(p_LogLevel, log_level),
540 log_file_name = decode(p_LogFileName,
541 'LogFileName', log_file_name,
542 p_LogFileName),
543 seq_in_fe = decode(p_SeqInFE,
544 -1, seq_in_fe,
545 p_SeqInFE),
546 last_update_date = sysdate,
547 last_updated_by = fnd_global.user_id,
548 last_update_login = fnd_global.login_id
549 where
550 channel_name = p_ChannelName;
551
552 end UpdateAdapter;
553
554
555 Procedure Update_Adapter_Active_Time(p_ChannelName IN VARCHAR2)
556 IS
557
558 BEGIN
559 UPDATE xdp_adapter_reg
560 SET status_active_time = sysdate,
561 last_update_date = sysdate,
562 last_updated_by = fnd_global.user_id,
563 last_update_login = fnd_global.login_id
564 WHERE channel_name = p_ChannelName;
565
566 END Update_Adapter_Active_time;
567
568 --********** For Getting Rolled Up status of FE **************
569
570 FUNCTION GetOAMFERolledStatus (p_fe_id IN NUMBER,
571 p_mode IN VARCHAR2) RETURN VARCHAR2 AS
572
573
574 CURSOR c_GetASyncAdapterStatus IS
575 SELECT a.adapter_status
576 FROM xdp_adapter_reg a,
577 xdp_adapter_types_b b
578 WHERE a.adapter_type = b.adapter_type AND
579 a.fe_id = p_fe_id AND
580 (b.application_mode = 'QUEUE' OR
581 (b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'));
582
583 CURSOR c_GetSyncAdapterStatus IS
584 SELECT a.adapter_status
585 FROM xdp_adapter_reg a,
586 xdp_adapter_types_b b
587 WHERE a.adapter_type = b.adapter_type AND
588 a.fe_id = p_fe_id AND
589 NOT(b.application_mode = 'QUEUE' OR
590 (b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'));
591
592 l_fe_status VARCHAR2(30);
593 l_match_found BOOLEAN := FALSE;
594
595 BEGIN
596
597 IF p_mode = 'ASYNC' THEN
598
599 FOR v_GetAdapterStatus IN c_GetAsyncAdapterStatus LOOP
600
601 IF v_GetAdapterStatus.adapter_status IN (XDP_ADAPTER.pv_statusError, XDP_ADAPTER.pv_statusSessionLost, XDP_ADAPTER.pv_statusStoppedError, XDP_ADAPTER.pv_statusTerminated)
602 THEN
603 l_fe_status := XDP_ADAPTER.pv_rolledStatusError;
604 RETURN l_fe_status;
605 ELSIF v_GetAdapterStatus.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning, XDP_ADAPTER.pv_statusDisconnected, XDP_ADAPTER.pv_statusSuspended)
606 THEN
607 l_match_found := TRUE;
608 exit;
609 END IF;
610 END LOOP;
611
612 ELSE
613
614 FOR v_GetAdapterStatus IN c_GetSyncAdapterStatus LOOP
615
616 IF v_GetAdapterStatus.adapter_status IN (XDP_ADAPTER.pv_statusError, XDP_ADAPTER.pv_statusSessionLost, XDP_ADAPTER.pv_statusStoppedError, XDP_ADAPTER.pv_statusTerminated)
617 THEN
618 l_fe_status := XDP_ADAPTER.pv_rolledStatusError;
619 RETURN l_fe_status;
620 ELSIF v_GetAdapterStatus.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning, XDP_ADAPTER.pv_statusDisconnected, XDP_ADAPTER.pv_statusSuspended)
621 THEN
622 l_match_found := TRUE;
623 exit;
624 END IF;
625 END LOOP;
626
627 END IF;
628
629 IF l_match_found THEN
630 l_fe_status := XDP_ADAPTER.pv_rolledStatusRunning;
631 RETURN l_fe_status;
632 ELSE
633 l_fe_status := XDP_ADAPTER.pv_rolledStatusUnavailable;
634 RETURN l_fe_status;
635 END IF;
636 END GetOAMFERolledStatus;
637
638
639
640 --********** For Running Adapters**********
641
642 FUNCTION GetOAMAdapterRunningCount(p_fe_id IN NUMBER,
643 p_mode IN VARCHAR2) RETURN NUMBER AS
644
645 l_adapter_status_cnt NUMBER;
646
647 BEGIN
648
649 IF p_mode = 'ASYNC' THEN
650
651 SELECT count(*)
652 INTO l_adapter_status_cnt
653 FROM xdp_adapter_reg a, xdp_adapter_types_b b
654 WHERE a.adapter_type = b.adapter_type
655 AND a.fe_id = p_fe_id
656 AND (b.application_mode = 'QUEUE' OR
657 (b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'))
658 AND a.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning);
659
660 ELSE
661
662 SELECT count(*)
663 INTO l_adapter_status_cnt
664 FROM xdp_adapter_reg a, xdp_adapter_types_b b
665 WHERE a.adapter_type = b.adapter_type
666 AND a.fe_id = p_fe_id
667 AND NOT(b.application_mode = 'QUEUE' OR
668 (b.application_mode = 'NONE' AND b.inbound_required_flag = 'Y'))
669 AND a.adapter_status IN (XDP_ADAPTER.pv_statusInUse, XDP_ADAPTER.pv_statusRunning);
670
671 END IF;
672
673 RETURN l_adapter_status_cnt;
674
675 END GetOAMAdapterRunningCount;
676
677
678 --********** For counting no. of job an adapter ***********
679
680 FUNCTION GetNumOfJobsCount(p_fe_id IN NUMBER,
681 p_fe_name IN VARCHAR2,
682 p_mode IN VARCHAR2) return NUMBER AS
683 l_num_of_job_cnt NUMBER;
684
685 BEGIN
686
687 if p_mode = 'ASYNC' THEN
688
689 SELECT COUNT(xomq.msg_id) num_of_jobs
690 INTO l_num_of_job_cnt
691 FROM AQ$xnp_out_msg_qtab xomq
692 WHERE xomq.consumer_name = p_fe_name;
693
694 else
695
696 SELECT COUNT(DISTINCT xaj.job_id) num_of_jobs
697 INTO l_num_of_job_cnt
698 FROM xdp_adapter_job_queue xaj, xdp_adapter_reg xar, xdp_adapter_types_b xat
699 WHERE xaj.fe_id = xar.fe_id
700 AND xar.adapter_type = xat.adapter_type
701 AND xaj.fe_id = p_fe_id
702 AND xat.application_mode <> 'QUEUE';
703
704
705 end if;
706
707 RETURN l_num_of_job_cnt;
708
709 END GetNumOfJobsCount;
710
711
712 Function GetCurrentAdapterStatus(p_ChannelName in varchar2) return varchar2
713 is
714 cursor c_GetAdapterStatus is
715 select xag.adapter_status
716 from xdp_adapter_reg xag
717 where channel_name = p_ChannelName;
718
719 l_CurrentAdapterStatus varchar2(40);
720 l_exists varchar2(1) := 'N';
721 begin
722
723 for v_AdapterStatus in c_GetAdapterStatus loop
724 l_CurrentAdapterStatus := v_AdapterStatus.adapter_status;
725 l_exists := 'Y';
726 end loop;
727
728 if l_exists = 'N' then
729 raise no_data_found;
730 end if;
731
732 return (l_CurrentAdapterStatus);
733
734 end GetCurrentAdapterStatus;
735
736 --Used by adapter verification logic
737 Function ObtainAdapterLock_Verify(p_ChannelName in varchar2,
738 p_Timeout in number default pv_LockTimeout) return varchar2
739 is
740 l_Status number;
741 l_LockHandle varchar2(240);
742
743 begin
744 l_LockHandle := ObtainHandle(p_ChannelName => p_ChannelName);
745
746 l_Status := DBMS_LOCK.REQUEST(lockhandle=> l_LockHandle,
747 timeout => p_Timeout,
748 lockmode => 6);
749
750 if l_Status in(0, 4) then
751 return 'Y';
752 elsif l_Status = 1 then
753 return 'N';
754 else
755 raise e_LockException;
756 end if;
757
758 end ObtainAdapterLock_Verify;
759
760 --Used by FA processing
761 Function ObtainAdapterLock_FA(p_ChannelName in varchar2,
762 p_Timeout in number default pv_LockTimeout) return varchar2
763 is
764 l_LockFlag varchar2(1) := 'N';
765 l_InstanceName varchar2(40);
766 l_ChannelName varchar2(40);
767 begin
768 l_LockFlag := ObtainAdapterLock_Verify (p_ChannelName => p_ChannelName,
769 p_Timeout => p_Timeout);
770
771 if l_LockFlag = 'Y' then
772
773 IF (INSTR(p_ChannelName, 'SESSION_', 1) > 0) THEN
774 l_ChannelName := substr(p_ChannelName, 9);
775 ELSE
776 l_ChannelName := p_ChannelName;
777 END IF;
778
779 select node into l_InstanceName from xdp_adapter_reg where channel_name = l_ChannelName;
780 --Adapter is running on different instance than this session
781 if l_InstanceName is not null and l_InstanceName <> pv_InstanceName then
782 l_LockFlag := ReleaseAdapterLock(p_ChannelName => p_ChannelName);
783 l_LockFlag := 'N';
784 end if;
785 end if;
786
787 return l_LockFlag;
788 end ObtainAdapterLock_FA;
789
790 --Used by adapter to hold session lock
791 Function ObtainAdapterLock(p_ChannelName in varchar2,
792 p_Timeout in number default pv_LockTimeout) return varchar2
793 is
794 PRAGMA AUTONOMOUS_TRANSACTION;
795 l_LockFlag varchar2(1);
796 l_ChannelName varchar2(40);
797 begin
798 l_LockFlag := ObtainAdapterLock_Verify (p_ChannelName => p_ChannelName,
799 p_Timeout => p_Timeout);
800
801 if l_LockFlag = 'Y' then
802
803 IF (INSTR(p_ChannelName, 'SESSION_', 1) > 0) THEN
804 l_ChannelName := substr(p_ChannelName, 9);
805 ELSE
806 l_ChannelName := p_ChannelName;
807 END IF;
808
809 update xdp_adapter_reg set node = pv_InstanceName where channel_name = l_ChannelName;
810 commit;
811 end if;
812
813 return l_LockFlag;
814 end ObtainAdapterLock;
815
816
817 Function ReleaseAdapterLock(p_ChannelName in varchar2) return varchar2
818 is
819 l_Status number;
820 l_LockHandle varchar2(240);
821
822 begin
823 l_LockHandle := ObtainHandle(ReleaseAdapterLock.p_ChannelName);
824
825 l_Status := DBMS_LOCK.RELEASE(lockhandle => l_LockHandle);
826
827 if l_Status in (0, 4) then
828 return 'Y';
829 else
830 raise e_LockReleaseException;
831 end if;
832
833 end ReleaseAdapterLock;
834
835
836 Function IsChannelCOD(p_ChannelName in varchar2) return varchar2
837 is
838 l_CODFlag varchar2(1) := 'N';
839 begin
840
841 select NVL(CONNECT_ON_DEMAND_FLAG, 'N') into l_CODFlag
842 from xdp_adapter_reg
843 where CHANNEL_NAME = p_ChannelName;
844
845 return (l_CODFlag);
846
847 end IsChannelCOD;
848
849
850 Function PeekIntoFeWaitQueue(p_ChannelName in varchar2) return varchar2
851 is
852 l_check varchar2(1) := 'N';
853 begin
854
855 begin
856 select 'Y' into l_check
857 from dual
858 where exists
859 ( select JOB_ID from xdp_adapter_job_queue a, xdp_adapter_reg b
860 where b.channel_name = p_ChannelName
861 and b.fe_id = a.fe_id);
862 exception
863 when no_data_found then
864 l_check := 'N';
865 end;
866
867 return (l_check);
868 end PeekIntoFeWaitQueue;
869
870
871 Function ObtainHandle(p_ChannelName in varchar2) return varchar2
872 is
873 PRAGMA AUTONOMOUS_TRANSACTION;
874
875 l_LockHandle varchar2(240);
876 begin
877
878 DBMS_LOCK.ALLOCATE_UNIQUE(lockname => ObtainHandle.p_ChannelName,
879 lockhandle => l_LockHandle);
880
881 commit;
882
883 return (l_LockHandle);
884
885 end ObtainHandle;
886
887
888 Function GetAckTimeOut return number
889 is
890 l_ProfileValue varchar2(40);
891 begin
892 if fnd_profile.defined('XDP_ACK_TIMEOUT') then
893 fnd_profile.get('XDP_ACK_TIMEOUT', l_ProfileValue);
894 if to_number(l_ProfileValue) <= 0 then
895 l_ProfileValue := '60';
896 end if;
897 else
898 l_ProfileValue := '60';
899 end if;
900
901 return to_number(l_ProfileValue);
902
903 end GetAckTimeOut;
904
905 -- ************** Added - sacsharm - START *********************
906
907 Function GetLockTimeOut return number
908 is
909 l_ProfileValue varchar2(40);
910 begin
911 -- 1 hr = 60 * 60 = 3600 secs
912 if fnd_profile.defined('XDP_ADAPTER_LOCK_TIMEOUT') then
913 fnd_profile.get('XDP_ADAPTER_LOCK_TIMEOUT', l_ProfileValue);
914 if to_number(l_ProfileValue) <= 0 then
915 l_ProfileValue := '3600';
916 end if;
917 else
918 l_ProfileValue := '3600';
919 end if;
920
921 return to_number(l_ProfileValue);
922
923 end GetLockTimeOut;
924
925 Procedure Update_Adapter_Status (p_ChannelName in varchar2,
926 p_Status in varchar2,
927 p_ErrorMsg in varchar2 default null,
928 p_ErrorMsgParams in varchar2 default null,
929 p_WFItemType in varchar2 default null,
930 p_WFItemKey in varchar2 default null)
931 is
932 PRAGMA AUTONOMOUS_TRANSACTION;
933 begin
934 UpdateAdapter (
935 p_ChannelName => p_ChannelName,
936 p_Status => p_Status,
937 p_WFItemType => p_WFItemType,
938 p_WFItemKey => p_WFItemKey
939 );
940 if p_ErrorMsg is not null then
941 XDP_ERRORS_PKG.Set_Message (
942 p_object_type => XDP_ADAPTER.pv_errorObjectTypeAdapter,
943 p_object_key => p_ChannelName,
944 p_message_name => p_ErrorMsg,
945 p_message_parameters => p_ErrorMsgParams);
946 end if;
947 commit;
948 end Update_Adapter_Status;
949
950 Function Get_Job_Id_For_Request (p_RequestId in number) return number
951 is
952 cursor c_GetJobID is
953 select job_id
954 from xdp_adapter_admin_reqs
955 where request_id = p_RequestId;
956
957 l_JobId number := -1;
958
959 begin
960
961 for v_GetJobId in c_GetJobID loop
962 l_JobId := v_GetJobId.job_id;
963
964 exit;
965 end loop;
966
967 if l_JobId = -1 then
968 raise no_data_found;
969 end if;
970
971 return l_JobId;
972
973 end Get_Job_Id_For_Request;
974
975 Function Get_Fe_Id_For_name (p_FeName in varchar2) return number
976 is
977 cursor c_GetFEiD is
978 select fe_id
979 from xdp_fes
980 where UPPER(fulfillment_element_name) = UPPER(p_FeName);
981
982 l_FEId number := -1;
983
984 begin
985
986 for v_GetFeId in c_GetFEid loop
987 l_FeID := v_GetFeId.fe_id;
988
989 exit;
990 end loop;
991
992 if l_FeID = -1 then
993 raise no_data_found;
994 end if;
995
996 return l_FEID;
997
998 end Get_Fe_Id_For_Name;
999
1000 Function Is_Max_Connection_Reached (p_fe_id in NUMBER) return boolean
1001 is
1002
1003 l_CurrentCount number := 0;
1004 l_MaxCount number := 0;
1005 begin
1006 select count(*)
1007 into l_CurrentCount
1008 from XDP_ADAPTER_REG
1009 where FE_ID = p_fe_id and
1010 ADAPTER_STATUS not in (XDP_ADAPTER.pv_statusStopped,
1011 XDP_ADAPTER.pv_statusStoppedError,
1012 XDP_ADAPTER.pv_statusTerminated,
1013 -- (ankung) XDP_ADAPTER.pv_statusStopping,
1014 -- XDP_ADAPTER.pv_statusTerminating,
1015 XDP_ADAPTER.pv_statusNotAvailable,
1016 XDP_ADAPTER.pv_statusDeactivated,
1017 XDP_ADAPTER.pv_statusDeactivatedSystem);
1018
1019 select MAX_CONNECTION
1020 into l_MaxCount
1021 from XDP_FES
1022 where FE_ID = p_fe_id;
1023
1024 if l_CurrentCount < l_maxCount then
1025 return TRUE;
1026 else
1027 return FALSE;
1028 end if;
1029 end Is_Max_Connection_Reached;
1030
1031 --
1032 -- Procedure to delete an Adapter
1033 --
1034 PROCEDURE Delete_Adapter (p_channel_name IN VARCHAR2)
1035 IS
1036
1037 cursor c_GetAdapterAdminReqs is
1038 select xar.request_id
1039 from xdp_adapter_admin_reqs xar
1040 where channel_name = p_channel_name;
1041
1042 BEGIN
1043 -- Cleanup XDP_ERROR_LOG
1044 BEGIN
1045 DELETE FROM xdp_error_log WHERE
1046 object_type = XDP_ADAPTER.pv_errorObjectTypeAdapter and
1047 object_key = p_channel_name;
1048 EXCEPTION
1049 -- Not an error if no errors exists for the adapter
1050 --
1051 WHEN NO_DATA_FOUND THEN
1052 NULL;
1053 END;
1054
1055 -- Cleanup request audit table
1056 BEGIN
1057 DELETE FROM xdp_adapter_audit WHERE channel_name = p_channel_name;
1058 EXCEPTION
1059 -- Not an error if no rows exist in audit table
1060 --
1061 WHEN NO_DATA_FOUND THEN
1062 NULL;
1063 END;
1064
1065 -- Delete all occurences of the requests and dbms_jobs for the adapter from the
1066 -- XDP_ADAPTER_ADMIN_REQS table if present
1067 --
1068 for v_AdapterReq in c_GetAdapterAdminReqs loop
1069 RemoveAdapterAdminReq (p_RequestID => v_AdapterReq.request_id);
1070 END LOOP;
1071
1072 -- Delete the Adapter from the XDP_ADAPTER_REG table
1073 --
1074 DELETE FROM xdp_adapter_reg WHERE channel_name = p_channel_name;
1075
1076 END Delete_Adapter;
1077
1078 --
1079 -- Procedure to delete all Adapter for a FE
1080 --
1081 PROCEDURE Delete_Adapters_For_Fe (p_fe_id IN NUMBER)
1082 IS
1083
1084 cursor c_GetAdapters is
1085 select channel_name
1086 from xdp_adapter_reg
1087 where fe_id = p_fe_id;
1088
1089 BEGIN
1090 -- Delete all adapters belonging to the FE, if present
1091
1092 for v_Adapters in c_GetAdapters loop
1093 Delete_Adapter (p_channel_name => v_Adapters.channel_name);
1094 END LOOP;
1095
1096 END Delete_Adapters_For_Fe;
1097
1098 PROCEDURE Audit_Adapter_Admin_Request (p_RequestID in number,
1099 p_RequestType in varchar2,
1100 p_RequestDate in date,
1101 p_RequestedBy in varchar2,
1102 p_Freq in number,
1103 p_RequestStatus in varchar2,
1104 p_RequestMessage in varchar2,
1105 p_ChannelName in varchar2)
1106 IS
1107
1108 cursor c_GetAdapterInfo is
1109 select xag.adapter_name,
1110 xag.adapter_status,
1111 xag.adapter_type,
1112 xag.service_instance_id,
1113 xag.connect_on_demand_flag,
1114 xag.max_idle_time_minutes,
1115 xag.cmd_line_options,
1116 xag.cmd_line_args,
1117 xag.log_level,
1118 xag.log_file_name
1119 from
1120 xdp_adapter_reg xag
1121 where xag.channel_name = p_ChannelName;
1122
1123 l_AdapterName varchar2(40);
1124 l_AdapterStatus varchar2(40);
1125 l_AdapterType varchar2(40);
1126 l_ConcQID number;
1127 l_COD varchar2(1);
1128 l_MaxIdleTime number;
1129 l_CmdOptions varchar2(240);
1130 l_CmdArgs varchar2(240);
1131 l_LogLevel varchar2(40);
1132 l_LogFileName varchar2(240);
1133
1134 BEGIN
1135 for v_GetAdapterInfo in c_GetAdapterInfo loop
1136
1137 l_AdapterName := v_GetAdapterInfo.adapter_name;
1138 l_AdapterStatus := v_GetAdapterInfo.adapter_status;
1139 l_AdapterType := v_GetAdapterInfo.adapter_type;
1140 l_ConcQID := v_GetAdapterInfo.service_instance_id;
1141 l_COD := v_GetAdapterInfo.connect_on_demand_flag;
1142 l_MaxIdleTime := v_GetAdapterInfo.max_idle_time_minutes;
1143 l_CmdOptions := v_GetAdapterInfo.cmd_line_options;
1144 l_CmdArgs := v_GetAdapterInfo.cmd_line_args;
1145 l_LogLevel := v_GetAdapterInfo.log_level;
1146 l_LogFileName := v_GetAdapterInfo.log_file_name;
1147
1148 exit;
1149 end loop;
1150
1151 insert into xdp_adapter_audit (
1152 adapter_audit_id,
1153 channel_name,
1154 request_type,
1155 request_status,
1156 completion_date,
1157 requested_by_user,
1158 adapter_name,
1159 adapter_status,
1160 adapter_type,
1161 service_instance_id,
1162 connect_on_demand_flag,
1163 max_idle_time_minutes,
1164 cmd_line_options,
1165 cmd_line_args,
1166 log_file_name,
1167 application_id,
1168 created_by,
1169 creation_date,
1170 last_updated_by,
1171 last_update_date,
1172 last_update_login)
1173 values
1174 ( XDP_ADAPTER_AUDIT_S.NEXTVAL,
1175 p_ChannelName,
1176 p_RequestType,
1177 p_RequestStatus,
1178 sysdate,
1179 p_RequestedBy,
1180 l_AdapterName,
1181 l_AdapterStatus,
1182 l_AdapterType,
1183 l_ConcQID,
1184 l_COD,
1185 l_MaxIdleTime,
1186 l_CmdOptions,
1187 l_CmdArgs,
1188 l_LogFileName,
1189 XDP_ADAPTER.pv_AppID,
1190 FND_GLOBAL.USER_ID,
1191 sysdate,
1192 FND_GLOBAL.USER_ID,
1193 sysdate,
1194 FND_GLOBAL.LOGIN_ID);
1195
1196 END Audit_Adapter_Admin_Request;
1197
1198 --Function Is_Adapter_Available (p_fe_id in NUMBER, p_AdapterType in VARCHAR2) return boolean;
1199 Procedure Are_Adapter_Generics_Available (p_fe_id in NUMBER, p_AdapterType in VARCHAR2,
1200 p_GenCountActive OUT NOCOPY NUMBER, p_GenCountFuture OUT NOCOPY NUMBER)
1201 is
1202 cursor c_get_valid_generics (c_fe_id NUMBER, c_AdapterType VARCHAR2) is
1203 select XGC.START_DATE
1204 from XDP_FE_SW_GEN_LOOKUP XSW, XDP_FE_GENERIC_CONFIG XGC
1205 where XGC.FE_ID = c_fe_id and
1206 XSW.ADAPTER_TYPE = c_AdapterType and
1207 XSW.FE_SW_GEN_LOOKUP_ID = XGC.FE_SW_GEN_LOOKUP_ID and
1208 ((XGC.END_DATE is null) or ((XGC.END_DATE is not null) and (XGC.END_DATE > SYSDATE)));
1209 begin
1210 p_GenCountActive := 0;
1211 p_GenCountFuture := 0;
1212
1213 for v_GetValidGens in c_get_valid_generics (p_fe_id, p_AdapterType) loop
1214 if (v_GetValidGens.START_DATE <= SYSDATE) then
1215 p_GenCountActive := p_GenCountActive + 1;
1216 else
1217 p_GenCountFuture := p_GenCountFuture + 1;
1218 END IF;
1219 END LOOP;
1220
1221 end Are_Adapter_Generics_Available;
1222
1223 Function Is_Message_Adapter_Available(p_fe_name in varchar2) return VARCHAR2
1224
1225 is
1226 l_adapter_name varchar2(80) := NULL;
1227
1228 --
1229 -- NOTE: This method should always be exactly the same as
1230 -- XNP_UTILS.Get_Adapter_Using_FE(p_fe_name).
1231 -- We were not able to call this method directly from that
1232 -- function because of the PRAGMA restrictions
1233 --
1234
1235 cursor c_getadapter IS
1236 SELECT xad.adapter_name
1237 FROM xdp_adapter_reg xad, xdp_adapter_types_b t,xdp_fes XFE
1238 WHERE XAD.fe_id = XFE.fe_id
1239 AND XFE.fulfillment_element_name = p_fe_name
1240 AND xad.adapter_type = t.adapter_type
1241 AND application_mode='QUEUE'
1242 AND xad.adapter_status not in (XDP_ADAPTER.pv_statusNotAvailable)
1243 ORDER BY
1244 DECODE(adapter_status, xdp_adapter.pv_statusRunning, 1,
1245 xdp_adapter.pv_statusSuspended, 2,
1246 xdp_adapter.pv_statusDisconnected, 3,
1247 xdp_adapter.pv_statusStopped, 4, 5)
1248 ASC ;
1249
1250 BEGIN
1251 if c_getadapter%ISOPEN then
1252 close c_getadapter;
1253 end if;
1254
1255 open c_getadapter;
1256
1257 fetch c_getadapter into l_adapter_name;
1258
1259 if c_getadapter%NOTFOUND then
1260 l_adapter_name := NULL;
1261 end if;
1262
1263 return l_adapter_name;
1264 exception
1265 when others then
1266 if c_getadapter%ISOPEN then
1267 close c_getadapter;
1268 end if;
1269 raise;
1270
1271 END Is_Message_Adapter_Available;
1272
1273
1274 Function Is_Message_Adapter_Available(p_fe_id in number) return VARCHAR2
1275
1276 is
1277 l_adapter_name varchar2(80) := NULL;
1278
1279 cursor c_getadapter(FEID number) is
1280 SELECT xad.adapter_name
1281 FROM xdp_adapter_reg xad, xdp_adapter_types_b t
1282 WHERE xad.adapter_type = t.adapter_type
1283 AND FE_ID = FEID
1284 AND application_mode='QUEUE'
1285 AND xad.adapter_status not in (XDP_ADAPTER.pv_statusNotAvailable)
1286 order by
1287 DECODE(adapter_status, xdp_adapter.pv_statusRunning, 1,
1288 xdp_adapter.pv_statusSuspended, 2,
1289 xdp_adapter.pv_statusDisconnected, 3,
1290 xdp_adapter.pv_statusStopped, 4, 5)
1291 ASC ;
1292
1293 BEGIN
1294
1295 if c_getadapter%ISOPEN then
1296 close c_getadapter;
1297 end if;
1298
1299 open c_getadapter(p_fe_id);
1300
1301 fetch c_getadapter into l_adapter_name;
1302
1303 if c_getadapter%NOTFOUND then
1304 l_adapter_name := NULL;
1305 end if;
1306
1307 return l_adapter_name;
1308
1309 exception
1310 when others then
1311 if c_getadapter%ISOPEN then
1312 close c_getadapter;
1313 end if;
1314 raise;
1315 end Is_Message_Adapter_Available;
1316
1317
1318 /*
1319 Check if any Adapter is running for
1320 a given Fulfillment Element
1321 */
1322 Function Is_FE_Adapter_Running(p_fe_id in number)
1323 return BOOLEAN
1324 IS
1325 lv_exists varchar2(1) := 'N';
1326
1327 CURSOR c_IsAdapterRunning is
1328 select 'Y' yahoo
1329 from dual
1330 where exists(
1331 select 1
1332 from XDP_ADAPTER_REG
1333 where fe_id = p_fe_id
1334 and adapter_status not in (XDP_ADAPTER.pv_statusStopped,
1335 XDP_ADAPTER.pv_statusStoppedError,
1336 XDP_ADAPTER.pv_statusTerminated,
1337 XDP_ADAPTER.pv_statusNotAvailable,
1338 XDP_ADAPTER.pv_statusDeactivated,
1339 XDP_ADAPTER.pv_statusDeactivatedSystem)
1340 );
1341 BEGIN
1342 for v_IsAdapterRunning in c_IsAdapterRunning loop
1343 lv_exists := v_IsAdapterRunning.yahoo;
1344 exit;
1345 end loop;
1346
1347 if lv_exists = 'Y' then
1348 return TRUE;
1349 else
1350 return FALSE;
1351 end if;
1352
1353 END Is_FE_Adapter_Running;
1354
1355 /*
1356 Check if any Adapter is running for
1357 a given Fulfillment Element Type
1358 */
1359 Function Is_FEType_Adapter_Running(p_fetype_id in number)
1360 return BOOLEAN
1361 IS
1362 lv_exists varchar2(1) := 'N';
1363
1364 CURSOR c_IsAdapterRunning is
1365 select 'Y' yahoo
1366 from dual
1367 where exists(
1368 select 1
1369 from XDP_ADAPTER_REG arn, XDP_FES fet
1370 where arn.fe_id = fet.fe_id
1371 and fet.fetype_id = p_fetype_id
1372 and arn.adapter_status not in (XDP_ADAPTER.pv_statusStopped,
1373 XDP_ADAPTER.pv_statusStoppedError,
1374 XDP_ADAPTER.pv_statusTerminated,
1375 XDP_ADAPTER.pv_statusNotAvailable,
1376 XDP_ADAPTER.pv_statusDeactivated,
1377 XDP_ADAPTER.pv_statusDeactivatedSystem)
1378 );
1379 BEGIN
1380
1381 for v_IsAdapterRunning in c_IsAdapterRunning loop
1382 lv_exists := v_IsAdapterRunning.yahoo;
1383 exit;
1384 end loop;
1385
1386 if lv_exists = 'Y' then
1387 return TRUE;
1388 else
1389 return FALSE;
1390 end if;
1391
1392 END Is_FEType_Adapter_Running;
1393
1394
1395 Function Is_Adapter_Implemented (p_ChannelName in varchar2) return boolean
1396 is
1397 l_ClassName varchar2(240);
1398 begin
1399 select b.adapter_class into l_ClassName
1400 from xdp_adapter_reg a, xdp_adapter_types_b b
1401 where a.CHANNEL_NAME = p_ChannelName and
1402 a.adapter_type = b.adapter_type;
1403
1404 if (upper(l_ClassName) = 'NONE') then
1405 return FALSE;
1406 else
1407 return TRUE;
1408 END IF;
1409
1410 end Is_Adapter_Implemented;
1411
1412 Function Verify_Adapter (p_ChannelName in varchar2) return boolean
1413 is
1414 PRAGMA AUTONOMOUS_TRANSACTION;
1415
1416 l_FeID number;
1417 l_ProcessID number;
1418 l_ConcQID number;
1419
1420 l_AdapterLocked1 varchar2(1) := 'N';
1421
1422 begin
1423 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1424 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'XDP_ADAPTER_CORE_DB.VERIFY_ADAPTER',
1425 'BEGIN:p_ChannelName: '||p_ChannelName);
1426 end if;
1427
1428 if (XDP_ADAPTER_CORE_DB.Is_Adapter_Implemented (p_ChannelName)) then
1429
1430 --skilaru 05/17/2002
1431 --If the channel is of type PIPE then we dont need to lock..
1432 IF ( checkLockRequired( p_ChannelName ) ) THEN
1433 l_AdapterLocked1 := XDP_ADAPTER_CORE_DB.ObtainAdapterLock_Verify('SESSION_'||p_ChannelName);
1434 END IF;
1435
1436 if l_AdapterLocked1 = 'Y' then
1437
1438 -- Adapter NOT running, release the SESSION lock
1439
1440 --dbms_output.put_line('Got SESSION lock for: ' || p_ChannelName);
1441 --dbms_output.put_line('Adapter NOT RUNNING');
1442
1443 if XDP_ADAPTER_CORE_DB.ReleaseAdapterLock('SESSION_'||p_ChannelName) = 'N' then
1444 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1445 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED, 'XDP_ADAPTER_CORE_DB.VERIFY_ADAPTER',
1446 'Could not release SESSION lock, Channel name: '||p_ChannelName);
1447 end if;
1448 end if;
1449
1450 XDP_ADAPTER_CORE_DB.FetchAdapterInfo(
1451 p_ChannelName => p_ChannelName,
1452 p_FEID => l_FeID,
1453 p_ProcessID => l_ProcessID,
1454 p_ConcQID => l_ConcQID);
1455
1456 -- Cannot use XDP_ADAPTER_CORE_DB.Update_Adapter_Status, an autonomous
1457 -- procedure
1458
1459 XDP_ADAPTER_CORE_DB.UpdateAdapter (
1460 p_ChannelName => p_ChannelName,
1461 p_Status => XDP_ADAPTER.pv_statusStoppedError
1462 );
1463
1464 XDP_ERRORS_PKG.Set_Message (
1465 p_object_type => XDP_ADAPTER.pv_errorObjectTypeAdapter,
1466 p_object_key => p_ChannelName,
1467 p_message_name => 'XDP_ADAPTER_ABNORMAL_EXIT',
1468 p_message_parameters => 'PROCESS_ID='||l_ProcessID||'#XDP#');
1469
1470 commit;
1471
1472 return false;
1473 else
1474 --dbms_output.put_line('Did not get SESSION lock for: ' || p_ChannelName);
1475 --dbms_output.put_line('Adapter RUNNING');
1476
1477 -- Could not get lock, Adapter running, update last_verified_date
1478 XDP_ADAPTER_CORE_DB.UpdateAdapter (
1479 p_ChannelName => p_ChannelName,
1480 p_LastVerified => sysdate);
1481 end if;
1482 else
1483 -- Adapter not implemented, so Adapter is 'running', update last_verified_date
1484 XDP_ADAPTER_CORE_DB.UpdateAdapter (
1485 p_ChannelName => p_ChannelName,
1486 p_LastVerified => sysdate);
1487 end if;
1488
1489 commit;
1490
1491 return true;
1492
1493 end Verify_Adapter;
1494
1495 Function Is_Adapter_Automatic (p_ChannelName in varchar2) return boolean
1496 is
1497 l_StartupMode varchar2(30);
1498 begin
1499 select a.startup_mode into l_StartupMode
1500 from xdp_adapter_reg a
1501 where a.CHANNEL_NAME = p_ChannelName;
1502
1503 if (upper(l_StartupMode) IN (XDP_ADAPTER.pv_startAutomatic, XDP_ADAPTER.pv_startOnDemand)) then
1504 return TRUE;
1505 else
1506 return FALSE;
1507 END IF;
1508
1509 end Is_Adapter_Automatic;
1510
1511 Function GetAdapterRestartCount return number
1512 is
1513 l_ProfileValue varchar2(40);
1514 begin
1515 if fnd_profile.defined('XDP_ADAPTER_RESTART_COUNT') then
1516 fnd_profile.get('XDP_ADAPTER_RESTART_COUNT', l_ProfileValue);
1517 if to_number(l_ProfileValue) <= 0 then
1518 l_ProfileValue := '5';
1519 end if;
1520 else
1521 l_ProfileValue := '5';
1522 end if;
1523
1524 return to_number(l_ProfileValue);
1525
1526 end GetAdapterRestartCount;
1527
1528 -- ************** Added - sacsharm - END *********************
1529
1530 Function checkLockRequired( p_Channelname in varchar2) return boolean IS
1531
1532 cursor getApplMode IS
1533 SELECT atb.application_mode
1534 FROM xdp_adapter_reg ar, xdp_adapter_types_b atb
1535 WHERE ar.channel_name = p_Channelname
1536 AND ar.adapter_type = atb.adapter_type;
1537
1538 lv_appl_mode VARCHAR2(40);
1539 lv_is_lock_required BOOLEAN := TRUE;
1540
1541 Begin
1542
1543 FOR lv_rec in getApplMode LOOP
1544 lv_appl_mode := lv_rec.application_mode;
1545 IF( lv_appl_mode <> 'PIPE' ) THEN
1546 lv_is_lock_required := FALSE;
1547 END IF;
1548 END LOOP;
1549
1550 RETURN lv_is_lock_required;
1551
1552 EXCEPTION
1553 WHEN others THEN
1554 RAISE;
1555 END checkLockRequired;
1556
1557
1558 -----------------------------------------------------------
1559 Procedure Copy_FET_Attribute(
1560 p_fe_sw_gen_lookup_id in NUMBER,
1561 p_adapter_type IN VARCHAR2,
1562 p_caller_id NUMBER,
1563 x_retcode OUT NOCOPY NUMBER,
1564 x_errbuf OUT NOCOPY VARCHAR2)
1565 AS
1566 cursor c_adapter_type_attribute is
1567 select ATTRIBUTE_NAME,DEFAULT_VALUE,DISPLAY_NAME,DESCRIPTION
1568 from XDP_ADAPTER_TYPE_ATTRS_VL
1569 where ADAPTER_TYPE = p_adapter_type
1570 and attribute_name not in (
1571 select FE_ATTRIBUTE_NAME from xdp_fe_attribute_def_vl
1572 where fe_sw_gen_lookup_id = p_fe_sw_gen_lookup_id);
1573
1574 cursor c_adapter_types is
1575 SELECT BASE_ADAPTER_TYPE FROM XDP_ADAPTER_TYPES_B
1576 WHERE ADAPTER_TYPE = p_adapter_type;
1577
1578 l_rowid ROWID;
1579 l_base_type XDP_ADAPTER_TYPES_B.ADAPTER_TYPE%TYPE;
1580 l_fe_attribute_id number;
1581 BEGIN
1582 x_retcode := 0;
1583 SavePoint CopyFETATTR;
1584
1585 FOR l_at_attr in c_adapter_type_attribute LOOP
1586
1587 select XDP.XDP_FE_ATTRIBUTE_DEF_S.nextval into l_fe_attribute_id from dual;
1588
1589 XDP_FE_ATTRIBUTE_DEF_PKG.INSERT_ROW(
1590 l_rowid,
1591 l_fe_attribute_id,
1592 p_fe_sw_gen_lookup_id,
1593 l_at_attr.ATTRIBUTE_NAME,
1594 'N',
1595 l_at_attr.DEFAULT_VALUE,
1596 null,
1597 l_at_attr.DISPLAY_NAME,
1598 l_at_attr.DESCRIPTION,
1599 sysdate,
1600 p_caller_id,
1601 sysdate,
1602 p_caller_id,p_caller_id);
1603
1604
1605 END LOOP;
1606 --
1607 -- Should be only one entry for this cursor. Recursively copy parent adapter attributes
1608 --
1609 FOR l_base_type in c_adapter_types LOOP
1610 IF l_base_type.BASE_ADAPTER_TYPE is not NULL THEN
1611 Copy_FET_Attribute(
1612 p_fe_sw_gen_lookup_id,
1613 l_base_type.BASE_ADAPTER_TYPE,
1614 p_caller_id,
1615 x_retcode,
1616 x_errbuf);
1617 END IF;
1618 END LOOP;
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 rollback to CopyFETATTR;
1622 x_retcode := SQLCODE;
1623 x_errbuf := sqlerrm;
1624 END Copy_FET_Attribute;
1625
1626 -----------------------------------------------------------
1627 Procedure Copy_FE(
1628 p_FeName in varchar2,
1629 p_FeDisplayName in varchar2,
1630 p_FeID in varchar2,
1631 p_NewFeID in NUMBER,
1632 p_CallerID in NUMBER,
1633 x_retcode OUT NOCOPY NUMBER,
1634 x_errbuf OUT NOCOPY VARCHAR2)
1635 AS
1636 l_rowid ROWID;
1637 l_new_feId Number;
1638 l_new_generic_config_Id Number;
1639 CURSOR aFe is
1640 SELECT fetype_id,
1641 max_connection,
1642 min_connection,
1643 session_controller_id,
1644 valid_date,
1645 invalid_date,
1646 geo_area_id,
1647 role_name,
1648 network_unit_id,
1649 description
1650 from xdp_fes_vl
1651 where fe_id = p_FeID;
1652 CURSOR c_fe_generic_config(l_fe_id number) IS
1653 SELECT
1654 fe_generic_config_id,
1655 fe_sw_gen_lookup_id,
1656 start_date,
1657 end_date,
1658 sw_start_proc,
1659 sw_exit_proc
1660 FROM XDP_FE_GENERIC_CONFIG
1661 WHERE fe_id = l_fe_id;
1662 CURSOR c_fe_attribute_val(l_fe_config_id number) IS
1663 SELECT
1664 fe_generic_config_id,
1665 fe_attribute_id,
1666 fe_attribute_value,
1667 display_name,
1668 description
1669 FROM XDP_FE_ATTRIBUTE_VAL_VL
1670 WHERE fe_generic_config_id = l_fe_config_id;
1671 BEGIN
1672 x_retcode := 0;
1673 SavePoint CopyFE;
1674 select XDP.xdp_fes_s.nextval into l_new_feId from dual;
1675
1676 for l_FeRecord in aFe loop
1677 XDP_FES_PKG.INSERT_ROW(
1678 l_rowid,
1679 l_new_feId,
1680 l_FeRecord.fetype_id,
1681 p_FeName,
1682 l_FeRecord.MAX_CONNECTION,
1683 l_FeRecord.MIN_CONNECTION,
1684 l_FeRecord.SESSION_CONTROLLER_ID,
1685 l_FeRecord.VALID_DATE,
1686 l_FeRecord.INVALID_DATE,
1687 l_FeRecord.GEO_AREA_ID,
1688 l_FeRecord.ROLE_NAME,
1689 l_FeRecord.NETWORK_UNIT_ID,
1690 p_FeDisplayName,
1691 l_FeRecord.DESCRIPTION,
1692 SYSDATE,
1693 p_CallerID,
1694 SYSDATE,
1695 p_CallerID,
1696 p_CallerID
1697 );
1698 END LOOP;
1699
1700 FOR l_fe_gen_config in c_fe_generic_config(p_FeID) LOOP
1701 select xdp_fe_generic_config_s.nextval into l_new_generic_config_Id from dual;
1702
1703 insert into xdp_fe_generic_config (
1704 fe_generic_config_id,
1705 fe_id,
1706 fe_sw_gen_lookup_id,
1707 start_date,
1708 end_date,
1709 sw_start_proc,
1710 sw_exit_proc,
1711 creation_date,
1712 last_update_date,
1713 last_updated_by,
1714 created_by,
1715 last_update_login,
1716 security_group_id)
1717 Values (
1718 l_new_generic_config_Id,
1719 l_new_feId,
1720 l_fe_gen_config.fe_sw_gen_lookup_id,
1721 l_fe_gen_config.start_date,
1722 l_fe_gen_config.end_date,
1723 l_fe_gen_config.sw_start_proc,
1724 l_fe_gen_config.sw_exit_proc,
1725 sysdate,
1726 sysdate,
1727 p_CallerID,
1728 p_CallerID,
1729 p_CallerID,
1730 null
1731 );
1732 FOR l_fe_val in c_fe_attribute_val(l_fe_gen_config.fe_generic_config_id) LOOP
1733 XDP_FE_ATTRIBUTE_VAL_PKG.INSERT_ROW (
1734 l_rowid,
1735 l_fe_val.fe_attribute_id,
1736 l_new_generic_config_Id,
1737 l_fe_val.fe_attribute_value,
1738 l_fe_val.display_name,
1739 l_fe_val.description,
1740 SYSDATE,
1741 p_CallerID,
1742 SYSDATE,
1743 p_CallerID,
1744 p_callerID
1745 );
1746 END LOOP;
1747
1748 END LOOP;
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 rollback to CopyFE;
1752 x_retcode := SQLCODE;
1753 x_errbuf := sqlerrm;
1754 END Copy_FE;
1755
1756 begin
1757
1758 pv_AckTimeout := GetAckTimeOut;
1759 select instance_name into pv_InstanceName from v$instance;
1760
1761 end XDP_ADAPTER_CORE_DB;