[Home] [Help]
PACKAGE BODY: APPS.PER_EIT_UTILITY_SS
Source
1 PACKAGE BODY PER_EIT_UTILITY_SS AS
2 /* $Header: hreitutl.pkb 120.0 2005/05/31 00:04:30 appldev noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |-----------------------< EIT_NOT_EXIST >--------------------------|
6 -- ----------------------------------------------------------------------------
7
8
9 FUNCTION EIT_NOT_EXIST (P_APPLICATION_SHORT_NAME VARCHAR2,
10 P_RESPONSIBILITY_NAME VARCHAR2,
11 P_INFO_TYPE_TABLE_NAME VARCHAR2,
12 P_INFORMATION_TYPE VARCHAR2,
13 P_ROWID VARCHAR2) RETURN BOOLEAN
14 IS
15 L_DUMMY1 number;
16
17 l_appl_id number;
18 l_resp_id number;
19 CURSOR C_APPL IS
20 select application_id
21 from fnd_application
22 where application_short_name = upper(P_APPLICATION_SHORT_NAME);
23 CURSOR C_RESP IS
24 select responsibility_id
25 from fnd_responsibility_vl
26 where responsibility_name = P_RESPONSIBILITY_NAME
27 and application_id = l_appl_id;
28
29 CURSOR C1 (c1_p_appl_id number, c1_p_resp_id number) IS
30
31 select 1
32 from PER_INFO_TYPE_SECURITY t
33 where t.application_id = c1_p_appl_id
34 and t.responsibility_id = c1_p_resp_id
35 and t.info_type_table_name = P_INFO_TYPE_TABLE_NAME
36 and t.information_type = P_INFORMATION_TYPE
37 and (P_ROWID is null
38 or P_ROWID <> t.rowid);
39 BEGIN
40 OPEN C_APPL;
41 FETCH C_APPL INTO l_appl_id;
42 CLOSE C_APPL;
43 OPEN C_RESP;
44 FETCH C_RESP INTO l_resp_id;
45 CLOSE C_RESP;
46 OPEN C1(l_appl_id, l_resp_id);
47 FETCH C1 INTO L_DUMMY1;
48 IF C1%NOTFOUND THEN
49 CLOSE C1;
50 return true;
51 ELSE
52 CLOSE C1;
53 return false;
54 END IF;
55
56 end EIT_NOT_EXIST;
57
58
59 -- ----------------------------------------------------------------------------
60 -- |-----------------------< GET_RESP_KEY >--------------------------|
61 -- ----------------------------------------------------------------------------
62
63
64 FUNCTION GET_RESP_KEY (P_APPLICATION_SHORT_NAME VARCHAR2,
65 P_RESPONSIBILITY_NAME VARCHAR2) RETURN VARCHAR2
66 IS
67 l_resp_key varchar2(30) := null;
68 l_appl_id number;
69 CURSOR C_APPL IS
70 select application_id
71 from fnd_application
72 where application_short_name = upper(P_APPLICATION_SHORT_NAME);
73 CURSOR RESP_KEY (resp_appl_id number, resp_resp_name varchar2)IS
74 select responsibility_key
75 from fnd_responsibility_vl
76 where responsibility_name = resp_resp_name
77 and application_id = resp_appl_id;
78 BEGIN
79 OPEN C_APPL;
80 FETCH C_APPL INTO l_appl_id;
81 CLOSE C_APPL;
82 OPEN RESP_KEY(l_appl_id, P_RESPONSIBILITY_NAME);
83 FETCH RESP_KEY INTO l_resp_key;
84 IF RESP_KEY%NOTFOUND THEN
85 CLOSE RESP_KEY;
86 return l_resp_key;
87 ELSE
88 CLOSE RESP_KEY;
89 return l_resp_key;
90 END IF;
91
92 end GET_RESP_KEY;
93
94
95
96 -- ----------------------------------------------------------------------------
97 -- |-----------------------< create_eit_resp_security >--------------------------|
98 -- ----------------------------------------------------------------------------
99
100 PROCEDURE create_eit_resp_security (P_RESPONSIBILITY_NAME IN VARCHAR2)
101 IS
102
103 cursor c_person_types is
104 select information_type
105 from per_people_info_types
106 where information_type not like 'GHR%';
107
108 cursor c_job_types is
109 select information_type
110 from per_job_info_types
111 where information_type not like 'GHR%';
112
113 cursor c_assignment_types is
114 select information_type
115 from per_assignment_info_types
116 where information_type not like 'GHR%';
117
118 cursor c_position_types is
119 select information_type
120 from per_position_info_types
121 where information_type not like 'GHR%';
122
123 cursor c_location_types is
124 select information_type
125 from hr_location_info_types
126 where information_type not like 'GHR%';
127
128 l_row_id varchar2(80) := null;
129
130 begin
131
132 -----------------------------------------
133 --------------PERSON ---------
134 -----------------------------------------
135 FOR person_type IN c_person_types LOOP
136
137 if eit_not_exist(P_APPLICATION_SHORT_NAME => 'PER',
138 P_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
139 P_INFO_TYPE_TABLE_NAME => 'PER_PEOPLE_INFO_TYPES',
140 P_INFORMATION_TYPE => person_type.information_type,
141 P_ROWID => l_row_id)
142 THEN
143
144 PER_PEOPLE_INFO_TYPES_SEC_PKG.INSERT_ROW
145 (x_rowid => l_row_id,
146 x_application_short_name => 'PER',
147 --X_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
148 X_RESPONSIBILITY_KEY => GET_RESP_KEY('PER', P_RESPONSIBILITY_NAME),
149 X_INFO_TYPE_TABLE_NAME => 'PER_PEOPLE_INFO_TYPES',
150 X_INFORMATION_TYPE => person_type.information_type,
151 X_OBJECT_VERSION_NUMBER => 1,
152 X_CREATION_DATE => sysdate,
153 X_CREATED_BY => -1,
154 X_LAST_UPDATE_DATE => sysdate,
155 X_LAST_UPDATED_BY => -1,
156 X_LAST_UPDATE_LOGIN => -1);
157
158 END IF;
159 END LOOP;
160
161 -----------------------------------------
162 --------------JOB ---------
163 -----------------------------------------
164 FOR job_type IN c_job_types LOOP
165
166 if eit_not_exist(P_APPLICATION_SHORT_NAME => 'PER',
167 P_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
168 P_INFO_TYPE_TABLE_NAME => 'PER_JOB_INFO_TYPES',
169 P_INFORMATION_TYPE => job_type.information_type,
170 P_ROWID => l_row_id)
171 THEN
172
173 PER_PEOPLE_INFO_TYPES_SEC_PKG.INSERT_ROW
174 (x_rowid => l_row_id,
175 x_application_short_name => 'PER',
176 --X_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
177 X_RESPONSIBILITY_KEY => GET_RESP_KEY('PER', P_RESPONSIBILITY_NAME),
178 X_INFO_TYPE_TABLE_NAME => 'PER_JOB_INFO_TYPES',
179 X_INFORMATION_TYPE => job_type.information_type,
180 X_OBJECT_VERSION_NUMBER => 1,
181 X_CREATION_DATE => sysdate,
182 X_CREATED_BY => -1,
183 X_LAST_UPDATE_DATE => sysdate,
184 X_LAST_UPDATED_BY => -1,
185 X_LAST_UPDATE_LOGIN => -1);
186
187 END IF;
188
189 END LOOP;
190
191 -----------------------------------------
192 --------------ASSIGNMENT ---------
193 -----------------------------------------
194
195 FOR assignment_type IN c_assignment_types LOOP
196
197 if eit_not_exist(P_APPLICATION_SHORT_NAME => 'PER',
198 P_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
199 P_INFO_TYPE_TABLE_NAME => 'PER_ASSIGNMENT_INFO_TYPES',
200 P_INFORMATION_TYPE => assignment_type.information_type,
201 P_ROWID => l_row_id)
202 THEN
203
204 PER_PEOPLE_INFO_TYPES_SEC_PKG.INSERT_ROW
205 (x_rowid => l_row_id,
206 x_application_short_name => 'PER',
207 --X_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
208 X_RESPONSIBILITY_KEY => GET_RESP_KEY('PER', P_RESPONSIBILITY_NAME),
209 X_INFO_TYPE_TABLE_NAME => 'PER_ASSIGNMENT_INFO_TYPES',
210 X_INFORMATION_TYPE => assignment_type.information_type,
211 X_OBJECT_VERSION_NUMBER => 1,
212 X_CREATION_DATE => sysdate,
213 X_CREATED_BY => -1,
214 X_LAST_UPDATE_DATE => sysdate,
215 X_LAST_UPDATED_BY => -1,
216 X_LAST_UPDATE_LOGIN => -1);
217
218 END IF;
219
220 END LOOP;
221
222 -----------------------------------------
223 --------------POSITION ---------
224 -----------------------------------------
225
226 FOR position_type IN c_position_types LOOP
227
228 if eit_not_exist(P_APPLICATION_SHORT_NAME => 'PER',
229 P_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
230 P_INFO_TYPE_TABLE_NAME => 'PER_POSITION_INFO_TYPES',
231 P_INFORMATION_TYPE => position_type.information_type,
232 P_ROWID => l_row_id)
233 THEN
234
235 PER_PEOPLE_INFO_TYPES_SEC_PKG.INSERT_ROW
236 (x_rowid => l_row_id,
237 x_application_short_name => 'PER',
238 --X_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
239 X_RESPONSIBILITY_KEY => GET_RESP_KEY('PER', P_RESPONSIBILITY_NAME),
240 X_INFO_TYPE_TABLE_NAME => 'PER_POSITION_INFO_TYPES',
241 X_INFORMATION_TYPE => position_type.information_type,
242 X_OBJECT_VERSION_NUMBER => 1,
243 X_CREATION_DATE => sysdate,
244 X_CREATED_BY => -1,
245 X_LAST_UPDATE_DATE => sysdate,
246 X_LAST_UPDATED_BY => -1,
247 X_LAST_UPDATE_LOGIN => -1);
248
249 END IF;
250
251 END LOOP;
252
253 -----------------------------------------
254 --------------LOCATION ---------
255 -----------------------------------------
256
257 FOR location_type IN c_location_types LOOP
258
259 if eit_not_exist(P_APPLICATION_SHORT_NAME => 'PER',
260 P_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
261 P_INFO_TYPE_TABLE_NAME => 'HR_LOCATION_NINFO_TYPES',
262 P_INFORMATION_TYPE => location_type.information_type,
263 P_ROWID => l_row_id)
264 THEN
265
266 PER_PEOPLE_INFO_TYPES_SEC_PKG.INSERT_ROW
267 (x_rowid => l_row_id,
268 x_application_short_name => 'PER',
269 --X_RESPONSIBILITY_NAME => P_RESPONSIBILITY_NAME,
270 X_RESPONSIBILITY_KEY => GET_RESP_KEY('PER', P_RESPONSIBILITY_NAME),
271 X_INFO_TYPE_TABLE_NAME => 'HR_LOCATION_INFO_TYPES',
272 X_INFORMATION_TYPE => location_type.information_type,
273 X_OBJECT_VERSION_NUMBER => 1,
274 X_CREATION_DATE => sysdate,
275 X_CREATED_BY => -1,
276 X_LAST_UPDATE_DATE => sysdate,
277 X_LAST_UPDATED_BY => -1,
278 X_LAST_UPDATE_LOGIN => -1);
279
280 END IF;
281
282 END LOOP;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 null;
287
288 end create_eit_resp_security;
289
290 END;