[Home] [Help]
PACKAGE BODY: APPS.FND_SVC_COMP_PARAMS_PKG
Source
1 package body FND_SVC_COMP_PARAMS_PKG as
2 /* $Header: AFSVCPTB.pls 115.3 2002/12/27 20:39:50 ankung noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PARAMETER_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_PARAMETER_NAME in VARCHAR2,
8 X_COMPONENT_TYPE in VARCHAR2,
9 X_DEFAULT_PARAMETER_VALUE in VARCHAR2,
10 X_REQUIRED_FLAG in VARCHAR2,
11 X_ALLOW_RELOAD_FLAG in VARCHAR2,
12 X_ENCRYPTED_FLAG in VARCHAR2,
13 X_CUSTOMIZATION_LEVEL in VARCHAR2,
14 X_DISPLAY_NAME in VARCHAR2,
15 X_DESCRIPTION in VARCHAR2,
16 X_CREATION_DATE in DATE,
17 X_CREATED_BY in NUMBER,
18 X_LAST_UPDATE_DATE in DATE,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select ROWID from FND_SVC_COMP_PARAMS_B
23 where PARAMETER_ID = X_PARAMETER_ID
24 ;
25 begin
26 insert into FND_SVC_COMP_PARAMS_B (
27 OBJECT_VERSION_NUMBER,
28 PARAMETER_ID,
29 PARAMETER_NAME,
30 COMPONENT_TYPE,
31 DEFAULT_PARAMETER_VALUE,
32 REQUIRED_FLAG,
33 ALLOW_RELOAD_FLAG,
34 ENCRYPTED_FLAG,
35 CUSTOMIZATION_LEVEL,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 ) values (
42 X_OBJECT_VERSION_NUMBER,
43 X_PARAMETER_ID,
44 X_PARAMETER_NAME,
45 X_COMPONENT_TYPE,
46 X_DEFAULT_PARAMETER_VALUE,
47 X_REQUIRED_FLAG,
48 X_ALLOW_RELOAD_FLAG,
49 X_ENCRYPTED_FLAG,
50 X_CUSTOMIZATION_LEVEL,
51 X_CREATION_DATE,
52 X_CREATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATED_BY,
55 X_LAST_UPDATE_LOGIN
56 );
57
58 insert into FND_SVC_COMP_PARAMS_TL (
59 PARAMETER_ID,
60 DISPLAY_NAME,
61 DESCRIPTION,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN,
67 LANGUAGE,
68 SOURCE_LANG
69 ) select
70 X_PARAMETER_ID,
71 X_DISPLAY_NAME,
72 X_DESCRIPTION,
73 X_CREATION_DATE,
74 X_CREATED_BY,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATED_BY,
77 X_LAST_UPDATE_LOGIN,
78 L.CODE,
79 userenv('LANG')
80 from WF_LANGUAGES L
81 where L.INSTALLED_FLAG = 'Y'
82 and not exists
83 (select NULL
84 from FND_SVC_COMP_PARAMS_TL T
85 where T.PARAMETER_ID = X_PARAMETER_ID
86 and T.LANGUAGE = L.CODE);
87
88 open c;
89 fetch c into X_ROWID;
90 if (c%notfound) then
91 close c;
92 raise no_data_found;
93 end if;
94 close c;
95
96 exception
97 when others then
98 wf_core.context('FND_SVC_COMP_PARAMS_PKG', 'Insert_Row', X_PARAMETER_ID, X_PARAMETER_NAME);
99 raise;
100 end INSERT_ROW;
101
102 procedure LOCK_ROW (
103 X_PARAMETER_ID in NUMBER,
104 X_OBJECT_VERSION_NUMBER in NUMBER,
105 X_PARAMETER_NAME in VARCHAR2,
106 X_COMPONENT_TYPE in VARCHAR2,
107 X_DEFAULT_PARAMETER_VALUE in VARCHAR2,
108 X_REQUIRED_FLAG in VARCHAR2,
109 X_ALLOW_RELOAD_FLAG in VARCHAR2,
110 X_ENCRYPTED_FLAG in VARCHAR2,
111 X_CUSTOMIZATION_LEVEL in VARCHAR2,
112 X_DISPLAY_NAME in VARCHAR2,
113 X_DESCRIPTION in VARCHAR2
114 ) is
115 cursor c is select
116 OBJECT_VERSION_NUMBER,
117 PARAMETER_NAME,
118 COMPONENT_TYPE,
119 DEFAULT_PARAMETER_VALUE,
120 REQUIRED_FLAG,
121 ALLOW_RELOAD_FLAG,
122 ENCRYPTED_FLAG,
123 CUSTOMIZATION_LEVEL
124 from FND_SVC_COMP_PARAMS_B
125 where PARAMETER_ID = X_PARAMETER_ID
126 for update of PARAMETER_ID nowait;
127 recinfo c%rowtype;
128
129 cursor c1 is select
130 DISPLAY_NAME,
131 DESCRIPTION,
132 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
133 from FND_SVC_COMP_PARAMS_TL
134 where PARAMETER_ID = X_PARAMETER_ID
135 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
136 for update of PARAMETER_ID nowait;
137 begin
138 open c;
139 fetch c into recinfo;
140 if (c%notfound) then
141 close c;
142 wf_core.raise('WF_RECORD_DELETED');
143 end if;
144 close c;
145 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
146 AND (recinfo.PARAMETER_NAME = X_PARAMETER_NAME)
147 AND (recinfo.COMPONENT_TYPE = X_COMPONENT_TYPE)
148 AND ((recinfo.DEFAULT_PARAMETER_VALUE = X_DEFAULT_PARAMETER_VALUE)
149 OR ((recinfo.DEFAULT_PARAMETER_VALUE is null) AND (X_DEFAULT_PARAMETER_VALUE is null)))
150 AND (recinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
151 AND (recinfo.ALLOW_RELOAD_FLAG = X_ALLOW_RELOAD_FLAG)
152 AND (recinfo.ENCRYPTED_FLAG = X_ENCRYPTED_FLAG)
153 AND (recinfo.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL)
154 ) then
155 null;
156 else
157 wf_core.raise('WF_RECORD_CHANGED');
158 end if;
159
160 for tlinfo in c1 loop
161 if (tlinfo.BASELANG = 'Y') then
162 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
163 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
164 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
165 ) then
166 null;
167 else
168 wf_core.raise('WF_RECORD_CHANGED');
169 end if;
170 end if;
171 end loop;
172 return;
173
174 exception
175 when others then
176 wf_core.context('FND_SVC_COMP_PARAMS_PKG', 'Lock_Row', X_PARAMETER_ID, X_PARAMETER_NAME);
177 raise;
178 end LOCK_ROW;
179
180 procedure UPDATE_ROW (
181 X_PARAMETER_ID in NUMBER,
182 X_OBJECT_VERSION_NUMBER in NUMBER,
183 X_PARAMETER_NAME in VARCHAR2,
184 X_COMPONENT_TYPE in VARCHAR2,
185 X_DEFAULT_PARAMETER_VALUE in VARCHAR2,
186 X_REQUIRED_FLAG in VARCHAR2,
187 X_ALLOW_RELOAD_FLAG in VARCHAR2,
188 X_ENCRYPTED_FLAG in VARCHAR2,
189 X_CUSTOMIZATION_LEVEL in VARCHAR2,
190 X_DISPLAY_NAME in VARCHAR2,
191 X_DESCRIPTION in VARCHAR2,
192 X_LAST_UPDATE_DATE in DATE,
193 X_LAST_UPDATED_BY in NUMBER,
194 X_LAST_UPDATE_LOGIN in NUMBER
195 ) is
196
197 l_object_version_number NUMBER;
198 begin
199
200 --
201 -- Perform OVN checks
202 --
203 if X_OBJECT_VERSION_NUMBER = -1 then
204
205 --
206 -- Allow update. Increment the database's OVN by 1
207 --
208 select OBJECT_VERSION_NUMBER
209 into l_object_version_number
210 from FND_SVC_COMP_PARAMS_B
211 where PARAMETER_ID = X_PARAMETER_ID;
212
213 l_object_version_number := l_object_version_number + 1;
214
215 else
216
217 --
218 -- Lock the row. Allow update only if the database's OVN equals the one
219 -- passed in.
220 --
221 -- If update is allowed, increment the database's OVN by 1.
222 -- Otherwise, raise an error.
223 --
224
225 select OBJECT_VERSION_NUMBER
226 into l_object_version_number
227 from FND_SVC_COMP_PARAMS_B
228 where PARAMETER_ID = X_PARAMETER_ID
229 for update;
230
231 if (l_object_version_number = X_OBJECT_VERSION_NUMBER) then
232
233 l_object_version_number := l_object_version_number + 1;
234 else
235
236 raise_application_error(-20002,
237 wf_core.translate('SVC_RECORD_ALREADY_UPDATED'));
238
239 end if;
240
241 end if;
242
243 update FND_SVC_COMP_PARAMS_B set
244 OBJECT_VERSION_NUMBER = l_object_version_number,
245 PARAMETER_NAME = X_PARAMETER_NAME,
246 COMPONENT_TYPE = X_COMPONENT_TYPE,
247 DEFAULT_PARAMETER_VALUE = X_DEFAULT_PARAMETER_VALUE,
248 REQUIRED_FLAG = X_REQUIRED_FLAG,
249 ALLOW_RELOAD_FLAG = X_ALLOW_RELOAD_FLAG,
250 ENCRYPTED_FLAG = X_ENCRYPTED_FLAG,
251 CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
252 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
253 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
254 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
255 where PARAMETER_ID = X_PARAMETER_ID;
256
257 if (sql%notfound) then
258 raise no_data_found;
259 end if;
260
261 update FND_SVC_COMP_PARAMS_TL set
262 DISPLAY_NAME = X_DISPLAY_NAME,
263 DESCRIPTION = X_DESCRIPTION,
264 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
265 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
266 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
267 SOURCE_LANG = userenv('LANG')
268 where PARAMETER_ID = X_PARAMETER_ID
269 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
270
271 if (sql%notfound) then
272 raise no_data_found;
273 end if;
274
275 exception
276 when others then
277 wf_core.context('FND_SVC_COMP_PARAMS_PKG', 'Update_Row', X_PARAMETER_ID, X_PARAMETER_NAME);
278 raise;
279 end UPDATE_ROW;
280
281 procedure DELETE_ROW (
282 X_PARAMETER_ID in NUMBER
283 ) is
284 begin
285 delete from FND_SVC_COMP_PARAMS_TL
286 where PARAMETER_ID = X_PARAMETER_ID;
287
288 if (sql%notfound) then
289 raise no_data_found;
290 end if;
291
292 delete from FND_SVC_COMP_PARAMS_B
293 where PARAMETER_ID = X_PARAMETER_ID;
294
295 if (sql%notfound) then
296 raise no_data_found;
297 end if;
298
299 exception
300 when others then
301 wf_core.context('FND_SVC_COMP_PARAMS_PKG', 'Delete_Row', X_PARAMETER_ID);
302 raise;
303 end DELETE_ROW;
304
305 procedure ADD_LANGUAGE
306 is
307 begin
308 delete from FND_SVC_COMP_PARAMS_TL T
309 where not exists
310 (select NULL
311 from FND_SVC_COMP_PARAMS_B B
312 where B.PARAMETER_ID = T.PARAMETER_ID
313 );
314
318 ) = (select
315 update FND_SVC_COMP_PARAMS_TL T set (
316 DISPLAY_NAME,
317 DESCRIPTION
319 B.DISPLAY_NAME,
320 B.DESCRIPTION
321 from FND_SVC_COMP_PARAMS_TL B
322 where B.PARAMETER_ID = T.PARAMETER_ID
323 and B.LANGUAGE = T.SOURCE_LANG)
324 where (
325 T.PARAMETER_ID,
326 T.LANGUAGE
327 ) in (select
328 SUBT.PARAMETER_ID,
329 SUBT.LANGUAGE
330 from FND_SVC_COMP_PARAMS_TL SUBB, FND_SVC_COMP_PARAMS_TL SUBT
331 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
332 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
333 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
334 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
335 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
336 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
337 ));
338
339 insert into FND_SVC_COMP_PARAMS_TL (
340 PARAMETER_ID,
341 DISPLAY_NAME,
342 DESCRIPTION,
343 CREATION_DATE,
344 CREATED_BY,
345 LAST_UPDATE_DATE,
346 LAST_UPDATED_BY,
347 LAST_UPDATE_LOGIN,
348 LANGUAGE,
349 SOURCE_LANG
350 ) select
351 B.PARAMETER_ID,
352 B.DISPLAY_NAME,
353 B.DESCRIPTION,
354 B.CREATION_DATE,
355 B.CREATED_BY,
356 B.LAST_UPDATE_DATE,
357 B.LAST_UPDATED_BY,
358 B.LAST_UPDATE_LOGIN,
359 L.CODE,
360 B.SOURCE_LANG
361 from FND_SVC_COMP_PARAMS_TL B, WF_LANGUAGES L
362 where L.INSTALLED_FLAG = 'Y'
363 and B.LANGUAGE = userenv('LANG')
364 and not exists
365 (select NULL
366 from FND_SVC_COMP_PARAMS_TL T
367 where T.PARAMETER_ID = B.PARAMETER_ID
368 and T.LANGUAGE = L.CODE);
369 end ADD_LANGUAGE;
370
371
372 procedure LOAD_ROW (
373 X_PARAMETER_NAME in VARCHAR2,
374 X_COMPONENT_TYPE in VARCHAR2,
375 X_DEFAULT_PARAMETER_VALUE in VARCHAR2,
376 X_REQUIRED_FLAG in VARCHAR2,
377 X_ALLOW_RELOAD_FLAG in VARCHAR2,
378 X_ENCRYPTED_FLAG in VARCHAR2,
379 X_CUSTOMIZATION_LEVEL in VARCHAR2,
380 X_DISPLAY_NAME in VARCHAR2,
381 X_DESCRIPTION in VARCHAR2,
382 X_OBJECT_VERSION_NUMBER in NUMBER,
383 X_OWNER in VARCHAR2
384 ) IS
385
386 begin
387
388 declare
389 user_id number := 0;
390 row_id varchar2(64);
391
392 l_parameter_id NUMBER;
393 begin
394
395 if (X_OWNER = 'ORACLE') then
396 user_id := 1;
397 end if;
398
399
400 BEGIN
401 SELECT parameter_id
402 INTO l_parameter_id
403 FROM fnd_svc_comp_params_b
404 WHERE component_type = x_component_type
405 AND parameter_name = x_parameter_name;
406
407 FND_SVC_COMP_PARAMS_PKG.UPDATE_ROW (
408 X_PARAMETER_ID => l_parameter_id,
409 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
410 X_PARAMETER_NAME => X_PARAMETER_NAME,
411 X_COMPONENT_TYPE => X_COMPONENT_TYPE,
412 X_DEFAULT_PARAMETER_VALUE => X_DEFAULT_PARAMETER_VALUE,
413 X_REQUIRED_FLAG => X_REQUIRED_FLAG,
414 X_ALLOW_RELOAD_FLAG => X_ALLOW_RELOAD_FLAG,
415 X_ENCRYPTED_FLAG => X_ENCRYPTED_FLAG,
416 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
417 X_DISPLAY_NAME => X_DISPLAY_NAME,
418 X_DESCRIPTION => X_DESCRIPTION,
419 X_LAST_UPDATE_DATE => sysdate,
420 X_LAST_UPDATED_BY => user_id,
421 X_LAST_UPDATE_LOGIN => 0);
422
423 EXCEPTION
424 WHEN No_Data_Found THEN
425 SELECT fnd_svc_comp_params_b_s.nextval
426 INTO l_parameter_id
427 FROM dual;
428
429
430 FND_SVC_COMP_PARAMS_PKG.INSERT_ROW (
431 X_ROWID => row_id,
432 X_PARAMETER_ID => l_parameter_id,
433 X_PARAMETER_NAME => X_PARAMETER_NAME,
434 X_COMPONENT_TYPE => X_COMPONENT_TYPE,
435 X_DEFAULT_PARAMETER_VALUE => X_DEFAULT_PARAMETER_VALUE,
436 X_REQUIRED_FLAG => X_REQUIRED_FLAG,
437 X_ALLOW_RELOAD_FLAG => X_ALLOW_RELOAD_FLAG,
438 X_ENCRYPTED_FLAG => X_ENCRYPTED_FLAG,
439 X_CUSTOMIZATION_LEVEL => X_CUSTOMIZATION_LEVEL,
440 X_DISPLAY_NAME => X_DISPLAY_NAME,
441 X_DESCRIPTION => X_DESCRIPTION,
442 X_CREATION_DATE => sysdate,
443 X_CREATED_BY => user_id,
444 X_LAST_UPDATE_DATE => sysdate,
445 X_LAST_UPDATED_BY => user_id,
446 X_LAST_UPDATE_LOGIN => 0);
447 END;
448 end;
449 end LOAD_ROW;
450
451 procedure TRANSLATE_ROW (
452 X_PARAMETER_ID in NUMBER,
453 X_DISPLAY_NAME in VARCHAR2,
454 X_DESCRIPTION in VARCHAR2,
455 X_OWNER in VARCHAR2
456 ) IS
457 BEGIN
458
459 --
460 -- Only update rows that have not been altered by user
461 --
462 UPDATE FND_SVC_COMP_PARAMS_TL
463 SET display_name = X_DISPLAY_NAME,
464 description = X_DESCRIPTION,
465 source_lang = userenv('LANG'),
466 last_update_date = sysdate,
467 last_updated_by = decode(X_OWNER, 'ORACLE', 1, 0),
468 last_update_login = 0
469 WHERE parameter_id = X_PARAMETER_ID
470 AND userenv('LANG') in (language, source_lang);
471
472 end TRANSLATE_ROW;
473
474
475 end FND_SVC_COMP_PARAMS_PKG;