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