1 PACKAGE BODY PER_POS_STRUCT_ELEMENTS_PKG2 as
2 /* $Header: pepse02t.pkb 115.6 2002/12/04 16:15:24 eumenyio ship $ */
3
4 procedure p_root_test(p_root out nocopy boolean,
5 X_Business_Group_Id NUMBER,
6 X_Pos_Structure_Version_Id NUMBER,
7 X_Position_Id NUMBER) is
8 l_dummy varchar2(1);
9 begin
10 /*
11 This step checks if the hierarchy has any POSITIONs entered under it.
12 If the hierarchy has no POSITIONs, the child pos is treated as though
13 it is the top of the hierarchy structure.
14 */
15 l_dummy := NULL;
16 p_root := FALSE;
17
18 begin
19 select '1'
20 into l_dummy
21 from per_pos_structure_elements a
22 where a.business_group_id + 0 = X_Business_Group_Id
23 and a.POS_structure_version_id = X_Pos_Structure_Version_Id;
24 exception when no_data_found then
25 null;
26 when too_many_rows then
27 null;
28 end;
29
30 if l_dummy <> '1' then
31 p_root := TRUE;
32
33 else
34 /*
35 This step checks if the child POSITION is the top POSITION in
36 the hierarchy (ie it is not ever a child in this hierarchy).
37 */
38
39 l_dummy := NULL;
40
41 begin
42 select '1'
43 into l_dummy
44 from per_pos_structure_elements ose
45 where ose.business_group_id + 0 = X_Business_Group_Id
46 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
47 and exists
48 (select null
49 from per_pos_structure_elements a
50 where a.POS_structure_version_id = X_Pos_Structure_Version_Id
51 and a.parent_position_id = X_Position_Id)
52 and not exists
53 (select null
54 from per_pos_structure_elements b
55 where b.POS_structure_version_id = X_Pos_Structure_Version_Id
56 and b.subordinate_position_id = X_Position_Id);
57
58 exception when no_data_found then
59 null;
60 when too_many_rows then
61 null;
62 end;
63
64 if l_dummy = '1' then
65 p_root := TRUE;
66 end if;
67
68 end if;
69 end;
70
71 PROCEDURE parent_insert_update_checks (X_Business_Group_Id NUMBER,
72 X_Pos_Structure_Version_Id NUMBER,
73 X_Position_Id NUMBER,
74 X_Parent_Position_Id NUMBER,
75 X_Subordinate_Position_Id NUMBER) is
76 l_root boolean;
77 l_dummy varchar2(1);
78 begin
79 p_root_test(l_root,
80 X_Business_Group_Id,
81 X_Pos_Structure_Version_Id,
82 X_Position_Id);
83
84 if l_root then
85 begin
86 /*
87 This step checks that the parent POSITION being linked to the
88 root POSITION does not already exist in the hierarchy.
89 The parent of a root POSITION should not already exist in the hierarchy
90 */
91 select '1'
92 into l_dummy
93 from per_pos_structure_elements ose
94 where (ose.subordinate_position_id = X_Parent_Position_Id
95 or ose.parent_position_id = X_Parent_Position_Id)
96 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
97 and ose.business_group_id + 0 = X_Business_Group_Id;
98 exception when no_data_found then
99 null;
100 when too_many_rows then
101 null;
102 end;
103
104 if l_dummy = '1' then
105 hr_utility.set_message(801,'PER_7420_POS_PARENT_EXISTS');
106 hr_utility.raise_error;
107 end if;
108 else
109 /* Non root
110 This step checks that the parent POSITION exists in the hierarchy.
111 The parent of a non-root POSITION must already exist in the hierarchy.
112 */
113 l_dummy :=NULL;
114 begin
115 select '1'
116 into l_dummy
117 from per_pos_structure_elements ose
118 where (ose.subordinate_position_id = X_Parent_Position_Id
119 or ose.parent_position_id = X_Parent_Position_Id)
120 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
121 and ose.business_group_id + 0 = X_Business_Group_Id;
122 exception when no_data_found then
123 null;
124 when too_many_rows then
125 null;
126 end;
127
128 if l_dummy <> '1' then
129 hr_utility.set_message(801,'PER_7419_POS_PARENT_NEEDED');
130 hr_utility.raise_error;
131 else
132 /*
133 This step checks that the parent of the non-root POSITION is not
134 below the non-root POSITION. The parent must not be the non-root
135 POSITION or any of its children.
136 */
137 l_dummy :=NULL;
138 begin
139 select '1'
140 into l_dummy
141 from per_pos_structure_elements ose
142 where
143 ose.POS_structure_version_id = X_Pos_Structure_Version_Id
144 and ose.business_group_id + 0 = X_Business_Group_Id
145 and X_Parent_Position_Id in
146 (
147 select ose.subordinate_position_id
148 from per_pos_structure_elements ose
149 where ose.POS_structure_version_id = X_Pos_Structure_Version_Id
150 and ose.business_group_id + 0 = X_Business_Group_Id
151 connect by
152 prior ose.subordinate_position_id = ose.parent_position_id
153 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
154 and ose.business_group_id + 0 = X_Business_Group_Id
155 start with
156 ose.parent_position_id = X_Subordinate_Position_Id
157 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
158 and ose.business_group_id + 0 = X_Business_Group_Id
159 );
160 exception when no_data_found then
161 null;
162 when too_many_rows then
163 null;
164 end;
165 if l_dummy = '1' then
166 hr_utility.set_message(801,'PER_7421_POS_PARENT_BELOW');
167 hr_utility.raise_error;
168 end if;
169 end if;
170 end if;
171
172 end;
173
174 PROCEDURE Check_duplicate_hierarchies (X_Rowid VARCHAR2,
175 X_Subordinate_Position_Id NUMBER,
176 X_Business_Group_Id NUMBER,
177 X_Pos_Structure_Version_Id NUMBER) is
178
179 l_exists boolean;
180 l_dummy varchar2(1);
181
182 cursor csr_exists is
183 select '1'
184 from per_pos_structure_elements tab
185 where (( X_Rowid is not null
186 and tab.rowid <> X_Rowid)
187 or X_Rowid is null)
188 and X_Pos_Structure_Version_Id = tab.pos_structure_version_id
189 and (X_Subordinate_Position_Id = tab.subordinate_position_id)
190 and X_Business_Group_Id = tab.business_group_id + 0;
191
192 begin
193 open csr_exists;
194 fetch csr_exists into l_dummy;
195 l_exists := csr_exists%found;
196 close csr_exists;
197 if l_exists then
198 hr_utility.set_message(801,'PER_7408_POS_HIER_POS');
199 hr_utility.raise_error;
200 end if;
201 end Check_duplicate_hierarchies;
202
203 PROCEDURE check_sec_profiles (X_Business_Group_Id NUMBER,
204 X_Pos_Structure_Version_Id NUMBER,
205 X_Parent_Position_Id NUMBER,
206 X_Subordinate_Position_Id NUMBER,
207 X_Position_Id NUMBER) is
208 l_dummy varchar2(1);
209 l_Position_Structure_Id number;
210 begin
211 begin
212 select position_structure_id
213 into l_Position_Structure_Id
214 from per_pos_structure_versions
215 where pos_structure_version_id = X_Pos_Structure_Version_Id;
216 exception when too_many_rows then
217 null;
218 end;
219 l_dummy:= NULL;
220 begin
221 SELECT '1'
222 into l_dummy
223 FROM PER_SECURITY_PROFILES PSP
224 WHERE PSP.business_group_id + 0 = X_Business_Group_Id
225 AND (PSP.POSITION_ID = X_Subordinate_Position_Id
226 OR PSP.POSITION_ID = X_Parent_Position_Id)
227 AND PSP.POSITION_STRUCTURE_ID = l_Position_Structure_Id;
228 exception when no_data_found then
229 null;
230 when too_many_rows then
231 null;
232 end;
233 if l_dummy = '1' then
234 hr_utility.set_message(801,'PAY_7694_PER_NO_DEL_STRUCTURE');
235 hr_utility.raise_error;
236 else
237 l_dummy:= NULL;
238 begin
239 SELECT '1'
240 into l_dummy
241 FROM PER_SECURITY_PROFILES PSP
242 WHERE PSP.business_group_id + 0 = X_Business_Group_Id
243 AND PSP.POSITION_ID = X_Position_Id
244 AND PSP.POSITION_STRUCTURE_ID = l_position_structure_id;
245 exception when no_data_found then
246 null;
247 when too_many_rows then
248 null;
249 end;
250
251 if l_dummy = '1' then
252 hr_utility.set_message(801,'PAY_7694_PER_NO_DEL_STRUCTURE');
253 hr_utility.raise_error;
254 end if;
255
256 end if;
257
258 end;
259
260 PROCEDURE check_if_child_is_parent (X_Business_Group_Id NUMBER,
261 X_Pos_Structure_Version_Id NUMBER,
262 X_Parent_Position_Id NUMBER,
263 X_Subordinate_Position_Id NUMBER) is
264 l_dummy varchar2(1);
265 begin
266 l_dummy := NULL;
267 begin
268 select '1'
269 into l_dummy
270 from per_pos_structure_elements ose
271 where ose.business_group_id + 0 = X_Business_Group_Id
272 and ose.POS_structure_version_id = X_Pos_Structure_Version_Id
273 and not exists
274 (select null
275 from per_pos_structure_elements b
276 where b.POS_structure_version_id = X_Pos_Structure_Version_Id
277 and b.subordinate_position_id = X_Parent_Position_Id)
278 and not exists
279 (select null
280 from per_pos_structure_elements c
281 where c.pos_structure_version_id = X_Pos_Structure_Version_Id
282 and c.parent_position_id = X_Parent_Position_Id
283 and c.subordinate_position_id <> X_Subordinate_Position_Id);
284 exception when no_data_found then
285 null;
286 when too_many_rows then
287 null;
288 end;
289 if l_dummy <> '1' then
290 l_dummy := NULL;
291 begin
292 SELECT '1'
293 into l_dummy
294 FROM PER_POS_STRUCTURE_ELEMENTS OSE
295 WHERE OSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id
296 AND OSE.PARENT_POSITION_ID = X_Subordinate_Position_Id;
297 exception when no_data_found then
298 null;
299 when too_many_rows then
300 null;
301 end;
302 if l_dummy = '1' then
303 hr_utility.set_message(801,'PER_7418_POS_PARENT');
304 hr_utility.raise_error;
305 end if;
306 end if;
307 end;
308
309
310
311 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
312 X_Pos_Structure_Element_Id IN OUT NOCOPY NUMBER,
313 X_Business_Group_Id NUMBER,
314 X_Pos_Structure_Version_Id NUMBER,
315 X_Subordinate_Position_Id NUMBER,
316 X_Parent_Position_Id NUMBER,
317 X_Position_Id NUMBER,
318 X_Security_Profile_Id NUMBER,
319 X_View_All_Positions VARCHAR2,
320 X_End_of_time DATE,
321 X_Session_Date DATE,
322 X_hr_ins VARCHAR2
323 ) IS
324 CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_ELEMENTS
325
326 WHERE pos_structure_element_id = X_Pos_Structure_Element_Id;
327
328
329
330
331
332 CURSOR C2 IS SELECT per_pos_structure_elements_s.nextval FROM sys.dual;
333 BEGIN
334
335 if (X_Pos_Structure_Element_Id is NULL) then
336 OPEN C2;
337 FETCH C2 INTO X_Pos_Structure_Element_Id;
338 CLOSE C2;
339 end if;
340
341 Check_duplicate_hierarchies(X_Rowid ,
342 X_Subordinate_Position_Id ,
343 X_Business_Group_Id ,
344 X_Pos_Structure_Version_Id );
345
346 Parent_insert_update_checks(X_Business_Group_Id,
347 X_Pos_Structure_Version_Id,
348 X_Position_Id,
349 X_Parent_Position_Id,
350 X_Subordinate_Position_Id);
351
352 INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
353 pos_structure_element_id,
354 business_group_id,
355 pos_structure_version_id,
356 subordinate_position_id,
357 parent_position_id
358 ) VALUES (
359 X_Pos_Structure_Element_Id,
360 X_Business_Group_Id,
361 X_Pos_Structure_Version_Id,
362 X_Subordinate_Position_Id,
363 X_Parent_Position_Id
364 );
365
366 OPEN C;
367 FETCH C INTO X_Rowid;
368 if (C%NOTFOUND) then
369 CLOSE C;
370 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
371 hr_utility.set_message_token('PROCEDURE','Insert_row');
372 hr_utility.set_message_token('STEP','1');
373 hr_utility.raise_error;
374 end if;
375 CLOSE C;
376
377 END Insert_Row;
378 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
379
380 X_Pos_Structure_Element_Id NUMBER,
381 X_Business_Group_Id NUMBER,
382 X_Pos_Structure_Version_Id NUMBER,
383 X_Subordinate_Position_Id NUMBER,
384 X_Parent_Position_Id NUMBER
385 ) IS
386 CURSOR C IS
387 SELECT *
388 FROM PER_POS_STRUCTURE_ELEMENTS
389 WHERE rowid = X_Rowid
390 FOR UPDATE of Pos_Structure_Element_Id NOWAIT;
391 Recinfo C%ROWTYPE;
392 BEGIN
393 OPEN C;
394 FETCH C INTO Recinfo;
395 if (C%NOTFOUND) then
396 CLOSE C;
397 RAISE NO_DATA_FOUND;
398 end if;
399 CLOSE C;
400 if (
401 ( (Recinfo.pos_structure_element_id = X_Pos_Structure_Element_Id)
402 OR ( (Recinfo.pos_structure_element_id IS NULL)
406 AND (X_Business_Group_Id IS NULL)))
403 AND (X_Pos_Structure_Element_Id IS NULL)))
404 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
405 OR ( (Recinfo.business_group_id IS NULL)
407 AND ( (Recinfo.pos_structure_version_id = X_Pos_Structure_Version_Id)
408 OR ( (Recinfo.pos_structure_version_id IS NULL)
409 AND (X_Pos_Structure_Version_Id IS NULL)))
410 AND ( (Recinfo.subordinate_position_id = X_Subordinate_Position_Id)
411 OR ( (Recinfo.subordinate_position_id IS NULL)
412 AND (X_Subordinate_Position_Id IS NULL)))
413 AND ( (Recinfo.parent_position_id = X_Parent_Position_Id)
414 OR ( (Recinfo.parent_position_id IS NULL)
415 AND (X_Parent_Position_Id IS NULL)))
416 ) then
417 return;
418 else
419 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
420 APP_EXCEPTION.RAISE_EXCEPTION;
421 end if;
422 END Lock_Row;
423
424 PROCEDURE Update_Row(X_Rowid VARCHAR2,
425 X_Pos_Structure_Element_Id NUMBER,
426 X_Business_Group_Id NUMBER,
427 X_Pos_Structure_Version_Id NUMBER,
428 X_Subordinate_Position_Id NUMBER,
429 X_Parent_Position_Id NUMBER,
430 X_Position_Id NUMBER
431 ) IS
432 BEGIN
433
434 Parent_insert_update_checks(X_Business_Group_Id,
435 X_Pos_Structure_Version_Id,
436 X_Position_Id,
437 X_Parent_Position_Id,
438 X_Subordinate_Position_Id);
439
440
441 UPDATE PER_POS_STRUCTURE_ELEMENTS
442 SET
443
444 pos_structure_element_id = X_Pos_Structure_Element_Id,
445 business_group_id = X_Business_Group_Id,
446 pos_structure_version_id = X_Pos_Structure_Version_Id,
447 subordinate_position_id = X_Subordinate_Position_Id,
448 parent_position_id = X_Parent_Position_Id
449 WHERE rowid = X_rowid;
450
451 if (SQL%NOTFOUND) then
452 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
453 hr_utility.set_message_token('PROCEDURE','update_row');
454 hr_utility.set_message_token('STEP','1');
455 hr_utility.raise_error;
456 end if;
457
458 END Update_Row;
459
460 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
461 X_Business_Group_Id NUMBER,
462 X_Pos_Structure_Version_Id NUMBER,
463 X_Parent_Position_Id NUMBER,
464 X_Subordinate_Position_Id NUMBER,
465 X_hr_ins VARCHAR2,
466 X_Position_Id NUMBER) IS
467 BEGIN
468
469 check_if_child_is_parent(X_Business_Group_Id,
470 X_Pos_Structure_Version_Id,
471 X_Parent_Position_Id,
472 X_Subordinate_Position_Id);
473
474 if x_hr_ins = 'Y' then
475 check_sec_profiles(X_Business_Group_Id,
476 X_Pos_Structure_Version_Id,
477 X_Parent_Position_Id,
478 X_Subordinate_Position_Id,
479 X_Position_Id);
480 end if;
481
482 DELETE FROM PER_POS_STRUCTURE_ELEMENTS
483 WHERE rowid = X_Rowid;
484
485 if (SQL%NOTFOUND) then
486 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
487 hr_utility.set_message_token('PROCEDURE','delete_row');
488 hr_utility.set_message_token('STEP','1');
489 hr_utility.raise_error;
490 end if;
491 END Delete_Row;
492
493 END PER_POS_STRUCT_ELEMENTS_PKG2;
[Home] [Help]