[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_TAX_VAL
Source
1 Package body PAY_IE_TAX_VAL as
2 /* $Header: pyietxvl.pkb 120.20.12010000.1 2008/07/27 22:51:50 appldev ship $ */
3
4 g_validate_count NUMBER := 0;
5 TYPE error_rec is record
6 (p_pps_number VARCHAR2(11),
7 p_works_number VARCHAR2(12),
8 p_err_msg VARCHAR2(1000));
9
10 TYPE err_tab IS TABLE OF error_rec INDEX BY BINARY_INTEGER;
11
12 l_err_tab err_tab;
13 err_cnt number := 1;
14
15 PROCEDURE getparam(
16 errbuf OUT NOCOPY VARCHAR2
17 , retcode OUT NOCOPY VARCHAR2
18 , p_data_file IN VARCHAR2
19 , p_employer_number IN VARCHAR2
20 , p_tax_year IN NUMBER
21 , p_validate_mode IN VARCHAR2 :='IE_VALIDATE'
22 , p_payroll_id IN NUMBER := NULL)
23 IS
24 Begin
25 Null;
26 /* Dummy Procedure created to accept all the parameters once and share them
27 later in the stages of request set */
28 retcode := 0;
29 end;
30
31 PROCEDURE count_validation(
32 errbuf OUT NOCOPY VARCHAR2
33 , retcode OUT NOCOPY VARCHAR2
34 , p_employer_number IN VARCHAR2
35 , p_tax_year IN pay_ie_tax_header_interface.tax_year%TYPE) IS
36
37 -- Cursor to get the total values form body
38 CURSOR c_body
39 IS
40 SELECT COUNT(PBS.MTH_TAX_CREDIT) count_mth_taxcredit
41 , SUM (NVL(PBS.MTH_RATE_CUTOFF,0)) sum_mth_rate_cutoff
42 , SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
43 , SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
44 , SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
45 FROM PAY_IE_TAX_HEADER_INTERFACE phs
46 , PAY_IE_TAX_BODY_INTERFACE pbs
47 WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
48 AND PHS.TAX_YEAR = p_tax_year
49 AND PBS.EMPLOYER_NUMBER = p_employer_number;
50
51 -- Cursor to get the total values from trailer table
52 CURSOR c_trailer
53 IS
54 SELECT PTS.RECORD_NO count_emp_recno
55 , NVL(PTS.TOTAL_MTH_RATE_CUTOFF,0) total_mth_cutoff
56 , NVL(PTS.TOTAL_WK_RATE_CUTOFF,0) total_wk_cutoff
57 , NVL(PTS.TOTAL_MTH_TAX_CREDIT,0) total_mth_credit
58 , NVL(PTS.TOTAL_WK_TAX_CREDIT,0) total_wk_credit
59 FROM PAY_IE_TAX_HEADER_INTERFACE phs
60 , PAY_IE_TAX_TRAILER_INTERFACE pts
61 WHERE PTS.EMPLOYER_NUMBER = p_employer_number
62 AND PHS.TAX_YEAR = p_tax_year;
63
64 l_error_stack VARCHAR2 (2000);
65 l_error VARCHAR2 (80);
66 l_request_id NUMBER;
67 l_count_mth_taxcredit NUMBER ;
68 l_sum_mth_rate_cutoff NUMBER;
69 l_sum_wk_rate_cutoff NUMBER;
70 l_sum_mth_tax_credit NUMBER;
71 l_sum_wk_tax_credit NUMBER;
72 l_count_emp_recno NUMBER;
73 l_total_mth_cutoff NUMBER;
74 l_total_wk_cutoff NUMBER;
75 l_total_mth_credit NUMBER;
76 l_total_wk_credit NUMBER;
77
78 unequal_value EXCEPTION;
79 BodyRec c_body%rowtype;
80 TrailRec c_trailer%rowtype;
81
82 BEGIN
83 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
84
85
86 Begin
87 Delete from pay_ie_tax_error;
88 if sql%rowcount > 0 then
89 commit;
90 end if;
91 exception
92 when others then
93 FND_FILE.PUT_LINE(fnd_file.log,'Error occured while deleting exisiting rows in
94 PAY_IE_TAX_ERROR table');
95 end;
96
97 UPDATE PAY_IE_TAX_BODY_INTERFACE
98 SET EMPLOYER_NUMBER =
99 (SELECT EMPLOYER_NUMBER
100 FROM PAY_IE_TAX_HEADER_INTERFACE);
101
102 OPEN c_body;
103
104 FETCH c_body into BodyRec;
105 IF (c_body%NOTFOUND) THEN
106 RAISE NO_DATA_FOUND;
107 END IF;
108 l_count_mth_taxcredit := BodyRec.count_mth_taxcredit;
109 l_sum_mth_rate_cutoff := BodyRec.sum_mth_rate_cutoff;
110 l_sum_wk_rate_cutoff := BodyRec.sum_wk_rate_cutoff;
111 l_sum_mth_tax_credit := BodyRec.sum_mth_tax_credit;
112 l_sum_wk_tax_credit := BodyRec.sum_wk_tax_credit;
113
114
115 OPEN c_trailer;
116
117 FETCH c_trailer into TrailRec;
118 IF (c_trailer%NOTFOUND) THEN
119 RAISE NO_DATA_FOUND;
120 END IF;
121 l_count_emp_recno := TrailRec.count_emp_recno;
122 l_total_mth_cutoff := TrailRec.total_mth_cutoff;
123 l_total_wk_cutoff := TrailRec.total_wk_cutoff;
124 l_total_mth_credit := TrailRec.total_mth_credit;
125 l_total_wk_credit := TrailRec.total_wk_credit;
126
127 IF (BodyRec.count_mth_taxcredit = TrailRec.count_emp_recno
128 AND BodyRec.sum_mth_rate_cutoff = TrailRec.total_mth_cutoff
129 AND BodyRec.sum_wk_rate_cutoff = TrailRec.total_wk_cutoff
130 AND BodyRec.sum_mth_tax_credit = TrailRec.total_mth_credit
131 AND BodyRec.sum_wk_tax_credit = TrailRec.total_wk_credit)
132 THEN
133 retcode := 0;
134 fnd_file.put_line( fnd_file.log, 'FND - CONC-COMPLETION TEXT:NORMAL');
135
136 update pay_ie_tax_body_interface
137 set process_flag = 'Y'
138 where EMPLOYER_NUMBER = p_employer_number;
139 Commit;
140
141 ELSE
142 RAISE unequal_value;
143 END IF;
144
145 close c_body;
146 close c_trailer;
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 l_error := SQLERRM;
151 retcode := 2;
152 FND_FILE.PUT_LINE(fnd_file.log,'No data found');
153 WHEN unequal_value THEN
154 errbuf := fnd_message.get;
155 l_error_stack := errbuf;
156 l_error := SQLERRM;
157 retcode := 2;
158 IF l_count_mth_taxcredit <> l_count_emp_recno THEN
159 FND_FILE.NEW_LINE(fnd_file.log, 1);
160 FND_FILE.PUT_LINE(fnd_file.log,
161 'The total number of record in pay_ie_tax_body_interface is '
162 || TO_CHAR (l_count_mth_taxcredit));
163 FND_FILE.PUT_LINE (fnd_file.log,
164 'The value in pay_ie_tax_trailer_interface.record_no is '
165 || TO_CHAR(l_count_emp_recno));
166 FND_FILE.PUT_LINE(fnd_file.log,
167 'Error - Retcode = 2, total number of record in pay_ie_tax_body_interface');
168 FND_FILE.PUT_LINE(fnd_file.log,
169 'does not match the value in pay_ie_tax_trailer_interface.record_no');
170 END IF;
171
172 IF l_sum_mth_rate_cutoff <> l_total_mth_cutoff THEN
173 FND_FILE.NEW_LINE(fnd_file.log, 1);
174 FND_FILE.PUT_LINE(fnd_file.log,
175 'The sum of pay_ie_tax_body_interface.mth_rate_cutoff is '
176 || TO_CHAR (l_sum_mth_rate_cutoff));
177 FND_FILE.PUT_LINE (fnd_file.log,
178 'The total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff is '
179 || TO_CHAR(l_total_mth_cutoff));
180 FND_FILE.PUT_LINE(fnd_file.log,
181 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_rate_cutoff');
182 FND_FILE.PUT_LINE(fnd_file.log,
183 'does not match the total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff');
184 END IF;
185 IF l_sum_wk_rate_cutoff <> l_total_wk_cutoff THEN
186 FND_FILE.NEW_LINE(fnd_file.log, 1);
187 FND_FILE.PUT_LINE(fnd_file.log,
188 'The sum of pay_ie_tax_body_interface.wk_rate_cutoff is '
189 || TO_CHAR(l_sum_wk_rate_cutoff));
190 FND_FILE.PUT_LINE(fnd_file.log,
191 'The total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff is '
192 || TO_CHAR(l_total_wk_cutoff));
193 FND_FILE.PUT_LINE(fnd_file.log,
194 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.wk_rate_cutoff');
195 FND_FILE.PUT_LINE(fnd_file.log,
196 'does not match the the total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff');
197 END IF;
198 IF l_sum_mth_tax_credit <> l_total_mth_credit THEN
199 FND_FILE.NEW_LINE(fnd_file.log, 1);
200 FND_FILE.PUT_LINE(fnd_file.log,
201 'The sum of pay_ie_tax_body_interface.mth_tax_credit is '
202 || TO_CHAR(l_sum_mth_tax_credit));
203 FND_FILE.PUT_LINE(fnd_file.log,
204 'The total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit is '
205 || TO_CHAR(l_total_mth_credit));
206 FND_FILE.PUT_LINE(fnd_file.log,
207 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_tax_credit');
208 FND_FILE.PUT_LINE(fnd_file.log,
209 'does not match the total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit');
210 END IF;
211 IF BodyRec.sum_wk_tax_credit <> TrailRec.total_wk_credit THEN
212 FND_FILE.NEW_LINE(fnd_file.log, 1);
213 FND_FILE.PUT_LINE(fnd_file.log,
214 'The sum of pay_ie_tax_body_interface.wk_tax_credit is '
215 || TO_CHAR(l_sum_wk_tax_credit));
216 FND_FILE.PUT_LINE(fnd_file.log,
217 'The total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit is '
218 || TO_CHAR(l_total_wk_credit));
219 FND_FILE.PUT_LINE(fnd_file.log,
220 'Error - Retcode = 2 because the sum of pay_ie_tax_body_interface.wk_tax_credit');
221 FND_FILE.PUT_LINE(fnd_file.log,
222 'does not match the total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit');
223 END IF;
224
225 INSERT INTO pay_ie_tax_error ( pps_number
226 , employee_number
227 , full_name
228 , payroll_name
229 , error_stack_message
230 , error_message
231 , request_id
232 , error_date)
233 VALUES (0
234 , NULL
235 , NULL
236 , NULL
237 , l_error_stack
238 , l_error
239 , l_request_id
240 , sysdate);
241 COMMIT;
242
243 WHEN OTHERS THEN
244 errbuf := fnd_message.get;
245 l_error_stack := errbuf;
246 l_error := SQLERRM;
247 retcode := 2;
248 FND_FILE.PUT_LINE (fnd_file.log, 'Error raised in loading data into one or all of the follo
249 wing tables: pay_ie_tax_header_interface, pay_ie_tax_body_interface, pay_ie_tax_trailer_interface');
250
251 INSERT INTO pay_ie_tax_error ( pps_number
252 , employee_number
253 , full_name
254 , payroll_name
255 , error_stack_message
256 , error_message
257 , request_id
258 , error_date)
259 VALUES (0
260 , NULL
261 , NULL
262 , NULL
263 , l_error_stack
264 , l_error
265 , l_request_id
266 , sysdate);
267 COMMIT;
268 END count_validation;
269
270 -- Procedure to validate every row from the interface table and update
271 -- PAY_IE_PAYE_DETAILS_F table if required.
272
273 PROCEDURE valinsupd (
274 errbuf OUT NOCOPY VARCHAR2
275 , retcode OUT NOCOPY VARCHAR2
276 , p_employer_number IN VARCHAR2
277 , p_tax_year IN NUMBER
278 , p_validate_mode IN VARCHAR2 :='IE_VALIDATE'
279 , p_payroll_id IN NUMBER := NULL
280 ) AS
281 --bug 6376140
282 --BUG 6652299 ADDED DISTINCT KEY WORD TO THE CURSOR C_NO_OF_ASSG
283 /*Declare cursor to retrieve no.of assignments from person
284 and interface tables based on input parameters*/
285 cursor c_no_of_assg IS
286 select per.person_id person_id
287 ,per.NATIONAL_IDENTIFIER pps_number
288 ,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
289 from per_all_assignments_f asg,
290 per_all_people_f per,
291 pay_all_payrolls_f pay,
292 pay_ie_tax_body_interface tbi,
293 per_periods_of_service pps
294 where per.national_identifier = tbi.pps_number
295 --AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
296 --AND asg.payroll_id = pay.payroll_id
297 AND per.person_id = asg.person_id
298 AND tbi.process_flag = 'Y'
299 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
300 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
301 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
302 AND per.effective_end_date >= trunc(tbi.cert_start_date)
303 and asg.period_of_service_id=pps.period_of_service_id
304 and pps.person_id=per.person_id
305 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
306 and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
307 --AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
308 --AND pay.effective_end_date >= trunc(tbi.cert_start_date)
309 group by per.person_id,per.NATIONAL_IDENTIFIER;
310
311 /* Cursor check_pps(p_pps_no varchar) is
312 Select 1 from per_all_people_f per
313 ,pay_ie_tax_body_interface tbi
314 Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
315 AND per.effective_end_date >= trunc(tbi.cert_start_date)
316 and per.national_identifier=p_pps_no
317 AND tbi.process_flag = 'Y'
318 and tbi.pps_number=per.national_identifier;
319 p_check_pps check_pps%rowtype; */
320
321 /*Declare cursor to retrieve all employee details from payroll
322 and interface tables based on input parameters for a multiple assignments*/
323 CURSOR c_pay(p_pps_number varchar) IS
324 SELECT distinct per.employee_number employee_no_hr
325 , per.national_identifier pps_number_hr
326 , per.last_name last_name_hr
327 , per.first_name first_name_hr
328 , asg.assignment_id assignment_id
329 --, asg.effective_start_date effective_start_date
330 , hoi.org_information1 tax_district
331 , pay.payroll_name payroll_name_hr
332 , pay.payroll_id payroll_id -- 4878630
333 --, ppd.paye_details_id paye_details_id --4878630
334 --, ppd.object_version_number object_version_no --4878630
335 --, ppd.effective_start_date ppd_effective_start_date
336 , tbi.pps_number pps_number_int
337 , asg.assignment_number employee_no_int --5724436
338 , tbi.first_name first_name_int
339 , tbi.last_name last_name_int
340 , tbi.cert_start_date cert_start_date
341 , tbi.cert_end_date cert_end_date
342 , tbi.cert_date cert_date
343 , tbi.wk_tax_credit/100 wk_tax_credit
344 , tbi.mth_tax_credit/100 mth_tax_credit
345 , tbi.wk_rate_cutoff/100 wk_rate_cutoff
346 , tbi.mth_rate_cutoff/100 mth_rate_cutoff
347 -- Bug Fix 3500192
348 , tbi.wk_mth_indicator wk_mth_indicator
349 -- Bug Fix 4618981
350 , tbi.exemption_indicator exemption_indicator
351 , tbi.tot_tax_to_date/100 tot_tax_to_date
352 , tbi.tot_pay_to_date/100 tot_pay_to_date
353 , tbi.std_rate_of_tax std_rate_of_tax
354 , tbi.higher_rate_of_tax higher_rate_of_tax
355 FROM hr_organization_information hoi
356 , hr_organization_units hou
357 , per_all_assignments_f asg
358 , per_all_people_f per
359 , pay_all_payrolls_f pay
360 --, pay_ie_paye_details_f ppd
361 , pay_ie_tax_body_interface tbi
362 , pay_ie_tax_header_interface thi
363 WHERE per.person_id = asg.person_id
364 AND per.national_identifier = tbi.pps_number
365 AND asg.business_group_id = hou.business_group_id
366 AND hou.organization_id = hoi.organization_id
367 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
368 AND hoi.org_information2 = p_employer_number
369 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
370 AND asg.payroll_id = pay.payroll_id
371 --AND asg.assignment_id = ppd.assignment_id
372 AND per.national_identifier = p_pps_number
373 -- Bug Fix 3500192
374 -- added for multiple assignment issue 5894942
375 AND asg.assignment_number = tbi.works_number
376 AND thi.employer_number = p_employer_number
377 AND thi.tax_year = p_tax_year
378 AND tbi.employer_number = thi.employer_number
379 AND tbi.process_flag = 'Y'
380 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
381 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
382 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
383 AND per.effective_end_date >= trunc(tbi.cert_start_date)
384 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
385 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
386 --bug 6376140
387 /*Declare cursor to retrieve all employee details from payroll
388 and interface tables based on input parameters for a single assignment*/
389 CURSOR c_pay1(p_pps_number varchar) IS
390 SELECT distinct per.employee_number employee_no_hr
391 , per.national_identifier pps_number_hr
392 , per.last_name last_name_hr
393 , per.first_name first_name_hr
394 , asg.assignment_id assignment_id
395 --, asg.effective_start_date effective_start_date
396 , hoi.org_information1 tax_district
397 , pay.payroll_name payroll_name_hr
398 , pay.payroll_id payroll_id -- 4878630
399 --, ppd.paye_details_id paye_details_id --4878630
400 --, ppd.object_version_number object_version_no --4878630
401 --, ppd.effective_start_date ppd_effective_start_date
402 , tbi.pps_number pps_number_int
403 , asg.assignment_number employee_no_int --5724436
404 , tbi.first_name first_name_int
405 , tbi.last_name last_name_int
406 , tbi.cert_start_date cert_start_date
407 , tbi.cert_end_date cert_end_date
408 , tbi.cert_date cert_date
409 , tbi.wk_tax_credit/100 wk_tax_credit
410 , tbi.mth_tax_credit/100 mth_tax_credit
411 , tbi.wk_rate_cutoff/100 wk_rate_cutoff
412 , tbi.mth_rate_cutoff/100 mth_rate_cutoff
413 -- Bug Fix 3500192
414 , tbi.wk_mth_indicator wk_mth_indicator
415 -- Bug Fix 4618981
416 , tbi.exemption_indicator exemption_indicator
417 , tbi.tot_tax_to_date/100 tot_tax_to_date
418 , tbi.tot_pay_to_date/100 tot_pay_to_date
419 , tbi.std_rate_of_tax std_rate_of_tax
420 , tbi.higher_rate_of_tax higher_rate_of_tax
421 FROM hr_organization_information hoi
422 , hr_organization_units hou
423 , per_all_assignments_f asg
424 , per_all_people_f per
425 , pay_all_payrolls_f pay
426 --, pay_ie_paye_details_f ppd
427 , pay_ie_tax_body_interface tbi
428 , pay_ie_tax_header_interface thi,
429 per_periods_of_service pps
430 WHERE per.person_id = asg.person_id
431 AND per.national_identifier = tbi.pps_number
432 AND asg.business_group_id = hou.business_group_id
433 AND hou.organization_id = hoi.organization_id
434 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
435 AND hoi.org_information2 = p_employer_number
436 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
437 AND asg.payroll_id = pay.payroll_id
438 --AND asg.assignment_id = ppd.assignment_id
439 AND per.national_identifier = p_pps_number
440 -- Bug Fix 3500192
441 -- added for multiple assignment issue 5894942
442 --AND asg.assignment_number = tbi.works_number
443 AND thi.employer_number = p_employer_number
444 AND thi.tax_year = p_tax_year
445 AND tbi.employer_number = thi.employer_number
446 AND tbi.process_flag = 'Y'
447 and asg.period_of_service_id=pps.period_of_service_id
448 and pps.person_id=per.person_id
449 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
450 and pps.period_of_service_id in (select max(pps1.period_of_service_id) from per_periods_of_service pps1 where pps1.person_id=pps.person_id and pps1.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy'))
451 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
452 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
453 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
454 AND per.effective_end_date >= trunc(tbi.cert_start_date)
455 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
456 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
457
458 -- cursor get the skipped assignments. Assignments that didnt get processed
459 -- in the process
460 --bug 6376140 cursor modified for processing single assg
461 /* cursor csr_skipped_asg is
462 select pps_number, works_number from pay_ie_tax_body_interface
463 minus
464 SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
465 FROM hr_organization_information hoi
466 , hr_organization_units hou
467 , per_all_assignments_f asg
468 , per_all_people_f per
469 , pay_all_payrolls_f pay
470 --, pay_ie_paye_details_f ppd
471 , pay_ie_tax_body_interface tbi
472 , pay_ie_tax_header_interface thi
473 WHERE per.person_id = asg.person_id
474 AND asg.business_group_id = hou.business_group_id
475 AND hou.organization_id = hoi.organization_id
476 AND hoi.org_information_context = 'IE_EMPLOYER_INFO'
477 AND hoi.org_information2 = p_employer_number
478 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
479 AND asg.payroll_id = pay.payroll_id
480 AND per.national_identifier = tbi.pps_number
481 AND asg.assignment_number = tbi.works_number
482 AND thi.employer_number = p_employer_number
483 AND thi.tax_year = p_tax_year
484 AND tbi.employer_number = thi.employer_number
485 AND tbi.process_flag = 'Y'
486 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
487 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
488 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
489 AND per.effective_end_date >= trunc(tbi.cert_start_date)
490 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
491 AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
492
493 cursor csr_skipped_assignments is
494 select distinct pps_number pps_number,works_number,last_name,first_name
495 from pay_ie_tax_body_interface;
496
497 /* check to see if any single paye details exists */
498 cursor get_paye_details (p_assignment_id number) is
499 select count(*) from pay_ie_paye_details_f where
500 assignment_id = p_assignment_id;
501
502 -- For Bug 5724436
503 -- Cursor to get the max assignment action id, to fetch the P45 details.
504 cursor get_p45_details (p_assignment_id number) is
505 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa
506 from pay_assignment_actions paa,
507 pay_payroll_actions ppa
508 where ppa.payroll_action_id = paa.payroll_action_id
509 and paa.assignment_id = p_assignment_id
510 and to_number(to_char(ppa.effective_date,'YYYY')) = p_tax_year;
511
512 -- Cursor to fetch existing PAYE details.
513 cursor c_get_paye_details(p_assignment_id number
514 ,p_ppsn varchar2
515 ,p_cert_start_date date
516 ,p_assignment_number varchar2) is
517 select distinct ppd.*
518 from per_all_people_f papf,
519 per_all_assignments_f paaf,
520 pay_ie_paye_details_f ppd
521 where papf.national_identifier = p_ppsn
522 and papf.person_id = paaf.person_id
523 and paaf.assignment_id = p_assignment_id
524 and paaf.assignment_id = ppd.assignment_id
525 and paaf.assignment_number = p_assignment_number
526 AND paaf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
527 AND paaf.effective_end_date >= trunc(p_cert_start_date)
528 AND papf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
529 AND papf.effective_end_date >= trunc(p_cert_start_date)
530 AND ppd.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
531 AND ppd.effective_end_date >= trunc(p_cert_start_date);
532
533 -- Cursor to see if payroll exists for an assignment.
534 CURSOR csr_pay_freq (p_assignment_id NUMBER,
535 p_effective_date DATE) IS
536 SELECT pp.period_type
537 FROM pay_payrolls_f pp, per_assignments_f pa
538 WHERE pa.assignment_id = p_assignment_id
539 AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
540 AND pp.payroll_id = pa.payroll_id
541 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
542
543 pay_freq_rec csr_pay_freq%ROWTYPE;
544
545 -- cursor get values from globals bug 5766334
546 cursor csr_get_global_value(p_global_name varchar2,
547 p_cert_date date) is
548 select global_value
549 from ff_globals_f
550 where global_name = p_global_name
551 and p_cert_date between effective_start_date and effective_end_date;
552
553 l_tax_rate_exempt ff_globals_f.global_value%TYPE;
554 l_tax_rate_high ff_globals_f.global_value%TYPE;
555 --bug 6376140
556 --follw variables are added
557 r_pay c_pay%rowtype;
558 TYPE t_pps_number IS TABLE OF varchar2(30) INDEX BY Binary_Integer;
559 l_pps_number t_pps_number;
560 ppsno_cnt number :=0;
561 flag char :='N';
562 --end of bug 6376140
563 -- End bug 5766334
564
565 --Declare variables
566 l_error VARCHAR2(2000);
567 l_error_stack VARCHAR2(2000) := NULL;
568 l_request_id NUMBER;
569 l_program_application_id NUMBER;
570 l_program_id NUMBER;
571 l_comm_period_no NUMBER;
572 l_pps_number_hr VARCHAR2(9);
573 l_employee_number_hr per_all_people_F.employee_number%TYPE; -- bug 5766372
574 l_last_name_hr per_people_f.last_name%TYPE;
575 l_first_name_hr per_people_f.first_name%TYPE;
576 l_last_name_int VARCHAR2(20);
577 l_first_name_int VARCHAR2(20);
578 l_payroll_name_hr VARCHAR2(80);
579 l_tax_district NUMBER;
580 l_pps_number_int VARCHAR2(9);
581 l_employee_number_int per_all_assignments_f.assignment_number%TYPE; -- bug 5766372
582 l_validate BOOLEAN := FALSE;
583 -- Bug Fix 3500192
584 l_datetrack_mode VARCHAR2(12);
585 l_tax_basis pay_ie_paye_details_f.tax_basis%TYPE; -- bug 5766372
586 l_header_count NUMBER := 0;
587 l_record_count NUMBER := 0;
588 l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%TYPE;
589 l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%TYPE;
590
591 --Declare output parameters from api row handlersb
592 l_ins_paye_details_id NUMBER;
593 l_ins_object_version_no NUMBER;
594 l_ins_effective_start_date DATE;
595 l_ins_effective_end_date DATE;
596 l_upd_effective_start_date DATE;
597 l_upd_effective_end_date DATE;
598 l_flag number;
599
600 -- Bug Fix 3500192
601 -- name_not_equal EXCEPTION;
602 -- same_day EXCEPTION;
603 future_day EXCEPTION;
604 std_rate_of_tax_is_null EXCEPTION;
605 higher_rate_of_tax_is_null EXCEPTION;
606 exemption_is_null EXCEPTION;
607 exemption_mismatch EXCEPTION;
608 normal_tax_mismatch EXCEPTION;
609 pay_to_date EXCEPTION;
610 l_paye_count NUMBER(3);
611 o_paye_details_id NUMBER;
612 o_ovn NUMBER;
613 o_effective_start_date DATE;
614 o_effective_end_date DATE;
615 l_effective_date DATE; -- Bug 6929566
616
617 l_tax_to_date NUMBER;
618 l_pay_to_date NUMBER;
619 l_max_action_id NUMBER := 0;
620 r_paye_details c_get_paye_details%ROWTYPE;
621 r_empty_details c_get_paye_details%ROWTYPE;
622
623 BEGIN
624 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
625 retcode := 1;
626 -- Bug 5724436, the audit report will be called only in mode="Validate"
627 IF p_validate_mode <> 'IE_VALIDATE' THEN
628 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11, ' ')||lpad('Works Number',15,' ')||lpad('Status',30,' ')); --4878630
629 fnd_file.put_line(fnd_file.output,lpad('----------',11, ' ')||lpad('------------',15,' ')||lpad('------',30,' '));
630 ELSE
631 -- bug 5724436
632 -- This is called only once to set the report fields.
633 fnd_file.put_line(fnd_file.output,'Index');
634 fnd_file.put_line(fnd_file.output,'I :- Week1/Month1 Indicator');
635 fnd_file.put_line(fnd_file.output,'F :- Exemption Flag');
636 fnd_file.put_line(fnd_file.output,'Tax1 :- Standard Rate of Tax');
637 fnd_file.put_line(fnd_file.output,'Tax2 :- Higher Rate of Tax');
638 fnd_file.put_line(fnd_file.output,' ');
639
640 fnd_file.put_line(fnd_file.output,lpad('PAYE Details in Oracle Payroll',70,' ')||lpad('PAYE Details from Revenue',105,' '));
641 fnd_file.put_line(fnd_file.output,lpad('==============================',70,' ')||lpad('=========================',105,' '));
642 fnd_file.put_line(fnd_file.output,' ');
643 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11,' ')
644 -- for previous PAYE Details
645 || lpad('Works',13,' ')
646 || lpad('Last Name',16,' ')
647 || lpad('I',3,' ')||lpad('F',3,' ')
648 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
649 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
650 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
651 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
652 || lpad('Tax2',6,' ')|| lpad(' ',10,' ')
653 -- for Current PAYE Details
654 || lpad('I',3,' ')||lpad('F',3,' ')
655 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
656 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
657 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
658 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
659 || lpad('Tax2',6,' '));
660
661 fnd_file.put_line(fnd_file.output, lpad('Number',24,' ')
662 || lpad('Cutoff',30,' ')||lpad('Credit',9,' ')
663 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
664 || lpad('Date',10,' ')||lpad('to Date',15,' ')
665 || lpad('to Date',12,' ') ||lpad(' ',28,' ')
666 -- for Current PAYE Details
667 || lpad('Cutoff',8,' ')||lpad('Credit',9,' ')
668 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
669 || lpad('Date',10,' ')||lpad('to Date',15,' ')
670 || lpad('to Date',12,' '));
671
672 fnd_file.put_line(fnd_file.output,lpad('----------',11,' ')
673 -- for previous PAYE Details
674 || lpad('----------',13,' ')
675 || lpad('---------',16,' ')
676 || lpad('-',3,' ')||lpad('-',3,' ')
677 || lpad('-------',9,' ')||lpad('-------',9,' ')
678 || lpad('--------',10,' ')||lpad('--------',10,' ')
679 || lpad('----------',12,' ')||lpad('-------',12,' ')
680 || lpad('-------',12,' ')||lpad('----',6,' ')
681 || lpad('----',6,' ')|| lpad(' ',10,' ')
682 -- for Current PAYE Details
683 || lpad('-',3,' ')||lpad('-',3,' ')
684 || lpad('-------',9,' ')||lpad('-------',9,' ')
685 || lpad('--------',10,' ')||lpad('--------',10,' ')
686 || lpad('----------',12,' ')||lpad('-------',12,' ')
687 || lpad('-------',12,' ')||lpad('----',6,' ')
688 || lpad('----',6,' '));
689
690 g_validate_count := 1;
691 END IF;
692 -- END 5724436
693 --BUG 6652299 ADDED L_FLAG
694 FOR r_no_of_assg IN c_no_of_assg
695 LOOP
696 BEGIN
697 l_flag :=0;
698 --bug 6376140
699 IF r_no_of_assg.no_of_assg =1
700 THEN OPEN c_pay1(r_no_of_assg.pps_number);
701 FETCH c_pay1 INTO r_pay;
702 IF c_pay1%FOUND THEN
703 l_flag:=1;
704 END IF;
705 CLOSE c_pay1;
706 ELSIF r_no_of_assg.no_of_assg >1
707 THEN OPEN c_pay(r_no_of_assg.pps_number);
708 FETCH c_pay INTO r_pay;
709 IF c_pay%FOUND THEN
710 l_flag:=1;
711 END IF;
712 CLOSE c_pay;
713 END IF;
714 --end if;
715
716 --end of bug 6376140
717
718
719 -- Bug 6929566 Start
720 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_date is ' || r_pay.cert_date);
721 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_start_date is ' || r_pay.cert_start_date);
722 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_end_date is ' || r_pay.cert_end_date);
723 IF r_pay.cert_date < r_pay.cert_start_date THEN
724 l_effective_date := r_pay.cert_start_date;
725 ELSIF r_pay.cert_date >= r_pay.cert_start_date THEN
726 l_effective_date := r_pay.cert_date;
727 END IF;
728 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_effective_date is ' || l_effective_date);
729 --
730 -- Bug 6929566 End
731
732 --FOR r_pay IN c_pay
733 --LOOP
734 --BEGIN
735 --IF r_pay.pps_number_hr IS NOT NULL THEN
736 --Initialize local variables on each loop pass to pass to outer exception handler
737 IF l_flag=1 THEN
738 l_pps_number_hr := r_pay.pps_number_hr;
739 l_employee_number_hr := r_pay.employee_no_hr;
740 l_first_name_hr := r_pay.first_name_hr;
741 l_last_name_hr := r_pay.last_name_hr;
742 l_payroll_name_hr := r_pay.payroll_name_hr;
743 l_tax_district := r_pay.tax_district;
744 l_pps_number_int := r_pay.pps_number_int;
745 l_employee_number_int := r_pay.employee_no_int;
746 --l_ins_object_version_no := r_pay.object_version_no;
747 l_std_rate_of_tax := r_pay.std_rate_of_tax;
748 l_higher_rate_of_tax := r_pay.higher_rate_of_tax;
749 --bug 6376140
750 --ppsno_cnt := ppsno_cnt+1;
751 IF l_pps_number_hr IS NOT NULL THEN
752 ppsno_cnt := ppsno_cnt+1;
753 l_pps_number(ppsno_cnt) :=r_pay.pps_number_hr;
754 --fnd_file.put_line(l_pps_number(ppsno_cnt));
755 hr_utility.set_location('PPS Number..'||l_pps_number_hr,420);
756 -- checks for standard rate tax to be mandatory
757 IF l_std_rate_of_tax IS NULL THEN
758 raise std_rate_of_tax_is_null;
759 END IF; --l_std_rate_of_tax
760 -- checks for higher rate of tax to be mandatory
761 IF l_higher_rate_of_tax IS NULL THEN
762 raise higher_rate_of_tax_is_null;
763 END IF;--l_higher_rate_of_tax
764 -- checks for exemption indicator to be mandatory
765 IF r_pay.exemption_indicator is null then
766 raise exemption_is_null; --r_pay.exemption_indicator
767 END IF;
768 -- checks for exact rate of tax bug 5766334
769 open csr_get_global_value('IE_TAX_RATE_EXEMPT',l_effective_date); -- Bug 6929566
770 FETCH csr_get_global_value into l_tax_rate_exempt;
771 CLOSE csr_get_global_value;
772
773 open csr_get_global_value('IE_TAX_RATE2', l_effective_date); -- Bug 6929566
774 FETCH csr_get_global_value into l_tax_rate_high;
775 CLOSE csr_get_global_value;
776
777 -- end bug 5766334
778
779
780 IF r_pay.exemption_indicator='Y' AND r_pay.higher_rate_of_tax <> l_tax_rate_exempt then
781 raise exemption_mismatch;
782 END IF; --r_pay.exemption_indicator
783 -- checks for exact rate of tax
784 IF r_pay.exemption_indicator='N' AND r_pay.higher_rate_of_tax <> l_tax_rate_high then
785 raise normal_tax_mismatch;
786 END IF; --r_pay.exemption_indicator
787 hr_utility.set_location('PPS Number..Initial check'||l_pps_number_hr,421);
788 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,101);
789 --
790 IF r_pay.tot_pay_to_date is null and r_pay.tot_pay_to_date is null and
791 (r_pay.wk_mth_indicator = 0 or (r_pay.wk_mth_indicator=1 and r_pay.exemption_indicator='Y')) then
792 raise pay_to_date;
793 END IF;
794 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,422);
795
796 -- check if cerificate start date is before or equal to certificate end date
797
798 pay_ipd_bus.chk_cert_start_end_dates(
799 p_certificate_start_date => r_pay.cert_start_date
800 , p_certificate_end_date => r_pay.cert_end_date
801 );
802 hr_utility.set_location('PPS Number..third check'||l_pps_number_hr,423);
803 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,102);
804 -- Bug Fix 3500192
805 -- tax basis is set as per the value of week month indicator in the interface table
806 IF (r_pay.wk_mth_indicator = 1) THEN
807 l_tax_basis := 'IE_WEEK1_MONTH1';
808 ELSE
809 l_tax_basis := 'IE_CUMULATIVE';
810 END IF;
811 -- Bug Fix 4618981
812 IF r_pay.exemption_indicator='Y' then
813 IF r_pay.wk_mth_indicator = 1 THEN
814 l_tax_basis := 'IE_EXEMPT_WEEK_MONTH';
815 ELSE
816 l_tax_basis := 'IE_EXEMPTION';
817 END IF;
818 END IF;
819
820 hr_utility.set_location('l_tax_basis'||l_tax_basis,424);
821 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,103);
822 -- check if amounts are valid for the given tax basis, for 'Emergency'
823 -- tax basis weekly and monthly tax credits ans std rate cut-off amounts must
824 -- be null and for other values of tax basis weekly or monthly amounts
825 -- (depending on payroll frequency) must be not null.
826
827 /*pay_ipd_bus.chk_tax_basis_amounts(
828 p_effective_date => r_pay.cert_start_date
829 , p_assignment_id => r_pay.assignment_id
830 -- Bug Fix 3500192
831 --, p_tax_basis => 'IE_CUMULATIVE'
832 , p_tax_basis => l_tax_basis
833 , p_weekly_tax_credit => r_pay.wk_tax_credit
834 , p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
835 , p_monthly_tax_credit => r_pay.mth_tax_credit
836 , p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
837 );*/
838
839 hr_utility.set_location('pay_ipd_bus.chk_tax_basis_amounts'||l_pps_number_hr,424);
840 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,104);
841 /* If the validate mode is 'Validate and Rollback' then set the validation input paramter
842 to true else for all modes it is set to false*/
843
844 IF p_validate_mode = 'IE_VALIDATE_ROLLBACK' THEN
845 l_validate := TRUE;
846 ELSE
847 l_validate := FALSE;
848 END IF;
849
850 --IF (r_pay.paye_details_id IS NOT NULL AND
851 IF (p_validate_mode = 'IE_VALIDATE_ROLLBACK' OR p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
852 hr_utility.set_location('PPS Number..'||l_pps_number_hr,425);
853 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,105);
854 OPEN get_paye_details(r_pay.assignment_id);
855 FETCH get_paye_details INTO l_paye_count;
856 CLOSE get_paye_details;
857 -- to check if the payroll is attached to the assignment as of certificate
858 -- issue date. This is called irrespective of whether PAYE details exists
859 -- or not.
860 pay_ipd_bus.chk_tax_basis_amounts(
861 p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5396580
862 ,p_assignment_id => r_pay.assignment_id
863 -- Bug Fix 3500192
864 -- , p_tax_basis => 'IE_CUMULATIVE'
865 ,p_tax_basis => l_tax_basis
866 ,p_weekly_tax_credit => r_pay.wk_tax_credit
867 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
868 ,p_monthly_tax_credit => r_pay.mth_tax_credit
869 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
870 );
871 IF l_paye_count <> 0 THEN
872 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,426);
873
874
875 hr_utility.set_location(r_pay.cert_date, 10);
876
877 pay_ie_paye_pkg.update_paye_change_freq --4878630
878 (p_assignment_id => r_pay.assignment_id
879 ,p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5724436
880 ,p_payroll_id => r_pay.payroll_id
881 ,P_DATETRACK_UPDATE_MODE => 'UPDATE'
882 ,p_tax_upload_flag => 'TU'
883 ,p_tax_basis => l_tax_basis
884 ,p_cert_start_date => r_pay.cert_start_date -- 17140460.6
885 ,p_cert_end_date => r_pay.cert_end_date
886 ,p_weekly_tax_credit => r_pay.wk_tax_credit
887 ,p_monthly_tax_credit => r_pay.mth_tax_credit
888 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
889 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
890 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
891 ,p_pay_to_date => r_pay.tot_pay_to_date
892 ,p_cert_date =>r_pay.cert_date);
893
894 hr_utility.set_location(r_pay.cert_date, 20);
895 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,427);
896 ELSE
897 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,428);
898 hr_utility.set_location(r_pay.cert_date, 30);
899 pay_ie_paye_api.create_ie_paye_details --4878630
900 (p_validate => false
901 ,p_effective_date => l_effective_date -- Bug 6929566
902 ,p_assignment_id => r_pay.assignment_id
903 ,p_info_source => 'IE_ELECTRONIC'
904 ,p_tax_basis => l_tax_basis
905 ,p_certificate_start_date => r_pay.cert_start_date -- For bug 5396549
906 ,p_tax_assess_basis => 'IE_SEP_TREAT'
907 ,p_certificate_issue_date => r_pay.cert_date
908 ,p_certificate_end_date => r_pay.cert_end_date
909 ,p_weekly_tax_credit => r_pay.wk_tax_credit
910 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
911 ,p_monthly_tax_credit => r_pay.mth_tax_credit
912 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
913 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
914 ,p_pay_to_date => r_pay.tot_pay_to_date
915 ,p_disability_benefit => null
916 ,p_lump_sum_payment => null
917 ,p_paye_details_id => o_paye_details_id
918 ,p_object_version_number => o_ovn
919 ,p_effective_start_date => o_effective_start_date
920 ,p_effective_end_date => o_effective_end_date);
921 hr_utility.set_location(r_pay.cert_date, 40);
922 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,429);
923 END IF;
924 END IF;
925 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,106);
926 retcode := 0;
927
928
929 /*Update interface table and set processed flag to 'Yes' to record that record has been updated or
930 inserted into payroll tables successfully using the row handler APIs.*/
931
932 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
933 UPDATE pay_ie_tax_body_interface
934 SET processed_flag = 'Y'
935 WHERE pps_number = r_pay.pps_number_int;
936 END IF;
937 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,107);
938 IF p_validate_mode <> 'IE_VALIDATE' THEN
939 fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Success');
940 ELSE
941 IF g_validate_count =1 then
942 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,841);
943 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,108);
944 OPEN c_get_paye_details(r_pay.assignment_id,
945 r_pay.pps_number_int,
946 l_effective_date, -- Bug 6929566 -- r_pay.cert_date,
947 r_pay.employee_no_int);
948 FETCH c_get_paye_details INTO r_paye_details;
949 -- IF no PAYE details exists then set the default values for PAYE.
950 IF c_get_paye_details%ROWCOUNT = 0 then
951 r_paye_details.tax_basis := 'IE_EMERGENCY';
952 r_paye_details.certificate_issue_date := to_date('01/01/0001','dd/mm/yyyy');
953 r_paye_details.WEEKLY_TAX_CREDIT := 0;
954 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
955 r_paye_details.MONTHLY_TAX_CREDIT := 0;
956 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
957 -- bug 5837091
958 ELSIF r_paye_details.tax_basis in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS') then
959 r_paye_details.certificate_issue_date := nvl(to_date(to_char(r_paye_details.certificate_issue_date,'dd-mm-yyyy'),'dd-mm-yyyy'),to_date('01/01/0001','dd/mm/yyyy'));
960 r_paye_details.WEEKLY_TAX_CREDIT := 0;
961 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
962 r_paye_details.MONTHLY_TAX_CREDIT := 0;
963 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
964 else
965 r_paye_details.certificate_issue_date := nvl(to_date(to_char(r_paye_details.certificate_issue_date,'dd-mm-yyyy'),'dd-mm-yyyy'),to_date('01/01/0001','dd/mm/yyyy'));
966 r_paye_details.WEEKLY_TAX_CREDIT := nvl(r_paye_details.WEEKLY_TAX_CREDIT,0);
967 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := nvl(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,0);
968 r_paye_details.MONTHLY_TAX_CREDIT := nvl(r_paye_details.MONTHLY_TAX_CREDIT,0);
969 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := nvl(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,0);
970 END IF;
971 -- end bug 5837091
972 CLOSE c_get_paye_details;
973 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,842);
974
975 OPEN csr_pay_freq (r_pay.assignment_id,l_effective_date); -- Bug 6929566 --r_pay.cert_date);
976 FETCH csr_pay_freq INTO pay_freq_rec;
977 --
978 IF csr_pay_freq%NOTFOUND THEN
979 CLOSE csr_pay_freq;
980 hr_utility.set_message(801, 'HR_IE_ASG_NOT_IN_PAYROLL');
981 hr_utility.raise_error;
982 END IF;
983 --
984 CLOSE csr_pay_freq;
985 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,843);
986
987 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,109);
988 OPEN get_p45_details(r_pay.assignment_id);
989 FETCH get_p45_details INTO l_max_action_id;
990 CLOSE get_p45_details;
991
992 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,110);
993 hr_utility.set_location('Assignment Action ID..'||l_max_action_id,110);
994 -- This will be called only if has any assignment actions.
995 IF l_max_action_id <> 0 THEN
996 hr_utility.set_location('Assignment Action ID is not null',112);
997 l_pay_to_date := NVL (
998 ROUND (
999 TO_NUMBER (
1000 pay_balance_pkg.get_value (
1001 pay_ie_p35.get_defined_balance_id (
1002 '_ASG_YTD',
1003 'IE P45 Pay'
1004 ),
1005 l_max_action_id
1006 )
1007 ),
1008 2
1009 ),
1010 0
1011 );
1012 l_tax_to_date := NVL (
1013 ROUND (
1014 TO_NUMBER (
1015 pay_balance_pkg.get_value (
1016 pay_ie_p35.get_defined_balance_id (
1017 '_ASG_YTD',
1018 'IE P45 Tax Deducted'
1019 ),
1020 l_max_action_id
1021 )
1022 ),
1023 2
1024 ),
1025 0
1026 );
1027 ELSE
1028 hr_utility.set_location('Assignment Action ID is null',113);
1029 l_pay_to_date := 0;
1030 l_tax_to_date := 0;
1031 END IF;
1032 -- print the values.
1033 fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')
1034 -- for previous PAYE Details
1035 || lpad(substr(r_pay.employee_no_int,1,12),13,' ')
1036 || lpad(substr(r_pay.last_name_hr,1,15),16,' ')
1037 || lpad(pay_ie_paye_pkg.decode_value_char(r_paye_details.tax_basis='IE_WEEK1_MONTH1','1','0'),3,' ')
1038 || lpad(pay_ie_paye_pkg.decode_value_char(r_paye_details.tax_basis='IE_EXEMPT_WEEK_MONTH' or r_paye_details.tax_basis='IE_EXEMPTION' ,'Y','N'),3,' ')
1039 || lpad(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,9,' ')||lpad(r_paye_details.MONTHLY_TAX_CREDIT,9,' ')
1040 || lpad(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,10,' ')||lpad(r_paye_details.WEEKLY_TAX_CREDIT,10,' ')
1041 || lpad(pay_ie_paye_pkg.decode_value_char(to_char(r_paye_details.CERTIFICATE_ISSUE_DATE,'DDMMRRRR')=to_char(to_date('01/01/0001','dd/mm/yyyy'),'DDMMRRRR'),'NIL',to_char(r_paye_details.CERTIFICATE_ISSUE_DATE,'DD-mm-RRRR')),12,' ')
1042 || lpad(to_char(l_pay_to_date),12,' ')
1043 || lpad(to_char(l_tax_to_date),12,' ')||lpad('20',6,' ')
1044 || lpad(pay_ie_paye_pkg.decode_value_char(r_paye_details.tax_basis='IE_EXEMPT_WEEK_MONTH' or r_paye_details.tax_basis='IE_EXEMPTION',l_tax_rate_exempt,l_tax_rate_high),6,' ')
1045 || lpad(' ',10,' ')
1046 -- for Current PAYE Details
1047 || lpad(r_pay.wk_mth_indicator,3,' ')||lpad(r_pay.exemption_indicator,3,' ')
1048 || lpad(r_pay.mth_rate_cutoff,9,' ')||lpad(r_pay.mth_tax_credit,9,' ')
1049 || lpad(r_pay.wk_rate_cutoff,10,' ')||lpad(r_pay.wk_tax_credit,10,' ')
1050 || lpad(to_char(r_pay.cert_date,'dd-mm-yyyy'),12,' ')||lpad(r_pay.tot_pay_to_date,12,' ')
1051 || lpad(r_pay.tot_tax_to_date,12,' ')||lpad(r_pay.std_rate_of_tax,6,' ')
1052 || lpad(r_pay.higher_rate_of_tax,6,' '));
1053
1054
1055 END IF;
1056 r_paye_details := r_empty_details;
1057 END IF;
1058 END IF;
1059 END IF;
1060 -- end bug 5724436.
1061 EXCEPTION
1062 -- Bug Fix 3500192
1063 -- WHEN name_not_equal THEN
1064 -- l_error := SQLERRM;
1065 -- retcode := 1;
1066 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1067 -- FND_FILE.PUT_LINE(fnd_file.log, 'The first name and last name in the interface body table does not match
1068 -- the first and last name in the payroll tables');
1069 --
1070 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1071 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1072 -- r_pay.employee_no_hr ||', '||
1073 -- r_pay.last_name_hr ||' '||
1074 -- r_pay.first_name_hr ||', '||
1075 -- r_pay.last_name_int ||' '||
1076 -- r_pay.first_name_int||', '||
1077 -- r_pay.payroll_name_hr
1078 -- );
1079 --
1080 -- -- The exception details are written to an error table
1081 --
1082 -- INSERT INTO pay_ie_tax_error ( pps_number
1083 -- , employee_number
1084 -- , full_name
1085 -- , payroll_name
1086 -- , tax_district
1087 -- , error_stack_message
1088 -- , error_message
1089 -- , request_id
1090 -- , error_date )
1091 -- VALUES (r_pay.pps_number_hr
1092 -- , r_pay.employee_no_hr
1093 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1094 -- , r_pay.payroll_name_hr
1095 -- , r_pay.tax_district
1096 -- , l_error_stack
1097 -- , l_error
1098 -- , l_request_id
1099 -- , sysdate);
1100 -- COMMIT;
1101 --
1102 -- WHEN same_day THEN
1103 -- l_error := SQLERRM;
1104 -- retcode := 1;
1105 -- FND_FILE.PUT_LINE(fnd_file.log, 'This record has already been updated today with changes to the
1106 -- PAY_IE_PAYE_DETAILS_F table');
1107 --
1108 --
1109 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1110 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1111 -- r_pay.employee_no_hr ||', '||
1112 -- r_pay.last_name_hr ||' '||
1113 -- r_pay.first_name_hr ||', '||
1114 -- r_pay.ppd_effective_start_date ||', '||
1115 -- r_pay.payroll_name_hr
1116 -- );
1117 --
1118 -- -- The exception details are written to an error table
1119 --
1120 -- INSERT INTO pay_ie_tax_error ( pps_number
1121 -- , employee_number
1122 -- , full_name
1123 -- , payroll_name
1124 -- , tax_district
1125 -- , error_stack_message
1126 -- , error_message
1127 -- , request_id
1128 -- , error_date )
1129 -- VALUES (r_pay.pps_number_hr
1130 -- , r_pay.employee_no_hr
1131 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1132 -- , r_pay.payroll_name_hr
1133 -- , r_pay.tax_district
1134 -- , l_error_stack
1135 -- , l_error
1136 -- , l_request_id
1137 -- , sysdate);
1138 -- COMMIT;
1139
1140 /*WHEN future_day THEN
1141 l_error := SQLERRM;
1142 retcode := 1;
1143 FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');
1144
1145 FND_FILE.NEW_LINE(fnd_file.log, 1);
1146 FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1147 r_pay.employee_no_hr ||', '||
1148 r_pay.last_name_hr ||' '||
1149 r_pay.first_name_hr ||', '||
1150 r_pay.payroll_name_hr
1151 );
1152 -- The exception details are written to an error table
1153
1154 INSERT INTO pay_ie_tax_error ( pps_number
1155 , employee_number
1156 , full_name
1157 , payroll_name
1158 , tax_district
1159 , error_stack_message
1160 , error_message
1161 , request_id
1162 , error_date )
1163 VALUES (r_pay.pps_number_hr
1164 , r_pay.employee_no_hr
1165 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1166 , r_pay.payroll_name_hr
1167 , r_pay.tax_district
1168 , l_error_stack
1169 , l_error
1170 , l_request_id
1171 , sysdate);
1172 COMMIT;*/
1173
1174 WHEN std_rate_of_tax_is_null THEN
1175 l_error := 'Standard Rate of Tax cannot be Null';--SQLERRM;
1176 retcode := 1;
1177 FND_FILE.PUT_LINE(fnd_file.log, 'Standard Rate of Tax cannot be Null');
1178
1179 FND_FILE.NEW_LINE(fnd_file.log, 1);
1180 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1181 r_pay.employee_no_hr ||', '||
1182 r_pay.last_name_hr ||' '||
1183 r_pay.first_name_hr ||', '||
1184 r_pay.payroll_name_hr
1185 );*/
1186 --IF p_validate_mode in ('IE_VALIDATE') THEN
1187 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1188 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1189 l_err_tab(err_cnt).p_err_msg := 'Failed : Standard Rate of Tax cannot be Null';
1190 err_cnt := err_cnt + 1;
1191 --ELSE
1192 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : Standard Rate of Tax cannot be Null');
1193 --END IF;
1194 -- The exception details are written to an error table
1195
1196 INSERT INTO pay_ie_tax_error ( pps_number
1197 , employee_number
1198 , full_name
1199 , payroll_name
1200 , tax_district
1201 , error_stack_message
1202 , error_message
1203 , request_id
1204 , error_date )
1205 VALUES (r_pay.pps_number_hr
1206 , substr(r_pay.employee_no_int,1,12)
1207 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1208 , r_pay.payroll_name_hr
1209 , r_pay.tax_district
1210 , l_error_stack
1211 , l_error
1212 , l_request_id
1213 , sysdate);
1214 COMMIT;
1215
1216 WHEN higher_rate_of_tax_is_null THEN
1217 l_error := 'Higher Rate of Tax cannot be Null';--SQLERRM;
1218 retcode := 1;
1219 FND_FILE.PUT_LINE(fnd_file.log, 'Higher Rate of Tax cannot be Null');
1220
1221 FND_FILE.NEW_LINE(fnd_file.log, 1);
1222 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1223 r_pay.employee_no_hr ||', '||
1224 r_pay.last_name_hr ||' '||
1225 r_pay.first_name_hr ||', '||
1226 r_pay.payroll_name_hr
1227 );*/
1228
1229 --IF p_validate_mode = 'IE_VALIDATE' THEN
1230 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1231 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1232 l_err_tab(err_cnt).p_err_msg := 'Failed : Higher Rate of Tax cannot be Null';
1233 err_cnt := err_cnt + 1;
1234 --ELSE
1235 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : Higher Rate of Tax cannot be Null');
1236 --END IF;
1237
1238 -- The exception details are written to an error table
1239 INSERT INTO pay_ie_tax_error ( pps_number
1240 , employee_number
1241 , full_name
1242 , payroll_name
1243 , tax_district
1244 , error_stack_message
1245 , error_message
1246 , request_id
1247 , error_date )
1248 VALUES (r_pay.pps_number_hr
1249 , substr(r_pay.employee_no_int,1,12)
1250 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1251 , r_pay.payroll_name_hr
1252 , r_pay.tax_district
1253 , l_error_stack
1254 , l_error
1255 , l_request_id
1256 , sysdate);
1257 COMMIT;
1258
1259 WHEN exemption_is_null THEN
1260
1261 l_error := 'Exemption Indicator cannot be Null';--SQLERRM;
1262 retcode := 1;
1263 FND_FILE.PUT_LINE(fnd_file.log, 'Exemption Indicator cannot be Null');
1264
1265 FND_FILE.NEW_LINE(fnd_file.log, 1);
1266 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1267 r_pay.employee_no_hr ||', '||
1268 r_pay.last_name_hr ||' '||
1269 r_pay.first_name_hr ||', '||
1270 r_pay.payroll_name_hr
1271 );*/
1272
1273 --IF p_validate_mode = 'IE_VALIDATE' THEN
1274 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1275 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1276 l_err_tab(err_cnt).p_err_msg := 'Failed : Exemption Indicator cannot be Null';
1277 err_cnt := err_cnt + 1;
1278 --ELSE
1279 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : Exemption Indicator cannot be Null');
1280 --END IF;
1281
1282 -- The exception details are written to an error table
1283 INSERT INTO pay_ie_tax_error ( pps_number
1284 , employee_number
1285 , full_name
1286 , payroll_name
1287 , tax_district
1288 , error_stack_message
1289 , error_message
1290 , request_id
1291 , error_date )
1292 VALUES (r_pay.pps_number_hr
1293 , substr(r_pay.employee_no_int,1,12)
1294 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1295 , r_pay.payroll_name_hr
1296 , r_pay.tax_district
1297 , l_error_stack
1298 , l_error
1299 , l_request_id
1300 , sysdate);
1301 COMMIT;
1302
1303 WHEN exemption_mismatch THEN
1304 l_error := 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';--SQLERRM;
1305 retcode := 1;
1306 FND_FILE.PUT_LINE(fnd_file.log, 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%');
1307
1308 FND_FILE.NEW_LINE(fnd_file.log, 1);
1309 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1310 r_pay.employee_no_hr ||', '||
1311 r_pay.last_name_hr ||' '||
1312 r_pay.first_name_hr ||', '||
1313 r_pay.payroll_name_hr
1314 );*/
1315
1316 --IF p_validate_mode = 'IE_VALIDATE' THEN
1317 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1318 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1319 l_err_tab(err_cnt).p_err_msg := 'Failed : The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';
1320 err_cnt := err_cnt + 1;
1321 --ELSE
1322 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%');
1323 --END IF;
1324
1325 -- The exception details are written to an error table
1326 INSERT INTO pay_ie_tax_error ( pps_number
1327 , employee_number
1328 , full_name
1329 , payroll_name
1330 , tax_district
1331 , error_stack_message
1332 , error_message
1333 , request_id
1334 , error_date )
1335 VALUES (r_pay.pps_number_hr
1336 , substr(r_pay.employee_no_int,1,12)
1337 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1338 , r_pay.payroll_name_hr
1339 , r_pay.tax_district
1340 , l_error_stack
1341 , l_error
1342 , l_request_id
1343 , sysdate);
1344 COMMIT;
1345
1346 WHEN normal_tax_mismatch THEN
1347 l_error := 'The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%';--SQLERRM;
1348 retcode := 1;
1349 FND_FILE.PUT_LINE(fnd_file.log, 'The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%');
1350
1351 FND_FILE.NEW_LINE(fnd_file.log, 1);
1352 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1353 r_pay.employee_no_hr ||', '||
1354 r_pay.last_name_hr ||' '||
1355 r_pay.first_name_hr ||', '||
1356 r_pay.payroll_name_hr
1357 );*/
1358 -- The exception details are written to an error table
1359 --IF p_validate_mode = 'IE_VALIDATE' THEN
1360 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1361 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1362 l_err_tab(err_cnt).p_err_msg := 'Failed : The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%';
1363 err_cnt := err_cnt + 1;
1364 --ELSE
1365 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%');
1366 --END IF;
1367
1368 -- The exception details are written to an error table
1369 INSERT INTO pay_ie_tax_error ( pps_number
1370 , employee_number
1371 , full_name
1372 , payroll_name
1373 , tax_district
1374 , error_stack_message
1375 , error_message
1376 , request_id
1377 , error_date )
1378 VALUES (r_pay.pps_number_hr
1379 , substr(r_pay.employee_no_int,1,12)
1380 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1381 , r_pay.payroll_name_hr
1382 , r_pay.tax_district
1383 , l_error_stack
1384 , l_error
1385 , l_request_id
1386 , sysdate);
1387 COMMIT;
1388
1389 WHEN pay_to_date THEN
1390 l_error := 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.';--SQLERRM;
1391 retcode := 1;
1392 FND_FILE.PUT_LINE(fnd_file.log, 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.');
1393
1394 FND_FILE.NEW_LINE(fnd_file.log, 1);
1395 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1396 r_pay.employee_no_hr ||', '||
1397 r_pay.last_name_hr ||' '||
1398 r_pay.first_name_hr ||', '||
1399 r_pay.payroll_name_hr
1400 );*/
1401
1402 --IF p_validate_mode = 'IE_VALIDATE' THEN
1403 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1404 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1405 l_err_tab(err_cnt).p_err_msg := 'Failed : Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis';
1406 err_cnt := err_cnt + 1;
1407 --ELSE
1408 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis');
1409 --END IF;
1410
1411 -- The exception details are written to an error table
1412 INSERT INTO pay_ie_tax_error ( pps_number
1413 , employee_number
1414 , full_name
1415 , payroll_name
1416 , tax_district
1417 , error_stack_message
1418 , error_message
1419 , request_id
1420 , error_date )
1421 VALUES (r_pay.pps_number_hr
1422 , substr(r_pay.employee_no_int,1,12)
1423 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1424 , r_pay.payroll_name_hr
1425 , r_pay.tax_district
1426 , l_error_stack
1427 , l_error
1428 , l_request_id
1429 , sysdate);
1430 COMMIT;
1431
1432 WHEN OTHERS THEN
1433 errbuf := fnd_message.get;
1434 l_error_stack := errbuf;
1435 l_error := SQLERRM;
1436
1437 /*Update interface table and set processed flag to 'No' to record that record has not been updated
1438 or inserted into payroll tables*/
1439
1440 IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
1441 UPDATE pay_ie_tax_body_interface
1442 SET processed_flag = 'N'
1443 WHERE pps_number = r_pay.pps_number_int;
1444 END IF;
1445 -- The following command will be used to output the exception details to an output file:
1446
1447 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1448 r_pay.pps_number_int ||', '||
1449 r_pay.employee_no_hr ||', '||
1450 r_pay.pps_number_int ||', '||
1451 r_pay.last_name_hr ||' '||
1452 r_pay.first_name_hr ||', '||
1453 r_pay.pps_number_int ||', '||
1454 r_pay.payroll_name_hr ||', '||
1455 r_pay.pps_number_int
1456 );*/
1457 --IF p_validate_mode = 'IE_VALIDATE' THEN
1458 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1459 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1460 l_err_tab(err_cnt).p_err_msg := 'Failed : '||l_error;
1461 err_cnt := err_cnt + 1;
1462 --ELSE
1463 -- fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')||lpad(substr(r_pay.employee_no_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : '||l_error);
1464 --END IF;
1465 -- The exception details are written to an error table
1466
1467 INSERT INTO pay_ie_tax_error ( pps_number
1468 , employee_number
1469 , full_name
1470 , payroll_name
1471 , tax_district
1472 , error_stack_message
1473 , error_message
1474 , request_id
1475 , error_date )
1476 VALUES (r_pay.pps_number_hr
1477 , substr(r_pay.employee_no_int,1,12)
1478 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1479 , r_pay.payroll_name_hr
1480 , r_pay.tax_district
1481 , l_error_stack
1482 , l_error
1483 , l_request_id
1484 , sysdate);
1485 COMMIT;
1486
1487 retcode := 1; -- 6215901
1488
1489 END;
1490 --end if;
1491 END LOOP;
1492
1493
1494 IF l_err_tab.COUNT <> 0 then
1495 for i in l_err_tab.first..l_err_tab.last
1496 loop
1497 fnd_file.put_line(fnd_file.output,lpad(l_err_tab(i).p_pps_number,11,' ')||lpad(l_err_tab(i).p_works_number,13,' ')||lpad(' ',20,' ')||l_err_tab(i).p_err_msg);
1498 end loop;
1499 END IF;
1500 --bug 6376140
1501 /* for i in csr_skipped_asg
1502 loop
1503 fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||lpad(i.works_number,13,' ')||lpad(' ',20,' ') || 'Please check the employee''s works number and/or PPS number');
1504 end loop;
1505 */
1506 FOR i in csr_skipped_assignments
1507 LOOP
1508 flag :='N';
1509 IF(l_pps_number.count<>0) then
1510 FOR j in l_pps_number.first..l_pps_number.last
1511 LOOP
1512 IF l_pps_number(j)=i.pps_number THEN
1513 flag :='Y';
1514 END IF;
1515 EXIT WHEN flag='Y';
1516 END LOOP;
1517 END IF;
1518 IF flag='N' THEN
1519 -- open check_pps(i.pps_number);
1520 -- FETCH check_pps INTO p_check_pps
1521 -- if check_pps%found then
1522 fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||lpad(substr(nvl(i.works_number,' '),1,12),13,' ')
1523 || lpad(substr(nvl(i.last_name,' '),1,20),16,' ')||lpad(substr(nvl(i.first_name,' '),1,20),16,' ')||'Please check the employee''s PPS number/works number');
1524 -- else
1525 -- else
1526 --fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||'Please check the employee''s PPS number');
1527 -- end if;
1528 -- close check_pps;
1529 END IF;
1530 END LOOP;
1531 --end of bug 6376140
1532
1533 -- Bug Fix 3500192
1534 -- Writes the trailer record in the log file
1535 IF l_header_count = 1 THEN
1536 log_ie_paye_footer(l_record_count);
1537 END IF;
1538
1539 /* If user selects the mode to be 'Validate and Commit' then
1540 p_validate_mode = 'IE_VALIDATE_COMMIT' then records are committed else records are rolled back */
1541
1542 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
1543 COMMIT;
1544 ELSE
1545 ROLLBACK;
1546 END IF;
1547
1548
1549 EXCEPTION
1550 WHEN NO_DATA_FOUND THEN
1551 l_error := SQLERRM;
1552 retcode := 1;
1553 FND_FILE.PUT_LINE(fnd_file.log, 'No data found');
1554
1555 INSERT INTO pay_ie_tax_error ( pps_number
1556 , employee_number
1557 , full_name
1558 , payroll_name
1559 , tax_district
1560 , error_stack_message
1561 , error_message
1562 , request_id
1563 , error_date)
1564 VALUES (l_pps_number_hr
1565 , substr(l_employee_number_hr,1,12)
1566 , l_last_name_hr ||' '|| l_first_name_hr
1567 , l_payroll_name_hr
1568 , l_tax_district
1569 , l_error_stack
1570 , l_error
1571 , l_request_id
1572 , sysdate
1573 );
1574 COMMIT;
1575
1576 WHEN OTHERS THEN
1577
1578 errbuf := fnd_message.get;
1579 l_error_stack := errbuf;
1580 l_error := SQLERRM;
1581 retcode := 2;
1582 /* The following command will be used to output the exception details to an output file*/
1583
1584 /*FND_FILE.PUT_LINE(fnd_file.output, l_pps_number_hr ||', '||
1585 l_pps_number_int ||', '||
1586 l_employee_number_hr ||', '||
1587 l_employee_number_int ||', '||
1588 l_last_name_hr ||' '||l_first_name_hr ||', '||
1589 l_last_name_int ||' '|| l_first_name_int ||', '||
1590 l_payroll_name_hr ||', '||
1591 l_tax_district
1592 );*/
1593 --IF p_validate_mode = 'IE_VALIDATE' THEN
1594 l_err_tab(err_cnt).p_pps_number := l_pps_number_int;
1595 l_err_tab(err_cnt).p_works_number := substr(l_employee_number_int,1,12);
1596 l_err_tab(err_cnt).p_err_msg := 'Failed : OTHER in Main..'||l_error;
1597 err_cnt := err_cnt + 1;
1598 --ELSE
1599 -- fnd_file.put_line(fnd_file.output,lpad(l_pps_number_int,20,' ')||lpad(substr(l_employee_number_int,1,12),13,' ')||lpad(' ',20,' ')||'Failed : OTHER in Main..'||l_error);
1600 --END IF;
1601 /* The exception details are written to an error table */
1602 INSERT INTO pay_ie_tax_error ( pps_number
1603 , employee_number
1604 , full_name
1605 , payroll_name
1606 , tax_district
1607 , error_stack_message
1608 , error_message
1609 , request_id
1610 , error_date)
1611 VALUES (l_pps_number_hr
1612 , substr(l_employee_number_hr,1,12)
1613 , l_last_name_hr ||' '||l_first_name_hr
1614 , l_payroll_name_hr
1615 , l_tax_district
1616 , l_error_stack
1617 , l_error
1618 , l_request_id
1619 , sysdate);
1620 COMMIT;
1621 END valinsupd;
1622
1623 -- Bug Fix 3500192
1624 -- This procedures writes the Paye Details of the employee in the log file
1625 PROCEDURE log_ie_paye_header
1626 AS
1627 l_line_1 varchar2(1000) := ' ';
1628 l_line_2 varchar2(1000) := ' ';
1629 l_line_3 varchar2(1000) := ' ';
1630 BEGIN
1631 l_line_1 := rpad('Assignment',10)
1632 || ' '
1633 || rpad('Employee',15)
1634 || ' '
1635 || rpad('PPS',62)
1636 || ' '
1637 || rpad(lpad('Tax Credit',18-length('Tax Credit')/2),17)
1638 || ' '
1639 || rpad('Std Rate Cut Off',17);
1640
1641 l_line_2 := rpad('Number',10)
1642 || ' '
1643 || rpad('Number',15)
1644 || ' '
1645 || rpad('Number',10)
1646 || ' '
1647 || rpad('Information Source',30)
1648 || ' '
1649 || rpad('Tax Basis',20)
1650 || ' '
1651 || rpad('Weekly',8)
1652 || ' '
1653 || rpad('Monthly',8)
1654 || ' '
1655 || rpad('Weekly',8)
1656 || ' '
1657 || rpad('Monthly',8);
1658
1659 l_line_3 := rpad('-',10,'-')
1660 || ' '
1661 || rpad('-',15,'-')
1662 || ' '
1663 || rpad('-',10,'-')
1664 || ' '
1665 || rpad('-',30,'-')
1666 || ' '
1667 || rpad('-',20,'-')
1668 || ' '
1669 || rpad('-',8,'-')
1670 || ' '
1671 || rpad('-',8,'-')
1672 || ' '
1673 || rpad('-',8,'-')
1674 || ' '
1675 || rpad('-',8,'-');
1676
1677 FND_FILE.NEW_LINE(fnd_file.log, 1);
1678 FND_FILE.PUT_LINE(fnd_file.log,l_line_1);
1679 FND_FILE.PUT_LINE(fnd_file.log,l_line_2);
1680 FND_FILE.PUT_LINE(fnd_file.log,l_line_3);
1681
1682 END log_ie_paye_header;
1683
1684 PROCEDURE log_ie_paye_body(
1685 p_paye_details_id IN NUMBER
1686 , p_pps_number IN VARCHAR2
1687 , p_employee_number IN VARCHAR2
1688 )
1689 AS
1690 CURSOR c_paye_details(p_paye_details_id NUMBER)
1691 IS
1692 SELECT assignment_id
1693 , tax_basis
1694 , info_source
1695 , weekly_tax_credit
1696 , weekly_std_rate_cut_off
1697 , monthly_tax_credit
1698 , monthly_std_rate_cut_off
1699 FROM pay_ie_paye_details_f
1700 WHERE paye_details_id = p_paye_details_id;
1701
1702 CURSOR c_lookup_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
1703 IS
1704 SELECT meaning
1705 FROM hr_lookups
1706 where lookup_type = p_lookup_type
1707 and lookup_code = p_lookup_code;
1708
1709 r_paye_details c_paye_details%ROWTYPE;
1710 l_line varchar2(1000);
1711 l_info_source varchar2(30);
1712 l_tax_basis varchar2(20);
1713
1714 BEGIN
1715 OPEN c_paye_details(p_paye_details_id);
1716 FETCH c_paye_details INTO r_paye_details;
1717 CLOSE c_paye_details;
1718 --
1719 OPEN c_lookup_meaning('IE_PAYE_INFO_SOURCE',r_paye_details.info_source);
1720 FETCH c_lookup_meaning INTO l_info_source;
1721 CLOSE c_lookup_meaning;
1722 --
1723 OPEN c_lookup_meaning('IE_PAYE_TAX_BASIS',r_paye_details.tax_basis);
1724 FETCH c_lookup_meaning INTO l_tax_basis;
1725 CLOSE c_lookup_meaning;
1726 --
1727 l_line := rpad(nvl(r_paye_details.assignment_id,0),10,' ')
1728 || ' '
1729 || rpad(nvl(p_employee_number,0),15,' ')
1730 || ' '
1731 || rpad(nvl(p_pps_number,' '),10,' ')
1732 || ' '
1733 || rpad(nvl(l_info_source,' '),30,' ')
1734 || ' '
1735 || rpad(nvl(l_tax_basis,' '),20,' ')
1736 || ' '
1737 || rpad(nvl(to_char(r_paye_details.weekly_tax_credit),'-'),8,' ')
1738 || ' '
1739 || rpad(nvl(to_char(r_paye_details.monthly_tax_credit),'-'),8,' ')
1740 || ' '
1741 || rpad(nvl(to_char(r_paye_details.weekly_std_rate_cut_off),'-'),8,' ')
1742 || ' '
1743 || rpad(nvl(to_char(r_paye_details.monthly_std_rate_cut_off),'-'),8,' ');
1744
1745 FND_FILE.PUT_LINE(fnd_file.log,l_line);
1746
1747 END log_ie_paye_body;
1748
1749 PROCEDURE log_ie_paye_footer(p_total IN NUMBER)
1750 AS
1751 l_line varchar2(100);
1752 BEGIN
1753 l_line := 'Number of Records: '
1754 || p_total;
1755 FND_FILE.NEW_LINE(fnd_file.log, 1);
1756 FND_FILE.PUT_LINE(fnd_file.log,l_line);
1757 FND_FILE.NEW_LINE(fnd_file.log, 1);
1758 END log_ie_paye_footer;
1759
1760 END PAY_IE_TAX_VAL;