[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_SKILL_LEVELS_PKG
Source
1 package body JTF_RS_SKILL_LEVELS_PKG as
2 /* $Header: jtfrshsb.pls 120.0 2005/05/11 08:20:10 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SKILL_LEVEL_ID in NUMBER,
6 X_ATTRIBUTE15 in VARCHAR2,
7 X_ATTRIBUTE_CATEGORY in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_ATTRIBUTE4 in VARCHAR2,
10 X_ATTRIBUTE5 in VARCHAR2,
11 X_ATTRIBUTE9 in VARCHAR2,
12 X_ATTRIBUTE10 in VARCHAR2,
13 X_ATTRIBUTE11 in VARCHAR2,
14 X_SKILL_LEVEL in NUMBER,
15 X_ATTRIBUTE1 in VARCHAR2,
16 X_ATTRIBUTE2 in VARCHAR2,
17 X_ATTRIBUTE3 in VARCHAR2,
18 X_ATTRIBUTE12 in VARCHAR2,
19 X_ATTRIBUTE13 in VARCHAR2,
20 X_ATTRIBUTE14 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ATTRIBUTE7 in VARCHAR2,
23 X_ATTRIBUTE8 in VARCHAR2,
24 X_LEVEL_NAME in VARCHAR2,
25 X_LEVEL_DESC in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER
31 ) is
32 cursor C is select ROWID from JTF_RS_SKILL_LEVELS_B
33 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID
34 ;
35 begin
36 insert into JTF_RS_SKILL_LEVELS_B (
37 ATTRIBUTE15,
38 ATTRIBUTE_CATEGORY,
39 OBJECT_VERSION_NUMBER,
40 ATTRIBUTE4,
41 ATTRIBUTE5,
42 ATTRIBUTE9,
43 ATTRIBUTE10,
44 ATTRIBUTE11,
45 SKILL_LEVEL_ID,
46 SKILL_LEVEL,
47 ATTRIBUTE1,
48 ATTRIBUTE2,
49 ATTRIBUTE3,
50 ATTRIBUTE12,
51 ATTRIBUTE13,
52 ATTRIBUTE14,
53 ATTRIBUTE6,
54 ATTRIBUTE7,
55 ATTRIBUTE8,
56 CREATION_DATE,
57 CREATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_LOGIN
61 ) values (
62 X_ATTRIBUTE15,
63 X_ATTRIBUTE_CATEGORY,
64 X_OBJECT_VERSION_NUMBER,
65 X_ATTRIBUTE4,
66 X_ATTRIBUTE5,
67 X_ATTRIBUTE9,
68 X_ATTRIBUTE10,
69 X_ATTRIBUTE11,
70 X_SKILL_LEVEL_ID,
71 X_SKILL_LEVEL,
72 X_ATTRIBUTE1,
73 X_ATTRIBUTE2,
74 X_ATTRIBUTE3,
75 X_ATTRIBUTE12,
76 X_ATTRIBUTE13,
77 X_ATTRIBUTE14,
78 X_ATTRIBUTE6,
79 X_ATTRIBUTE7,
80 X_ATTRIBUTE8,
81 X_CREATION_DATE,
82 X_CREATED_BY,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_LAST_UPDATE_LOGIN
86 );
87
88 insert into JTF_RS_SKILL_LEVELS_TL (
89 CREATION_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_DATE,
92 LAST_UPDATE_LOGIN,
93 LEVEL_NAME,
94 LEVEL_DESC,
95 CREATED_BY,
96 SKILL_LEVEL_ID,
97 LANGUAGE,
98 SOURCE_LANG
99 ) select
100 X_CREATION_DATE,
101 X_LAST_UPDATED_BY,
102 X_LAST_UPDATE_DATE,
103 X_LAST_UPDATE_LOGIN,
104 X_LEVEL_NAME,
105 X_LEVEL_DESC,
106 X_CREATED_BY,
107 X_SKILL_LEVEL_ID,
108 L.LANGUAGE_CODE,
109 userenv('LANG')
110 from FND_LANGUAGES L
111 where L.INSTALLED_FLAG in ('I', 'B')
112 and not exists
113 (select NULL
114 from JTF_RS_SKILL_LEVELS_TL T
115 where T.SKILL_LEVEL_ID = X_SKILL_LEVEL_ID
116 and T.LANGUAGE = L.LANGUAGE_CODE);
117
118 open c;
119 fetch c into X_ROWID;
120 if (c%notfound) then
121 close c;
122 raise no_data_found;
123 end if;
124 close c;
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW (
129 X_SKILL_LEVEL_ID in NUMBER,
130 X_OBJECT_VERSION_NUMBER in NUMBER)
131 is
132 cursor c is select
133 OBJECT_VERSION_NUMBER
134 from JTF_RS_SKILL_LEVELS_B
135 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID
136 for update of SKILL_LEVEL_ID nowait;
137 recinfo c%rowtype;
138 begin
139 open c;
140 fetch c into recinfo;
141 if (c%notfound) then
142 close c;
143 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
144 app_exception.raise_exception;
145 end if;
146 close c;
147 if (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER) then
148 null;
149 else
150 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
151 app_exception.raise_exception;
152 end if;
153 return;
154 end LOCK_ROW;
155
156 procedure UPDATE_ROW (
157 X_SKILL_LEVEL_ID in NUMBER,
158 X_ATTRIBUTE15 in VARCHAR2,
159 X_ATTRIBUTE_CATEGORY in VARCHAR2,
160 X_OBJECT_VERSION_NUMBER in NUMBER,
161 X_ATTRIBUTE4 in VARCHAR2,
162 X_ATTRIBUTE5 in VARCHAR2,
163 X_ATTRIBUTE9 in VARCHAR2,
164 X_ATTRIBUTE10 in VARCHAR2,
165 X_ATTRIBUTE11 in VARCHAR2,
166 X_SKILL_LEVEL in NUMBER,
167 X_ATTRIBUTE1 in VARCHAR2,
168 X_ATTRIBUTE2 in VARCHAR2,
169 X_ATTRIBUTE3 in VARCHAR2,
170 X_ATTRIBUTE12 in VARCHAR2,
171 X_ATTRIBUTE13 in VARCHAR2,
172 X_ATTRIBUTE14 in VARCHAR2,
173 X_ATTRIBUTE6 in VARCHAR2,
174 X_ATTRIBUTE7 in VARCHAR2,
175 X_ATTRIBUTE8 in VARCHAR2,
176 X_LEVEL_NAME in VARCHAR2,
177 X_LEVEL_DESC in VARCHAR2,
178 X_LAST_UPDATE_DATE in DATE,
179 X_LAST_UPDATED_BY in NUMBER,
180 X_LAST_UPDATE_LOGIN in NUMBER
181 ) is
182 begin
183 update JTF_RS_SKILL_LEVELS_B set
184 ATTRIBUTE15 = X_ATTRIBUTE15,
185 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
186 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
187 ATTRIBUTE4 = X_ATTRIBUTE4,
188 ATTRIBUTE5 = X_ATTRIBUTE5,
189 ATTRIBUTE9 = X_ATTRIBUTE9,
190 ATTRIBUTE10 = X_ATTRIBUTE10,
191 ATTRIBUTE11 = X_ATTRIBUTE11,
192 SKILL_LEVEL = X_SKILL_LEVEL,
193 ATTRIBUTE1 = X_ATTRIBUTE1,
194 ATTRIBUTE2 = X_ATTRIBUTE2,
195 ATTRIBUTE3 = X_ATTRIBUTE3,
196 ATTRIBUTE12 = X_ATTRIBUTE12,
197 ATTRIBUTE13 = X_ATTRIBUTE13,
198 ATTRIBUTE14 = X_ATTRIBUTE14,
199 ATTRIBUTE6 = X_ATTRIBUTE6,
200 ATTRIBUTE7 = X_ATTRIBUTE7,
201 ATTRIBUTE8 = X_ATTRIBUTE8,
202 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
203 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
205 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID;
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210
211 update JTF_RS_SKILL_LEVELS_TL set
212 LEVEL_NAME = X_LEVEL_NAME,
213 LEVEL_DESC = X_LEVEL_DESC,
214 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
215 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
216 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
217 SOURCE_LANG = userenv('LANG')
218 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID
219 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224 end UPDATE_ROW;
225
226 procedure DELETE_ROW (
227 X_SKILL_LEVEL_ID in NUMBER
228 ) is
229 begin
230 delete from JTF_RS_SKILL_LEVELS_TL
231 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236
237 delete from JTF_RS_SKILL_LEVELS_B
238 where SKILL_LEVEL_ID = X_SKILL_LEVEL_ID;
239
240 if (sql%notfound) then
241 raise no_data_found;
242 end if;
243 end DELETE_ROW;
244
245 procedure ADD_LANGUAGE
246 is
247 begin
248 delete from JTF_RS_SKILL_LEVELS_TL T
249 where not exists
250 (select NULL
251 from JTF_RS_SKILL_LEVELS_B B
252 where B.SKILL_LEVEL_ID = T.SKILL_LEVEL_ID
253 );
254
255 update JTF_RS_SKILL_LEVELS_TL T set (
256 LEVEL_NAME,
257 LEVEL_DESC
258 ) = (select
259 B.LEVEL_NAME,
260 B.LEVEL_DESC
261 from JTF_RS_SKILL_LEVELS_TL B
262 where B.SKILL_LEVEL_ID = T.SKILL_LEVEL_ID
263 and B.LANGUAGE = T.SOURCE_LANG)
264 where (
265 T.SKILL_LEVEL_ID,
266 T.LANGUAGE
267 ) in (select
268 SUBT.SKILL_LEVEL_ID,
269 SUBT.LANGUAGE
270 from JTF_RS_SKILL_LEVELS_TL SUBB, JTF_RS_SKILL_LEVELS_TL SUBT
271 where SUBB.SKILL_LEVEL_ID = SUBT.SKILL_LEVEL_ID
272 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
273 and (SUBB.LEVEL_NAME <> SUBT.LEVEL_NAME
274 or SUBB.LEVEL_DESC <> SUBT.LEVEL_DESC
275 or (SUBB.LEVEL_DESC is null and SUBT.LEVEL_DESC is not null)
276 or (SUBB.LEVEL_DESC is not null and SUBT.LEVEL_DESC is null)
277 ));
278
279 insert into JTF_RS_SKILL_LEVELS_TL (
280 CREATION_DATE,
281 LAST_UPDATED_BY,
282 LAST_UPDATE_DATE,
283 LAST_UPDATE_LOGIN,
284 LEVEL_NAME,
285 LEVEL_DESC,
286 CREATED_BY,
287 SKILL_LEVEL_ID,
288 LANGUAGE,
289 SOURCE_LANG
290 ) select
291 B.CREATION_DATE,
292 B.LAST_UPDATED_BY,
293 B.LAST_UPDATE_DATE,
294 B.LAST_UPDATE_LOGIN,
295 B.LEVEL_NAME,
296 B.LEVEL_DESC,
297 B.CREATED_BY,
298 B.SKILL_LEVEL_ID,
299 L.LANGUAGE_CODE,
300 B.SOURCE_LANG
301 from JTF_RS_SKILL_LEVELS_TL B, FND_LANGUAGES L
302 where L.INSTALLED_FLAG in ('I', 'B')
303 and B.LANGUAGE = userenv('LANG')
304 and not exists
305 (select NULL
306 from JTF_RS_SKILL_LEVELS_TL T
307 where T.SKILL_LEVEL_ID = B.SKILL_LEVEL_ID
308 and T.LANGUAGE = L.LANGUAGE_CODE);
309 end ADD_LANGUAGE;
310
311 Procedure TRANSLATE_ROW
312 (x_skill_level_id in number,
313 x_level_name in varchar2,
314 x_level_desc in varchar2,
315 x_last_update_date in date,
316 x_last_updated_by in number,
317 x_last_update_login in number)
318 is
319 begin
320
321 update jtf_rs_skill_levels_tl set
322 level_name = nvl(x_level_name,level_name),
323 level_desc = nvl(x_level_desc,level_desc),
324 last_update_date = nvl(x_last_update_date,sysdate),
325 last_updated_by = x_last_updated_by,
326 last_update_login = 0,
327 source_lang = userenv('LANG')
328 where skill_level_id = x_skill_level_id
329 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
330
331 if (sql%notfound) then
332 raise no_data_found;
333 end if;
334 end TRANSLATE_ROW;
335
336 procedure LOAD_ROW (
337 X_ROWID in out NOCOPY VARCHAR2,
338 X_SKILL_LEVEL_ID in NUMBER,
339 X_SKILL_LEVEL in NUMBER,
340 X_LEVEL_NAME in VARCHAR2,
341 X_LEVEL_DESC in VARCHAR2,
342 X_ATTRIBUTE1 in VARCHAR2,
343 X_ATTRIBUTE2 in VARCHAR2,
344 X_ATTRIBUTE3 in VARCHAR2,
345 X_ATTRIBUTE4 in VARCHAR2,
346 X_ATTRIBUTE5 in VARCHAR2,
347 X_ATTRIBUTE6 in VARCHAR2,
348 X_ATTRIBUTE7 in VARCHAR2,
349 X_ATTRIBUTE8 in VARCHAR2,
350 X_ATTRIBUTE9 in VARCHAR2,
351 X_ATTRIBUTE10 in VARCHAR2,
352 X_ATTRIBUTE11 in VARCHAR2,
353 X_ATTRIBUTE12 in VARCHAR2,
354 X_ATTRIBUTE13 in VARCHAR2,
355 X_ATTRIBUTE14 in VARCHAR2,
356 X_ATTRIBUTE15 in VARCHAR2,
357 X_ATTRIBUTE_CATEGORY in VARCHAR2,
358 X_OBJECT_VERSION_NUMBER in NUMBER,
359 X_CREATION_DATE in DATE,
360 X_CREATED_BY in NUMBER,
361 X_LAST_UPDATE_DATE in DATE,
362 X_LAST_UPDATED_BY in NUMBER,
363 X_LAST_UPDATE_LOGIN in NUMBER,
364 X_OWNER in VARCHAR2
365 ) is
366 l_row_id rowid;
367 l_user_id number;
368 l_last_updated_by number := -1;
369
370 CURSOR c_last_updated IS
371 SELECT last_updated_by from JTF_RS_SKILL_LEVELS_VL
372 WHERE skill_level_id = X_SKILL_LEVEL_ID;
373
374 begin
375 if (X_OWNER = 'SEED') then
376 l_user_id := 1;
377 else
378 l_user_id := 0;
379 end if;
380
381 OPEN c_last_updated;
382 FETCH c_last_updated into l_last_updated_by;
383 IF c_last_updated%NOTFOUND THEN
384 JTF_RS_SKILL_LEVELS_PKG.INSERT_ROW(
385 X_ROWID => X_ROWID,
386 X_SKILL_LEVEL_ID => x_skill_level_id,
387 X_LEVEL_NAME => X_LEVEL_NAME,
388 X_LEVEL_DESC => X_LEVEL_DESC,
389 X_SKILL_LEVEL => X_SKILL_LEVEL,
390 X_ATTRIBUTE1 => X_ATTRIBUTE1,
391 X_ATTRIBUTE2 => X_ATTRIBUTE2,
392 X_ATTRIBUTE3 => X_ATTRIBUTE3,
393 X_ATTRIBUTE4 => X_ATTRIBUTE4,
394 X_ATTRIBUTE5 => X_ATTRIBUTE5,
395 X_ATTRIBUTE6 => X_ATTRIBUTE6,
396 X_ATTRIBUTE7 => X_ATTRIBUTE7,
397 X_ATTRIBUTE8 => X_ATTRIBUTE8,
398 X_ATTRIBUTE9 => X_ATTRIBUTE9,
399 X_ATTRIBUTE10 => X_ATTRIBUTE10,
400 X_ATTRIBUTE11 => X_ATTRIBUTE11,
401 X_ATTRIBUTE12 => X_ATTRIBUTE12,
402 X_ATTRIBUTE13 => X_ATTRIBUTE13,
403 X_ATTRIBUTE14 => X_ATTRIBUTE14,
404 X_ATTRIBUTE15 => X_ATTRIBUTE15,
405 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
406 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
407 X_CREATION_DATE => sysdate,
408 X_CREATED_BY => l_user_id,
409 X_LAST_UPDATE_DATE => sysdate,
410 X_LAST_UPDATED_BY => l_user_id,
411 X_LAST_UPDATE_LOGIN => 0);
412 ELSIF c_last_updated%FOUND THEN
413 IF l_last_updated_by = 1 THEN
414 JTF_RS_SKILL_LEVELS_PKG.UPDATE_ROW(
415 X_SKILL_LEVEL_ID => X_SKILL_LEVEL_ID,
416 X_SKILL_LEVEL => X_SKILL_LEVEL,
417 X_LEVEL_NAME => X_LEVEL_NAME,
418 X_LEVEL_DESC => X_LEVEL_DESC,
419 X_ATTRIBUTE1 => X_ATTRIBUTE1,
420 X_ATTRIBUTE2 => X_ATTRIBUTE2,
421 X_ATTRIBUTE3 => X_ATTRIBUTE3,
422 X_ATTRIBUTE4 => X_ATTRIBUTE4,
423 X_ATTRIBUTE5 => X_ATTRIBUTE5,
424 X_ATTRIBUTE6 => X_ATTRIBUTE6,
425 X_ATTRIBUTE7 => X_ATTRIBUTE7,
426 X_ATTRIBUTE8 => X_ATTRIBUTE8,
427 X_ATTRIBUTE9 => X_ATTRIBUTE9,
428 X_ATTRIBUTE10 => X_ATTRIBUTE10,
429 X_ATTRIBUTE11 => X_ATTRIBUTE11,
430 X_ATTRIBUTE12 => X_ATTRIBUTE12,
431 X_ATTRIBUTE13 => X_ATTRIBUTE13,
432 X_ATTRIBUTE14 => X_ATTRIBUTE14,
433 X_ATTRIBUTE15 => X_ATTRIBUTE15,
434 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
435 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
436 X_LAST_UPDATE_DATE => sysdate,
437 X_LAST_UPDATED_BY => l_user_id,
438 X_LAST_UPDATE_LOGIN => 0);
439 END IF;
440 END IF;
441 CLOSE c_last_updated;
442 End LOAD_ROW;
443
444 end JTF_RS_SKILL_LEVELS_PKG;