[Home] [Help]
PACKAGE BODY: APPS.PER_ASSIGNMENT_INFO_TYPES_PKG
Source
1 PACKAGE BODY PER_ASSIGNMENT_INFO_TYPES_PKG as
2 /* $Header: peait01t.pkb 115.3 99/07/17 18:28:42 porting shi $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 --
9 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE VARCHAR2,
10 P_ACTIVE_INACTIVE_FLAG VARCHAR2,
11 P_LEGISLATION_CODE VARCHAR2,
12 P_ROWID VARCHAR2,
13 P_DESCRIPTION VARCHAR2)
14 IS
15 L_DUMMY1 number;
16 CURSOR C1 IS
17 select 1
18 from per_assignment_info_types t
19 where upper(t.description) = upper(P_DESCRIPTION)
20 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
21 = nvl(P_LEGISLATION_CODE, 'XXX')
22 and (P_ROWID is null
23 or P_ROWID <> t.rowid);
24 BEGIN
25 OPEN C1;
26 FETCH C1 INTO L_DUMMY1;
27 IF C1%NOTFOUND THEN
28 CLOSE C1;
29 ELSE
30 CLOSE C1;
31 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
32 hr_utility.raise_error;
33 END IF;
34 end UNIQUENESS_CHECK;
35 --
36 procedure INSERT_ROW (
37 X_ROWID in out VARCHAR2,
38 X_INFORMATION_TYPE in VARCHAR2,
39 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
40 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
41 X_LEGISLATION_CODE in VARCHAR2,
42 X_REQUEST_ID in NUMBER,
43 X_OBJECT_VERSION_NUMBER in NUMBER,
44 X_DESCRIPTION in VARCHAR2,
45 X_CREATION_DATE in DATE,
46 X_CREATED_BY in NUMBER,
47 X_LAST_UPDATE_DATE in DATE,
48 X_LAST_UPDATED_BY in NUMBER,
49 X_LAST_UPDATE_LOGIN in NUMBER
50 ) is
51 cursor C is select ROWID from PER_ASSIGNMENT_INFO_TYPES
52 where INFORMATION_TYPE = X_INFORMATION_TYPE
53 ;
54 begin
55 insert into PER_ASSIGNMENT_INFO_TYPES (
56 INFORMATION_TYPE,
57 ACTIVE_INACTIVE_FLAG,
58 MULTIPLE_OCCURENCES_FLAG,
59 LEGISLATION_CODE,
60 REQUEST_ID,
61 OBJECT_VERSION_NUMBER,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LAST_UPDATE_LOGIN
67 ) values (
68 X_INFORMATION_TYPE,
69 X_ACTIVE_INACTIVE_FLAG,
70 X_MULTIPLE_OCCURENCES_FLAG,
71 X_LEGISLATION_CODE,
72 X_REQUEST_ID,
73 X_OBJECT_VERSION_NUMBER,
74 X_CREATION_DATE,
75 X_CREATED_BY,
76 X_LAST_UPDATE_DATE,
77 X_LAST_UPDATED_BY,
78 X_LAST_UPDATE_LOGIN
79 );
80
81 insert into PER_ASSIGNMENT_INFO_TYPES_TL (
82 INFORMATION_TYPE,
83 DESCRIPTION,
84 LAST_UPDATE_DATE,
85 LAST_UPDATED_BY,
86 LAST_UPDATE_LOGIN,
87 CREATED_BY,
88 CREATION_DATE,
89 LANGUAGE,
90 SOURCE_LANG
91 ) select
92 X_INFORMATION_TYPE,
93 X_DESCRIPTION,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATED_BY,
96 X_LAST_UPDATE_LOGIN,
97 X_CREATED_BY,
98 X_CREATION_DATE,
99 L.LANGUAGE_CODE,
100 userenv('LANG')
101 from FND_LANGUAGES L
102 where L.INSTALLED_FLAG in ('I', 'B')
103 and not exists
104 (select NULL
105 from PER_ASSIGNMENT_INFO_TYPES_TL T
106 where T.INFORMATION_TYPE = X_INFORMATION_TYPE
107 and T.LANGUAGE = L.LANGUAGE_CODE);
108
109 open c;
110 fetch c into X_ROWID;
111 if (c%notfound) then
112 close c;
113 raise no_data_found;
114 end if;
115 close c;
116
117 end INSERT_ROW;
118
119 procedure LOCK_ROW (
120 X_INFORMATION_TYPE in VARCHAR2,
121 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
122 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
123 X_LEGISLATION_CODE in VARCHAR2,
124 X_REQUEST_ID in NUMBER,
125 X_OBJECT_VERSION_NUMBER in NUMBER,
126 X_DESCRIPTION in VARCHAR2
127 ) is
128 cursor c is select
129 ACTIVE_INACTIVE_FLAG,
130 MULTIPLE_OCCURENCES_FLAG,
131 LEGISLATION_CODE,
132 REQUEST_ID,
133 OBJECT_VERSION_NUMBER
134 from PER_ASSIGNMENT_INFO_TYPES
135 where INFORMATION_TYPE = X_INFORMATION_TYPE
136 for update of INFORMATION_TYPE nowait;
137 recinfo c%rowtype;
138
139 cursor c1 is select
140 DESCRIPTION,
141 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
142 from PER_ASSIGNMENT_INFO_TYPES_TL
143 where INFORMATION_TYPE = X_INFORMATION_TYPE
144 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
145 for update of INFORMATION_TYPE nowait;
146 begin
147 open c;
148 fetch c into recinfo;
149 if (c%notfound) then
150 close c;
151 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
152 app_exception.raise_exception;
153 end if;
154 close c;
155 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
156 AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
157 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
158 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
159 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
160 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
161 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
162 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
163 ) then
164 null;
165 else
166 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167 app_exception.raise_exception;
168 end if;
169
170 for tlinfo in c1 loop
171 if (tlinfo.BASELANG = 'Y') then
172 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
173 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
174 ) then
175 null;
176 else
177 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
178 app_exception.raise_exception;
179 end if;
180 end if;
181 end loop;
182 return;
183 end LOCK_ROW;
184
185 procedure UPDATE_ROW (
186 X_INFORMATION_TYPE in VARCHAR2,
187 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
188 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
189 X_LEGISLATION_CODE in VARCHAR2,
190 X_REQUEST_ID in NUMBER,
191 X_OBJECT_VERSION_NUMBER in NUMBER,
192 X_DESCRIPTION in VARCHAR2,
193 X_LAST_UPDATE_DATE in DATE,
194 X_LAST_UPDATED_BY in NUMBER,
195 X_LAST_UPDATE_LOGIN in NUMBER
196 ) is
197 begin
198 update PER_ASSIGNMENT_INFO_TYPES set
199 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
200 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
201 LEGISLATION_CODE = X_LEGISLATION_CODE,
202 REQUEST_ID = X_REQUEST_ID,
203 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
204 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
205 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
206 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
207 where INFORMATION_TYPE = X_INFORMATION_TYPE;
208
209 if (sql%notfound) then
210 raise no_data_found;
211 end if;
212
213 update PER_ASSIGNMENT_INFO_TYPES_TL set
214 DESCRIPTION = X_DESCRIPTION,
215 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
216 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
218 SOURCE_LANG = userenv('LANG')
219 where INFORMATION_TYPE = X_INFORMATION_TYPE
220 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225 end UPDATE_ROW;
226
227 procedure DELETE_ROW (
228 X_INFORMATION_TYPE in VARCHAR2
229 ) is
230 begin
231 delete from PER_ASSIGNMENT_INFO_TYPES_TL
232 where INFORMATION_TYPE = X_INFORMATION_TYPE;
233
234 if (sql%notfound) then
235 raise no_data_found;
236 end if;
237
238 delete from PER_ASSIGNMENT_INFO_TYPES
239 where INFORMATION_TYPE = X_INFORMATION_TYPE;
240
241 if (sql%notfound) then
242 raise no_data_found;
243 end if;
244 end DELETE_ROW;
245
246 procedure LOAD_ROW
247 (X_INFORMATION_TYPE in varchar2
248 ,X_ACTIVE_INACTIVE_FLAG in varchar2
249 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
250 ,X_DESCRIPTION in varchar2
251 ,X_LEGISLATION_CODE in varchar2
252 ,X_OBJECT_VERSION_NUMBER in number
253 ,X_OWNER in varchar2
254 )
255 is
256 l_proc VARCHAR2(61) := 'PER_ASSIGNMENT_INFO_TYPES_PKG.LOAD_ROW';
257 l_rowid rowid;
258 l_request_id per_assignment_info_types.request_id%TYPE;
259 l_progam_application_id per_assignment_info_types.program_application_id%TYPE;
260 l_program_id per_assignment_info_types.program_id%TYPE;
261 l_program_update_date per_assignment_info_types.program_update_date%TYPE;
262 l_created_by per_assignment_info_types.created_by%TYPE := 0;
263 l_creation_date per_assignment_info_types.creation_date%TYPE := SYSDATE;
264 l_last_update_date per_assignment_info_types.last_update_date%TYPE := SYSDATE;
265 l_last_updated_by per_assignment_info_types.last_updated_by%TYPE := 0;
266 l_last_update_login per_assignment_info_types.last_update_login%TYPE := 0;
267 begin
268 -- Translate developer keys to internal parameters
269 if X_OWNER = 'SEED' then
270 l_created_by := 1;
271 l_last_updated_by := 1;
272 end if;
273 -- Update or insert row as appropriate
274 begin
275 UPDATE_ROW
276 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
277 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
278 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
279 ,X_DESCRIPTION => X_DESCRIPTION
280 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
281 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
282 ,X_REQUEST_ID => l_request_id
283 ,X_LAST_UPDATE_DATE => l_last_update_date
284 ,X_LAST_UPDATED_BY => l_last_updated_by
285 ,X_LAST_UPDATE_LOGIN => l_last_update_login
286 );
287 exception
288 when no_data_found then
289 INSERT_ROW
290 (X_ROWID => l_rowid
291 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
292 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
293 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
294 ,X_DESCRIPTION => X_DESCRIPTION
295 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
296 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
297 ,X_REQUEST_ID => l_request_id
298 ,X_CREATED_BY => l_created_by
299 ,X_CREATION_DATE => l_creation_date
300 ,X_LAST_UPDATE_DATE => l_last_update_date
301 ,X_LAST_UPDATED_BY => l_last_updated_by
302 ,X_LAST_UPDATE_LOGIN => l_last_update_login
303 );
304 end;
305 --
306 end LOAD_ROW;
307
308 procedure TRANSLATE_ROW
309 (X_INFORMATION_TYPE in varchar2
310 ,X_DESCRIPTION in varchar2
311 ,X_OWNER in varchar2
312 )
313 is
314 begin
315 UPDATE per_assignment_info_types_tl
316 SET description = X_DESCRIPTION
317 ,last_update_date = SYSDATE
318 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
319 ,last_update_login = 0
320 ,source_lang = USERENV('LANG')
321 WHERE USERENV('LANG') IN (language,source_lang)
322 AND information_type = X_INFORMATION_TYPE;
323 end TRANSLATE_ROW;
324
325 procedure ADD_LANGUAGE
326 is
327 begin
328 delete from PER_ASSIGNMENT_INFO_TYPES_TL T
329 where not exists
330 (select NULL
331 from PER_ASSIGNMENT_INFO_TYPES B
332 where B.INFORMATION_TYPE = T.INFORMATION_TYPE
333 );
334
335 update PER_ASSIGNMENT_INFO_TYPES_TL T set (
336 DESCRIPTION
337 ) = (select
338 B.DESCRIPTION
339 from PER_ASSIGNMENT_INFO_TYPES_TL B
340 where B.INFORMATION_TYPE = T.INFORMATION_TYPE
341 and B.LANGUAGE = T.SOURCE_LANG)
342 where (
343 T.INFORMATION_TYPE,
344 T.LANGUAGE
345 ) in (select
346 SUBT.INFORMATION_TYPE,
347 SUBT.LANGUAGE
348 from PER_ASSIGNMENT_INFO_TYPES_TL SUBB, PER_ASSIGNMENT_INFO_TYPES_TL SUBT
349 where SUBB.INFORMATION_TYPE = SUBT.INFORMATION_TYPE
350 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
351 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
352 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
353 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
354 ));
355
356 insert into PER_ASSIGNMENT_INFO_TYPES_TL (
357 INFORMATION_TYPE,
358 DESCRIPTION,
359 LAST_UPDATE_DATE,
360 LAST_UPDATED_BY,
361 LAST_UPDATE_LOGIN,
362 CREATED_BY,
363 CREATION_DATE,
364 LANGUAGE,
365 SOURCE_LANG
366 ) select
367 B.INFORMATION_TYPE,
368 B.DESCRIPTION,
369 B.LAST_UPDATE_DATE,
370 B.LAST_UPDATED_BY,
371 B.LAST_UPDATE_LOGIN,
372 B.CREATED_BY,
373 B.CREATION_DATE,
374 L.LANGUAGE_CODE,
375 B.SOURCE_LANG
376 from PER_ASSIGNMENT_INFO_TYPES_TL B, FND_LANGUAGES L
377 where L.INSTALLED_FLAG in ('I', 'B')
378 and B.LANGUAGE = userenv('LANG')
379 and not exists
380 (select NULL
381 from PER_ASSIGNMENT_INFO_TYPES_TL T
382 where T.INFORMATION_TYPE = B.INFORMATION_TYPE
383 and T.LANGUAGE = L.LANGUAGE_CODE);
384 end ADD_LANGUAGE;
385 --------------------------------------------------------------------------------
386 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
387 p_legislation_code IN VARCHAR2) IS
388 BEGIN
389 g_business_group_id := p_business_group_id;
390 g_legislation_code := p_legislation_code;
391 END;
392 --------------------------------------------------------------------------------
393 --------------------------------------------------------------------------------
394 procedure validate_translation(information_type IN VARCHAR2,
395 language IN VARCHAR2,
396 description IN VARCHAR2)
397 IS
398 /*
399
400 This procedure fails if a description translation is already present in
401 the table for a given language. Otherwise, no action is performed. It is
405
402 used to ensure uniqueness of translated descriptions.
403
404 */
406 --
407 -- This cursor implements the validation we require,
408 -- and expects that the various package globals are set before
409 -- the call to this procedure is made. This is done from the
410 -- user-named trigger 'TRANSLATIONS' in the form
411 --
412 cursor c_translation(p_language IN VARCHAR2,
413 p_description IN VARCHAR2,
414 p_information_type IN VARCHAR2)
415 IS
416 SELECT 1
417 FROM per_assignment_info_types_tl aitt,
418 per_assignment_info_types ait
419 WHERE upper(aitt.description)=upper(p_description)
420 AND aitt.information_type = ait.information_type
421 AND aitt.language = p_language
422 AND (ait.information_type <> p_information_type
423 OR p_information_type IS NULL)
424 ;
425
426 l_package_name VARCHAR2(80) := 'PER_ASSIGNMENT_INFO_TYPES_PKG.VALIDATE_TRANSLATION';
427
428 BEGIN
429 hr_utility.set_location (l_package_name,10);
430 OPEN c_translation(language, description,information_type);
431 hr_utility.set_location (l_package_name,50);
432 FETCH c_translation INTO g_dummy;
433
434 IF c_translation%NOTFOUND THEN
435 hr_utility.set_location (l_package_name,60);
436 CLOSE c_translation;
437 ELSE
438 hr_utility.set_location (l_package_name,70);
439 CLOSE c_translation;
440 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
441 fnd_message.raise_error;
442 END IF;
443 hr_utility.set_location ('Leaving:'||l_package_name,80);
444 END validate_translation;
445 --------------------------------------------------------------------------------
446
447 --
448 END PER_ASSIGNMENT_INFO_TYPES_PKG;