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