[Home] [Help]
PACKAGE BODY: APPS.PAY_PL_SII_API
Source
1 Package Body pay_pl_sii_api as
2 /* $Header: pypsdapi.pkb 120.1 2005/12/08 05:09:19 ssekhar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'pay_pl_sii_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_pl_sii_details >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_pl_sii_details
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_contract_category in varchar2
16 ,p_per_or_asg_id in number
17 ,p_business_group_id in number
18 ,p_emp_social_security_info in varchar2
19 ,p_old_age_contribution in varchar2 default null
20 ,p_pension_contribution in varchar2 default null
21 ,p_sickness_contribution in varchar2 default null
22 ,p_work_injury_contribution in varchar2 default null
23 ,p_labor_contribution in varchar2 default null
24 ,p_health_contribution in varchar2 default null
25 ,p_unemployment_contribution in varchar2 default null
26 ,p_old_age_cont_end_reason in varchar2 default null
27 ,p_pension_cont_end_reason in varchar2 default null
28 ,p_sickness_cont_end_reason in varchar2 default null
29 ,p_work_injury_cont_end_reason in varchar2 default null
30 ,p_labor_fund_cont_end_reason in varchar2 default null
31 ,p_health_cont_end_reason in varchar2 default null
32 ,p_unemployment_cont_end_reason in varchar2 default null
33 ,p_sii_details_id out nocopy number
34 ,p_object_version_number out nocopy number
35 ,p_effective_start_date out nocopy date
36 ,p_effective_end_date out nocopy date
37 ,p_effective_date_warning out nocopy boolean
38 )
39 is
40 --
41 -- Declare cursors and local variables
42 --
43 l_effective_date date;
44 l_proc varchar2(72) := g_package||'create_pl_sii_details';
45 l_program_id number;
46 l_program_login_id number;
47 l_program_application_id number;
48 l_request_id number;
49 l_sii_details_id number;
50 l_object_version_number number;
51 l_effective_start_date date;
52 l_effective_end_date date;
53 l_reset_date date;
54
55 l_old_age_contribution pay_pl_sii_details_f.old_age_contribution%type;
56 l_pension_contribution pay_pl_sii_details_f.pension_contribution%type;
57 l_sickness_contribution pay_pl_sii_details_f.sickness_contribution%type;
58 l_work_injury_contribution pay_pl_sii_details_f.work_injury_contribution%type;
59 l_labor_contribution pay_pl_sii_details_f.labor_contribution%type;
60 l_health_contribution pay_pl_sii_details_f.health_contribution%type;
61 l_unemployment_contribution pay_pl_sii_details_f.unemployment_contribution%type;
62
63 l_emp per_person_types.system_person_type%TYPE;
64 l_emp_apl per_person_types.system_person_type%TYPE;
65 l_active_assign per_assignment_status_types.per_system_status%TYPE;
66 l_susp_assign per_assignment_status_types.per_system_status%TYPE;
67
68 cursor csr_per_date is
69 select min(papf.effective_start_date)
70 from per_all_people_f papf,
71 per_person_types ppt
72 where papf.person_type_id = ppt.person_type_id
73 and system_person_type in (l_emp,l_emp_apl)
74 and papf.person_id = p_per_or_asg_id
75 and papf.business_group_id = p_business_group_id;
76
77 cursor csr_asg_date is
78 select min(effective_start_date)
79 from per_all_assignments_f paaf,
80 hr_soft_coding_keyflex scl,
81 per_assignment_status_types past
82 where paaf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
83 and paaf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
84 and scl.segment3 = p_contract_category
85 and paaf.assignment_id = p_per_or_asg_id
86 and paaf.business_group_id = p_business_group_id
87 and past.per_system_status in (l_active_assign,l_susp_assign);
88
89 begin
90
91 hr_utility.set_location('Entering:'|| l_proc, 10);
92 --
93
94 l_emp := 'EMP';
95 l_emp_apl := 'EMP_APL';
96 l_active_assign := 'ACTIVE_ASSIGN';
97 l_susp_assign := 'SUSP_ASSIGN';
98
99 -- Issue a savepoint
100 --
101 savepoint create_pl_sii_details;
102 --
103
104 -- Reset the effective_start_date to the Effective Start date of Assignment/Person depending on
105 -- whether the Contract Category is 'CIVIL' or 'NORMAL'
106
107 if p_contract_category in ('CIVIL','LUMP','F_LUMP') then
108 open csr_asg_date;
109 fetch csr_asg_date into l_reset_date;
110 close csr_asg_date;
111 elsif p_contract_category = 'NORMAL' then
112 open csr_per_date;
113 fetch csr_per_date into l_reset_date;
114 close csr_per_date;
115 end if;
116
117 if p_effective_date > l_reset_date then
118 l_effective_date := trunc(l_reset_date);
119 p_effective_date_warning := TRUE;
120 else
121 l_effective_date := trunc(p_effective_date);
122 p_effective_date_warning := FALSE;
123 end if;
124
125
126 -- Validation prior to deriving the Contribution values
127 -- Here we validate the 'Employee Social Security Information' prior to deriving
128 -- the null Contribution values
129
130 pay_psd_bus.chk_emp_social_security_info(p_sii_details_id => l_sii_details_id
131 ,p_effective_date => l_effective_date
132 ,p_emp_social_security_info => p_emp_social_security_info
133 ,p_object_version_number => l_object_version_number);
134
135
136 -- Since the 'Employee Social Security Info' has been validated, we derive the various Contribution values
137
138 l_old_age_contribution := p_old_age_contribution;
139 l_pension_contribution := p_pension_contribution;
140 l_sickness_contribution := p_sickness_contribution;
141 l_work_injury_contribution := p_work_injury_contribution;
142 l_labor_contribution := p_labor_contribution;
143 l_health_contribution := p_health_contribution;
144 l_unemployment_contribution := p_unemployment_contribution;
145
146
147 pay_psd_bus.get_contribution_values(p_effective_date => l_effective_date
148 ,p_emp_social_security_info => p_emp_social_security_info
149 ,p_old_age_contribution => l_old_age_contribution
150 ,p_pension_contribution => l_pension_contribution
151 ,p_sickness_contribution => l_sickness_contribution
152 ,p_work_injury_contribution => l_work_injury_contribution
153 ,p_labor_contribution => l_labor_contribution
154 ,p_health_contribution => l_health_contribution
155 ,p_unemployment_contribution => l_unemployment_contribution);
156
157 --
158 -- Call Before Process User Hook
159 --
160 begin
161 PAY_PL_SII_BK1.create_pl_sii_details_b
162 (p_effective_date => l_effective_date
163 ,p_contract_category => p_contract_category
164 ,p_business_group_id => p_business_group_id
165 ,p_per_or_asg_id => p_per_or_asg_id
166 ,p_emp_social_security_info => p_emp_social_security_info
167 ,p_old_age_contribution => l_old_age_contribution
168 ,p_pension_contribution => l_pension_contribution
169 ,p_sickness_contribution => l_sickness_contribution
170 ,p_work_injury_contribution => l_work_injury_contribution
171 ,p_labor_contribution => l_labor_contribution
172 ,p_health_contribution => l_health_contribution
173 ,p_unemployment_contribution => l_unemployment_contribution
174 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
175 ,p_pension_cont_end_reason => p_pension_cont_end_reason
176 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
177 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
178 ,p_labor_fund_cont_end_reason => p_health_cont_end_reason
179 ,p_health_cont_end_reason => p_health_cont_end_reason
180 ,p_unemployment_cont_end_reason => p_unemployment_cont_end_reason
181 ,p_effective_date_warning => p_effective_date_warning
182 );
183 exception
184 when hr_api.cannot_find_prog_unit then
185 hr_api.cannot_find_prog_unit_error
186 (p_module_name => 'create_pl_sii_details'
187 ,p_hook_type => 'BP'
188 );
189 end;
190 --
191 --
192 -- Process Logic
193 --
194 pay_psd_ins.ins
195 (p_effective_date => l_effective_date
196 ,p_per_or_asg_id => p_per_or_asg_id
197 ,p_business_group_id => p_business_group_id
198 ,p_contract_category => p_contract_category
199 ,p_emp_social_security_info => p_emp_social_security_info
200 ,p_old_age_contribution => l_old_age_contribution
201 ,p_pension_contribution => l_pension_contribution
202 ,p_sickness_contribution => l_sickness_contribution
203 ,p_work_injury_contribution => l_work_injury_contribution
204 ,p_labor_contribution => l_labor_contribution
205 ,p_health_contribution => l_health_contribution
206 ,p_unemployment_contribution => l_unemployment_contribution
207 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
208 ,p_pension_cont_end_reason => p_pension_cont_end_reason
209 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
210 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
211 ,p_labor_fund_cont_end_reason => p_labor_fund_cont_end_reason
212 ,p_health_cont_end_reason => p_health_cont_end_reason
213 ,p_unemployment_cont_end_reason => p_health_cont_end_reason
214 ,p_program_id => l_program_id
215 ,p_program_login_id => l_program_login_id
216 ,p_program_application_id => l_program_application_id
217 ,p_request_id => l_request_id
218 ,p_sii_details_id => l_sii_details_id
219 ,p_object_version_number => l_object_version_number
220 ,p_effective_start_date => l_effective_start_date
221 ,p_effective_end_date => l_effective_end_date);
222
223
224
225 --
226 -- Call After Process User Hook
227 --
228 begin
229 pay_pl_sii_bk1.create_pl_sii_details_a
230 (p_effective_date => l_effective_date
231 ,p_per_or_asg_id => p_per_or_asg_id
232 ,p_business_group_id => p_business_group_id
233 ,p_contract_category => p_contract_category
234 ,p_emp_social_security_info => p_emp_social_security_info
235 ,p_old_age_contribution => l_old_age_contribution
236 ,p_pension_contribution => l_pension_contribution
237 ,p_sickness_contribution => l_sickness_contribution
238 ,p_work_injury_contribution => l_work_injury_contribution
239 ,p_labor_contribution => l_labor_contribution
240 ,p_health_contribution => l_health_contribution
241 ,p_unemployment_contribution => l_unemployment_contribution
242 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
243 ,p_pension_cont_end_reason => p_pension_cont_end_reason
244 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
245 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
246 ,p_labor_fund_cont_end_reason => p_labor_fund_cont_end_reason
247 ,p_health_cont_end_reason => p_health_cont_end_reason
248 ,p_unemployment_cont_end_reason => p_health_cont_end_reason
249 ,p_sii_details_id => l_sii_details_id
250 ,p_object_version_number => l_object_version_number
251 ,p_effective_start_date => l_effective_start_date
252 ,p_effective_end_date => l_effective_end_date
253 ,p_effective_date_warning => p_effective_date_warning);
254
255 exception
256 when hr_api.cannot_find_prog_unit then
257 hr_api.cannot_find_prog_unit_error
258 (p_module_name => 'create_pl_sii_details'
259 ,p_hook_type => 'AP'
260 );
261 end;
262 --
263 -- When in validation only mode raise the Validate_Enabled exception
264 --
265 if p_validate then
266 raise hr_api.validate_enabled;
267 end if;
268 --
269 -- Set all IN OUT and OUT parameters with out values
270 --
271 p_sii_details_id := l_sii_details_id;
272 p_object_version_number := l_object_version_number;
273 p_effective_start_date := l_effective_start_date;
274 p_effective_end_date := l_effective_end_date;
275
279 when hr_api.validate_enabled then
276 --
277 hr_utility.set_location(' Leaving:'||l_proc, 70);
278 exception
280 --
281 -- As the Validate_Enabled exception has been raised
282 -- we must rollback to the savepoint
283 --
284 rollback to create_pl_sii_details;
285 --
286 -- Reset IN OUT parameters and set OUT parameters
287 -- (Any key or derived arguments must be set to null
288 -- when validation only mode is being used.)
289 --
290 p_sii_details_id := NULL;
291 p_object_version_number := NULL;
292 p_effective_start_date := NULL;
293 p_effective_end_date := NULL;
294 p_effective_date_warning := NULL;
295
296 hr_utility.set_location(' Leaving:'||l_proc, 80);
297 when others then
298 --
299 -- A validation or unexpected error has occured
300 --
301 rollback to create_pl_sii_details;
302 --
303 -- Reset IN OUT parameters and set all
304 -- OUT parameters, including warnings, to null
305 --
306 p_sii_details_id := NULL;
307 p_object_version_number := NULL;
308 p_effective_start_date := NULL;
309 p_effective_end_date := NULL;
310 p_effective_date_warning := NULL;
311 hr_utility.set_location(' Leaving:'||l_proc, 90);
312 raise;
313 end create_pl_sii_details;
314 --
315 --
316 -- ----------------------------------------------------------------------------
317 -- |----------------------< create_pl_civil_sii_details >----------------------|
318 -- ----------------------------------------------------------------------------
319 procedure create_pl_civil_sii_details
320 (p_validate in boolean default false
321 ,p_effective_date in date
322 ,p_assignment_id in number
323 ,p_emp_social_security_info in varchar2
324 ,p_old_age_contribution in varchar2 default null
325 ,p_pension_contribution in varchar2 default null
326 ,p_sickness_contribution in varchar2 default null
327 ,p_work_injury_contribution in varchar2 default null
328 ,p_labor_contribution in varchar2 default null
329 ,p_health_contribution in varchar2 default null
330 ,p_unemployment_contribution in varchar2 default null
331 ,p_sii_details_id out nocopy number
332 ,p_object_version_number out nocopy number
333 ,p_effective_start_date out nocopy date
334 ,p_effective_end_date out nocopy date
335 ,p_effective_date_warning out nocopy boolean)
336 is
337 --
338 -- Declare cursors and local variables
339 --
340
341 l_business_group_id per_business_groups.business_group_id%TYPE;
342 l_legislation_code per_business_groups.legislation_code%TYPE;
343 l_contract_category pay_pl_sii_details_f.contract_category%TYPE;
344
345 l_effective_date date;
346 l_effective_start_date date;
347 l_effective_end_date date;
348 l_object_version_number number;
349 l_sii_details_id number;
350 l_proc varchar2(72) := g_package||'create_pl_civil_sii_details';
351 l_reset_date date;
352
353 cursor csr_get_derived_details is
354 select bus.business_group_id
355 , bus.legislation_code
356 from per_all_assignments_f paf
357 , per_business_groups_perf bus
358 where paf.person_id = p_assignment_id
359 and l_effective_date between paf.effective_start_date
360 and paf.effective_end_date
361 and bus.business_group_id = paf.business_group_id;
362
363 begin
364 hr_utility.set_location('Entering:'|| l_proc, 10);
365
366 l_effective_date := trunc(p_effective_date);
367 l_contract_category := 'CIVIL';
368
369 open csr_get_derived_details;
370 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
371 --
372 if csr_get_derived_details%NOTFOUND then
373 --
374 close csr_get_derived_details;
375 --
376 hr_utility.set_message(801,'PAY_375857_PL_INVALID_ASG');
377 hr_utility.set_message_token('ENTITY',hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT'));
378 hr_utility.raise_error;
379 end if;
380 --
381 close csr_get_derived_details;
382
383 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
384 -- we first validate the assignment id before deriving the effective_start_date.
385
386 pay_psd_bus.chk_per_asg_id(p_effective_date => l_effective_date
387 ,p_per_or_asg_id => p_assignment_id
388 ,p_contract_category => l_contract_category
389 ,p_business_group_id => l_business_group_id
390 ,p_object_version_number => l_object_version_number);
391
392 -- Calling the Create SII API
393
394 pay_pl_sii_api.create_pl_sii_details
395 (p_validate => p_validate
396 ,p_effective_date => l_effective_date
397 ,p_contract_category => l_contract_category
398 ,p_per_or_asg_id => p_assignment_id
399 ,p_business_group_id => l_business_group_id
400 ,p_emp_social_security_info => p_emp_social_security_info
401 ,p_old_age_contribution => p_old_age_contribution
402 ,p_pension_contribution => p_pension_contribution
403 ,p_sickness_contribution => p_sickness_contribution
404 ,p_work_injury_contribution => p_work_injury_contribution
405 ,p_labor_contribution => p_labor_contribution
406 ,p_health_contribution => p_health_contribution
410 ,p_effective_start_date => l_effective_start_date
407 ,p_unemployment_contribution => p_unemployment_contribution
408 ,p_sii_details_id => l_sii_details_id
409 ,p_object_version_number => l_object_version_number
411 ,p_effective_end_date => l_effective_end_date
412 ,p_effective_date_warning => p_effective_date_warning
413 );
414
415 end create_pl_civil_sii_details;
416 --
417 -- ----------------------------------------------------------------------------
418 -- |----------------------< create_pl_lump_sii_details >----------------------|
419 -- ----------------------------------------------------------------------------
420 procedure create_pl_lump_sii_details
421 (p_validate in boolean default false
422 ,p_effective_date in date
423 ,p_assignment_id in number
424 ,p_emp_social_security_info in varchar2
425 ,p_old_age_contribution in varchar2 default null
426 ,p_pension_contribution in varchar2 default null
427 ,p_sickness_contribution in varchar2 default null
428 ,p_work_injury_contribution in varchar2 default null
429 ,p_labor_contribution in varchar2 default null
430 ,p_health_contribution in varchar2 default null
431 ,p_unemployment_contribution in varchar2 default null
432 ,p_sii_details_id out nocopy number
433 ,p_object_version_number out nocopy number
434 ,p_effective_start_date out nocopy date
435 ,p_effective_end_date out nocopy date
436 ,p_effective_date_warning out nocopy boolean)
437 is
438 --
439 -- Declare cursors and local variables
440 --
441
442 l_business_group_id per_business_groups.business_group_id%TYPE;
443 l_legislation_code per_business_groups.legislation_code%TYPE;
444 l_contract_category pay_pl_sii_details_f.contract_category%TYPE;
445
446 l_effective_date date;
447 l_effective_start_date date;
448 l_effective_end_date date;
449 l_object_version_number number;
450 l_sii_details_id number;
451 l_proc varchar2(72) := g_package||'create_pl_lump_sii_details';
452 l_reset_date date;
453
454 cursor csr_get_derived_details is
455 select bus.business_group_id
456 , bus.legislation_code
457 from per_all_assignments_f paf
458 , per_business_groups_perf bus
459 where paf.person_id = p_assignment_id
460 and l_effective_date between paf.effective_start_date
461 and paf.effective_end_date
462 and bus.business_group_id = paf.business_group_id;
463
464 begin
465 hr_utility.set_location('Entering:'|| l_proc, 10);
466
467 l_effective_date := trunc(p_effective_date);
468 l_contract_category := 'LUMP';
469
470 open csr_get_derived_details;
471 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
472 --
473 if csr_get_derived_details%NOTFOUND then
474 --
475 close csr_get_derived_details;
476 --
477 hr_utility.set_message(801,'PAY_375857_PL_INVALID_ASG');
478 hr_utility.set_message_token('ENTITY',hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT'));
479 hr_utility.raise_error;
480 end if;
481 --
482 close csr_get_derived_details;
483
484 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
485 -- we first validate the assignment id before deriving the effective_start_date.
486
487 pay_psd_bus.chk_per_asg_id(p_effective_date => l_effective_date
488 ,p_per_or_asg_id => p_assignment_id
489 ,p_contract_category => l_contract_category
490 ,p_business_group_id => l_business_group_id
491 ,p_object_version_number => l_object_version_number);
492
493 -- Calling the Create SII API
494
495 pay_pl_sii_api.create_pl_sii_details
496 (p_validate => p_validate
497 ,p_effective_date => l_effective_date
498 ,p_contract_category => l_contract_category
499 ,p_per_or_asg_id => p_assignment_id
500 ,p_business_group_id => l_business_group_id
501 ,p_emp_social_security_info => p_emp_social_security_info
502 ,p_old_age_contribution => p_old_age_contribution
503 ,p_pension_contribution => p_pension_contribution
504 ,p_sickness_contribution => p_sickness_contribution
505 ,p_work_injury_contribution => p_work_injury_contribution
506 ,p_labor_contribution => p_labor_contribution
507 ,p_health_contribution => p_health_contribution
508 ,p_unemployment_contribution => p_unemployment_contribution
509 ,p_sii_details_id => l_sii_details_id
510 ,p_object_version_number => l_object_version_number
511 ,p_effective_start_date => l_effective_start_date
512 ,p_effective_end_date => l_effective_end_date
513 ,p_effective_date_warning => p_effective_date_warning
514 );
515
516 end create_pl_lump_sii_details;
517 --
518 -- ----------------------------------------------------------------------------
519 -- |----------------------< create_pl_f_lump_sii_details >---------------------|
520 -- ----------------------------------------------------------------------------
521 procedure create_pl_f_lump_sii_details
522 (p_validate in boolean default false
523 ,p_effective_date in date
524 ,p_assignment_id in number
525 ,p_emp_social_security_info in varchar2
529 ,p_work_injury_contribution in varchar2 default null
526 ,p_old_age_contribution in varchar2 default null
527 ,p_pension_contribution in varchar2 default null
528 ,p_sickness_contribution in varchar2 default null
530 ,p_labor_contribution in varchar2 default null
531 ,p_health_contribution in varchar2 default null
532 ,p_unemployment_contribution in varchar2 default null
533 ,p_sii_details_id out nocopy number
534 ,p_object_version_number out nocopy number
535 ,p_effective_start_date out nocopy date
536 ,p_effective_end_date out nocopy date
537 ,p_effective_date_warning out nocopy boolean)
538 is
539 --
540 -- Declare cursors and local variables
541 --
542
543 l_business_group_id per_business_groups.business_group_id%TYPE;
544 l_legislation_code per_business_groups.legislation_code%TYPE;
545 l_contract_category pay_pl_sii_details_f.contract_category%TYPE;
546
547 l_effective_date date;
548 l_effective_start_date date;
549 l_effective_end_date date;
550 l_object_version_number number;
551 l_sii_details_id number;
552 l_proc varchar2(72) := g_package||'create_pl_f_lump_sii_details';
553 l_reset_date date;
554
555 cursor csr_get_derived_details is
556 select bus.business_group_id
557 , bus.legislation_code
558 from per_all_assignments_f paf
559 , per_business_groups_perf bus
560 where paf.person_id = p_assignment_id
561 and l_effective_date between paf.effective_start_date
562 and paf.effective_end_date
563 and bus.business_group_id = paf.business_group_id;
564
565 begin
566 hr_utility.set_location('Entering:'|| l_proc, 10);
567
568 l_effective_date := trunc(p_effective_date);
569 l_contract_category := 'F_LUMP';
570
571 open csr_get_derived_details;
572 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
573 --
574 if csr_get_derived_details%NOTFOUND then
575 --
576 close csr_get_derived_details;
577 --
578 hr_utility.set_message(801,'PAY_375857_PL_INVALID_ASG');
579 hr_utility.set_message_token('ENTITY',hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT'));
580 hr_utility.raise_error;
581 end if;
582 --
583 close csr_get_derived_details;
584
585 -- Since we will be re-setting the effective_start_date to the Assignment's start date,
586 -- we first validate the assignment id before deriving the effective_start_date.
587
588 pay_psd_bus.chk_per_asg_id(p_effective_date => l_effective_date
589 ,p_per_or_asg_id => p_assignment_id
590 ,p_contract_category => l_contract_category
591 ,p_business_group_id => l_business_group_id
592 ,p_object_version_number => l_object_version_number);
593
594 -- Calling the Create SII API
595
596 pay_pl_sii_api.create_pl_sii_details
597 (p_validate => p_validate
598 ,p_effective_date => l_effective_date
599 ,p_contract_category => l_contract_category
600 ,p_per_or_asg_id => p_assignment_id
601 ,p_business_group_id => l_business_group_id
602 ,p_emp_social_security_info => p_emp_social_security_info
603 ,p_old_age_contribution => p_old_age_contribution
604 ,p_pension_contribution => p_pension_contribution
605 ,p_sickness_contribution => p_sickness_contribution
606 ,p_work_injury_contribution => p_work_injury_contribution
607 ,p_labor_contribution => p_labor_contribution
608 ,p_health_contribution => p_health_contribution
609 ,p_unemployment_contribution => p_unemployment_contribution
610 ,p_sii_details_id => l_sii_details_id
611 ,p_object_version_number => l_object_version_number
612 ,p_effective_start_date => l_effective_start_date
613 ,p_effective_end_date => l_effective_end_date
614 ,p_effective_date_warning => p_effective_date_warning
615 );
616
617 end create_pl_f_lump_sii_details;
618 --
619
620 -- ----------------------------------------------------------------------------
621 -- |----------------------< create_pl_normal_sii_details >---------------------|
622 -- ----------------------------------------------------------------------------
623
624 procedure create_pl_normal_sii_details
625 (p_validate in boolean default false
626 ,p_effective_date in date
627 ,p_person_id in number
628 ,p_emp_social_security_info in varchar2
629 ,p_old_age_contribution in varchar2 default null
630 ,p_pension_contribution in varchar2 default null
631 ,p_sickness_contribution in varchar2 default null
632 ,p_work_injury_contribution in varchar2 default null
633 ,p_labor_contribution in varchar2 default null
634 ,p_health_contribution in varchar2 default null
635 ,p_unemployment_contribution in varchar2 default null
636 ,p_sii_details_id out nocopy number
637 ,p_object_version_number out nocopy number
638 ,p_effective_start_date out nocopy date
639 ,p_effective_end_date out nocopy date
640 ,p_effective_date_warning out nocopy boolean)
641 is
642 --
643 -- Declare cursors and local variables
644 --
645
646 l_business_group_id per_business_groups.business_group_id%TYPE;
647 l_legislation_code per_business_groups.legislation_code%TYPE;
651 l_effective_end_date date;
648
649 l_effective_date date;
650 l_effective_start_date date;
652 l_object_version_number number;
653 l_sii_details_id number;
654 l_proc varchar2(72) := g_package||'create_pl_civil_sii_details';
655 l_contract_category pay_pl_sii_details_f.contract_category%TYPE;
656
657 cursor csr_get_derived_details is
658 select bus.business_group_id
659 , bus.legislation_code
660 from per_all_people_f papf
661 , per_business_groups_perf bus
662 where papf.person_id = p_person_id
663 and l_effective_date between papf.effective_start_date
664 and papf.effective_end_date
665 and bus.business_group_id = papf.business_group_id;
666
667 begin
668 hr_utility.set_location('Entering:'|| l_proc, 10);
669
670 l_effective_date := trunc(p_effective_date);
671 l_contract_category := 'NORMAL';
672
673 open csr_get_derived_details;
674 fetch csr_get_derived_details into l_business_group_id,l_legislation_code;
675 --
676 if csr_get_derived_details%NOTFOUND then
677 --
678 close csr_get_derived_details;
679 --
680 hr_utility.set_message(801,'PAY_375857_PL_INVALID_ASG');
681 hr_utility.set_message_token('ENTITY',hr_general.decode_lookup('PL_FORM_LABELS','PERSON'));
682 hr_utility.raise_error;
683 end if;
684 --
685 close csr_get_derived_details;
686
687 -- Since we will be re-setting the effective_start_date to the Person's start date,
688 -- we first validate the person id before deriving the effective_start_date.
689
690 pay_psd_bus.chk_per_asg_id(p_effective_date => l_effective_date
691 ,p_per_or_asg_id => p_person_id
692 ,p_contract_category => l_contract_category
693 ,p_business_group_id => l_business_group_id
694 ,p_object_version_number => l_object_version_number);
695
696 -- Calling the Create SII API
697
698 pay_pl_sii_api.create_pl_sii_details
699 (p_validate => p_validate
700 ,p_effective_date => l_effective_date
701 ,p_contract_category => l_contract_category
702 ,p_per_or_asg_id => p_person_id
703 ,p_business_group_id => l_business_group_id
704 ,p_emp_social_security_info => p_emp_social_security_info
705 ,p_old_age_contribution => p_old_age_contribution
706 ,p_pension_contribution => p_pension_contribution
707 ,p_sickness_contribution => p_sickness_contribution
708 ,p_work_injury_contribution => p_work_injury_contribution
709 ,p_labor_contribution => p_labor_contribution
710 ,p_health_contribution => p_health_contribution
711 ,p_unemployment_contribution => p_unemployment_contribution
712 ,p_sii_details_id => l_sii_details_id
713 ,p_object_version_number => l_object_version_number
714 ,p_effective_start_date => l_effective_start_date
715 ,p_effective_end_date => l_effective_end_date
716 ,p_effective_date_warning => p_effective_date_warning);
717
718 end create_pl_normal_sii_details;
719
720
721
722 --
723 --
724 -- ----------------------------------------------------------------------------
725 -- |-------------------------< update_pl_sii_details >-------------------------|
726 -- ----------------------------------------------------------------------------
727 procedure update_pl_sii_details
728 (p_validate in boolean default false
729 ,p_effective_date in date
730 ,p_datetrack_update_mode in varchar2
731 ,p_sii_details_id in number
732 ,p_object_version_number in out nocopy number
733 ,p_emp_social_security_info in varchar2 default hr_api.g_varchar2
734 ,p_old_age_contribution in varchar2 default hr_api.g_varchar2
735 ,p_pension_contribution in varchar2 default hr_api.g_varchar2
736 ,p_sickness_contribution in varchar2 default hr_api.g_varchar2
737 ,p_work_injury_contribution in varchar2 default hr_api.g_varchar2
738 ,p_labor_contribution in varchar2 default hr_api.g_varchar2
739 ,p_health_contribution in varchar2 default hr_api.g_varchar2
740 ,p_unemployment_contribution in varchar2 default hr_api.g_varchar2
741 ,p_old_age_cont_end_reason in varchar2 default hr_api.g_varchar2
742 ,p_pension_cont_end_reason in varchar2 default hr_api.g_varchar2
743 ,p_sickness_cont_end_reason in varchar2 default hr_api.g_varchar2
744 ,p_work_injury_cont_end_reason in varchar2 default hr_api.g_varchar2
745 ,p_labor_fund_cont_end_reason in varchar2 default hr_api.g_varchar2
746 ,p_health_cont_end_reason in varchar2 default hr_api.g_varchar2
747 ,p_unemployment_cont_end_reason in varchar2 default hr_api.g_varchar2
748 ,p_effective_start_date out nocopy date
749 ,p_effective_end_date out nocopy date
750 )
751 is
752 --
753 -- Declare cursors and local variables
754 --
755 l_effective_date date;
756 l_proc varchar2(72) := g_package||'update_pl_sii_details';
757 l_program_id number;
758 l_program_login_id number;
759 l_program_application_id number;
760 l_request_id number;
761 l_effective_start_date date;
762 l_effective_end_date date;
763 l_object_version_number number;
764 l_in_out_parameter1 number;
765 l_norm_term pay_pl_sii_details_f.contract_category%TYPE;
766 l_norm_active pay_pl_sii_details_f.contract_category%TYPE;
770 l_assg_type2 per_assignment_status_types.per_system_status%TYPE;
767 l_exists pay_pl_sii_details_f.per_or_asg_id%TYPE;
768
769 l_assg_type1 per_assignment_status_types.per_system_status%TYPE;
771 l_flag varchar2(1);
772
773 cursor csr_term_catg(p_contract_catg char) is
774 select per_or_asg_id from pay_pl_sii_details_f
775 where sii_details_id = p_sii_details_id and
776 p_effective_date between effective_start_date and effective_end_date and
777 contract_category = p_contract_catg;
778
779 cursor csr_contract_type is
780 select soft1.segment4 contract_type1, soft2.segment4 contract_type2
781 from hr_soft_coding_keyflex soft1,
782 per_all_assignments_f paf1,
783 per_all_people_f pap,
784 per_assignment_status_types pst,
785 hr_soft_coding_keyflex soft2,
786 per_all_assignments_f paf2
787 where pap.person_id = (select per_or_asg_id from pay_pl_sii_details_f
788 where sii_details_id = p_sii_details_id and
789 p_effective_date between effective_start_date
790 and effective_end_date)
791 and pap.person_id = paf1.person_id
792 and p_effective_date between pap.effective_start_date and pap.effective_end_date
793 and p_effective_date between paf1.effective_start_date and paf1.effective_end_date
794 and paf1.soft_coding_keyflex_id = soft1.soft_coding_keyflex_id
795 and soft1.segment3 = l_norm_active
796 and paf1.assignment_status_type_id = pst.assignment_status_type_id
797 and pst.per_system_status in (l_assg_type1,l_assg_type2)
798 and pap.person_id = paf2.person_id
799 and (p_effective_date-1) between paf2.effective_start_date and paf2.effective_end_date
800 and paf2.soft_coding_keyflex_id = soft2.soft_coding_keyflex_id
801 and soft2.segment3 = l_norm_active
802 and paf2.assignment_status_type_id = pst.assignment_status_type_id
803 -- This join ensures that the Contract types are for the same assignment
804 and paf2.assignment_id = paf1.assignment_id;
805
806 begin
807 hr_utility.set_location('Entering:'|| l_proc, 10);
808
809 l_norm_term := 'TERM_NORMAL';
810 l_norm_active := 'NORMAL';
811 l_assg_type1 := 'ACTIVE_ASSIGN';
812 l_assg_type2 := 'SUSP_ASSIGN';
813 l_flag := 'Y';
814 --
815 -- Issue a savepoint
816 --
817 savepoint update_pl_sii_details;
818 --
819 -- Remember IN OUT parameter IN values
820 --
821 l_in_out_parameter1 := p_object_version_number;
822 --
823 --
824 l_object_version_number := p_object_version_number;
825
826 --
827 -- Truncate the time portion from all IN date parameters
828 --
829 l_effective_date := trunc(p_effective_date);
830
831 open csr_term_catg(l_norm_term);
832 fetch csr_term_catg into l_exists;
833 if csr_term_catg%FOUND and p_datetrack_update_mode <> 'CORRECTION' then
834 -- Raise an error message as the record can only be Corrected for a Normal Terminated
835 -- Assignment
836 hr_utility.set_message(801,'PAY_375859_INVALID_TERM_MODE');
837 hr_utility.raise_error;
838 end if;
839 close csr_term_catg;
840
841 open csr_term_catg(l_norm_active);
842 fetch csr_term_catg into l_exists;
843 if csr_term_catg%FOUND and p_datetrack_update_mode <> 'CORRECTION' then
844 --
845 close csr_term_catg;
846 -- If the Normal Active Record is not being Corrected, then we need to check if on the date
847 -- the record is being changed, we need to ensure that the Contract type for the corresponding
848 -- assignment has changed
849 FOR r_contract_type in csr_contract_type
850 LOOP
851 if r_contract_type.contract_type1 <> r_contract_type.contract_type2 then
852 l_flag := 'N';
853 exit;
854 end if;
855 END LOOP;
856
857 if l_flag = 'Y' then
858 -- If this flag is Yes then an UPDATE is being done on a date when the Contract type has not
859 -- changed. So we raise an error message
860 hr_utility.set_message(801,'PAY_375860_INVALID_NORMAL_MODE');
861 hr_utility.raise_error;
862 end if;
863
864 end if;
865
866 --
867 -- Call Before Process User Hook
868 --
869 begin
870 PAY_PL_SII_BK2.update_pl_sii_details_b
871 (p_effective_date => p_effective_date
872 ,p_sii_details_id => p_sii_details_id
873 ,p_datetrack_update_mode => p_datetrack_update_mode
874 ,p_emp_social_security_info => p_emp_social_security_info
875 ,p_old_age_contribution => p_old_age_contribution
876 ,p_pension_contribution => p_pension_contribution
877 ,p_sickness_contribution => p_sickness_contribution
878 ,p_work_injury_contribution => p_work_injury_contribution
879 ,p_labor_contribution => p_labor_contribution
880 ,p_health_contribution => p_health_contribution
881 ,p_unemployment_contribution => p_unemployment_contribution
882 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
883 ,p_pension_cont_end_reason => p_pension_cont_end_reason
884 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
885 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
886 ,p_labor_fund_cont_end_reason => p_labor_fund_cont_end_reason
887 ,p_health_cont_end_reason => p_health_cont_end_reason
888 ,p_unemployment_cont_end_reason => p_unemployment_cont_end_reason
889 ,p_object_version_number => l_object_version_number
890 );
891 exception
895 ,p_hook_type => 'BP'
892 when hr_api.cannot_find_prog_unit then
893 hr_api.cannot_find_prog_unit_error
894 (p_module_name => 'update_pl_sii_details'
896 );
897 end;
898
899 --
900 -- Process Logic
901 --
902
903 pay_psd_upd.upd
904 (p_effective_date => p_effective_date
905 ,p_datetrack_mode => p_datetrack_update_mode
906 ,p_sii_details_id => p_sii_details_id
907 ,p_object_version_number => l_object_version_number
908 ,p_emp_social_security_info => p_emp_social_security_info
909 ,p_old_age_contribution => p_old_age_contribution
910 ,p_pension_contribution => p_pension_contribution
911 ,p_sickness_contribution => p_sickness_contribution
912 ,p_work_injury_contribution => p_work_injury_contribution
913 ,p_labor_contribution => p_labor_contribution
914 ,p_health_contribution => p_health_contribution
915 ,p_unemployment_contribution => p_unemployment_contribution
916 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
917 ,p_pension_cont_end_reason => p_pension_cont_end_reason
918 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
919 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
920 ,p_labor_fund_cont_end_reason => p_labor_fund_cont_end_reason
921 ,p_health_cont_end_reason => p_health_cont_end_reason
922 ,p_unemployment_cont_end_reason => p_unemployment_cont_end_reason
923 ,p_program_id => l_program_id
924 ,p_program_login_id => l_program_login_id
925 ,p_program_application_id => l_program_application_id
926 ,p_request_id => l_request_id
927 ,p_effective_start_date => l_effective_start_date
928 ,p_effective_end_date => l_effective_end_date
929 );
930 --
931 -- Call After Process User Hook
932 --
933 begin
934 pay_pl_sii_bk2.update_pl_sii_details_a
935 (p_effective_date => p_effective_date
936 ,p_sii_details_id => p_sii_details_id
937 ,p_datetrack_update_mode => p_datetrack_update_mode
938 ,p_emp_social_security_info => p_emp_social_security_info
939 ,p_old_age_contribution => p_old_age_contribution
940 ,p_pension_contribution => p_pension_contribution
941 ,p_sickness_contribution => p_sickness_contribution
942 ,p_work_injury_contribution => p_work_injury_contribution
943 ,p_labor_contribution => p_labor_contribution
944 ,p_health_contribution => p_health_contribution
945 ,p_unemployment_contribution => p_unemployment_contribution
946 ,p_old_age_cont_end_reason => p_old_age_cont_end_reason
947 ,p_pension_cont_end_reason => p_pension_cont_end_reason
948 ,p_sickness_cont_end_reason => p_sickness_cont_end_reason
949 ,p_work_injury_cont_end_reason => p_work_injury_cont_end_reason
950 ,p_labor_fund_cont_end_reason => p_labor_fund_cont_end_reason
951 ,p_health_cont_end_reason => p_health_cont_end_reason
952 ,p_unemployment_cont_end_reason => p_unemployment_cont_end_reason
953 ,p_object_version_number => l_object_version_number
954 ,p_effective_start_date => l_effective_start_date
955 ,p_effective_end_date => l_effective_end_date
956 );
957
958 exception
959 when hr_api.cannot_find_prog_unit then
960 hr_api.cannot_find_prog_unit_error
961 (p_module_name => 'update_pl_sii_details'
962 ,p_hook_type => 'AP'
963 );
964 end;
965 --
966 -- When in validation only mode raise the Validate_Enabled exception
967 --
968 if p_validate then
969 raise hr_api.validate_enabled;
970 end if;
971 --
972 -- Set all IN OUT and OUT parameters with out values
973 --
974 p_object_version_number := l_object_version_number;
975 p_effective_start_date := l_effective_start_date;
976 p_effective_end_date := l_effective_end_date;
977
978 --
979 hr_utility.set_location(' Leaving:'||l_proc, 70);
980 exception
981 when hr_api.validate_enabled then
982 --
983 -- As the Validate_Enabled exception has been raised
984 -- we must rollback to the savepoint
985 --
986 rollback to update_pl_sii_details;
987 --
988 -- Reset IN OUT parameters and set OUT parameters
989 -- (Any key or derived arguments must be set to null
990 -- when validation only mode is being used.)
991 --
992 p_object_version_number := l_in_out_parameter1;
993 p_effective_start_date := NULL;
994 p_effective_end_date := NULL;
995
996 hr_utility.set_location(' Leaving:'||l_proc, 80);
997 when others then
998 --
999 -- A validation or unexpected error has occured
1000 --
1001 rollback to update_pl_sii_details;
1002 --
1003 -- Reset IN OUT parameters and set all
1004 -- OUT parameters, including warnings, to null
1005 --
1006 p_object_version_number := l_in_out_parameter1;
1007 p_effective_start_date := NULL;
1008 p_effective_end_date := NULL;
1009 hr_utility.set_location(' Leaving:'||l_proc, 90);
1010 raise;
1011 end update_pl_sii_details;
1012 --
1013 -- ----------------------------------------------------------------------------
1014 -- |-------------------------< delete_pl_sii_details >-------------------------|
1015 -- ----------------------------------------------------------------------------
1016 --
1017 procedure delete_pl_sii_details
1018 (p_validate in boolean default false
1022 ,p_object_version_number in out nocopy number
1019 ,p_effective_date in date
1020 ,p_datetrack_delete_mode in varchar2
1021 ,p_sii_details_id in number
1023 ,p_effective_start_date out nocopy date
1024 ,p_effective_end_date out nocopy date
1025 )
1026 is
1027 --
1028 -- Declare cursors and local variables
1029 --
1030 l_effective_date date;
1031 l_proc varchar2(72) := g_package||'delete_pl_sii_details';
1032 l_program_id number;
1033 l_program_login_id number;
1034 l_program_application_id number;
1035 l_request_id number;
1036 l_sii_details_id number;
1037 l_object_version_number number;
1038 l_effective_start_date date;
1039 l_effective_end_date date;
1040 l_in_out_parameter1 number;
1041
1042 begin
1043
1044 hr_utility.set_location('Entering:'|| l_proc, 10);
1045 --
1046 -- Issue a savepoint
1047 --
1048 savepoint delete_pl_sii_details;
1049 --
1050 -- Remember IN OUT parameter IN values
1051 --
1052 l_in_out_parameter1 := p_object_version_number;
1053 --
1054 --
1055 l_object_version_number := p_object_version_number;
1056 --
1057 -- Truncate the time portion from all IN date parameters
1058 --
1059 l_effective_date := trunc(p_effective_date);
1060
1061 --
1062 -- Call Before Process User Hook
1063 --
1064
1065 begin
1066 PAY_PL_SII_BK3.delete_pl_sii_details_b
1067 (p_effective_date => p_effective_date
1068 ,p_sii_details_id => p_sii_details_id
1069 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1070 ,p_object_version_number => p_object_version_number
1071 );
1072 exception
1073 when hr_api.cannot_find_prog_unit then
1074 hr_api.cannot_find_prog_unit_error
1075 (p_module_name => 'delete_pl_sii_details'
1076 ,p_hook_type => 'BP'
1077 );
1078 end;
1079 --
1080 --
1081 -- Process Logic
1082 --
1083
1084 pay_psd_del.del
1085 (p_effective_date => p_effective_date
1086 ,p_datetrack_mode => p_datetrack_delete_mode
1087 ,p_sii_details_id => p_sii_details_id
1088 ,p_object_version_number => l_object_version_number
1089 ,p_effective_start_date => l_effective_start_date
1090 ,p_effective_end_date => l_effective_end_date
1091 );
1092 --
1093
1094
1095 --
1096 -- Call After Process User Hook
1097 --
1098 begin
1099
1100 pay_pl_sii_bk3.delete_pl_sii_details_a
1101 (p_effective_date => p_effective_date
1102 ,p_sii_details_id => p_sii_details_id
1103 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1104 ,p_object_version_number => l_object_version_number
1105 ,p_effective_start_date => l_effective_start_date
1106 ,p_effective_end_date => l_effective_end_date
1107 );
1108
1109 exception
1110 when hr_api.cannot_find_prog_unit then
1111 hr_api.cannot_find_prog_unit_error
1112 (p_module_name => 'delete_pl_sii_details'
1113 ,p_hook_type => 'AP'
1114 );
1115 end;
1116 --
1117 -- When in validation only mode raise the Validate_Enabled exception
1118 --
1119 if p_validate then
1120 raise hr_api.validate_enabled;
1121 end if;
1122 --
1123 -- Set all IN OUT and OUT parameters with out values
1124 --
1125 p_object_version_number := l_object_version_number;
1126 p_effective_start_date := l_effective_start_date;
1127 p_effective_end_date := l_effective_end_date;
1128
1129
1130 --
1131 hr_utility.set_location(' Leaving:'||l_proc, 70);
1132 exception
1133 when hr_api.validate_enabled then
1134 --
1135 -- As the Validate_Enabled exception has been raised
1136 -- we must rollback to the savepoint
1137 --
1138 rollback to delete_pl_sii_details;
1139 --
1140 -- Reset IN OUT parameters and set OUT parameters
1141 -- (Any key or derived arguments must be set to null
1142 -- when validation only mode is being used.)
1143 --
1144 p_object_version_number := l_in_out_parameter1;
1145 p_effective_start_date := NULL;
1146 p_effective_end_date := NULL;
1147
1148 hr_utility.set_location(' Leaving:'||l_proc, 80);
1149 when others then
1150 --
1151 -- A validation or unexpected error has occured
1152 --
1153 rollback to pay_pl_sii_details;
1154 --
1155 -- Reset IN OUT parameters and set all
1156 -- OUT parameters, including warnings, to null
1157 --
1158 p_object_version_number := l_in_out_parameter1;
1159 p_effective_start_date := NULL;
1160 p_effective_end_date := NULL;
1161 hr_utility.set_location(' Leaving:'||l_proc, 90);
1162 raise;
1163 end delete_pl_sii_details;
1164 --
1165 end pay_pl_sii_api;