1 PACKAGE BODY PER_PL_TERMINATION as
2 /* $Header: peplterp.pkb 120.0 2006/03/01 22:41:38 mseshadr noship $ */
3
4 PROCEDURE REVERSE(p_period_of_service_id per_periods_of_service.period_of_service_id%TYPE
5 ,p_actual_termination_date per_periods_of_service.actual_termination_date%TYPE
6 ,p_leaving_reason per_periods_of_service.leaving_reason%TYPE) is
7
8 cursor csr_normal_assignments is
9 select distinct(paaf.assignment_id)
10 from per_all_assignments_f paaf
11 ,per_assignment_status_types past
12 ,hr_soft_coding_keyflex kyflex
13 where paaf.period_of_service_id=p_period_of_service_id
14 and (p_actual_termination_date+1) between paaf.effective_start_date
15 and paaf.effective_end_date
16 and paaf.assignment_status_type_id=past.assignment_status_type_id
17 and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
18 and kyflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
19 and kyflex.segment3='NORMAL';
20
21 cursor csr_term_paye_records(r_per_or_asg_id number)is
22 select paye_details_id
23 ,object_version_number ovn
24 ,effective_start_date
25 from pay_pl_paye_details_f
26 where per_or_asg_id=r_per_or_asg_id
27 and contract_category='TERM_NORMAL';
28
29 cursor csr_term_sii_records(r_per_or_asg_id number)is
30 select sii_details_id
31 ,object_version_number ovn
32 ,effective_start_date
33 from pay_pl_sii_details_f
34 where per_or_asg_id=r_per_or_asg_id
35 and contract_category='TERM_NORMAL';
36
37
38 l_csr_term_sii_records csr_term_sii_records%rowtype;
39 l_csr_term_paye_records csr_term_paye_records%rowtype;
40 l_temp_effective_start_date date;
41 l_temp_effective_end_date date;
42 l_debug boolean;
43 l_proc varchar2(26);
44 Begin
45
46 --Find all non terminated normal contracts
47 --that are not terminated as on actual_termination_date+1
48 --if exists,check if they have sii/tax record.If they do,zap the sii/tax record
49
50 l_debug:=hr_utility.debug_enabled;
51
52 --If performing any HR related validation
53 --after post termination,call chk_product_install for
54 --Oracle Human Resources also.
55
56
57 IF NOT hr_utility.chk_product_install( p_product =>'Oracle Payroll'
58 ,p_legislation =>'PL'
59 ,p_language =>'US'
60 ) then
61 if l_debug then
62 hr_utility.trace('PL PAY not installed.Not performing validations');
63 end if;
64 return; -- Polish HR not installed
65 End if;
66
67 if l_debug then
68 l_proc:='PER_PL_TERMINATION.REVERSE';
69 hr_utility.set_location('Entering: '||l_proc,10);
70 end if;
71
72 for i in csr_normal_assignments loop
73
74 if l_debug then
75 hr_utility.set_location(l_proc ,i.assignment_id);
76 end if;
77 open csr_term_paye_records(r_per_or_asg_id=>i.assignment_id);
78 fetch csr_term_paye_records into l_csr_term_paye_records;
79 if csr_term_paye_records%found then
80 close csr_term_paye_records;
81 --zap the unwanted record
82 if l_debug then
83 hr_utility.trace('Calling Paye Zap');
84 end if;
85 pay_pl_paye_api.delete_pl_paye_details
86 (p_validate =>false
87 ,p_effective_date =>l_csr_term_paye_records.effective_start_date
88 ,p_datetrack_delete_mode =>'ZAP'
89 ,p_paye_details_id =>l_csr_term_paye_records.paye_details_id
90 ,p_object_version_number =>l_csr_term_paye_records.ovn
91 ,p_effective_start_date =>l_temp_effective_start_date
92 ,p_effective_end_date =>l_temp_effective_start_date
93 );
94 else
95 close csr_term_paye_records;
96 end if;
97
98 if l_debug then
99 hr_utility.set_location(l_proc ,15);
100 end if;
101 --Paye records processed.SII record next
102
103 open csr_term_sii_records(r_per_or_asg_id=>i.assignment_id);
104 fetch csr_term_sii_records into l_csr_term_sii_records;
105 if csr_term_sii_records%found then
106 close csr_term_sii_records;
107 --zap the unwanted record
108 if l_debug then
109 hr_utility.trace('Calling SII Zap');
110 end if;
111 pay_pl_sii_api.delete_pl_sii_details
112 (p_validate =>false
113 ,p_effective_date =>l_csr_term_sii_records.effective_start_date
114 ,p_datetrack_delete_mode =>'ZAP'
115 ,p_sii_details_id =>l_csr_term_sii_records.sii_details_id
116 ,p_object_version_number =>l_csr_term_sii_records.ovn
117 ,p_effective_start_date =>l_temp_effective_start_date
118 ,p_effective_end_date =>l_temp_effective_start_date
119 );
120 else
121 close csr_term_sii_records;
122 end if;
123
124 if l_debug then
125 hr_utility.set_location(l_proc ,20);
126 end if;
127
128 end loop;
129
130 if l_debug then
131 hr_utility.set_location('Leaving: '||l_proc,30);
132 end if;
133
134 End REVERSE;
135 PROCEDURE ACTUAL_TERMINATION_EMP(p_period_of_service_id per_periods_of_service.period_of_service_id%TYPE
136 ,p_actual_termination_date per_periods_of_service.actual_termination_date%TYPE
137 ,p_business_group_id NUMBER) is
138 -------------------------------------------------------------------------+
139 -- -+
140 -- Name : ACTUAL_TERMINATION_EMP -+
141 -- Type : Procedure -+
142 -- Access : Public -+
143 -- Description : Polish leg hook to be called when person type -+
144 -- employee is Terminated. -+
145 -- -+
146 -- 1)Check if Polish HR is installed before performing -+
147 -- any validation -+
148 -- 2)Check if Payroll is installed.If yes,call package -+
149 -- procedure to create/modify SII and Tax records -+
150 -- -+
151 -------------------------------------------------------------------------+
152
153
154 l_debug boolean;
155 l_proc varchar2(41);
156 Begin
157 l_debug:=hr_utility.debug_enabled;
158
159 if l_debug then
160 l_proc:='PER_PL_TERMINATION.ACTUAL_TERMINATION_EMP';
161 hr_utility.set_location(l_proc,10);
162 end if;
163
164
165 IF NOT hr_utility.chk_product_install( p_product =>'Oracle Human Resources'
166 ,p_legislation =>'PL'
167 ,p_language =>'US'
168 ) then
169 if l_debug then
170 hr_utility.set_location(l_proc,20);
171 hr_utility.trace('PL PER not installed.Not performing validations');
172 end if;
173
174 return; -- Polish HR not installed
175
176
177 End if;
178
179 --Place anymore hr related logic here.
180 --calls to Payroll related pkg to be done after
181 --checking Polish Payroll Installation.
182
183
184 IF hr_utility.chk_product_install( p_product =>'Oracle Payroll' --should use PAY :)
185 ,p_legislation =>'PL'
186 ,p_language =>'US'
187 ) then
188 if l_debug then
189 hr_utility.set_location(l_proc,30);
190 end if;
191
192 Pay_PL_POST_TERMINATION_PKG.Actual_term_sii_tax_records( p_debug => l_debug
193 ,p_period_of_service_id => p_period_of_service_id
194 ,p_actual_termination_date => p_actual_termination_date
195 ,p_business_group_id => p_business_group_id
196 );
197 End if;
198
199 if l_debug then
200 hr_utility.set_location(l_proc,40);
201 end if;
202 END Actual_Termination_Emp;
203 End PER_PL_TERMINATION;
204