[Home] [Help]
PACKAGE BODY: APPS.PAY_USER_TABLES_PKG
Source
1 PACKAGE BODY PAY_USER_TABLES_PKG AS
2 /* $Header: pyust01t.pkb 120.2 2006/05/12 02:45:59 snekkala noship $ */
3 --
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 --
6 procedure insert_row(p_rowid in out NOCOPY varchar2,
7 p_user_table_id in out NOCOPY number,
8 p_business_group_id in number,
9 p_legislation_code in varchar2,
10 p_legislation_subgroup in varchar2,
11 p_range_or_match in varchar2,
12 p_user_key_units in varchar2,
13 p_user_table_name in varchar2,
14 p_user_row_title in varchar2 ) is
15 cursor c1 is
16 select pay_user_tables_s.nextval
17 from sys.dual ;
18 --
19 cursor c2 is
20 select rowid
21 from pay_user_tables
22 where user_table_id = p_user_table_id ;
23 --
24 begin
25 --
26 check_unique ( p_rowid => p_rowid,
27 p_user_table_name => p_user_table_name,
28 p_business_group_id => p_business_group_id,
29 p_legislation_code => p_legislation_code,
30 p_base_user_table_name => p_user_table_name) ;
31 --
32 open c1 ;
33 fetch c1 into p_user_table_id ;
34 close c1 ;
35 --
36 insert into PAY_USER_TABLES
37 ( USER_TABLE_ID,
38 BUSINESS_GROUP_ID,
39 LEGISLATION_CODE,
40 LEGISLATION_SUBGROUP,
41 RANGE_OR_MATCH,
42 USER_KEY_UNITS,
43 USER_TABLE_NAME,
44 USER_ROW_TITLE )
45 values ( p_user_table_id,
46 p_business_group_id,
47 p_legislation_code,
48 p_legislation_subgroup,
49 p_range_or_match,
50 p_user_key_units,
51 p_user_table_name,
52 p_user_row_title ) ;
53 --
54 open c2 ;
55 fetch c2 into p_rowid ;
56 close c2 ;
57 --
58 g_dml_status := TRUE;
59 ---For MLS-----------------------------------------------------------------------
60 pay_ptt_ins.ins_tl(userenv('LANG'),p_user_table_id,
61 p_user_table_name,p_user_row_title);
62 --------------------------------------------------------------------------------
63 g_dml_status := FALSE;
64 Exception
65 When Others then
66 g_dml_status := FALSE;
67 raise;
68
69 end insert_row ;
70 --
71 procedure update_row(p_rowid in varchar2,
72 p_user_table_id in number,
73 p_business_group_id in number,
74 p_legislation_code in varchar2,
75 p_legislation_subgroup in varchar2,
76 p_range_or_match in varchar2,
77 p_user_key_units in varchar2,
78 p_user_table_name in varchar2,
79 p_user_row_title in varchar2,
80 p_base_user_table_name in varchar2 default hr_api.g_varchar2,
81 p_base_user_row_title in varchar2 default hr_api.g_varchar2) is
82 begin
83 --
84 check_unique ( p_rowid => p_rowid,
85 p_user_table_name => p_user_table_name,
86 p_business_group_id => p_business_group_id,
87 p_legislation_code => p_legislation_code,
88 p_base_user_table_name => p_base_user_table_name) ;
89 --
90 update PAY_USER_TABLES
91 set USER_TABLE_ID = p_user_table_id,
92 BUSINESS_GROUP_ID = p_business_group_id ,
93 LEGISLATION_CODE = p_legislation_code,
94 LEGISLATION_SUBGROUP = p_legislation_subgroup ,
95 RANGE_OR_MATCH = p_range_or_match,
96 USER_KEY_UNITS = p_user_key_units,
97 USER_TABLE_NAME = p_base_user_table_name,
98 USER_ROW_TITLE = p_base_user_row_title
99 where ROWID = p_rowid;
100 --
101 g_dml_status := TRUE;
102 ---For MLS-----------------------------------------------------------------------
103 pay_ptt_upd.upd_tl(userenv('LANG'),p_user_table_id,
104 p_user_table_name,p_user_row_title);
105 ---------------------------------------------------------------------------------
106 g_dml_status := FALSE;
107 Exception
108 When Others then
109 g_dml_status := FALSE;
110 raise;
111 end update_row;
112 --
113 procedure delete_row(p_rowid in varchar2,
114 p_user_table_id in number ) is
115 --
116 begin
117 check_references( p_user_table_id => p_user_table_id ) ;
118 g_dml_status := TRUE;
119 ---For MLS-----------------------------------------------------------------------
120 pay_ptt_del.del_tl(p_user_table_id);
121 --------------------------------------------------------------------------------
122 g_dml_status := FALSE;
123 --
124 delete from PAY_USER_TABLES
125 where ROWID = p_rowid;
126 --
130 raise;
127 Exception
128 When Others then
129 g_dml_status := FALSE;
131 end delete_row;
132 --
133 procedure lock_row (p_rowid in varchar2,
134 p_user_table_id in number,
135 p_business_group_id in number,
136 p_legislation_code in varchar2,
137 p_legislation_subgroup in varchar2,
138 p_range_or_match in varchar2,
139 p_user_key_units in varchar2,
140 p_user_table_name in varchar2,
141 p_user_row_title in varchar2,
142 p_base_user_table_name in varchar2 default hr_api.g_varchar2,
143 p_base_user_row_title in varchar2 default hr_api.g_varchar2) is
144
145 --_TL cursor--
146 cursor T is select *
147 from PAY_USER_TABLES_TL
148 where user_table_id = p_user_table_id
149 and language = userenv('lang')
150 for update NOWAIT ;
151 --
152 tlrowinfo T%rowtype;
153 --
154 --
155 cursor C is select *
156 from PAY_USER_TABLES
157 where rowid = p_rowid
158 for update of USER_TABLE_ID NOWAIT ;
159 --
160 rowinfo C%rowtype;
161 --
162
163 begin
164 --
165 open C;
166 fetch C into rowinfo;
167 close C;
168 --
169 --
170 -- Remove trailing blanks from char fields
171 rowinfo.legislation_code := rtrim(rowinfo.legislation_code);
172 rowinfo.range_or_match := rtrim(rowinfo.range_or_match);
173 rowinfo.user_key_units := rtrim(rowinfo.user_key_units);
174 rowinfo.user_table_name := rtrim(rowinfo.user_table_name);
175 rowinfo.legislation_subgroup := rtrim(rowinfo.legislation_subgroup);
176 rowinfo.user_row_title := rtrim(rowinfo.user_row_title);
177 --
178 if ( (rowinfo.USER_TABLE_ID = p_user_table_id )
179 or (rowinfo.USER_TABLE_ID is null and p_user_table_id
180 is null ))
181 and( (rowinfo.BUSINESS_GROUP_ID = p_business_group_id )
182 or (rowinfo.BUSINESS_GROUP_ID is null and p_business_group_id
183 is null ))
184 and( (rowinfo.LEGISLATION_CODE = p_legislation_code )
185 or (rowinfo.LEGISLATION_CODE is null and p_legislation_code
186 is null ))
187 and( (rowinfo.LEGISLATION_SUBGROUP = p_legislation_subgroup )
188 or (rowinfo.LEGISLATION_SUBGROUP is null and p_legislation_subgroup
189 is null ))
190 and( (rowinfo.RANGE_OR_MATCH = p_range_or_match )
191 or (rowinfo.RANGE_OR_MATCH is null and p_range_or_match
192 is null ))
193 and ( (rowinfo.USER_KEY_UNITS = p_user_key_units )
194 or (rowinfo.USER_KEY_UNITS is null and p_user_key_units
195 is null ))
196 and ( (rowinfo.USER_TABLE_NAME = p_base_user_table_name )
197 or (rowinfo.USER_TABLE_NAME is null and p_base_user_table_name
198 is null ))
199 and ( (rowinfo.USER_ROW_TITLE = p_base_user_row_title )
200 or (rowinfo.USER_ROW_TITLE is null and p_base_user_row_title
201 is null ))
202 then
203 return ;
204 else
205 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED' ) ;
206 app_exception.raise_exception ;
207 end if;
208
209 --_TL table lock
210 open T;
211 fetch T into tlrowinfo;
212 close T;
213 --
214 --
215 -- Remove trailing blanks from char fields
216 tlrowinfo.user_table_name := rtrim(tlrowinfo.user_table_name);
217 tlrowinfo.user_row_title := rtrim(tlrowinfo.user_row_title);
218 --
219 if ( (tlrowinfo.USER_TABLE_NAME = p_user_table_name )
220 or (tlrowinfo.USER_TABLE_NAME is null and p_user_table_name
221 is null ))
222 and ( (tlrowinfo.USER_ROW_TITLE = p_user_row_title )
223 or (tlrowinfo.USER_ROW_TITLE is null and p_user_row_title
224 is null ))
225 then
226 return ;
227 else
228 fnd_message.set_name( 'FND' , 'FORM_RECORD_CHANGED' ) ;
229 app_exception.raise_exception ;
230 end if;
231
232 end lock_row;
233 --
234
235 procedure check_unique ( p_rowid in varchar2,
236 p_user_table_name in varchar2,
237 p_business_group_id in number,
238 p_legislation_code in varchar2,
239 p_base_user_table_name in varchar2 default hr_api.g_varchar2) is
240 cursor c1 is
241 select '1'
242 from pay_user_tables ut
243 where upper(ut.user_table_name) = upper(p_base_user_table_name)
244 and nvl(ut.business_group_id,nvl(p_business_group_id, -1))
245 = nvl(p_business_group_id, -1)
246 and nvl(ut.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
247 = nvl(p_legislation_code, '~~nvl~~')
248 and ( p_rowid is null
249 or ( p_rowid is not null
250 and p_rowid <> ut.rowid )
251 ) ;
252 cursor c2 is
253 select '1'
254 from pay_user_tables ut,pay_user_tables_tl utl
255 where upper(utl.user_table_name) = upper(p_user_table_name)
256 and utl.user_table_id = ut.user_table_id
257 and nvl(ut.business_group_id,nvl(p_business_group_id, -1))
258 = nvl(p_business_group_id, -1)
259 and nvl(ut.legislation_code, nvl(p_legislation_code,'~~nvl~~'))
260 = nvl(p_legislation_code, '~~nvl~~')
261 and (utl.rowid not in ((select rowid from pay_user_tables_tl ptt
262 where ptt.user_table_id = (select user_table_id from
263 pay_user_tables
264 where rowid = p_rowid)
265 --and language = userenv('lang')
266 )));
267 l_dummy varchar2(1) ;
268 begin
269 --
270 open c1 ;
271 fetch c1 into l_dummy ;
272 if c1%found
273 then close c1 ;
274 hr_utility.set_message( 801 , 'PAY_7689_USER_TAB_TAB_UNIQUE' ) ;
275 hr_utility.raise_error ;
276 end if ;
277 close c1 ;
278 --
279 --check _TL uniqeness
280 open c2 ;
281 fetch c2 into l_dummy ;
282 if c2%found
283 then close c2 ;
284 hr_utility.set_message( 801 , 'PAY_7689_USER_TAB_TAB_UNIQUE' ) ;
285 hr_utility.raise_error ;
286 end if ;
287 close c2 ;
288 --
289 end check_unique ;
290 --
291 procedure check_references ( p_user_table_id in number ) is
292 cursor c1 is
293 select '1'
294 from pay_user_columns
295 where user_table_id = p_user_table_id ;
296 --
297 cursor c2 is
298 select '1'
299 from pay_user_rows_f
300 where user_table_id = p_user_table_id ;
301 l_dummy varchar2(1) ;
302 l_detail_found boolean := FALSE ;
303 begin
304 --
305 -- Check PAY_USER_COLUMNS
306 open c1 ;
307 fetch c1 into l_dummy ;
308 if c1%found then
309 close c1 ;
310 hr_utility.set_message ( 801 , 'PAY_6368_USERTAB_COLUMNS_FIRST' ) ;
311 hr_utility.raise_error ;
312 end if ;
313 close c1 ;
314 --
315 -- Check PAY_USER_ROWS_F
316 open c2 ;
317 fetch c2 into l_dummy ;
318 if c2%found then
319 close c2 ;
320 hr_utility.set_message ( 801 , 'PAY_6369_USERTAB_ROWS_FIRST' ) ;
321 hr_utility.raise_error ;
322 end if ;
323 close c2 ;
324 --
325 end check_references ;
326 --
327
328 procedure check_base_update(p_base_user_table_name in varchar2,
329 p_rowid in varchar2) is
330 l_package_name VARCHAR2(80) := 'PAY_USER_TABLES_PKG.CHECK_UPDATE';
331 original_user_table_name varchar2(80);
332 begin
333 select base_user_table_name into original_user_table_name
334 from pay_user_tables_vl
335 where row_id = p_rowid;
336 if(p_base_user_table_name <> original_user_table_name) then
337 hr_utility.set_location (l_package_name,1);
338 fnd_message.set_name ('PER','PER_52480_SSM_NON_UPD_FIELD'); -- checkformat failure
339 fnd_message.raise_error;
340 end if;
341 --
342 end check_base_update;
343 --
344
345 procedure get_db_defaults ( p_lower_bound in out NOCOPY varchar2,
346 p_upper_bound in out NOCOPY varchar2,
347 p_match_prompt in out NOCOPY varchar2,
348 p_number_text in out NOCOPY varchar2 ) is
349 cursor c1 is
350 select lo.lookup_code,
351 lo.meaning
352 from hr_lookups lo
353 where lookup_type = 'USER_VALUES_PROMPT'
354 and lo.lookup_code in ( 'L' , 'U' , 'E' ) ;
355 begin
356 for range_prompt in c1
357 loop
358 if ( range_prompt.lookup_code = 'L' ) then
359 p_lower_bound := range_prompt.meaning ;
360 elsif ( range_prompt.lookup_code = 'U' ) then
361 p_upper_bound := range_prompt.meaning ;
362 else
363 p_match_prompt := range_prompt.meaning ;
364 end if ;
365 end loop ;
366 --
367 p_number_text := hr_general.decode_lookup( 'DATA_TYPE' , 'N' ) ;
368 --
369 end get_db_defaults ;
370 --
371 function ut_lov_conversion ( p_value in varchar2,
372 p_uom in varchar2 ) return varchar2 is
373 --
374 l_display varchar2 (80);
375 --
376 begin
377 --
378 if ( p_uom = 'D' ) then
379 l_display := fnd_date.date_to_displaydate ( fnd_date.canonical_to_date ( p_value ) );
380 elsif ( p_uom = 'N' ) then
381 -- l_display := fnd_number.canonical_to_number ( p_value );
382 l_display := hr_chkfmt.changeformat(p_value,'N',NULL);
383 else
384 l_display := p_value; -- for Text data type
385 end if;
386 return l_display;
387 --
388 exception
389 when others then
390 l_display := p_value;
391 return l_display;
392 --
393 end ut_lov_conversion;
394 --
395 --For MLS-----------------------------------------------------------------------
396 procedure ADD_LANGUAGE
397 is
398 begin
399 delete from PAY_USER_TABLES_TL T
400 where not exists
401 (select NULL
402 from PAY_USER_TABLES B
403 where B.USER_TABLE_ID = T.USER_TABLE_ID
404 );
405 update PAY_USER_TABLES_TL T set (
406 USER_TABLE_NAME,
407 USER_ROW_TITLE
408 ) = (select
409 B.USER_TABLE_NAME,
410 B.USER_ROW_TITLE
411 from PAY_USER_TABLES_TL B
412 where B.USER_TABLE_ID = T.USER_TABLE_ID
413 and B.LANGUAGE = T.SOURCE_LANG)
414 where (
415 T.USER_TABLE_ID,
416 T.LANGUAGE
417 ) in (select
418 SUBT.USER_TABLE_ID,
419 SUBT.LANGUAGE
420 from PAY_USER_TABLES_TL SUBB, PAY_USER_TABLES_TL SUBT
421 where SUBB.USER_TABLE_ID = SUBT.USER_TABLE_ID
422 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
423 and (SUBB.USER_TABLE_NAME <> SUBT.USER_TABLE_NAME
424 or SUBB.USER_ROW_TITLE <> SUBT.USER_ROW_TITLE
425 or (SUBB.USER_ROW_TITLE is null and SUBT.USER_ROW_TITLE is not null)
426 or (SUBB.USER_ROW_TITLE is not null and SUBT.USER_ROW_TITLE is null)
427 ));
428 insert into PAY_USER_TABLES_TL (
429 USER_TABLE_ID,
430 USER_TABLE_NAME,
431 USER_ROW_TITLE,
432 LAST_UPDATE_DATE,
433 LAST_UPDATED_BY,
434 LAST_UPDATE_LOGIN,
435 CREATED_BY,
436 CREATION_DATE,
437 LANGUAGE,
438 SOURCE_LANG
439 ) select
440 B.USER_TABLE_ID,
441 B.USER_TABLE_NAME,
442 B.USER_ROW_TITLE,
443 B.LAST_UPDATE_DATE,
444 B.LAST_UPDATED_BY,
445 B.LAST_UPDATE_LOGIN,
446 B.CREATED_BY,
447 B.CREATION_DATE,
448 L.LANGUAGE_CODE,
449 B.SOURCE_LANG
450 from PAY_USER_TABLES_TL B, FND_LANGUAGES L
451 where L.INSTALLED_FLAG in ('I', 'B')
452 and B.LANGUAGE = userenv('LANG')
453 and not exists
454 (select NULL
455 from PAY_USER_TABLES_TL T
456 where T.USER_TABLE_ID = B.USER_TABLE_ID
457 and T.LANGUAGE = L.LANGUAGE_CODE);
458 end ADD_LANGUAGE;
459 --
460 procedure TRANSLATE_ROW (
461 X_B_USER_TABLE_NAME in VARCHAR2,
462 X_B_LEGISLATION_CODE in VARCHAR2,
463 X_USER_TABLE_NAME in VARCHAR2,
464 X_USER_ROW_TITLE in VARCHAR2,
465 X_OWNER in VARCHAR2
466 ) is
467 begin
468 UPDATE PAY_USER_TABLES_tl
469 SET USER_TABLE_NAME = nvl(X_USER_TABLE_NAME,USER_TABLE_NAME),
470 USER_ROW_TITLE = nvl(X_USER_ROW_TITLE,USER_ROW_TITLE),
471 last_update_date = SYSDATE,
472 last_updated_by = decode(x_owner,'SEED',1,0),
473 last_update_login = 0,
474 source_lang = userenv('LANG')
475 WHERE userenv('LANG') IN (language,source_lang)
476 AND USER_TABLE_ID in
477 (select USER_TABLE_ID
478 from PAY_USER_TABLES
479 where nvl(USER_TABLE_NAME,'~null~')=nvl(X_B_USER_TABLE_NAME,'~null~')
480 and nvl(LEGISLATION_CODE,'~null~') = nvl(X_B_LEGISLATION_CODE,'~null~')
481 and BUSINESS_GROUP_ID is NULL);
482 if (sql%notfound) then
483 null;
484 end if;
485 end TRANSLATE_ROW;
486
487 --
488
489 procedure validate_translation(user_table_id NUMBER,
490 language VARCHAR2,
491 user_table_name VARCHAR2,
492 user_row_title VARCHAR2,
493 p_business_group_id IN NUMBER DEFAULT NULL,
494 p_legislation_code IN VARCHAR2 DEFAULT NULL) IS
495 /*
496
497 This procedure fails if a user_table translation is already present in
498 the table for a given language. Otherwise, no action is performed. It is
499 used to ensure uniqueness of translated user_table names.
500
501 */
502
503 --
504 -- This cursor implements the validation we require,
505 -- and expects that the various package globals are set before
506 -- the call to this procedure is made. This is done from the
507 -- user-named trigger 'TRANSLATIONS' in the form
508 --
509 cursor c_translation(p_language IN VARCHAR2,
510 p_user_table_name IN VARCHAR2,
511 p_user_table_id IN NUMBER,
512 p_bus_grp_id IN NUMBER,
513 p_leg_code IN varchar2) IS
514 SELECT 1
515 FROM pay_user_tables_tl ptt,
516 pay_user_tables put
517 WHERE upper(ptt.user_table_name)=upper(p_user_table_name)
518 AND ptt.user_table_id = put.user_table_id
519 AND ptt.language = p_language
520 AND (put.user_table_id <> p_user_table_id OR p_user_table_id IS NULL)
521 AND (nvl(put.business_group_id,-1) = nvl(p_bus_grp_id,-1) OR p_bus_grp_id IS NULL)
522 AND (nvl(put.LEGISLATION_CODE,'~null~') = nvl(p_leg_code,'~null~') OR p_leg_code IS NULL);
523
524 l_package_name VARCHAR2(80);
525 l_business_group_id NUMBER;
526 l_legislation_code VARCHAR2(150);
527
528 BEGIN
529 l_package_name := 'PAY_USER_TABLES_PKG.VALIDATE_TRANSLATION';
530 l_business_group_id := p_business_group_id;
531 l_legislation_code := p_legislation_code;
532 hr_utility.set_location (l_package_name,10);
533 OPEN c_translation(language, user_table_name,user_table_id,
534 l_business_group_id,l_legislation_code);
535 hr_utility.set_location (l_package_name,50);
536 FETCH c_translation INTO g_dummy;
537
538 IF c_translation%NOTFOUND THEN
539 hr_utility.set_location (l_package_name,60);
540 CLOSE c_translation;
541 ELSE
542 hr_utility.set_location (l_package_name,70);
543 CLOSE c_translation;
544 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
545 fnd_message.raise_error;
546 END IF;
547 hr_utility.set_location ('Leaving:'||l_package_name,80);
548 END validate_translation;
549 --
550
551 function return_dml_status
552 return boolean
553 IS
554 begin
555 return g_dml_status;
556 end return_dml_status;
557 --
558 END PAY_USER_TABLES_PKG;