[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_STAT_SITUATIONS_SWI
Source
1 Package Body pqh_fr_stat_situations_swi As
2 /* $Header: pqstsswi.pkb 120.0 2005/05/29 02:43 appldev noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_fr_stat_situations_swi.';
7 g_debug boolean := hr_utility.debug_enabled;
8
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------< create_statutory_situation >----------------------|
12 -- ----------------------------------------------------------------------------
13 PROCEDURE create_statutory_situation
14 (p_validate in number default hr_api.g_false_num
15 ,p_effective_date in date default null
16 ,p_business_group_id in number
17 ,p_situation_name in varchar2
18 ,p_type_of_ps in varchar2
19 ,p_situation_type in varchar2
20 ,p_sub_type in varchar2 default null
21 ,p_source in varchar2 default null
22 ,p_location in varchar2 default null
23 ,p_reason in varchar2 default null
24 ,p_is_default in varchar2 default null
25 ,p_date_from in date default null
26 ,p_date_to in date default null
27 ,p_request_type in varchar2 default null
28 ,p_employee_agreement_needed in varchar2 default null
29 ,p_manager_agreement_needed in varchar2 default null
30 ,p_print_arrette in varchar2 default null
31 ,p_reserve_position in varchar2 default null
32 ,p_allow_progressions in varchar2 default null
33 ,p_extend_probation_period in varchar2 default null
34 ,p_remuneration_paid in varchar2 default null
35 ,p_pay_share in number default null
36 ,p_pay_periods in number default null
37 ,p_frequency in varchar2 default null
38 ,p_first_period_max_duration in number default null
39 ,p_min_duration_per_request in number default null
40 ,p_max_duration_per_request in number default null
41 ,p_max_duration_whole_career in number default null
42 ,p_renewable_allowed in varchar2 default null
43 ,p_max_no_of_renewals in number default null
44 ,p_max_duration_per_renewal in number default null
45 ,p_max_tot_continuous_duration in number default null
46 ,p_remunerate_assign_status_id in number default null
47 ,p_statutory_situation_id out nocopy number
48 ,p_object_version_number out nocopy number
49 ,p_return_status out nocopy varchar2
50 ) is
51 --
52 -- Variables for API Boolean parameters
53 l_validate boolean;
54 --
55 -- Variables for IN/OUT parameters
56 --
57 -- Other variables
58 l_statutory_situation_id number;
59 l_proc varchar2(72) := g_package ||'create_statutory_situation';
60 Begin
61
62 g_debug := hr_utility.debug_enabled;
63
64 if g_debug then
65 --
66 hr_utility.set_location(' Entering:' || l_proc,10);
67 --
68 End if;
69 --
70 -- Issue a savepoint
71 --
72 savepoint create_statutory_situation_swi;
73 --
74 -- Initialise Multiple Message Detection
75 --
76 hr_multi_message.enable_message_list;
77 --
78 -- Remember IN OUT parameter IN values
79 --
80 --
81 -- Convert constant values to their corresponding boolean value
82 --
83 l_validate :=
84 hr_api.constant_to_boolean
85 (p_constant_value => p_validate);
86 --
87 -- Register Surrogate ID or user key values
88 --
89 pqh_sts_ins.set_base_key_value
90 (p_statutory_situation_id => p_statutory_situation_id
91 );
92 --
93 -- Call API
94 --
95 pqh_fr_stat_situations_api.create_statutory_situation
96 (p_validate => l_validate
97 ,p_effective_date => p_effective_date
98 ,p_business_group_id => p_business_group_id
99 ,p_situation_name => p_situation_name
100 ,p_type_of_ps => p_type_of_ps
101 ,p_situation_type => p_situation_type
102 ,p_sub_type => p_sub_type
103 ,p_source => p_source
104 ,p_location => p_location
105 ,p_reason => p_reason
106 ,p_is_default => p_is_default
107 ,p_date_from => p_date_from
108 ,p_date_to => p_date_to
109 ,p_request_type => p_request_type
110 ,p_employee_agreement_needed => p_employee_agreement_needed
111 ,p_manager_agreement_needed => p_manager_agreement_needed
112 ,p_print_arrette => p_print_arrette
113 ,p_reserve_position => p_reserve_position
114 ,p_allow_progressions => p_allow_progressions
115 ,p_extend_probation_period => p_extend_probation_period
116 ,p_remuneration_paid => p_remuneration_paid
117 ,p_pay_share => p_pay_share
118 ,p_pay_periods => p_pay_periods
119 ,p_frequency => p_frequency
120 ,p_first_period_max_duration => p_first_period_max_duration
121 ,p_min_duration_per_request => p_min_duration_per_request
122 ,p_max_duration_per_request => p_max_duration_per_request
123 ,p_max_duration_whole_career => p_max_duration_whole_career
124 ,p_renewable_allowed => p_renewable_allowed
125 ,p_max_no_of_renewals => p_max_no_of_renewals
126 ,p_max_duration_per_renewal => p_max_duration_per_renewal
127 ,p_max_tot_continuous_duration => p_max_tot_continuous_duration
128 ,p_statutory_situation_id => l_statutory_situation_id
129 ,p_object_version_number => p_object_version_number
130 ,p_remunerate_assign_status_id => p_remunerate_assign_status_id
131 );
132 --
133 -- Convert API warning boolean parameter values to specific
134 -- messages and add them to Multiple Message List
135 --
136 --
137 -- Convert API non-warning boolean parameter values
138 --
139 --
140 -- Derive the API return status value based on whether
141 -- messages of any type exist in the Multiple Message List.
142 -- Also disable Multiple Message Detection.
143 --
144 p_statutory_situation_id := l_statutory_situation_id;
145 p_return_status := hr_multi_message.get_return_status_disable;
146
147 if g_debug then
148 --
149 hr_utility.set_location(' Leaving:' || l_proc,20);
150 --
151 End if;
152
153 --
154 exception
155 when hr_multi_message.error_message_exist then
156 --
157 -- Catch the Multiple Message List exception which
158 -- indicates API processing has been aborted because
159 -- at least one message exists in the list.
160 --
161 rollback to create_statutory_situation_swi;
162 --
163 -- Reset IN OUT parameters and set OUT parameters
164 --
165 p_object_version_number := null;
166
167 p_return_status := hr_multi_message.get_return_status_disable;
168
169 if g_debug then
170 --
171 hr_utility.set_location(' Leaving:' || l_proc, 30);
172 --
173 End if;
174
175 when others then
176 --
177 -- When Multiple Message Detection is enabled catch
178 -- any Application specific or other unexpected
179 -- exceptions. Adding appropriate details to the
180 -- Multiple Message List. Otherwise re-raise the
181 -- error.
182 --
183 rollback to create_statutory_situation_swi;
184 if hr_multi_message.unexpected_error_add(l_proc) then
185 if g_debug then
186 --
187 hr_utility.set_location(' Leaving:' || l_proc,40);
188 --
189 End if;
190 raise;
191 end if;
192 --
193 -- Reset IN OUT and set OUT parameters
194 --
195 p_object_version_number := null;
196
197 p_return_status := hr_multi_message.get_return_status_disable;
198
199 if g_debug then
200 --
201 hr_utility.set_location(' Leaving:' || l_proc,50);
202 --
203 End if;
204
205 end create_statutory_situation;
206 -- ----------------------------------------------------------------------------
207 -- |----------------------< delete_statutory_situation >----------------------|
208 -- ----------------------------------------------------------------------------
209 PROCEDURE delete_statutory_situation
210 (p_validate in number default hr_api.g_false_num
211 ,p_statutory_situation_id in number
212 ,p_object_version_number in number
213 ,p_return_status out nocopy varchar2
214 ) is
215 --
216 -- Variables for API Boolean parameters
217 l_validate boolean;
218 --
219 -- Variables for IN/OUT parameters
220 --
221 -- Other variables
222 l_proc varchar2(72) := g_package ||'delete_statutory_situation';
223
224 --
225 Cursor csr_get_child_records IS
226 Select stat_situation_rule_id , object_version_number
227 from pqh_fr_stat_situation_rules
228 where statutory_situation_id = p_statutory_situation_id;
229
230 Begin
231
232 g_debug := hr_utility.debug_enabled;
233
234 if g_debug then
235 --
236 hr_utility.set_location(' Entering:' || l_proc,10);
237 --
238 End if;
239
240 --
241 -- Issue a savepoint
242 --
243 savepoint delete_statutory_situation_swi;
244 --
245 -- Initialise Multiple Message Detection
246 --
247 hr_multi_message.enable_message_list;
248 --
249 -- Remember IN OUT parameter IN values
250 --
251 --
252 -- Convert constant values to their corresponding boolean value
253 --
254 l_validate :=
255 hr_api.constant_to_boolean
256 (p_constant_value => p_validate);
257 --
258 -- Register Surrogate ID or user key values
259 --
260 --
261 --
262 --
263 -- Call API to delete Child Records
264 --
265 FOR l_rec in csr_get_child_records
266 Loop
267
268 pqh_fr_stat_sit_rules_api.delete_stat_situation_rule
269 (p_validate => l_validate
270 ,p_stat_situation_rule_id => l_rec.stat_situation_rule_id
271 ,p_object_version_number => l_rec.object_version_number
272 );
273
274 End loop;
275
276 --
277 -- Call API Delete Master
278 --
279 pqh_fr_stat_situations_api.delete_statutory_situation
280 (p_validate => l_validate
281 ,p_statutory_situation_id => p_statutory_situation_id
282 ,p_object_version_number => p_object_version_number
283 );
284 --
285 -- Convert API warning boolean parameter values to specific
286 -- messages and add them to Multiple Message List
287 --
288 --
289 -- Convert API non-warning boolean parameter values
290 --
291 --
292 -- Derive the API return status value based on whether
293 -- messages of any type exist in the Multiple Message List.
294 -- Also disable Multiple Message Detection.
295 --
296 p_return_status := hr_multi_message.get_return_status_disable;
297 if g_debug then
298 --
299 hr_utility.set_location(' Leaving:' || l_proc,20);
300 --
301 End if;
302 --
303 exception
304 when hr_multi_message.error_message_exist then
305 --
306 -- Catch the Multiple Message List exception which
307 -- indicates API processing has been aborted because
308 -- at least one message exists in the list.
309 --
310 rollback to delete_statutory_situation_swi;
311 --
312 -- Reset IN OUT parameters and set OUT parameters
313 --
314 p_return_status := hr_multi_message.get_return_status_disable;
315
316 if g_debug then
317 --
318 hr_utility.set_location(' Leaving:' || l_proc, 30);
319 --
320 End if;
321
322 when others then
323 --
324 -- When Multiple Message Detection is enabled catch
325 -- any Application specific or other unexpected
326 -- exceptions. Adding appropriate details to the
327 -- Multiple Message List. Otherwise re-raise the
328 -- error.
329 --
330 rollback to delete_statutory_situation_swi;
331 if hr_multi_message.unexpected_error_add(l_proc) then
332 if g_debug then
333 --
334 hr_utility.set_location(' Leaving:' || l_proc,40);
335 --
336 End if;
337
338 raise;
339 end if;
340 --
341 -- Reset IN OUT and set OUT parameters
342 --
343 p_return_status := hr_multi_message.get_return_status_disable;
344 if g_debug then
345 --
346 hr_utility.set_location(' Leaving:' || l_proc,50);
347 --
348 End if;
349
350 end delete_statutory_situation;
351 -- ----------------------------------------------------------------------------
352 -- |----------------------< update_statutory_situation >----------------------|
353 -- ----------------------------------------------------------------------------
354 PROCEDURE update_statutory_situation
355 (p_validate in number default hr_api.g_false_num
356 ,p_effective_date in date default hr_api.g_date
357 ,p_statutory_situation_id in number
358 ,p_object_version_number in out nocopy number
359 ,p_business_group_id in number default hr_api.g_number
360 ,p_situation_name in varchar2 default hr_api.g_varchar2
361 ,p_type_of_ps in varchar2 default hr_api.g_varchar2
362 ,p_situation_type in varchar2 default hr_api.g_varchar2
363 ,p_sub_type in varchar2 default hr_api.g_varchar2
364 ,p_source in varchar2 default hr_api.g_varchar2
365 ,p_location in varchar2 default hr_api.g_varchar2
366 ,p_reason in varchar2 default hr_api.g_varchar2
367 ,p_is_default in varchar2 default hr_api.g_varchar2
368 ,p_date_from in date default hr_api.g_date
369 ,p_date_to in date default hr_api.g_date
370 ,p_request_type in varchar2 default hr_api.g_varchar2
371 ,p_employee_agreement_needed in varchar2 default hr_api.g_varchar2
372 ,p_manager_agreement_needed in varchar2 default hr_api.g_varchar2
373 ,p_print_arrette in varchar2 default hr_api.g_varchar2
374 ,p_reserve_position in varchar2 default hr_api.g_varchar2
375 ,p_allow_progressions in varchar2 default hr_api.g_varchar2
376 ,p_extend_probation_period in varchar2 default hr_api.g_varchar2
377 ,p_remuneration_paid in varchar2 default hr_api.g_varchar2
378 ,p_pay_share in number default hr_api.g_number
379 ,p_pay_periods in number default hr_api.g_number
380 ,p_frequency in varchar2 default hr_api.g_varchar2
381 ,p_first_period_max_duration in number default hr_api.g_number
382 ,p_min_duration_per_request in number default hr_api.g_number
383 ,p_max_duration_per_request in number default hr_api.g_number
384 ,p_max_duration_whole_career in number default hr_api.g_number
385 ,p_renewable_allowed in varchar2 default hr_api.g_varchar2
386 ,p_max_no_of_renewals in number default hr_api.g_number
387 ,p_max_duration_per_renewal in number default hr_api.g_number
388 ,p_max_tot_continuous_duration in number default hr_api.g_number
389 ,p_remunerate_assign_status_id in number default hr_api.g_number
390 ,p_return_status out nocopy varchar2
391 ) is
392 --
393 -- Variables for API Boolean parameters
394 l_validate boolean;
395 --
396 -- Variables for IN/OUT parameters
397 l_object_version_number number;
398 --
399 -- Other variables
400 l_proc varchar2(72) := g_package ||'update_statutory_situation';
401 Begin
402
403 g_debug := hr_utility.debug_enabled;
404
405 if g_debug then
406 --
407 hr_utility.set_location(' Entering:' || l_proc,10);
408 --
409 End if;
410 --
411 -- Issue a savepoint
412 --
413 savepoint update_statutory_situation_swi;
414 --
415 -- Initialise Multiple Message Detection
416 --
417 hr_multi_message.enable_message_list;
418 --
419 -- Remember IN OUT parameter IN values
420 --
421 l_object_version_number := p_object_version_number;
422 --
423 -- Convert constant values to their corresponding boolean value
424 --
425 l_validate :=
426 hr_api.constant_to_boolean
427 (p_constant_value => p_validate);
428 --
429 -- Register Surrogate ID or user key values
430 --
431 --
432 -- Call API
433 --
434 pqh_fr_stat_situations_api.update_statutory_situation
435 (p_validate => l_validate
436 ,p_effective_date => p_effective_date
437 ,p_statutory_situation_id => p_statutory_situation_id
438 ,p_object_version_number => p_object_version_number
439 ,p_business_group_id => p_business_group_id
440 ,p_situation_name => p_situation_name
441 ,p_type_of_ps => p_type_of_ps
442 ,p_situation_type => p_situation_type
443 ,p_sub_type => p_sub_type
444 ,p_source => p_source
445 ,p_location => p_location
446 ,p_reason => p_reason
447 ,p_is_default => p_is_default
448 ,p_date_from => p_date_from
449 ,p_date_to => p_date_to
450 ,p_request_type => p_request_type
451 ,p_employee_agreement_needed => p_employee_agreement_needed
452 ,p_manager_agreement_needed => p_manager_agreement_needed
453 ,p_print_arrette => p_print_arrette
454 ,p_reserve_position => p_reserve_position
455 ,p_allow_progressions => p_allow_progressions
456 ,p_extend_probation_period => p_extend_probation_period
457 ,p_remuneration_paid => p_remuneration_paid
458 ,p_pay_share => p_pay_share
459 ,p_pay_periods => p_pay_periods
460 ,p_frequency => p_frequency
461 ,p_first_period_max_duration => p_first_period_max_duration
462 ,p_min_duration_per_request => p_min_duration_per_request
463 ,p_max_duration_per_request => p_max_duration_per_request
464 ,p_max_duration_whole_career => p_max_duration_whole_career
465 ,p_renewable_allowed => p_renewable_allowed
466 ,p_max_no_of_renewals => p_max_no_of_renewals
467 ,p_max_duration_per_renewal => p_max_duration_per_renewal
468 ,p_max_tot_continuous_duration => p_max_tot_continuous_duration
469 ,p_remunerate_assign_status_id => p_remunerate_assign_status_id
470 );
471 --
472 -- Convert API warning boolean parameter values to specific
473 -- messages and add them to Multiple Message List
474 --
475 --
476 -- Convert API non-warning boolean parameter values
477 --
478 --
479 -- Derive the API return status value based on whether
480 -- messages of any type exist in the Multiple Message List.
481 -- Also disable Multiple Message Detection.
482 --
483 p_return_status := hr_multi_message.get_return_status_disable;
484
485 if g_debug then
486 --
487 hr_utility.set_location(' Leaving:' || l_proc,20);
488 --
489 End if;
490
491 --
492 exception
493 when hr_multi_message.error_message_exist then
494 --
495 -- Catch the Multiple Message List exception which
496 -- indicates API processing has been aborted because
497 -- at least one message exists in the list.
498 --
499 rollback to update_statutory_situation_swi;
500 --
501 -- Reset IN OUT parameters and set OUT parameters
502 --
503 p_object_version_number := l_object_version_number;
504 p_return_status := hr_multi_message.get_return_status_disable;
505
506 if g_debug then
507 --
508 hr_utility.set_location(' Leaving:' || l_proc, 30);
509 --
510 End if;
511
512 when others then
513 --
514 -- When Multiple Message Detection is enabled catch
515 -- any Application specific or other unexpected
516 -- exceptions. Adding appropriate details to the
517 -- Multiple Message List. Otherwise re-raise the
518 -- error.
519 --
520 rollback to update_statutory_situation_swi;
521 if hr_multi_message.unexpected_error_add(l_proc) then
522 if g_debug then
523 --
524 hr_utility.set_location(' Leaving:' || l_proc,40);
525 --
526 End if;
527
528 raise;
529 end if;
530 --
531 -- Reset IN OUT and set OUT parameters
532 --
533 p_object_version_number := l_object_version_number;
534
535 p_return_status := hr_multi_message.get_return_status_disable;
536
537 if g_debug then
538 --
539 hr_utility.set_location(' Leaving:' || l_proc,50);
540 --
541 end if;
542
543 end update_statutory_situation;
544 end pqh_fr_stat_situations_swi;