1 PACKAGE BODY PAY_US_EXTRA_PER_INFO_LEG_HOOK AS
2 /* $Header: pyusnra.pkb 120.1.12010000.2 2008/08/06 08:34:54 ubhat ship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : PAY_US_EXTRA_PER_INFO_LEG_HOOK
9 Package File Name : pyusnra.pkb
10
11 Description : This package will be called from Before Process Hook
12 hr_person_extra_info_api.create_person_extra_info and
13 hr_person_extra_info_api.update_person_extra_info for US
14 legislation. It is used to check for the Non Resident Status
15 of the employee and do the necessary checks .
16 Change List:
17 ------------
18 Name Date Version Bug Text
19 ------------- ----------- ------- ------- ------------------------------
20 vaprakas 7-DEC-2006 115.0 5601735 Created.
21 rnestor 22-Feb-2008 115.2 6794488 Removed FIT check
22
23 */
24
25 procedure person_check_nra_status_create(P_PERSON_ID in NUMBER
26 ,P_INFORMATION_TYPE in VARCHAR2
27 ,P_PEI_INFORMATION_CATEGORY in VARCHAR2
28 ,P_PEI_INFORMATION5 in VARCHAR2
29 ,P_PEI_INFORMATION9 in VARCHAR2)
30
31 is
32 l_filing_status_code varchar2(2);
33 l_information_type per_people_extra_info.information_type%TYPE;
34 l_pei_information_category per_people_extra_info.pei_information_category%TYPE;
35 l_pei_information5 per_people_extra_info.pei_information5%TYPE;
36 l_pei_information9 per_people_extra_info.pei_information9%TYPE;
37 l_person_id per_people_f.person_id%TYPE;
38 l_withholding_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
39 l_fit_exempt pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE;
40 l_wa_fed pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
41 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
42 l_student_flag varchar2(3);
43 l_student per_people_extra_info.pei_information1%TYPE;
44 l_business_apprentice per_people_extra_info.pei_information2%TYPE;
45
46
47 cursor csr_chk_student_status is
48 select pei_information1,pei_information2
49 from per_people_extra_info
50 where person_id=l_person_id
51 and information_type like 'PER_US_ADDITIONAL_DETAILS'
52 and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
53 and (pei_information1 = 'Y' or pei_information2 = 'Y');
54
55
56 CURSOR get_assignment_id(p_person_id NUMBER) is
57 SELECT distinct assignment_id
58 FROM per_all_assignments_f paf ,per_all_people_f ppf
59 WHERE paf.person_id=ppf.person_id
60 and ppf.person_id=p_person_id
61 and paf.primary_flag='Y';
62
63 CURSOR get_wa_fed(p_assignment_id NUMBER)
64 IS
65 SELECT WITHHOLDING_ALLOWANCES
66 FROM pay_us_emp_fed_tax_rules_f fetr
67 WHERE fetr.assignment_id=p_assignment_id
68 and fetr.effective_start_date <=(select sysdate from dual)
69 and fetr.effective_end_date >=(select sysdate from dual);
70
71 CURSOR get_fsc_fed(p_assignment_id NUMBER)
72 IS
73 SELECT FILING_STATUS_CODE
74 FROM pay_us_emp_fed_tax_rules_f fetr
75 WHERE fetr.assignment_id=p_assignment_id
76 and fetr.effective_start_date <=(select sysdate from dual)
77 and fetr.effective_end_date >=(select sysdate from dual);
78
79 CURSOR get_fitexempt_fed(p_assignment_id NUMBER)
80 IS
81 SELECT FIT_EXEMPT
82 FROM pay_us_emp_fed_tax_rules_f fetr
83 WHERE fetr.assignment_id=p_assignment_id
84 and fetr.effective_start_date <=(select sysdate from dual)
85 and fetr.effective_end_date >=(select sysdate from dual);
86
87 begin
88 hr_utility.trace('Entering PAY_US_EXTRA_PER_INFO_LEG_HOOK.person_check_nra_status_create');
89
90 l_person_id := P_PERSON_ID;
91 l_information_type := P_INFORMATION_TYPE;
92 l_pei_information_category := P_PEI_INFORMATION_CATEGORY;
93 l_pei_information5 := P_PEI_INFORMATION5;
94 l_pei_information9 := P_PEI_INFORMATION9;
95 l_student_flag :='No';
96
97
98 open get_assignment_id(P_PERSON_ID);
99 fetch get_assignment_id into l_assignment_id;
100 close get_assignment_id;
101
102 open get_wa_fed(l_assignment_id);
103 fetch get_wa_fed into l_wa_fed;
104 close get_wa_fed;
105
106 open get_fsc_fed(l_assignment_id);
107 fetch get_fsc_fed into l_filing_status_code;
108 close get_fsc_fed;
109
110 open get_fitexempt_fed(l_assignment_id);
111 fetch get_fitexempt_fed into l_fit_exempt;
112 close get_fitexempt_fed;
113
114 open csr_chk_student_status;
115 fetch csr_chk_student_status into l_student,l_business_apprentice;
116 if csr_chk_student_status%FOUND
117 then l_student_flag :='Yes';
118 end if;
119 close csr_chk_student_status;
120
121 if l_information_type like 'PER_US_ADDITIONAL_DETAILS'
122 and l_pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
123 and l_pei_information5 like 'N'
124 and l_pei_information9 not in ('US')
125 then
126 if l_wa_fed > 1 and not ((l_student_flag ='Yes' and l_pei_information9 ='IN') or l_pei_information9 in ('CA','MX','KS'))
127 then
128 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_ALLOWANCES');
129 fnd_message.raise_error;
130 end if;
131 if l_filing_status_code <> '01'
132 then
133 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_FILING_STATUS');
134 fnd_message.raise_error;
135 end if;
136 /* Bug 6794488
137 if (l_fit_exempt = 'Y')
138 then
139 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_EXEMPTIONS');
140 fnd_message.raise_error;
141 end if; */
142 end if;
143
144 hr_utility.trace('Leaving PAY_US_EXTRA_PER_INFO_LEG_HOOK.person_check_nra_status_create');
145
146 end person_check_nra_status_create;
147
148
149 procedure person_check_nra_status_update(P_PERSON_EXTRA_INFO_ID in NUMBER
150 ,P_PEI_INFORMATION_CATEGORY in VARCHAR2
151 ,P_PEI_INFORMATION1 in VARCHAR2
152 ,P_PEI_INFORMATION2 in VARCHAR2
153 ,P_PEI_INFORMATION5 in VARCHAR2
154 ,P_PEI_INFORMATION9 in VARCHAR2)
155
156 is
157 l_filing_status_code varchar2(2);
158 l_person_id per_all_people_f.person_id%TYPE;
159 l_information_type per_people_extra_info.information_type%TYPE;/* stores per us additional details*/
160 l_pei_information_category per_people_extra_info.pei_information_category%TYPE;/* stores per us additional details*/
161 l_information_type_1 per_people_extra_info.information_type%TYPE;/* stores per us student details*/
162 l_pei_information_category_1 per_people_extra_info.pei_information_category%TYPE;/* stores per us student details*/
163 l_pei_information1 per_people_extra_info.pei_information1%TYPE;
164 l_pei_information2 per_people_extra_info.pei_information2%TYPE;
165 l_pei_information5 per_people_extra_info.pei_information5%TYPE;
166 l_pei_information9 per_people_extra_info.pei_information9%TYPE;
167 l_pei_information9_1 per_people_extra_info.pei_information9%TYPE;
168 l_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE;
169 l_withholding_allowances pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
170 l_wa_fed pay_us_emp_fed_tax_rules_f.withholding_allowances%TYPE;
171 l_fit_exempt pay_us_emp_fed_tax_rules_f.fit_exempt%TYPE;
172 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
173 l_student_flag varchar2(3);
174 l_student per_people_extra_info.pei_information1%TYPE;
175 l_business_apprentice per_people_extra_info.pei_information2%TYPE;
176
177 /* This cursor checks whether any information exists for the employee that he is either a student or business apprentice*/
178 cursor csr_chk_student_status is
179 select pei_information1,pei_information2
180 from per_people_extra_info
181 where person_id=l_person_id
182 and information_type like 'PER_US_ADDITIONAL_DETAILS'
183 and pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
184 and (pei_information1 = 'Y' or pei_information2 = 'Y');
185
186 CURSOR get_assignment_id(p_person_id NUMBER)
187 IS
188 SELECT distinct assignment_id
189 FROM per_all_assignments_f paf ,per_all_people_f ppf
190 WHERE paf.person_id=ppf.person_id
191 and ppf.person_id=p_person_id
192 and paf.primary_flag='Y';
193
194
195 CURSOR get_person_id(p_person_extra_info_id NUMBER)
196 IS
197 SELECT person_id
198 FROM per_people_extra_info pei
199 WHERE pei.person_extra_info_id=p_person_extra_info_id;
200
201
202 CURSOR get_information_type(p_person_extra_info_id NUMBER)
203 IS
204 SELECT information_type
205 FROM per_people_extra_info pei
206 WHERE pei.person_extra_info_id=p_person_extra_info_id;
207
208 CURSOR get_wa_fed(p_assignment_id NUMBER)
209 IS
210 SELECT WITHHOLDING_ALLOWANCES
211 FROM pay_us_emp_fed_tax_rules_f fetr
212 WHERE fetr.assignment_id=p_assignment_id
213 and fetr.effective_start_date <=(select sysdate from dual)
214 and fetr.effective_end_date >=(select sysdate from dual);
215
216 CURSOR get_fsc_fed(p_assignment_id NUMBER)
217 IS
218 SELECT FILING_STATUS_CODE
219 FROM pay_us_emp_fed_tax_rules_f fetr
220 WHERE fetr.assignment_id=p_assignment_id
221 and fetr.effective_start_date <=(select sysdate from dual)
222 and fetr.effective_end_date >=(select sysdate from dual);
223
224 CURSOR get_fitexempt_fed(p_assignment_id NUMBER)
225 IS
226 SELECT FIT_EXEMPT
227 FROM pay_us_emp_fed_tax_rules_f fetr
228 WHERE fetr.assignment_id=p_assignment_id
229 and fetr.effective_start_date <=(select sysdate from dual)
230 and fetr.effective_end_date >=(select sysdate from dual);
231
232
233
234 begin
235 hr_utility.trace('Entering PAY_US_EXTRA_PER_INFO_LEG_HOOK.person_check_nra_status_update');
236
237 l_person_extra_info_id := P_PERSON_EXTRA_INFO_ID;
238 l_pei_information_category := P_PEI_INFORMATION_CATEGORY;
239 l_pei_information1 := P_PEI_INFORMATION1; /*student status*/
240 l_pei_information2 := P_PEI_INFORMATION2; /*business apprenctice */
241 l_pei_information5 := P_PEI_INFORMATION5; /*residentioal status*/
242 l_pei_information9 := P_PEI_INFORMATION9; /*country information*/
243 l_student_flag :='No'; /* assuming the person is not a student */
244
245
246 open get_person_id(P_PERSON_EXTRA_INFO_ID);
247 fetch get_person_id into l_person_id;
248 close get_person_id;
249
250 open get_information_type(P_PERSON_EXTRA_INFO_ID);
251 fetch get_information_type into l_information_type;
252 close get_information_type;
253
254 open get_assignment_id(l_person_id);
255 fetch get_assignment_id into l_assignment_id;
256 close get_assignment_id;
257
258 open get_wa_fed(l_assignment_id);
259 fetch get_wa_fed into l_wa_fed;
260 close get_wa_fed;
261
262 open get_fsc_fed(l_assignment_id);
263 fetch get_fsc_fed into l_filing_status_code;
264 close get_fsc_fed;
265
266 open get_fitexempt_fed(l_assignment_id);
267 fetch get_fitexempt_fed into l_fit_exempt;
268 close get_fitexempt_fed;
269
270 /*
271 hr_utility.trace('l_person_id '||l_person_id);
272 hr_utility.trace('l_information_type '||l_information_type);
273 hr_utility.trace('l_assignment_id '||l_assignment_id);
274 hr_utility.trace('l_wa_fed '||l_wa_fed);
275 hr_utility.trace('l_filing_status_code '||l_filing_status_code);
276 hr_utility.trace('l_fit_exempt '||l_fit_exempt);
277 */
278
279 /* if person is a student or a business appprentice his records are selected */
280 open csr_chk_student_status;
281 fetch csr_chk_student_status into l_student,l_business_apprentice;
282 if csr_chk_student_status%FOUND /* checking setting the student flag to yes*/
283 then
284 l_student_flag :='Yes';
285 l_information_type_1 :='PER_US_ADDITIONAL_DETAILS';
286 l_pei_information_category_1 :='PER_US_ADDITIONAL_DETAILS';
287
288 select pei_information9 into l_pei_information9_1 /* select find the country of the persosn */
289 from per_people_extra_info where person_id=l_person_id
290 and information_type like 'PER_US_ADDITIONAL_DETAILS'
291 and pei_information_category like 'PER_US_ADDITIONAL_DETAILS';
292 end if;
293 close csr_chk_student_status;
294
295
296 l_pei_information1:=l_student; /** will be 'y' if the person is a student or a business apprentce */
297 l_pei_information2:=l_business_apprentice; /* other wise wil be 'N' */
298
299 if l_information_type_1 like 'PER_US_ADDITIONAL_DETAILS'
300 and l_pei_information_category_1 like 'PER_US_ADDITIONAL_DETAILS'
301 and
302 (nvl(P_pei_information1,'N')='N' and l_student = 'Y' and nvl(P_pei_information2,'N')='N')
303 or
304 (nvl(P_pei_information2,'N')='N' and l_business_apprentice ='Y' and nvl(P_pei_information1,'N')='N')
305 then
306 if l_wa_fed > 1 and l_pei_information9_1 ='IN'
307 then fnd_message.set_name('PAY', 'PAY_US_CHK_W4_ALLOWANCES');
308 fnd_message.raise_error;
309 end if;
310 end if;
311
312 /*
313 hr_utility.trace('l_student_flag '||l_student_flag);
314 hr_utility.trace('l_information_type '||l_information_type);
315 hr_utility.trace('l_pei_information_category '||l_pei_information_category);
316 hr_utility.trace('l_pei_information5 '||l_pei_information5);
317 hr_utility.trace('l_pei_information9 '||l_pei_information9);
318 hr_utility.trace('l_filing_status_code '||l_filing_status_code);
319 hr_utility.trace('l_fit_exempt '||l_fit_exempt);
320 */
321
322 if l_information_type like 'PER_US_ADDITIONAL_DETAILS'
323 and l_pei_information_category like 'PER_US_ADDITIONAL_DETAILS'
324 and l_pei_information5 like 'N'
325 and l_pei_information9 not in ('US')
326 then
327 if l_wa_fed > 1 and not ((l_student_flag ='Yes' and l_pei_information9 ='IN') or l_pei_information9 in ('CA','MX','KS'))
328 then
329 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_ALLOWANCES');
330 fnd_message.raise_error;
331 end if;
332 if l_filing_status_code <> '01'
333 then
334 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_FILING_STATUS');
335 fnd_message.raise_error;
336 end if;
337 /* Bug 6794488
338 if (l_fit_exempt = 'Y')
339 then
340 fnd_message.set_name('PAY', 'PAY_US_CHK_W4_EXEMPTIONS');
341 fnd_message.raise_error;
342 end if; */
343 end if;
344
345 hr_utility.trace('Leaving PAY_US_EXTRA_PER_INFO_LEG_HOOK.person_check_nra_status_update');
346
347 end person_check_nra_status_update;
348
349 end PAY_US_EXTRA_PER_INFO_LEG_HOOK ;
[Home] [Help]