[Home] [Help]
PACKAGE BODY: APPS.FND_SVC_COMP_PARAM_VALS_PKG
Source
1 package body FND_SVC_COMP_PARAM_VALS_PKG as
2 /* $Header: AFSVCVTB.pls 115.6 2002/12/27 20:33:49 ankung noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_COMPONENT_PARAMETER_ID in NUMBER,
7 X_COMPONENT_ID in NUMBER,
8 X_PARAMETER_ID in NUMBER,
9 X_PARAMETER_VALUE in VARCHAR2,
10 X_CUSTOMIZATION_LEVEL in VARCHAR2,
11 X_OBJECT_VERSION_NUMBER in NUMBER,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from FND_SVC_COMP_PARAM_VALS
19 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID
20 ;
21 begin
22 insert into FND_SVC_COMP_PARAM_VALS (
23 COMPONENT_PARAMETER_ID,
24 COMPONENT_ID,
25 PARAMETER_ID,
26 PARAMETER_VALUE,
27 CUSTOMIZATION_LEVEL,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN,
33 OBJECT_VERSION_NUMBER
34 ) values
35 (
36 X_COMPONENT_PARAMETER_ID,
37 X_COMPONENT_ID,
38 X_PARAMETER_ID,
39 X_PARAMETER_VALUE,
40 X_CUSTOMIZATION_LEVEL,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN,
46 X_OBJECT_VERSION_NUMBER
47 );
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 exception
58 when others then
59 wf_core.context('FND_SVC_COMP_PARAM_VALS_PKG', 'Insert_Row', X_COMPONENT_PARAMETER_ID);
60 raise;
61 end INSERT_ROW;
62
63
64 procedure LOCK_ROW (
65 X_COMPONENT_PARAMETER_ID in NUMBER,
66 X_COMPONENT_ID in NUMBER,
67 X_PARAMETER_ID in NUMBER,
68 X_PARAMETER_VALUE in VARCHAR2,
69 X_CUSTOMIZATION_LEVEL in VARCHAR2,
70 X_OBJECT_VERSION_NUMBER in NUMBER
71 ) is
72 cursor c is select
73 COMPONENT_ID,
74 PARAMETER_ID,
75 PARAMETER_VALUE,
76 CUSTOMIZATION_LEVEL,
77 OBJECT_VERSION_NUMBER
78 from FND_SVC_COMP_PARAM_VALS
79 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID
80 for update of COMPONENT_PARAMETER_ID nowait;
81
82 recinfo c%rowtype;
83 begin
84 open c;
85 fetch c into recinfo;
86 if (c%notfound) then
87 close c;
88 wf_core.raise('WF_RECORD_DELETED');
89 end if;
90 close c;
91
92 if ( ((recinfo.PARAMETER_VALUE = X_PARAMETER_VALUE)
93 OR ((recinfo.PARAMETER_VALUE is null) AND (X_PARAMETER_VALUE is null)))
94 AND (recinfo.COMPONENT_ID = X_COMPONENT_ID)
95 AND (recinfo.PARAMETER_ID = X_PARAMETER_ID)
96 AND (recinfo.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL)
97 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
98 ) then
99 null;
100 else
101 wf_core.raise('WF_RECORD_CHANGED');
102 end if;
103
104 return;
105
106 exception
107 when others then
108 wf_core.context('FND_SVC_COMP_PARAM_VALS_PKG', 'Lock_Row', X_COMPONENT_PARAMETER_ID);
109 raise;
110 end LOCK_ROW;
111
112 procedure UPDATE_ROW (
113 X_COMPONENT_PARAMETER_ID in NUMBER,
114 X_COMPONENT_ID in NUMBER,
115 X_PARAMETER_ID in NUMBER,
116 X_PARAMETER_VALUE in VARCHAR2,
117 X_CUSTOMIZATION_LEVEL in VARCHAR2,
118 X_OBJECT_VERSION_NUMBER in NUMBER,
119 X_LAST_UPDATE_DATE in DATE,
120 X_LAST_UPDATED_BY in NUMBER,
121 X_LAST_UPDATE_LOGIN in NUMBER
122 ) is
123
124 l_object_version_number NUMBER;
125 begin
126
127 --
128 -- Perform OVN checks
129 --
130 if X_OBJECT_VERSION_NUMBER = -1 then
131
132 --
133 -- Allow update. Increment the database's OVN by 1
134 --
135 select OBJECT_VERSION_NUMBER
136 into l_object_version_number
137 from FND_SVC_COMP_PARAM_VALS
138 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
139
140 l_object_version_number := l_object_version_number + 1;
141
142 else
143
144 --
145 -- Lock the row. Allow update only if the database's OVN equals the one
146 -- passed in.
147 --
148 -- If update is allowed, increment the database's OVN by 1.
149 -- Otherwise, raise an error.
150 --
151
152 select OBJECT_VERSION_NUMBER
153 into l_object_version_number
154 from FND_SVC_COMP_PARAM_VALS
155 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID
156 for update;
157
158 if (l_object_version_number = X_OBJECT_VERSION_NUMBER) then
159
160 l_object_version_number := l_object_version_number + 1;
161 else
162
163 raise_application_error(-20002,
164 wf_core.translate('SVC_RECORD_ALREADY_UPDATED'));
165
166 end if;
167
168 end if;
169
170 --
171 -- If CORE customization level
172 --
173 if X_CUSTOMIZATION_LEVEL = 'C' then
174
175 --
176 -- If loader is calling this:
177 -- It can update everything
178 --
179 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
180
181 update FND_SVC_COMP_PARAM_VALS set
182 COMPONENT_ID = X_COMPONENT_ID,
183 PARAMETER_ID = X_PARAMETER_ID,
184 PARAMETER_VALUE = X_PARAMETER_VALUE,
185 CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
186 OBJECT_VERSION_NUMBER = l_object_version_number,
187 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
190 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195
196 --
197 -- If user is calling this:
198 -- It can NOT update anything
199 --
200 else
201 null;
202 end if;
203
204 --
205 -- If LIMIT customization level
206 --
207 elsif X_CUSTOMIZATION_LEVEL = 'L' then
208
209 --
210 -- If loader is calling this
211 -- It can update everything EXCEPT
212 -- > parameter_value
213 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
214
215 update FND_SVC_COMP_PARAM_VALS set
216 COMPONENT_ID = X_COMPONENT_ID,
217 PARAMETER_ID = X_PARAMETER_ID,
218 -- PARAMETER_VALUE = X_PARAMETER_VALUE, // limit column
219 CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
220 OBJECT_VERSION_NUMBER = l_object_version_number,
221 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
222 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
223 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
224 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 --
231 -- If user is calling this:
232 -- It can update ONLY
233 -- > startup_mode
234 -- > max_idle_time
235 else
236
237 update FND_SVC_COMP_PARAM_VALS set
238 PARAMETER_VALUE = X_PARAMETER_VALUE,
239 OBJECT_VERSION_NUMBER = l_object_version_number,
240 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
241 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
242 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
243 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
244
245 if (sql%notfound) then
246 raise no_data_found;
247 end if;
248
249 end if;
250
251 --
252 -- If USER customization level
253 --
254 elsif X_CUSTOMIZATION_LEVEL = 'U' then
255 --
256 -- If loader is calling this
257 -- It can NOT update anything
258 --
259 if WF_EVENTS_PKG.g_Mode = 'UPGRADE' then
260 null;
261
262 --
263 -- If user is calling this:
264 -- It can update everything
265 --
266 else
267
268 update FND_SVC_COMP_PARAM_VALS set
269 COMPONENT_ID = X_COMPONENT_ID,
270 PARAMETER_ID = X_PARAMETER_ID,
271 PARAMETER_VALUE = X_PARAMETER_VALUE,
272 CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
273 OBJECT_VERSION_NUMBER = l_object_version_number,
274 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
277 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282 end if;
283 end if;
284
285 exception
286 when others then
287 wf_core.context('FND_SVC_COMP_PARAM_VALS_PKG', 'Update_Row', X_COMPONENT_PARAMETER_ID);
288 raise;
289 end UPDATE_ROW;
290
291 procedure DELETE_ROW (
292 X_COMPONENT_PARAMETER_ID in NUMBER
293 ) is
294 l_customization_level varchar2(1);
295 begin
296
297 select CUSTOMIZATION_LEVEL
298 into l_customization_level
299 from FND_SVC_COMP_PARAM_VALS
300 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
301
302 if l_customization_level = 'U' then
303
304 delete from FND_SVC_COMP_PARAM_VALS
305 where COMPONENT_PARAMETER_ID = X_COMPONENT_PARAMETER_ID;
306
307 if (sql%notfound) then
308 raise no_data_found;
309 end if;
310 end if;
311 exception
312 when others then
313 wf_core.context('FND_SVC_COMP_PARAM_VALS_PKG', 'Delete_Row', X_COMPONENT_PARAMETER_ID);
314 raise;
315
319 X_COMPONENT_NAME in VARCHAR2,
316 end DELETE_ROW;
317
318 procedure LOAD_ROW (
320 X_PARAMETER_NAME in VARCHAR2,
321 X_PARAMETER_VALUE in VARCHAR2,
322 X_CUSTOMIZATION_LEVEL in VARCHAR2,
326 IS
323 X_OBJECT_VERSION_NUMBER in NUMBER,
324 X_OWNER in VARCHAR2
325 )
327
328 begin
329
330 declare
331 user_id number := 0;
332 row_id varchar2(64);
333
334 l_component_parameter_id number;
335 l_parameter_id number;
336 l_component_id number;
337 l_component_type fnd_svc_components.component_type%TYPE;
338 begin
339
340 if (X_OWNER = 'ORACLE') then
341 user_id := 1;
342 end if;
343
344 SELECT component_id, component_type
345 INTO l_component_id, l_component_type
346 FROM fnd_svc_components
347 WHERE component_name = X_COMPONENT_NAME;
348
349 SELECT parameter_id
350 INTO l_parameter_id
351 FROM fnd_svc_comp_params_b
352 WHERE parameter_name = X_PARAMETER_NAME
353 AND component_type = l_component_type;
354
355 BEGIN
356
357 SELECT component_parameter_id
358 INTO l_component_parameter_id
359 FROM fnd_svc_comp_param_vals
360 WHERE component_id = l_component_id
361 AND parameter_id = l_parameter_id;
362
363 FND_SVC_COMP_PARAM_VALS_PKG.UPDATE_ROW (
364 X_COMPONENT_PARAMETER_ID => l_component_parameter_id,
365 X_COMPONENT_ID => l_component_id,
366 X_PARAMETER_ID => l_parameter_id,
367 X_PARAMETER_VALUE => X_PARAMETER_VALUE,
368 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
369 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
370 X_LAST_UPDATE_DATE => sysdate,
371 X_LAST_UPDATED_BY => user_id,
372 X_LAST_UPDATE_LOGIN => 0);
373
374 EXCEPTION
375 WHEN No_Data_Found THEN
376 SELECT fnd_svc_comp_param_vals_s.nextval
377 INTO l_component_parameter_id
378 FROM dual;
379
380 FND_SVC_COMP_PARAM_VALS_PKG.INSERT_ROW (
381 X_ROWID => row_id,
382 X_COMPONENT_PARAMETER_ID => l_component_parameter_id,
383 X_COMPONENT_ID => l_component_id,
384 X_PARAMETER_ID => l_parameter_id,
385 X_PARAMETER_VALUE => X_PARAMETER_VALUE,
386 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
387 X_CREATION_DATE => sysdate,
388 X_CREATED_BY => user_id,
389 X_LAST_UPDATE_DATE => sysdate,
390 X_LAST_UPDATED_BY => user_id,
391 X_LAST_UPDATE_LOGIN => 0);
392 END;
393 END;
394 end LOAD_ROW;
395
396
397 end FND_SVC_COMP_PARAM_VALS_PKG;