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