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