1 PACKAGE BODY pay_ie_prsi AS
2 /* $Header: pyieprsi.pkb 120.1 2006/12/17 08:43:38 rbhardwa 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 ** 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 ------------------------------------------------------------------------------
44 */
45 g_package varchar2(33) := 'pay_ie_prsi.';
46
47 Function get_prsi_details ( p_assignment_id in number
48 ,p_payroll_action_id in number
49 ,p_contribution_class out nocopy varchar2
50 ,p_overridden_subclass out nocopy varchar2
51 ,p_soc_ben_flag out nocopy varchar2
52 ,p_overridden_ins_weeks out nocopy number
53 ,p_community_flag out nocopy varchar2
54 ,p_exemption_start_date out nocopy date
55 ,p_exemption_end_date out nocopy date) return number is
56
57
58 --Local vriables-----
59
60 l_proc varchar2(72) := g_package||'get_prsi_details';
61
62 cursor c_prsi_dtl is select contribution_class
63 ,overridden_subclass
64 ,soc_ben_flag
65 ,overridden_ins_weeks
66 ,community_flag
67 ,exemption_start_date
68 ,exemption_end_date
69 from pay_ie_prsi_details_v pipd
70 ,pay_payroll_actions ppa
71 where assignment_id = p_assignment_id
72 and ppa.payroll_action_id = p_payroll_action_id
73 and ppa.date_earned between pipd.effective_start_date and pipd.effective_end_date;
74
75 procedure initialise is
76 begin
77 p_contribution_class:='IE_A';
78 --p_overridden_subclass:='z ';
79 p_soc_ben_flag:='N';
80 --p_overridden_ins_weeks:=0;
81 --p_community_flag:=' ';
82 --p_exemption_start_date:=to_date('01-01-0001','DD-MM-YYYY');
83 --p_exemption_end_date:=to_date('01-01-0001','DD-MM-YYYY');
84 end;
85
86
87 --end Local vriables---------
88
89 begin
90
91 hr_utility.set_location('Entering:'||l_proc, 5);
92
93 open c_prsi_dtl;
94
95 fetch c_prsi_dtl into p_contribution_class
96 ,p_overridden_subclass
97 ,p_soc_ben_flag
98 ,p_overridden_ins_weeks
99 ,p_community_flag
100 ,p_exemption_start_date
101 ,p_exemption_end_date;
102 if c_prsi_dtl%notfound then
103 initialise;
104 close c_prsi_dtl;
105 return 1;
106 end if;
107 close c_prsi_dtl;
108 hr_utility.set_location('Leaving:'||l_proc, 30);
109 return 1;
110 exception when others then
111 initialise;
112 close c_prsi_dtl;
113 return 0;
114
115 end get_prsi_details;
116 --
117 -- Adding function get_ins_weeks_for_monthly_emps
118 -- to address bug 2652940.
119 -- We work out what DAY the 01-JAN for the processing
120 -- year falls on, and then work out how many of those
121 -- days falls between the greatest start date and
122 -- lowest end date. The greatest start date is the greatest
123 -- of processing period start date and emp hire date (except
124 -- for January which excludes the first day of the month).
125 -- The lowest end date is the earliest of the processing
126 -- period end date and the emp termination date
127 --
128 Function get_ins_weeks_for_monthly_emps( p_hire_date in date
129 ,p_proc_period_start_date in date
130 ,p_term_date in date
131 ,p_proc_period_end_date in date
132 ,p_processing_date in date)
133 Return NUMBER IS
134 --
135 l_day varchar2(120);
136 l_test_day varchar2(120);
137 l_actual_start_of_year date;
138 l_calc_start_of_year date;
139 l_count_of_days number := 0;
140 l_greatest_start_date date;
141 l_lowest_end_date date;
142 l_test_date date;
143 l_compare_date date; -- 3427614
144 --
145 Cursor day_of_week (p_test_date in date) is
146 select to_char(p_test_date, 'DAY') from sys.dual;
147 BEGIN
148 /* 3436179 */
149 -- l_calc_start_of_year := to_date('02-JAN-'||to_char(p_processing_date,'RRRR'));
150 l_calc_start_of_year := to_date('02/01/'||to_char(p_processing_date,'RRRR'),
151 'DD/MM/RRRR');
152
153 l_actual_start_of_year := l_calc_start_of_year -1;
154 --
155 -- What days is the first day of the year
156 --
157 OPEN day_of_week(l_actual_start_of_year);
158 FETCH day_of_week into l_day;
159 CLOSE day_of_week;
160 l_greatest_start_date := greatest(p_proc_period_start_date, p_hire_date, l_actual_start_of_year); -- 3427614
161 l_lowest_end_date := least(p_proc_period_end_date, p_term_date);
162 l_test_date := l_greatest_start_date;
163 WHILE l_test_date <= l_lowest_end_date loop
164 OPEN day_of_week(l_test_date);
165 FETCH day_of_week into l_test_day;
166 CLOSE day_of_week;
167 IF l_test_day = l_day THEN
168 l_count_of_days := l_count_of_days + 1;
169 END IF;
170 l_test_date := l_test_date + 1;
171 END LOOP;
172 /* 3419204
173 If l_count_of_days < 1 then
174 l_count_of_days := 1;
175 End If;
176 */
177 l_compare_date := to_date('01/12/'||to_char(l_greatest_start_date,'RRRR'), -- to assign only 4 weeks for dec month
178 'DD/MM/RRRR'); -- 3427614
179 IF l_greatest_start_date >= l_compare_date THEN
180 l_count_of_days := l_count_of_days - 1;
181 END IF;
182 RETURN l_count_of_days;
183 end get_ins_weeks_for_monthly_emps;
184 --
185 Function get_period_type (p_payroll_id in number
186 ,p_session_date in date)
187 RETURN varchar2 IS
188 --
189 l_period_type varchar2(120);
190 --
191 cursor get_type is
192 select pap.period_type
193 from pay_all_payrolls_f pap
194 where pap.payroll_id = p_payroll_id
195 and p_session_date between pap.effective_start_date and pap.effective_end_date;
196 --
197 Begin
198 OPEN get_type;
199 FETCH get_type into l_period_type;
200 CLOSE get_type;
201 RETURN l_period_type;
202 End get_period_type;
203 --
204 Function get_period_start_date (p_payroll_id in number
205 ,p_session_date in date)
206 RETURN varchar2 IS
207 --
208 l_period_start_date date;
209 --
210 cursor get_start_date is
211 select ptp.start_date
212 from per_time_periods ptp
213 where ptp.payroll_id = p_payroll_id
214 and p_session_date between ptp.start_date and ptp.end_date;
215 --
216 Begin
217 OPEN get_start_date;
218 FETCH get_start_date into l_period_start_date;
219 CLOSE get_start_date;
220 RETURN l_period_start_date;
221 End get_period_start_date;
222 --
223 Function get_period_end_date (p_payroll_id in number
224 ,p_session_date in date)
225 RETURN varchar2 IS
226 --
227 l_period_end_date date;
228 --
229 cursor get_end_date is
230 select ptp.end_date
231 from per_time_periods ptp
232 where ptp.payroll_id = p_payroll_id
233 and p_session_date between ptp.start_date and ptp.end_date;
234 --
235 Begin
236 OPEN get_end_date;
237 FETCH get_end_date into l_period_end_date;
238 CLOSE get_end_date;
239 RETURN l_period_end_date;
240 End get_period_end_date;
241 --
242 end pay_ie_prsi;
[Home] [Help]