DBA Data[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;