[Home] [Help]
PACKAGE BODY: APPS.FND_SVC_COMP_REQUESTS_PKG
Source
1 package body FND_SVC_COMP_REQUESTS_PKG as
2 /* $Header: AFSVCRTB.pls 115.4 2003/01/17 22:20:38 ankung noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_COMPONENT_REQUEST_ID in NUMBER,
7 X_COMPONENT_ID in NUMBER,
8 X_EVENT_NAME in VARCHAR2,
9 X_EVENT_DATE in DATE,
10 X_REQUESTED_BY_USER in VARCHAR2,
11 X_JOB_ID in NUMBER,
12 X_EVENT_PARAMS in VARCHAR2,
13 X_EVENT_FREQUENCY in NUMBER,
14 X_OBJECT_VERSION_NUMBER in NUMBER,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from FND_SVC_COMP_REQUESTS
22 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID
23 ;
24 begin
25 insert into FND_SVC_COMP_REQUESTS (
26 COMPONENT_REQUEST_ID,
27 COMPONENT_ID,
28 EVENT_NAME,
29 EVENT_DATE,
30 REQUESTED_BY_USER,
31 JOB_ID,
32 EVENT_PARAMS,
33 EVENT_FREQUENCY,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN,
39 OBJECT_VERSION_NUMBER
40 ) values (
41 X_COMPONENT_REQUEST_ID,
42 X_COMPONENT_ID,
43 X_EVENT_NAME,
44 X_EVENT_DATE,
45 X_REQUESTED_BY_USER,
46 X_JOB_ID,
47 X_EVENT_PARAMS,
48 X_EVENT_FREQUENCY,
49 X_CREATION_DATE,
50 X_CREATED_BY,
51 X_LAST_UPDATE_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_LOGIN,
54 X_OBJECT_VERSION_NUMBER
55 );
56
57 open c;
58 fetch c into X_ROWID;
59 if (c%notfound) then
60 close c;
61 raise no_data_found;
62 end if;
63 close c;
64
65 exception
66 when others then
67 wf_core.context('FND_SVC_COMP_REQUESTS_PKG', 'Insert_Row', X_COMPONENT_ID, X_EVENT_NAME);
68 raise;
69
70 end INSERT_ROW;
71
72 procedure LOCK_ROW (
73 X_COMPONENT_REQUEST_ID in NUMBER,
74 X_COMPONENT_ID in NUMBER,
75 X_EVENT_NAME in VARCHAR2,
76 X_EVENT_DATE in DATE,
77 X_REQUESTED_BY_USER in VARCHAR2,
78 X_JOB_ID in NUMBER,
79 X_EVENT_PARAMS in VARCHAR2,
80 X_EVENT_FREQUENCY in NUMBER,
81 X_OBJECT_VERSION_NUMBER in NUMBER
82 ) is
83 cursor c is select
84 COMPONENT_ID,
85 EVENT_NAME,
86 EVENT_DATE,
87 REQUESTED_BY_USER,
88 JOB_ID,
89 EVENT_FREQUENCY,
90 OBJECT_VERSION_NUMBER,
91 EVENT_PARAMS
92 from FND_SVC_COMP_REQUESTS
93 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID
94 for update of COMPONENT_REQUEST_ID nowait;
95
96 recinfo c%rowtype;
97 begin
98
99 open c;
100 fetch c into recinfo;
101 if (c%notfound) then
102 close c;
103 wf_core.raise('WF_RECORD_DELETED');
104 end if;
105 close c;
106
107 if ( ((recinfo.EVENT_PARAMS = X_EVENT_PARAMS)
108 OR ((recinfo.EVENT_PARAMS is null) AND (X_EVENT_PARAMS is null)))
109 AND (recinfo.COMPONENT_ID = X_COMPONENT_ID)
110 AND (recinfo.EVENT_NAME = X_EVENT_NAME)
111 AND (recinfo.EVENT_DATE = X_EVENT_DATE)
112 AND (recinfo.REQUESTED_BY_USER = X_REQUESTED_BY_USER)
113 AND (recinfo.JOB_ID = X_JOB_ID)
114 AND ((recinfo.EVENT_FREQUENCY = X_EVENT_FREQUENCY)
115 OR ((recinfo.EVENT_FREQUENCY is null) AND (X_EVENT_FREQUENCY is null)))
116 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
117 ) then
118 null;
119 else
120 wf_core.raise('WF_RECORD_CHANGED');
121 end if;
122
123 return;
124
125 exception
126 when others then
127 wf_core.context('FND_SVC_COMP_REQUESTS_PKG', 'Lock_Row', X_COMPONENT_REQUEST_ID);
128 raise;
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_COMPONENT_REQUEST_ID in NUMBER,
133 X_COMPONENT_ID in NUMBER,
134 X_EVENT_NAME in VARCHAR2,
135 X_EVENT_DATE in DATE,
136 X_REQUESTED_BY_USER in VARCHAR2,
137 X_JOB_ID in NUMBER,
138 X_EVENT_PARAMS in VARCHAR2,
139 X_EVENT_FREQUENCY in NUMBER,
140 X_OBJECT_VERSION_NUMBER in NUMBER,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145
146 l_object_version_number NUMBER;
147 begin
148
149 --
150 -- Perform OVN checks
151 --
152 if X_OBJECT_VERSION_NUMBER = -1 then
153
154 --
155 -- Allow update. Increment the database's OVN by 1
156 --
157 select OBJECT_VERSION_NUMBER
158 into l_object_version_number
159 from FND_SVC_COMP_REQUESTS
160 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID;
161
162 l_object_version_number := l_object_version_number + 1;
163
164 else
165
166 --
167 -- Lock the row. Allow update only if the database's OVN equals the one
168 -- passed in.
169 --
170 -- If update is allowed, increment the database's OVN by 1.
171 -- Otherwise, raise an error.
172 --
173
174 select OBJECT_VERSION_NUMBER
175 into l_object_version_number
176 from FND_SVC_COMP_REQUESTS
177 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID
178 for update;
179
180 if (l_object_version_number = X_OBJECT_VERSION_NUMBER) then
181
182 l_object_version_number := l_object_version_number + 1;
183 else
184
185 raise_application_error(-20002,
186 wf_core.translate('SVC_RECORD_ALREADY_UPDATED'));
187
188 end if;
189
190 end if;
191
192 update FND_SVC_COMP_REQUESTS set
193 COMPONENT_ID = X_COMPONENT_ID,
194 EVENT_NAME = X_EVENT_NAME,
195 EVENT_DATE = X_EVENT_DATE,
196 REQUESTED_BY_USER = X_REQUESTED_BY_USER,
197 JOB_ID = X_JOB_ID,
198 EVENT_PARAMS = X_EVENT_PARAMS,
199 EVENT_FREQUENCY = X_EVENT_FREQUENCY,
200 OBJECT_VERSION_NUMBER = l_object_version_number,
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
204 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 exception
211 when others then
212 wf_core.context('FND_SVC_COMP_REQUESTS_PKG', 'Update_Row', X_COMPONENT_REQUEST_ID);
213 raise;
214 end UPDATE_ROW;
215
216 procedure DELETE_ROW (
217 X_COMPONENT_REQUEST_ID in NUMBER
218 ) is
219 begin
220 delete from FND_SVC_COMP_REQUESTS
221 where COMPONENT_REQUEST_ID = X_COMPONENT_REQUEST_ID;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226
227 exception
228 when others then
229 wf_core.context('FND_SVC_COMP_REQUESTS_PKG', 'Delete_Row', X_COMPONENT_REQUEST_ID);
230 raise;
231 end DELETE_ROW;
232
233
234 procedure LOAD_ROW (
235 X_COMPONENT_NAME in VARCHAR2,
236 X_EVENT_NAME in VARCHAR2,
237 X_EVENT_DATE in DATE,
238 X_REQUESTED_BY_USER in VARCHAR2,
239 X_EVENT_PARAMS in VARCHAR2,
240 X_EVENT_FREQUENCY in NUMBER,
241 X_OBJECT_VERSION_NUMBER in NUMBER,
242 X_OWNER in VARCHAR2
243 )
244 IS
245
246 begin
247 declare
248 user_id number := 0;
249 row_id varchar2(64);
250
251 l_component_request_id number;
252 l_component_id number;
253 l_job_id number;
254 l_interval varchar2(1996);
255
256 begin
257
258 if (X_OWNER = 'ORACLE') then
259 user_id := 1;
260 end if;
261
262 SELECT component_id
263 INTO l_component_id
264 FROM fnd_svc_components
265 WHERE component_name = X_COMPONENT_NAME;
266
267
268 IF X_EVENT_FREQUENCY IS NOT NULL THEN
269 l_interval := 'SYSDATE + (' || TO_CHAR(X_EVENT_FREQUENCY) || '/(24*60))';
270 ELSE
271 l_interval := null;
272 END IF;
273
274 BEGIN
275
276 --
277 -- NOTE: We don't expect users to ever define two events with exactly
278 -- the same name, date, and frequency. However, just in case they do,
279 -- we're only selecting the first one we find here.
280 --
281 SELECT component_request_id, job_id
282 INTO l_component_request_id, l_job_id
283 FROM fnd_svc_comp_requests
284 WHERE component_id = l_component_id
285 AND rownum = 1
286 AND event_name = X_EVENT_NAME
287 AND event_date = X_EVENT_DATE
288 AND ( (event_frequency is NULL AND X_EVENT_FREQUENCY IS NULL)
289 OR (event_frequency = X_EVENT_FREQUENCY));
290
291
292 FND_SVC_COMP_REQUESTS_PKG.UPDATE_ROW (
293 X_COMPONENT_REQUEST_ID => l_component_request_id,
294 X_COMPONENT_ID => l_component_id,
295 X_EVENT_NAME => X_EVENT_NAME,
296 X_EVENT_DATE => X_EVENT_DATE,
297 X_REQUESTED_BY_USER => X_REQUESTED_BY_USER,
298 X_JOB_ID => l_job_id,
299 X_EVENT_PARAMS => X_EVENT_PARAMS,
300 X_EVENT_FREQUENCY => X_EVENT_FREQUENCY,
301 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
302 X_LAST_UPDATE_DATE => sysdate,
303 X_LAST_UPDATED_BY => user_id,
304 X_LAST_UPDATE_LOGIN => 0);
305
306 DBMS_JOB.CHANGE
307 (
308 job => l_job_id
309 , what => null
310 , next_date => X_EVENT_DATE
311 , interval => l_interval
312 );
313
314 EXCEPTION
315 WHEN No_Data_Found THEN
316
317 SELECT fnd_svc_comp_requests_s.nextval
318 INTO l_component_request_id
319 FROM dual;
320
321 DBMS_JOB.SUBMIT
322 (
323 job => l_job_id
324 , what => 'FND_SVC_COMPONENT.EXECUTE_REQUEST (p_component_request_id => ' || TO_CHAR(l_component_request_id) || ');'
325 , next_date => X_EVENT_DATE
326 , interval => l_interval
327 );
328
329 FND_SVC_COMP_REQUESTS_PKG.INSERT_ROW (
330 X_ROWID => row_id,
331 X_COMPONENT_REQUEST_ID => l_component_request_id,
332 X_COMPONENT_ID => l_component_id,
333 X_EVENT_NAME => X_EVENT_NAME,
334 X_EVENT_DATE => X_EVENT_DATE,
335 X_REQUESTED_BY_USER => X_REQUESTED_BY_USER,
336 X_JOB_ID => l_job_id,
337 X_EVENT_PARAMS => X_EVENT_PARAMS,
338 X_EVENT_FREQUENCY => X_EVENT_FREQUENCY,
339 X_CREATED_BY => user_id,
340 X_LAST_UPDATED_BY => user_id,
341 X_LAST_UPDATE_LOGIN => 0);
342 END;
343 END;
344
345 end LOAD_ROW;
346
347
348 end FND_SVC_COMP_REQUESTS_PKG;