1 PACKAGE XDP_ADAPTER_CORE_DB AUTHID CURRENT_USER AS
2 /* $Header: XDPACODS.pls 120.1 2005/06/08 23:33:33 appldev $ */
3
4 e_SendPipedMsgException exception;
5 e_ReceivePipedMsgException exception;
6 e_LockException exception;
7 e_LockReleaseException exception;
8
9 pv_JreCommand varchar2(10) := 'jre';
10
11 pv_InboundChannelName varchar2(40) := 'XNP_IN_MSG_Q';
15 -- Very small timeout, please donot change
12 pv_OutboundChannelName varchar2(40) := 'XNP_OUT_MSG_Q';
13
14 pv_AckTimeout number := 60;
16 pv_LockTimeout number := 1;
17 pv_InstanceName varchar2(40);
18
19 cursor G_Get_Running_Adapters (SvcInstID number) is
20 select CHANNEL_NAME, PROCESS_ID, ADAPTER_STATUS,
21 decode(ADAPTER_CLASS, 'NONE', 'N', 'Y') IS_IMPLEMENTED,
22 STATUS_ACTIVE_TIME
23 from XDP_ADAPTER_REG a, XDP_ADAPTER_TYPES_B b
24 where ADAPTER_STATUS not in (XDP_ADAPTER.pv_statusStopped,
25 XDP_ADAPTER.pv_statusStoppedError,
26 XDP_ADAPTER.pv_statusTerminated,
27 XDP_ADAPTER.pv_statusNotAvailable,
28 XDP_ADAPTER.pv_statusDeactivated,
29 XDP_ADAPTER.pv_statusDeactivatedSystem)
30 and a.adapter_type = b.adapter_type
31 and service_instance_id = SvcInstId;
32
33 cursor G_Get_Controller_Instances is
34 select distinct a.service_instance_id, b.CONCURRENT_QUEUE_NAME,
35 decode(b.USER_CONCURRENT_QUEUE_NAME,
36 null, b.CONCURRENT_QUEUE_NAME,
37 b.USER_CONCURRENT_QUEUE_NAME) USER_CONCURRENT_QUEUE_NAME
38 from XDP_ADAPTER_REG a, FND_CONCURRENT_QUEUES_VL b
39 where a.service_instance_id = b.CONCURRENT_QUEUE_ID and
40 b.APPLICATION_ID = XDP_ADAPTER.pv_AppID;
41
42 -- DWI - Disconnect/Stop when idle
43 cursor G_Get_DWI_Adapters (SvcInstID number) is
44 select CHANNEL_NAME, STARTUP_MODE, CONNECT_ON_DEMAND_FLAG
45 from XDP_ADAPTER_REG
46 where ADAPTER_STATUS = XDP_ADAPTER.pv_statusRunning and
47 ((STARTUP_MODE = XDP_ADAPTER.pv_startOnDemand) OR
48 ((CONNECT_ON_DEMAND_FLAG is not null) and (CONNECT_ON_DEMAND_FLAG = 'Y'))) and
49 ((MAX_IDLE_TIME_MINUTES is not null) and
50 ((STATUS_ACTIVE_TIME + (MAX_IDLE_TIME_MINUTES/(60*24))) < SYSDATE)) and
51 service_instance_id = SvcInstId;
52
53 cursor G_Get_All_Adapters is
54 select a.CHANNEL_NAME, a.PROCESS_ID, a.ADAPTER_STATUS, a.FE_ID,
55 a.ADAPTER_TYPE, f.ROLE_NAME, a.ADAPTER_DISPLAY_NAME
56 from XDP_ADAPTER_REG a, XDP_FES f
57 where a.fe_id = f.fe_id;
58
59 -- Gets all automatic (AUTO and SOD) adapters for a Controller instance that are/may
60 -- be required to be started. AUTO adapters are started whereas SOD adapters are checked if
61 -- they are required to be started.
62 -- Intentionally ignores DEACTIVATED adapters
63
64 cursor G_Get_Automatic_Adapters (SvcInstID number) is
65 select CHANNEL_NAME, STARTUP_MODE, XAR.FE_ID, XFE.FULFILLMENT_ELEMENT_NAME,
66 decode(ADAPTER_CLASS, 'NONE', 'N', 'Y') IS_IMPLEMENTED, APPLICATION_MODE
67 from XDP_ADAPTER_REG xar, XDP_ADAPTER_TYPES_B xat, XDP_FES xfe
68 where ADAPTER_STATUS in (XDP_ADAPTER.pv_statusStopped,
69 XDP_ADAPTER.pv_statusStoppedError,
70 XDP_ADAPTER.pv_statusTerminated)
71 and STARTUP_MODE in (XDP_ADAPTER.pv_startAutomatic, XDP_ADAPTER.pv_startOnDemand)
72 and xat.adapter_type = xar.adapter_type
73 and xar.fe_id = xfe.fe_id
74 and service_instance_id = SvcInstId;
75
76 -- Gets all automatic (AUTO and SOD) adapters having status DEACTIVATED_SYSTEM that are
77 -- required to be resetted when the Controller instance starts
78
79 cursor G_Get_SysDeactivated_Adapters (SvcInstID number) is
80 select CHANNEL_NAME, adapter_status
81 from XDP_ADAPTER_REG
82 where STARTUP_MODE in (XDP_ADAPTER.pv_startAutomatic, XDP_ADAPTER.pv_startOnDemand)
83 and service_instance_id = SvcInstId;
84
85 -- This creates an entry in the Adapter Registration table.
86 -- Once the adapter is created it can be started
87 Procedure LoadNewAdapter( p_ChannelName in varchar2,
88 p_FeID in number,
89 p_AdapterType in varchar2,
90 p_AdapterName in varchar2,
91 p_AdapterDispName in varchar2,
92 p_AdapterStatus in varchar2,
93 p_ConcQID in number,
94 p_StartupMode in varchar2 default 'MANUAL',
95 p_UsageCode in varchar2 default 'NORMAL',
96 p_LogLevel in varchar2 default 'ERROR',
97 p_CODFlag in varchar2 default 'N',
98 p_MaxIdleTime in number default 0,
99 p_LogFileName in varchar2 default NULL,
100 p_SeqInFE in number default null,
101 p_CmdLineOpts in varchar2 default NULL,
102 p_CmdLineArgs in varchar2 default NULL);
103
104 -- Allocate a new Control Channel for the Adapter.
105 -- Returns a new Channel Name
106 Procedure CreateNewAdapterChannel(p_FeName in varchar2, p_ChannelName OUT NOCOPY varchar2);
107
108 -- Fetch Adapter Information for a Particular Channel
109 -- This is used for Verifying and Terminating an adapter process
110 -- Returns the Process ID and the Controller Serivice Intance
111 -- which is currently servicing the adapter
112 Procedure FetchAdapterInfo(p_ChannelName in varchar2,
113 p_FEID OUT NOCOPY number,
114 p_ProcessID OUT NOCOPY number,
115 p_ConcQID OUT NOCOPY number);
116
117 -- Fetch all the informaion required to be passed to the Contoller
118 -- when starting the adapter
119 -- The informaion is obtained from the Adapter Registration and also
120 -- The Adapter Types table.
121 Procedure FetchAdapterStartupInfo(p_ChannelName in varchar2,
122 p_CmdOptions OUT NOCOPY varchar2,
123 p_CmdArgs OUT NOCOPY varchar2,
124 p_ControlChannelName OUT NOCOPY varchar2,
125 p_ApplChannelName OUT NOCOPY varchar2,
126 p_ApplMode OUT NOCOPY varchar2,
127 p_FeName OUT NOCOPY varchar2,
128 p_AdapterClass OUT NOCOPY varchar2,
129 p_AdapterName OUT NOCOPY varchar2,
130 p_ConcQID OUT NOCOPY number,
131 p_InboundChannelName OUT NOCOPY varchar2,
132 p_LogFileName OUT NOCOPY varchar2);
133
134 -- API to update the Adapter Information from the Adapter registration
135 Procedure UpdateAdapter( p_ChannelName in varchar2,
136 p_Status in varchar2 default null,
137 p_ProcessId in number default null,
138 p_UsageCode in varchar2 default null,
139 p_StartupMode in varchar2 default null,
140 p_AdapterName in varchar2 default null,
141 p_AdapterDispName in varchar2 default null,
142 p_SvcInstId in number default null,
143 p_WFItemType in varchar2 default null,
144 p_WFItemKey in varchar2 default null,
145 p_WFActivityName in varchar2 default null,
146 p_CODFlag in varchar2 default null,
147 p_MaxIdleTime in number default -1,
148 p_LastVerified in date default null,
149 p_CmdLineOpts in varchar2 default 'CmdLineOpts',
150 p_CmdLineArgs in varchar2 default 'CmdLineArgs',
151 p_LogLevel in varchar2 default null,
152 p_LogFileName in varchar2 default 'LogFileName',
153 p_SeqInFE in number default -1);
154
155 -- API to update the Adapter Active Time
156 Procedure Update_Adapter_Active_Time(p_ChannelName IN VARCHAR2);
157
158 -- Submits an Admin Request for an Adapter. A DBMS_JOB is also submitted.
159 Procedure SubmitAdapterAdminReq (p_ChannelName in varchar2,
160 p_RequestType in varchar2,
161 p_RequestDate in date default sysdate,
162 p_RequestedBy in varchar2,
163 p_Freq in number default null,
164 p_RequestID OUT NOCOPY number,
165 p_JobID OUT NOCOPY number);
166
167 -- Update an Admin Request
168 Procedure UpdateAdapterAdminReq(p_RequestID in number,
169 p_RequestDate in date default sysdate,
170 p_RequestedBy in varchar2,
171 p_Freq in number default null);
172
173 -- Remove Adapter Admin Requests
174 Procedure RemoveAdapterAdminReq (p_RequestID in number);
175
176 -- Fetch More Information regarding a particular Request
177 Procedure FetchAdapterAdminReqInfo (p_RequestID in number,
178 p_RequestType OUT NOCOPY varchar2,
179 p_RequestDate OUT NOCOPY date,
180 p_RequestedBy OUT NOCOPY varchar2,
181 p_Freq OUT NOCOPY number,
182 p_DBJobID OUT NOCOPY number,
183 p_ChannelName OUT NOCOPY varchar2);
184
185 -- Check if a System Generated Admin Request has already been submitted
186 Function DoesSystemReqAlreadyExist(p_ChannelName in varchar2,
187 p_RequestType in varchar2,
188 p_RequestDate in date) return number;
189
190 -- Get the Current Adapter Status
191 Function GetCurrentAdapterStatus(p_ChannelName in varchar2) return varchar2;
192
193 -- Try to obtain a lock on a particular Adapter Channel
194 -- The timeout specifies the duration for which you need to wait for the lock
195 -- 'Y' if successful else 'N'
196
197 --Used by Adapters
198 Function ObtainAdapterLock(p_ChannelName in varchar2,
199 p_Timeout in number default pv_LockTimeout) return varchar2;
200
201 --Used by FA processing logic
202 Function ObtainAdapterLock_FA(p_ChannelName in varchar2,
203 p_Timeout in number default pv_LockTimeout) return varchar2;
204
205 --Used by adapter verification logic
206 Function ObtainAdapterLock_Verify(p_ChannelName in varchar2,
207 p_Timeout in number default pv_LockTimeout) return varchar2;
208
209 -- Releases the lock on an Adapter Channel
210 -- 'Y' if successful else 'N'
211 Function ReleaseAdapterLock(p_ChannelName in varchar2) return varchar2;
212
213 -- Checks if the Channel on which the adapter is operating is Connect-On-Demand
214 -- enabled
215 Function IsChannelCOD(p_ChannelName in varchar2) return varchar2;
216
217 -- Check if there are any FA's waiting for a Channel to a Fulfillment Element
218 Function PeekIntoFeWaitQueue(p_ChannelName in varchar2) return varchar2;
219
220 -- ************* Added - sacsharm - START *****************************
221
222 -- Autonomous API to update the Adapter Status in the Adapter registration
223 Procedure Update_Adapter_Status (p_ChannelName in varchar2,
224 p_Status in varchar2,
225 p_ErrorMsg in varchar2 default null,
226 p_ErrorMsgParams in varchar2 default null,
227 p_WFItemType in varchar2 default null,
228 p_WFItemKey in varchar2 default null);
229
230 Function GetAckTimeOut return number;
231
232 Function GetLockTimeOut return number;
233
234 -- Check if any more adapters can be started for a FE
235 Function Is_Max_Connection_Reached (p_fe_id in number) return boolean;
236
237 -- Procedure to delete an Adapter
238 PROCEDURE Delete_Adapter (p_channel_name IN VARCHAR2);
239
240 -- Procedure to delete all Adapters for a FE
241 PROCEDURE Delete_Adapters_For_Fe (p_fe_id IN NUMBER);
242
243 Function Get_Fe_Id_For_name (p_FeName in varchar2) return number;
244
245 Function Get_Job_Id_For_Request (p_RequestId in number) return number;
246
247 PROCEDURE Audit_Adapter_Admin_Request (p_RequestID in number,
248 p_RequestType in varchar2,
249 p_RequestDate in date,
250 p_RequestedBy in varchar2,
251 p_Freq in number,
252 p_RequestStatus in varchar2,
253 p_RequestMessage in varchar2,
254 p_ChannelName in varchar2);
255
256 --Function Is_Adapter_Available (p_fe_id in NUMBER, p_AdapterType in VARCHAR2) return boolean;
257 Procedure Are_Adapter_Generics_Available (p_fe_id in NUMBER, p_AdapterType in VARCHAR2,
258 p_GenCountActive OUT NOCOPY NUMBER, p_GenCountFuture OUT NOCOPY NUMBER);
259
260 Function Is_Message_Adapter_Available(p_fe_name in varchar2) return VARCHAR2;
261
262 Function Is_Message_Adapter_Available(p_fe_id in number) return VARCHAR2;
263
264 Function Is_FE_Adapter_Running(p_fe_id in number) return BOOLEAN;
265
266 Function Is_FEType_Adapter_Running(p_fetype_id in number) return BOOLEAN;
267
268 -- Checks if the channel's adapter type is an implemented class
269 Function Is_Adapter_Implemented (p_ChannelName in varchar2) return boolean;
270
271 -- This autonomous function is called from FA WF and does not obtain or release
272 -- lock on the channel as the caller would have already obtained a
273 -- lock on the channel
274
275 Function Verify_Adapter (p_ChannelName IN varchar2) return boolean;
276
277 Function Is_Adapter_Automatic (p_ChannelName in varchar2) return boolean;
278
279 Function GetAdapterRestartCount return number;
280
281 -- ************* Added - sacsharm - END *****************************
282
283 -- ************* Added - mviswana - START ***************************
284
285 FUNCTION GetOAMFERolledStatus(p_fe_id IN NUMBER,
286 p_mode IN VARCHAR2) RETURN VARCHAR2;
287
288
289
290
291 FUNCTION GetOAMAdapterRunningCount(p_fe_id IN NUMBER,
292 p_mode IN VARCHAR2) return NUMBER;
293
294
295
296 FUNCTION GetNumOfJobsCount(p_fe_id IN NUMBER,
297 p_fe_name IN VARCHAR2,
298 p_mode IN VARCHAR2) return NUMBER;
299
300
301 -- ************* Added - mviswana - END *****************************
302
303
304 /********* Commented out - START - sacsharm ************************
305
306 -- Create a New Adaptr Type
307 -- This loads the adapter type information into the XDP_ADAPTER_TYPES_B table
308
309 Procedure LoadNewAdapterType ( p_AdapterType in varchar2,
310 p_AdapterClass in varchar2,
311 p_ApplicationMode in varchar2,
312 p_InboundReqFlag in varchar2 default 'N',
313 p_MaxBufSize in number default 2000,
314 p_CmdLineOpts in varchar2 default NULL,
315 p_CmdLineArgs in varchar2 default NULL);
316
317 -- Remove Adapter Admin DB Jobs alone
318 -- Used in application stop
319 Procedure RemoveAdapterAdminDBJobs(p_ChannelName in varchar2);
320
321 -- Re-submit adapter admin jobs. Used in application start
322 Procedure ResubmitAdapterAdminDBJob(p_Channelname in varchar2);
323
324
325 ********** Commented out - END - sacsharm ***********************/
326
327 --This function verifies whether the channel need to be locked or not..
328 --Approach to get the lock only if the adapter_type is PIPE..
329 Function checkLockRequired( p_Channelname in varchar2) return boolean;
330 -----------------------------------------------------------------------------
331 --
332 --
333 -- Start of comments
334 -- API name : Copy_FET_Attribute
335 -- Type : Group
336 -- Function : Copy attributes from an adapter type to FE Type/SW
337 -- Pre-reqs : None.
338 -- Version : : Current version 11.5.7
339 -- Notes :
340 -- This procedure will copy adapter type attribute to an FE type/SW combination
341 -- End of comments
342 --
343 --
344
345 Procedure Copy_FET_Attribute(
346 p_fe_sw_gen_lookup_id in NUMBER,
347 p_adapter_type IN VARCHAR2,
348 p_caller_id NUMBER,
349 x_retcode OUT NOCOPY NUMBER,
350 x_errbuf OUT NOCOPY VARCHAR2);
351
352 ------------------------------------------------------------------------
353 --
354 --
355 -- Start of comments
356 -- API name : Copy_FE
357 -- Type : Group
358 -- Function : Copy an exsiting fe to a new FE
359 -- Pre-reqs : None.
360 -- Version : : Current version 11.5.7
361 -- Notes :
362 -- This procedure will copy an existing fe, identified by p_feid
363 -- to a newly created FE identified by p_NewFeId with input internal
364 -- name. It copies all the fields of exsiting fe, including all
365 -- software generic config and attributes.
366 -- End of comments
367 --
368 --
369
370 Procedure Copy_FE(
371 p_FeName in varchar2,
372 p_FeDisplayName in varchar2,
373 p_FeID in varchar2,
374 p_NewFeID in NUMBER,
375 p_CallerID in NUMBER,
376 x_retcode OUT NOCOPY NUMBER,
377 x_errbuf OUT NOCOPY VARCHAR2);
378
379
380
381 END XDP_ADAPTER_CORE_DB;
[Home] [Help]