[Home] [Help]
PACKAGE BODY: APPS.HR_POS_HIERARCHY_ELE_API
Source
1 Package Body hr_pos_hierarchy_ele_api as
2 /* $Header: pepseapi.pkb 120.0.12000000.2 2007/06/29 10:07:15 sidsaxen noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_pos_hierarchy_ele_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_pos_hierarchy_ele >--------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_pos_hierarchy_ele
13 (p_validate in boolean default false
14 ,p_parent_position_id in number
15 ,p_pos_structure_version_id in number
16 ,p_subordinate_position_id in number
17 ,p_business_group_id in number
18 ,p_hr_installed in VARCHAR2
19 ,p_effective_date in date
20 ,p_pos_structure_element_id out nocopy number
21 ,p_object_version_number out nocopy number
22 ) is
23 --
24 -- Declare cursors and local variables
25 --
26 l_pos_structure_element_id
27 per_pos_structure_elements.pos_structure_element_id%TYPE;
28 l_object_version_number
29 per_pos_structure_elements.object_version_number%TYPE;
30 l_proc varchar2(72) := g_package||'create_pos_hierarchy_ele';
31 l_effective_date date;
32 --
33 cursor get_old_element is select pos_structure_element_id,
34 object_version_number
35 from per_pos_structure_elements
36 where subordinate_position_id = p_subordinate_position_id
37 and pos_structure_version_id = p_pos_structure_version_id;
38 --
39
40 --start changes for bug 6139893
41 --cursor returns values when the passing 'Parent' is already present as a 'Child' in the below hierarchy
42 cursor chk_child_sub_node is
43 SELECT distinct '1'
44 FROM per_pos_structure_elements a
45 WHERE a.business_group_id = p_business_group_id
46 AND a.POS_structure_version_id = p_pos_structure_version_id
47 AND a.parent_position_id = p_subordinate_position_id
48 AND p_parent_position_id IN
49 (SELECT subordinate_position_id
50 FROM per_pos_structure_elements b
51 WHERE a.business_group_id = b.business_group_id
52 AND a.POS_structure_version_id = b.pos_structure_version_id
53 CONNECT BY parent_position_id = prior subordinate_position_id
54 START WITH parent_position_id = a.parent_position_id
55 );
56 l_chk varchar2(10);
57
58 --end changes for bug 6139893
59
60 begin
61 hr_utility.set_location('Entering:'|| l_proc, 10);
62 --
63 -- Issue a savepoint
64 --
65 savepoint create_pos_hierarchy_ele;
66 --
67 -- Truncate the time portion from all IN date parameters
68 l_effective_date := trunc(p_effective_date);
69 --
70 --
71 -- Call Before Process User Hook
72 --
73 begin
74 hr_pos_hierarchy_ele_bk1.create_pos_hierarchy_ele_b
75 (p_parent_position_id => p_parent_position_id
76 ,p_pos_structure_version_id => p_pos_structure_version_id
77 ,p_subordinate_position_id => p_subordinate_position_id
78 ,p_business_group_id => p_business_group_id
79 );
80 exception
81 when hr_api.cannot_find_prog_unit then
82 hr_api.cannot_find_prog_unit_error
83 (p_module_name => 'create_pos_hierarchy_ele'
84 ,p_hook_type => 'BP'
85 );
86 end;
87 --
88 -- Validation in addition to Row Handlers
89 --
90 --
91 -- Process Logic
92 --
93
94 --start changes for bug 6139893
95 -- check the 'Parent' should not present as 'Child' in the lower hierarchy.
96
97 open chk_child_sub_node;
98 fetch chk_child_sub_node into l_chk;
99
100 if chk_child_sub_node%found THEN
101 close chk_child_sub_node;
102 hr_utility.set_message(801,'PER_7421_POS_PARENT_BELOW');
103 hr_utility.raise_error;
104 ELSE
105 CLOSE chk_child_sub_node;
106 END if;
107 ---
108 --end changes for bug 6139893
109 --
110 if ((PER_POSITIONS_PKG.exists_in_hierarchy(
111 X_Pos_Structure_Version_Id => p_Pos_Structure_Version_Id
112 ,X_Position_Id => p_Subordinate_Position_Id)) ='Y') then
113 --
114 -- Yes, then remove existing element and replace with a new one
115 --
116 open get_old_element;
117 fetch get_old_element into l_pos_structure_element_id, l_object_version_number;
118 if get_old_element%found then
119 close get_old_element;
120 --
121 per_pse_del.del
122 (p_pos_structure_element_id => l_pos_structure_element_id
123 ,p_object_version_number => l_object_version_number
124 ,p_hr_installed => p_hr_installed
125 ,p_chk_children => 'N'
126 );
127 --
128 else
129 close get_old_element;
130 end if;
131 end if;
132 --
133 per_pse_ins.ins
134 (p_business_group_id => p_business_group_id
135 ,p_pos_structure_version_id => p_pos_structure_version_id
136 ,p_subordinate_position_id => p_subordinate_position_id
137 ,p_parent_position_id => p_parent_position_id
138 ,p_effective_date => l_effective_date
139 ,p_pos_structure_element_id => p_pos_structure_element_id
140 ,p_object_version_number => p_object_version_number
141 );
142 --
143 -- Post-insert code
144 --
145 --
146 --
147 -- Call After Process User Hook
148 --
149 begin
150 hr_pos_hierarchy_ele_bk1.create_pos_hierarchy_ele_a
151 (p_parent_position_id => p_parent_position_id
152 ,p_pos_structure_version_id => p_pos_structure_version_id
153 ,p_subordinate_position_id => p_subordinate_position_id
154 ,p_business_group_id => p_business_group_id
155 );
156 exception
157 when hr_api.cannot_find_prog_unit then
158 hr_api.cannot_find_prog_unit_error
159 (p_module_name => 'create_pos_hierarchy_ele'
160 ,p_hook_type => 'AP'
161 );
162 end;
163 --
164 -- When in validation only mode raise the Validate_Enabled exception
165 --
166 if p_validate then
167 raise hr_api.validate_enabled;
168 end if;
169 --
170 -- Set all output arguments
171 -- use values returned from ins (PZWALKER)
172 --
173 hr_utility.set_location(' Leaving:'||l_proc, 70);
174 exception
175 when hr_api.validate_enabled then
176 --
177 -- As the Validate_Enabled exception has been raised
178 -- we must rollback to the savepoint
179 --
180 rollback to create_pos_hierarchy_ele;
181 --
182 -- Only set output warning arguments
183 -- (Any key or derived arguments must be set to null
184 -- when validation only mode is being used.)
185 --
186 p_pos_structure_element_id := null;
187 p_object_version_number := null;
188 hr_utility.set_location(' Leaving:'||l_proc, 80);
189 when others then
190 --
191 -- A validation or unexpected error has occured
192 --
193 p_pos_structure_element_id := null;
194 p_object_version_number := null;
195 rollback to create_pos_hierarchy_ele;
196 hr_utility.set_location(' Leaving:'||l_proc, 90);
197 raise;
198 end create_pos_hierarchy_ele;
199 --
200 --
201 -- ----------------------------------------------------------------------------
202 -- |--------------------------< update_pos_hierarchy_ele >--------------------|
203 -- ----------------------------------------------------------------------------
204 --
205 procedure update_pos_hierarchy_ele
206 (p_validate in boolean default false
207 ,p_pos_structure_element_id in number
208 ,p_effective_date in date
209 ,p_parent_position_id in number default hr_api.g_number
210 ,p_subordinate_position_id in number default hr_api.g_number
211 ,p_object_version_number in out nocopy number
212 ) is
213 --
214 -- Declare cursors and local variables
215 --
216 l_effective_date date;
217 l_proc varchar2(72) := g_package||'update_pos_hierarchy_ele';
218 l_object_version_number per_pos_structure_elements
219 .object_version_number%TYPE;
220 l_temp_ovn number := p_object_version_number;
221 begin
222 hr_utility.set_location('Entering:'|| l_proc, 10);
223 --
224 l_object_version_number := p_object_version_number;
225 --
226 -- Issue a savepoint
227 --
228 savepoint update_pos_hierarchy_ele;
229 --
230 -- Truncate the time portion from all IN date parameters
231 --
232 l_effective_date := trunc(p_effective_date);
233
234 --
235 -- Call Before Process User Hook
236 --
237 begin
238 hr_pos_hierarchy_ele_bk2.update_pos_hierarchy_ele_b
239 (p_pos_structure_element_id => p_pos_structure_element_id
240 ,p_parent_position_id => p_parent_position_id
241 ,p_subordinate_position_id => p_subordinate_position_id
242 );
243 exception
244 when hr_api.cannot_find_prog_unit then
245 hr_api.cannot_find_prog_unit_error
246 (p_module_name => 'update_pos_hierarchy_ele'
247 ,p_hook_type => 'BP'
248 );
249 end;
250 --
251 -- Validation in addition to Row Handlers
252 --
253 --
254 -- Process Logic
255 --
256 per_pse_upd.upd
257 (p_pos_structure_element_id => p_pos_structure_element_id
258 ,p_object_version_number => l_object_version_number
259 ,p_effective_date => l_effective_date
260 ,p_parent_position_id => p_parent_position_id
261 ,p_subordinate_position_id => p_subordinate_position_id
262 );
263 --
264 -- Call After Process User Hook
265 --
266 begin
267 hr_pos_hierarchy_ele_bk2.update_pos_hierarchy_ele_a
268 (p_pos_structure_element_id => p_pos_structure_element_id
269 ,p_parent_position_id => p_parent_position_id
270 ,p_subordinate_position_id => p_subordinate_position_id
271 );
272 exception
273 when hr_api.cannot_find_prog_unit then
274 hr_api.cannot_find_prog_unit_error
275 (p_module_name => 'update_pos_hierarchy_ele'
276 ,p_hook_type => 'AP'
277 );
278 end;
279 --
280 -- When in validation only mode raise the Validate_Enabled exception
281 --
282 if p_validate then
283 raise hr_api.validate_enabled;
284 end if;
285 --
286 -- Set all output arguments
287 --
288 p_object_version_number := l_object_version_number;
289 --
290 hr_utility.set_location(' Leaving:'||l_proc, 70);
291 exception
292 when hr_api.validate_enabled then
293 --
294 -- As the Validate_Enabled exception has been raised
295 -- we must rollback to the savepoint
296 --
297 rollback to update_pos_hierarchy_ele;
298 --
299 -- Only set output warning arguments
300 -- (Any key or derived arguments must be set to null
301 -- when validation only mode is being used.)
302 --
303 p_object_version_number := p_object_version_number;
304 hr_utility.set_location(' Leaving:'||l_proc, 80);
305 when others then
306 --
307 -- A validation or unexpected error has occured
308 --
309 p_object_version_number := l_temp_ovn;
310 rollback to update_pos_hierarchy_ele;
311 hr_utility.set_location(' Leaving:'||l_proc, 90);
312 raise;
313 end update_pos_hierarchy_ele;
314 --
315 -- ----------------------------------------------------------------------------
316 -- |--------------------------< delete_pos_hierarchy_ele >--------------------|
317 -- ----------------------------------------------------------------------------
318 --
319 procedure delete_pos_hierarchy_ele
320 (p_validate in boolean default false
321 ,p_pos_structure_element_id in number
322 ,p_object_version_number in number
323 ,p_hr_installed in VARCHAR2
324 ) is
325 --
326 -- Declare cursors and local variables
327 --
328 l_proc varchar2(72) := g_package||'delete_pos_hierarchy_ele';
329 begin
330 hr_utility.set_location('Entering:'|| l_proc, 10);
331 --
332 --
333 -- Issue a savepoint
334 --
335 savepoint delete_pos_hierarchy_ele;
336 --
337 -- Truncate the time portion from all IN date parameters
338 --
339 --
340 -- Call Before Process User Hook
341 --
342 begin
343 hr_pos_hierarchy_ele_bk3.delete_pos_hierarchy_ele_b
344 (p_pos_structure_element_id => p_pos_structure_element_id
345 ,p_object_version_number => p_object_version_number
346 );
347 exception
348 when hr_api.cannot_find_prog_unit then
349 hr_api.cannot_find_prog_unit_error
350 (p_module_name => 'delete_pos_hierarchy_ele'
351 ,p_hook_type => 'BP'
352 );
353 end;
354 --
355 -- Validation in addition to Row Handlers
356 --
357 --
358 -- Process Logic
359 --
360 per_pse_del.del
361 (p_pos_structure_element_id => p_pos_structure_element_id
362 ,p_object_version_number => p_object_version_number
363 ,p_hr_installed => p_hr_installed
364 ,p_chk_children => 'Y'
365 );
366 --
367 -- Call After Process User Hook
368 --
369 begin
370 hr_pos_hierarchy_ele_bk3.delete_pos_hierarchy_ele_a
371 (p_pos_structure_element_id => p_pos_structure_element_id
372 ,p_object_version_number => p_object_version_number
373 );
374 exception
375 when hr_api.cannot_find_prog_unit then
376 hr_api.cannot_find_prog_unit_error
377 (p_module_name => 'delete_pos_hierarchy_ele'
378 ,p_hook_type => 'AP'
379 );
380 end;
381 --
382 -- When in validation only mode raise the Validate_Enabled exception
383 --
384 if p_validate then
385 raise hr_api.validate_enabled;
386 end if;
387 --
388 -- Set all output arguments
389 --
390 --
391 hr_utility.set_location(' Leaving:'||l_proc, 70);
392 exception
393 when hr_api.validate_enabled then
394 --
395 -- As the Validate_Enabled exception has been raised
396 -- we must rollback to the savepoint
397 --
398 rollback to delete_pos_hierarchy_ele;
399 --
400 -- Only set output warning arguments
401 -- (Any key or derived arguments must be set to null
402 -- when validation only mode is being used.)
403 --
404 hr_utility.set_location(' Leaving:'||l_proc, 80);
405 when others then
406 --
407 -- A validation or unexpected error has occured
408 --
409 rollback to delete_pos_hierarchy_ele;
410 hr_utility.set_location(' Leaving:'||l_proc, 90);
411 raise;
412 end delete_pos_hierarchy_ele;
413 --
414 --
415 -- ----------------------------------------------------------------------------
416 -- |--------------------------< create_pos_hier_elem_internal >--------------------|
417 -- ----------------------------------------------------------------------------
418 --
419 procedure create_pos_hier_elem_internal
420 (p_parent_position_id in number
421 ,p_pos_structure_version_id in number
422 ,p_subordinate_position_id in number
423 ,p_business_group_id in number
424 ,p_hr_installed in VARCHAR2
425 ,p_effective_date in date
426 ,p_pos_structure_element_id out nocopy number
427 ,p_object_version_number out nocopy number
428 ) is
429 begin
430 hr_pos_hierarchy_ele_api.create_pos_hierarchy_ele
431 (p_validate => false
432 ,p_parent_position_id => p_parent_position_id
433 ,p_pos_structure_version_id => p_pos_structure_version_id
434 ,p_subordinate_position_id => p_subordinate_position_id
435 ,p_business_group_id => p_business_group_id
436 ,p_hr_installed => p_hr_installed
437 ,p_pos_structure_element_id => p_pos_structure_element_id
438 ,p_effective_date => p_effective_date
439 ,p_object_version_number => p_object_version_number
440 );
441 end;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |--------------------------< update_pos_hier_elem_internal >--------------------|
445 -- ----------------------------------------------------------------------------
446 --
447 procedure update_pos_hier_elem_internal
448 (p_pos_structure_element_id in number
449 ,p_parent_position_id in number default hr_api.g_number
450 ,p_subordinate_position_id in number default hr_api.g_number
451 ,p_effective_date in date
452 ,p_object_version_number in out nocopy number
453 ) is
454 begin
455 hr_pos_hierarchy_ele_api.update_pos_hierarchy_ele
456 (p_validate => false
457 ,p_pos_structure_element_id => p_pos_structure_element_id
458 ,p_parent_position_id => p_parent_position_id
459 ,p_subordinate_position_id => p_subordinate_position_id
460 ,p_effective_date => p_effective_date
461 ,p_object_version_number => p_object_version_number
462 );
463 end;
464 --
465 -- ----------------------------------------------------------------------------
466 -- |--------------------------< delete_pos_hier_elem_internal >--------------------|
467 -- ----------------------------------------------------------------------------
468 --
469 procedure delete_pos_hier_elem_internal
470 (p_pos_structure_element_id in number
471 ,p_object_version_number in number
472 ,p_hr_installed in VARCHAR2
473 ) is
474 begin
475 hr_pos_hierarchy_ele_api.delete_pos_hierarchy_ele
476 (p_validate => false
477 ,p_pos_structure_element_id => p_pos_structure_element_id
478 ,p_object_version_number => p_object_version_number
479 ,p_hr_installed => p_hr_installed
480 );
481 end;
482 -- -
483 end hr_pos_hierarchy_ele_api;