[Home] [Help]
PACKAGE BODY: APPS.PER_RESTR_VALUES_PKG
Source
1 PACKAGE BODY PER_RESTR_VALUES_PKG as
2 /* $Header: perpeprv.pkb 115.3 2002/12/06 14:46:06 eumenyio noship $ */
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_APPLICATION_SHORT_NAME VARCHAR2,
10 P_FORM_NAME VARCHAR2,
11 P_NAME VARCHAR2,
12 P_BUSINESS_GROUP_NAME VARCHAR2,
13 P_LEGISLATION_CODE VARCHAR2,
14 P_RESTRICTION_CODE VARCHAR2,
15 P_VALUE VARCHAR2,
16 P_ROWID VARCHAR2)
17 IS
18 L_DUMMY1 number;
19 l_appl_id number;
20 l_cr_id number;
21 CURSOR C_APPL IS
22 select application_id
23 from fnd_application
24 where application_short_name = upper(P_APPLICATION_SHORT_NAME);
25 cursor C_CR_ID is
26 select customized_restriction_id
27 from PAY_CUSTOMIZED_RESTRICTIONS pcr
28 where
29 pcr.application_id = l_appl_id
30 and pcr.form_name = P_FORM_NAME
31 and pcr.name = P_NAME
32 and pcr.legislation_code = P_LEGISLATION_CODE;
33 CURSOR C1 IS
34 select 1
35 from PAY_RESTRICTION_VALUES prv
36 where prv.customized_restriction_id = l_cr_id
37 and prv.restriction_code = P_RESTRICTION_CODE
38 and prv.value = P_VALUE
39 and (P_ROWID is null
40 or P_ROWID <> prv.rowid);
41 BEGIN
42 OPEN C_APPL;
43 FETCH C_APPL INTO l_appl_id;
44 CLOSE C_APPL;
45 OPEN C_CR_ID;
46 FETCH C_CR_ID INTO l_cr_id;
47 CLOSE C_CR_ID;
48 OPEN C1;
49 FETCH C1 INTO L_DUMMY1;
50 IF C1%NOTFOUND THEN
51 CLOSE C1;
52 ELSE
53 CLOSE C1;
54 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
55 hr_utility.raise_error;
56 END IF;
57 end UNIQUENESS_CHECK;
58 --
59 procedure INSERT_ROW (
60 X_ROWID in out nocopy VARCHAR2,
61 X_APPLICATION_SHORT_NAME in VARCHAR2,
62 X_FORM_NAME in VARCHAR2,
63 X_NAME in VARCHAR2,
64 X_BUSINESS_GROUP_NAME in VARCHAR2,
65 X_LEGISLATION_CODE in VARCHAR2,
66 X_RESTRICTION_CODE in VARCHAR2,
67 X_VALUE in VARCHAR2,
68 X_CREATION_DATE in DATE,
69 X_CREATED_BY in NUMBER,
70 X_LAST_UPDATE_DATE in DATE,
71 X_LAST_UPDATED_BY in NUMBER,
72 X_LAST_UPDATE_LOGIN in NUMBER
73 ) is
74 l_cr_id number;
75 l_appl_id number;
76 cursor C_APPL is
77 select application_id
78 from fnd_application
79 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
80 cursor C_CR_ID is
81 select customized_restriction_id
82 from PAY_CUSTOMIZED_RESTRICTIONS pcr
83 where
84 pcr.application_id = l_appl_id
85 and pcr.form_name = X_FORM_NAME
86 and pcr.name = X_NAME
87 and (X_LEGISLATION_CODE is null or
88 (X_LEGISLATION_CODE is not null
89 and pcr.legislation_code = X_LEGISLATION_CODE));
90 cursor C is
91 select ROWID
92 from PAY_RESTRICTION_VALUES prv
93 where prv.customized_restriction_id = l_cr_id
94 and prv.value = X_VALUE
95 and prv.restriction_code = X_RESTRICTION_CODE
96 ;
97 begin
98 open C_APPL;
99 fetch C_APPL into l_appl_id;
100 close C_APPL;
101 open C_CR_ID;
102 fetch C_CR_ID into l_cr_id;
103 close C_CR_ID;
104 insert into PAY_RESTRICTION_VALUES (
105 CUSTOMIZED_RESTRICTION_ID,
106 RESTRICTION_CODE,
107 VALUE,
108 CREATION_DATE,
109 CREATED_BY,
110 LAST_UPDATE_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_LOGIN
113 ) values (
114 l_cr_id,
115 X_RESTRICTION_CODE,
116 X_VALUE,
117 X_CREATION_DATE,
118 X_CREATED_BY,
119 X_LAST_UPDATE_DATE,
120 X_LAST_UPDATED_BY,
121 X_LAST_UPDATE_LOGIN
122 );
123
124 open c;
125 fetch c into X_ROWID;
126 if (c%notfound) then
127 close c;
128 raise no_data_found;
129 end if;
130 close c;
131
132 end INSERT_ROW;
133
134 procedure LOCK_ROW (
135 X_APPLICATION_SHORT_NAME VARCHAR2,
136 X_FORM_NAME in VARCHAR2,
137 X_NAME in VARCHAR2,
138 X_BUSINESS_GROUP_NAME in VARCHAR2,
139 X_LEGISLATION_CODE in VARCHAR2,
140 X_RESTRICTION_CODE in VARCHAR2,
141 X_VALUE in VARCHAR2
142 ) is
143 l_appl_id number;
144 l_cr_id number;
145 cursor C_APPL is
146 select application_id
147 from fnd_application
148 where application_short_name = upper(X_APPLICATION_SHORT_NAME);
149 cursor C_CR_ID is
150 select customized_restriction_id
151 from PAY_CUSTOMIZED_RESTRICTIONS pcr
152 where
153 pcr.application_id = l_appl_id
154 and pcr.form_name = X_FORM_NAME
155 and pcr.name = X_NAME
156 and nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX');
157 cursor c is select
158 RESTRICTION_CODE,
159 VALUE
160 from PAY_RESTRICTION_VALUES prv
161 where prv.CUSTOMIZED_RESTRICTION_ID = l_cr_id
162 and prv.value = X_VALUE
163 and prv.restriction_code = X_RESTRICTION_CODE
164 for update of VALUE nowait;
165 recinfo c%rowtype;
166
167 begin
168 open C_APPL;
169 fetch C_APPL into l_appl_id;
170 close C_APPL;
171 open C_CR_ID;
172 fetch C_CR_ID into l_cr_id;
173 close C_CR_ID;
174 open c;
175 fetch c into recinfo;
176 if (c%notfound) then
177 close c;
178 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
179 app_exception.raise_exception;
180 end if;
181 close c;
182 if ( (recinfo.RESTRICTION_CODE = X_RESTRICTION_CODE)
183 AND (recinfo.VALUE = X_VALUE)
184 ) then
185 null;
186 else
187 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
188 app_exception.raise_exception;
189 end if;
190
191 return;
192 end LOCK_ROW;
193
194 procedure UPDATE_ROW (
195 X_APPLICATION_SHORT_NAME in VARCHAR2,
196 X_FORM_NAME in VARCHAR2,
197 X_NAME in VARCHAR2,
198 X_BUSINESS_GROUP_NAME in VARCHAR2,
199 X_LEGISLATION_CODE in VARCHAR2,
200 X_RESTRICTION_CODE in VARCHAR2,
201 X_VALUE in VARCHAR2,
202 X_RESTRICTION_CODE_NEW in VARCHAR2,
203 X_VALUE_NEW in VARCHAR2,
204 X_LAST_UPDATE_DATE in DATE,
205 X_LAST_UPDATED_BY in NUMBER,
206 X_LAST_UPDATE_LOGIN in NUMBER
207 ) is
208 l_appl_id number;
209 l_cr_id number;
210 cursor C_APPL is
211 select application_id
212 from fnd_application
213 where application_short_name = X_APPLICATION_SHORT_NAME;
214 cursor C_CR_ID is
215 select customized_restriction_id
216 from PAY_CUSTOMIZED_RESTRICTIONS pcr
217 where
218 pcr.application_id = l_appl_id
219 and pcr.form_name = X_FORM_NAME
220 and pcr.name = X_NAME
221 and nvl(pcr.legislation_code,'XXX') = nvl(X_LEGISLATION_CODE,'XXX');
222 begin
223 open C_APPL;
224 fetch C_APPL into l_appl_id;
225 close C_APPL;
226 open C_CR_ID;
227 fetch C_CR_ID into l_cr_id;
228 close C_CR_ID;
229 update PAY_RESTRICTION_VALUES set
230 RESTRICTION_CODE = X_RESTRICTION_CODE_NEW ,
231 VALUE = X_VALUE_NEW,
232 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
233 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
234 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
235 where CUSTOMIZED_RESTRICTION_ID = l_cr_id
236 and value = X_VALUE
237 and restriction_code = X_RESTRICTION_CODE;
238
239 if (sql%notfound) then
240 raise no_data_found;
241 end if;
242
243 end UPDATE_ROW;
244
245 procedure DELETE_ROW (
246 X_APPLICATION_SHORT_NAME in VARCHAR2,
247 X_FORM_NAME in VARCHAR2,
248 X_NAME in VARCHAR2,
249 X_BUSINESS_GROUP_NAME in VARCHAR2,
250 X_LEGISLATION_CODE in VARCHAR2,
251 X_RESTRICTION_CODE in VARCHAR2,
252 X_VALUE in VARCHAR2
253 ) is
254 l_appl_id number;
255 l_cr_id number;
256 cursor C_APPL is
257 select application_id
258 from fnd_application
259 where application_short_name = X_APPLICATION_SHORT_NAME;
260 cursor C_CR_ID is
261 select customized_restriction_id
262 from PAY_CUSTOMIZED_RESTRICTIONS pcr
263 where
264 pcr.application_id = l_appl_id
265 and pcr.form_name = X_FORM_NAME
266 and pcr.name = X_NAME
267 and pcr.legislation_code = X_LEGISLATION_CODE;
268 begin
269 open C_APPL;
270 fetch C_APPL into l_appl_id;
271 close C_APPL;
272 open C_CR_ID;
273 fetch C_CR_ID into l_cr_id;
274 close C_CR_ID;
275
276 delete from PAY_RESTRICTION_VALUES
277 where CUSTOMIZED_RESTRICTION_ID = l_cr_id
278 and value = X_VALUE
279 and restriction_code = X_RESTRICTION_CODE;
280
281 if (sql%notfound) then
282 raise no_data_found;
283 end if;
284 end DELETE_ROW;
285
286 procedure LOAD_ROW
287 (X_APPLICATION_SHORT_NAME in varchar2,
288 X_FORM_NAME in VARCHAR2,
289 X_NAME in VARCHAR2,
290 X_BUSINESS_GROUP_NAME in VARCHAR2,
291 X_LEGISLATION_CODE in VARCHAR2,
292 X_RESTRICTION_CODE in VARCHAR2,
293 X_VALUE in VARCHAR2,
294 X_OWNER in VARCHAR2
295 )
296 is
297 l_proc VARCHAR2(61) := 'PER_RESTR_VALUES_PKG.LOAD_ROW';
298 l_rowid rowid;
299 l_created_by PAY_RESTRICTION_VALUES.created_by%TYPE := 0;
300 l_creation_date PAY_RESTRICTION_VALUES.creation_date%TYPE := SYSDATE;
301 l_last_update_date PAY_RESTRICTION_VALUES.last_update_date%TYPE := SYSDATE;
302 l_last_updated_by PAY_RESTRICTION_VALUES.last_updated_by%TYPE := 0;
303 l_last_update_login PAY_RESTRICTION_VALUES.last_update_login%TYPE := 0;
304 begin
305 -- Translate developer keys to internal parameters
306 if X_OWNER = 'SEED' then
307 l_created_by := 1;
308 l_last_updated_by := 1;
309 end if;
310 -- Update or insert row as appropriate
311 begin
312 UPDATE_ROW
313 (X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME
314 ,X_FORM_NAME => X_FORM_NAME
315 ,X_NAME => X_NAME
316 ,X_BUSINESS_GROUP_NAME => X_BUSINESS_GROUP_NAME
317 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
318 ,X_RESTRICTION_CODE => X_RESTRICTION_CODE
319 ,X_VALUE => X_VALUE
320 ,X_RESTRICTION_CODE_NEW => X_RESTRICTION_CODE
321 ,X_VALUE_NEW => X_VALUE
322 ,X_LAST_UPDATE_DATE => l_last_update_date
323 ,X_LAST_UPDATED_BY => l_last_updated_by
324 ,X_LAST_UPDATE_LOGIN => l_last_update_login
325 );
326 exception
327 when no_data_found then
328 INSERT_ROW
329 (X_ROWID => l_rowid
330 ,X_APPLICATION_SHORT_NAME => X_APPLICATION_SHORT_NAME
331 ,X_FORM_NAME => X_FORM_NAME
332 ,X_NAME => X_NAME
333 ,X_BUSINESS_GROUP_NAME => X_BUSINESS_GROUP_NAME
334 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
335 ,X_RESTRICTION_CODE => X_RESTRICTION_CODE
336 ,X_VALUE => X_VALUE
337 ,X_CREATED_BY => l_created_by
338 ,X_CREATION_DATE => l_creation_date
339 ,X_LAST_UPDATE_DATE => l_last_update_date
340 ,X_LAST_UPDATED_BY => l_last_updated_by
341 ,X_LAST_UPDATE_LOGIN => l_last_update_login
342 );
343 end;
344 --
345 end LOAD_ROW;
346
347 procedure TRANSLATE_ROW
348 (X_APPLICATION_SHORT_NAME in varchar2,
349 X_FORM_NAME in VARCHAR2,
350 X_NAME in VARCHAR2,
351 X_BUSINESS_GROUP_NAME in VARCHAR2,
352 X_LEGISLATION_CODE in VARCHAR2,
353 X_RESTRICTION_CODE in VARCHAR2,
354 X_VALUE in VARCHAR2,
355 X_OWNER in varchar2
356 )
357 is
358 begin
359 null;
360 /*
361 UPDATE per_info_type_security
362 SET description = X_DESCRIPTION
363 ,last_update_date = SYSDATE
364 ,last_updated_by = DECODE(X_OWNER,'SEED',1,0)
365 ,last_update_login = 0
366 WHERE USERENV('LANG') = (select language_code from fnd_languages
367 where installed_flag = 'B')
368 AND application_id = l_appl_id
369 AND responsibility_id = l_resp_id
370 AND info_type_table_name = X_INFO_TYPE_TABLE_NAME
371 AND information_type = X_INFORMATION_TYPE;
372 */
373 end TRANSLATE_ROW;
374
375
376 END PER_RESTR_VALUES_PKG;