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