4 **
1 PACKAGE BODY pay_ie_prsi AS
2 /* $Header: pyieprsi.pkb 120.2 2009/05/06 05:09:27 knadhan ship $ */
3 /*
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 ** 26 JUN 2001 ILeath N/A Created
16 ** 16 SEP 2002 Vimal N/A Fixed bug 2547639.
17 ** The procedure initialise has been changed
18 ** such that contribution_class is now set to
19 ** IE_A. Also even if the cursor
20 ** c_prsi_dtl retuns no row, wew still retun
21 ** 1 instead of 0 as this will let default
22 ** PRSI contributions.
23 ** 10-JAN-2003 SMRobins 2652940 Added function:
24 ** get_ins_weeks_for_monthly_emps
25 ** 14-JAN-2003 SMRobins Added function: get_period_type
26 ** 04-feb-2004 vmkhande 3419204 The get_ins_weeks_for_monthly
27 returns l_count_of_days of 1, when
28 l_count_of_days is -ve, this is incorrect
29 for terminarted employees being processed
30 after last standard process date.
31 it should retrun 0 as the number of insuarable
32 weeks for terminated employee is 0. But
33 l_count_of_days is used in IE_PRSI_INITIALIZE
34 to derive the multiplying factor. hence
35 the l_count_of_days is not being set to 0
36 but we will return -ve and handle -ve value
37 in fast formula.
38 11-feb-2004 vmkhande 3436179 Fixed gscc warning.
39 Date conversion was not using format mask.
40 Added format mask.
41 16-dec-2006 rbhardwa 3427614 Modified code to calculate correct insurable weeks
42 for jan and dec.
43 ** 21-APR-2009 knadhan 8448176 Added function get_bal_value_30_04_09
44 ** 21-APR-2009 knadhan 8448176 Added exception block in function get_bal_value_30_04_09
45 ------------------------------------------------------------------------------
46 */
47 g_package varchar2(33) := 'pay_ie_prsi.';
48
49 /* knadhan */
50 FUNCTION get_bal_value_30_04_09 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
51 ,p_tax_unit_id IN NUMBER
52 ,p_balance_name IN pay_balance_types.balance_name%TYPE
53 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
54 ,p_till_date IN DATE) RETURN number IS
55 CURSOR cur_assignment_action is
56 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
57 paa.assignment_action_id),16))
58 FROM pay_assignment_actions paa
59 ,pay_payroll_actions ppa
60 WHERE paa.assignment_id=p_assignment_id
61 AND paa.payroll_action_id=ppa.payroll_action_id
62 AND ppa.action_type in ('Q','B','R','I','V')
63 AND ppa.action_status ='C'
64 AND paa.source_action_id is null
65 AND ppa.effective_date<= p_till_date;
66 CURSOR cur_defined_balance_id is
67 SELECT pdb.defined_balance_id
68 FROM pay_balance_dimensions pbd
69 ,pay_defined_balances pdb
73 AND pbd.dimension_name =p_dimension_name
70 ,pay_balance_types pbt
71 WHERE pbt.balance_name = p_balance_name
72 AND pbt.legislation_code='IE'
74 AND pbd.legislation_code='IE'
75 AND pdb.balance_dimension_id=pbd.balance_dimension_id
76 AND pdb.balance_type_id=pbt.balance_type_id;
77 l_assignment_action pay_assignment_actions.assignment_action_id%type;
78 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
79 l_balance_value NUMBER:=0;
80 BEGIN
81 OPEN cur_assignment_action;
82 FETCH cur_assignment_action INTO l_assignment_action;
83 CLOSE cur_assignment_action;
84 OPEN cur_defined_balance_id;
85 FETCH cur_defined_balance_id INTO l_defined_balance_id;
86 CLOSE cur_defined_balance_id;
87 /* 8448176 */
88 BEGIN
89 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
90 l_assignment_action,
91 p_tax_unit_id,
92 null,
93 null,
94 null,
95 null,
96 null);
97 EXCEPTION When others THEN
98 RETURN 0;
99 END;
100 IF l_balance_value is null THEN
101 l_balance_value:=0;
102 END IF;
103 RETURN l_balance_value;
104 END;
105
106 Function get_prsi_details ( p_assignment_id in number
107 ,p_payroll_action_id in number
108 ,p_contribution_class out nocopy varchar2
109 ,p_overridden_subclass out nocopy varchar2
110 ,p_soc_ben_flag out nocopy varchar2
111 ,p_overridden_ins_weeks out nocopy number
112 ,p_community_flag out nocopy varchar2
113 ,p_exemption_start_date out nocopy date
114 ,p_exemption_end_date out nocopy date) return number is
115
116
117 --Local vriables-----
118
119 l_proc varchar2(72) := g_package||'get_prsi_details';
120
121 cursor c_prsi_dtl is select contribution_class
122 ,overridden_subclass
123 ,soc_ben_flag
124 ,overridden_ins_weeks
125 ,community_flag
126 ,exemption_start_date
127 ,exemption_end_date
128 from pay_ie_prsi_details_v pipd
129 ,pay_payroll_actions ppa
130 where assignment_id = p_assignment_id
131 and ppa.payroll_action_id = p_payroll_action_id
132 and ppa.date_earned between pipd.effective_start_date and pipd.effective_end_date;
133
134 procedure initialise is
135 begin
136 p_contribution_class:='IE_A';
137 --p_overridden_subclass:='z ';
138 p_soc_ben_flag:='N';
139 --p_overridden_ins_weeks:=0;
140 --p_community_flag:=' ';
141 --p_exemption_start_date:=to_date('01-01-0001','DD-MM-YYYY');
142 --p_exemption_end_date:=to_date('01-01-0001','DD-MM-YYYY');
143 end;
144
145
146 --end Local vriables---------
147
148 begin
149
150 hr_utility.set_location('Entering:'||l_proc, 5);
151
152 open c_prsi_dtl;
153
154 fetch c_prsi_dtl into p_contribution_class
155 ,p_overridden_subclass
156 ,p_soc_ben_flag
157 ,p_overridden_ins_weeks
158 ,p_community_flag
159 ,p_exemption_start_date
160 ,p_exemption_end_date;
161 if c_prsi_dtl%notfound then
162 initialise;
163 close c_prsi_dtl;
164 return 1;
165 end if;
166 close c_prsi_dtl;
167 hr_utility.set_location('Leaving:'||l_proc, 30);
168 return 1;
169 exception when others then
170 initialise;
171 close c_prsi_dtl;
172 return 0;
173
174 end get_prsi_details;
175 --
176 -- Adding function get_ins_weeks_for_monthly_emps
177 -- to address bug 2652940.
178 -- We work out what DAY the 01-JAN for the processing
179 -- year falls on, and then work out how many of those
180 -- days falls between the greatest start date and
181 -- lowest end date. The greatest start date is the greatest
182 -- of processing period start date and emp hire date (except
183 -- for January which excludes the first day of the month).
184 -- The lowest end date is the earliest of the processing
185 -- period end date and the emp termination date
186 --
187 Function get_ins_weeks_for_monthly_emps( p_hire_date in date
188 ,p_proc_period_start_date in date
189 ,p_term_date in date
190 ,p_proc_period_end_date in date
191 ,p_processing_date in date)
192 Return NUMBER IS
193 --
194 l_day varchar2(120);
195 l_test_day varchar2(120);
196 l_actual_start_of_year date;
197 l_calc_start_of_year date;
198 l_count_of_days number := 0;
199 l_greatest_start_date date;
200 l_lowest_end_date date;
201 l_test_date date;
202 l_compare_date date; -- 3427614
203 --
204 Cursor day_of_week (p_test_date in date) is
208 -- l_calc_start_of_year := to_date('02-JAN-'||to_char(p_processing_date,'RRRR'));
205 select to_char(p_test_date, 'DAY') from sys.dual;
206 BEGIN
207 /* 3436179 */
209 l_calc_start_of_year := to_date('02/01/'||to_char(p_processing_date,'RRRR'),
210 'DD/MM/RRRR');
211
212 l_actual_start_of_year := l_calc_start_of_year -1;
213 --
214 -- What days is the first day of the year
215 --
216 OPEN day_of_week(l_actual_start_of_year);
217 FETCH day_of_week into l_day;
218 CLOSE day_of_week;
219 l_greatest_start_date := greatest(p_proc_period_start_date, p_hire_date, l_actual_start_of_year); -- 3427614
220 l_lowest_end_date := least(p_proc_period_end_date, p_term_date);
221 l_test_date := l_greatest_start_date;
222 WHILE l_test_date <= l_lowest_end_date loop
223 OPEN day_of_week(l_test_date);
224 FETCH day_of_week into l_test_day;
225 CLOSE day_of_week;
226 IF l_test_day = l_day THEN
227 l_count_of_days := l_count_of_days + 1;
228 END IF;
229 l_test_date := l_test_date + 1;
230 END LOOP;
231 /* 3419204
232 If l_count_of_days < 1 then
233 l_count_of_days := 1;
234 End If;
235 */
236 l_compare_date := to_date('01/12/'||to_char(l_greatest_start_date,'RRRR'), -- to assign only 4 weeks for dec month
237 'DD/MM/RRRR'); -- 3427614
238 IF l_greatest_start_date >= l_compare_date THEN
239 l_count_of_days := l_count_of_days - 1;
240 END IF;
241 RETURN l_count_of_days;
242 end get_ins_weeks_for_monthly_emps;
243 --
244 Function get_period_type (p_payroll_id in number
245 ,p_session_date in date)
246 RETURN varchar2 IS
247 --
248 l_period_type varchar2(120);
249 --
250 cursor get_type is
251 select pap.period_type
252 from pay_all_payrolls_f pap
253 where pap.payroll_id = p_payroll_id
254 and p_session_date between pap.effective_start_date and pap.effective_end_date;
255 --
256 Begin
257 OPEN get_type;
258 FETCH get_type into l_period_type;
259 CLOSE get_type;
260 RETURN l_period_type;
261 End get_period_type;
262 --
263 Function get_period_start_date (p_payroll_id in number
264 ,p_session_date in date)
265 RETURN varchar2 IS
266 --
267 l_period_start_date date;
268 --
269 cursor get_start_date is
270 select ptp.start_date
271 from per_time_periods ptp
272 where ptp.payroll_id = p_payroll_id
273 and p_session_date between ptp.start_date and ptp.end_date;
274 --
275 Begin
276 OPEN get_start_date;
277 FETCH get_start_date into l_period_start_date;
278 CLOSE get_start_date;
279 RETURN l_period_start_date;
280 End get_period_start_date;
281 --
282 Function get_period_end_date (p_payroll_id in number
283 ,p_session_date in date)
284 RETURN varchar2 IS
285 --
286 l_period_end_date date;
287 --
288 cursor get_end_date is
289 select ptp.end_date
290 from per_time_periods ptp
291 where ptp.payroll_id = p_payroll_id
292 and p_session_date between ptp.start_date and ptp.end_date;
293 --
294 Begin
295 OPEN get_end_date;
296 FETCH get_end_date into l_period_end_date;
297 CLOSE get_end_date;
298 RETURN l_period_end_date;
299 End get_period_end_date;
300 --
301 end pay_ie_prsi;
[Home] [Help]