1 PACKAGE BODY PER_POS_STRUCTURE_ELEMENTS_PKG as
2 /* $Header: pepse01t.pkb 120.0 2005/05/31 15:14:31 appldev noship $ */
3 ------------------------------------------------------------------------------
4 FUNCTION get_subordinates(X_View_All_Positions VARCHAR2
5 ,X_Parent_Position_Id NUMBER
6 ,X_Pos_Structure_Version_id NUMBER
7 ,X_Security_Profile_Id NUMBER) RETURN NUMBER IS
8 --
9 -- Local Varaible
10 --
11 l_count NUMBER;
12 Begin
13 --
14 -- Return the number of subordinates of a given positions
15 -- in a given hierarchy
16 --
17 -- If security exists then return only those in
18 -- the current security profile
19 --
20 select nvl( count(pse.pos_structure_element_id), 0)
21 into l_count
22 from per_pos_structure_elements pse
23 where ((X_View_All_Positions <> 'Y'
24 and EXISTS
25 (select '1'
26 from hr_positions_f hpf
27 where hpf.position_id = pse.subordinate_position_id
28 ))
29 or X_View_All_Positions = 'Y')
30 connect by
31 prior pse.subordinate_position_id = pse.parent_position_id
32 and pse.pos_structure_version_id = X_Pos_structure_version_id
33 start with
34 pse.parent_position_id = X_Parent_Position_Id
35 and pse.pos_structure_version_id = X_Pos_structure_version_id;
36 -- no exception necessary as a single row group function
37 -- hence no no_data_found or Too_many_rows errors;
38 return l_count;
39 exception
40 when no_data_found then
41 return 0;
42 end;
43 --
44 ------------------------------------------------------------------------------
45 PROCEDURE get_holders(X_Business_Group_Id NUMBER
46 ,X_Position_Id NUMBER
47 ,X_Organization_id NUMBER
48 ,X_Holder IN OUT NOCOPY VARCHAR2
49 ,X_No_Holders IN OUT NOCOPY NUMBER
50 ,X_Session_date DATE
51 ,X_Employee_Number IN OUT NOCOPY VARCHAR2
52 ,X_User_Person_Type IN OUT NOCOPY VARCHAR2) IS
53 --
54 -- Local Variables
55 --
56 l_message VARCHAR2(80);
57
58 Begin
59 hr_utility.set_message('801','HR_ALL_COUNT_HOLDERS');
60 l_message := hr_utility.get_message;
61 -- Bug fix 3681825: Distinct added to sql to avoid counting the same holder more
62 -- than once.
63 select COUNT(DISTINCT P.PERSON_ID), '** ' || COUNT(DISTINCT P.PERSON_ID) ||' '||l_message
64 into X_No_Holders , l_message
65 from per_all_people_f p
66 , per_all_assignments_f a
67 where a.position_id = X_Position_Id
68 and a.business_group_id + 0 = X_Business_Group_Id
69 and a.organization_id = X_Organization_id
70 and a.assignment_type in ('E', 'C')
71 and a.person_id = p.person_id
72
73 and exists (select ppt.system_person_type
74 from per_person_types ppt, per_person_type_usages_f pptu
75 where pptu.person_id = p.person_id
76 and ppt.person_type_id = pptu.person_type_id
77 and ppt.system_person_type in ('EMP','CWK')
78 and X_session_date between pptu.effective_start_date
79 AND pptu.effective_end_date)
80 and exists (select past.per_system_status
81 from per_assignment_status_types past
82 where past.assignment_status_type_id = a.assignment_status_type_id
83 and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','ACTIVE_CWK','SUSP_CWK_ASG'))
84 and X_Session_date between a.effective_start_date
85 and a.effective_end_date
86 and X_Session_date between p.effective_start_date
87 and p.effective_end_date;
88 if X_No_Holders = 0 then
89 hr_utility.set_message('801','HR_ALL_NO_HOLDERS');
90 X_Holder := hr_utility.get_message;
91 X_Employee_Number := NULL;
92 elsif X_No_Holders = 1 then
93 begin
94 -- Bug fix 3681825: Distinct added to sql to avoid fetching multiple rows
95 select DISTINCT p.full_name
96 ,hr_person_type_usage_info.get_worker_number
97 (X_Session_date, p.person_id) worker_number
98 ,hr_person_type_usage_info.get_worker_user_person_type
99 (X_Session_date, p.person_id) user_person_type
100 into X_Holder
101 ,X_Employee_Number
102 ,X_User_Person_type
103 from per_all_people_f p
104 ,per_all_assignments_f a
105 where a.position_id = X_Position_Id
106 and a.business_group_id = X_Business_Group_Id
107 and a.organization_id = X_Organization_id
108 and a.assignment_type in ('E', 'C')
109 and a.person_id = p.person_id
110 and exists
111 (select ppt.system_person_type
112 from per_person_types ppt, per_person_type_usages_f ptu
113 where ptu.person_id = p.person_id
114 and ppt.person_type_id = ptu.person_type_id
115 and ppt.system_person_type in ('EMP', 'CWK')
116 and X_session_date between ptu.effective_start_date and ptu.effective_end_date)
117 and exists
118 (select past.per_system_status
119 from per_assignment_status_types past
120 where past.assignment_status_type_id
121 = a.assignment_status_type_id
122 and past.per_system_status in
123 ('ACTIVE_ASSIGN','SUSP_ASSIGN'
124 ,'ACTIVE_CWK', 'SUSP_CWK_ASG'))
125 and X_Session_date between a.effective_start_date
126 and a.effective_end_date
127 and X_Session_date between p.effective_start_date
128 and p.effective_end_date;
129 exception
130 when no_data_found then
131 null;
132 when too_many_rows then
133 null;
134 end;
135 else
136 X_Holder := l_message;
137 X_Employee_Number := NULL;
138 end if;
139 end get_holders;
140 --
141 ------------------------------------------------------------------------------
142 PROCEDURE block_post_query(X_Business_Group_Id NUMBER
143 ,X_Position_Id NUMBER
144 ,X_Organization_id IN OUT NOCOPY NUMBER
145 ,X_Holder IN OUT NOCOPY VARCHAR2
146 ,X_No_Holders IN OUT NOCOPY NUMBER
147 ,X_Employee_Number IN OUT NOCOPY VARCHAR2
148 ,X_Subordinate_position_id NUMBER
149 ,X_View_All_Positions VARCHAR2
150 ,X_Parent_Position_Id NUMBER
151 ,X_Pos_Structure_Version_id NUMBER
152 ,X_Security_Profile_Id NUMBER
153 ,X_Session_date DATE
154 ,X_exists_in_hierarchy IN OUT NOCOPY VARCHAR2
155 ,X_Number_of_Subordinates IN OUT NOCOPY NUMBER
156 ,X_User_Person_Type IN OUT NOCOPY VARCHAR2) IS
157 --
158 l_organization_id NUMBER;
159 Begin
160
161 --
162 -- Does the block requerying to save two extra trips per row.
163 --
164 --
165 -- Get the number of Subordinates this Position has.
166 if X_Organization_id is null then
167 --
168 -- If Organization id is null then
169 -- retrieve it.(Only when called from structure_element )
170 Begin
171 --
172 -- Changes 01-Oct-99 SCNair (per_all_positions to hr_all_positions) date tracked positions req.
173 --
174 select p.organization_id
175 into X_Organization_Id
176 from hr_all_positions p
177 where p.position_id = X_Position_Id
178 and p.business_group_id + 0 = X_Business_Group_Id;
179 --
180 exception
181 when no_data_found then
182 null;
183 end;
184 end if;
185 X_Number_of_Subordinates := get_subordinates(
186 X_View_All_Positions => X_View_All_Positions
187 ,X_Parent_Position_Id => X_Parent_Position_Id
188 ,X_Pos_Structure_Version_id => X_Pos_Structure_Version_id
189 ,X_Security_Profile_Id => X_Security_Profile_Id);
190 --
191 --
192 -- Get the Holder,if any, of this position. if >1 then return count of holders.:
193 get_holders(X_Business_Group_Id => X_Business_Group_Id
194 ,X_Position_Id => X_Position_Id
195 ,X_Organization_id => X_Organization_id
196 ,X_Holder => X_Holder
197 ,X_No_Holders => X_No_Holders
198 ,X_Session_date => X_Session_date
199 ,X_Employee_Number => X_Employee_Number
200 ,X_User_Person_Type => X_User_Person_Type);
201 --
202 X_exists_in_hierarchy:=PER_POSITIONS_PKG.exists_in_hierarchy(
203 X_Pos_Structure_Version_Id => X_Pos_Structure_Version_id
204 ,X_Position_Id => X_Position_Id);
205 end block_post_query;
206 ------------------------------------------------------------------------------
207 PROCEDURE maintain_pos_list(X_Business_Group_Id NUMBER
208 ,X_Security_Profile_Id NUMBER
209 ,X_View_All_Positions VARCHAR2
210 ,X_Sec_Pos_Structure_Version_id NUMBER
211 ,X_Position_Id NUMBER) IS
212 --
213 --Local Variables
214 --
215 l_view_all_positions VARCHAR2(1);
216 l_include_top_pos_flag VARCHAR2(1);
217 l_sec_pos_id NUMBER;
218 --
219 -- Local Function
220 --
221 FUNCTION tree_walk(X_Business_Group_Id NUMBER
222 ,X_Sec_Pos_Structure_Version_id NUMBER
223 ,X_Position_Id NUMBER) RETURN BOOLEAN IS
224 --
225 --
226 l_exists VARCHAR2(1);
227 begin
228 select null
229 into l_exists
230 from sys.dual
231 where X_Position_Id in
232 (select a.subordinate_position_id
233 from per_POS_structure_elements a
234 where a.business_group_id + 0 = X_Business_Group_Id
235 and a.POS_structure_VERSION_id =
236 X_Sec_Pos_Structure_Version_id
237 connect by prior
238 a.subordinate_position_id = a.parent_position_id
239 and a.POS_structure_VERSION_id =
240 X_Sec_Pos_Structure_Version_id
241 and a.business_group_id + 0 = X_Business_Group_Id
242 start with
243 a.parent_position_id = X_Position_Id
244 and a.POS_structure_VERSION_id =
245 X_Sec_Pos_Structure_Version_id
246 and a.business_group_id + 0 = X_Business_Group_Id);
247 return true;
248 exception
249 when no_data_found then
250 return false;
251 when too_many_rows then
252 raise;
253 end tree_walk;
254 begin
255 if X_view_all_positions = 'N' then
256 return;
257 end if;
258 begin
259 select x.view_all_POSITIONs_flag
260 , x.POSITION_id
261 , x.include_top_POSITION_flag
262 into l_view_all_positions
263 , l_sec_pos_id
264 , l_include_top_pos_flag
265 from per_security_profiles x
266 where x.business_group_id + 0 = X_Business_Group_Id
267 and x.security_profile_id = X_Security_Profile_Id;
268 --
269 exception
270 when no_data_found then
271 null;
272 end;
273 --
274 if l_view_all_positions <>'Y' then
275 if l_include_top_pos_flag = 'N' then
276 if tree_walk(X_Business_Group_Id =>X_Business_Group_Id
277 ,X_Sec_Pos_Structure_Version_id => X_Sec_Pos_Structure_Version_id
278 ,X_Position_id => l_sec_pos_id) then
279 null;
280 else
281 if l_sec_pos_id <> X_Position_Id then
282 if tree_walk(X_Business_Group_Id =>X_Business_Group_Id
283 ,X_Sec_Pos_Structure_Version_id => X_Sec_Pos_Structure_Version_id
284 ,X_Position_id => l_sec_pos_id) then
285 null;
286 else
287 return;
288 end if;
289 end if;
290 end if;
291 end if;
292 end if;
293
294 hr_security.add_position(X_Position_Id,
295 X_Security_Profile_Id);
296 --
297 if sql%ROWCOUNT <>1 then
298 -- raise_error;
299 null;
300 end if;
301 end maintain_pos_list;
302 --
303 ------------------------------------------------------------------------------
304 PROCEDURE check_unique(X_Parent_position_id NUMBER
305 ,X_Pos_Structure_Version_Id NUMBER
306 ,X_Subordinate_Position_Id NUMBER) IS
307 --
308 -- Local Variables
309 --
310 l_dummy VARCHAR2(1);
311 begin
312 select null
313 into l_dummy
314 FROM PER_POS_STRUCTURE_ELEMENTS PSE
315 WHERE PSE.POS_STRUCTURE_VERSION_ID = X_Pos_Structure_Version_Id
316 AND PSE.PARENT_POSITION_ID = X_Parent_position_id
317 AND PSE.SUBORDINATE_POSITION_ID = X_Subordinate_Position_Id;
318 --
319 hr_utility.set_message('801','HR_6012_ROW_INSERTED');
320 hr_utility.raise_error;
321 exception
322 when no_data_found then
323 null;
324 end check_unique;
325 -------------------------------------------------------------------------------
326 PROCEDURE pre_delete_checks(X_Subordinate_position_Id NUMBER
327 ,X_Position_Structure_Id NUMBER
328 ,X_Business_Group_Id NUMBER
329 ,X_Hr_Installed VARCHAR2
330 ,X_Pos_Structure_version_Id NUMBER) IS
331 l_dummy VARCHAR2(1);
332 begin
333 begin
334 select null
335 into l_dummy
336 from sys.dual
337 where exists(select 1
338 from per_pos_structure_elements pse
339 where pse.parent_position_id = X_Subordinate_position_Id
340 and pse.pos_structure_version_id = X_Pos_Structure_version_Id);
341 --
342 hr_utility.set_message('801','HR_6915_POS_DEL_FIRST');
343 hr_utility.raise_error;
344 --
345 exception
346 when no_data_found then
347 null;
348 end;
349 if X_Hr_Installed <> 'I' then
350 begin
351 select null
352 into l_dummy
353 from sys.dual
354 where exists(select 1
355 from per_security_profiles psp
356 where psp.business_group_id + 0 = X_Business_Group_Id
357 and psp.position_id = X_Subordinate_position_Id
358 and psp.position_structure_id = X_Position_Structure_Id);
359 --
360 hr_utility.set_message(801,'PAY_7694_PER_NO_DEL_STRUCTURE');
361 hr_utility.raise_error;
362 --
363 exception
364 when no_data_found then
365 null;
366 end;
367 end if;
368 end pre_delete_checks;
369 -------------------------------------------------------------------------------
370 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
371 X_Pos_Structure_Element_Id IN OUT NOCOPY NUMBER,
372 X_Business_Group_Id NUMBER,
373 X_Pos_Structure_Version_Id NUMBER,
374 X_Subordinate_Position_Id NUMBER,
375 X_Parent_Position_Id NUMBER
376 ) IS
377 CURSOR C IS SELECT rowid FROM PER_POS_STRUCTURE_ELEMENTS
378 WHERE pos_structure_element_id = X_Pos_Structure_Element_Id;
379 CURSOR C2 IS SELECT per_pos_structure_elements_s.nextval FROM sys.dual;
380 --
381 cursor get_details is select rowid, POS_STRUCTURE_ELEMENT_ID
382 ,BUSINESS_GROUP_ID, POS_STRUCTURE_VERSION_ID
383 ,SUBORDINATE_POSITION_ID,PARENT_POSITION_ID
384 FROM PER_POS_STRUCTURE_ELEMENTS
385 where Subordinate_Position_Id = X_Subordinate_Position_Id
386 and Pos_Structure_Version_Id = X_Pos_Structure_Version_Id;
387 Recinfo get_details%ROWTYPE;
388 --
389 BEGIN
390 /* PER_POS_STRUCTURE_ELEMENTS_PKG.check_unique(
391 X_Parent_position_id => X_Parent_position_id
392 ,X_Pos_Structure_Version_Id => X_Pos_Structure_Version_Id
396 -- Does the row subordinate already exist in the hierarchy as a subordinate?
393 ,X_Subordinate_Position_Id => X_Subordinate_Position_Id);
394 */
395 --
397 --
398 if ((PER_POSITIONS_PKG.exists_in_hierarchy(
399 X_Pos_Structure_Version_Id =>X_Pos_Structure_Version_Id
400 ,X_Position_Id => X_Subordinate_Position_Id)) ='Y') then
401 --
402 -- Yes , then merely update the old structure element
403 --
404 open get_details;
405 fetch get_details into Recinfo;
406 if get_details%found then
407 --
408 -- Lock the row
409 --
410
411 PER_POS_STRUCTURE_ELEMENTS_PKG.lock_row(
412 X_Rowid => Recinfo.ROWID,
413 X_Pos_Structure_Element_Id => Recinfo.POS_STRUCTURE_ELEMENT_ID,
414 X_Business_Group_Id => Recinfo.BUSINESS_GROUP_ID,
415 X_Pos_Structure_Version_Id => Recinfo.POS_STRUCTURE_VERSION_ID,
416 X_Subordinate_Position_Id => Recinfo.SUBORDINATE_POSITION_ID,
417 X_Parent_Position_Id => Recinfo.PARENT_POSITION_ID);
418 --
419 -- Update the row
420 --
421 PER_POS_STRUCTURE_ELEMENTS_PKG.update_row(
422 X_Rowid => Recinfo.ROWID,
423 X_Pos_Structure_Element_Id => Recinfo.POS_STRUCTURE_ELEMENT_ID,
424 X_Business_Group_Id => Recinfo.BUSINESS_GROUP_ID,
425 X_Pos_Structure_Version_Id => Recinfo.POS_STRUCTURE_VERSION_ID,
426 X_Subordinate_Position_Id => Recinfo.SUBORDINATE_POSITION_ID,
427 X_Parent_Position_Id => X_Parent_Position_Id);
428 --
429 -- set the values
430 --
431 X_Rowid := Recinfo.ROWID;
432 X_Pos_Structure_Element_Id := Recinfo.POS_STRUCTURE_ELEMENT_ID;
433 close get_details;
434 return;
435 end if;
436 end if;
437 if (X_Pos_Structure_Element_Id is NULL) then
438 OPEN C2;
439 FETCH C2 INTO X_Pos_Structure_Element_Id;
440 CLOSE C2;
441 end if;
442 INSERT INTO PER_POS_STRUCTURE_ELEMENTS(
443 pos_structure_element_id,
444 business_group_id,
445 pos_structure_version_id,
446 subordinate_position_id,
447 parent_position_id
448 ) VALUES (
449 X_Pos_Structure_Element_Id,
450 X_Business_Group_Id,
451 X_Pos_Structure_Version_Id,
452 X_Subordinate_Position_Id,
453 X_Parent_Position_Id
454 );
455 OPEN C;
456 FETCH C INTO X_Rowid;
457 if (C%NOTFOUND) then
458 CLOSE C;
459 RAISE NO_DATA_FOUND;
460 end if;
461 CLOSE C;
462 END Insert_Row;
463 -------------------------------------------------------------------------------
464 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
465 X_Pos_Structure_Element_Id NUMBER,
466 X_Business_Group_Id NUMBER,
467 X_Pos_Structure_Version_Id NUMBER,
468 X_Subordinate_Position_Id NUMBER,
469 X_Parent_Position_Id NUMBER
470 ) IS
471 CURSOR C IS
472 SELECT *
473 FROM PER_POS_STRUCTURE_ELEMENTS
474 WHERE rowid = X_Rowid
475 FOR UPDATE of Pos_Structure_Element_Id NOWAIT;
476 Recinfo C%ROWTYPE;
477 BEGIN
478 OPEN C;
479 FETCH C INTO Recinfo;
480 if (C%NOTFOUND) then
481 CLOSE C;
482 RAISE NO_DATA_FOUND;
483 end if;
484 CLOSE C;
485 if (
486 ( (Recinfo.pos_structure_element_id = X_Pos_Structure_Element_Id)
487 OR ( (Recinfo.pos_structure_element_id IS NULL)
488 AND (X_Pos_Structure_Element_Id IS NULL)))
489 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
490 OR ( (Recinfo.business_group_id IS NULL)
491 AND (X_Business_Group_Id IS NULL)))
492 AND ( (Recinfo.pos_structure_version_id = X_Pos_Structure_Version_Id)
493 OR ( (Recinfo.pos_structure_version_id IS NULL)
494 AND (X_Pos_Structure_Version_Id IS NULL)))
495 AND ( (Recinfo.subordinate_position_id = X_Subordinate_Position_Id)
496 OR ( (Recinfo.subordinate_position_id IS NULL)
497 AND (X_Subordinate_Position_Id IS NULL)))
498 AND ( (Recinfo.parent_position_id = X_Parent_Position_Id)
499 OR ( (Recinfo.parent_position_id IS NULL)
500 AND (X_Parent_Position_Id IS NULL)))
501 ) then
502 return;
503 else
504 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
505 APP_EXCEPTION.RAISE_EXCEPTION;
506 end if;
507 END Lock_Row;
508 ------------------------------------------------------------------------------
509 PROCEDURE Update_Row(X_Rowid VARCHAR2,
510 X_Pos_Structure_Element_Id NUMBER,
511 X_Business_Group_Id NUMBER,
512 X_Pos_Structure_Version_Id NUMBER,
513 X_Subordinate_Position_Id NUMBER,
514 X_Parent_Position_Id NUMBER
515 ) IS
516 BEGIN
517 UPDATE PER_POS_STRUCTURE_ELEMENTS
518 SET
519 pos_structure_element_id = X_Pos_Structure_Element_Id,
523 parent_position_id = X_Parent_Position_Id
520 business_group_id = X_Business_Group_Id,
521 pos_structure_version_id = X_Pos_Structure_Version_Id,
522 subordinate_position_id = X_Subordinate_Position_Id,
524 WHERE rowid = X_rowid;
525 if (SQL%NOTFOUND) then
526 RAISE NO_DATA_FOUND;
527 end if;
528 END Update_Row;
529 ------------------------------------------------------------------------------
530 PROCEDURE Delete_Row(X_Rowid VARCHAR2
531 ,X_Subordinate_position_Id NUMBER
532 ,X_Position_Structure_Id NUMBER
533 ,X_Business_Group_Id NUMBER
534 ,X_Hr_Installed VARCHAR2
535 ,X_Pos_Structure_version_Id NUMBER) IS
536 BEGIN
537 --
538 -- Do the Pre-delete checks
539 --
540 PER_POS_STRUCTURE_ELEMENTS_PKG.pre_delete_checks(
541 X_Subordinate_position_Id => X_Subordinate_position_Id
542 ,X_Position_Structure_Id => X_Position_Structure_Id
543 ,X_Business_Group_Id => X_Business_Group_Id
544 ,X_Hr_Installed => X_Hr_Installed
545 ,X_Pos_Structure_version_Id => X_Pos_Structure_version_Id);
546 --
547 DELETE FROM PER_POS_STRUCTURE_ELEMENTS
548 WHERE rowid = X_Rowid;
549 if (SQL%NOTFOUND) then
550 RAISE NO_DATA_FOUND;
551 end if;
552 END Delete_Row;
553
554 END PER_POS_STRUCTURE_ELEMENTS_PKG;
[Home] [Help]