1 Package Body PQH_RATE_ELEMENT_RELATIONS_API as
2 /* $Header: pqrerapi.pkb 120.0 2005/10/06 14:53:06 srajakum noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'PQH_RATE_ELEMENT_RELATIONS_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------<create_rate_element_relation >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_rate_element_relation
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_rate_element_relation_id in out nocopy number
16 ,p_criteria_rate_element_id in number
17 ,p_relation_type_cd in varchar2
18 ,p_rel_element_type_id in number
19 ,p_rel_input_value_id in number default null
20 ,p_business_group_id in number default null
21 ,p_legislation_code in varchar2 default null
22 ,p_object_version_number out nocopy number
23 )is
24
25 --
26 -- Declare cursors and local variables
27 --
28 l_effective_date date;
29 l_rate_element_relation_id PQH_RATE_ELEMENT_RELATIONS.rate_element_relation_id%TYPE;
30 l_object_version_number PQH_RATE_ELEMENT_RELATIONS.object_version_number%TYPE;
31 l_proc varchar2(72) := g_package||'create_rate_element_relation';
32 begin
33 hr_utility.set_location('Entering:'|| l_proc, 10);
34 --
35 -- Issue a savepoint
36 --
37 savepoint create_rate_element_relation;
38 --
39 -- Remember IN OUT parameter IN values
40 --
41
42 --
43 -- Truncate the time portion from all IN date parameters
44 --
45 l_effective_date := trunc(p_effective_date);
46
47 --
48 -- Call Before Process User Hook
49 --
50
51 begin
52
53 /* Header
54
55 procedure create_rate_element_relation_b
56 (
57 p_effective_date in date
58 ,p_criteria_rate_element_id in number
59 ,p_relation_type_cd in varchar2
60 ,p_rel_element_type_id in number
61 ,p_rel_input_value_id in number
62 ,p_business_group_id in number
63 ,p_legislation_code in varchar2
64 )
65
66 */
67
68 PQH_RATE_ELEMENT_RELATIONS_BK1.create_rate_element_relation_b
69 (p_effective_date => l_effective_date
70 ,p_criteria_rate_element_id => p_criteria_rate_element_id
71 ,p_relation_type_cd => p_relation_type_cd
72 ,p_rel_element_type_id => p_rel_element_type_id
73 ,p_rel_input_value_id => p_rel_input_value_id
74 ,p_business_group_id => p_business_group_id
75 ,p_legislation_code => p_legislation_code
76 );
77 exception
78 when hr_api.cannot_find_prog_unit then
79 hr_api.cannot_find_prog_unit_error
80 (p_module_name => 'create_rate_element_relation'
81 ,p_hook_type => 'BP'
82 );
83 end;
84
85
86 --
87 -- Validation in addition to Row Handlers
88 --
89 --
90 -- Process Logic
91 --
92 PQH_RER_INS.ins
93 (p_effective_date => p_effective_date
94 ,p_criteria_rate_element_id => p_criteria_rate_element_id
95 ,p_relation_type_cd => p_relation_type_cd
96 ,p_rel_element_type_id => p_rel_element_type_id
97 ,p_rel_input_value_id => p_rel_input_value_id
98 ,p_business_group_id => p_business_group_id
99 ,p_legislation_code => p_legislation_code
100 ,p_rate_element_relation_id => l_rate_element_relation_id
101 ,p_object_version_number => l_object_version_number
102 );
103
104
105 --
106 -- Call After Process User Hook
107 --
108 /* HEADER
109 procedure create_rate_element_relation_a
110 (
111 p_effective_date in date
112 ,p_rate_element_relation_id in number
113 ,p_criteria_rate_element_id in number
114 ,p_relation_type_cd in varchar2
115 ,p_rel_element_type_id in number
116 ,p_rel_input_value_id in number
117 ,p_business_group_id in number
118 ,p_legislation_code in varchar2
119 ,p_object_version_number in number
120 );
121 */
122
123
124 begin
125 PQH_RATE_ELEMENT_RELATIONS_BK1.create_rate_element_relation_a
126 (
127 p_effective_date => l_effective_date
128 ,p_criteria_rate_element_id => p_criteria_rate_element_id
129 ,p_relation_type_cd => p_relation_type_cd
130 ,p_rel_element_type_id => p_rel_element_type_id
131 ,p_rel_input_value_id => p_rel_input_value_id
132 ,p_business_group_id => p_business_group_id
133 ,p_legislation_code => p_legislation_code
134 ,p_rate_element_relation_id => l_rate_element_relation_id
135 ,p_object_version_number => l_object_version_number
136 );
137 exception
138 when hr_api.cannot_find_prog_unit then
139 hr_api.cannot_find_prog_unit_error
140 (p_module_name => 'create_rate_element_relation'
141 ,p_hook_type => 'AP'
142 );
143 end;
144 --
145 -- When in validation only mode raise the Validate_Enabled exception
146 --
147 if p_validate then
148 raise hr_api.validate_enabled;
149 end if;
150 --
151 -- Set all IN OUT and OUT parameters with out values
152 --
153 p_rate_element_relation_id := l_rate_element_relation_id;
154 p_object_version_number := l_object_version_number;
155 --
156 hr_utility.set_location(' Leaving:'||l_proc, 70);
157 exception
158 when hr_api.validate_enabled then
159 --
160 -- As the Validate_Enabled exception has been raised
161 -- we must rollback to the savepoint
162 --
163 rollback to create_rate_element_relation;
164 --
165 -- Reset IN OUT parameters and set OUT parameters
166 -- (Any key or derived arguments must be set to null
167 -- when validation only mode is being used.)
168 --
169 p_rate_element_relation_id := null;
170 p_object_version_number := null;
171
172 hr_utility.set_location(' Leaving:'||l_proc, 80);
173 when others then
174 --
175 -- A validation or unexpected error has occured
176 --
177 rollback to create_rate_element_relation;
178 --
179 -- Reset IN OUT parameters and set all
180 -- OUT parameters, including warnings, to null
181 --
182 p_rate_element_relation_id := null;
183 p_object_version_number := null;
184
185 hr_utility.set_location(' Leaving:'||l_proc, 90);
186 raise;
187 end create_rate_element_relation;
188
189
190
191 --
192 -- ----------------------------------------------------------------------------
193 -- |--------------------------< update_rate_element_relation >--------------------------|
194 -- ----------------------------------------------------------------------------
195 --
196 procedure update_rate_element_relation
197 (p_validate in boolean default false
198 ,p_effective_date in date
199 ,p_rate_element_relation_id in number
200 ,p_criteria_rate_element_id in number default hr_api.g_number
201 ,p_relation_type_cd in varchar2 default hr_api.g_varchar2
202 ,p_rel_element_type_id in number default hr_api.g_number
203 ,p_rel_input_value_id in number default hr_api.g_number
204 ,p_business_group_id in number default hr_api.g_number
205 ,p_legislation_code in varchar2 default hr_api.g_varchar2
206 ,p_object_version_number in out nocopy number
207 )is
208 --
209 -- Declare cursors and local variables
210 --
211 l_effective_date date;
212 l_proc varchar2(72) := g_package||'update_rate_element_relation';
213 l_object_version_number PQH_RATE_ELEMENT_RELATIONS.object_version_number%TYPE;
214 begin
215 hr_utility.set_location('Entering:'|| l_proc, 10);
216 --
217 -- Issue a savepoint
218 --
219 savepoint update_rate_element_relation;
220 --
221 -- Remember IN OUT parameter IN values
222 --
223 l_object_version_number := p_object_version_number;
224
225 --
226 -- Truncate the time portion from all IN date parameters
227 --
228 l_effective_date := trunc(p_effective_date);
229
230 --
231 -- Call Before Process User Hook
232 --
233
234 /* HEADER
235 --
236 procedure update_rate_element_relation_b
237 (
238 p_effective_date in date
239 ,p_rate_element_relation_id in number
240 ,p_criteria_rate_element_id in number
241 ,p_relation_type_cd in varchar2
242 ,p_rel_element_type_id in number
243 ,p_rel_input_value_id in number
244 ,p_business_group_id in number
245 ,p_legislation_code in varchar2
246 ,p_object_version_number in number
247 );
248 */
249
250 begin
251 PQH_RATE_ELEMENT_RELATIONS_BK2.update_rate_element_relation_b
252 (
253 p_effective_date => l_effective_date
254 ,p_criteria_rate_element_id => p_criteria_rate_element_id
255 ,p_relation_type_cd => p_relation_type_cd
256 ,p_rel_element_type_id => p_rel_element_type_id
257 ,p_rel_input_value_id => p_rel_input_value_id
258 ,p_business_group_id => p_business_group_id
259 ,p_legislation_code => p_legislation_code
260 ,p_rate_element_relation_id => p_rate_element_relation_id
261 ,p_object_version_number => l_object_version_number
262 );
263 exception
264 when hr_api.cannot_find_prog_unit then
265 hr_api.cannot_find_prog_unit_error
266 (p_module_name => 'update_rate_element_relation'
267 ,p_hook_type => 'BP'
268 );
269 end;
270 --
271 -- Validation in addition to Row Handlers
272 --
273
274
275
276 --
277 -- Process Logic
278 --
279 PQH_RER_UPD.upd
280 (p_effective_date => p_effective_date
281 ,p_criteria_rate_element_id => p_criteria_rate_element_id
282 ,p_relation_type_cd => p_relation_type_cd
283 ,p_rel_element_type_id => p_rel_element_type_id
284 ,p_rel_input_value_id => p_rel_input_value_id
285 ,p_business_group_id => p_business_group_id
286 ,p_legislation_code => p_legislation_code
287 ,p_rate_element_relation_id => p_rate_element_relation_id
288 ,p_object_version_number => l_object_version_number
289 );
290
291 --
292 -- Call After Process User Hook
293 --
294 begin
295 PQH_RATE_ELEMENT_RELATIONS_BK2.update_rate_element_relation_a
296 (
297 p_effective_date => l_effective_date
298 ,p_criteria_rate_element_id => p_criteria_rate_element_id
299 ,p_relation_type_cd => p_relation_type_cd
300 ,p_rel_element_type_id => p_rel_element_type_id
301 ,p_rel_input_value_id => p_rel_input_value_id
302 ,p_business_group_id => p_business_group_id
303 ,p_legislation_code => p_legislation_code
304 ,p_rate_element_relation_id => p_rate_element_relation_id
305 ,p_object_version_number => l_object_version_number
306 );
307 exception
308 when hr_api.cannot_find_prog_unit then
309 hr_api.cannot_find_prog_unit_error
310 (p_module_name => 'update_rate_element_relation'
311 ,p_hook_type => 'AP'
312 );
313 end;
314 --
315 -- When in validation only mode raise the Validate_Enabled exception
316 --
317 if p_validate then
318 raise hr_api.validate_enabled;
319 end if;
320 --
321 -- Set all IN OUT and OUT parameters with out values
322 --
323 p_object_version_number := l_object_version_number;
324 --
325 hr_utility.set_location(' Leaving:'||l_proc, 70);
326 exception
327 when hr_api.validate_enabled then
328 --
329 -- As the Validate_Enabled exception has been raised
330 -- we must rollback to the savepoint
331 --
332 rollback to update_rate_element_relation;
333 --
334 -- Reset IN OUT parameters and set OUT parameters
335 -- (Any key or derived arguments must be set to null
336 -- when validation only mode is being used.)
337 --
338 p_object_version_number := p_object_version_number;
339 hr_utility.set_location(' Leaving:'||l_proc, 80);
340 when others then
341 --
342 -- A validation or unexpected error has occured
343 --
344 rollback to update_rate_element_relation;
345 --
346 -- Reset IN OUT parameters and set all
347 -- OUT parameters, including warnings, to null
348 --
349 p_object_version_number := p_object_version_number;
350 hr_utility.set_location(' Leaving:'||l_proc, 90);
351 raise;
352 end update_rate_element_relation;
353
354
355 --
356 -- ----------------------------------------------------------------------------
357 -- |--------------------------< delete_rate_element_relation >--------------------------|
358 -- ----------------------------------------------------------------------------
359 --
360 procedure delete_rate_element_relation
361 (p_validate in boolean default false
362 ,p_effective_date in date
363 ,p_rate_element_relation_id in number
364 ,p_object_version_number in number
365 ) is
366 --
367 -- Declare cursors and local variables
368 --
369
370 l_effective_date date;
371 l_proc varchar2(72) := g_package||'delete_rate_element_relation';
372 begin
373 hr_utility.set_location('Entering:'|| l_proc, 10);
374 --
375 -- Issue a savepoint
376 --
377 savepoint delete_rate_element_relation;
378 --
379 -- Remember IN OUT parameter IN values
380 --
381
382 --
383 -- Truncate the time portion from all IN date parameters
384 --
385 l_effective_date := trunc(p_effective_date);
386
387 --
388 -- Call Before Process User Hook
389 --
390 begin
391 PQH_RATE_ELEMENT_RELATIONS_BK3.delete_rate_element_relation_b
392 ( p_effective_date => l_effective_date
393 ,p_rate_element_relation_id => p_rate_element_relation_id
394 ,p_object_version_number => p_object_version_number
395 );
396 exception
397 when hr_api.cannot_find_prog_unit then
398 hr_api.cannot_find_prog_unit_error
399 (p_module_name => 'delete_rate_element_relation'
400 ,p_hook_type => 'BP'
401 );
402 end;
403 --
404 -- Validation in addition to Row Handlers
405 --
406
407
408
409 --
410 -- Process Logic
411 --
412 PQH_RER_DEL.del
413 (
414 p_rate_element_relation_id =>p_rate_element_relation_id
415 ,p_object_version_number =>p_object_version_number
416 );
417
418
419 --
420 -- Call After Process User Hook
421 --
422 begin
423 PQH_RATE_ELEMENT_RELATIONS_BK3.delete_rate_element_relation_a
424 (p_effective_date => l_effective_date
425 ,p_rate_element_relation_id => p_rate_element_relation_id
426 ,p_object_version_number => p_object_version_number
427 );
428 exception
429 when hr_api.cannot_find_prog_unit then
430 hr_api.cannot_find_prog_unit_error
431 (p_module_name => 'delete_rate_element_relation'
432 ,p_hook_type => 'AP'
433 );
434 end;
435 --
436 -- When in validation only mode raise the Validate_Enabled exception
437 --
438 if p_validate then
439 raise hr_api.validate_enabled;
440 end if;
441 --
442 -- Set all IN OUT and OUT parameters with out values
443 --
444 --
445 hr_utility.set_location(' Leaving:'||l_proc, 70);
446 exception
447 when hr_api.validate_enabled then
448 --
449 -- As the Validate_Enabled exception has been raised
450 -- we must rollback to the savepoint
451 --
452 rollback to delete_rate_element_relation;
453 --
454 -- Reset IN OUT parameters and set OUT parameters
455 -- (Any key or derived arguments must be set to null
456 -- when validation only mode is being used.)
457 --
458 hr_utility.set_location(' Leaving:'||l_proc, 80);
459 when others then
460 --
461 -- A validation or unexpected error has occured
462 --
463 rollback to delete_rate_element_relation;
464 --
465 -- Reset IN OUT parameters and set all
466 -- OUT parameters, including warnings, to null
467 --
468 hr_utility.set_location(' Leaving:'||l_proc, 90);
469 raise;
470 end delete_rate_element_relation;
471 --
472 --
473 end PQH_RATE_ELEMENT_RELATIONS_API;