[Home] [Help]
PACKAGE BODY: APPS.PER_CUSTOMIZED_RESTR_PKG
Source
1 PACKAGE BODY PER_CUSTOMIZED_RESTR_PKG as
2 /* $Header: perpepcr.pkb 115.5 2003/07/03 13:33:09 tvankayl noship $ */
3 ------------------------------------------------------------------------------
4 /*
5 ==============================================================================
6
7 01-JUL-03 tvankayl Modified table handles Insert_row,
8 Update_Row , Lock_ Row , Delete_row
9
10 1. prototypes were changed to follow
11 AOL standards.
12 2. DML operations were applied on
13 Translation table also.
14
15 Load_row and Translate_row were
16 modified to compensate for changes in
17 insert_row and update_row
18 115.5 03-JUL-03 tvankayl Removed unnecessary comments.
19 ==============================================================================
20 */
21
22 --------------------------------------------------------------------------------
23 g_dummy number(1); -- Dummy for cursor returns which are not needed
24 g_business_group_id number(15) default null; -- For validating translation;
25 g_legislation_code varchar2(30) default null; -- For validating translation;
26
27 --------------------------------------------------------------------------------
28 --
29
30 PROCEDURE UNIQUENESS_CHECK(P_APPLICATION_SHORT_NAME VARCHAR2,
31 P_FORM_NAME VARCHAR2,
32 P_NAME VARCHAR2,
33 P_BUSINESS_GROUP_NAME VARCHAR2,
34 P_LEGISLATION_CODE VARCHAR2,
35 P_ROWID VARCHAR2)
36 IS
37 L_DUMMY1 number;
38 l_appl_id number;
39 CURSOR C_APPL IS
40 select application_id
41 from fnd_application
42 where application_short_name = upper(P_APPLICATION_SHORT_NAME);
43 CURSOR C1 (c1_p_appl_id number) IS
44 select 1
45 from PAY_CUSTOMIZED_RESTRICTIONS pcr
46 where pcr.application_id = c1_p_appl_id
47 and pcr.form_name = P_FORM_NAME
48 and pcr.name = P_NAME
49 and pcr.legislation_code = P_LEGISLATION_CODE
50 and (P_ROWID is null
51 or P_ROWID <> pcr.rowid);
52 BEGIN
53 OPEN C_APPL;
54 FETCH C_APPL INTO l_appl_id;
55 CLOSE C_APPL;
56 OPEN C1(l_appl_id);
57 FETCH C1 INTO L_DUMMY1;
58 IF C1%NOTFOUND THEN
59 CLOSE C1;
60 ELSE
61 CLOSE C1;
62 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
63 hr_utility.raise_error;
64 END IF;
65
66 end UNIQUENESS_CHECK;
67
68
69
70 procedure INSERT_ROW (
71 X_ROWID in out nocopy VARCHAR2,
72 X_CUSTOMIZED_RESTRICTION_ID in out nocopy NUMBER,
73 X_BUSINESS_GROUP_ID in NUMBER,
74 X_LEGISLATION_CODE in VARCHAR2,
75 X_APPLICATION_ID in NUMBER,
76 X_FORM_NAME in VARCHAR2,
77 X_ENABLED_FLAG in VARCHAR2,
78 X_NAME in VARCHAR2,
79 X_COMMENTS in LONG,
80 X_LEGISLATION_SUBGROUP in VARCHAR2,
81 X_QUERY_FORM_TITLE in VARCHAR2,
82 X_STANDARD_FORM_TITLE in VARCHAR2,
83 X_CREATION_DATE in DATE,
84 X_CREATED_BY in NUMBER,
85 X_LAST_UPDATE_DATE in DATE,
86 X_LAST_UPDATED_BY in NUMBER,
87 X_LAST_UPDATE_LOGIN in NUMBER
88 ) is
89 cursor C is select ROWID from PAY_CUSTOMIZED_RESTRICTIONS
90 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;
91
92 cursor C_NEXTVAL is select PAY_CUSTOMIZED_RESTRICTIONS_S.NEXTVAL from SYS.DUAL;
93
94 begin
95
96
97 OPEN C_NEXTVAL;
98 FETCH C_NEXTVAL INTO X_CUSTOMIZED_RESTRICTION_ID;
99 CLOSE C_NEXTVAL;
100
101
102 insert into PAY_CUSTOMIZED_RESTRICTIONS (
103 CUSTOMIZED_RESTRICTION_ID,
104 BUSINESS_GROUP_ID,
105 LEGISLATION_CODE,
106 APPLICATION_ID,
107 FORM_NAME,
108 ENABLED_FLAG,
109 NAME,
110 COMMENTS,
111 LEGISLATION_SUBGROUP,
112 CREATION_DATE,
113 CREATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN
117 ) values (
118 X_CUSTOMIZED_RESTRICTION_ID,
119 X_BUSINESS_GROUP_ID,
120 X_LEGISLATION_CODE,
121 X_APPLICATION_ID,
122 X_FORM_NAME,
123 X_ENABLED_FLAG,
124 X_NAME,
125 X_COMMENTS,
126 X_LEGISLATION_SUBGROUP,
127 X_CREATION_DATE,
128 X_CREATED_BY,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATED_BY,
131 X_LAST_UPDATE_LOGIN
132 );
133
134 insert into PAY_CUSTOM_RESTRICTIONS_TL (
135 CUSTOMIZED_RESTRICTION_ID,
136 QUERY_FORM_TITLE,
137 STANDARD_FORM_TITLE,
138 CREATED_BY,
139 CREATION_DATE,
140 LAST_UPDATED_BY,
141 LAST_UPDATE_DATE,
142 LAST_UPDATE_LOGIN,
143 LANGUAGE,
144 SOURCE_LANG
145 ) select
146 X_CUSTOMIZED_RESTRICTION_ID,
147 X_QUERY_FORM_TITLE,
148 X_STANDARD_FORM_TITLE,
149 X_CREATED_BY,
150 X_CREATION_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_DATE,
153 X_LAST_UPDATE_LOGIN,
154 L.LANGUAGE_CODE,
155 userenv('LANG')
156 from FND_LANGUAGES L
157 where L.INSTALLED_FLAG in ('I', 'B')
158 and not exists
159 (select NULL
160 from PAY_CUSTOM_RESTRICTIONS_TL T
161 where T.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
162 and T.LANGUAGE = L.LANGUAGE_CODE);
163
164 open c;
165 fetch c into X_ROWID;
166 if (c%notfound) then
167 close c;
168 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
169 hr_utility.set_message_token('PROCEDURE',
170 'per_customized_restr_pkg.insert_row');
171 hr_utility.set_message_token('STEP','1');
172 hr_utility.raise_error;
173
174 raise no_data_found;
175 end if;
176 close c;
177
178 end INSERT_ROW;
179
180 procedure LOCK_ROW (
181 X_CUSTOMIZED_RESTRICTION_ID in NUMBER,
182 X_BUSINESS_GROUP_ID in NUMBER,
183 X_LEGISLATION_CODE in VARCHAR2,
184 X_APPLICATION_ID in NUMBER,
185 X_FORM_NAME in VARCHAR2,
186 X_ENABLED_FLAG in VARCHAR2,
187 X_NAME in VARCHAR2,
188 X_COMMENTS in LONG,
189 X_LEGISLATION_SUBGROUP in VARCHAR2,
190 X_QUERY_FORM_TITLE in VARCHAR2,
191 X_STANDARD_FORM_TITLE in VARCHAR2
192 ) is
193 cursor c is select
194 BUSINESS_GROUP_ID,
195 LEGISLATION_CODE,
196 APPLICATION_ID,
197 FORM_NAME,
198 ENABLED_FLAG,
199 NAME,
200 COMMENTS,
201 LEGISLATION_SUBGROUP
202 from PAY_CUSTOMIZED_RESTRICTIONS
203 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
204 for update of CUSTOMIZED_RESTRICTION_ID nowait;
205 recinfo c%rowtype;
206
207 cursor c1 is select
208 QUERY_FORM_TITLE,
209 STANDARD_FORM_TITLE,
210 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
211 from PAY_CUSTOM_RESTRICTIONS_TL
212 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
213 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
214 for update of CUSTOMIZED_RESTRICTION_ID nowait;
215 begin
216 open c;
217 fetch c into recinfo;
218 if (c%notfound) then
219 close c;
220 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
221 app_exception.raise_exception;
222 end if;
223 close c;
224 if ( ((recinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
225 OR ((recinfo.BUSINESS_GROUP_ID is null) AND (X_BUSINESS_GROUP_ID is null)))
226 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
227 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
228 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
229 AND (recinfo.FORM_NAME = X_FORM_NAME)
230 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
231 AND (recinfo.NAME = X_NAME)
232 AND ((recinfo.COMMENTS = X_COMMENTS)
233 OR ((recinfo.COMMENTS is null) AND (X_COMMENTS is null)))
234 AND ((recinfo.LEGISLATION_SUBGROUP = X_LEGISLATION_SUBGROUP)
235 OR ((recinfo.LEGISLATION_SUBGROUP is null) AND (X_LEGISLATION_SUBGROUP is null)))
236 ) then
237 null;
238 else
239 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
240 app_exception.raise_exception;
241 end if;
242
243 for tlinfo in c1 loop
244 if (tlinfo.BASELANG = 'Y') then
245 if ( (tlinfo.QUERY_FORM_TITLE = X_QUERY_FORM_TITLE)
246 AND (tlinfo.STANDARD_FORM_TITLE = X_STANDARD_FORM_TITLE)
247 ) then
248 null;
249 else
250 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
251 app_exception.raise_exception;
252 end if;
253 end if;
254 end loop;
255 return;
256 end LOCK_ROW;
257
258 procedure UPDATE_ROW (
259 X_CUSTOMIZED_RESTRICTION_ID in NUMBER,
260 X_BUSINESS_GROUP_ID in NUMBER,
261 X_LEGISLATION_CODE in VARCHAR2,
262 X_APPLICATION_ID in NUMBER,
263 X_FORM_NAME in VARCHAR2,
264 X_ENABLED_FLAG in VARCHAR2,
265 X_NAME in VARCHAR2,
266 X_COMMENTS in LONG,
267 X_LEGISLATION_SUBGROUP in VARCHAR2,
268 X_QUERY_FORM_TITLE in VARCHAR2,
269 X_STANDARD_FORM_TITLE in VARCHAR2,
270 X_LAST_UPDATE_DATE in DATE,
271 X_LAST_UPDATED_BY in NUMBER,
272 X_LAST_UPDATE_LOGIN in NUMBER
273 ) is
274 begin
275 update PAY_CUSTOMIZED_RESTRICTIONS set
276 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
277 LEGISLATION_CODE = X_LEGISLATION_CODE,
278 APPLICATION_ID = X_APPLICATION_ID,
279 FORM_NAME = X_FORM_NAME,
280 ENABLED_FLAG = X_ENABLED_FLAG,
281 NAME = X_NAME,
282 COMMENTS = X_COMMENTS,
283 LEGISLATION_SUBGROUP = X_LEGISLATION_SUBGROUP,
284 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
285 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
286 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
287 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;
288
289 if (sql%notfound) then
290 raise no_data_found;
291 end if;
292
293 update PAY_CUSTOM_RESTRICTIONS_TL set
294 QUERY_FORM_TITLE = X_QUERY_FORM_TITLE,
295 STANDARD_FORM_TITLE = X_STANDARD_FORM_TITLE,
296 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
297 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
298 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
299 SOURCE_LANG = userenv('LANG')
300 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
301 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
302
303 if (sql%notfound) then
304
305 insert into PAY_CUSTOM_RESTRICTIONS_TL (
306 CUSTOMIZED_RESTRICTION_ID,
307 QUERY_FORM_TITLE,
308 STANDARD_FORM_TITLE,
309 CREATED_BY,
310 CREATION_DATE,
311 LAST_UPDATED_BY,
312 LAST_UPDATE_DATE,
313 LAST_UPDATE_LOGIN,
314 LANGUAGE,
315 SOURCE_LANG
316 ) select
317 X_CUSTOMIZED_RESTRICTION_ID,
318 X_QUERY_FORM_TITLE,
319 X_STANDARD_FORM_TITLE,
320 0 ,
321 SYSDATE,
322 X_LAST_UPDATED_BY,
323 X_LAST_UPDATE_DATE,
324 X_LAST_UPDATE_LOGIN,
325 L.LANGUAGE_CODE,
326 userenv('LANG')
327 from FND_LANGUAGES L
328 where L.INSTALLED_FLAG in ('I', 'B')
329 and not exists
330 (select NULL
331 from PAY_CUSTOM_RESTRICTIONS_TL T
332 where T.CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID
333 and T.LANGUAGE = L.LANGUAGE_CODE);
334
335 end if;
336 end UPDATE_ROW;
337
338 procedure DELETE_ROW (
339 X_CUSTOMIZED_RESTRICTION_ID in NUMBER
340 ) is
341 begin
342 delete from PAY_CUSTOM_RESTRICTIONS_TL
343 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;
344
345 if (sql%notfound) then
346 raise no_data_found;
347 end if;
348
349 delete from PAY_CUSTOMIZED_RESTRICTIONS
350 where CUSTOMIZED_RESTRICTION_ID = X_CUSTOMIZED_RESTRICTION_ID;
351
352 if (sql%notfound) then
353 raise no_data_found;
354 end if;
355 end DELETE_ROW;
356
357
358 procedure LOAD_ROW
359 (X_APPLICATION_SHORT_NAME in varchar2,
360 X_FORM_NAME in VARCHAR2,
361 X_NAME in VARCHAR2,
362 X_BUSINESS_GROUP_NAME in VARCHAR2,
363 X_LEGISLATION_CODE in VARCHAR2,
364 X_ENABLED_FLAG in VARCHAR2,
365 X_QUERY_FORM_TITLE in VARCHAR2,
366 X_STANDARD_FORM_TITLE in VARCHAR2,
367 X_LEGISLATION_SUBGROUP in VARCHAR2,
368 X_OWNER in VARCHAR2
369 )
370 is
371 l_proc VARCHAR2(61) := 'PER_CUSTOMIZED_RESTR_PKG.LOAD_ROW';
372 l_rowid rowid;
373 l_created_by PAY_CUSTOMIZED_RESTRICTIONS.created_by%TYPE := 0;
374 l_creation_date PAY_CUSTOMIZED_RESTRICTIONS.creation_date%TYPE := SYSDATE;
375 l_last_update_date PAY_CUSTOMIZED_RESTRICTIONS.last_update_date%TYPE := SYSDATE;
376 l_last_updated_by PAY_CUSTOMIZED_RESTRICTIONS.last_updated_by%TYPE := 0;
377 l_last_update_login PAY_CUSTOMIZED_RESTRICTIONS.last_update_login%TYPE := 0;
378 l_cust_rest_id PAY_CUSTOMIZED_RESTRICTIONS.customized_restriction_id%TYPE ;
379 l_comments PAY_CUSTOMIZED_RESTRICTIONS.comments%TYPE ;
380 l_business_group_id PAY_CUSTOMIZED_RESTRICTIONS.business_group_id%TYPE;
381 l_appl_id PAY_CUSTOMIZED_RESTRICTIONS.application_id%TYPE;
382
383 CURSOR C_APPL IS
384 select application_id
385 from fnd_application
386 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
387
388 CURSOR C1 IS
389 select customized_restriction_id , comments , business_group_id
390 from PAY_CUSTOMIZED_RESTRICTIONS pcr
391 where pcr.application_id = l_appl_id
392 and pcr.form_name = X_FORM_NAME
393 and pcr.name = X_NAME
394 and nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX') ;
395
396 begin
397
398 -- Translate developer keys to internal parameters
399
400 if X_OWNER = 'SEED' then
401 l_created_by := 1;
402 l_last_updated_by := 1;
403 end if;
404
405 -- Update or insert row as appropriate
406 begin
407
408 OPEN C_APPL;
409 FETCH C_APPL INTO l_appl_id;
410 CLOSE C_APPL;
411
412
413 OPEN C1;
414 FETCH C1 INTO l_cust_rest_id , l_comments , l_business_group_id;
415
416 if (C1%NOTFOUND) then
417 close C1;
418 raise no_data_found;
419 end if;
420
421 close C1;
422
423 UPDATE_ROW
424 ( X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
425 ,X_APPLICATION_ID => l_appl_id
426 ,X_FORM_NAME => X_FORM_NAME
427 ,X_NAME => X_NAME
428 ,X_BUSINESS_GROUP_ID => l_business_group_id
429 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
430 ,X_ENABLED_FLAG => X_ENABLED_FLAG
431 ,X_QUERY_FORM_TITLE => X_QUERY_FORM_TITLE
432 ,X_STANDARD_FORM_TITLE => X_STANDARD_FORM_TITLE
433 ,X_COMMENTS => l_comments
434 ,X_LEGISLATION_SUBGROUP => X_LEGISLATION_SUBGROUP
435 ,X_LAST_UPDATE_DATE => l_last_update_date
436 ,X_LAST_UPDATED_BY => l_last_updated_by
437 ,X_LAST_UPDATE_LOGIN => l_last_update_login
438 );
439
440
441 exception
442 when no_data_found then
443 INSERT_ROW
444 (X_ROWID => l_rowid
445 ,X_CUSTOMIZED_RESTRICTION_ID => l_cust_rest_id
446 ,X_APPLICATION_ID => l_appl_id
447 ,X_FORM_NAME => X_FORM_NAME
448 ,X_NAME => X_NAME
449 ,X_BUSINESS_GROUP_ID => null
450 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
451 ,X_ENABLED_FLAG => X_ENABLED_FLAG
452 ,X_QUERY_FORM_TITLE => X_QUERY_FORM_TITLE
453 ,X_STANDARD_FORM_TITLE => X_STANDARD_FORM_TITLE
454 ,X_COMMENTS => l_comments
455 ,X_LEGISLATION_SUBGROUP => X_LEGISLATION_SUBGROUP
456 ,X_CREATED_BY => l_created_by
457 ,X_CREATION_DATE => l_creation_date
458 ,X_LAST_UPDATE_DATE => l_last_update_date
459 ,X_LAST_UPDATED_BY => l_last_updated_by
460 ,X_LAST_UPDATE_LOGIN => l_last_update_login
461 );
462 end;
463 --
464 end LOAD_ROW;
465
466 procedure TRANSLATE_ROW
467 (X_APPLICATION_SHORT_NAME in varchar2,
468 X_FORM_NAME in VARCHAR2,
469 X_NAME in VARCHAR2,
470 X_BUSINESS_GROUP_NAME in VARCHAR2,
471 X_LEGISLATION_CODE in VARCHAR2,
472 X_QUERY_FORM_TITLE in VARCHAR2,
473 X_STANDARD_FORM_TITLE in VARCHAR2,
474 X_OWNER in varchar2
475 )
476 is
477 l_appl_id PAY_CUSTOMIZED_RESTRICTIONS.application_id%TYPE;
478 l_cust_rest_id PAY_CUSTOMIZED_RESTRICTIONS.customized_restriction_id%TYPE ;
479
480 CURSOR C_APPL IS
481 select application_id
482 from fnd_application
483 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
484
485 CURSOR C1 IS
486 select customized_restriction_id
487 from PAY_CUSTOMIZED_RESTRICTIONS pcr
488 where pcr.application_id = l_appl_id
489 and pcr.form_name = X_FORM_NAME
490 and pcr.name = X_NAME
491 and nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX') ;
492
493 begin
494
495 OPEN C_APPL;
496 FETCH C_APPL INTO l_appl_id;
497 CLOSE C_APPL;
498
499 OPEN C1;
500 FETCH C1 INTO l_cust_rest_id ;
501 IF C1%FOUND THEN
502
503 UPDATE PAY_CUSTOM_RESTRICTIONS_TL
504 SET
505 QUERY_FORM_TITLE = X_QUERY_FORM_TITLE ,
506 STANDARD_FORM_TITLE = X_STANDARD_FORM_TITLE ,
507 LAST_UPDATE_DATE = sysdate ,
508 LAST_UPDATED_BY = decode(X_OWNER , 'SEED', 1, 0),
509 LAST_UPDATE_LOGIN = 0,
510 SOURCE_LANG = userenv('LANG')
511 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
512 and CUSTOMIZED_RESTRICTION_ID = l_cust_rest_id;
513
514 END IF;
515
516 CLOSE C1;
517
518 end TRANSLATE_ROW;
519
520 procedure ADD_LANGUAGE
521 is
522 begin
523 delete from PAY_CUSTOM_RESTRICTIONS_TL T
524 where not exists
525 (select NULL
526 from PAY_CUSTOMIZED_RESTRICTIONS B
527 where B.CUSTOMIZED_RESTRICTION_ID = T.CUSTOMIZED_RESTRICTION_ID
528 );
529
530 update PAY_CUSTOM_RESTRICTIONS_TL T set (
531 QUERY_FORM_TITLE,
532 STANDARD_FORM_TITLE
533 ) = (select
534 B.QUERY_FORM_TITLE,
535 B.STANDARD_FORM_TITLE
536 from PAY_CUSTOM_RESTRICTIONS_TL B
537 where B.CUSTOMIZED_RESTRICTION_ID = T.CUSTOMIZED_RESTRICTION_ID
538 and B.LANGUAGE = T.SOURCE_LANG)
539 where (
540 T.CUSTOMIZED_RESTRICTION_ID,
541 T.LANGUAGE
542 ) in (select
543 SUBT.CUSTOMIZED_RESTRICTION_ID,
544 SUBT.LANGUAGE
545 from PAY_CUSTOM_RESTRICTIONS_TL SUBB, PAY_CUSTOM_RESTRICTIONS_TL SUBT
546 where SUBB.CUSTOMIZED_RESTRICTION_ID = SUBT.CUSTOMIZED_RESTRICTION_ID
547 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
548 and (SUBB.QUERY_FORM_TITLE <> SUBT.QUERY_FORM_TITLE
549 or SUBB.STANDARD_FORM_TITLE <> SUBT.STANDARD_FORM_TITLE
550 ));
551
552 insert into PAY_CUSTOM_RESTRICTIONS_TL (
553 CUSTOMIZED_RESTRICTION_ID,
554 QUERY_FORM_TITLE,
555 STANDARD_FORM_TITLE,
556 CREATED_BY,
557 CREATION_DATE,
558 LAST_UPDATED_BY,
559 LAST_UPDATE_DATE,
560 LAST_UPDATE_LOGIN,
561 LANGUAGE,
562 SOURCE_LANG
563 ) select
564 B.CUSTOMIZED_RESTRICTION_ID,
565 B.QUERY_FORM_TITLE,
566 B.STANDARD_FORM_TITLE,
567 B.CREATED_BY,
568 B.CREATION_DATE,
569 B.LAST_UPDATED_BY,
570 B.LAST_UPDATE_DATE,
571 B.LAST_UPDATE_LOGIN,
572 L.LANGUAGE_CODE,
573 B.SOURCE_LANG
574 from PAY_CUSTOM_RESTRICTIONS_TL B, FND_LANGUAGES L
575 where L.INSTALLED_FLAG in ('I', 'B')
576 and B.LANGUAGE = userenv('LANG')
577 and not exists
578 (select NULL
579 from PAY_CUSTOM_RESTRICTIONS_TL T
580 where T.CUSTOMIZED_RESTRICTION_ID = B.CUSTOMIZED_RESTRICTION_ID
581 and T.LANGUAGE = L.LANGUAGE_CODE);
582 end ADD_LANGUAGE;
583
584 END PER_CUSTOMIZED_RESTR_PKG;