1 package body PER_IE_TERMINATION as
2 /* $Header: peieterm.pkb 120.1 2006/09/18 08:48:03 spendhar noship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** IE PAYE package
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 15 SEP 2003 srkotwal N/A Created for bug 3134506
16 24-oct-2003 vmkhande bug fix for 3208777
17 18-SEP-06 spendhar 5472781 Added a check for chk_product_install so that
18 validation fires only if legislation installed.
19 -------------------------------------------------------------------------------
20 */
21 -- Gets Person_id for given period_of_service_id
22
23 CURSOR c_get_person_id (c_period_of_service_id per_periods_of_service.period_of_service_id%Type) is
24 select ppos.person_id
25 from per_periods_of_service ppos
26 where period_of_service_id = c_period_of_service_id;
27
28 -- Gets assignment_id from per_all_assignments_f for above generated person_id
29 CURSOR c_get_asg_id (c_person_id per_periods_of_service.person_id%type,
30 c_effective_date DATE ) is
31 select paaf.assignment_id
32 from per_all_assignments_f paaf
33 where paaf.person_id = c_person_id and
34 c_effective_date between effective_start_date
35 and effective_end_date;
36
37 PROCEDURE actual_termination(
38 p_period_of_service_id per_periods_of_service.period_of_service_id%TYPE,
39 p_actual_termination_date per_periods_of_service.actual_termination_date%TYPE)
40 IS
41 --end Local vriables---------
42 BEGIN
43 /*
44 doing no processing here as we need to set the end dates in
45 paye and prsi tables to final process date.
46 final process date is not available in this user hook.
47 so created another user use which is called by FINAL_PROCESS_DATE
48 */
49 hr_utility.set_location(
50 'Entering pay_ie_termination ....'|| 'per_ie_termination',
51 10);
52 hr_utility.set_location(
53 'Leaving pay_ie_termination ....'|| 'per_ie_termination',
54 10);
55 EXCEPTION
56 WHEN OTHERS
57 THEN
58 raise_application_error(-20001, SQLERRM(SQLCODE) );
59 END actual_termination;
60
61 PROCEDURE Final_termination( p_period_of_service_id per_periods_of_service.period_of_service_id%TYPE
62 ,p_final_process_date Date)
63 IS
64 /*
65 cursor csr_asg_id is
66 select assignment_id
67 from per_assignments_f
68 where period_of_service_id = p_period_of_service_id;
69 */
70 -- Get paye_details_id,effective_start_date and effective_end_date for above assignment_id
71
72 CURSOR c_get_paye_details(
73 p_assignment_id per_all_assignments_f.assignment_id%TYPE)
74 IS
75 SELECT paye_details_id,
76 effective_start_date,
77 effective_end_date
78 FROM pay_ie_paye_details_f
79 WHERE assignment_id = p_assignment_id AND
80 p_final_process_date BETWEEN effective_start_date
81 AND effective_end_date
82 ORDER BY effective_start_date;
83
84 -- Get prsi_details_id,effective_start_date and effective_end_date for above assignment_id
85 CURSOR c_get_prsi_details(
86 p_assignment_id per_all_assignments_f.assignment_id%TYPE)
87 IS
88 SELECT prsi_details_id, effective_start_date, effective_end_date
89 FROM pay_ie_prsi_details_f
90 WHERE assignment_id = p_assignment_id AND
91 p_final_process_date BETWEEN effective_start_date
92 AND effective_end_date
93 ORDER BY effective_start_date;
94 --Local vriables-----
95 l_person_id per_periods_of_service.person_id%TYPE;
96 -- l_assignment_id per_all_assignments_f.assignment_id%TYPE;
97 l_paye_detail_id pay_ie_paye_details_f.paye_details_id%TYPE;
98 l_paye_start_date pay_ie_paye_details_f.effective_start_date%TYPE;
99 l_paye_end_date pay_ie_paye_details_f.effective_end_date%TYPE;
100 l_prsi_detail_id pay_ie_prsi_details_f.prsi_details_id%TYPE;
101 l_prsi_start_date pay_ie_prsi_details_f.effective_start_date%TYPE;
102 l_prsi_end_date pay_ie_prsi_details_f.effective_end_date%TYPE;
103 --end Local vriables---------
104 BEGIN
105
106
107 /* Added for GSI Bug 5472781 */
108 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
109
110 hr_utility.set_location(
111 'Entering pay_ie_termination ....'|| 'per_ie_termination',
112 10);
113
114 OPEN c_get_person_id (p_period_of_service_id);
115 FETCH c_get_person_id into l_person_id;
116 CLOSE c_get_person_id;
117 /*
118 OPEN c_get_asg_id(l_person_id, p_final_process_date);
119 FETCH c_get_asg_id into l_assignment_id;
120 CLOSE c_get_asg_id;
121 */
122 for asg_id in c_get_asg_id(l_person_id, p_final_process_date)
123 loop
124 l_prsi_detail_id := null;
125 l_prsi_start_date := null;
126 l_prsi_end_date := null;
127 l_paye_detail_id := null;
128 l_paye_start_date := null;
129 l_paye_end_date := null;
130 -- Processing PRSI data
131 OPEN c_get_prsi_details(asg_id.assignment_id);
132 FETCH c_get_prsi_details INTO l_prsi_detail_id,
133 l_prsi_start_date,
134 l_prsi_end_date;
135
136 UPDATE pay_ie_prsi_details_f
137 SET effective_end_date = p_final_process_date
138 WHERE prsi_details_id = l_prsi_detail_id AND
139 p_final_process_date BETWEEN effective_start_date
140 AND effective_end_date;
141
142 DELETE FROM pay_ie_prsi_details_f
143 WHERE prsi_details_id = l_prsi_detail_id AND
144 effective_start_date > p_final_process_date;
145
146 CLOSE c_get_prsi_details;
147 -- Processing PAYE data
148 OPEN c_get_paye_details(asg_id.assignment_id);
149 FETCH c_get_paye_details INTO l_paye_detail_id,
150 l_paye_start_date,
151 l_paye_end_date;
152 UPDATE pay_ie_paye_details_f
153 SET effective_end_date = p_final_process_date
154 WHERE paye_details_id = l_paye_detail_id AND
155 p_final_process_date BETWEEN effective_start_date
156 AND effective_end_date;
157 DELETE FROM pay_ie_paye_details_f
158 WHERE paye_details_id = l_paye_detail_id AND
159 effective_start_date > p_final_process_date;
160 CLOSE c_get_paye_details;
161 hr_utility.set_location(
162 'Leaving pay_ie_termination ....'|| 'per_ie_termination',
163 10);
164 End Loop;
165
166 END IF; /* Added for GSI Bug 5472781 */
167
168 EXCEPTION
169 WHEN OTHERS
170 THEN
171 raise_application_error(-20001, SQLERRM(SQLCODE) );
172 END Final_termination;
173
174 PROCEDURE REVERSE(
175 p_period_of_service_id per_periods_of_service.period_of_service_id%TYPE,
176 p_actual_termination_date per_periods_of_service.actual_termination_date%TYPE,
177 p_leaving_reason per_periods_of_service.leaving_reason%TYPE)
178 IS
179 l_person_id per_periods_of_service.person_id%TYPE;
180 -- l_assignment_id per_all_assignments_f.assignment_id%TYPE;
181 l_final_process_date DATE;
182
183 CURSOR csr_final_process_date(p_assignment_id Number)
184 IS
185 SELECT max(effective_end_date)
186 FROM pay_ie_paye_details_f
187 WHERE assignment_id = p_assignment_id;
188 --
189 BEGIN
190 hr_utility.trace('Inside reverse term');
191
192 OPEN c_get_person_id (p_period_of_service_id);
193 FETCH c_get_person_id into l_person_id;
194 CLOSE c_get_person_id;
195 /*
196 OPEN c_get_asg_id(l_person_id, p_actual_termination_date);
197 FETCH c_get_asg_id into l_assignment_id;
198 CLOSE c_get_asg_id;
199 */
200 for asg_id in c_get_asg_id(l_person_id, p_actual_termination_date)
201 loop
202 l_final_process_date:=null;
203 OPEN csr_final_process_date(asg_id.assignment_id);
204 FETCH csr_final_process_date INTO l_final_process_date;
205 CLOSE csr_final_process_date;
206 hr_utility.trace('l_final_process_date' || to_char(l_final_process_date));
207 --
208 UPDATE pay_ie_paye_details_f
209 SET effective_end_date = TO_CHAR(hr_general.end_of_time, 'DD-MON-YYYY')
210 WHERE assignment_id = asg_id.assignment_id AND
211 l_final_process_date BETWEEN effective_start_date
212 AND effective_end_date;
213
214 UPDATE pay_ie_prsi_details_f
215 SET effective_end_date = TO_CHAR(hr_general.end_of_time, 'DD-MON-YYYY')
216 WHERE assignment_id = asg_id.assignment_id AND
217 l_final_process_date BETWEEN effective_start_date
218 AND effective_end_date;
219 End loop;
220 END REVERSE;
221 END per_ie_termination;
[Home] [Help]