[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_COLUMNS_PKG
Source
1 PACKAGE BODY PAY_USER_COLUMNS_PKG AS
2 /* $Header: pyusc01t.pkb 120.1 2005/07/29 05:08:17 shisriva noship $ */
3 --
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_user_table_id number(9); -- For validating translation;
6 g_business_group_id number(15); -- For validating translation;
7 g_legislation_code varchar2(150); -- For validating translation;
8 --
9
10 procedure insert_row(p_rowid in out NOCOPY varchar2,
11 p_user_column_id in out NOCOPY number,
12 p_user_table_id in number,
13 p_business_group_id in number,
14 p_legislation_code in varchar2,
15 p_legislation_subgroup in varchar2,
16 p_user_column_name in varchar2,
17 p_formula_id in number ) is
18 cursor c1 is
19 select pay_user_columns_s.nextval
20 from sys.dual ;
21
22 cursor c2 is
23 select rowid
24 from pay_user_columns
25 where user_column_id = p_user_column_id ;
26 --
27 begin
28 --
29 open c1 ;
30 fetch c1 into p_user_column_id ;
31 close c1 ;
32
33 insert into PAY_USER_COLUMNS
34 ( USER_COLUMN_ID,
35 USER_TABLE_ID,
36 BUSINESS_GROUP_ID,
37 LEGISLATION_CODE,
38 LEGISLATION_SUBGROUP,
39 USER_COLUMN_NAME,
40 FORMULA_ID )
41 values ( p_user_column_id,
42 p_user_table_id,
43 p_business_group_id,
44 p_legislation_code,
45 p_legislation_subgroup,
46 p_user_column_name,
47 p_formula_id ) ;
48 --
49 open c2 ;
50 fetch c2 into p_rowid ;
51 close c2 ;
52 --
53 g_dml_status := TRUE;
54 --For MLS------------------------------------------------------------------
55 pay_pct_ins.ins_tl(userenv('LANG'),p_user_column_id,p_user_column_name);
56 ---------------------------------------------------------------------------
57 g_dml_status := FALSE;
58 Exception
59 When Others then
60 g_dml_status := FALSE;
61 raise;
62 end insert_row ;
63 --
64 --
65 procedure update_row(p_rowid in varchar2,
66 p_user_column_id in number,
67 p_user_table_id in number,
68 p_business_group_id in number,
69 p_legislation_code in varchar2,
70 p_legislation_subgroup in varchar2,
71 p_user_column_name in varchar2,
72 p_formula_id in number,
73 p_base_user_column_name in varchar2 default hr_api.g_varchar2) is
74 begin
75 --
76 update PAY_USER_COLUMNS
77 set USER_COLUMN_ID = p_user_column_id,
78 USER_TABLE_ID = p_user_table_id,
79 BUSINESS_GROUP_ID = p_business_group_id ,
80 LEGISLATION_CODE = p_legislation_code,
81 LEGISLATION_SUBGROUP = p_legislation_subgroup ,
82 USER_COLUMN_NAME = p_base_user_column_name,
83 FORMULA_ID = p_formula_id
84 where ROWID = p_rowid;
85 --
86 g_dml_status := TRUE;
87 --For MLS------------------------------------------------------------------
88 pay_pct_upd.upd_tl(userenv('LANG'),p_user_column_id,p_user_column_name);
89 ---------------------------------------------------------------------------
90 g_dml_status := FALSE;
91 Exception
92 When Others then
93 g_dml_status := FALSE;
94 raise;
95 end update_row;
96 --
97 procedure delete_row(p_rowid in varchar2) is
98 --
99 ucid NUMBER;
100 begin
101 --
102 g_dml_status := TRUE;
103 --For MLS------------------------------------------------------------------
104 select user_column_id into ucid from pay_user_columns
105 where rowid = p_rowid;
106 pay_pct_del.del_tl(ucid);
107 ---------------------------------------------------------------------------
108 g_dml_status := FALSE;
109 delete from PAY_USER_COLUMNS
110 where ROWID = p_rowid;
111 --
112 Exception
113 When Others then
114 g_dml_status := FALSE;
115 raise;
116 end delete_row;
117 --
118 procedure lock_row (p_rowid in varchar2,
119 p_user_column_id in number,
120 p_user_table_id in number,
121 p_business_group_id in number,
122 p_legislation_code in varchar2,
123 p_legislation_subgroup in varchar2,
124 p_user_column_name in varchar2,
125 p_formula_id in number,
126 p_base_user_column_name in varchar2 default hr_api.g_varchar2) is
127 --_TL cursor--
128 cursor T is select *
129 from PAY_USER_COLUMNS_TL
130 where user_column_id = p_user_column_id
131 and language = userenv('lang')
132 for update NOWAIT ;
133 --
134 tlrowinfo T%rowtype;
135 --
136 --
137 cursor C is select *
138 from PAY_USER_COLUMNS
139 where rowid = p_rowid
140 for update of USER_COLUMN_ID NOWAIT ;
141 --
142 rowinfo C%rowtype;
143 --
144 begin
145 --
146 open C;
147 fetch C into rowinfo;
148 close C;
149 --
150 rowinfo.legislation_code := rtrim(rowinfo.legislation_code);
151 rowinfo.user_column_name := rtrim(rowinfo.user_column_name);
152 rowinfo.legislation_subgroup := rtrim(rowinfo.legislation_subgroup);
153 --
154 if ( (rowinfo.USER_COLUMN_ID = p_user_column_id )
155 or (rowinfo.USER_COLUMN_ID is null and p_user_column_id
156 is null ))
157 and( (rowinfo.USER_TABLE_ID = p_user_table_id )
158 or (rowinfo.USER_TABLE_ID is null and p_user_table_id
159 is null ))
160 and( (rowinfo.BUSINESS_GROUP_ID = p_business_group_id )
161 or (rowinfo.BUSINESS_GROUP_ID is null and p_business_group_id
162 is null ))
163 and( (rowinfo.LEGISLATION_CODE = p_legislation_code )
164 or (rowinfo.LEGISLATION_CODE is null and p_legislation_code
165 is null ))
166 and( (rowinfo.LEGISLATION_SUBGROUP = p_legislation_subgroup )
167 or (rowinfo.LEGISLATION_SUBGROUP is null and p_legislation_subgroup
168 is null ))
169 and ( (rowinfo.USER_COLUMN_NAME = p_base_user_column_name )
170 or (rowinfo.USER_COLUMN_NAME is null and p_base_user_column_name
171 is null ))
172 and ( (rowinfo.FORMULA_ID = p_formula_id )
173 or (rowinfo.FORMULA_ID is null and p_formula_id
174 is null ))
175 then
176 return ;
177 else
178 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
179 app_exception.raise_exception ;
180 end if;
181
182 --_TL table lock
183 open T;
184 fetch T into tlrowinfo;
185 close T;
186 --
187 -- Remove trailing blanks from char fields
188 tlrowinfo.user_column_name := rtrim(tlrowinfo.user_column_name);
189 --
190 if ( (tlrowinfo.USER_COLUMN_NAME = p_user_column_name )
191 or (tlrowinfo.USER_COLUMN_NAME is null and p_user_column_name
192 is null ))
193 then
194 return ;
195 else
196 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED' ) ;
197 app_exception.raise_exception ;
198 end if;
199
200 end lock_row;
201 --
202
203 procedure check_unique ( p_rowid in varchar2,
204 p_user_column_name in varchar2,
205 p_user_table_id in number,
206 p_business_group_id in number,
207 p_base_user_column_name in varchar2 default hr_api.g_varchar2) is
208
209 cursor c1 is
210 select '1'
211 from pay_user_columns uc
212 where upper(uc.user_column_name) = upper( p_base_user_column_name)
213 and uc.user_table_id = p_user_table_id
214 and ( p_rowid is null
215 or ( p_rowid is not null
216 and p_rowid <> uc.rowid ) )
217 and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
218 = nvl(p_business_group_id, -1);
219 l_dummy varchar2(1) ;
220 begin
221 --
222 open c1 ;
223 fetch c1 into l_dummy ;
224 if c1%found
225 then close c1 ;
226 fnd_message.set_name( 'PAY' , 'PAY_7885_USER_TABLE_UNIQUE' ) ;
227 fnd_message.raise_error ;
228 end if ;
229 close c1 ;
230 end check_unique ;
231 --
232 procedure check_unique_f ( p_rowid in varchar2,
233 p_user_column_name in varchar2,
234 p_user_table_id in number,
235 p_business_group_id in number,
236 p_legislation_code in varchar2,
237 p_base_user_column_name in varchar2 default hr_api.g_varchar2) is
238 cursor c1 is
239 select '1'
240 from pay_user_columns uc
241 where upper(uc.user_column_name) = upper( p_base_user_column_name)
242 and uc.user_table_id = p_user_table_id
243 and ( p_rowid is null
244 or ( p_rowid is not null
245 and p_rowid <> uc.rowid ) )
246 and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
247 = nvl(p_business_group_id, -1)
248 and nvl(uc.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
249 = nvl(p_legislation_code, '~~nvl~~');
250
251 --_TL cursor
252 cursor c2 is
253 select '1'
254 from pay_user_columns uc,pay_user_columns_tl ucl
255 where upper(ucl.user_column_name) = upper(p_user_column_name)
256 and ucl.user_column_id = uc.user_column_id
257 and uc.user_table_id = p_user_table_id
258 and nvl(uc.business_group_id,nvl(p_business_group_id, -1))
259 = nvl(p_business_group_id, -1)
260 and nvl(uc.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
261 = nvl(p_legislation_code, '~~nvl~~')
262 and (ucl.rowid not in ((select rowid from pay_user_columns_tl pct
263 where pct.user_column_id = (select user_column_id from
264 pay_user_columns
265 where rowid = p_rowid)
266 --and language = userenv('lang')
267 )));
268
269 l_dummy varchar2(1) ;
270 begin
271 --
272 open c1 ;
273 fetch c1 into l_dummy ;
274 if c1%found
275 then close c1 ;
276 fnd_message.set_name( 'PAY' , 'PAY_7885_USER_TABLE_UNIQUE' ) ;
277 fnd_message.raise_error ;
278 end if ;
279 close c1 ;
280
281 --check _TL uniqeness
282 open c2 ;
283 fetch c2 into l_dummy ;
284 if c2%found
285 then close c2 ;
286 fnd_message.set_name( 'PAY' , 'PAY_7885_USER_TABLE_UNIQUE' ) ;
287 fnd_message.raise_error ;
288 end if ;
289 close c2 ;
290 --
291
292 end check_unique_f ;
293 --
294
295 procedure check_delete ( p_user_column_id in number ) is
296 cursor c1 is
297 select null
298 from pay_user_column_instances_f
299 where user_column_id = p_user_column_id ;
300 --
301 l_dummy varchar2(1) ;
302 begin
303 --
304 -- Check PAY_USER_COLUMN_INSTANCES_F
305 open c1 ;
306 fetch c1 into l_dummy ;
307 if c1%found then
308 close c1 ;
309 fnd_message.set_name ( 'PAY' , 'HR_6980_USERTAB_VALUES_FIRST' ) ;
310 fnd_message.set_token ( 'ROWCOL' , 'column' ) ;
311 fnd_message.raise_error ;
312 end if ;
313 close c1 ;
314 --
315 end check_delete ;
316
317 --
318 ----For MLS---------------------------------------------------------------------
319
320 procedure check_base_update(p_base_user_column_name in varchar2,
321 p_rowid in varchar2) is
322 l_package_name VARCHAR2(80) := 'PAY_USER_COLUMNS_PKG.CHECK_UPDATE';
323 original_user_column_name varchar2(80);
324 begin
325 select base_user_column_name into original_user_column_name
326 from pay_user_columns_vl
327 where row_id = p_rowid;
328 if(p_base_user_column_name <> original_user_column_name) then
329 hr_utility.set_location (l_package_name,1);
330 fnd_message.set_name ('PER','PER_52480_SSM_NON_UPD_FIELD'); -- checkformat failure
331 fnd_message.raise_error;
332 end if;
333 --
334 end check_base_update;
335 --
336
337 procedure ADD_LANGUAGE
338 is
339 begin
340 delete from PAY_USER_COLUMNS_TL T
341 where not exists
342 (select NULL
343 from PAY_USER_COLUMNS B
344 where B.USER_COLUMN_ID = T.USER_COLUMN_ID
345 );
346 update PAY_USER_COLUMNS_TL T
347 set (USER_COLUMN_NAME) =
348 (select B.USER_COLUMN_NAME
349 from PAY_USER_COLUMNS_TL B
350 where B.USER_COLUMN_ID = T.USER_COLUMN_ID
351 and B.LANGUAGE = T.SOURCE_LANG)
352 where (T.USER_COLUMN_ID,T.LANGUAGE) in
353 (select SUBT.USER_COLUMN_ID,SUBT.LANGUAGE
354 from PAY_USER_COLUMNS_TL SUBB, PAY_USER_COLUMNS_TL SUBT
355 where SUBB.USER_COLUMN_ID = SUBT.USER_COLUMN_ID
356 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
357 and (SUBB.USER_COLUMN_NAME <> SUBT.USER_COLUMN_NAME
358 ));
359
360 insert into PAY_USER_COLUMNS_TL (
361 USER_COLUMN_ID,
362 USER_COLUMN_NAME,
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN,
366 CREATED_BY,
367 CREATION_DATE,
368 LANGUAGE,
369 SOURCE_LANG
370 ) select
371 B.USER_COLUMN_ID,
372 B.USER_COLUMN_NAME,
373 B.LAST_UPDATE_DATE,
374 B.LAST_UPDATED_BY,
375 B.LAST_UPDATE_LOGIN,
376 B.CREATED_BY,
377 B.CREATION_DATE,
378 L.LANGUAGE_CODE,
379 B.SOURCE_LANG
380 from PAY_USER_COLUMNS_TL B, FND_LANGUAGES L
381 where L.INSTALLED_FLAG in ('I', 'B')
382 and B.LANGUAGE = userenv('LANG')
383 and not exists
384 (select NULL
385 from PAY_USER_COLUMNS_TL T
386 where T.USER_COLUMN_ID = B.USER_COLUMN_ID
387 and T.LANGUAGE = L.LANGUAGE_CODE);
388 end ADD_LANGUAGE;
389 --
390 procedure TRANSLATE_ROW (
391 X_B_USER_COLUMN_NAME in VARCHAR2,
392 X_B_LEGISLATION_CODE in VARCHAR2,
393 X_USER_COLUMN_NAME in VARCHAR2,
394 X_OWNER in VARCHAR2
395 ) is
396 begin
397 UPDATE PAY_USER_COLUMNS_TL
398 SET USER_COLUMN_NAME = nvl(X_USER_COLUMN_NAME,USER_COLUMN_NAME),
399 last_update_date = SYSDATE,
400 last_updated_by = decode(x_owner,'SEED',1,0),
401 last_update_login = 0,
402 source_lang = userenv('LANG')
403 WHERE userenv('LANG') IN (language,source_lang)
404 AND USER_COLUMN_ID in
405 (select USER_COLUMN_ID
406 from PAY_USER_COLUMNS
407 where nvl(USER_COLUMN_NAME,'~null~')=nvl(X_B_USER_COLUMN_NAME,'~null~')
408 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
409 and BUSINESS_GROUP_ID is NULL);
410 if (sql%notfound) then
411 null;
412 end if;
413 end TRANSLATE_ROW;
414 --
415 --
416 PROCEDURE set_translation_globals(p_business_group_id IN NUMBER,
417 p_legislation_code IN VARCHAR2,
418 p_user_table_id IN NUMBER) IS
419 BEGIN
420 g_business_group_id := p_business_group_id;
421 g_legislation_code := p_legislation_code;
422 g_user_table_id := p_user_table_id;
423 END;
424 --
425 --
426
427 procedure validate_translation(user_column_id NUMBER,
428 language VARCHAR2,
429 user_column_name VARCHAR2,
430 p_business_group_id IN NUMBER DEFAULT NULL,
431 p_legislation_code IN VARCHAR2 DEFAULT NULL) IS
432 /*
433
434 This procedure fails if a user_column translation is already present in
435 the table for a given language. Otherwise, no action is performed. It is
436 used to ensure uniqueness of translated user_column names.
437
438 */
439
440 --
441 -- This cursor implements the validation we require,
442 -- and expects that the various package globals are set before
443 -- the call to this procedure is made. This is done from the
444 -- user-named trigger 'TRANSLATIONS' in the form
445 --
446 cursor c_translation(p_language IN VARCHAR2,
447 p_user_column_name IN VARCHAR2,
448 p_user_column_id IN NUMBER,
449 p_user_table_id IN NUMBER,
450 p_bus_grp_id IN NUMBER,
451 p_leg_code IN varchar2) IS
452 select '1'
453 from pay_user_columns uc,
454 pay_user_columns_tl ucl
455 where upper(ucl.user_column_name) = upper(p_user_column_name)
456 AND uc.user_column_id = ucl.user_column_id
457 AND (ucl.user_column_id <> p_user_column_id OR p_user_column_id IS NULL)
458 AND uc.user_table_id = p_user_table_id
459 AND ucl.language = p_language
460 AND (nvl(uc.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
461 AND (nvl(uc.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);
462
463 l_package_name VARCHAR2(80);
464 l_business_group_id NUMBER;
465 l_legislation_code VARCHAR2(150);
466
467
468 BEGIN
469 l_package_name := 'PAY_USER_COLUMNS_PKG.VALIDATE_TRANSLATION';
470 l_business_group_id := p_business_group_id;
471 l_legislation_code := p_legislation_code;
472 hr_utility.set_location (l_package_name,10);
473 OPEN c_translation(language, user_column_name,user_column_id,g_user_table_id,
474 l_business_group_id,l_legislation_code);
475 hr_utility.set_location (l_package_name,50);
476 FETCH c_translation INTO g_dummy;
477
478 IF c_translation%NOTFOUND THEN
479 hr_utility.set_location (l_package_name,60);
480 CLOSE c_translation;
481 ELSE
482 hr_utility.set_location (l_package_name,70);
483 CLOSE c_translation;
484 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
485 fnd_message.raise_error;
486 END IF;
487 hr_utility.set_location ('Leaving:'||l_package_name,80);
488 END validate_translation;
489 --
490
491 function return_dml_status
492 return boolean
493 IS
494 begin
495 return g_dml_status;
496 end return_dml_status;
497 --
498 --------------------------------------------------------------------------------
499 END PAY_USER_COLUMNS_PKG;