[Home] [Help]
PACKAGE BODY: APPS.PAY_CNU_API
Source
1 Package Body PAY_CNU_API as
2 /* $Header: pycnuapi.pkb 115.6 2004/02/06 04:53:20 sspratur noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_CNU_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_CONTRIBUTION_USAGE >--------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_contribution_usage(
13 p_validate IN boolean default false
14 ,p_effective_date IN date
15 ,p_date_from IN date
16 ,p_date_to IN date default null
17 ,p_group_code IN varchar2
18 ,p_process_type IN varchar2
19 ,p_element_name IN varchar2
20 ,p_contribution_usage_type IN varchar2
21 ,p_rate_type IN varchar2 default null
22 ,p_rate_category IN varchar2
23 ,p_contribution_code IN varchar2 default null
24 ,p_contribution_type IN varchar2
25 ,p_retro_contribution_code IN varchar2 default null
26 ,p_business_group_id IN varchar2 default null
27 ,p_object_version_number OUT nocopy number
28 ,p_contribution_usage_id OUT nocopy number
29 ,p_code_rate_id IN OUT nocopy number
30 ) is
31 --
32 -- Declare cursors and local variables
33 --
34 l_proc varchar2(72) := g_package||' Create Contribution Usage';
35 l_contribution_usage_id number;
36 l_object_version_number number;
37 l_effective_date date;
38 l_date_from date;
39 l_date_to date;
40 l_adj_cu_id number;
41 l_adj_ovn number;
42 l_adj_date_to date;
43 l_code_Rate_id number;
44 l_original_code_Rate_id number;
45 --
46 cursor csr_adjust is
47 select cnu.contribution_usage_id, cnu.object_version_number
48 from pay_fr_contribution_usages cnu
49 where cnu.group_code = p_group_code
50 and cnu.process_type = p_process_type
51 and cnu.element_name = p_element_name
52 and cnu.contribution_usage_type = p_contribution_usage_type
53 and cnu.date_to is null
54 and cnu.date_from < p_date_from
55 and ( nvl(p_business_group_id, -1)
56 = nvl(cnu.business_group_id, -1)
57 );
58 --
59 begin
60 hr_utility.set_location('Entering:'|| l_proc, 10);
61 --
62 -- Issue a savepoint
63 --
64 savepoint CREATE_CONTRIBUTION_USAGE;
65 --
66 -- Truncate the time portion from all IN date parameters
67 --
68 l_effective_date := trunc(p_effective_date);
69 l_date_from := trunc(p_date_from);
70 l_date_to := trunc(p_date_to);
71 l_code_Rate_id := p_code_Rate_id;
72 l_original_code_Rate_id := p_code_Rate_id;
73 --
74 -- update an existing record if necessary
75 --
76 Open csr_adjust;
77 Fetch csr_adjust into l_adj_cu_id, l_adj_ovn;
78 if csr_adjust%FOUND
79 then
80 -- a row exists and needs adjusting. Always set validate to false
81 -- as this insert rollback will rollback the update.
82 --
83 close csr_adjust;
84 pay_cnu_api.update_contribution_usage
85 (p_validate => FALSE
86 ,p_effective_date => l_effective_date
87 ,p_contribution_usage_id => l_adj_cu_id
88 ,p_date_to => l_date_from-1
89 ,p_contribution_code => p_contribution_code
90 ,p_contribution_type => p_contribution_type
91 ,p_object_version_number => l_adj_ovn
92 ,p_code_rate_id => l_code_rate_id
93 );
94 else
95 close csr_adjust;
96 end if;
97 --
98 -- Call Before Process User Hook
99 --
100 begin
101 pay_cnu_api_bk1.create_contribution_usage_b
102 (p_effective_date => l_effective_date
103 ,p_date_from => l_date_from
104 ,p_date_to => l_date_to
105 ,p_group_code => p_group_code
106 ,p_process_type => p_process_type
107 ,p_element_name => p_element_name
108 ,p_contribution_usage_type => p_contribution_usage_type
109 ,p_rate_type => p_rate_type
110 ,p_rate_category => p_rate_category
111 ,p_contribution_code => p_contribution_code
112 ,p_contribution_type => p_contribution_type
113 ,p_retro_contribution_code => p_retro_contribution_code
114 ,p_business_group_id => p_business_group_id
115 ,p_code_Rate_id => l_code_Rate_id
116 );
117 exception
118 when hr_api.cannot_find_prog_unit then
119 hr_api.cannot_find_prog_unit_error
120 (p_module_name => 'CREATE_CONTRIBUTION_USAGE'
121 ,p_hook_type => 'BP'
122 );
123 end;
124 --
125 -- Validation in addition to Row Handlers
126 --
127 --
128 -- Process Logic
129 --
130 pay_cnu_ins.ins
131 (p_effective_date => l_effective_date
132 ,p_date_from => l_date_from
133 ,p_date_to => l_date_to
134 ,p_group_code => p_group_code
135 ,p_process_type => p_process_type
136 ,p_element_name => p_element_name
137 ,p_contribution_usage_type => p_contribution_usage_type
138 ,p_rate_type => p_rate_type
139 ,p_rate_category => p_rate_category
140 ,p_contribution_code => p_contribution_code
141 ,p_contribution_type => p_contribution_type
142 ,p_retro_contribution_code => p_retro_contribution_code
143 ,p_business_group_id => p_business_group_id
144 ,p_contribution_usage_id => l_contribution_usage_id
145 ,p_object_version_number => l_object_version_number
146 ,p_code_Rate_id => l_code_Rate_id
147 );
148 --
149 -- Call After Process User Hook
150 --
151 begin
152 pay_cnu_api_bk1.create_contribution_usage_a
153 (p_effective_date => l_effective_date
154 ,p_date_from => l_date_from
155 ,p_date_to => l_date_to
156 ,p_group_code => p_group_code
157 ,p_process_type => p_process_type
158 ,p_element_name => p_element_name
159 ,p_contribution_usage_type => p_contribution_usage_type
160 ,p_rate_type => p_rate_type
161 ,p_rate_category => p_rate_category
162 ,p_contribution_code => p_contribution_code
163 ,p_contribution_type => p_contribution_type
164 ,p_retro_contribution_code => p_retro_contribution_code
165 ,p_business_group_id => p_business_group_id
166 ,p_contribution_usage_id => l_contribution_usage_id
167 ,p_object_version_number => l_object_version_number
168 ,p_code_Rate_id => l_code_Rate_id
169 );
170 exception
171 when hr_api.cannot_find_prog_unit then
172 hr_api.cannot_find_prog_unit_error
173 (p_module_name => 'CREATE_CONTRIBUTION_USAGE'
174 ,p_hook_type => 'AP'
175 );
176 end;
177 --
178 -- When in validation only mode raise the Validate_Enabled exception
179 --
180 if p_validate then
181 raise hr_api.validate_enabled;
182 end if;
183 --
184 -- Set all output arguments
185 --
186 p_contribution_usage_id := l_contribution_usage_id;
187 p_object_version_number := l_object_version_number;
188 p_code_Rate_id := l_code_Rate_id;
189 --
190 hr_utility.set_location(' Leaving:'||l_proc, 70);
191 exception
192 when hr_api.validate_enabled then
193 --
194 -- As the Validate_Enabled exception has been raised
195 -- we must rollback to the savepoint
196 --
197 rollback to CREATE_CONTRIBUTION_USAGE;
198 --
199 -- Only set output warning arguments
200 -- (Any key or derived arguments must be set to null
201 -- when validation only mode is being used.)
202 --
203 p_contribution_usage_id := null;
204 p_object_version_number := null;
205 p_code_Rate_id := l_original_code_Rate_id;
206 hr_utility.set_location(' Leaving:'||l_proc, 80);
207 when others then
208 --
209 -- A validation or unexpected error has occured
210 --
211 rollback to CREATE_CONTRIBUTION_USAGE;
212 hr_utility.set_location(' Leaving:'||l_proc, 90);
213 --
214 -- Reset IN OUT params and set OUT params.
215 --
216 p_contribution_usage_id := null;
217 p_object_version_number := null;
218 p_code_Rate_id := l_original_code_Rate_id;
219 --
220 raise;
221 end create_contribution_usage;
222 -- ----------------------------------------------------------------------------
223 -- |-------------------------< UPDATE_CONTRIBUTION_USAGE >--------------------|
224 -- ----------------------------------------------------------------------------
225 --
226 procedure update_contribution_usage
227 (p_validate IN boolean default false
228 ,p_effective_date IN date
229 ,p_contribution_usage_id IN number
230 ,p_date_to IN date default hr_api.g_date
231 ,p_contribution_code IN varchar2
232 ,p_contribution_type IN varchar2
233 ,p_retro_contribution_code IN varchar2 default hr_api.g_varchar2
234 ,p_object_version_number IN OUT nocopy number
235 ,p_code_rate_id IN varchar2
236 ) is
237 --
238 -- Declare cursors and local variables
239 --
240 l_proc varchar2(72) := g_package||' Update Contribution Usage';
241 l_effective_date date;
242 l_date_to date;
243 l_object_version_number number;
244 begin
245 hr_utility.set_location('Entering:'|| l_proc, 10);
246 --
247 -- Issue a savepoint
248 --
249 savepoint UPDATE_CONTRIBUTION_USAGE;
250 --
251 -- Truncate the time portion from all IN date parameters
252 --
253 l_effective_date := trunc(p_effective_date);
254 l_object_version_number := p_object_version_number;
255 l_date_to := trunc(p_date_to);
256 --
257 -- Call Before Process User Hook
258 --
259 begin
260 pay_cnu_api_bk2.update_contribution_usage_b
261 (p_effective_date => l_effective_date
262 ,p_contribution_usage_id => p_contribution_usage_id
263 ,p_object_version_number => l_object_version_number
264 ,p_date_to => l_date_to
265 ,p_contribution_code => p_contribution_code
266 ,p_contribution_type => p_contribution_type
267 ,p_retro_contribution_code => p_retro_contribution_code
268 ,p_code_rate_id => p_code_rate_id
269 );
270 exception
271 when hr_api.cannot_find_prog_unit then
272 hr_api.cannot_find_prog_unit_error
273 (p_module_name => 'UPDATE_CONTRIBUTION_USAGE'
274 ,p_hook_type => 'BP'
275 );
276 end;
277 --
278 -- Validation in addition to Row Handlers
279 --
280 --
281 -- Process Logic
282 --
283 pay_cnu_upd.upd
284 (p_effective_date => l_effective_date
285 ,p_contribution_usage_id => p_contribution_usage_id
286 ,p_date_to => l_date_to
287 ,p_contribution_code => p_contribution_code
288 ,p_contribution_type => p_contribution_type
289 ,p_retro_contribution_code => p_retro_contribution_code
290 ,p_object_version_number => l_object_version_number
291 ,p_code_rate_id => p_code_rate_id
292 );
293 --
294 -- Call After Process User Hook
295 --
296 begin
297 pay_cnu_api_bk2.update_contribution_usage_a
298 (p_effective_date => l_effective_date
299 ,p_contribution_usage_id => p_contribution_usage_id
300 ,p_object_version_number => l_object_version_number
301 ,p_date_to => l_date_to
302 ,p_contribution_code => p_contribution_code
303 ,p_contribution_type => p_contribution_type
304 ,p_retro_contribution_code => p_retro_contribution_code
305 ,p_code_rate_id => p_code_rate_id
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_CONTRIBUTION_USAGE'
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 output arguments
322 p_object_version_number := l_object_version_number;
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 70);
325 exception
326 when hr_api.validate_enabled then
327 --
328 -- As the Validate_Enabled exception has been raised
329 -- we must rollback to the savepoint
330 --
331 rollback to UPDATE_CONTRIBUTION_USAGE;
332 --
333 -- Only set output warning arguments
334 -- (Any key or derived arguments must be set to null
335 -- when validation only mode is being used.)
336 --
337 p_object_version_number := null;
338 hr_utility.set_location(' Leaving:'||l_proc, 80);
339 when others then
340 --
341 -- A validation or unexpected error has occured
342 --
343 rollback to UPDATE_CONTRIBUTION_USAGE;
344 --
345 -- Reset IN OUT params and set OUT params.
346 --
347 p_object_version_number := l_object_version_number;
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 90);
350 raise;
351 end update_contribution_usage;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |-------------------------< DELETE_CONTRIBUTION_USAGE >--------------------|
355 -- ----------------------------------------------------------------------------
356 --
357 procedure delete_contribution_usage
358 (p_validate in boolean default false
359 ,p_contribution_usage_id in number
360 ,p_object_version_number in number
361 ) is
362 --
363 -- Declare cursors and local variables
364 --
365 l_proc varchar2(72) := g_package||' Delete Contribution Usage';
366 --
367 begin
368 hr_utility.set_location('Entering:'|| l_proc, 10);
369 --
370 -- Issue a savepoint
371 --
372 savepoint DELETE_CONTRIBUTION_USAGE;
373 --
374 -- Truncate the time portion from all IN date parameters
375 --
376 --
377 -- Call Before Process User Hook
378 --
379 begin
380 pay_cnu_api_bk3.delete_contribution_usage_b
381 (p_contribution_usage_id => p_contribution_usage_id
382 ,p_object_version_number => p_object_version_number
383 );
384 exception
385 when hr_api.cannot_find_prog_unit then
386 hr_api.cannot_find_prog_unit_error
390 end;
387 (p_module_name => 'DELETE_CONTRIBUTION_USAGE'
388 ,p_hook_type => 'BP'
389 );
391 --
392 -- Validation in addition to Row Handlers
393 --
394 --
395 -- Process Logic
396 --
397 pay_cnu_del.del
398 (p_contribution_usage_id => p_contribution_usage_id
399 ,p_object_version_number => p_object_version_number
400 );
401 --
402 -- Call After Process User Hook
403 --
404 begin
405 pay_cnu_api_bk3.delete_contribution_usage_a
406 (p_contribution_usage_id => p_contribution_usage_id
407 ,p_object_version_number => p_object_version_number
408 );
409 exception
410 when hr_api.cannot_find_prog_unit then
411 hr_api.cannot_find_prog_unit_error
412 (p_module_name => 'DELETE_CONTRIBUTION_USAGE'
413 ,p_hook_type => 'AP'
414 );
415 end;
416 --
417 -- When in validation only mode raise the Validate_Enabled exception
418 --
419 if p_validate then
420 raise hr_api.validate_enabled;
421 end if;
422 --
423 -- Set all output arguments
424 --
425 --
426 hr_utility.set_location(' Leaving:'||l_proc, 170);
427 exception
428 when hr_api.validate_enabled then
429 --
430 -- As the Validate_Enabled exception has been raised
431 -- we must rollback to the savepoint
432 --
433 rollback to DELETE_CONTRIBUTION_USAGE;
434 --
435 -- Only set output warning arguments
436 -- (Any key or derived arguments must be set to null
437 -- when validation only mode is being used.)
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 180);
440 when others then
441 --
442 -- A validation or unexpected error has occured
443 --
444 rollback to DELETE_CONTRIBUTION_USAGE;
445 hr_utility.set_location(' Leaving:'||l_proc, 190);
446 raise;
447 end delete_contribution_usage;
448 --
449 end pay_cnu_api;