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