[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_TAX_VAL
Source
1 Package body PAY_IE_TAX_VAL as
2 /* $Header: pyietxvl.pkb 120.22.12020000.2 2012/09/28 06:43:00 rsahai 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 --13359423
16 PROCEDURE valinsupd_new (
17 errbuf OUT NOCOPY VARCHAR2
18 , retcode OUT NOCOPY 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 ) AS
24 --bug 6376140
25 --BUG 6652299 ADDED DISTINCT KEY WORD TO THE CURSOR C_NO_OF_ASSG
26 /*Declare cursor to retrieve no.of assignments from person
27 and interface tables based on input parameters*/
28 cursor c_no_of_assg IS
29 select per.person_id person_id
30 ,per.NATIONAL_IDENTIFIER pps_number
31 ,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
32 from per_all_assignments_f asg,
33 per_all_people_f per,
34 pay_all_payrolls_f pay,
35 pay_ie_tax_body_interface tbi,
36 per_periods_of_service pps
37 where per.national_identifier = tbi.pps_number
38 --AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
39 --AND asg.payroll_id = pay.payroll_id
40 AND per.person_id = asg.person_id
41 AND tbi.process_flag = 'Y'
42 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
43 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
44 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
45 AND per.effective_end_date >= trunc(tbi.cert_start_date)
46 and asg.period_of_service_id=pps.period_of_service_id
47 and pps.person_id=per.person_id
48 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
49 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'))
50 --AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
51 --AND pay.effective_end_date >= trunc(tbi.cert_start_date)
52 group by per.person_id,per.NATIONAL_IDENTIFIER;
53
54 /* Cursor check_pps(p_pps_no varchar) is
55 Select 1 from per_all_people_f per
56 ,pay_ie_tax_body_interface tbi
57 Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
58 AND per.effective_end_date >= trunc(tbi.cert_start_date)
59 and per.national_identifier=p_pps_no
60 AND tbi.process_flag = 'Y'
61 and tbi.pps_number=per.national_identifier;
62 p_check_pps check_pps%rowtype; */
63
64 /*Declare cursor to retrieve all employee details from payroll
65 and interface tables based on input parameters for a multiple assignments*/
66 CURSOR c_pay(p_pps_number varchar) IS
67 SELECT distinct per.employee_number employee_no_hr
68 , per.national_identifier pps_number_hr
69 , per.last_name last_name_hr
70 , per.first_name first_name_hr
71 , asg.assignment_id assignment_id
72 --, asg.effective_start_date effective_start_date
73 , hoi.org_information1 tax_district
74 , pay.payroll_name payroll_name_hr
75 , pay.payroll_id payroll_id -- 4878630
76 --, ppd.paye_details_id paye_details_id --4878630
77 --, ppd.object_version_number object_version_no --4878630
78 --, ppd.effective_start_date ppd_effective_start_date
79 , tbi.pps_number pps_number_int
80 , asg.assignment_number employee_no_int --5724436
81 , tbi.first_name first_name_int
82 , tbi.last_name last_name_int
83 , tbi.cert_start_date cert_start_date
84 , tbi.cert_end_date cert_end_date
85 , tbi.cert_date cert_date
86 , tbi.wk_tax_credit wk_tax_credit
87 , tbi.mth_tax_credit mth_tax_credit
88 , tbi.wk_rate_cutoff wk_rate_cutoff
89 , tbi.mth_rate_cutoff mth_rate_cutoff
90 -- Bug Fix 3500192
91 , tbi.wk_mth_indicator wk_mth_indicator
92 -- Bug Fix 4618981
93 , tbi.exemption_indicator exemption_indicator
94 , tbi.tot_tax_to_date tot_tax_to_date
95 , tbi.tot_pay_to_date tot_pay_to_date
96 , tbi.std_rate_of_tax std_rate_of_tax
97 , tbi.higher_rate_of_tax higher_rate_of_tax
98 --13359423
99 ,tbi.cd_yrly_tax_cred
100 ,tbi.cd_yrly_tax_rate_1
101 ,tbi.cd_yrly_tax_rate_2
102 ,tbi.cd_mthly_tax_rate_2
103 ,tbi.cd_wkly_tax_rate_2
104 ,tbi.cd_tax_rate_3
105 ,tbi.cd_yrly_tax_rate_3
106 ,tbi.cd_mthly_tax_rate_3
107 ,tbi.cd_wkly_tax_rate_3
108 ,tbi.cd_tax_rate_4
109 ,tbi.cd_yrly_tax_rate_4
110 ,tbi.cd_mthly_tax_rate_4
111 ,tbi.cd_wkly_tax_rate_4
112 ,tbi.cd_tax_rate_5
113 ,tbi.in_exempt_usc
114 ,tbi.cd_total_usc_pay_todate
115 ,tbi.cd_total_usc_tax_todate
116 ,tbi.cd_usc_rate_1
117 ,tbi.am_usc_yrly_cutoff_1
118 ,tbi.am_usc_mthly_cutoff_1
119 ,tbi.am_usc_wkly_cutoff_1
120 ,tbi.cd_usc_rate_2
121 ,tbi.am_usc_yrly_cutoff_2
122 ,tbi.am_usc_mthly_cutoff_2
123 ,tbi.am_usc_wkly_cutoff_2
124 ,tbi.cd_usc_rate_3
125 ,tbi.am_usc_yrly_cutoff_3
126 ,tbi.am_usc_mthly_cutoff_3
127 ,tbi.am_usc_wkly_cutoff_3
128 ,tbi.cd_usc_rate_4
129 ,tbi.am_usc_yrly_cutoff_4
130 ,tbi.am_usc_mthly_cutoff_4
131 ,tbi.am_usc_wkly_cutoff_4
132 ,tbi.cd_usc_rate_5
133 --13359423
134 FROM hr_organization_information hoi
135 , hr_organization_units hou
136 , per_all_assignments_f asg
137 , per_all_people_f per
138 , pay_all_payrolls_f pay
139 --, pay_ie_paye_details_f ppd
140 , pay_ie_tax_body_interface tbi
141 , pay_ie_tax_header_interface thi
142 WHERE per.person_id = asg.person_id
143 AND per.national_identifier = tbi.pps_number
144 AND asg.business_group_id = hou.business_group_id
145 AND hou.organization_id = hoi.organization_id
146 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
147 AND hoi.org_information2 = p_employer_number
148 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
149 AND asg.payroll_id = pay.payroll_id
150 --AND asg.assignment_id = ppd.assignment_id
151 AND per.national_identifier = p_pps_number
152 -- Bug Fix 3500192
153 -- added for multiple assignment issue 5894942
154 AND asg.assignment_number = tbi.works_number
155 AND thi.employer_number = p_employer_number
156 AND thi.tax_year = p_tax_year
157 AND tbi.employer_number = thi.employer_number
158 AND tbi.process_flag = 'Y'
159 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
160 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
161 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
162 AND per.effective_end_date >= trunc(tbi.cert_start_date)
163 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
164 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
165 --bug 6376140
166 /*Declare cursor to retrieve all employee details from payroll
167 and interface tables based on input parameters for a single assignment*/
168 CURSOR c_pay1(p_pps_number varchar) IS
169 SELECT distinct per.employee_number employee_no_hr
170 , per.national_identifier pps_number_hr
171 , per.last_name last_name_hr
172 , per.first_name first_name_hr
173 , asg.assignment_id assignment_id
174 --, asg.effective_start_date effective_start_date
175 , hoi.org_information1 tax_district
176 , pay.payroll_name payroll_name_hr
177 , pay.payroll_id payroll_id -- 4878630
178 --, ppd.paye_details_id paye_details_id --4878630
179 --, ppd.object_version_number object_version_no --4878630
180 --, ppd.effective_start_date ppd_effective_start_date
181 , tbi.pps_number pps_number_int
182 , asg.assignment_number employee_no_int --5724436
183 , tbi.first_name first_name_int
184 , tbi.last_name last_name_int
185 , tbi.cert_start_date cert_start_date
186 , tbi.cert_end_date cert_end_date
187 , tbi.cert_date cert_date
188 , tbi.wk_tax_credit wk_tax_credit
189 , tbi.mth_tax_credit mth_tax_credit
190 , tbi.wk_rate_cutoff wk_rate_cutoff
191 , tbi.mth_rate_cutoff mth_rate_cutoff
192 -- Bug Fix 3500192
193 , tbi.wk_mth_indicator wk_mth_indicator
194 -- Bug Fix 4618981
195 , tbi.exemption_indicator exemption_indicator
196 , tbi.tot_tax_to_date tot_tax_to_date
197 , tbi.tot_pay_to_date tot_pay_to_date
198 , tbi.std_rate_of_tax std_rate_of_tax
199 , tbi.higher_rate_of_tax higher_rate_of_tax
200 --13359423
201 ,tbi.cd_yrly_tax_cred
202 ,tbi.cd_yrly_tax_rate_1
203 ,tbi.cd_yrly_tax_rate_2
204 ,tbi.cd_mthly_tax_rate_2
205 ,tbi.cd_wkly_tax_rate_2
206 ,tbi.cd_tax_rate_3
207 ,tbi.cd_yrly_tax_rate_3
208 ,tbi.cd_mthly_tax_rate_3
209 ,tbi.cd_wkly_tax_rate_3
210 ,tbi.cd_tax_rate_4
211 ,tbi.cd_yrly_tax_rate_4
212 ,tbi.cd_mthly_tax_rate_4
213 ,tbi.cd_wkly_tax_rate_4
214 ,tbi.cd_tax_rate_5
215 ,tbi.in_exempt_usc
216 ,tbi.cd_total_usc_pay_todate
217 ,tbi.cd_total_usc_tax_todate
218 ,tbi.cd_usc_rate_1
219 ,tbi.am_usc_yrly_cutoff_1
220 ,tbi.am_usc_mthly_cutoff_1
221 ,tbi.am_usc_wkly_cutoff_1
222 ,tbi.cd_usc_rate_2
223 ,tbi.am_usc_yrly_cutoff_2
224 ,tbi.am_usc_mthly_cutoff_2
225 ,tbi.am_usc_wkly_cutoff_2
226 ,tbi.cd_usc_rate_3
227 ,tbi.am_usc_yrly_cutoff_3
228 ,tbi.am_usc_mthly_cutoff_3
229 ,tbi.am_usc_wkly_cutoff_3
230 ,tbi.cd_usc_rate_4
231 ,tbi.am_usc_yrly_cutoff_4
232 ,tbi.am_usc_mthly_cutoff_4
233 ,tbi.am_usc_wkly_cutoff_4
234 ,tbi.cd_usc_rate_5
235 --13359423
236 FROM hr_organization_information hoi
237 , hr_organization_units hou
238 , per_all_assignments_f asg
239 , per_all_people_f per
240 , pay_all_payrolls_f pay
241 --, pay_ie_paye_details_f ppd
242 , pay_ie_tax_body_interface tbi
243 , pay_ie_tax_header_interface thi,
244 per_periods_of_service pps
245 WHERE per.person_id = asg.person_id
246 AND per.national_identifier = tbi.pps_number
247 AND asg.business_group_id = hou.business_group_id
248 AND hou.organization_id = hoi.organization_id
249 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
250 AND hoi.org_information2 = p_employer_number
251 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
252 AND asg.payroll_id = pay.payroll_id
253 --AND asg.assignment_id = ppd.assignment_id
254 AND per.national_identifier = p_pps_number
255 -- Bug Fix 3500192
256 -- added for multiple assignment issue 5894942
257 --AND asg.assignment_number = tbi.works_number
258 AND thi.employer_number = p_employer_number
259 AND thi.tax_year = p_tax_year
260 AND tbi.employer_number = thi.employer_number
261 AND tbi.process_flag = 'Y'
262 and asg.period_of_service_id=pps.period_of_service_id
263 and pps.person_id=per.person_id
264 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
265 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'))
266 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
267 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
268 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
269 AND per.effective_end_date >= trunc(tbi.cert_start_date)
270 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
271 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
272
273 -- cursor get the skipped assignments. Assignments that didnt get processed
274 -- in the process
275 --bug 6376140 cursor modified for processing single assg
276 /* cursor csr_skipped_asg is
277 select pps_number, works_number from pay_ie_tax_body_interface
278 minus
279 SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
280 FROM hr_organization_information hoi
281 , hr_organization_units hou
282 , per_all_assignments_f asg
283 , per_all_people_f per
284 , pay_all_payrolls_f pay
285 --, pay_ie_paye_details_f ppd
286 , pay_ie_tax_body_interface tbi
287 , pay_ie_tax_header_interface thi
288 WHERE per.person_id = asg.person_id
289 AND asg.business_group_id = hou.business_group_id
290 AND hou.organization_id = hoi.organization_id
291 AND hoi.org_information_context = 'IE_EMPLOYER_INFO'
292 AND hoi.org_information2 = p_employer_number
293 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
294 AND asg.payroll_id = pay.payroll_id
295 AND per.national_identifier = tbi.pps_number
296 AND asg.assignment_number = tbi.works_number
297 AND thi.employer_number = p_employer_number
298 AND thi.tax_year = p_tax_year
299 AND tbi.employer_number = thi.employer_number
300 AND tbi.process_flag = 'Y'
301 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
302 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
303 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
304 AND per.effective_end_date >= trunc(tbi.cert_start_date)
305 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
306 AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
307
308 cursor csr_skipped_assignments is
309 select distinct pps_number pps_number,works_number,last_name,first_name
310 from pay_ie_tax_body_interface;
311
312 /* check to see if any single paye details exists */
313 cursor get_paye_details (p_assignment_id number) is
314 select count(*) from pay_ie_paye_details_f where
315 assignment_id = p_assignment_id;
316
317 -- For Bug 5724436
318 -- Cursor to get the max assignment action id, to fetch the P45 details.
319 cursor get_p45_details (p_assignment_id number) is
320 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa
321 from pay_assignment_actions paa,
322 pay_payroll_actions ppa
323 where ppa.payroll_action_id = paa.payroll_action_id
324 and paa.assignment_id = p_assignment_id
325 and to_number(to_char(ppa.effective_date,'YYYY')) = p_tax_year;
326
327 -- Cursor to fetch existing PAYE details.
328 cursor c_get_paye_details(p_assignment_id number
329 ,p_ppsn varchar2
330 ,p_cert_start_date date
331 ,p_assignment_number varchar2) is
332 select distinct ppd.*
333 from per_all_people_f papf,
334 per_all_assignments_f paaf,
335 pay_ie_paye_details_f ppd
336 where papf.national_identifier = p_ppsn
337 and papf.person_id = paaf.person_id
338 and paaf.assignment_id = p_assignment_id
339 and paaf.assignment_id = ppd.assignment_id
340 and paaf.assignment_number = p_assignment_number
341 AND paaf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
342 AND paaf.effective_end_date >= trunc(p_cert_start_date)
343 AND papf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
344 AND papf.effective_end_date >= trunc(p_cert_start_date)
345 AND ppd.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
346 AND ppd.effective_end_date >= trunc(p_cert_start_date);
347
348 -- Cursor to see if payroll exists for an assignment.
349 CURSOR csr_pay_freq (p_assignment_id NUMBER,
350 p_effective_date DATE) IS
351 SELECT pp.period_type
352 FROM pay_payrolls_f pp, per_assignments_f pa
353 WHERE pa.assignment_id = p_assignment_id
354 AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
355 AND pp.payroll_id = pa.payroll_id
356 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
357
358 pay_freq_rec csr_pay_freq%ROWTYPE;
359
360 -- cursor get values from globals bug 5766334
361 cursor csr_get_global_value(p_global_name varchar2,
362 p_cert_date date) is
363 select global_value
364 from ff_globals_f
365 where global_name = p_global_name
366 and p_cert_date between effective_start_date and effective_end_date;
367
368 l_tax_rate_exempt ff_globals_f.global_value%TYPE;
369 l_tax_rate_high ff_globals_f.global_value%TYPE;
370 --bug 6376140
371 --follw variables are added
372 r_pay c_pay%rowtype;
373 TYPE t_pps_number IS TABLE OF varchar2(30) INDEX BY Binary_Integer;
374 l_pps_number t_pps_number;
375 ppsno_cnt number :=0;
376 flag char :='N';
377 --end of bug 6376140
378 -- End bug 5766334
379
380 --Declare variables
381 l_error VARCHAR2(2000);
382 l_error_stack VARCHAR2(2000) := NULL;
383 l_request_id NUMBER;
384 l_program_application_id NUMBER;
385 l_program_id NUMBER;
386 l_comm_period_no NUMBER;
387 l_pps_number_hr VARCHAR2(9);
388 l_employee_number_hr per_all_people_F.employee_number%TYPE; -- bug 5766372
389 l_last_name_hr per_people_f.last_name%TYPE;
390 l_first_name_hr per_people_f.first_name%TYPE;
391 l_last_name_int VARCHAR2(20);
392 l_first_name_int VARCHAR2(20);
393 l_payroll_name_hr VARCHAR2(80);
394 l_tax_district NUMBER;
395 l_pps_number_int VARCHAR2(9);
396 l_employee_number_int per_all_assignments_f.assignment_number%TYPE; -- bug 5766372
397 l_validate BOOLEAN := FALSE;
398 -- Bug Fix 3500192
399 l_datetrack_mode VARCHAR2(12);
400 l_tax_basis pay_ie_paye_details_f.tax_basis%TYPE; -- bug 5766372
401 l_header_count NUMBER := 0;
402 l_record_count NUMBER := 0;
403 l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%TYPE;
404 l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%TYPE;
405
406 l_usc_tax_basis pay_ie_paye_details_f.usc_tax_basis%TYPE;
407 --Declare output parameters from api row handlersb
408 l_ins_paye_details_id NUMBER;
409 l_ins_object_version_no NUMBER;
410 l_ins_effective_start_date DATE;
411 l_ins_effective_end_date DATE;
412 l_upd_effective_start_date DATE;
413 l_upd_effective_end_date DATE;
414 l_flag number;
415
416 -- Bug Fix 3500192
417 -- name_not_equal EXCEPTION;
418 -- same_day EXCEPTION;
419 future_day EXCEPTION;
420 std_rate_of_tax_is_null EXCEPTION;
421 higher_rate_of_tax_is_null EXCEPTION;
422 exemption_is_null EXCEPTION;
423 exemption_mismatch EXCEPTION;
424 normal_tax_mismatch EXCEPTION;
425 pay_to_date EXCEPTION;
426 l_paye_count NUMBER(3);
427 o_paye_details_id NUMBER;
428 o_ovn NUMBER;
429 o_effective_start_date DATE;
430 o_effective_end_date DATE;
431 l_effective_date DATE; -- Bug 6929566
432
433 l_tax_to_date NUMBER;
434 l_pay_to_date NUMBER;
435 l_max_action_id NUMBER := 0;
436 r_paye_details c_get_paye_details%ROWTYPE;
437 r_empty_details c_get_paye_details%ROWTYPE;
438
439 --13359423
440 l_usc_pay_todate NUMBER;
441 l_usc_tax_todate NUMBER;
442 --13359423
443
444 BEGIN
445 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
446 retcode := 1;
447 -- Bug 5724436, the audit report will be called only in mode="Validate"
448 IF p_validate_mode <> 'IE_VALIDATE' THEN
449 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11, ' ')||lpad('Works Number',15,' ')||lpad('Status',30,' ')); --4878630
450 fnd_file.put_line(fnd_file.output,lpad('----------',11, ' ')||lpad('------------',15,' ')||lpad('------',30,' '));
451 ELSE
452 -- bug 5724436
453 -- This is called only once to set the report fields.
454 fnd_file.put_line(fnd_file.output,'Index');
455 fnd_file.put_line(fnd_file.output,'I :- Week1/Month1 Indicator');
456 fnd_file.put_line(fnd_file.output,'F :- Exemption Flag');
457 fnd_file.put_line(fnd_file.output,'Tax1 :- Standard Rate of Tax');
458 fnd_file.put_line(fnd_file.output,'Tax2 :- Higher Rate of Tax');
459 fnd_file.put_line(fnd_file.output,'U :- USC Exemption Flag');
460 fnd_file.put_line(fnd_file.output,' ');
461
462 fnd_file.put_line(fnd_file.output,lpad('PAYE, USC Details in Oracle Payroll',70,' ')||lpad('PAYE, USC Details from Revenue',191,' '));
463 fnd_file.put_line(fnd_file.output,lpad('===================================',70,' ')||lpad('==============================',191,' '));
464 fnd_file.put_line(fnd_file.output,' ');
465 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11,' ')
466 -- for previous PAYE Details
467 || lpad('Works',13,' ')
468 || lpad('Last Name',16,' ')
469 || lpad('I',3,' ')||lpad('F',3,' ')
470 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
471 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
472 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
473 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
474 || lpad('Tax2',6,' ')||lpad('U',3,' ')||lpad('USC Mth',9,' ')||lpad('USC Wk',9,' ')||lpad('USC Mth',9,' ')||lpad('USC Wk',9,' ')
475 ||lpad('Tot USC',14,' ')||lpad('Tot USC',13,' ')
476 || lpad('USC',7,' ')||lpad('USC',7,' ')||lpad('USC',7,' ')
477 || lpad(' ',10,' ')
478 -- for Current PAYE Details
479 || lpad('I',3,' ')||lpad('F',3,' ')
480 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
481 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
482 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
483 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
484 || lpad('Tax2',6,' ')||lpad('U',3,' ')||lpad('USC Mth',9,' ')||lpad('USC Wk',9,' ')||lpad('USC Mth',9,' ')||lpad('USC Wk',9,' ')
485 ||lpad('Tot USC',14,' ')||lpad('Tot USC',13,' ')
486 || lpad('USC',7,' ')||lpad('USC',7,' ')||lpad('USC',7,' ')
487 );
488
489 fnd_file.put_line(fnd_file.output, lpad('Number',24,' ')
490 || lpad('Cutoff',30,' ')||lpad('Credit',9,' ')
491 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
492 || lpad('Date',10,' ')||lpad('to Date',15,' ')
493 || lpad('to Date',12,' ')||lpad('Cutoff1',24,' ')||lpad('Cutoff1',9,' ')||lpad('Cutoff2',9,' ')||lpad('Cutoff2',9,' ')
494 ||lpad('pay to Date',14,' ')||lpad('Tax to Date',13,' ')
495 || lpad('Rate1',7,' ')||lpad('Rate2',7,' ')||lpad('Rate3',7,' ')
496 ||lpad(' ',16,' ')
497 -- for Current PAYE Details
498 || lpad('Cutoff',8,' ')||lpad('Credit',9,' ')
499 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
500 || lpad('Date',10,' ')||lpad('to Date',15,' ')
501 || lpad('to Date',12,' ')||lpad('Cutoff1',24,' ')||lpad('Cutoff1',9,' ')||lpad('Cutoff2',9,' ')||lpad('Cutoff2',9,' ')
502 ||lpad('pay to Date',14,' ')||lpad('Tax to Date',13,' ')
503 || lpad('Rate1',7,' ')||lpad('Rate2',7,' ')||lpad('Rate3',7,' ')
504 );
505
506 fnd_file.put_line(fnd_file.output,lpad('----------',11,' ')
507 -- for previous PAYE Details
508 || lpad('----------',13,' ')
509 || lpad('---------',16,' ')
510 || lpad('-',3,' ')||lpad('-',3,' ')
511 || lpad('-------',9,' ')||lpad('-------',9,' ')
512 || lpad('--------',10,' ')||lpad('--------',10,' ')
513 || lpad('----------',12,' ')||lpad('-------',12,' ')
514 || lpad('-------',12,' ')||lpad('----',6,' ')
515 || lpad('----',6,' ')||lpad('-',3,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')
516 ||lpad('-----------',14,' ')||lpad('-----------',13,' ')
517 || lpad('-----',7,' ')||lpad('-----',7,' ')||lpad('-----',7,' ')
518 || lpad(' ',10,' ')
519 -- for Current PAYE Details
520 || lpad('-',3,' ')||lpad('-',3,' ')
521 || lpad('-------',9,' ')||lpad('-------',9,' ')
522 || lpad('--------',10,' ')||lpad('--------',10,' ')
523 || lpad('----------',12,' ')||lpad('-------',12,' ')
524 || lpad('-------',12,' ')||lpad('----',6,' ')
525 || lpad('----',6,' ')||lpad('-',3,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')||lpad('-------',9,' ')
526 ||lpad('-----------',14,' ')||lpad('-----------',13,' ')
527 || lpad('-----',7,' ')||lpad('-----',7,' ')||lpad('-----',7,' ')
528 );
529
530 g_validate_count := 1;
531 END IF;
532 -- END 5724436
533 --BUG 6652299 ADDED L_FLAG
534 FOR r_no_of_assg IN c_no_of_assg
535 LOOP
536 BEGIN
537 l_flag :=0;
538 --bug 6376140
539 IF r_no_of_assg.no_of_assg =1
540 THEN OPEN c_pay1(r_no_of_assg.pps_number);
541 FETCH c_pay1 INTO r_pay;
542 IF c_pay1%FOUND THEN
543 l_flag:=1;
544 END IF;
545 CLOSE c_pay1;
546 ELSIF r_no_of_assg.no_of_assg >1
547 THEN OPEN c_pay(r_no_of_assg.pps_number);
548 FETCH c_pay INTO r_pay;
549 IF c_pay%FOUND THEN
550 l_flag:=1;
551 END IF;
552 CLOSE c_pay;
553 END IF;
554 --end if;
555
556 --end of bug 6376140
557
558
559 -- Bug 6929566 Start
560 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_date is ' || r_pay.cert_date);
561 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_start_date is ' || r_pay.cert_start_date);
562 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_end_date is ' || r_pay.cert_end_date);
563 IF r_pay.cert_date < r_pay.cert_start_date THEN
564 l_effective_date := r_pay.cert_start_date;
565 ELSIF r_pay.cert_date >= r_pay.cert_start_date THEN
566 l_effective_date := r_pay.cert_date;
567 END IF;
568 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_effective_date is ' || l_effective_date);
569 --
570 -- Bug 6929566 End
571
572 --FOR r_pay IN c_pay
573 --LOOP
574 --BEGIN
575 --IF r_pay.pps_number_hr IS NOT NULL THEN
576 --Initialize local variables on each loop pass to pass to outer exception handler
577 IF l_flag=1 THEN
578
579 l_pps_number_hr := r_pay.pps_number_hr;
580 l_employee_number_hr := r_pay.employee_no_hr;
581 l_first_name_hr := r_pay.first_name_hr;
582 l_last_name_hr := r_pay.last_name_hr;
583 l_payroll_name_hr := r_pay.payroll_name_hr;
584 l_tax_district := r_pay.tax_district;
585 l_pps_number_int := r_pay.pps_number_int;
586 l_employee_number_int := r_pay.employee_no_int;
587 --l_ins_object_version_no := r_pay.object_version_no;
588 l_std_rate_of_tax := r_pay.std_rate_of_tax;
589 l_higher_rate_of_tax := r_pay.higher_rate_of_tax;
590 --bug 6376140
591 --ppsno_cnt := ppsno_cnt+1;
592 IF l_pps_number_hr IS NOT NULL THEN
593 ppsno_cnt := ppsno_cnt+1;
594 l_pps_number(ppsno_cnt) :=r_pay.pps_number_hr;
595 --fnd_file.put_line(l_pps_number(ppsno_cnt));
596 hr_utility.set_location('PPS Number..'||l_pps_number_hr,420);
597 -- checks for standard rate tax to be mandatory
598 IF l_std_rate_of_tax IS NULL THEN
599 raise std_rate_of_tax_is_null;
600 END IF; --l_std_rate_of_tax
601 -- checks for higher rate of tax to be mandatory
602 IF l_higher_rate_of_tax IS NULL THEN
603 raise higher_rate_of_tax_is_null;
604 END IF;--l_higher_rate_of_tax
605 -- checks for exemption indicator to be mandatory
606 IF r_pay.exemption_indicator is null then
607 raise exemption_is_null; --r_pay.exemption_indicator
608 END IF;
609 -- checks for exact rate of tax bug 5766334
610 open csr_get_global_value('IE_TAX_RATE_EXEMPT',l_effective_date); -- Bug 6929566
611 FETCH csr_get_global_value into l_tax_rate_exempt;
612 CLOSE csr_get_global_value;
613
614 open csr_get_global_value('IE_TAX_RATE2', l_effective_date); -- Bug 6929566
615 FETCH csr_get_global_value into l_tax_rate_high;
616 CLOSE csr_get_global_value;
617
618 -- end bug 5766334
619
620 IF r_pay.exemption_indicator='Y' AND r_pay.higher_rate_of_tax <> l_tax_rate_exempt then
621 raise exemption_mismatch;
622 END IF; --r_pay.exemption_indicator
623 -- checks for exact rate of tax
624 IF r_pay.exemption_indicator='N' AND r_pay.higher_rate_of_tax <> l_tax_rate_high then
625 raise normal_tax_mismatch;
626 END IF; --r_pay.exemption_indicator
627 hr_utility.set_location('PPS Number..Initial check'||l_pps_number_hr,421);
628 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,101);
629 --
630 IF r_pay.tot_pay_to_date is null and r_pay.tot_pay_to_date is null and
631 (r_pay.wk_mth_indicator = 0 or (r_pay.wk_mth_indicator=1 and r_pay.exemption_indicator='Y')) then
632 raise pay_to_date;
633 END IF;
634 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,422);
635
636 -- check if cerificate start date is before or equal to certificate end date
637
638 pay_ipd_bus.chk_cert_start_end_dates(
639 p_certificate_start_date => r_pay.cert_start_date
640 , p_certificate_end_date => r_pay.cert_end_date
641 );
642 hr_utility.set_location('PPS Number..third check'||l_pps_number_hr,423);
643 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,102);
644 -- Bug Fix 3500192
645 -- tax basis is set as per the value of week month indicator in the interface table
646 IF (r_pay.wk_mth_indicator = 1) THEN
647 l_tax_basis := 'IE_WEEK1_MONTH1';
648 ELSE
649 l_tax_basis := 'IE_CUMULATIVE';
650 END IF;
651 -- Bug Fix 4618981
652 IF r_pay.exemption_indicator='Y' then
653 IF r_pay.wk_mth_indicator = 1 THEN
654 l_tax_basis := 'IE_EXEMPT_WEEK_MONTH';
655 ELSE
656 l_tax_basis := 'IE_EXEMPTION';
657 END IF;
658 END IF;
659
660 IF r_pay.IN_EXEMPT_USC = 'Y' THEN
661 l_usc_tax_basis := 'IE_EXEMPTION';
662 ELSE
663 IF r_pay.wk_mth_indicator = 1 THEN
664 l_usc_tax_basis := 'IE_WEEK1_MONTH1';
665 ELSE
666 l_usc_tax_basis := 'IE_CUMULATIVE';
667 END IF;
668 END IF;
669
670 hr_utility.set_location('l_tax_basis'||l_tax_basis,424);
671 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,103);
672
673 -- check if amounts are valid for the given tax basis, for 'Emergency'
674 -- tax basis weekly and monthly tax credits ans std rate cut-off amounts must
675 -- be null and for other values of tax basis weekly or monthly amounts
676 -- (depending on payroll frequency) must be not null.
677
678 /*pay_ipd_bus.chk_tax_basis_amounts(
679 p_effective_date => r_pay.cert_start_date
680 , p_assignment_id => r_pay.assignment_id
681 -- Bug Fix 3500192
682 --, p_tax_basis => 'IE_CUMULATIVE'
683 , p_tax_basis => l_tax_basis
684 , p_weekly_tax_credit => r_pay.wk_tax_credit
685 , p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
686 , p_monthly_tax_credit => r_pay.mth_tax_credit
687 , p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
688 );*/
689
690 hr_utility.set_location('pay_ipd_bus.chk_tax_basis_amounts'||l_pps_number_hr,424);
691 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,104);
692 /* If the validate mode is 'Validate and Rollback' then set the validation input paramter
693 to true else for all modes it is set to false*/
694
695 IF p_validate_mode = 'IE_VALIDATE_ROLLBACK' THEN
696 l_validate := TRUE;
697 ELSE
698 l_validate := FALSE;
699 END IF;
700
701 --IF (r_pay.paye_details_id IS NOT NULL AND
702 IF (p_validate_mode = 'IE_VALIDATE_ROLLBACK' OR p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
703 hr_utility.set_location('PPS Number..'||l_pps_number_hr,425);
704 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,105);
705
706 OPEN get_paye_details(r_pay.assignment_id);
707 FETCH get_paye_details INTO l_paye_count;
708 CLOSE get_paye_details;
709 -- to check if the payroll is attached to the assignment as of certificate
710 -- issue date. This is called irrespective of whether PAYE details exists
711 -- or not.
712 pay_ipd_bus.chk_tax_basis_amounts(
713 p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5396580
714 ,p_assignment_id => r_pay.assignment_id
715 -- Bug Fix 3500192
716 -- , p_tax_basis => 'IE_CUMULATIVE'
717 ,p_tax_basis => l_tax_basis
718 ,p_weekly_tax_credit => r_pay.wk_tax_credit
719 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
720 ,p_monthly_tax_credit => r_pay.mth_tax_credit
721 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
722 );
723 IF l_paye_count <> 0 THEN
724 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,426);
725
726 hr_utility.set_location(r_pay.cert_date, 10);
727
728 pay_ie_paye_pkg.update_paye_change_freq --4878630
729 (p_assignment_id => r_pay.assignment_id
730 ,p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5724436
731 ,p_payroll_id => r_pay.payroll_id
732 ,P_DATETRACK_UPDATE_MODE => 'UPDATE'
733 ,p_tax_upload_flag => 'TU'
734 ,p_tax_basis => l_tax_basis
735 ,p_cert_start_date => r_pay.cert_start_date -- 17140460.6
736 ,p_cert_end_date => r_pay.cert_end_date
737 ,p_weekly_tax_credit => r_pay.wk_tax_credit
738 ,p_monthly_tax_credit => r_pay.mth_tax_credit
739 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
740 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
741 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
742 ,p_pay_to_date => r_pay.tot_pay_to_date
743 ,p_cert_date =>r_pay.cert_date
744 --13359423
745 ,p_yrly_tax_cred => r_pay.cd_yrly_tax_cred
746 ,p_yrly_tax_rate_1 => r_pay.cd_yrly_tax_rate_1
747 ,p_yrly_tax_rate_2 => r_pay.cd_yrly_tax_rate_2
748 ,p_mthly_tax_rate_2 => r_pay.cd_mthly_tax_rate_2
749 ,p_wkly_tax_rate_2 => r_pay.cd_wkly_tax_rate_2
750 ,p_tax_rate_3 => r_pay.cd_tax_rate_3
751 ,p_yrly_tax_rate_3 => r_pay.cd_yrly_tax_rate_3
752 ,p_mthly_tax_rate_3 => r_pay.cd_mthly_tax_rate_3
753 ,p_wkly_tax_rate_3 => r_pay.cd_wkly_tax_rate_3
754 ,p_tax_rate_4 => r_pay.cd_tax_rate_4
755 ,p_yrly_tax_rate_4 => r_pay.cd_yrly_tax_rate_4
756 ,p_mthly_tax_rate_4 => r_pay.cd_mthly_tax_rate_4
757 ,p_wkly_tax_rate_4 => r_pay.cd_wkly_tax_rate_4
758 ,p_tax_rate_5 => r_pay.cd_tax_rate_5
759 ,p_in_exempt_usc => r_pay.in_exempt_usc
760 ,p_total_usc_pay_todate => r_pay.cd_total_usc_pay_todate
761 ,p_total_usc_tax_todate => r_pay.cd_total_usc_tax_todate
762 ,p_usc_rate_1 => r_pay.cd_usc_rate_1
763 ,p_usc_yrly_cutoff_1 => r_pay.am_usc_yrly_cutoff_1
764 ,p_usc_mthly_cutoff_1 => r_pay.am_usc_mthly_cutoff_1
765 ,p_usc_wkly_cutoff_1 => r_pay.am_usc_wkly_cutoff_1
766 ,p_usc_rate_2 => r_pay.cd_usc_rate_2
767 ,p_usc_yrly_cutoff_2 => r_pay.am_usc_yrly_cutoff_2
768 ,p_usc_mthly_cutoff_2 => r_pay.am_usc_mthly_cutoff_2
769 ,p_usc_wkly_cutoff_2 => r_pay.am_usc_wkly_cutoff_2
770 ,p_usc_rate_3 => r_pay.cd_usc_rate_3
771 ,p_usc_yrly_cutoff_3 => r_pay.am_usc_yrly_cutoff_3
772 ,p_usc_mthly_cutoff_3 => r_pay.am_usc_mthly_cutoff_3
773 ,p_usc_wkly_cutoff_3 => r_pay.am_usc_wkly_cutoff_3
774 ,p_usc_rate_4 => r_pay.cd_usc_rate_4
775 ,p_usc_yrly_cutoff_4 => r_pay.am_usc_yrly_cutoff_4
776 ,p_usc_mthly_cutoff_4 => r_pay.am_usc_mthly_cutoff_4
777 ,p_usc_wkly_cutoff_4 => r_pay.am_usc_wkly_cutoff_4
778 ,p_usc_rate_5 => r_pay.cd_usc_rate_5
779 ,p_usc_tax_basis => l_usc_tax_basis
780 ,p_usc_info_source => 'IE_ELECTRONIC'
781 --13359423
782 );
783
784 hr_utility.set_location(r_pay.cert_date, 20);
785 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,427);
786 ELSE
787 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,428);
788 hr_utility.set_location(r_pay.cert_date, 30);
789 pay_ie_paye_api.create_ie_paye_details --4878630
790 (p_validate => false
791 ,p_effective_date => l_effective_date -- Bug 6929566
792 ,p_assignment_id => r_pay.assignment_id
793 ,p_info_source => 'IE_ELECTRONIC'
794 ,p_tax_basis => l_tax_basis
795 ,p_certificate_start_date => r_pay.cert_start_date -- For bug 5396549
796 ,p_tax_assess_basis => 'IE_SEP_TREAT'
797 ,p_certificate_issue_date => r_pay.cert_date
798 ,p_certificate_end_date => r_pay.cert_end_date
799 ,p_weekly_tax_credit => r_pay.wk_tax_credit
800 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
801 ,p_monthly_tax_credit => r_pay.mth_tax_credit
802 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
803 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
804 ,p_pay_to_date => r_pay.tot_pay_to_date
805 ,p_disability_benefit => null
806 ,p_lump_sum_payment => null
807 ,p_paye_details_id => o_paye_details_id
808 ,p_object_version_number => o_ovn
809 ,p_effective_start_date => o_effective_start_date
810 ,p_effective_end_date => o_effective_end_date
811 --13359423
812 ,p_yrly_tax_cred => r_pay.cd_yrly_tax_cred
813 ,p_yrly_tax_rate_1 => r_pay.cd_yrly_tax_rate_1
814 ,p_yrly_tax_rate_2 => r_pay.cd_yrly_tax_rate_2
815 ,p_mthly_tax_rate_2 => r_pay.cd_mthly_tax_rate_2
816 ,p_wkly_tax_rate_2 => r_pay.cd_wkly_tax_rate_2
817 ,p_tax_rate_3 => r_pay.cd_tax_rate_3
818 ,p_yrly_tax_rate_3 => r_pay.cd_yrly_tax_rate_3
819 ,p_mthly_tax_rate_3 => r_pay.cd_mthly_tax_rate_3
820 ,p_wkly_tax_rate_3 => r_pay.cd_wkly_tax_rate_3
821 ,p_tax_rate_4 => r_pay.cd_tax_rate_4
822 ,p_yrly_tax_rate_4 => r_pay.cd_yrly_tax_rate_4
823 ,p_mthly_tax_rate_4 => r_pay.cd_mthly_tax_rate_4
824 ,p_wkly_tax_rate_4 => r_pay.cd_wkly_tax_rate_4
825 ,p_tax_rate_5 => r_pay.cd_tax_rate_5
826 ,p_in_exempt_usc => r_pay.in_exempt_usc
827 ,p_total_usc_pay_todate => r_pay.cd_total_usc_pay_todate
828 ,p_total_usc_tax_todate => r_pay.cd_total_usc_tax_todate
829 ,p_usc_rate_1 => r_pay.cd_usc_rate_1
830 ,p_usc_yrly_cutoff_1 => r_pay.am_usc_yrly_cutoff_1
831 ,p_usc_mthly_cutoff_1 => r_pay.am_usc_mthly_cutoff_1
832 ,p_usc_wkly_cutoff_1 => r_pay.am_usc_wkly_cutoff_1
833 ,p_usc_rate_2 => r_pay.cd_usc_rate_2
834 ,p_usc_yrly_cutoff_2 => r_pay.am_usc_yrly_cutoff_2
835 ,p_usc_mthly_cutoff_2 => r_pay.am_usc_mthly_cutoff_2
836 ,p_usc_wkly_cutoff_2 => r_pay.am_usc_wkly_cutoff_2
837 ,p_usc_rate_3 => r_pay.cd_usc_rate_3
838 ,p_usc_yrly_cutoff_3 => r_pay.am_usc_yrly_cutoff_3
839 ,p_usc_mthly_cutoff_3 => r_pay.am_usc_mthly_cutoff_3
840 ,p_usc_wkly_cutoff_3 => r_pay.am_usc_wkly_cutoff_3
841 ,p_usc_rate_4 => r_pay.cd_usc_rate_4
842 ,p_usc_yrly_cutoff_4 => r_pay.am_usc_yrly_cutoff_4
843 ,p_usc_mthly_cutoff_4 => r_pay.am_usc_mthly_cutoff_4
844 ,p_usc_wkly_cutoff_4 => r_pay.am_usc_wkly_cutoff_4
845 ,p_usc_rate_5 => r_pay.cd_usc_rate_5
846 ,p_usc_tax_basis => l_usc_tax_basis
847 ,p_usc_info_source => 'IE_ELECTRONIC'
848 --13359423
849 );
850 hr_utility.set_location(r_pay.cert_date, 40);
851 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,429);
852 END IF;
853 END IF;
854 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,106);
855 retcode := 0;
856
857
858 /*Update interface table and set processed flag to 'Yes' to record that record has been updated or
859 inserted into payroll tables successfully using the row handler APIs.*/
860
861 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
862 UPDATE pay_ie_tax_body_interface
863 SET processed_flag = 'Y'
864 WHERE pps_number = r_pay.pps_number_int;
865 END IF;
866 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,107);
867 IF p_validate_mode <> 'IE_VALIDATE' THEN
868 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');
869 ELSE
870 IF g_validate_count =1 then
871 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,841);
872 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,108);
873 OPEN c_get_paye_details(r_pay.assignment_id,
874 r_pay.pps_number_int,
875 l_effective_date, -- Bug 6929566 -- r_pay.cert_date,
876 r_pay.employee_no_int);
877 FETCH c_get_paye_details INTO r_paye_details;
878 -- IF no PAYE details exists then set the default values for PAYE.
879 IF c_get_paye_details%ROWCOUNT = 0 then
880 r_paye_details.tax_basis := 'IE_EMERGENCY';
881 r_paye_details.certificate_issue_date := to_date('01/01/0001','dd/mm/yyyy');
882 r_paye_details.WEEKLY_TAX_CREDIT := 0;
883 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
884 r_paye_details.MONTHLY_TAX_CREDIT := 0;
885 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
886 -- bug 5837091
887 ELSIF r_paye_details.tax_basis in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS') then
888 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'));
889 r_paye_details.WEEKLY_TAX_CREDIT := 0;
890 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
891 r_paye_details.MONTHLY_TAX_CREDIT := 0;
892 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
893 else
894 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'));
895 r_paye_details.WEEKLY_TAX_CREDIT := nvl(r_paye_details.WEEKLY_TAX_CREDIT,0);
896 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := nvl(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,0);
897 r_paye_details.MONTHLY_TAX_CREDIT := nvl(r_paye_details.MONTHLY_TAX_CREDIT,0);
898 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := nvl(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,0);
899 END IF;
900 -- end bug 5837091
901 CLOSE c_get_paye_details;
902 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,842);
903
904 OPEN csr_pay_freq (r_pay.assignment_id,l_effective_date); -- Bug 6929566 --r_pay.cert_date);
905 FETCH csr_pay_freq INTO pay_freq_rec;
906 --
907 IF csr_pay_freq%NOTFOUND THEN
908 CLOSE csr_pay_freq;
909 hr_utility.set_message(801, 'HR_IE_ASG_NOT_IN_PAYROLL');
910 hr_utility.raise_error;
911 END IF;
912 --
913 CLOSE csr_pay_freq;
914 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,843);
915
916 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,109);
917 OPEN get_p45_details(r_pay.assignment_id);
918 FETCH get_p45_details INTO l_max_action_id;
919 CLOSE get_p45_details;
920
921 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,110);
922 hr_utility.set_location('Assignment Action ID..'||l_max_action_id,110);
923 -- This will be called only if has any assignment actions.
924 IF l_max_action_id <> 0 THEN
925 hr_utility.set_location('Assignment Action ID is not null',112);
926 l_pay_to_date := NVL (
927 ROUND (
928 TO_NUMBER (
929 pay_balance_pkg.get_value (
930 pay_ie_p35.get_defined_balance_id (
931 '_ASG_YTD',
932 'IE P45 Pay'
933 ),
934 l_max_action_id
935 )
936 ),
937 2
938 ),
939 0
940 );
941 l_tax_to_date := NVL (
942 ROUND (
943 TO_NUMBER (
944 pay_balance_pkg.get_value (
945 pay_ie_p35.get_defined_balance_id (
946 '_ASG_YTD',
947 'IE P45 Tax Deducted'
948 ),
949 l_max_action_id
950 )
951 ),
952 2
953 ),
954 0
955 );
956 --13359423
957 l_usc_pay_todate := NVL (
958 ROUND (
959 TO_NUMBER (
960 pay_balance_pkg.get_value (
961 pay_ie_p35.get_defined_balance_id (
962 '_ASG_YTD',
963 'IE P45 USC Pay'
964 ),
965 l_max_action_id
966 )
967 ),
968 2
969 ),
970 0
971 );
972 l_usc_tax_todate := NVL (
973 ROUND (
974 TO_NUMBER (
975 pay_balance_pkg.get_value (
976 pay_ie_p35.get_defined_balance_id (
977 '_ASG_YTD',
978 'IE P45 USC Deducted'
979 ),
980 l_max_action_id
981 )
982 ),
983 2
984 ),
985 0
986 );
987 --13359423
988
989 ELSE
990 hr_utility.set_location('Assignment Action ID is null',113);
991 l_pay_to_date := 0;
992 l_tax_to_date := 0;
993
994 --13359423
995 l_usc_pay_todate := 0;
996 l_usc_tax_todate := 0;
997 --13359423
998
999 END IF;
1000 -- print the values.
1001 fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')
1002 -- for previous PAYE Details
1003 || lpad(substr(r_pay.employee_no_int,1,12),13,' ')
1004 || lpad(substr(r_pay.last_name_hr,1,15),16,' ')
1005 || lpad(pay_ie_paye_pkg.decode_value_char(r_paye_details.tax_basis='IE_WEEK1_MONTH1','1','0'),3,' ')
1006 || 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,' ')
1007 || lpad(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,9,' ')||lpad(r_paye_details.MONTHLY_TAX_CREDIT,9,' ')
1008 || lpad(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,10,' ')||lpad(r_paye_details.WEEKLY_TAX_CREDIT,10,' ')
1009 || 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,' ')
1010 || lpad(to_char(l_pay_to_date),12,' ')
1011 || lpad(to_char(l_tax_to_date),12,' ')||lpad('20',6,' ')
1012 || 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,' ')
1013 || lpad(NVL(r_paye_details.in_exempt_usc,'N'),3,' ')
1014 || lpad(NVL(r_paye_details.USC_MTHLY_CUTOFF_1,0),9,' ')||lpad(NVL(r_paye_details.USC_WKLY_CUTOFF_1,0),9,' ')|| lpad(NVL(r_paye_details.USC_MTHLY_CUTOFF_2,0),9,' ')||lpad(NVL(r_paye_details.USC_WKLY_CUTOFF_2,0),9,' ')
1015 || lpad(NVL(l_usc_pay_todate,0),14,' ')||lpad(NVL(l_usc_tax_todate,0),13,' ') --13359423
1016 || lpad(NVL(r_paye_details.USC_RATE_1,0),7,' ')||lpad(NVL(r_paye_details.USC_RATE_2,0),7,' ')||lpad(NVL(r_paye_details.USC_RATE_3,0),7,' ')
1017 || lpad(' ',10,' ')
1018 -- for Current PAYE Details
1019 || lpad(r_pay.wk_mth_indicator,3,' ')||lpad(r_pay.exemption_indicator,3,' ')
1020 || lpad(r_pay.mth_rate_cutoff,9,' ')||lpad(r_pay.mth_tax_credit,9,' ')
1021 || lpad(r_pay.wk_rate_cutoff,10,' ')||lpad(r_pay.wk_tax_credit,10,' ')
1022 || lpad(to_char(r_pay.cert_date,'dd-mm-yyyy'),12,' ')||lpad(r_pay.tot_pay_to_date,12,' ')
1023 || lpad(r_pay.tot_tax_to_date,12,' ')||lpad(r_pay.std_rate_of_tax,6,' ')
1024 || lpad(r_pay.higher_rate_of_tax,6,' ')
1025 ||lpad(r_pay.in_exempt_usc,3,' ')
1026 || lpad(r_pay.AM_USC_MTHLY_CUTOFF_1,9,' ')||lpad(r_pay.AM_USC_WKLY_CUTOFF_1,9,' ')|| lpad(r_pay.AM_USC_MTHLY_CUTOFF_2,9,' ')||lpad(r_pay.AM_USC_WKLY_CUTOFF_2,9,' ')
1027 ||lpad(r_pay.CD_TOTAL_USC_PAY_TODATE,14,' ')||lpad(r_pay.CD_TOTAL_USC_TAX_TODATE,13,' ')
1028 || lpad(r_pay.CD_USC_RATE_1,7,' ')||lpad(r_pay.CD_USC_RATE_2,7,' ')||lpad(r_pay.CD_USC_RATE_3,7,' ')
1029 );
1030
1031
1032 END IF;
1033 r_paye_details := r_empty_details;
1034 END IF;
1035 END IF;
1036 END IF;
1037 -- end bug 5724436.
1038 EXCEPTION
1039 -- Bug Fix 3500192
1040 -- WHEN name_not_equal THEN
1041 -- l_error := SQLERRM;
1042 -- retcode := 1;
1043 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1044 -- FND_FILE.PUT_LINE(fnd_file.log, 'The first name and last name in the interface body table does not match
1045 -- the first and last name in the payroll tables');
1046 --
1047 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1048 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1049 -- r_pay.employee_no_hr ||', '||
1050 -- r_pay.last_name_hr ||' '||
1051 -- r_pay.first_name_hr ||', '||
1052 -- r_pay.last_name_int ||' '||
1053 -- r_pay.first_name_int||', '||
1054 -- r_pay.payroll_name_hr
1055 -- );
1056 --
1057 -- -- The exception details are written to an error table
1058 --
1059 -- INSERT INTO pay_ie_tax_error ( pps_number
1060 -- , employee_number
1061 -- , full_name
1062 -- , payroll_name
1063 -- , tax_district
1064 -- , error_stack_message
1065 -- , error_message
1066 -- , request_id
1067 -- , error_date )
1068 -- VALUES (r_pay.pps_number_hr
1069 -- , r_pay.employee_no_hr
1070 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1071 -- , r_pay.payroll_name_hr
1072 -- , r_pay.tax_district
1073 -- , l_error_stack
1074 -- , l_error
1075 -- , l_request_id
1076 -- , sysdate);
1077 -- COMMIT;
1078 --
1079 -- WHEN same_day THEN
1080 -- l_error := SQLERRM;
1081 -- retcode := 1;
1082 -- FND_FILE.PUT_LINE(fnd_file.log, 'This record has already been updated today with changes to the
1083 -- PAY_IE_PAYE_DETAILS_F table');
1084 --
1085 --
1086 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
1087 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1088 -- r_pay.employee_no_hr ||', '||
1089 -- r_pay.last_name_hr ||' '||
1090 -- r_pay.first_name_hr ||', '||
1091 -- r_pay.ppd_effective_start_date ||', '||
1092 -- r_pay.payroll_name_hr
1093 -- );
1094 --
1095 -- -- The exception details are written to an error table
1096 --
1097 -- INSERT INTO pay_ie_tax_error ( pps_number
1098 -- , employee_number
1099 -- , full_name
1100 -- , payroll_name
1101 -- , tax_district
1102 -- , error_stack_message
1103 -- , error_message
1104 -- , request_id
1105 -- , error_date )
1106 -- VALUES (r_pay.pps_number_hr
1107 -- , r_pay.employee_no_hr
1108 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1109 -- , r_pay.payroll_name_hr
1110 -- , r_pay.tax_district
1111 -- , l_error_stack
1112 -- , l_error
1113 -- , l_request_id
1114 -- , sysdate);
1115 -- COMMIT;
1116
1117 /*WHEN future_day THEN
1118 l_error := SQLERRM;
1119 retcode := 1;
1120 FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');
1121
1122 FND_FILE.NEW_LINE(fnd_file.log, 1);
1123 FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1124 r_pay.employee_no_hr ||', '||
1125 r_pay.last_name_hr ||' '||
1126 r_pay.first_name_hr ||', '||
1127 r_pay.payroll_name_hr
1128 );
1129 -- The exception details are written to an error table
1130
1131 INSERT INTO pay_ie_tax_error ( pps_number
1132 , employee_number
1133 , full_name
1134 , payroll_name
1135 , tax_district
1136 , error_stack_message
1137 , error_message
1138 , request_id
1139 , error_date )
1140 VALUES (r_pay.pps_number_hr
1141 , r_pay.employee_no_hr
1142 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1143 , r_pay.payroll_name_hr
1144 , r_pay.tax_district
1145 , l_error_stack
1146 , l_error
1147 , l_request_id
1148 , sysdate);
1149 COMMIT;*/
1150
1151 WHEN std_rate_of_tax_is_null THEN
1152 l_error := 'Standard Rate of Tax cannot be Null';--SQLERRM;
1153 retcode := 1;
1154 FND_FILE.PUT_LINE(fnd_file.log, 'Standard Rate of Tax cannot be Null');
1155
1156 FND_FILE.NEW_LINE(fnd_file.log, 1);
1157 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1158 r_pay.employee_no_hr ||', '||
1159 r_pay.last_name_hr ||' '||
1160 r_pay.first_name_hr ||', '||
1161 r_pay.payroll_name_hr
1162 );*/
1163 --IF p_validate_mode in ('IE_VALIDATE') THEN
1164 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1165 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1166 l_err_tab(err_cnt).p_err_msg := 'Failed : Standard Rate of Tax cannot be Null';
1167 err_cnt := err_cnt + 1;
1168 --ELSE
1169 -- 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');
1170 --END IF;
1171 -- The exception details are written to an error table
1172
1173 INSERT INTO pay_ie_tax_error ( pps_number
1174 , employee_number
1175 , full_name
1176 , payroll_name
1177 , tax_district
1178 , error_stack_message
1179 , error_message
1180 , request_id
1181 , error_date )
1182 VALUES (r_pay.pps_number_hr
1183 , substr(r_pay.employee_no_int,1,12)
1184 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1185 , r_pay.payroll_name_hr
1186 , r_pay.tax_district
1187 , l_error_stack
1188 , l_error
1189 , l_request_id
1190 , sysdate);
1191 COMMIT;
1192
1193 WHEN higher_rate_of_tax_is_null THEN
1194 l_error := 'Higher Rate of Tax cannot be Null';--SQLERRM;
1195 retcode := 1;
1196 FND_FILE.PUT_LINE(fnd_file.log, 'Higher Rate of Tax cannot be Null');
1197
1198 FND_FILE.NEW_LINE(fnd_file.log, 1);
1199 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1200 r_pay.employee_no_hr ||', '||
1201 r_pay.last_name_hr ||' '||
1202 r_pay.first_name_hr ||', '||
1203 r_pay.payroll_name_hr
1204 );*/
1205
1206 --IF p_validate_mode = 'IE_VALIDATE' THEN
1207 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1208 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1209 l_err_tab(err_cnt).p_err_msg := 'Failed : Higher Rate of Tax cannot be Null';
1210 err_cnt := err_cnt + 1;
1211 --ELSE
1212 -- 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');
1213 --END IF;
1214
1215 -- The exception details are written to an error table
1216 INSERT INTO pay_ie_tax_error ( pps_number
1217 , employee_number
1218 , full_name
1219 , payroll_name
1220 , tax_district
1221 , error_stack_message
1222 , error_message
1223 , request_id
1224 , error_date )
1225 VALUES (r_pay.pps_number_hr
1226 , substr(r_pay.employee_no_int,1,12)
1227 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1228 , r_pay.payroll_name_hr
1229 , r_pay.tax_district
1230 , l_error_stack
1231 , l_error
1232 , l_request_id
1233 , sysdate);
1234 COMMIT;
1235
1236 WHEN exemption_is_null THEN
1237
1238 l_error := 'Exemption Indicator cannot be Null';--SQLERRM;
1239 retcode := 1;
1240 FND_FILE.PUT_LINE(fnd_file.log, 'Exemption Indicator cannot be Null');
1241
1242 FND_FILE.NEW_LINE(fnd_file.log, 1);
1243 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1244 r_pay.employee_no_hr ||', '||
1245 r_pay.last_name_hr ||' '||
1246 r_pay.first_name_hr ||', '||
1247 r_pay.payroll_name_hr
1248 );*/
1249
1250 --IF p_validate_mode = 'IE_VALIDATE' THEN
1251 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1252 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1253 l_err_tab(err_cnt).p_err_msg := 'Failed : Exemption Indicator cannot be Null';
1254 err_cnt := err_cnt + 1;
1255 --ELSE
1256 -- 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');
1257 --END IF;
1258
1259 -- The exception details are written to an error table
1260 INSERT INTO pay_ie_tax_error ( pps_number
1261 , employee_number
1262 , full_name
1263 , payroll_name
1264 , tax_district
1265 , error_stack_message
1266 , error_message
1267 , request_id
1268 , error_date )
1269 VALUES (r_pay.pps_number_hr
1270 , substr(r_pay.employee_no_int,1,12)
1271 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1272 , r_pay.payroll_name_hr
1273 , r_pay.tax_district
1274 , l_error_stack
1275 , l_error
1276 , l_request_id
1277 , sysdate);
1278 COMMIT;
1279
1280 WHEN exemption_mismatch THEN
1281 l_error := 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';--SQLERRM;
1282 retcode := 1;
1283 FND_FILE.PUT_LINE(fnd_file.log, 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%');
1284
1285 FND_FILE.NEW_LINE(fnd_file.log, 1);
1286 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1287 r_pay.employee_no_hr ||', '||
1288 r_pay.last_name_hr ||' '||
1289 r_pay.first_name_hr ||', '||
1290 r_pay.payroll_name_hr
1291 );*/
1292
1293 --IF p_validate_mode = 'IE_VALIDATE' THEN
1294 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1295 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1296 l_err_tab(err_cnt).p_err_msg := 'Failed : The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';
1297 err_cnt := err_cnt + 1;
1298 --ELSE
1299 -- 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||'%');
1300 --END IF;
1301
1302 -- The exception details are written to an error table
1303 INSERT INTO pay_ie_tax_error ( pps_number
1304 , employee_number
1305 , full_name
1306 , payroll_name
1307 , tax_district
1308 , error_stack_message
1309 , error_message
1310 , request_id
1311 , error_date )
1312 VALUES (r_pay.pps_number_hr
1313 , substr(r_pay.employee_no_int,1,12)
1314 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1315 , r_pay.payroll_name_hr
1316 , r_pay.tax_district
1317 , l_error_stack
1318 , l_error
1319 , l_request_id
1320 , sysdate);
1321 COMMIT;
1322
1323 WHEN normal_tax_mismatch THEN
1324 l_error := 'The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%';--SQLERRM;
1325 retcode := 1;
1326 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||'%');
1327
1328 FND_FILE.NEW_LINE(fnd_file.log, 1);
1329 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1330 r_pay.employee_no_hr ||', '||
1331 r_pay.last_name_hr ||' '||
1332 r_pay.first_name_hr ||', '||
1333 r_pay.payroll_name_hr
1334 );*/
1335 -- The exception details are written to an error table
1336 --IF p_validate_mode = 'IE_VALIDATE' THEN
1337 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1338 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1339 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||'%';
1340 err_cnt := err_cnt + 1;
1341 --ELSE
1342 -- 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||'%');
1343 --END IF;
1344
1345 -- The exception details are written to an error table
1346 INSERT INTO pay_ie_tax_error ( pps_number
1347 , employee_number
1348 , full_name
1349 , payroll_name
1350 , tax_district
1351 , error_stack_message
1352 , error_message
1353 , request_id
1354 , error_date )
1355 VALUES (r_pay.pps_number_hr
1356 , substr(r_pay.employee_no_int,1,12)
1357 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1358 , r_pay.payroll_name_hr
1359 , r_pay.tax_district
1360 , l_error_stack
1361 , l_error
1362 , l_request_id
1363 , sysdate);
1364 COMMIT;
1365
1366 WHEN pay_to_date THEN
1367 l_error := 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.';--SQLERRM;
1368 retcode := 1;
1369 FND_FILE.PUT_LINE(fnd_file.log, 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.');
1370
1371 FND_FILE.NEW_LINE(fnd_file.log, 1);
1372 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1373 r_pay.employee_no_hr ||', '||
1374 r_pay.last_name_hr ||' '||
1375 r_pay.first_name_hr ||', '||
1376 r_pay.payroll_name_hr
1377 );*/
1378
1379 --IF p_validate_mode = 'IE_VALIDATE' THEN
1380 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1381 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1382 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';
1383 err_cnt := err_cnt + 1;
1384 --ELSE
1385 -- 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');
1386 --END IF;
1387
1388 -- The exception details are written to an error table
1389 INSERT INTO pay_ie_tax_error ( pps_number
1390 , employee_number
1391 , full_name
1392 , payroll_name
1393 , tax_district
1394 , error_stack_message
1395 , error_message
1396 , request_id
1397 , error_date )
1398 VALUES (r_pay.pps_number_hr
1399 , substr(r_pay.employee_no_int,1,12)
1400 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1401 , r_pay.payroll_name_hr
1402 , r_pay.tax_district
1403 , l_error_stack
1404 , l_error
1405 , l_request_id
1406 , sysdate);
1407 COMMIT;
1408
1409 WHEN OTHERS THEN
1410 errbuf := fnd_message.get;
1411 l_error_stack := errbuf;
1412 l_error := SQLERRM;
1413
1414 /*Update interface table and set processed flag to 'No' to record that record has not been updated
1415 or inserted into payroll tables*/
1416
1417 IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
1418 UPDATE pay_ie_tax_body_interface
1419 SET processed_flag = 'N'
1420 WHERE pps_number = r_pay.pps_number_int;
1421 END IF;
1422 -- The following command will be used to output the exception details to an output file:
1423
1424 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
1425 r_pay.pps_number_int ||', '||
1426 r_pay.employee_no_hr ||', '||
1427 r_pay.pps_number_int ||', '||
1428 r_pay.last_name_hr ||' '||
1429 r_pay.first_name_hr ||', '||
1430 r_pay.pps_number_int ||', '||
1431 r_pay.payroll_name_hr ||', '||
1432 r_pay.pps_number_int
1433 );*/
1434 --IF p_validate_mode = 'IE_VALIDATE' THEN
1435 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
1436 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
1437 l_err_tab(err_cnt).p_err_msg := 'Failed : '||l_error;
1438 err_cnt := err_cnt + 1;
1439 --ELSE
1440 -- 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);
1441 --END IF;
1442 -- The exception details are written to an error table
1443
1444 INSERT INTO pay_ie_tax_error ( pps_number
1445 , employee_number
1446 , full_name
1447 , payroll_name
1448 , tax_district
1449 , error_stack_message
1450 , error_message
1451 , request_id
1452 , error_date )
1453 VALUES (r_pay.pps_number_hr
1454 , substr(r_pay.employee_no_int,1,12)
1455 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
1456 , r_pay.payroll_name_hr
1457 , r_pay.tax_district
1458 , l_error_stack
1459 , l_error
1460 , l_request_id
1461 , sysdate);
1462 COMMIT;
1463
1464 retcode := 1; -- 6215901
1465
1466 END;
1467 --end if;
1468 END LOOP;
1469
1470
1471 IF l_err_tab.COUNT <> 0 then
1472 for i in l_err_tab.first..l_err_tab.last
1473 loop
1474 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);
1475 end loop;
1476 END IF;
1477 --bug 6376140
1478 /* for i in csr_skipped_asg
1479 loop
1480 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');
1481 end loop;
1482 */
1483 FOR i in csr_skipped_assignments
1484 LOOP
1485 flag :='N';
1486 IF(l_pps_number.count<>0) then
1487 FOR j in l_pps_number.first..l_pps_number.last
1488 LOOP
1489 IF l_pps_number(j)=i.pps_number THEN
1490 flag :='Y';
1491 END IF;
1492 EXIT WHEN flag='Y';
1493 END LOOP;
1494 END IF;
1495 IF flag='N' THEN
1496 -- open check_pps(i.pps_number);
1497 -- FETCH check_pps INTO p_check_pps
1498 -- if check_pps%found then
1499 fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||lpad(substr(nvl(i.works_number,' '),1,12),13,' ')
1500 || 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');
1501 -- else
1502 -- else
1503 --fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||'Please check the employee''s PPS number');
1504 -- end if;
1505 -- close check_pps;
1506 END IF;
1507 END LOOP;
1508 --end of bug 6376140
1509
1510 -- Bug Fix 3500192
1511 -- Writes the trailer record in the log file
1512 IF l_header_count = 1 THEN
1513 log_ie_paye_footer(l_record_count);
1514 END IF;
1515
1516 /* If user selects the mode to be 'Validate and Commit' then
1517 p_validate_mode = 'IE_VALIDATE_COMMIT' then records are committed else records are rolled back */
1518
1519 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
1520 COMMIT;
1521 ELSE
1522 ROLLBACK;
1523 END IF;
1524
1525
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 l_error := SQLERRM;
1529 retcode := 1;
1530 FND_FILE.PUT_LINE(fnd_file.log, 'No data found');
1531
1532 INSERT INTO pay_ie_tax_error ( pps_number
1533 , employee_number
1534 , full_name
1535 , payroll_name
1536 , tax_district
1537 , error_stack_message
1538 , error_message
1539 , request_id
1540 , error_date)
1541 VALUES (l_pps_number_hr
1542 , substr(l_employee_number_hr,1,12)
1543 , l_last_name_hr ||' '|| l_first_name_hr
1544 , l_payroll_name_hr
1545 , l_tax_district
1546 , l_error_stack
1547 , l_error
1548 , l_request_id
1549 , sysdate
1550 );
1551 COMMIT;
1552
1553 WHEN OTHERS THEN
1554
1555 errbuf := fnd_message.get;
1556 l_error_stack := errbuf;
1557 l_error := SQLERRM;
1558 retcode := 2;
1559 /* The following command will be used to output the exception details to an output file*/
1560
1561 /*FND_FILE.PUT_LINE(fnd_file.output, l_pps_number_hr ||', '||
1562 l_pps_number_int ||', '||
1563 l_employee_number_hr ||', '||
1564 l_employee_number_int ||', '||
1565 l_last_name_hr ||' '||l_first_name_hr ||', '||
1566 l_last_name_int ||' '|| l_first_name_int ||', '||
1567 l_payroll_name_hr ||', '||
1568 l_tax_district
1569 );*/
1570 --IF p_validate_mode = 'IE_VALIDATE' THEN
1571 l_err_tab(err_cnt).p_pps_number := l_pps_number_int;
1572 l_err_tab(err_cnt).p_works_number := substr(l_employee_number_int,1,12);
1573 l_err_tab(err_cnt).p_err_msg := 'Failed : OTHER in Main..'||l_error;
1574 err_cnt := err_cnt + 1;
1575 --ELSE
1576 -- 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);
1577 --END IF;
1578 /* The exception details are written to an error table */
1579 INSERT INTO pay_ie_tax_error ( pps_number
1580 , employee_number
1581 , full_name
1582 , payroll_name
1583 , tax_district
1584 , error_stack_message
1585 , error_message
1586 , request_id
1587 , error_date)
1588 VALUES (l_pps_number_hr
1589 , substr(l_employee_number_hr,1,12)
1590 , l_last_name_hr ||' '||l_first_name_hr
1591 , l_payroll_name_hr
1592 , l_tax_district
1593 , l_error_stack
1594 , l_error
1595 , l_request_id
1596 , sysdate);
1597 COMMIT;
1598 END valinsupd_new;
1599
1600
1601
1602 PROCEDURE count_validation_new(
1603 errbuf OUT NOCOPY VARCHAR2
1604 , retcode OUT NOCOPY VARCHAR2
1605 , p_employer_number IN VARCHAR2
1606 , p_tax_year IN pay_ie_tax_header_interface.tax_year%TYPE) IS
1607
1608 -- Cursor to get the total values form body
1609 CURSOR c_body
1610 IS
1611 SELECT COUNT(PBS.MTH_TAX_CREDIT) count_mth_taxcredit
1612 , SUM (NVL(PBS.MTH_RATE_CUTOFF,0)) sum_mth_rate_cutoff
1613 , SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
1614 , SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
1615 , SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
1616 --
1617 ,SUM(NVL(PBS.CD_YRLY_TAX_CRED,0)) sum_YRLY_TAX_CRED
1618 ,SUM(NVL(PBS.CD_YRLY_TAX_RATE_1,0)) sum_YRLY_TAX_RATE_1
1619 ,SUM(NVL(PBS.CD_YRLY_TAX_RATE_2,0)) sum_YRLY_TAX_RATE_2
1620 ,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_2,0)) sum_MTHLY_TAX_RATE_2
1621 ,SUM(NVL(PBS.CD_WKLY_TAX_RATE_2,0)) sum_WKLY_TAX_RATE_2
1622 ,SUM(NVL(PBS.CD_YRLY_TAX_RATE_3,0)) sum_YRLY_TAX_RATE_3
1623 ,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_3,0)) sum_MTHLY_TAX_RATE_3
1624 ,SUM(NVL(PBS.CD_WKLY_TAX_RATE_3,0)) sum_WKLY_TAX_RATE_3
1625 ,SUM(NVL(PBS.CD_YRLY_TAX_RATE_4,0)) sum_YRLY_TAX_RATE_4
1626 ,SUM(NVL(PBS.CD_MTHLY_TAX_RATE_4,0)) sum_MTHLY_TAX_RATE_4
1627 ,SUM(NVL(PBS.CD_WKLY_TAX_RATE_4,0)) sum_WKLY_TAX_RATE_4
1628 ,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_1,0)) sum_USC_YRLY_CUTOFF_1
1629 ,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_2,0)) sum_USC_YRLY_CUTOFF_2
1630 ,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_3,0)) sum_USC_YRLY_CUTOFF_3
1631 ,SUM(NVL(PBS.AM_USC_YRLY_CUTOFF_4,0)) sum_USC_YRLY_CUTOFF_4
1632 --
1633 FROM PAY_IE_TAX_HEADER_INTERFACE phs
1634 , PAY_IE_TAX_BODY_INTERFACE pbs
1635 WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
1636 AND PHS.TAX_YEAR = p_tax_year
1637 AND PBS.EMPLOYER_NUMBER = p_employer_number;
1638
1639 -- Cursor to get the total values from trailer table
1640 CURSOR c_trailer
1641 IS
1642 SELECT PTS.RECORD_NO count_emp_recno
1643 , NVL(PTS.TOTAL_MTH_RATE_CUTOFF,0) total_mth_cutoff
1644 , NVL(PTS.TOTAL_WK_RATE_CUTOFF,0) total_wk_cutoff
1645 , NVL(PTS.TOTAL_MTH_TAX_CREDIT,0) total_mth_credit
1646 , NVL(PTS.TOTAL_WK_TAX_CREDIT,0) total_wk_credit
1647 --
1648 ,NVL(PTS.TC_TOT_YEARLY_TAX_CRED,0) total_YEARLY_TAX_CRED
1649 ,NVL(PTS.TOT_YEARLY_TAX_RATE_1,0) total_YEARLY_TAX_RATE_1
1650 ,NVL(PTS.TOT_YEARLY_TAX_RATE_2,0) total_YEARLY_TAX_RATE_2
1651 ,NVL(PTS.TOT_MONTHLY_TAX_RATE_2,0) total_MONTHLY_TAX_RATE_2
1652 ,NVL(PTS.TOT_WEEKLY_TAX_RATE_2,0) total_WEEKLY_TAX_RATE_2
1653 ,NVL(PTS.TOT_YEARLY_TAX_RATE_3,0) total_YEARLY_TAX_RATE_3
1654 ,NVL(PTS.TOT_MONTHLY_TAX_RATE_3,0) total_MONTHLY_TAX_RATE_3
1655 ,NVL(PTS.TOT_WEEKLY_TAX_RATE_3,0) total_WEEKLY_TAX_RATE_3
1656 ,NVL(PTS.TOT_YEARLY_TAX_RATE_4,0) total_YEARLY_TAX_RATE_4
1657 ,NVL(PTS.TOT_MONTHLY_TAX_RATE_4,0) total_MONTHLY_TAX_RATE_4
1658 ,NVL(PTS.TOT_WEEKLY_TAX_RATE_4,0) total_WEEKLY_TAX_RATE_4
1659 ,NVL(PTS.TOT_YEARLY_USC_RATE_1,0) total_YEARLY_USC_RATE_1
1660 ,NVL(PTS.TOT_YEARLY_USC_RATE_2,0) total_YEARLY_USC_RATE_2
1661 ,NVL(PTS.TOT_YEARLY_USC_RATE_3,0) total_YEARLY_USC_RATE_3
1662 ,NVL(PTS.TOT_YEARLY_USC_RATE_4,0) total_YEARLY_USC_RATE_4
1663 --
1664 FROM PAY_IE_TAX_HEADER_INTERFACE phs
1665 , PAY_IE_TAX_TRAILER_INTERFACE pts
1666 WHERE PTS.EMPLOYER_NUMBER = p_employer_number
1667 AND PHS.TAX_YEAR = p_tax_year;
1668
1669 l_error_stack VARCHAR2 (2000);
1670 l_error VARCHAR2 (80);
1671 l_request_id NUMBER;
1672
1673 unequal_value EXCEPTION;
1674 BodyRec c_body%rowtype;
1675 TrailRec c_trailer%rowtype;
1676
1677 --header
1678 l_count_mth_taxcredit BodyRec.count_mth_taxcredit%type;
1679 l_sum_mth_rate_cutoff BodyRec.sum_mth_rate_cutoff%type;
1680 l_sum_wk_rate_cutoff BodyRec.sum_wk_rate_cutoff%type;
1681 l_sum_mth_tax_credit BodyRec.sum_mth_tax_credit%type;
1682 l_sum_wk_tax_credit BodyRec.sum_wk_tax_credit%type;
1683
1684 l_sum_YRLY_TAX_CRED BodyRec.sum_YRLY_TAX_CRED%type;
1685 l_sum_YRLY_TAX_RATE_1 BodyRec.sum_YRLY_TAX_RATE_1%type;
1686 l_sum_YRLY_TAX_RATE_2 BodyRec.sum_YRLY_TAX_RATE_2%type;
1687 l_sum_MTHLY_TAX_RATE_2 BodyRec.sum_MTHLY_TAX_RATE_2%type;
1688 l_sum_WKLY_TAX_RATE_2 BodyRec.sum_WKLY_TAX_RATE_2%type;
1689 l_sum_YRLY_TAX_RATE_3 BodyRec.sum_YRLY_TAX_RATE_3%type;
1690 l_sum_MTHLY_TAX_RATE_3 BodyRec.sum_MTHLY_TAX_RATE_3%type;
1691 l_sum_WKLY_TAX_RATE_3 BodyRec.sum_WKLY_TAX_RATE_3%type;
1692 l_sum_YRLY_TAX_RATE_4 BodyRec.sum_YRLY_TAX_RATE_4%type;
1693 l_sum_MTHLY_TAX_RATE_4 BodyRec.sum_MTHLY_TAX_RATE_4%type;
1694 l_sum_WKLY_TAX_RATE_4 BodyRec.sum_WKLY_TAX_RATE_4%type;
1695 l_sum_USC_YRLY_CUTOFF_1 BodyRec.sum_USC_YRLY_CUTOFF_1%type;
1696 l_sum_USC_YRLY_CUTOFF_2 BodyRec.sum_USC_YRLY_CUTOFF_2%type;
1697 l_sum_USC_YRLY_CUTOFF_3 BodyRec.sum_USC_YRLY_CUTOFF_3%type;
1698 l_sum_USC_YRLY_CUTOFF_4 BodyRec.sum_USC_YRLY_CUTOFF_4%type;
1699
1700 --trailer
1701 l_count_emp_recno TrailRec.count_emp_recno%type;
1702 l_total_mth_cutoff TrailRec.total_mth_cutoff%type;
1703 l_total_wk_cutoff TrailRec.total_wk_cutoff%type;
1704 l_total_mth_credit TrailRec.total_mth_credit%type;
1705 l_total_wk_credit TrailRec.total_wk_credit%type;
1706 --
1707 l_total_YEARLY_TAX_CRED TrailRec.total_YEARLY_TAX_CRED%type;
1708 l_total_YEARLY_TAX_RATE_1 TrailRec.total_YEARLY_TAX_RATE_1%type;
1709 l_total_YEARLY_TAX_RATE_2 TrailRec.total_YEARLY_TAX_RATE_2%type;
1710 l_total_MONTHLY_TAX_RATE_2 TrailRec.total_MONTHLY_TAX_RATE_2%type;
1711 l_total_WEEKLY_TAX_RATE_2 TrailRec.total_WEEKLY_TAX_RATE_2%type;
1712 l_total_YEARLY_TAX_RATE_3 TrailRec.total_YEARLY_TAX_RATE_3%type;
1713 l_total_MONTHLY_TAX_RATE_3 TrailRec.total_MONTHLY_TAX_RATE_3%type;
1714 l_total_WEEKLY_TAX_RATE_3 TrailRec.total_WEEKLY_TAX_RATE_3%type;
1715 l_total_YEARLY_TAX_RATE_4 TrailRec.total_YEARLY_TAX_RATE_4%type;
1716 l_total_MONTHLY_TAX_RATE_4 TrailRec.total_MONTHLY_TAX_RATE_4%type;
1717 l_total_WEEKLY_TAX_RATE_4 TrailRec.total_WEEKLY_TAX_RATE_4%type;
1718 l_total_YEARLY_USC_RATE_1 TrailRec.total_YEARLY_USC_RATE_1%type;
1719 l_total_YEARLY_USC_RATE_2 TrailRec.total_YEARLY_USC_RATE_2%type;
1720 l_total_YEARLY_USC_RATE_3 TrailRec.total_YEARLY_USC_RATE_3%type;
1721 l_total_YEARLY_USC_RATE_4 TrailRec.total_YEARLY_USC_RATE_4%type;
1722 --
1723
1724 BEGIN
1725 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1726
1727 Begin
1728 Delete from pay_ie_tax_error;
1729 if sql%rowcount > 0 then
1730 commit;
1731 end if;
1732 exception
1733 when others then
1734 FND_FILE.PUT_LINE(fnd_file.log,'Error occured while deleting exisiting rows in PAY_IE_TAX_ERROR table.');
1735 end;
1736
1737 OPEN c_body;
1738 FETCH c_body into BodyRec;
1739 IF (c_body%NOTFOUND) THEN
1740 RAISE NO_DATA_FOUND;
1741 END IF;
1742
1743 l_count_mth_taxcredit := BodyRec.count_mth_taxcredit;
1744 l_sum_mth_rate_cutoff := BodyRec.sum_mth_rate_cutoff;
1745 l_sum_wk_rate_cutoff := BodyRec.sum_wk_rate_cutoff;
1746 l_sum_mth_tax_credit := BodyRec.sum_mth_tax_credit;
1747 l_sum_wk_tax_credit := BodyRec.sum_wk_tax_credit;
1748 --
1749 l_sum_YRLY_TAX_CRED := BodyRec.sum_YRLY_TAX_CRED;
1750 l_sum_YRLY_TAX_RATE_1 := BodyRec.sum_YRLY_TAX_RATE_1;
1751 l_sum_YRLY_TAX_RATE_2 := BodyRec.sum_YRLY_TAX_RATE_2;
1752 l_sum_MTHLY_TAX_RATE_2 := BodyRec.sum_MTHLY_TAX_RATE_2;
1753 l_sum_WKLY_TAX_RATE_2 := BodyRec.sum_WKLY_TAX_RATE_2;
1754 l_sum_YRLY_TAX_RATE_3 := BodyRec.sum_YRLY_TAX_RATE_3;
1755 l_sum_MTHLY_TAX_RATE_3 := BodyRec.sum_MTHLY_TAX_RATE_3;
1756 l_sum_WKLY_TAX_RATE_3 := BodyRec.sum_WKLY_TAX_RATE_3;
1757 l_sum_YRLY_TAX_RATE_4 := BodyRec.sum_YRLY_TAX_RATE_4;
1758 l_sum_MTHLY_TAX_RATE_4 := BodyRec.sum_MTHLY_TAX_RATE_4;
1759 l_sum_WKLY_TAX_RATE_4 := BodyRec.sum_WKLY_TAX_RATE_4;
1760 l_sum_USC_YRLY_CUTOFF_1 := BodyRec.sum_USC_YRLY_CUTOFF_1;
1761 l_sum_USC_YRLY_CUTOFF_2 := BodyRec.sum_USC_YRLY_CUTOFF_2;
1762 l_sum_USC_YRLY_CUTOFF_3 := BodyRec.sum_USC_YRLY_CUTOFF_3;
1763 l_sum_USC_YRLY_CUTOFF_4 := BodyRec.sum_USC_YRLY_CUTOFF_4;
1764 --
1765
1766 OPEN c_trailer;
1767
1768 FETCH c_trailer into TrailRec;
1769 IF (c_trailer%NOTFOUND) THEN
1770 RAISE NO_DATA_FOUND;
1771 END IF;
1772
1773 l_count_emp_recno := TrailRec.count_emp_recno;
1774 l_total_mth_cutoff := TrailRec.total_mth_cutoff;
1775 l_total_wk_cutoff := TrailRec.total_wk_cutoff;
1776 l_total_mth_credit := TrailRec.total_mth_credit;
1777 l_total_wk_credit := TrailRec.total_wk_credit;
1778 --
1779 l_total_YEARLY_TAX_CRED := TrailRec.total_YEARLY_TAX_CRED;
1780 l_total_YEARLY_TAX_RATE_1 := TrailRec.total_YEARLY_TAX_RATE_1;
1781 l_total_YEARLY_TAX_RATE_2 := TrailRec.total_YEARLY_TAX_RATE_2;
1782 l_total_MONTHLY_TAX_RATE_2 := TrailRec.total_MONTHLY_TAX_RATE_2;
1783 l_total_WEEKLY_TAX_RATE_2 := TrailRec.total_WEEKLY_TAX_RATE_2;
1784 l_total_YEARLY_TAX_RATE_3 := TrailRec.total_YEARLY_TAX_RATE_3;
1785 l_total_MONTHLY_TAX_RATE_3 := TrailRec.total_MONTHLY_TAX_RATE_3;
1786 l_total_WEEKLY_TAX_RATE_3 := TrailRec.total_WEEKLY_TAX_RATE_3;
1787 l_total_YEARLY_TAX_RATE_4 := TrailRec.total_YEARLY_TAX_RATE_4;
1788 l_total_MONTHLY_TAX_RATE_4 := TrailRec.total_MONTHLY_TAX_RATE_4;
1789 l_total_WEEKLY_TAX_RATE_4 := TrailRec.total_WEEKLY_TAX_RATE_4;
1790 l_total_YEARLY_USC_RATE_1 := TrailRec.total_YEARLY_USC_RATE_1;
1791 l_total_YEARLY_USC_RATE_2 := TrailRec.total_YEARLY_USC_RATE_2;
1792 l_total_YEARLY_USC_RATE_3 := TrailRec.total_YEARLY_USC_RATE_3;
1793 l_total_YEARLY_USC_RATE_4 := TrailRec.total_YEARLY_USC_RATE_4;
1794 --
1795
1796
1797 IF (BodyRec.count_mth_taxcredit = TrailRec.count_emp_recno
1798 AND BodyRec.sum_mth_rate_cutoff = TrailRec.total_mth_cutoff
1799 AND BodyRec.sum_wk_rate_cutoff = TrailRec.total_wk_cutoff
1800 AND BodyRec.sum_mth_tax_credit = TrailRec.total_mth_credit
1801 AND BodyRec.sum_wk_tax_credit = TrailRec.total_wk_credit
1802 --
1803 AND BodyRec.sum_YRLY_TAX_CRED = TrailRec.total_YEARLY_TAX_CRED
1804 AND BodyRec.sum_YRLY_TAX_RATE_1 = TrailRec.total_YEARLY_TAX_RATE_1
1805 AND BodyRec.sum_YRLY_TAX_RATE_2 = TrailRec.total_YEARLY_TAX_RATE_2
1806 AND BodyRec.sum_MTHLY_TAX_RATE_2 = TrailRec.total_MONTHLY_TAX_RATE_2
1807 AND BodyRec.sum_WKLY_TAX_RATE_2 = TrailRec.total_WEEKLY_TAX_RATE_2
1808 AND BodyRec.sum_YRLY_TAX_RATE_3 = TrailRec.total_YEARLY_TAX_RATE_3
1809 AND BodyRec.sum_MTHLY_TAX_RATE_3 = TrailRec.total_MONTHLY_TAX_RATE_3
1810 AND BodyRec.sum_WKLY_TAX_RATE_3 = TrailRec.total_WEEKLY_TAX_RATE_3
1811 AND BodyRec.sum_YRLY_TAX_RATE_4 = TrailRec.total_YEARLY_TAX_RATE_4
1812 AND BodyRec.sum_MTHLY_TAX_RATE_4 = TrailRec.total_MONTHLY_TAX_RATE_4
1813 AND BodyRec.sum_WKLY_TAX_RATE_4 = TrailRec.total_WEEKLY_TAX_RATE_4
1814 AND BodyRec.sum_USC_YRLY_CUTOFF_1 = TrailRec.total_YEARLY_USC_RATE_1
1815 AND BodyRec.sum_USC_YRLY_CUTOFF_2 = TrailRec.total_YEARLY_USC_RATE_2
1816 AND BodyRec.sum_USC_YRLY_CUTOFF_3 = TrailRec.total_YEARLY_USC_RATE_3
1817 AND BodyRec.sum_USC_YRLY_CUTOFF_4 = TrailRec.total_YEARLY_USC_RATE_4
1818 --
1819 )
1820 THEN
1821 retcode := 0;
1822 fnd_file.put_line( fnd_file.log, 'FND - CONC-COMPLETION TEXT:NORMAL');
1823
1824 update pay_ie_tax_body_interface
1825 set process_flag = 'Y'
1826 where EMPLOYER_NUMBER = p_employer_number;
1827 Commit;
1828
1829 ELSE
1830 RAISE unequal_value;
1831 END IF;
1832
1833 close c_body;
1834 close c_trailer;
1835
1836 EXCEPTION
1837 WHEN NO_DATA_FOUND THEN
1838 l_error := SQLERRM;
1839 retcode := 2;
1840 FND_FILE.PUT_LINE(fnd_file.log,'No data found');
1841 WHEN unequal_value THEN
1842 errbuf := fnd_message.get;
1843 l_error_stack := errbuf;
1844 l_error := SQLERRM;
1845 retcode := 2;
1846 IF l_count_mth_taxcredit <> l_count_emp_recno THEN
1847 FND_FILE.NEW_LINE(fnd_file.log, 1);
1848 FND_FILE.PUT_LINE(fnd_file.log,
1849 'The total number of record in pay_ie_tax_body_interface is '
1850 || TO_CHAR (l_count_mth_taxcredit));
1851 FND_FILE.PUT_LINE (fnd_file.log,
1852 'The value in pay_ie_tax_trailer_interface.record_no is '
1853 || TO_CHAR(l_count_emp_recno));
1854 FND_FILE.PUT_LINE(fnd_file.log,
1855 'Error - Retcode = 2, total number of record in pay_ie_tax_body_interface');
1856 FND_FILE.PUT_LINE(fnd_file.log,
1857 'does not match the value in pay_ie_tax_trailer_interface.record_no');
1858 END IF;
1859
1860 IF l_sum_mth_rate_cutoff <> l_total_mth_cutoff THEN
1861 FND_FILE.NEW_LINE(fnd_file.log, 1);
1862 FND_FILE.PUT_LINE(fnd_file.log,
1863 'The sum of pay_ie_tax_body_interface.mth_rate_cutoff is '
1864 || TO_CHAR (l_sum_mth_rate_cutoff));
1865 FND_FILE.PUT_LINE (fnd_file.log,
1866 'The total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff is '
1867 || TO_CHAR(l_total_mth_cutoff));
1868 FND_FILE.PUT_LINE(fnd_file.log,
1869 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_rate_cutoff');
1870 FND_FILE.PUT_LINE(fnd_file.log,
1871 'does not match the total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff');
1872 END IF;
1873 IF l_sum_wk_rate_cutoff <> l_total_wk_cutoff THEN
1874 FND_FILE.NEW_LINE(fnd_file.log, 1);
1875 FND_FILE.PUT_LINE(fnd_file.log,
1876 'The sum of pay_ie_tax_body_interface.wk_rate_cutoff is '
1877 || TO_CHAR(l_sum_wk_rate_cutoff));
1878 FND_FILE.PUT_LINE(fnd_file.log,
1879 'The total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff is '
1880 || TO_CHAR(l_total_wk_cutoff));
1881 FND_FILE.PUT_LINE(fnd_file.log,
1882 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.wk_rate_cutoff');
1883 FND_FILE.PUT_LINE(fnd_file.log,
1884 'does not match the the total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff');
1885 END IF;
1886 IF l_sum_mth_tax_credit <> l_total_mth_credit THEN
1887 FND_FILE.NEW_LINE(fnd_file.log, 1);
1888 FND_FILE.PUT_LINE(fnd_file.log,
1889 'The sum of pay_ie_tax_body_interface.mth_tax_credit is '
1890 || TO_CHAR(l_sum_mth_tax_credit));
1891 FND_FILE.PUT_LINE(fnd_file.log,
1892 'The total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit is '
1893 || TO_CHAR(l_total_mth_credit));
1894 FND_FILE.PUT_LINE(fnd_file.log,
1895 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_tax_credit');
1896 FND_FILE.PUT_LINE(fnd_file.log,
1897 'does not match the total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit');
1898 END IF;
1899 IF BodyRec.sum_wk_tax_credit <> TrailRec.total_wk_credit THEN
1900 FND_FILE.NEW_LINE(fnd_file.log, 1);
1901 FND_FILE.PUT_LINE(fnd_file.log,
1902 'The sum of pay_ie_tax_body_interface.wk_tax_credit is '
1903 || TO_CHAR(l_sum_wk_tax_credit));
1904 FND_FILE.PUT_LINE(fnd_file.log,
1905 'The total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit is '
1906 || TO_CHAR(l_total_wk_credit));
1907 FND_FILE.PUT_LINE(fnd_file.log,
1908 'Error - Retcode = 2 because the sum of pay_ie_tax_body_interface.wk_tax_credit');
1909 FND_FILE.PUT_LINE(fnd_file.log,
1910 'does not match the total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit');
1911 END IF;
1912
1913 --
1914 IF l_total_YEARLY_TAX_CRED <> l_sum_YRLY_TAX_CRED THEN
1915 FND_FILE.NEW_LINE(fnd_file.log, 1);
1916 FND_FILE.PUT_LINE(fnd_file.log,
1917 'The sum of pay_ie_tax_body_interface.cd_yrly_tax_cred '
1918 || TO_CHAR(l_sum_YRLY_TAX_CRED));
1919 FND_FILE.PUT_LINE(fnd_file.log,
1920 'The total yearly tax credit in pay_ie_tax_trailer_interface.tc_tot_yearly_tax_cred is '
1921 || TO_CHAR(l_total_YEARLY_TAX_CRED));
1922 FND_FILE.PUT_LINE(fnd_file.log,
1923 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_yrly_tax_cred');
1924 FND_FILE.PUT_LINE(fnd_file.log,
1925 'does not match the total yearly tax credit in pay_ie_tax_trailer_interface.tc_tot_yearly_tax_cred');
1926 END IF;
1927
1928 IF l_total_YEARLY_TAX_RATE_1 <> l_sum_YRLY_TAX_RATE_1 THEN
1929 FND_FILE.NEW_LINE(fnd_file.log, 1);
1930 FND_FILE.PUT_LINE(fnd_file.log,
1931 'The sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_1 '
1932 || TO_CHAR(l_sum_YRLY_TAX_RATE_1));
1933 FND_FILE.PUT_LINE(fnd_file.log,
1934 'The total yearly tax rate1 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_1 is '
1935 || TO_CHAR(l_total_YEARLY_TAX_RATE_1));
1936 FND_FILE.PUT_LINE(fnd_file.log,
1937 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_1');
1938 FND_FILE.PUT_LINE(fnd_file.log,
1939 'does not match the total yearly tax rate1 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_1');
1940 END IF;
1941
1942 IF l_total_YEARLY_TAX_RATE_2 <> l_sum_YRLY_TAX_RATE_2 THEN
1943 FND_FILE.NEW_LINE(fnd_file.log, 1);
1944 FND_FILE.PUT_LINE(fnd_file.log,
1945 'The sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_2 '
1946 || TO_CHAR(l_sum_YRLY_TAX_RATE_2));
1947 FND_FILE.PUT_LINE(fnd_file.log,
1948 'The total yearly tax rate2 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_2 is '
1949 || TO_CHAR(l_total_YEARLY_TAX_RATE_2));
1950 FND_FILE.PUT_LINE(fnd_file.log,
1951 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_2');
1952 FND_FILE.PUT_LINE(fnd_file.log,
1953 'does not match the total yearly tax rate2 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_2');
1954 END IF;
1955
1956 IF l_total_MONTHLY_TAX_RATE_2 <> l_sum_MTHLY_TAX_RATE_2 THEN
1957 FND_FILE.NEW_LINE(fnd_file.log, 1);
1958 FND_FILE.PUT_LINE(fnd_file.log,
1959 'The sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_2 '
1960 || TO_CHAR(l_sum_MTHLY_TAX_RATE_2));
1961 FND_FILE.PUT_LINE(fnd_file.log,
1962 'The total monthly tax rate2 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_2 is '
1963 || TO_CHAR(l_total_MONTHLY_TAX_RATE_2));
1964 FND_FILE.PUT_LINE(fnd_file.log,
1965 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_2');
1966 FND_FILE.PUT_LINE(fnd_file.log,
1967 'does not match the total monthly tax rate2 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_2');
1968 END IF;
1969
1970 IF l_total_WEEKLY_TAX_RATE_2 <> l_sum_WKLY_TAX_RATE_2 THEN
1971 FND_FILE.NEW_LINE(fnd_file.log, 1);
1972 FND_FILE.PUT_LINE(fnd_file.log,
1973 'The sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_2 '
1974 || TO_CHAR(l_sum_WKLY_TAX_RATE_2));
1975 FND_FILE.PUT_LINE(fnd_file.log,
1976 'The total weekly tax rate2 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_2 is '
1977 || TO_CHAR(l_total_WEEKLY_TAX_RATE_2));
1978 FND_FILE.PUT_LINE(fnd_file.log,
1979 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_2');
1980 FND_FILE.PUT_LINE(fnd_file.log,
1981 'does not match the total weekly tax rate2 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_2');
1982 END IF;
1983
1984 IF l_total_YEARLY_TAX_RATE_3 <> l_sum_YRLY_TAX_RATE_3 THEN
1985 FND_FILE.NEW_LINE(fnd_file.log, 1);
1986 FND_FILE.PUT_LINE(fnd_file.log,
1987 'The sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_3 '
1988 || TO_CHAR(l_sum_YRLY_TAX_RATE_3));
1989 FND_FILE.PUT_LINE(fnd_file.log,
1990 'The total yearly tax rate3 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_3 is '
1991 || TO_CHAR(l_total_YEARLY_TAX_RATE_3));
1992 FND_FILE.PUT_LINE(fnd_file.log,
1993 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_3');
1994 FND_FILE.PUT_LINE(fnd_file.log,
1995 'does not match the total yearly tax rate3 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_3');
1996 END IF;
1997
1998 IF l_total_MONTHLY_TAX_RATE_3 <> l_sum_MTHLY_TAX_RATE_3 THEN
1999 FND_FILE.NEW_LINE(fnd_file.log, 1);
2000 FND_FILE.PUT_LINE(fnd_file.log,
2001 'The sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_3 '
2002 || TO_CHAR(l_sum_MTHLY_TAX_RATE_3));
2003 FND_FILE.PUT_LINE(fnd_file.log,
2004 'The total monthly tax rate3 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_3 is '
2005 || TO_CHAR(l_total_MONTHLY_TAX_RATE_3));
2006 FND_FILE.PUT_LINE(fnd_file.log,
2007 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_3');
2008 FND_FILE.PUT_LINE(fnd_file.log,
2009 'does not match the total monthly tax rate3 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_3');
2010 END IF;
2011
2012 IF l_total_WEEKLY_TAX_RATE_3 <> l_sum_WKLY_TAX_RATE_3 THEN
2013 FND_FILE.NEW_LINE(fnd_file.log, 1);
2014 FND_FILE.PUT_LINE(fnd_file.log,
2015 'The sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_3 '
2016 || TO_CHAR(l_sum_WKLY_TAX_RATE_3));
2017 FND_FILE.PUT_LINE(fnd_file.log,
2018 'The total weekly tax rate3 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_3 is '
2019 || TO_CHAR(l_total_WEEKLY_TAX_RATE_3));
2020 FND_FILE.PUT_LINE(fnd_file.log,
2021 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_3');
2022 FND_FILE.PUT_LINE(fnd_file.log,
2023 'does not match the total weekly tax rate3 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_3');
2024 END IF;
2025
2026 IF l_total_YEARLY_TAX_RATE_4 <> l_sum_YRLY_TAX_RATE_4 THEN
2027 FND_FILE.NEW_LINE(fnd_file.log, 1);
2028 FND_FILE.PUT_LINE(fnd_file.log,
2029 'The sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_4 '
2030 || TO_CHAR(l_sum_YRLY_TAX_RATE_4));
2031 FND_FILE.PUT_LINE(fnd_file.log,
2032 'The total yearly tax rate3 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_4 is '
2033 || TO_CHAR(l_total_YEARLY_TAX_RATE_4));
2034 FND_FILE.PUT_LINE(fnd_file.log,
2035 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_yrly_tax_rate_4');
2036 FND_FILE.PUT_LINE(fnd_file.log,
2037 'does not match the total yearly tax rate4 in pay_ie_tax_trailer_interface.tot_yearly_tax_rate_4');
2038 END IF;
2039
2040 IF l_total_MONTHLY_TAX_RATE_4 <> l_sum_MTHLY_TAX_RATE_4 THEN
2041 FND_FILE.NEW_LINE(fnd_file.log, 1);
2042 FND_FILE.PUT_LINE(fnd_file.log,
2043 'The sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_4 '
2044 || TO_CHAR(l_sum_MTHLY_TAX_RATE_4));
2045 FND_FILE.PUT_LINE(fnd_file.log,
2046 'The total monthly tax rate3 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_4 is '
2047 || TO_CHAR(l_total_MONTHLY_TAX_RATE_4));
2048 FND_FILE.PUT_LINE(fnd_file.log,
2049 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_mthly_tax_rate_4');
2050 FND_FILE.PUT_LINE(fnd_file.log,
2051 'does not match the total monthly tax rate4 in pay_ie_tax_trailer_interface.tot_monthly_tax_rate_4');
2052 END IF;
2053
2054 IF l_total_WEEKLY_TAX_RATE_4 <> l_sum_WKLY_TAX_RATE_4 THEN
2055 FND_FILE.NEW_LINE(fnd_file.log, 1);
2056 FND_FILE.PUT_LINE(fnd_file.log,
2057 'The sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_4 '
2058 || TO_CHAR(l_sum_WKLY_TAX_RATE_4));
2059 FND_FILE.PUT_LINE(fnd_file.log,
2060 'The total weekly tax rate3 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_4 is '
2061 || TO_CHAR(l_total_WEEKLY_TAX_RATE_4));
2062 FND_FILE.PUT_LINE(fnd_file.log,
2063 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.cd_wkly_tax_rate_4');
2064 FND_FILE.PUT_LINE(fnd_file.log,
2065 'does not match the total weekly tax rate4 in pay_ie_tax_trailer_interface.tot_weekly_tax_rate_4');
2066 END IF;
2067
2068 IF l_total_YEARLY_USC_RATE_1 <> l_sum_USC_YRLY_CUTOFF_1 THEN
2069 FND_FILE.NEW_LINE(fnd_file.log, 1);
2070 FND_FILE.PUT_LINE(fnd_file.log,
2071 'The sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_1 '
2072 || TO_CHAR(l_sum_USC_YRLY_CUTOFF_1));
2073 FND_FILE.PUT_LINE(fnd_file.log,
2074 'The total yearly usc rate1 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_1 is '
2075 || TO_CHAR(l_total_YEARLY_USC_RATE_1));
2076 FND_FILE.PUT_LINE(fnd_file.log,
2077 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_1');
2078 FND_FILE.PUT_LINE(fnd_file.log,
2079 'does not match the total yearly usc rate1 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_1');
2080 END IF;
2081
2082 IF l_total_YEARLY_USC_RATE_2 <> l_sum_USC_YRLY_CUTOFF_2 THEN
2083 FND_FILE.NEW_LINE(fnd_file.log, 1);
2084 FND_FILE.PUT_LINE(fnd_file.log,
2085 'The sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_2 '
2086 || TO_CHAR(l_sum_USC_YRLY_CUTOFF_2));
2087 FND_FILE.PUT_LINE(fnd_file.log,
2088 'The total yearly usc rate2 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_2 is '
2089 || TO_CHAR(l_total_YEARLY_USC_RATE_2));
2090 FND_FILE.PUT_LINE(fnd_file.log,
2091 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_2');
2092 FND_FILE.PUT_LINE(fnd_file.log,
2093 'does not match the total yearly usc rate2 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_2');
2094 END IF;
2095
2096 IF l_total_YEARLY_USC_RATE_3 <> l_sum_USC_YRLY_CUTOFF_3 THEN
2097 FND_FILE.NEW_LINE(fnd_file.log, 1);
2098 FND_FILE.PUT_LINE(fnd_file.log,
2099 'The sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_3 '
2100 || TO_CHAR(l_sum_USC_YRLY_CUTOFF_3));
2101 FND_FILE.PUT_LINE(fnd_file.log,
2102 'The total yearly usc rate3 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_3 is '
2103 || TO_CHAR(l_total_YEARLY_USC_RATE_3));
2104 FND_FILE.PUT_LINE(fnd_file.log,
2105 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_3');
2106 FND_FILE.PUT_LINE(fnd_file.log,
2107 'does not match the total yearly usc rate3 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_3');
2108 END IF;
2109
2110 IF l_total_YEARLY_USC_RATE_4 <> l_sum_USC_YRLY_CUTOFF_4 THEN
2111 FND_FILE.NEW_LINE(fnd_file.log, 1);
2112 FND_FILE.PUT_LINE(fnd_file.log,
2113 'The sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_4 '
2114 || TO_CHAR(l_sum_USC_YRLY_CUTOFF_4));
2115 FND_FILE.PUT_LINE(fnd_file.log,
2116 'The total yearly usc rate4 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_4 is '
2117 || TO_CHAR(l_total_YEARLY_USC_RATE_4));
2118 FND_FILE.PUT_LINE(fnd_file.log,
2119 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.am_usc_yrly_cutoff_4');
2120 FND_FILE.PUT_LINE(fnd_file.log,
2121 'does not match the total yearly usc rate4 in pay_ie_tax_trailer_interface.tot_yearly_usc_rate_4');
2122 END IF;
2123
2124 --
2125
2126 INSERT INTO pay_ie_tax_error ( pps_number
2127 , employee_number
2128 , full_name
2129 , payroll_name
2130 , error_stack_message
2131 , error_message
2132 , request_id
2133 , error_date)
2134 VALUES (0
2135 , NULL
2136 , NULL
2137 , NULL
2138 , l_error_stack
2139 , l_error
2140 , l_request_id
2141 , sysdate);
2142 COMMIT;
2143
2144 WHEN OTHERS THEN
2145 errbuf := fnd_message.get;
2146 l_error_stack := errbuf;
2147 l_error := SQLERRM;
2148 retcode := 2;
2149 FND_FILE.PUT_LINE (fnd_file.log, 'Error raised in loading data into one or all of the follo
2150 wing tables: pay_ie_tax_header_interface, pay_ie_tax_body_interface, pay_ie_tax_trailer_interface');
2151
2152 INSERT INTO pay_ie_tax_error ( pps_number
2153 , employee_number
2154 , full_name
2155 , payroll_name
2156 , error_stack_message
2157 , error_message
2158 , request_id
2159 , error_date)
2160 VALUES (0
2161 , NULL
2162 , NULL
2163 , NULL
2164 , l_error_stack
2165 , l_error
2166 , l_request_id
2167 , sysdate);
2168 COMMIT;
2169 END count_validation_new;
2170
2171 PROCEDURE read_tax_file( errbuf OUT NOCOPY VARCHAR2
2172 , retcode OUT NOCOPY VARCHAR2
2173 , p_filename IN VARCHAR2
2174 , p_employer_number IN VARCHAR2
2175 , p_tax_year IN NUMBER
2176 , p_validate_mode IN VARCHAR2 :='IE_VALIDATE'
2177 , p_payroll_id IN NUMBER := NULL
2178 )
2179 IS
2180 l_location VARCHAR2(2000);
2181 l_file_handle utl_file.file_type;
2182 l_present_line VARCHAR2(32767) := NULL;
2183 l_present_line1 VARCHAR2(32767) := NULL;
2184 l_present_line2 VARCHAR2(32767) := NULL;
2185 invalid_file_format exception;
2186 e_fatal_error exception;
2187 no_rec_found_in_file exception;
2188 e_invlid_tax_year exception;
2189 l_pkg varchar2(100) := 'PAY_IE_TAX_VAL.READ_TAX_FILE';
2190
2191 --Header
2192 l_record_type pay_ie_tax_header_interface.record_type%type;
2193 l_tax_year pay_ie_tax_header_interface.tax_year%type;
2194 l_employer_number pay_ie_tax_header_interface.employer_number%type;
2195 l_employer_name pay_ie_tax_header_interface.employer_name%type;
2196 l_employer_unit_no pay_ie_tax_header_interface.employer_unit_no%type;
2197 l_hc_date pay_ie_tax_header_interface.hc_date%type;
2198 l_hc_label_year pay_ie_tax_header_interface.hc_label_year%type;
2199
2200 --Body
2201 --l_record_type pay_ie_tax_body_interface.record_type%type;
2202 l_pps_number pay_ie_tax_body_interface.pps_number%type;
2203 l_mth_rate_cutoff pay_ie_tax_body_interface.mth_rate_cutoff%type;
2204 l_wk_rate_cutoff pay_ie_tax_body_interface.wk_rate_cutoff%type;
2205 l_works_number pay_ie_tax_body_interface.works_number%type;
2206 l_mth_tax_credit pay_ie_tax_body_interface.mth_tax_credit%type;
2207 l_wk_tax_credit pay_ie_tax_body_interface.wk_tax_credit%type;
2208 l_cert_start_date pay_ie_tax_body_interface.cert_start_date%type;
2209 l_cert_end_date pay_ie_tax_body_interface.cert_end_date%type;
2210 l_wk_mth_indicator pay_ie_tax_body_interface.wk_mth_indicator%type;
2211 l_cert_date pay_ie_tax_body_interface.cert_date%type;
2212 l_last_name pay_ie_tax_body_interface.last_name%type;
2213 l_first_name pay_ie_tax_body_interface.first_name%type;
2214 l_address_line1 pay_ie_tax_body_interface.address_line1%type;
2215 l_address_line2 pay_ie_tax_body_interface.address_line2%type;
2216 l_address_line3 pay_ie_tax_body_interface.address_line3%type;
2217 l_tot_pay_to_date pay_ie_tax_body_interface.tot_pay_to_date%type;
2218 l_tot_tax_to_date pay_ie_tax_body_interface.tot_tax_to_date%type;
2219 l_exemption_indicator pay_ie_tax_body_interface.exemption_indicator%type;
2220 l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%type;
2221 l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%type;
2222 l_processed_flag VARCHAR2(1);
2223 l_processed_date DATE;
2224 --
2225 l_cd_yrly_tax_cred pay_ie_tax_body_interface.cd_yrly_tax_cred%type;
2226 l_cd_yrly_tax_rate_1 pay_ie_tax_body_interface.cd_yrly_tax_rate_1%type;
2227 l_cd_yrly_tax_rate_2 pay_ie_tax_body_interface.cd_yrly_tax_rate_2%type;
2228 l_cd_mthly_tax_rate_2 pay_ie_tax_body_interface.cd_mthly_tax_rate_2%type;
2229 l_cd_wkly_tax_rate_2 pay_ie_tax_body_interface.cd_wkly_tax_rate_2%type;
2230 l_cd_tax_rate_3 pay_ie_tax_body_interface.cd_tax_rate_3%type;
2231 l_cd_yrly_tax_rate_3 pay_ie_tax_body_interface.cd_yrly_tax_rate_3%type;
2232 l_cd_mthly_tax_rate_3 pay_ie_tax_body_interface.cd_mthly_tax_rate_3%type;
2233 l_cd_wkly_tax_rate_3 pay_ie_tax_body_interface.cd_wkly_tax_rate_3%type;
2234 l_cd_tax_rate_4 pay_ie_tax_body_interface.cd_tax_rate_4%type;
2235 l_cd_yrly_tax_rate_4 pay_ie_tax_body_interface.cd_yrly_tax_rate_4%type;
2236 l_cd_mthly_tax_rate_4 pay_ie_tax_body_interface.cd_mthly_tax_rate_4%type;
2237 l_cd_wkly_tax_rate_4 pay_ie_tax_body_interface.cd_wkly_tax_rate_4%type;
2238 l_cd_tax_rate_5 pay_ie_tax_body_interface.cd_tax_rate_5%type;
2239 l_in_exempt_usc pay_ie_tax_body_interface.in_exempt_usc%type;
2240 l_cd_total_usc_pay_todate pay_ie_tax_body_interface.cd_total_usc_pay_todate%type;
2241 l_cd_total_usc_tax_todate pay_ie_tax_body_interface.cd_total_usc_tax_todate%type;
2242 l_cd_usc_rate_1 pay_ie_tax_body_interface.cd_usc_rate_1%type;
2243 l_am_usc_yrly_cutoff_1 pay_ie_tax_body_interface.am_usc_yrly_cutoff_1%type;
2244 l_am_usc_mthly_cutoff_1 pay_ie_tax_body_interface.am_usc_mthly_cutoff_1%type;
2245 l_am_usc_wkly_cutoff_1 pay_ie_tax_body_interface.am_usc_wkly_cutoff_1%type;
2246 l_cd_usc_rate_2 pay_ie_tax_body_interface.cd_usc_rate_2%type;
2247 l_am_usc_yrly_cutoff_2 pay_ie_tax_body_interface.am_usc_yrly_cutoff_2%type;
2248 l_am_usc_mthly_cutoff_2 pay_ie_tax_body_interface.am_usc_mthly_cutoff_2%type;
2249 l_am_usc_wkly_cutoff_2 pay_ie_tax_body_interface.am_usc_wkly_cutoff_2%type;
2250 l_cd_usc_rate_3 pay_ie_tax_body_interface.cd_usc_rate_3%type;
2251 l_am_usc_yrly_cutoff_3 pay_ie_tax_body_interface.am_usc_yrly_cutoff_3%type;
2252 l_am_usc_mthly_cutoff_3 pay_ie_tax_body_interface.am_usc_mthly_cutoff_3%type;
2253 l_am_usc_wkly_cutoff_3 pay_ie_tax_body_interface.am_usc_wkly_cutoff_3%type;
2254 l_cd_usc_rate_4 pay_ie_tax_body_interface.cd_usc_rate_4%type;
2255 l_am_usc_yrly_cutoff_4 pay_ie_tax_body_interface.am_usc_yrly_cutoff_4%type;
2256 l_am_usc_mthly_cutoff_4 pay_ie_tax_body_interface.am_usc_mthly_cutoff_4%type;
2257 l_am_usc_wkly_cutoff_4 pay_ie_tax_body_interface.am_usc_wkly_cutoff_4%type;
2258 l_cd_usc_rate_5 pay_ie_tax_body_interface.cd_usc_rate_5%type;
2259 --
2260
2261
2262 --Trailer
2263 --l_record_type pay_ie_tax_trailer_interface.record_type%type;
2264 --l_employer_number pay_ie_tax_trailer_interface.employer_number%type;
2265 l_total_mth_rate_cutoff pay_ie_tax_trailer_interface.total_mth_rate_cutoff%type;
2266 l_total_wk_rate_cutoff pay_ie_tax_trailer_interface.total_wk_rate_cutoff%type;
2267 l_total_mth_tax_credit pay_ie_tax_trailer_interface.total_mth_tax_credit%type;
2268 l_total_wk_tax_credit pay_ie_tax_trailer_interface.total_wk_tax_credit%type;
2269 l_record_no pay_ie_tax_trailer_interface.record_no%type;
2270 --
2271 l_TC_TOT_YEARLY_TAX_CRED pay_ie_tax_trailer_interface.TC_TOT_YEARLY_TAX_CRED%type;
2272 l_TOT_YEARLY_TAX_RATE_1 pay_ie_tax_trailer_interface.TOT_YEARLY_TAX_RATE_1%type;
2273 l_TOT_YEARLY_TAX_RATE_2 pay_ie_tax_trailer_interface.TOT_YEARLY_TAX_RATE_2%type;
2274 l_TOT_MONTHLY_TAX_RATE_2 pay_ie_tax_trailer_interface.TOT_MONTHLY_TAX_RATE_2%type;
2275 l_TOT_WEEKLY_TAX_RATE_2 pay_ie_tax_trailer_interface.TOT_WEEKLY_TAX_RATE_2%type;
2276 l_TOT_YEARLY_TAX_RATE_3 pay_ie_tax_trailer_interface.TOT_YEARLY_TAX_RATE_3%type;
2277 l_TOT_MONTHLY_TAX_RATE_3 pay_ie_tax_trailer_interface.TOT_MONTHLY_TAX_RATE_3%type;
2278 l_TOT_WEEKLY_TAX_RATE_3 pay_ie_tax_trailer_interface.TOT_WEEKLY_TAX_RATE_3%type;
2279 l_TOT_YEARLY_TAX_RATE_4 pay_ie_tax_trailer_interface.TOT_YEARLY_TAX_RATE_4%type;
2280 l_TOT_MONTHLY_TAX_RATE_4 pay_ie_tax_trailer_interface.TOT_MONTHLY_TAX_RATE_4%type;
2281 l_TOT_WEEKLY_TAX_RATE_4 pay_ie_tax_trailer_interface.TOT_WEEKLY_TAX_RATE_4%type;
2282 l_TOT_YEARLY_USC_RATE_1 pay_ie_tax_trailer_interface.TOT_YEARLY_USC_RATE_1%type;
2283 l_TOT_YEARLY_USC_RATE_2 pay_ie_tax_trailer_interface.TOT_YEARLY_USC_RATE_2%type;
2284 l_TOT_YEARLY_USC_RATE_3 pay_ie_tax_trailer_interface.TOT_YEARLY_USC_RATE_3%type;
2285 l_TOT_YEARLY_USC_RATE_4 pay_ie_tax_trailer_interface.TOT_YEARLY_USC_RATE_4%type;
2286 --
2287
2288 ------------
2289 /* This function is used to read each assignment line and split the data into columns.
2290 * This function takes the below arguments
2291 * in_line - Each Line, which contains the delimiter tokens.
2292 * token_index - Nth Occurance of the token.
2293 * delim - Delimiter token.
2294 * return value - String between (N-1)th Occurence and Nth Occurence of the delimiter.
2295 */
2296
2297 function get_token(
2298 in_line varchar2,
2299 token_index number,
2300 delim varchar2 default ','
2301 )
2302 return varchar2
2303 is
2304 start_pos number;
2305 end_pos number;
2306 begin
2307 if token_index = 1 then
2308 start_pos := 1;
2309 else
2310 start_pos := instr(in_line, delim, 1, token_index - 1);
2311 if start_pos = 0 then
2312 return null;
2313 else
2314 start_pos := start_pos + length(delim);
2315 end if;
2316 end if;
2317
2318 end_pos := instr(in_line, delim, start_pos, 1);
2319
2320 if end_pos = 0 then
2321 return replace(trim(substr(in_line, start_pos)),'"');
2322 else
2323 return replace(trim(substr(in_line, start_pos, end_pos - start_pos)),'"');
2324 end if;
2325
2326 end get_token;
2327
2328 /* This function is used to count the number of occurances of the given delimiter.
2329 * in_line - Input Line
2330 * return value - Number of occurances.
2331 */
2332 function count_tokens (in_line varchar2,
2333 delim varchar2 default ',')
2334 return number is
2335 l_token_count number := 0;
2336 l_start number :=0;
2337 begin
2338 while true loop
2339 l_start := instr(in_line, delim, l_start+length(delim), 1);
2340 if l_start = 0 then
2341 -- No More Token Found. Hence return the count.
2342 exit;
2343 else
2344 -- One more Token Found. Increment the count.
2345 l_token_count := l_token_count+1;
2346 end if;
2347 end loop;
2348 return l_token_count;
2349 end count_tokens;
2350
2351
2352 BEGIN
2353
2354 If p_tax_year < 2012 THEN
2355 retcode := 2;
2356 errbuf := 'Invalid Tax Year.';
2357 fnd_file.PUT_LINE(FND_FILE.LOG,'Please enter tax year 2012 or later.');
2358 raise e_invlid_tax_year;
2359 End if;
2360
2361
2362 Begin
2363 Delete from PAY_IE_TAX_HEADER_INTERFACE;
2364 Delete from PAY_IE_TAX_BODY_INTERFACE;
2365 Delete from PAY_IE_TAX_TRAILER_INTERFACE;
2366 if sql%rowcount > 0 then
2367 commit;
2368 end if;
2369 exception
2370 when others then
2371 FND_FILE.PUT_LINE(fnd_file.log,'Error occured while deleting exisiting rows in interface tables.');
2372 end;
2373
2374 --fnd_file.PUT_LINE(FND_FILE.LOG, 'File Name:'|| p_filename);
2375 --fnd_file.PUT_LINE(FND_FILE.LOG, 'Mode:'||p_validate_mode);
2376
2377 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
2378
2379 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_location:'||l_location);
2380
2381 l_file_handle := utl_file.fopen(l_location,p_filename,'r');
2382 utl_file.get_line(l_file_handle,l_present_line);
2383
2384 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line-1:'||l_present_line); --Reading Header Columns
2385
2386
2387 --First Line must be this
2388 --HC_TAX_YEAR, HC_EMPLOYER_NUMBER, HC_EMPLOYER_NAME, HC_DATE, HC_LABEL_YEAR, HC_EMPLOYER_UNIT_NUM
2389 IF (substr(trim(l_present_line),1,11) <> 'HC_TAX_YEAR')
2390 THEN
2391 retcode := 2;
2392 errbuf := 'Invalid file format.';
2393 fnd_file.PUT_LINE(FND_FILE.LOG,'File not started with HC_TAX_YEAR');
2394 hr_utility.trace('Beginning line is :'||l_present_line);
2395 raise invalid_file_format;
2396 END IF;
2397
2398 utl_file.get_line(l_file_handle,l_present_line);--Reading Header values
2399 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line-2:'||l_present_line);
2400
2401 --13359423
2402 /* if (count_tokens(l_present_line) <> 5) then
2403 retcode := 2;
2404 errbuf := 'Header Record Format altered.';
2405 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered.');
2406 raise invalid_file_format;
2407 end if; */
2408 --13359423
2409
2410 --Inserting into the header table
2411 l_record_type := 'HC';
2412 l_tax_year := get_token(l_present_line,1);
2413 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_tax_year:'||l_tax_year);
2414 l_employer_number := get_token(l_present_line,2);
2415 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_employer_number:'||l_employer_number);
2416 l_employer_name := get_token(l_present_line,3);
2417 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_employer_name:'||l_employer_name);
2418 l_hc_date := to_date(get_token(l_present_line,4),'DDMMYY');
2419 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_hc_date:'||l_hc_date);
2420 l_hc_label_year := to_number(get_token(l_present_line,5));
2421 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_hc_label_year:'||l_hc_label_year);
2422 --fnd_file.PUT_LINE(FND_FILE.LOG, get_token(l_present_line,6));
2423 l_employer_unit_no:= substr(get_token(l_present_line,6),1,3);
2424 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_employer_unit_no:'||l_employer_unit_no);
2425
2426 INSERT INTO PAY_IE_TAX_HEADER_INTERFACE
2427 (
2428 RECORD_TYPE
2429 ,EMPLOYER_NUMBER
2430 ,EMPLOYER_NAME
2431 ,EMPLOYER_UNIT_NO
2432 ,TAX_YEAR
2433 ,HC_DATE
2434 ,HC_LABEL_YEAR
2435 )
2436 VALUES
2437 (
2438 'HC'
2439 ,l_employer_number
2440 ,l_employer_name
2441 ,l_employer_unit_no
2442 ,l_tax_year
2443 ,l_hc_date
2444 ,l_hc_label_year
2445 );
2446
2447 utl_file.get_line(l_file_handle,l_present_line);--Reading Body Columns
2448 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line-3:'||l_present_line);
2449
2450 --Body Column must start with
2451 --ID_PPSN, CD_SURNAME, CD_FORENAME,....
2452 IF (substr(trim(l_present_line),1,7) <> 'ID_PPSN')
2453 THEN
2454 retcode := 2;
2455 errbuf := 'Invalid file format.';
2456 fnd_file.PUT_LINE(FND_FILE.LOG,'Body not started with ID_PPSN');
2457 hr_utility.trace('Beginning line is :'||l_present_line);
2458 raise invalid_file_format;
2459 END IF;
2460
2461 --fnd_file.PUT_LINE(FND_FILE.LOG, '+++++++++BODY+++++++++');
2462
2463 LOOP
2464 BEGIN
2465
2466 utl_file.get_line(l_file_handle,l_present_line); --Reading Body values
2467 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line:'||l_present_line);
2468
2469 l_present_line1 := SUBSTR(l_present_line, 1, INSTR(l_present_line,'"',1,14)+1);
2470 l_present_line2 := SUBSTR(l_present_line, INSTR(l_present_line,'"',1,14)+2);
2471
2472 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line1:'||l_present_line1);
2473 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line2:'||l_present_line2);
2474 --fnd_file.PUT_LINE(FND_FILE.LOG, '+++++++++++++++++++++++++++++++++++++++++++++++');
2475
2476 IF (substr(trim(l_present_line),1,18) = 'TC_EMPLOYER_NUMBER') THEN
2477 EXIT;
2478 ELSE
2479 if (count_tokens(l_present_line) < 53) then
2480 retcode := 2;
2481 errbuf := 'Body Record Format altered.';
2482 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered.');
2483 raise invalid_file_format;
2484 end if;
2485 --Inserting into the Body table
2486
2487 l_pps_number := get_token(l_present_line1,1,'",');
2488 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_pps_number '||l_pps_number);
2489 l_last_name := get_token(l_present_line1,2,'",');
2490 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_last_name '||l_last_name);
2491 l_first_name := get_token(l_present_line1,3,'",');
2492 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_first_name '||l_first_name);
2493 l_works_number := get_token(l_present_line1,4);
2494 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_works_number '||l_works_number);
2495 l_address_line1 := get_token(l_present_line1,5,'",');
2496 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_address_line1 '||l_address_line1);
2497 l_address_line2 := get_token(l_present_line1,6,'",');
2498 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_address_line2 '||l_address_line2);
2499 l_address_line3 := get_token(l_present_line1,7,'",');
2500 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_address_line3 '||l_address_line3);
2501
2502 l_employer_number := l_employer_number;
2503
2504 l_cert_date := to_date(get_token(l_present_line2,8-7),'DDMMYY');
2505 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_cert_date '||l_cert_date);
2506 l_cert_start_date := to_date(get_token(l_present_line2,9-7),'DDMMYY');
2507 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_cert_start_date '||l_cert_start_date);
2508
2509 --fnd_file.PUT_LINE(FND_FILE.LOG, 'Before l_cert_end_date '||l_cert_end_date);
2510 If substr(get_token(l_present_line2,10-7),1,6) <> '000000' THEN
2511 l_cert_end_date := to_date(get_token(l_present_line2,10-7),'DDMMYY');
2512 ELSE
2513 l_cert_end_date := NULL; --14627559
2514 END IF;
2515 --fnd_file.PUT_LINE(FND_FILE.LOG, 'After l_cert_end_date '||l_cert_end_date);
2516
2517 l_wk_mth_indicator := get_token(l_present_line2,11-7);
2518 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_wk_mth_indicator '||l_wk_mth_indicator);
2519
2520 l_tot_pay_to_date := fnd_number.canonical_to_number(get_token(l_present_line2,12-7));
2521 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_tot_pay_to_date '||l_tot_pay_to_date);
2522
2523 l_tot_tax_to_date := fnd_number.canonical_to_number(get_token(l_present_line2,13-7));
2524 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_tot_tax_to_date '||l_tot_tax_to_date);
2525
2526 l_exemption_indicator := get_token(l_present_line2,14-7);
2527 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_exemption_indicator '||l_exemption_indicator);
2528
2529 l_CD_YRLY_TAX_CRED := fnd_number.canonical_to_number(get_token(l_present_line2,15-7));
2530 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_YRLY_TAX_CRED '||l_CD_YRLY_TAX_CRED);
2531
2532 l_mth_tax_credit := fnd_number.canonical_to_number(get_token(l_present_line2,16-7)); -->length 8
2533 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_mth_tax_credit '||l_mth_tax_credit);
2534
2535 l_wk_tax_credit := fnd_number.canonical_to_number(get_token(l_present_line2,17-7)); -->length 7
2536 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_wk_tax_credit '||l_wk_tax_credit);
2537
2538 l_std_rate_of_tax := fnd_number.canonical_to_number(get_token(l_present_line2,18-7));
2539 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_std_rate_of_tax '||l_std_rate_of_tax);
2540
2541 l_CD_YRLY_TAX_RATE_1 := fnd_number.canonical_to_number(get_token(l_present_line2,19-7));
2542 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_YRLY_TAX_RATE_1 '||l_CD_YRLY_TAX_RATE_1);
2543
2544 l_mth_rate_cutoff := fnd_number.canonical_to_number(get_token(l_present_line2,20-7)); -->length 8
2545 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_mth_rate_cutoff '||l_mth_rate_cutoff);
2546
2547 l_wk_rate_cutoff := fnd_number.canonical_to_number(get_token(l_present_line2,21-7)); -->length 7
2548 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_wk_rate_cutoff '||l_wk_rate_cutoff);
2549
2550 l_higher_rate_of_tax := fnd_number.canonical_to_number(get_token(l_present_line2,22-7));
2551 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_higher_rate_of_tax '||l_higher_rate_of_tax);
2552
2553 l_processed_flag := NULL;
2554 l_processed_date := NULL;
2555 --
2556
2557 l_CD_YRLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line2,23-7));
2558 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_YRLY_TAX_RATE_2 '||l_CD_YRLY_TAX_RATE_2);
2559 l_CD_MTHLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line2,24-7));
2560 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_MTHLY_TAX_RATE_2 '||l_CD_MTHLY_TAX_RATE_2);
2561 l_CD_WKLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line2,25-7));
2562 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_WKLY_TAX_RATE_2 '||l_CD_WKLY_TAX_RATE_2);
2563 l_CD_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line2,26-7));
2564 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_TAX_RATE_3 '||l_CD_TAX_RATE_3);
2565 l_CD_YRLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line2,27-7));
2566 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_YRLY_TAX_RATE_3 '||l_CD_YRLY_TAX_RATE_3);
2567 l_CD_MTHLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line2,28-7));
2568 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_MTHLY_TAX_RATE_3 '||l_CD_MTHLY_TAX_RATE_3);
2569 l_CD_WKLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line2,29-7));
2570 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_WKLY_TAX_RATE_3 '||l_CD_WKLY_TAX_RATE_3);
2571 l_CD_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line2,30-7));
2572 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_TAX_RATE_4 '||l_CD_TAX_RATE_4);
2573 l_CD_YRLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line2,31-7));
2574 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_YRLY_TAX_RATE_4 '||l_CD_YRLY_TAX_RATE_4);
2575 l_CD_MTHLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line2,32-7));
2576 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_MTHLY_TAX_RATE_4 '||l_CD_MTHLY_TAX_RATE_4);
2577 l_CD_WKLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line2,33-7));
2578 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_WKLY_TAX_RATE_4 '||l_CD_WKLY_TAX_RATE_4);
2579 l_CD_TAX_RATE_5 := fnd_number.canonical_to_number(get_token(l_present_line2,34-7));
2580 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_TAX_RATE_5 '||l_CD_TAX_RATE_5 );
2581 l_IN_EXEMPT_USC := get_token(l_present_line2,35-7);
2582 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_IN_EXEMPT_USC '||l_IN_EXEMPT_USC );
2583 l_CD_TOTAL_USC_PAY_TODATE := fnd_number.canonical_to_number(get_token(l_present_line2,36-7));
2584 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_TOTAL_USC_PAY_TODATE '||l_CD_TOTAL_USC_PAY_TODATE);
2585 l_CD_TOTAL_USC_TAX_TODATE := fnd_number.canonical_to_number(get_token(l_present_line2,37-7));
2586 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_TOTAL_USC_TAX_TODATE '||l_CD_TOTAL_USC_TAX_TODATE);
2587 l_CD_USC_RATE_1 := fnd_number.canonical_to_number(get_token(l_present_line2,38-7));
2588 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_USC_RATE_1 '||l_CD_USC_RATE_1);
2589 l_AM_USC_YRLY_CUTOFF_1 := fnd_number.canonical_to_number(get_token(l_present_line2,39-7));
2590 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_YRLY_CUTOFF_1 '||l_AM_USC_YRLY_CUTOFF_1);
2591 l_AM_USC_MTHLY_CUTOFF_1 := fnd_number.canonical_to_number(get_token(l_present_line2,40-7));
2592 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_MTHLY_CUTOFF_1 '||l_AM_USC_MTHLY_CUTOFF_1);
2593 l_AM_USC_WKLY_CUTOFF_1 := fnd_number.canonical_to_number(get_token(l_present_line2,41-7));
2594 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_WKLY_CUTOFF_1 '||l_AM_USC_WKLY_CUTOFF_1);
2595 l_CD_USC_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line2,42-7));
2596 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_USC_RATE_2 '||l_CD_USC_RATE_2);
2597 l_AM_USC_YRLY_CUTOFF_2 := fnd_number.canonical_to_number(get_token(l_present_line2,43-7));
2598 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_YRLY_CUTOFF_2 '||l_AM_USC_YRLY_CUTOFF_2);
2599 l_AM_USC_MTHLY_CUTOFF_2 := fnd_number.canonical_to_number(get_token(l_present_line2,44-7));
2600 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_MTHLY_CUTOFF_2 '||l_AM_USC_MTHLY_CUTOFF_2);
2601 l_AM_USC_WKLY_CUTOFF_2 := fnd_number.canonical_to_number(get_token(l_present_line2,45-7));
2602 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_WKLY_CUTOFF_2 '||l_AM_USC_WKLY_CUTOFF_2);
2603 l_CD_USC_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line2,46-7));
2604 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_USC_RATE_3 '||l_CD_USC_RATE_3);
2605 l_AM_USC_YRLY_CUTOFF_3 := fnd_number.canonical_to_number(get_token(l_present_line2,47-7));
2606 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_YRLY_CUTOFF_3 '||l_AM_USC_YRLY_CUTOFF_3);
2607 l_AM_USC_MTHLY_CUTOFF_3 := fnd_number.canonical_to_number(get_token(l_present_line2,48-7));
2608 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_MTHLY_CUTOFF_3 '||l_AM_USC_MTHLY_CUTOFF_3);
2609 l_AM_USC_WKLY_CUTOFF_3 := fnd_number.canonical_to_number(get_token(l_present_line2,49-7));
2610 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_WKLY_CUTOFF_3 '||l_AM_USC_WKLY_CUTOFF_3);
2611 l_CD_USC_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line2,50-7));
2612 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_USC_RATE_4 '||l_CD_USC_RATE_4);
2613 l_AM_USC_YRLY_CUTOFF_4 := fnd_number.canonical_to_number(get_token(l_present_line2,51-7));
2614 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_YRLY_CUTOFF_4 '||l_AM_USC_YRLY_CUTOFF_4);
2615 l_AM_USC_MTHLY_CUTOFF_4 := fnd_number.canonical_to_number(get_token(l_present_line2,52-7));
2616 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_MTHLY_CUTOFF_4 '||l_AM_USC_MTHLY_CUTOFF_4);
2617 l_AM_USC_WKLY_CUTOFF_4 := fnd_number.canonical_to_number(get_token(l_present_line2,53-7));
2618 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_AM_USC_WKLY_CUTOFF_4 '||l_AM_USC_WKLY_CUTOFF_4);
2619
2620 IF ASCII(substr(get_token(l_present_line2,54-7),1,2)) <> '13' THEN
2621 l_CD_USC_RATE_5 := fnd_number.canonical_to_number(substr(get_token(l_present_line2,54-7),1,2));
2622 END IF;
2623 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_CD_USC_RATE_5-3 '||l_CD_USC_RATE_5);
2624 --
2625
2626 INSERT INTO PAY_IE_TAX_BODY_INTERFACE
2627 (
2628 RECORD_TYPE
2629 ,PROCESS_FLAG
2630 ,PPS_NUMBER
2631 ,EMPLOYER_NUMBER
2632 ,WK_RATE_CUTOFF
2633 ,WORKS_NUMBER
2634 ,MTH_TAX_CREDIT
2635 ,MTH_RATE_CUTOFF
2636 ,WK_TAX_CREDIT
2637 ,CERT_START_DATE
2638 ,CERT_END_DATE
2639 ,WK_MTH_INDICATOR
2640 ,CERT_DATE
2641 ,LAST_NAME
2642 ,FIRST_NAME
2643 ,ADDRESS_LINE1
2644 ,ADDRESS_LINE2
2645 ,ADDRESS_LINE3
2646 ,PROCESSED_FLAG
2647 ,PROCESSED_DATE
2648 ,TOT_PAY_TO_DATE
2649 ,TOT_TAX_TO_DATE
2650 ,EXEMPTION_INDICATOR
2651 ,STD_RATE_OF_TAX
2652 ,HIGHER_RATE_OF_TAX
2653 --
2654 ,CD_YRLY_TAX_CRED
2655 ,CD_YRLY_TAX_RATE_1
2656 ,CD_YRLY_TAX_RATE_2
2657 ,CD_MTHLY_TAX_RATE_2
2658 ,CD_WKLY_TAX_RATE_2
2659 ,CD_TAX_RATE_3
2660 ,CD_YRLY_TAX_RATE_3
2661 ,CD_MTHLY_TAX_RATE_3
2662 ,CD_WKLY_TAX_RATE_3
2663 ,CD_TAX_RATE_4
2664 ,CD_YRLY_TAX_RATE_4
2665 ,CD_MTHLY_TAX_RATE_4
2666 ,CD_WKLY_TAX_RATE_4
2667 ,CD_TAX_RATE_5
2668 ,IN_EXEMPT_USC
2669 ,CD_TOTAL_USC_PAY_TODATE
2670 ,CD_TOTAL_USC_TAX_TODATE
2671 ,CD_USC_RATE_1
2672 ,AM_USC_YRLY_CUTOFF_1
2673 ,AM_USC_MTHLY_CUTOFF_1
2674 ,AM_USC_WKLY_CUTOFF_1
2675 ,CD_USC_RATE_2
2676 ,AM_USC_YRLY_CUTOFF_2
2677 ,AM_USC_MTHLY_CUTOFF_2
2678 ,AM_USC_WKLY_CUTOFF_2
2679 ,CD_USC_RATE_3
2680 ,AM_USC_YRLY_CUTOFF_3
2681 ,AM_USC_MTHLY_CUTOFF_3
2682 ,AM_USC_WKLY_CUTOFF_3
2683 ,CD_USC_RATE_4
2684 ,AM_USC_YRLY_CUTOFF_4
2685 ,AM_USC_MTHLY_CUTOFF_4
2686 ,AM_USC_WKLY_CUTOFF_4
2687 ,CD_USC_RATE_5
2688 --
2689 )
2690 VALUES
2691 (
2692 'PC'
2693 ,NULL
2694 ,l_pps_number
2695 ,l_employer_number
2696 ,l_wk_rate_cutoff
2697 ,l_works_number
2698 ,l_mth_tax_credit
2699 ,l_mth_rate_cutoff
2700 ,l_wk_tax_credit
2701 ,l_cert_start_date
2702 ,l_cert_end_date
2703 ,l_wk_mth_indicator
2704 ,l_cert_date
2705 ,l_last_name
2706 ,l_first_name
2707 ,l_address_line1
2708 ,l_address_line2
2709 ,l_address_line3
2710 ,l_processed_flag
2711 ,l_processed_date
2712 ,l_tot_pay_to_date
2713 ,l_tot_tax_to_date
2714 ,l_exemption_indicator
2715 ,l_std_rate_of_tax
2716 ,l_higher_rate_of_tax
2717 --
2718 ,l_CD_YRLY_TAX_CRED
2719 ,l_CD_YRLY_TAX_RATE_1
2720 ,l_CD_YRLY_TAX_RATE_2
2721 ,l_CD_MTHLY_TAX_RATE_2
2722 ,l_CD_WKLY_TAX_RATE_2
2723 ,l_CD_TAX_RATE_3
2724 ,l_CD_YRLY_TAX_RATE_3
2725 ,l_CD_MTHLY_TAX_RATE_3
2726 ,l_CD_WKLY_TAX_RATE_3
2727 ,l_CD_TAX_RATE_4
2728 ,l_CD_YRLY_TAX_RATE_4
2729 ,l_CD_MTHLY_TAX_RATE_4
2730 ,l_CD_WKLY_TAX_RATE_4
2731 ,l_CD_TAX_RATE_5
2732 ,l_IN_EXEMPT_USC
2733 ,l_CD_TOTAL_USC_PAY_TODATE
2734 ,l_CD_TOTAL_USC_TAX_TODATE
2735 ,l_CD_USC_RATE_1
2736 ,l_AM_USC_YRLY_CUTOFF_1
2737 ,l_AM_USC_MTHLY_CUTOFF_1
2738 ,l_AM_USC_WKLY_CUTOFF_1
2739 ,l_CD_USC_RATE_2
2740 ,l_AM_USC_YRLY_CUTOFF_2
2741 ,l_AM_USC_MTHLY_CUTOFF_2
2742 ,l_AM_USC_WKLY_CUTOFF_2
2743 ,l_CD_USC_RATE_3
2744 ,l_AM_USC_YRLY_CUTOFF_3
2745 ,l_AM_USC_MTHLY_CUTOFF_3
2746 ,l_AM_USC_WKLY_CUTOFF_3
2747 ,l_CD_USC_RATE_4
2748 ,l_AM_USC_YRLY_CUTOFF_4
2749 ,l_AM_USC_MTHLY_CUTOFF_4
2750 ,l_AM_USC_WKLY_CUTOFF_4
2751 ,l_CD_USC_RATE_5
2752 --
2753 );
2754
2755 END IF;
2756
2757 EXCEPTION
2758 WHEN NO_DATA_FOUND THEN
2759 fnd_file.PUT_LINE(FND_FILE.LOG, 'End of file reached.');
2760 EXIT;
2761 WHEN OTHERS THEN
2762 fnd_file.PUT_LINE(FND_FILE.LOG, 'Other error occured');
2763 fnd_file.PUT_LINE(FND_FILE.LOG, sqlerrm);
2764 EXIT;
2765 END;
2766 END LOOP;
2767
2768 utl_file.get_line(l_file_handle,l_present_line); --Reading the Footer Values.
2769
2770 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_present_line last:'||l_present_line);
2771 if (count_tokens(l_present_line) <> 20) then
2772 retcode := 2;
2773 errbuf := 'Trailer Record Format altered.';
2774 fnd_file.PUT_LINE(FND_FILE.OUTPUT,'Delimiter count is not as expected. Record Format altered.');
2775 raise invalid_file_format;
2776 end if;
2777
2778 --Inserting into the Footer table
2779 l_employer_number := l_employer_number;
2780 l_record_no := get_token(l_present_line,21);
2781 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_record_no '||l_record_no);
2782 l_total_mth_rate_cutoff := fnd_number.canonical_to_number(get_token(l_present_line,6));
2783 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_total_mth_rate_cutoff '||l_total_mth_rate_cutoff);
2784 l_total_wk_rate_cutoff := fnd_number.canonical_to_number(get_token(l_present_line,7));
2785 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_total_wk_rate_cutoff '||l_total_wk_rate_cutoff);
2786 l_total_mth_tax_credit := fnd_number.canonical_to_number(get_token(l_present_line,3));
2787 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_total_mth_tax_credit '||l_total_mth_tax_credit);
2788 l_total_wk_tax_credit := fnd_number.canonical_to_number(get_token(l_present_line,4));
2789 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_total_wk_tax_credit '||l_total_wk_tax_credit);
2790
2791 --
2792 l_TC_TOT_YEARLY_TAX_CRED := fnd_number.canonical_to_number(get_token(l_present_line,2));
2793 l_TOT_YEARLY_TAX_RATE_1 := fnd_number.canonical_to_number(get_token(l_present_line,5));
2794 l_TOT_YEARLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line,8));
2795 l_TOT_MONTHLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line,9));
2796 l_TOT_WEEKLY_TAX_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line,10));
2797 l_TOT_YEARLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line,11));
2798 l_TOT_MONTHLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line,12));
2799 l_TOT_WEEKLY_TAX_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line,13));
2800 l_TOT_YEARLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line,14));
2801 l_TOT_MONTHLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line,15));
2802 l_TOT_WEEKLY_TAX_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line,16));
2803 l_TOT_YEARLY_USC_RATE_1 := fnd_number.canonical_to_number(get_token(l_present_line,17));
2804 l_TOT_YEARLY_USC_RATE_2 := fnd_number.canonical_to_number(get_token(l_present_line,18));
2805 l_TOT_YEARLY_USC_RATE_3 := fnd_number.canonical_to_number(get_token(l_present_line,19));
2806 l_TOT_YEARLY_USC_RATE_4 := fnd_number.canonical_to_number(get_token(l_present_line,20));
2807 --
2808 --fnd_file.PUT_LINE(FND_FILE.LOG, '+++++++++TRAILER+++++++++');
2809
2810 INSERT INTO PAY_IE_TAX_TRAILER_INTERFACE
2811 (
2812 RECORD_TYPE
2813 ,EMPLOYER_NUMBER
2814 ,RECORD_NO
2815 ,TOTAL_MTH_RATE_CUTOFF
2816 ,TOTAL_WK_RATE_CUTOFF
2817 ,TOTAL_MTH_TAX_CREDIT
2818 ,TOTAL_WK_TAX_CREDIT
2819 --
2820 ,TC_TOT_YEARLY_TAX_CRED
2821 ,TOT_YEARLY_TAX_RATE_1
2822 ,TOT_YEARLY_TAX_RATE_2
2823 ,TOT_MONTHLY_TAX_RATE_2
2824 ,TOT_WEEKLY_TAX_RATE_2
2825 ,TOT_YEARLY_TAX_RATE_3
2826 ,TOT_MONTHLY_TAX_RATE_3
2827 ,TOT_WEEKLY_TAX_RATE_3
2828 ,TOT_YEARLY_TAX_RATE_4
2829 ,TOT_MONTHLY_TAX_RATE_4
2830 ,TOT_WEEKLY_TAX_RATE_4
2831 ,TOT_YEARLY_USC_RATE_1
2832 ,TOT_YEARLY_USC_RATE_2
2833 ,TOT_YEARLY_USC_RATE_3
2834 ,TOT_YEARLY_USC_RATE_4
2835 --
2836 )
2837 VALUES
2838 (
2839 'TC'
2840 ,l_employer_number
2841 ,l_record_no
2842 ,l_total_mth_rate_cutoff
2843 ,l_total_wk_rate_cutoff
2844 ,l_total_mth_tax_credit
2845 ,l_total_wk_tax_credit
2846 --
2847 ,l_TC_TOT_YEARLY_TAX_CRED
2848 ,l_TOT_YEARLY_TAX_RATE_1
2849 ,l_TOT_YEARLY_TAX_RATE_2
2850 ,l_TOT_MONTHLY_TAX_RATE_2
2851 ,l_TOT_WEEKLY_TAX_RATE_2
2852 ,l_TOT_YEARLY_TAX_RATE_3
2853 ,l_TOT_MONTHLY_TAX_RATE_3
2854 ,l_TOT_WEEKLY_TAX_RATE_3
2855 ,l_TOT_YEARLY_TAX_RATE_4
2856 ,l_TOT_MONTHLY_TAX_RATE_4
2857 ,l_TOT_WEEKLY_TAX_RATE_4
2858 ,l_TOT_YEARLY_USC_RATE_1
2859 ,l_TOT_YEARLY_USC_RATE_2
2860 ,l_TOT_YEARLY_USC_RATE_3
2861 ,l_TOT_YEARLY_USC_RATE_4
2862 --
2863 );
2864
2865
2866 --Validating rows
2867 count_validation_new( errbuf
2868 , retcode
2869 , l_employer_number
2870 , l_tax_year);
2871
2872
2873 --fnd_file.PUT_LINE(FND_FILE.LOG, 'l_tax_year: '||l_tax_year);
2874 --fnd_file.PUT_LINE(FND_FILE.LOG, 'retcode: '||retcode);
2875
2876 IF retcode = '0' Then
2877 valinsupd_new (
2878 errbuf
2879 , retcode
2880 , l_employer_number
2881 , l_tax_year
2882 , p_validate_mode
2883 , p_payroll_id
2884 );
2885 End if;
2886
2887 EXCEPTION
2888 WHEN e_fatal_error
2889 THEN
2890 hr_utility.set_location( l_pkg ,100);
2891
2892 WHEN UTL_FILE.INVALID_OPERATION
2893 THEN
2894
2895 UTL_FILE.FCLOSE(l_file_handle);
2896 hr_utility.set_location( l_pkg ,110);
2897 retcode:=2;
2898 errbuf := 'Reading File - Invalid Operation (file not found).';
2899
2900 WHEN UTL_FILE.INTERNAL_ERROR
2901 THEN
2902
2903 UTL_FILE.FCLOSE(l_file_handle);
2904 hr_utility.set_location( l_pkg,120);
2905 retcode:=2;
2906 errbuf := 'Reading File - Internal Error.';
2907 WHEN UTL_FILE.INVALID_MODE
2908 THEN
2909
2910 UTL_FILE.FCLOSE(l_file_handle);
2911 hr_utility.set_location( l_pkg,130);
2912 retcode:=2;
2913 errbuf := 'Reading Flat File - Invalid Mode.';
2914
2915 WHEN UTL_FILE.INVALID_PATH
2916 THEN
2917
2918 UTL_FILE.FCLOSE(l_file_handle);
2919 hr_utility.set_location( l_pkg ||'PAYE Upload',140);
2920 retcode:=2;
2921 errbuf := 'Reading File - Invalid Path.';
2922
2923 WHEN UTL_FILE.INVALID_FILEHANDLE
2924 THEN
2925
2926 UTL_FILE.FCLOSE(l_file_handle);
2927 hr_utility.set_location( l_pkg,150);
2928 retcode:=2;
2929 errbuf := 'Reading File - Invalid File Handle.';
2930
2931 WHEN UTL_FILE.READ_ERROR
2932 THEN
2933
2934 UTL_FILE.FCLOSE(l_file_handle);
2935 hr_utility.set_location( l_pkg,160);
2936 retcode:=2;
2937 errbuf := 'Reading File - Read Error.';
2938
2939 WHEN NO_DATA_FOUND
2940 THEN
2941 UTL_FILE.FCLOSE(l_file_handle);
2942 hr_utility.set_location( l_pkg,170);
2943 retcode:=2;
2944 errbuf := 'No Data Found.';
2945
2946 WHEN INVALID_FILE_FORMAT
2947 THEN
2948 UTL_FILE.FCLOSE(l_file_handle);
2949 hr_utility.set_location( l_pkg,180);
2950 retcode:=2;
2951 errbuf := 'Reading File - Invalid File Format.';
2952
2953 WHEN NO_REC_FOUND_IN_FILE
2954 THEN
2955 UTL_FILE.FCLOSE(l_file_handle);
2956 hr_utility.set_location( l_pkg,190);
2957 retcode:=2;
2958 errbuf := 'Reading File - No Records found.';
2959
2960 WHEN e_invlid_tax_year
2961 THEN
2962 hr_utility.set_location( l_pkg,200);
2963 retcode:=2;
2964 errbuf := 'Invalid Tax Year.';
2965
2966 WHEN others
2967 THEN
2968 retcode:=2;
2969 errbuf := 'Exception occured :'||sqlerrm;
2970 hr_utility.set_location( l_pkg,210);
2971
2972 END read_tax_file;
2973 --13359423
2974
2975
2976 PROCEDURE getparam(
2977 errbuf OUT NOCOPY VARCHAR2
2978 , retcode OUT NOCOPY VARCHAR2
2979 , p_data_file IN VARCHAR2
2980 , p_employer_number IN VARCHAR2
2981 , p_tax_year IN NUMBER
2982 , p_validate_mode IN VARCHAR2 :='IE_VALIDATE'
2983 , p_payroll_id IN NUMBER := NULL)
2984 IS
2985 Begin
2986 Null;
2987 /* Dummy Procedure created to accept all the parameters once and share them
2988 later in the stages of request set */
2989 retcode := 0;
2990 --13359423
2991 If p_tax_year > 2011 THEN
2992 retcode := 2;
2993 errbuf := 'Invalid Tax Year.';
2994 fnd_file.PUT_LINE(FND_FILE.LOG,'Please enter tax year 2011 or less.');
2995 End if;
2996 --13359423
2997 end;
2998
2999 PROCEDURE count_validation(
3000 errbuf OUT NOCOPY VARCHAR2
3001 , retcode OUT NOCOPY VARCHAR2
3002 , p_employer_number IN VARCHAR2
3003 , p_tax_year IN pay_ie_tax_header_interface.tax_year%TYPE) IS
3004
3005 -- Cursor to get the total values form body
3006 CURSOR c_body
3007 IS
3008 SELECT COUNT(PBS.MTH_TAX_CREDIT) count_mth_taxcredit
3009 , SUM (NVL(PBS.MTH_RATE_CUTOFF,0)) sum_mth_rate_cutoff
3010 , SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
3011 , SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
3012 , SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
3013 FROM PAY_IE_TAX_HEADER_INTERFACE phs
3014 , PAY_IE_TAX_BODY_INTERFACE pbs
3015 WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
3016 AND PHS.TAX_YEAR = p_tax_year
3017 AND PBS.EMPLOYER_NUMBER = p_employer_number;
3018
3019 -- Cursor to get the total values from trailer table
3020 CURSOR c_trailer
3021 IS
3022 SELECT PTS.RECORD_NO count_emp_recno
3023 , NVL(PTS.TOTAL_MTH_RATE_CUTOFF,0) total_mth_cutoff
3024 , NVL(PTS.TOTAL_WK_RATE_CUTOFF,0) total_wk_cutoff
3025 , NVL(PTS.TOTAL_MTH_TAX_CREDIT,0) total_mth_credit
3026 , NVL(PTS.TOTAL_WK_TAX_CREDIT,0) total_wk_credit
3027 FROM PAY_IE_TAX_HEADER_INTERFACE phs
3028 , PAY_IE_TAX_TRAILER_INTERFACE pts
3029 WHERE PTS.EMPLOYER_NUMBER = p_employer_number
3030 AND PHS.TAX_YEAR = p_tax_year;
3031
3032 l_error_stack VARCHAR2 (2000);
3033 l_error VARCHAR2 (80);
3034 l_request_id NUMBER;
3035 l_count_mth_taxcredit NUMBER ;
3036 l_sum_mth_rate_cutoff NUMBER;
3037 l_sum_wk_rate_cutoff NUMBER;
3038 l_sum_mth_tax_credit NUMBER;
3039 l_sum_wk_tax_credit NUMBER;
3040 l_count_emp_recno NUMBER;
3041 l_total_mth_cutoff NUMBER;
3042 l_total_wk_cutoff NUMBER;
3043 l_total_mth_credit NUMBER;
3044 l_total_wk_credit NUMBER;
3045
3046 unequal_value EXCEPTION;
3047 BodyRec c_body%rowtype;
3048 TrailRec c_trailer%rowtype;
3049
3050 BEGIN
3051 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3052
3053
3054 Begin
3055 Delete from pay_ie_tax_error;
3056 if sql%rowcount > 0 then
3057 commit;
3058 end if;
3059 exception
3060 when others then
3061 FND_FILE.PUT_LINE(fnd_file.log,'Error occured while deleting exisiting rows in
3062 PAY_IE_TAX_ERROR table');
3063 end;
3064
3065 UPDATE PAY_IE_TAX_BODY_INTERFACE
3066 SET EMPLOYER_NUMBER =
3067 (SELECT EMPLOYER_NUMBER
3068 FROM PAY_IE_TAX_HEADER_INTERFACE);
3069
3070 OPEN c_body;
3071
3072 FETCH c_body into BodyRec;
3073 IF (c_body%NOTFOUND) THEN
3074 RAISE NO_DATA_FOUND;
3075 END IF;
3076 l_count_mth_taxcredit := BodyRec.count_mth_taxcredit;
3077 l_sum_mth_rate_cutoff := BodyRec.sum_mth_rate_cutoff;
3078 l_sum_wk_rate_cutoff := BodyRec.sum_wk_rate_cutoff;
3079 l_sum_mth_tax_credit := BodyRec.sum_mth_tax_credit;
3080 l_sum_wk_tax_credit := BodyRec.sum_wk_tax_credit;
3081
3082
3083 OPEN c_trailer;
3084
3085 FETCH c_trailer into TrailRec;
3086 IF (c_trailer%NOTFOUND) THEN
3087 RAISE NO_DATA_FOUND;
3088 END IF;
3089 l_count_emp_recno := TrailRec.count_emp_recno;
3090 l_total_mth_cutoff := TrailRec.total_mth_cutoff;
3091 l_total_wk_cutoff := TrailRec.total_wk_cutoff;
3092 l_total_mth_credit := TrailRec.total_mth_credit;
3093 l_total_wk_credit := TrailRec.total_wk_credit;
3094
3095 IF (BodyRec.count_mth_taxcredit = TrailRec.count_emp_recno
3096 AND BodyRec.sum_mth_rate_cutoff = TrailRec.total_mth_cutoff
3097 AND BodyRec.sum_wk_rate_cutoff = TrailRec.total_wk_cutoff
3098 AND BodyRec.sum_mth_tax_credit = TrailRec.total_mth_credit
3099 AND BodyRec.sum_wk_tax_credit = TrailRec.total_wk_credit)
3100 THEN
3101 retcode := 0;
3102 fnd_file.put_line( fnd_file.log, 'FND - CONC-COMPLETION TEXT:NORMAL');
3103
3104 update pay_ie_tax_body_interface
3105 set process_flag = 'Y'
3106 where EMPLOYER_NUMBER = p_employer_number;
3107 Commit;
3108
3109 ELSE
3110 RAISE unequal_value;
3111 END IF;
3112
3113 close c_body;
3114 close c_trailer;
3115
3116 EXCEPTION
3117 WHEN NO_DATA_FOUND THEN
3118 l_error := SQLERRM;
3119 retcode := 2;
3120 FND_FILE.PUT_LINE(fnd_file.log,'No data found');
3121 WHEN unequal_value THEN
3122 errbuf := fnd_message.get;
3123 l_error_stack := errbuf;
3124 l_error := SQLERRM;
3125 retcode := 2;
3126 IF l_count_mth_taxcredit <> l_count_emp_recno THEN
3127 FND_FILE.NEW_LINE(fnd_file.log, 1);
3128 FND_FILE.PUT_LINE(fnd_file.log,
3129 'The total number of record in pay_ie_tax_body_interface is '
3130 || TO_CHAR (l_count_mth_taxcredit));
3131 FND_FILE.PUT_LINE (fnd_file.log,
3132 'The value in pay_ie_tax_trailer_interface.record_no is '
3133 || TO_CHAR(l_count_emp_recno));
3134 FND_FILE.PUT_LINE(fnd_file.log,
3135 'Error - Retcode = 2, total number of record in pay_ie_tax_body_interface');
3136 FND_FILE.PUT_LINE(fnd_file.log,
3137 'does not match the value in pay_ie_tax_trailer_interface.record_no');
3138 END IF;
3139
3140 IF l_sum_mth_rate_cutoff <> l_total_mth_cutoff THEN
3141 FND_FILE.NEW_LINE(fnd_file.log, 1);
3142 FND_FILE.PUT_LINE(fnd_file.log,
3143 'The sum of pay_ie_tax_body_interface.mth_rate_cutoff is '
3144 || TO_CHAR (l_sum_mth_rate_cutoff));
3145 FND_FILE.PUT_LINE (fnd_file.log,
3146 'The total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff is '
3147 || TO_CHAR(l_total_mth_cutoff));
3148 FND_FILE.PUT_LINE(fnd_file.log,
3149 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_rate_cutoff');
3150 FND_FILE.PUT_LINE(fnd_file.log,
3151 'does not match the total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff');
3152 END IF;
3153 IF l_sum_wk_rate_cutoff <> l_total_wk_cutoff THEN
3154 FND_FILE.NEW_LINE(fnd_file.log, 1);
3155 FND_FILE.PUT_LINE(fnd_file.log,
3156 'The sum of pay_ie_tax_body_interface.wk_rate_cutoff is '
3157 || TO_CHAR(l_sum_wk_rate_cutoff));
3158 FND_FILE.PUT_LINE(fnd_file.log,
3159 'The total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff is '
3160 || TO_CHAR(l_total_wk_cutoff));
3161 FND_FILE.PUT_LINE(fnd_file.log,
3162 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.wk_rate_cutoff');
3163 FND_FILE.PUT_LINE(fnd_file.log,
3164 'does not match the the total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff');
3165 END IF;
3166 IF l_sum_mth_tax_credit <> l_total_mth_credit THEN
3167 FND_FILE.NEW_LINE(fnd_file.log, 1);
3168 FND_FILE.PUT_LINE(fnd_file.log,
3169 'The sum of pay_ie_tax_body_interface.mth_tax_credit is '
3170 || TO_CHAR(l_sum_mth_tax_credit));
3171 FND_FILE.PUT_LINE(fnd_file.log,
3172 'The total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit is '
3173 || TO_CHAR(l_total_mth_credit));
3174 FND_FILE.PUT_LINE(fnd_file.log,
3175 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_tax_credit');
3176 FND_FILE.PUT_LINE(fnd_file.log,
3177 'does not match the total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit');
3178 END IF;
3179 IF BodyRec.sum_wk_tax_credit <> TrailRec.total_wk_credit THEN
3180 FND_FILE.NEW_LINE(fnd_file.log, 1);
3181 FND_FILE.PUT_LINE(fnd_file.log,
3182 'The sum of pay_ie_tax_body_interface.wk_tax_credit is '
3183 || TO_CHAR(l_sum_wk_tax_credit));
3184 FND_FILE.PUT_LINE(fnd_file.log,
3185 'The total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit is '
3186 || TO_CHAR(l_total_wk_credit));
3187 FND_FILE.PUT_LINE(fnd_file.log,
3188 'Error - Retcode = 2 because the sum of pay_ie_tax_body_interface.wk_tax_credit');
3189 FND_FILE.PUT_LINE(fnd_file.log,
3190 'does not match the total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit');
3191 END IF;
3192
3193 INSERT INTO pay_ie_tax_error ( pps_number
3194 , employee_number
3195 , full_name
3196 , payroll_name
3197 , error_stack_message
3198 , error_message
3199 , request_id
3200 , error_date)
3201 VALUES (0
3202 , NULL
3203 , NULL
3204 , NULL
3205 , l_error_stack
3206 , l_error
3207 , l_request_id
3208 , sysdate);
3209 COMMIT;
3210
3211 WHEN OTHERS THEN
3212 errbuf := fnd_message.get;
3213 l_error_stack := errbuf;
3214 l_error := SQLERRM;
3215 retcode := 2;
3216 FND_FILE.PUT_LINE (fnd_file.log, 'Error raised in loading data into one or all of the follo
3217 wing tables: pay_ie_tax_header_interface, pay_ie_tax_body_interface, pay_ie_tax_trailer_interface');
3218
3219 INSERT INTO pay_ie_tax_error ( pps_number
3220 , employee_number
3221 , full_name
3222 , payroll_name
3223 , error_stack_message
3224 , error_message
3225 , request_id
3226 , error_date)
3227 VALUES (0
3228 , NULL
3229 , NULL
3230 , NULL
3231 , l_error_stack
3232 , l_error
3233 , l_request_id
3234 , sysdate);
3235 COMMIT;
3236 END count_validation;
3237
3238 -- Procedure to validate every row from the interface table and update
3239 -- PAY_IE_PAYE_DETAILS_F table if required.
3240
3241 PROCEDURE valinsupd (
3242 errbuf OUT NOCOPY VARCHAR2
3243 , retcode OUT NOCOPY VARCHAR2
3244 , p_employer_number IN VARCHAR2
3245 , p_tax_year IN NUMBER
3246 , p_validate_mode IN VARCHAR2 :='IE_VALIDATE'
3247 , p_payroll_id IN NUMBER := NULL
3248 ) AS
3249 --bug 6376140
3250 --BUG 6652299 ADDED DISTINCT KEY WORD TO THE CURSOR C_NO_OF_ASSG
3251 /*Declare cursor to retrieve no.of assignments from person
3252 and interface tables based on input parameters*/
3253 cursor c_no_of_assg IS
3254 select per.person_id person_id
3255 ,per.NATIONAL_IDENTIFIER pps_number
3256 ,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
3257 from per_all_assignments_f asg,
3258 per_all_people_f per,
3259 pay_all_payrolls_f pay,
3260 pay_ie_tax_body_interface tbi,
3261 per_periods_of_service pps
3262 where per.national_identifier = tbi.pps_number
3263 --AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
3264 --AND asg.payroll_id = pay.payroll_id
3265 AND per.person_id = asg.person_id
3266 AND tbi.process_flag = 'Y'
3267 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3268 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
3269 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3270 AND per.effective_end_date >= trunc(tbi.cert_start_date)
3271 and asg.period_of_service_id=pps.period_of_service_id
3272 and pps.person_id=per.person_id
3273 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3274 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'))
3275 --AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3276 --AND pay.effective_end_date >= trunc(tbi.cert_start_date)
3277 group by per.person_id,per.NATIONAL_IDENTIFIER;
3278
3279 /* Cursor check_pps(p_pps_no varchar) is
3280 Select 1 from per_all_people_f per
3281 ,pay_ie_tax_body_interface tbi
3282 Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3283 AND per.effective_end_date >= trunc(tbi.cert_start_date)
3284 and per.national_identifier=p_pps_no
3285 AND tbi.process_flag = 'Y'
3286 and tbi.pps_number=per.national_identifier;
3287 p_check_pps check_pps%rowtype; */
3288
3289 /*Declare cursor to retrieve all employee details from payroll
3290 and interface tables based on input parameters for a multiple assignments*/
3291 CURSOR c_pay(p_pps_number varchar) IS
3292 SELECT distinct per.employee_number employee_no_hr
3293 , per.national_identifier pps_number_hr
3294 , per.last_name last_name_hr
3295 , per.first_name first_name_hr
3296 , asg.assignment_id assignment_id
3297 --, asg.effective_start_date effective_start_date
3298 , hoi.org_information1 tax_district
3299 , pay.payroll_name payroll_name_hr
3300 , pay.payroll_id payroll_id -- 4878630
3301 --, ppd.paye_details_id paye_details_id --4878630
3302 --, ppd.object_version_number object_version_no --4878630
3303 --, ppd.effective_start_date ppd_effective_start_date
3304 , tbi.pps_number pps_number_int
3305 , asg.assignment_number employee_no_int --5724436
3306 , tbi.first_name first_name_int
3307 , tbi.last_name last_name_int
3308 , tbi.cert_start_date cert_start_date
3309 , tbi.cert_end_date cert_end_date
3310 , tbi.cert_date cert_date
3311 , tbi.wk_tax_credit/100 wk_tax_credit
3312 , tbi.mth_tax_credit/100 mth_tax_credit
3313 , tbi.wk_rate_cutoff/100 wk_rate_cutoff
3314 , tbi.mth_rate_cutoff/100 mth_rate_cutoff
3315 -- Bug Fix 3500192
3316 , tbi.wk_mth_indicator wk_mth_indicator
3317 -- Bug Fix 4618981
3318 , tbi.exemption_indicator exemption_indicator
3319 , tbi.tot_tax_to_date/100 tot_tax_to_date
3320 , tbi.tot_pay_to_date/100 tot_pay_to_date
3321 , tbi.std_rate_of_tax std_rate_of_tax
3322 , tbi.higher_rate_of_tax higher_rate_of_tax
3323 FROM hr_organization_information hoi
3324 , hr_organization_units hou
3325 , per_all_assignments_f asg
3326 , per_all_people_f per
3327 , pay_all_payrolls_f pay
3328 --, pay_ie_paye_details_f ppd
3329 , pay_ie_tax_body_interface tbi
3330 , pay_ie_tax_header_interface thi
3331 WHERE per.person_id = asg.person_id
3332 AND per.national_identifier = tbi.pps_number
3333 AND asg.business_group_id = hou.business_group_id
3334 AND hou.organization_id = hoi.organization_id
3335 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
3336 AND hoi.org_information2 = p_employer_number
3337 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
3338 AND asg.payroll_id = pay.payroll_id
3339 --AND asg.assignment_id = ppd.assignment_id
3340 AND per.national_identifier = p_pps_number
3341 -- Bug Fix 3500192
3342 -- added for multiple assignment issue 5894942
3343 AND asg.assignment_number = tbi.works_number
3344 AND thi.employer_number = p_employer_number
3345 AND thi.tax_year = p_tax_year
3346 AND tbi.employer_number = thi.employer_number
3347 AND tbi.process_flag = 'Y'
3348 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3349 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
3350 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3351 AND per.effective_end_date >= trunc(tbi.cert_start_date)
3352 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3353 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
3354 --bug 6376140
3355 /*Declare cursor to retrieve all employee details from payroll
3356 and interface tables based on input parameters for a single assignment*/
3357 CURSOR c_pay1(p_pps_number varchar) IS
3358 SELECT distinct per.employee_number employee_no_hr
3359 , per.national_identifier pps_number_hr
3360 , per.last_name last_name_hr
3361 , per.first_name first_name_hr
3362 , asg.assignment_id assignment_id
3363 --, asg.effective_start_date effective_start_date
3364 , hoi.org_information1 tax_district
3365 , pay.payroll_name payroll_name_hr
3366 , pay.payroll_id payroll_id -- 4878630
3367 --, ppd.paye_details_id paye_details_id --4878630
3368 --, ppd.object_version_number object_version_no --4878630
3369 --, ppd.effective_start_date ppd_effective_start_date
3370 , tbi.pps_number pps_number_int
3371 , asg.assignment_number employee_no_int --5724436
3372 , tbi.first_name first_name_int
3373 , tbi.last_name last_name_int
3374 , tbi.cert_start_date cert_start_date
3375 , tbi.cert_end_date cert_end_date
3376 , tbi.cert_date cert_date
3377 , tbi.wk_tax_credit/100 wk_tax_credit
3378 , tbi.mth_tax_credit/100 mth_tax_credit
3379 , tbi.wk_rate_cutoff/100 wk_rate_cutoff
3380 , tbi.mth_rate_cutoff/100 mth_rate_cutoff
3381 -- Bug Fix 3500192
3382 , tbi.wk_mth_indicator wk_mth_indicator
3383 -- Bug Fix 4618981
3384 , tbi.exemption_indicator exemption_indicator
3385 , tbi.tot_tax_to_date/100 tot_tax_to_date
3386 , tbi.tot_pay_to_date/100 tot_pay_to_date
3387 , tbi.std_rate_of_tax std_rate_of_tax
3388 , tbi.higher_rate_of_tax higher_rate_of_tax
3389 FROM hr_organization_information hoi
3390 , hr_organization_units hou
3391 , per_all_assignments_f asg
3392 , per_all_people_f per
3393 , pay_all_payrolls_f pay
3394 --, pay_ie_paye_details_f ppd
3395 , pay_ie_tax_body_interface tbi
3396 , pay_ie_tax_header_interface thi,
3397 per_periods_of_service pps
3398 WHERE per.person_id = asg.person_id
3399 AND per.national_identifier = tbi.pps_number
3400 AND asg.business_group_id = hou.business_group_id
3401 AND hou.organization_id = hoi.organization_id
3402 AND hoi.org_information_context = 'IE_EMPLOYER_INFO' -- For Employer changes 4369280
3403 AND hoi.org_information2 = p_employer_number
3404 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
3405 AND asg.payroll_id = pay.payroll_id
3406 --AND asg.assignment_id = ppd.assignment_id
3407 AND per.national_identifier = p_pps_number
3408 -- Bug Fix 3500192
3409 -- added for multiple assignment issue 5894942
3410 --AND asg.assignment_number = tbi.works_number
3411 AND thi.employer_number = p_employer_number
3412 AND thi.tax_year = p_tax_year
3413 AND tbi.employer_number = thi.employer_number
3414 AND tbi.process_flag = 'Y'
3415 and asg.period_of_service_id=pps.period_of_service_id
3416 and pps.person_id=per.person_id
3417 --and pps.date_start <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3418 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'))
3419 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3420 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
3421 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3422 AND per.effective_end_date >= trunc(tbi.cert_start_date)
3423 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3424 AND pay.effective_end_date >= trunc(tbi.cert_start_date);
3425
3426 -- cursor get the skipped assignments. Assignments that didnt get processed
3427 -- in the process
3428 --bug 6376140 cursor modified for processing single assg
3429 /* cursor csr_skipped_asg is
3430 select pps_number, works_number from pay_ie_tax_body_interface
3431 minus
3432 SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
3433 FROM hr_organization_information hoi
3434 , hr_organization_units hou
3435 , per_all_assignments_f asg
3436 , per_all_people_f per
3437 , pay_all_payrolls_f pay
3438 --, pay_ie_paye_details_f ppd
3439 , pay_ie_tax_body_interface tbi
3440 , pay_ie_tax_header_interface thi
3441 WHERE per.person_id = asg.person_id
3442 AND asg.business_group_id = hou.business_group_id
3443 AND hou.organization_id = hoi.organization_id
3444 AND hoi.org_information_context = 'IE_EMPLOYER_INFO'
3445 AND hoi.org_information2 = p_employer_number
3446 AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
3447 AND asg.payroll_id = pay.payroll_id
3448 AND per.national_identifier = tbi.pps_number
3449 AND asg.assignment_number = tbi.works_number
3450 AND thi.employer_number = p_employer_number
3451 AND thi.tax_year = p_tax_year
3452 AND tbi.employer_number = thi.employer_number
3453 AND tbi.process_flag = 'Y'
3454 AND asg.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3455 AND asg.effective_end_date >= trunc(tbi.cert_start_date)
3456 AND per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3457 AND per.effective_end_date >= trunc(tbi.cert_start_date)
3458 AND pay.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
3459 AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
3460
3461 cursor csr_skipped_assignments is
3462 select distinct pps_number pps_number,works_number,last_name,first_name
3463 from pay_ie_tax_body_interface;
3464
3465 /* check to see if any single paye details exists */
3466 cursor get_paye_details (p_assignment_id number) is
3467 select count(*) from pay_ie_paye_details_f where
3468 assignment_id = p_assignment_id;
3469
3470 -- For Bug 5724436
3471 -- Cursor to get the max assignment action id, to fetch the P45 details.
3472 cursor get_p45_details (p_assignment_id number) is
3473 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa
3474 from pay_assignment_actions paa,
3475 pay_payroll_actions ppa
3476 where ppa.payroll_action_id = paa.payroll_action_id
3477 and paa.assignment_id = p_assignment_id
3478 and to_number(to_char(ppa.effective_date,'YYYY')) = p_tax_year;
3479
3480 -- Cursor to fetch existing PAYE details.
3481 cursor c_get_paye_details(p_assignment_id number
3482 ,p_ppsn varchar2
3483 ,p_cert_start_date date
3484 ,p_assignment_number varchar2) is
3485 select distinct ppd.*
3486 from per_all_people_f papf,
3487 per_all_assignments_f paaf,
3488 pay_ie_paye_details_f ppd
3489 where papf.national_identifier = p_ppsn
3490 and papf.person_id = paaf.person_id
3491 and paaf.assignment_id = p_assignment_id
3492 and paaf.assignment_id = ppd.assignment_id
3493 and paaf.assignment_number = p_assignment_number
3494 AND paaf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
3495 AND paaf.effective_end_date >= trunc(p_cert_start_date)
3496 AND papf.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
3497 AND papf.effective_end_date >= trunc(p_cert_start_date)
3498 AND ppd.effective_start_date <= to_date('31/12/'||to_char(p_cert_start_date,'YYYY'),'dd/mm/yyyy')
3499 AND ppd.effective_end_date >= trunc(p_cert_start_date);
3500
3501 -- Cursor to see if payroll exists for an assignment.
3502 CURSOR csr_pay_freq (p_assignment_id NUMBER,
3503 p_effective_date DATE) IS
3504 SELECT pp.period_type
3505 FROM pay_payrolls_f pp, per_assignments_f pa
3506 WHERE pa.assignment_id = p_assignment_id
3507 AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
3508 AND pp.payroll_id = pa.payroll_id
3509 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
3510
3511 pay_freq_rec csr_pay_freq%ROWTYPE;
3512
3513 -- cursor get values from globals bug 5766334
3514 cursor csr_get_global_value(p_global_name varchar2,
3515 p_cert_date date) is
3516 select global_value
3517 from ff_globals_f
3518 where global_name = p_global_name
3519 and p_cert_date between effective_start_date and effective_end_date;
3520
3521 l_tax_rate_exempt ff_globals_f.global_value%TYPE;
3522 l_tax_rate_high ff_globals_f.global_value%TYPE;
3523 --bug 6376140
3524 --follw variables are added
3525 r_pay c_pay%rowtype;
3526 TYPE t_pps_number IS TABLE OF varchar2(30) INDEX BY Binary_Integer;
3527 l_pps_number t_pps_number;
3528 ppsno_cnt number :=0;
3529 flag char :='N';
3530 --end of bug 6376140
3531 -- End bug 5766334
3532
3533 --Declare variables
3534 l_error VARCHAR2(2000);
3535 l_error_stack VARCHAR2(2000) := NULL;
3536 l_request_id NUMBER;
3537 l_program_application_id NUMBER;
3538 l_program_id NUMBER;
3539 l_comm_period_no NUMBER;
3540 l_pps_number_hr VARCHAR2(9);
3541 l_employee_number_hr per_all_people_F.employee_number%TYPE; -- bug 5766372
3542 l_last_name_hr per_people_f.last_name%TYPE;
3543 l_first_name_hr per_people_f.first_name%TYPE;
3544 l_last_name_int VARCHAR2(20);
3545 l_first_name_int VARCHAR2(20);
3546 l_payroll_name_hr VARCHAR2(80);
3547 l_tax_district NUMBER;
3548 l_pps_number_int VARCHAR2(9);
3549 l_employee_number_int per_all_assignments_f.assignment_number%TYPE; -- bug 5766372
3550 l_validate BOOLEAN := FALSE;
3551 -- Bug Fix 3500192
3552 l_datetrack_mode VARCHAR2(12);
3553 l_tax_basis pay_ie_paye_details_f.tax_basis%TYPE; -- bug 5766372
3554 l_header_count NUMBER := 0;
3555 l_record_count NUMBER := 0;
3556 l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%TYPE;
3557 l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%TYPE;
3558
3559 --Declare output parameters from api row handlersb
3560 l_ins_paye_details_id NUMBER;
3561 l_ins_object_version_no NUMBER;
3562 l_ins_effective_start_date DATE;
3563 l_ins_effective_end_date DATE;
3564 l_upd_effective_start_date DATE;
3565 l_upd_effective_end_date DATE;
3566 l_flag number;
3567
3568 -- Bug Fix 3500192
3569 -- name_not_equal EXCEPTION;
3570 -- same_day EXCEPTION;
3571 future_day EXCEPTION;
3572 std_rate_of_tax_is_null EXCEPTION;
3573 higher_rate_of_tax_is_null EXCEPTION;
3574 exemption_is_null EXCEPTION;
3575 exemption_mismatch EXCEPTION;
3576 normal_tax_mismatch EXCEPTION;
3577 pay_to_date EXCEPTION;
3578 l_paye_count NUMBER(3);
3579 o_paye_details_id NUMBER;
3580 o_ovn NUMBER;
3581 o_effective_start_date DATE;
3582 o_effective_end_date DATE;
3583 l_effective_date DATE; -- Bug 6929566
3584
3585 l_tax_to_date NUMBER;
3586 l_pay_to_date NUMBER;
3587 l_max_action_id NUMBER := 0;
3588 r_paye_details c_get_paye_details%ROWTYPE;
3589 r_empty_details c_get_paye_details%ROWTYPE;
3590
3591 BEGIN
3592 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3593 retcode := 1;
3594 -- Bug 5724436, the audit report will be called only in mode="Validate"
3595 IF p_validate_mode <> 'IE_VALIDATE' THEN
3596 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11, ' ')||lpad('Works Number',15,' ')||lpad('Status',30,' ')); --4878630
3597 fnd_file.put_line(fnd_file.output,lpad('----------',11, ' ')||lpad('------------',15,' ')||lpad('------',30,' '));
3598 ELSE
3599 -- bug 5724436
3600 -- This is called only once to set the report fields.
3601 fnd_file.put_line(fnd_file.output,'Index');
3602 fnd_file.put_line(fnd_file.output,'I :- Week1/Month1 Indicator');
3603 fnd_file.put_line(fnd_file.output,'F :- Exemption Flag');
3604 fnd_file.put_line(fnd_file.output,'Tax1 :- Standard Rate of Tax');
3605 fnd_file.put_line(fnd_file.output,'Tax2 :- Higher Rate of Tax');
3606 fnd_file.put_line(fnd_file.output,' ');
3607
3608 fnd_file.put_line(fnd_file.output,lpad('PAYE Details in Oracle Payroll',70,' ')||lpad('PAYE Details from Revenue',105,' '));
3609 fnd_file.put_line(fnd_file.output,lpad('==============================',70,' ')||lpad('=========================',105,' '));
3610 fnd_file.put_line(fnd_file.output,' ');
3611 fnd_file.put_line(fnd_file.output,lpad('PPS Number',11,' ')
3612 -- for previous PAYE Details
3613 || lpad('Works',13,' ')
3614 || lpad('Last Name',16,' ')
3615 || lpad('I',3,' ')||lpad('F',3,' ')
3616 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
3617 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
3618 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
3619 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
3620 || lpad('Tax2',6,' ')|| lpad(' ',10,' ')
3621 -- for Current PAYE Details
3622 || lpad('I',3,' ')||lpad('F',3,' ')
3623 || lpad('Mth Std',9,' ')||lpad('Mth Tax',9,' ')
3624 || lpad('Week Std',10,' ')||lpad('Week Tax',10,' ')
3625 || lpad('Cert Issue',12,' ')||lpad('Tot Pay',12,' ')
3626 || lpad('Tot Tax',12,' ')||lpad('Tax1',6,' ')
3627 || lpad('Tax2',6,' '));
3628
3629 fnd_file.put_line(fnd_file.output, lpad('Number',24,' ')
3630 || lpad('Cutoff',30,' ')||lpad('Credit',9,' ')
3631 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
3632 || lpad('Date',10,' ')||lpad('to Date',15,' ')
3633 || lpad('to Date',12,' ') ||lpad(' ',28,' ')
3634 -- for Current PAYE Details
3635 || lpad('Cutoff',8,' ')||lpad('Credit',9,' ')
3636 || lpad('Cutoff',10,' ')||lpad('Credit',10,' ')
3637 || lpad('Date',10,' ')||lpad('to Date',15,' ')
3638 || lpad('to Date',12,' '));
3639
3640 fnd_file.put_line(fnd_file.output,lpad('----------',11,' ')
3641 -- for previous PAYE Details
3642 || lpad('----------',13,' ')
3643 || lpad('---------',16,' ')
3644 || lpad('-',3,' ')||lpad('-',3,' ')
3645 || lpad('-------',9,' ')||lpad('-------',9,' ')
3646 || lpad('--------',10,' ')||lpad('--------',10,' ')
3647 || lpad('----------',12,' ')||lpad('-------',12,' ')
3648 || lpad('-------',12,' ')||lpad('----',6,' ')
3649 || lpad('----',6,' ')|| lpad(' ',10,' ')
3650 -- for Current PAYE Details
3651 || lpad('-',3,' ')||lpad('-',3,' ')
3652 || lpad('-------',9,' ')||lpad('-------',9,' ')
3653 || lpad('--------',10,' ')||lpad('--------',10,' ')
3654 || lpad('----------',12,' ')||lpad('-------',12,' ')
3655 || lpad('-------',12,' ')||lpad('----',6,' ')
3656 || lpad('----',6,' '));
3657
3658 g_validate_count := 1;
3659 END IF;
3660 -- END 5724436
3661 --BUG 6652299 ADDED L_FLAG
3662 FOR r_no_of_assg IN c_no_of_assg
3663 LOOP
3664 BEGIN
3665 l_flag :=0;
3666 --bug 6376140
3667 IF r_no_of_assg.no_of_assg =1
3668 THEN OPEN c_pay1(r_no_of_assg.pps_number);
3669 FETCH c_pay1 INTO r_pay;
3670 IF c_pay1%FOUND THEN
3671 l_flag:=1;
3672 END IF;
3673 CLOSE c_pay1;
3674 ELSIF r_no_of_assg.no_of_assg >1
3675 THEN OPEN c_pay(r_no_of_assg.pps_number);
3676 FETCH c_pay INTO r_pay;
3677 IF c_pay%FOUND THEN
3678 l_flag:=1;
3679 END IF;
3680 CLOSE c_pay;
3681 END IF;
3682 --end if;
3683
3684 --end of bug 6376140
3685
3686
3687 -- Bug 6929566 Start
3688 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_date is ' || r_pay.cert_date);
3689 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_start_date is ' || r_pay.cert_start_date);
3690 FND_FILE.PUT_LINE(FND_FILE.LOG, 'r_pay.cert_end_date is ' || r_pay.cert_end_date);
3691 IF r_pay.cert_date < r_pay.cert_start_date THEN
3692 l_effective_date := r_pay.cert_start_date;
3693 ELSIF r_pay.cert_date >= r_pay.cert_start_date THEN
3694 l_effective_date := r_pay.cert_date;
3695 END IF;
3696 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_effective_date is ' || l_effective_date);
3697 --
3698 -- Bug 6929566 End
3699
3700 --FOR r_pay IN c_pay
3701 --LOOP
3702 --BEGIN
3703 --IF r_pay.pps_number_hr IS NOT NULL THEN
3704 --Initialize local variables on each loop pass to pass to outer exception handler
3705 IF l_flag=1 THEN
3706 l_pps_number_hr := r_pay.pps_number_hr;
3707 l_employee_number_hr := r_pay.employee_no_hr;
3708 l_first_name_hr := r_pay.first_name_hr;
3709 l_last_name_hr := r_pay.last_name_hr;
3710 l_payroll_name_hr := r_pay.payroll_name_hr;
3711 l_tax_district := r_pay.tax_district;
3712 l_pps_number_int := r_pay.pps_number_int;
3713 l_employee_number_int := r_pay.employee_no_int;
3714 --l_ins_object_version_no := r_pay.object_version_no;
3715 l_std_rate_of_tax := r_pay.std_rate_of_tax;
3716 l_higher_rate_of_tax := r_pay.higher_rate_of_tax;
3717 --bug 6376140
3718 --ppsno_cnt := ppsno_cnt+1;
3719 IF l_pps_number_hr IS NOT NULL THEN
3720 ppsno_cnt := ppsno_cnt+1;
3721 l_pps_number(ppsno_cnt) :=r_pay.pps_number_hr;
3722 --fnd_file.put_line(l_pps_number(ppsno_cnt));
3723 hr_utility.set_location('PPS Number..'||l_pps_number_hr,420);
3724 -- checks for standard rate tax to be mandatory
3725 IF l_std_rate_of_tax IS NULL THEN
3726 raise std_rate_of_tax_is_null;
3727 END IF; --l_std_rate_of_tax
3728 -- checks for higher rate of tax to be mandatory
3729 IF l_higher_rate_of_tax IS NULL THEN
3730 raise higher_rate_of_tax_is_null;
3731 END IF;--l_higher_rate_of_tax
3732 -- checks for exemption indicator to be mandatory
3733 IF r_pay.exemption_indicator is null then
3734 raise exemption_is_null; --r_pay.exemption_indicator
3735 END IF;
3736 -- checks for exact rate of tax bug 5766334
3737 open csr_get_global_value('IE_TAX_RATE_EXEMPT',l_effective_date); -- Bug 6929566
3738 FETCH csr_get_global_value into l_tax_rate_exempt;
3739 CLOSE csr_get_global_value;
3740
3741 open csr_get_global_value('IE_TAX_RATE2', l_effective_date); -- Bug 6929566
3742 FETCH csr_get_global_value into l_tax_rate_high;
3743 CLOSE csr_get_global_value;
3744
3745 -- end bug 5766334
3746
3747
3748 IF r_pay.exemption_indicator='Y' AND r_pay.higher_rate_of_tax <> l_tax_rate_exempt then
3749 raise exemption_mismatch;
3750 END IF; --r_pay.exemption_indicator
3751 -- checks for exact rate of tax
3752 IF r_pay.exemption_indicator='N' AND r_pay.higher_rate_of_tax <> l_tax_rate_high then
3753 raise normal_tax_mismatch;
3754 END IF; --r_pay.exemption_indicator
3755 hr_utility.set_location('PPS Number..Initial check'||l_pps_number_hr,421);
3756 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,101);
3757 --
3758 IF r_pay.tot_pay_to_date is null and r_pay.tot_pay_to_date is null and
3759 (r_pay.wk_mth_indicator = 0 or (r_pay.wk_mth_indicator=1 and r_pay.exemption_indicator='Y')) then
3760 raise pay_to_date;
3761 END IF;
3762 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,422);
3763
3764 -- check if cerificate start date is before or equal to certificate end date
3765
3766 pay_ipd_bus.chk_cert_start_end_dates(
3767 p_certificate_start_date => r_pay.cert_start_date
3768 , p_certificate_end_date => r_pay.cert_end_date
3769 );
3770 hr_utility.set_location('PPS Number..third check'||l_pps_number_hr,423);
3771 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,102);
3772 -- Bug Fix 3500192
3773 -- tax basis is set as per the value of week month indicator in the interface table
3774 IF (r_pay.wk_mth_indicator = 1) THEN
3775 l_tax_basis := 'IE_WEEK1_MONTH1';
3776 ELSE
3777 l_tax_basis := 'IE_CUMULATIVE';
3778 END IF;
3779 -- Bug Fix 4618981
3780 IF r_pay.exemption_indicator='Y' then
3781 IF r_pay.wk_mth_indicator = 1 THEN
3782 l_tax_basis := 'IE_EXEMPT_WEEK_MONTH';
3783 ELSE
3784 l_tax_basis := 'IE_EXEMPTION';
3785 END IF;
3786 END IF;
3787
3788 hr_utility.set_location('l_tax_basis'||l_tax_basis,424);
3789 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,103);
3790 -- check if amounts are valid for the given tax basis, for 'Emergency'
3791 -- tax basis weekly and monthly tax credits ans std rate cut-off amounts must
3792 -- be null and for other values of tax basis weekly or monthly amounts
3793 -- (depending on payroll frequency) must be not null.
3794
3795 /*pay_ipd_bus.chk_tax_basis_amounts(
3796 p_effective_date => r_pay.cert_start_date
3797 , p_assignment_id => r_pay.assignment_id
3798 -- Bug Fix 3500192
3799 --, p_tax_basis => 'IE_CUMULATIVE'
3800 , p_tax_basis => l_tax_basis
3801 , p_weekly_tax_credit => r_pay.wk_tax_credit
3802 , p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
3803 , p_monthly_tax_credit => r_pay.mth_tax_credit
3804 , p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
3805 );*/
3806
3807 hr_utility.set_location('pay_ipd_bus.chk_tax_basis_amounts'||l_pps_number_hr,424);
3808 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,104);
3809 /* If the validate mode is 'Validate and Rollback' then set the validation input paramter
3810 to true else for all modes it is set to false*/
3811
3812 IF p_validate_mode = 'IE_VALIDATE_ROLLBACK' THEN
3813 l_validate := TRUE;
3814 ELSE
3815 l_validate := FALSE;
3816 END IF;
3817
3818 --IF (r_pay.paye_details_id IS NOT NULL AND
3819 IF (p_validate_mode = 'IE_VALIDATE_ROLLBACK' OR p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
3820 hr_utility.set_location('PPS Number..'||l_pps_number_hr,425);
3821 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,105);
3822 OPEN get_paye_details(r_pay.assignment_id);
3823 FETCH get_paye_details INTO l_paye_count;
3824 CLOSE get_paye_details;
3825 -- to check if the payroll is attached to the assignment as of certificate
3826 -- issue date. This is called irrespective of whether PAYE details exists
3827 -- or not.
3828 pay_ipd_bus.chk_tax_basis_amounts(
3829 p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5396580
3830 ,p_assignment_id => r_pay.assignment_id
3831 -- Bug Fix 3500192
3832 -- , p_tax_basis => 'IE_CUMULATIVE'
3833 ,p_tax_basis => l_tax_basis
3834 ,p_weekly_tax_credit => r_pay.wk_tax_credit
3835 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
3836 ,p_monthly_tax_credit => r_pay.mth_tax_credit
3837 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
3838 );
3839 IF l_paye_count <> 0 THEN
3840 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,426);
3841
3842
3843 hr_utility.set_location(r_pay.cert_date, 10);
3844
3845 pay_ie_paye_pkg.update_paye_change_freq --4878630
3846 (p_assignment_id => r_pay.assignment_id
3847 ,p_effective_date => l_effective_date -- Bug 6929566 -- r_pay.cert_date -- 5724436
3848 ,p_payroll_id => r_pay.payroll_id
3849 ,P_DATETRACK_UPDATE_MODE => 'UPDATE'
3850 ,p_tax_upload_flag => 'TU'
3851 ,p_tax_basis => l_tax_basis
3852 ,p_cert_start_date => r_pay.cert_start_date -- 17140460.6
3853 ,p_cert_end_date => r_pay.cert_end_date
3854 ,p_weekly_tax_credit => r_pay.wk_tax_credit
3855 ,p_monthly_tax_credit => r_pay.mth_tax_credit
3856 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
3857 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
3858 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
3859 ,p_pay_to_date => r_pay.tot_pay_to_date
3860 ,p_cert_date =>r_pay.cert_date);
3861
3862 hr_utility.set_location(r_pay.cert_date, 20);
3863 hr_utility.set_location('l_paye_count <> 0'||l_pps_number_hr,427);
3864 ELSE
3865 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,428);
3866 hr_utility.set_location(r_pay.cert_date, 30);
3867 pay_ie_paye_api.create_ie_paye_details --4878630
3868 (p_validate => false
3869 ,p_effective_date => l_effective_date -- Bug 6929566
3870 ,p_assignment_id => r_pay.assignment_id
3871 ,p_info_source => 'IE_ELECTRONIC'
3872 ,p_tax_basis => l_tax_basis
3873 ,p_certificate_start_date => r_pay.cert_start_date -- For bug 5396549
3874 ,p_tax_assess_basis => 'IE_SEP_TREAT'
3875 ,p_certificate_issue_date => r_pay.cert_date
3876 ,p_certificate_end_date => r_pay.cert_end_date
3877 ,p_weekly_tax_credit => r_pay.wk_tax_credit
3878 ,p_weekly_std_rate_cut_off => r_pay.wk_rate_cutoff
3879 ,p_monthly_tax_credit => r_pay.mth_tax_credit
3880 ,p_monthly_std_rate_cut_off => r_pay.mth_rate_cutoff
3881 ,p_tax_deducted_to_date => r_pay.tot_tax_to_date
3882 ,p_pay_to_date => r_pay.tot_pay_to_date
3883 ,p_disability_benefit => null
3884 ,p_lump_sum_payment => null
3885 ,p_paye_details_id => o_paye_details_id
3886 ,p_object_version_number => o_ovn
3887 ,p_effective_start_date => o_effective_start_date
3888 ,p_effective_end_date => o_effective_end_date);
3889 hr_utility.set_location(r_pay.cert_date, 40);
3890 hr_utility.set_location('l_paye_count = 0'||l_pps_number_hr,429);
3891 END IF;
3892 END IF;
3893 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,106);
3894 retcode := 0;
3895
3896
3897 /*Update interface table and set processed flag to 'Yes' to record that record has been updated or
3898 inserted into payroll tables successfully using the row handler APIs.*/
3899
3900 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
3901 UPDATE pay_ie_tax_body_interface
3902 SET processed_flag = 'Y'
3903 WHERE pps_number = r_pay.pps_number_int;
3904 END IF;
3905 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,107);
3906 IF p_validate_mode <> 'IE_VALIDATE' THEN
3907 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');
3908 ELSE
3909 IF g_validate_count =1 then
3910 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,841);
3911 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,108);
3912 OPEN c_get_paye_details(r_pay.assignment_id,
3913 r_pay.pps_number_int,
3914 l_effective_date, -- Bug 6929566 -- r_pay.cert_date,
3915 r_pay.employee_no_int);
3916 FETCH c_get_paye_details INTO r_paye_details;
3917 -- IF no PAYE details exists then set the default values for PAYE.
3918 IF c_get_paye_details%ROWCOUNT = 0 then
3919 r_paye_details.tax_basis := 'IE_EMERGENCY';
3920 r_paye_details.certificate_issue_date := to_date('01/01/0001','dd/mm/yyyy');
3921 r_paye_details.WEEKLY_TAX_CREDIT := 0;
3922 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
3923 r_paye_details.MONTHLY_TAX_CREDIT := 0;
3924 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
3925 -- bug 5837091
3926 ELSIF r_paye_details.tax_basis in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS') then
3927 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'));
3928 r_paye_details.WEEKLY_TAX_CREDIT := 0;
3929 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := 0;
3930 r_paye_details.MONTHLY_TAX_CREDIT := 0;
3931 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := 0;
3932 else
3933 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'));
3934 r_paye_details.WEEKLY_TAX_CREDIT := nvl(r_paye_details.WEEKLY_TAX_CREDIT,0);
3935 r_paye_details.WEEKLY_STD_RATE_CUT_OFF := nvl(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,0);
3936 r_paye_details.MONTHLY_TAX_CREDIT := nvl(r_paye_details.MONTHLY_TAX_CREDIT,0);
3937 r_paye_details.MONTHLY_STD_RATE_CUT_OFF := nvl(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,0);
3938 END IF;
3939 -- end bug 5837091
3940 CLOSE c_get_paye_details;
3941 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,842);
3942
3943 OPEN csr_pay_freq (r_pay.assignment_id,l_effective_date); -- Bug 6929566 --r_pay.cert_date);
3944 FETCH csr_pay_freq INTO pay_freq_rec;
3945 --
3946 IF csr_pay_freq%NOTFOUND THEN
3947 CLOSE csr_pay_freq;
3948 hr_utility.set_message(801, 'HR_IE_ASG_NOT_IN_PAYROLL');
3949 hr_utility.raise_error;
3950 END IF;
3951 --
3952 CLOSE csr_pay_freq;
3953 hr_utility.set_location('PPS Number..Second check'||l_pps_number_hr,843);
3954
3955 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,109);
3956 OPEN get_p45_details(r_pay.assignment_id);
3957 FETCH get_p45_details INTO l_max_action_id;
3958 CLOSE get_p45_details;
3959
3960 hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,110);
3961 hr_utility.set_location('Assignment Action ID..'||l_max_action_id,110);
3962 -- This will be called only if has any assignment actions.
3963 IF l_max_action_id <> 0 THEN
3964 hr_utility.set_location('Assignment Action ID is not null',112);
3965 l_pay_to_date := NVL (
3966 ROUND (
3967 TO_NUMBER (
3968 pay_balance_pkg.get_value (
3969 pay_ie_p35.get_defined_balance_id (
3970 '_ASG_YTD',
3971 'IE P45 Pay'
3972 ),
3973 l_max_action_id
3974 )
3975 ),
3976 2
3977 ),
3978 0
3979 );
3980 l_tax_to_date := NVL (
3981 ROUND (
3982 TO_NUMBER (
3983 pay_balance_pkg.get_value (
3984 pay_ie_p35.get_defined_balance_id (
3985 '_ASG_YTD',
3986 'IE P45 Tax Deducted'
3987 ),
3988 l_max_action_id
3989 )
3990 ),
3991 2
3992 ),
3993 0
3994 );
3995 ELSE
3996 hr_utility.set_location('Assignment Action ID is null',113);
3997 l_pay_to_date := 0;
3998 l_tax_to_date := 0;
3999 END IF;
4000 -- print the values.
4001 fnd_file.put_line(fnd_file.output,lpad(r_pay.pps_number_int,11,' ')
4002 -- for previous PAYE Details
4003 || lpad(substr(r_pay.employee_no_int,1,12),13,' ')
4004 || lpad(substr(r_pay.last_name_hr,1,15),16,' ')
4005 || lpad(pay_ie_paye_pkg.decode_value_char(r_paye_details.tax_basis='IE_WEEK1_MONTH1','1','0'),3,' ')
4006 || 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,' ')
4007 || lpad(r_paye_details.MONTHLY_STD_RATE_CUT_OFF,9,' ')||lpad(r_paye_details.MONTHLY_TAX_CREDIT,9,' ')
4008 || lpad(r_paye_details.WEEKLY_STD_RATE_CUT_OFF,10,' ')||lpad(r_paye_details.WEEKLY_TAX_CREDIT,10,' ')
4009 || 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,' ')
4010 || lpad(to_char(l_pay_to_date),12,' ')
4011 || lpad(to_char(l_tax_to_date),12,' ')||lpad('20',6,' ')
4012 || 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,' ')
4013 || lpad(' ',10,' ')
4014 -- for Current PAYE Details
4015 || lpad(r_pay.wk_mth_indicator,3,' ')||lpad(r_pay.exemption_indicator,3,' ')
4016 || lpad(r_pay.mth_rate_cutoff,9,' ')||lpad(r_pay.mth_tax_credit,9,' ')
4017 || lpad(r_pay.wk_rate_cutoff,10,' ')||lpad(r_pay.wk_tax_credit,10,' ')
4018 || lpad(to_char(r_pay.cert_date,'dd-mm-yyyy'),12,' ')||lpad(r_pay.tot_pay_to_date,12,' ')
4019 || lpad(r_pay.tot_tax_to_date,12,' ')||lpad(r_pay.std_rate_of_tax,6,' ')
4020 || lpad(r_pay.higher_rate_of_tax,6,' '));
4021
4022
4023 END IF;
4024 r_paye_details := r_empty_details;
4025 END IF;
4026 END IF;
4027 END IF;
4028 -- end bug 5724436.
4029 EXCEPTION
4030 -- Bug Fix 3500192
4031 -- WHEN name_not_equal THEN
4032 -- l_error := SQLERRM;
4033 -- retcode := 1;
4034 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
4035 -- FND_FILE.PUT_LINE(fnd_file.log, 'The first name and last name in the interface body table does not match
4036 -- the first and last name in the payroll tables');
4037 --
4038 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
4039 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4040 -- r_pay.employee_no_hr ||', '||
4041 -- r_pay.last_name_hr ||' '||
4042 -- r_pay.first_name_hr ||', '||
4043 -- r_pay.last_name_int ||' '||
4044 -- r_pay.first_name_int||', '||
4045 -- r_pay.payroll_name_hr
4046 -- );
4047 --
4048 -- -- The exception details are written to an error table
4049 --
4050 -- INSERT INTO pay_ie_tax_error ( pps_number
4051 -- , employee_number
4052 -- , full_name
4053 -- , payroll_name
4054 -- , tax_district
4055 -- , error_stack_message
4056 -- , error_message
4057 -- , request_id
4058 -- , error_date )
4059 -- VALUES (r_pay.pps_number_hr
4060 -- , r_pay.employee_no_hr
4061 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4062 -- , r_pay.payroll_name_hr
4063 -- , r_pay.tax_district
4064 -- , l_error_stack
4065 -- , l_error
4066 -- , l_request_id
4067 -- , sysdate);
4068 -- COMMIT;
4069 --
4070 -- WHEN same_day THEN
4071 -- l_error := SQLERRM;
4072 -- retcode := 1;
4073 -- FND_FILE.PUT_LINE(fnd_file.log, 'This record has already been updated today with changes to the
4074 -- PAY_IE_PAYE_DETAILS_F table');
4075 --
4076 --
4077 -- FND_FILE.NEW_LINE(fnd_file.log, 1);
4078 -- FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4079 -- r_pay.employee_no_hr ||', '||
4080 -- r_pay.last_name_hr ||' '||
4081 -- r_pay.first_name_hr ||', '||
4082 -- r_pay.ppd_effective_start_date ||', '||
4083 -- r_pay.payroll_name_hr
4084 -- );
4085 --
4086 -- -- The exception details are written to an error table
4087 --
4088 -- INSERT INTO pay_ie_tax_error ( pps_number
4089 -- , employee_number
4090 -- , full_name
4091 -- , payroll_name
4092 -- , tax_district
4093 -- , error_stack_message
4094 -- , error_message
4095 -- , request_id
4096 -- , error_date )
4097 -- VALUES (r_pay.pps_number_hr
4098 -- , r_pay.employee_no_hr
4099 -- , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4100 -- , r_pay.payroll_name_hr
4101 -- , r_pay.tax_district
4102 -- , l_error_stack
4103 -- , l_error
4104 -- , l_request_id
4105 -- , sysdate);
4106 -- COMMIT;
4107
4108 /*WHEN future_day THEN
4109 l_error := SQLERRM;
4110 retcode := 1;
4111 FND_FILE.PUT_LINE(fnd_file.log, 'This record has been updated to a future date');
4112
4113 FND_FILE.NEW_LINE(fnd_file.log, 1);
4114 FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4115 r_pay.employee_no_hr ||', '||
4116 r_pay.last_name_hr ||' '||
4117 r_pay.first_name_hr ||', '||
4118 r_pay.payroll_name_hr
4119 );
4120 -- The exception details are written to an error table
4121
4122 INSERT INTO pay_ie_tax_error ( pps_number
4123 , employee_number
4124 , full_name
4125 , payroll_name
4126 , tax_district
4127 , error_stack_message
4128 , error_message
4129 , request_id
4130 , error_date )
4131 VALUES (r_pay.pps_number_hr
4132 , r_pay.employee_no_hr
4133 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4134 , r_pay.payroll_name_hr
4135 , r_pay.tax_district
4136 , l_error_stack
4137 , l_error
4138 , l_request_id
4139 , sysdate);
4140 COMMIT;*/
4141
4142 WHEN std_rate_of_tax_is_null THEN
4143 l_error := 'Standard Rate of Tax cannot be Null';--SQLERRM;
4144 retcode := 1;
4145 FND_FILE.PUT_LINE(fnd_file.log, 'Standard Rate of Tax cannot be Null');
4146
4147 FND_FILE.NEW_LINE(fnd_file.log, 1);
4148 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4149 r_pay.employee_no_hr ||', '||
4150 r_pay.last_name_hr ||' '||
4151 r_pay.first_name_hr ||', '||
4152 r_pay.payroll_name_hr
4153 );*/
4154 --IF p_validate_mode in ('IE_VALIDATE') THEN
4155 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4156 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4157 l_err_tab(err_cnt).p_err_msg := 'Failed : Standard Rate of Tax cannot be Null';
4158 err_cnt := err_cnt + 1;
4159 --ELSE
4160 -- 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');
4161 --END IF;
4162 -- The exception details are written to an error table
4163
4164 INSERT INTO pay_ie_tax_error ( pps_number
4165 , employee_number
4166 , full_name
4167 , payroll_name
4168 , tax_district
4169 , error_stack_message
4170 , error_message
4171 , request_id
4172 , error_date )
4173 VALUES (r_pay.pps_number_hr
4174 , substr(r_pay.employee_no_int,1,12)
4175 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4176 , r_pay.payroll_name_hr
4177 , r_pay.tax_district
4178 , l_error_stack
4179 , l_error
4180 , l_request_id
4181 , sysdate);
4182 COMMIT;
4183
4184 WHEN higher_rate_of_tax_is_null THEN
4185 l_error := 'Higher Rate of Tax cannot be Null';--SQLERRM;
4186 retcode := 1;
4187 FND_FILE.PUT_LINE(fnd_file.log, 'Higher Rate of Tax cannot be Null');
4188
4189 FND_FILE.NEW_LINE(fnd_file.log, 1);
4190 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4191 r_pay.employee_no_hr ||', '||
4192 r_pay.last_name_hr ||' '||
4193 r_pay.first_name_hr ||', '||
4194 r_pay.payroll_name_hr
4195 );*/
4196
4197 --IF p_validate_mode = 'IE_VALIDATE' THEN
4198 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4199 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4200 l_err_tab(err_cnt).p_err_msg := 'Failed : Higher Rate of Tax cannot be Null';
4201 err_cnt := err_cnt + 1;
4202 --ELSE
4203 -- 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');
4204 --END IF;
4205
4206 -- The exception details are written to an error table
4207 INSERT INTO pay_ie_tax_error ( pps_number
4208 , employee_number
4209 , full_name
4210 , payroll_name
4211 , tax_district
4212 , error_stack_message
4213 , error_message
4214 , request_id
4215 , error_date )
4216 VALUES (r_pay.pps_number_hr
4217 , substr(r_pay.employee_no_int,1,12)
4218 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4219 , r_pay.payroll_name_hr
4220 , r_pay.tax_district
4221 , l_error_stack
4222 , l_error
4223 , l_request_id
4224 , sysdate);
4225 COMMIT;
4226
4227 WHEN exemption_is_null THEN
4228
4229 l_error := 'Exemption Indicator cannot be Null';--SQLERRM;
4230 retcode := 1;
4231 FND_FILE.PUT_LINE(fnd_file.log, 'Exemption Indicator cannot be Null');
4232
4233 FND_FILE.NEW_LINE(fnd_file.log, 1);
4234 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4235 r_pay.employee_no_hr ||', '||
4236 r_pay.last_name_hr ||' '||
4237 r_pay.first_name_hr ||', '||
4238 r_pay.payroll_name_hr
4239 );*/
4240
4241 --IF p_validate_mode = 'IE_VALIDATE' THEN
4242 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4243 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4244 l_err_tab(err_cnt).p_err_msg := 'Failed : Exemption Indicator cannot be Null';
4245 err_cnt := err_cnt + 1;
4246 --ELSE
4247 -- 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');
4248 --END IF;
4249
4250 -- The exception details are written to an error table
4251 INSERT INTO pay_ie_tax_error ( pps_number
4252 , employee_number
4253 , full_name
4254 , payroll_name
4255 , tax_district
4256 , error_stack_message
4257 , error_message
4258 , request_id
4259 , error_date )
4260 VALUES (r_pay.pps_number_hr
4261 , substr(r_pay.employee_no_int,1,12)
4262 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4263 , r_pay.payroll_name_hr
4264 , r_pay.tax_district
4265 , l_error_stack
4266 , l_error
4267 , l_request_id
4268 , sysdate);
4269 COMMIT;
4270
4271 WHEN exemption_mismatch THEN
4272 l_error := 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';--SQLERRM;
4273 retcode := 1;
4274 FND_FILE.PUT_LINE(fnd_file.log, 'The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%');
4275
4276 FND_FILE.NEW_LINE(fnd_file.log, 1);
4277 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4278 r_pay.employee_no_hr ||', '||
4279 r_pay.last_name_hr ||' '||
4280 r_pay.first_name_hr ||', '||
4281 r_pay.payroll_name_hr
4282 );*/
4283
4284 --IF p_validate_mode = 'IE_VALIDATE' THEN
4285 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4286 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4287 l_err_tab(err_cnt).p_err_msg := 'Failed : The higher rate of tax for Exemption should be '||l_tax_rate_exempt||'%';
4288 err_cnt := err_cnt + 1;
4289 --ELSE
4290 -- 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||'%');
4291 --END IF;
4292
4293 -- The exception details are written to an error table
4294 INSERT INTO pay_ie_tax_error ( pps_number
4295 , employee_number
4296 , full_name
4297 , payroll_name
4298 , tax_district
4299 , error_stack_message
4300 , error_message
4301 , request_id
4302 , error_date )
4303 VALUES (r_pay.pps_number_hr
4304 , substr(r_pay.employee_no_int,1,12)
4305 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4306 , r_pay.payroll_name_hr
4307 , r_pay.tax_district
4308 , l_error_stack
4309 , l_error
4310 , l_request_id
4311 , sysdate);
4312 COMMIT;
4313
4314 WHEN normal_tax_mismatch THEN
4315 l_error := 'The higher rate of tax for Cumulative or Week1/Month1 Tax Basis should be '||l_tax_rate_high||'%';--SQLERRM;
4316 retcode := 1;
4317 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||'%');
4318
4319 FND_FILE.NEW_LINE(fnd_file.log, 1);
4320 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4321 r_pay.employee_no_hr ||', '||
4322 r_pay.last_name_hr ||' '||
4323 r_pay.first_name_hr ||', '||
4324 r_pay.payroll_name_hr
4325 );*/
4326 -- The exception details are written to an error table
4327 --IF p_validate_mode = 'IE_VALIDATE' THEN
4328 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4329 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4330 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||'%';
4331 err_cnt := err_cnt + 1;
4332 --ELSE
4333 -- 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||'%');
4334 --END IF;
4335
4336 -- The exception details are written to an error table
4337 INSERT INTO pay_ie_tax_error ( pps_number
4338 , employee_number
4339 , full_name
4340 , payroll_name
4341 , tax_district
4342 , error_stack_message
4343 , error_message
4344 , request_id
4345 , error_date )
4346 VALUES (r_pay.pps_number_hr
4347 , substr(r_pay.employee_no_int,1,12)
4348 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4349 , r_pay.payroll_name_hr
4350 , r_pay.tax_district
4351 , l_error_stack
4352 , l_error
4353 , l_request_id
4354 , sysdate);
4355 COMMIT;
4356
4357 WHEN pay_to_date THEN
4358 l_error := 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.';--SQLERRM;
4359 retcode := 1;
4360 FND_FILE.PUT_LINE(fnd_file.log, 'Total Pay to Date and Total Tax to Date can be null only for Week1/Month1 basis.');
4361
4362 FND_FILE.NEW_LINE(fnd_file.log, 1);
4363 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4364 r_pay.employee_no_hr ||', '||
4365 r_pay.last_name_hr ||' '||
4366 r_pay.first_name_hr ||', '||
4367 r_pay.payroll_name_hr
4368 );*/
4369
4370 --IF p_validate_mode = 'IE_VALIDATE' THEN
4371 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4372 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4373 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';
4374 err_cnt := err_cnt + 1;
4375 --ELSE
4376 -- 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');
4377 --END IF;
4378
4379 -- The exception details are written to an error table
4380 INSERT INTO pay_ie_tax_error ( pps_number
4381 , employee_number
4382 , full_name
4383 , payroll_name
4384 , tax_district
4385 , error_stack_message
4386 , error_message
4387 , request_id
4388 , error_date )
4389 VALUES (r_pay.pps_number_hr
4390 , substr(r_pay.employee_no_int,1,12)
4391 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4392 , r_pay.payroll_name_hr
4393 , r_pay.tax_district
4394 , l_error_stack
4395 , l_error
4396 , l_request_id
4397 , sysdate);
4398 COMMIT;
4399
4400 WHEN OTHERS THEN
4401 errbuf := fnd_message.get;
4402 l_error_stack := errbuf;
4403 l_error := SQLERRM;
4404
4405 /*Update interface table and set processed flag to 'No' to record that record has not been updated
4406 or inserted into payroll tables*/
4407
4408 IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
4409 UPDATE pay_ie_tax_body_interface
4410 SET processed_flag = 'N'
4411 WHERE pps_number = r_pay.pps_number_int;
4412 END IF;
4413 -- The following command will be used to output the exception details to an output file:
4414
4415 /*FND_FILE.PUT_LINE(fnd_file.output, r_pay.pps_number_hr ||','||
4416 r_pay.pps_number_int ||', '||
4417 r_pay.employee_no_hr ||', '||
4418 r_pay.pps_number_int ||', '||
4419 r_pay.last_name_hr ||' '||
4420 r_pay.first_name_hr ||', '||
4421 r_pay.pps_number_int ||', '||
4422 r_pay.payroll_name_hr ||', '||
4423 r_pay.pps_number_int
4424 );*/
4425 --IF p_validate_mode = 'IE_VALIDATE' THEN
4426 l_err_tab(err_cnt).p_pps_number := r_pay.pps_number_int;
4427 l_err_tab(err_cnt).p_works_number := substr(r_pay.employee_no_int,1,12);
4428 l_err_tab(err_cnt).p_err_msg := 'Failed : '||l_error;
4429 err_cnt := err_cnt + 1;
4430 --ELSE
4431 -- 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);
4432 --END IF;
4433 -- The exception details are written to an error table
4434
4435 INSERT INTO pay_ie_tax_error ( pps_number
4436 , employee_number
4437 , full_name
4438 , payroll_name
4439 , tax_district
4440 , error_stack_message
4441 , error_message
4442 , request_id
4443 , error_date )
4444 VALUES (r_pay.pps_number_hr
4445 , substr(r_pay.employee_no_int,1,12)
4446 , r_pay.last_name_hr ||' '|| r_pay.first_name_hr
4447 , r_pay.payroll_name_hr
4448 , r_pay.tax_district
4449 , l_error_stack
4450 , l_error
4451 , l_request_id
4452 , sysdate);
4453 COMMIT;
4454
4455 retcode := 1; -- 6215901
4456
4457 END;
4458 --end if;
4459 END LOOP;
4460
4461
4462 IF l_err_tab.COUNT <> 0 then
4463 for i in l_err_tab.first..l_err_tab.last
4464 loop
4465 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);
4466 end loop;
4467 END IF;
4468 --bug 6376140
4469 /* for i in csr_skipped_asg
4470 loop
4471 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');
4472 end loop;
4473 */
4474 FOR i in csr_skipped_assignments
4475 LOOP
4476 flag :='N';
4477 IF(l_pps_number.count<>0) then
4478 FOR j in l_pps_number.first..l_pps_number.last
4479 LOOP
4480 IF l_pps_number(j)=i.pps_number THEN
4481 flag :='Y';
4482 END IF;
4483 EXIT WHEN flag='Y';
4484 END LOOP;
4485 END IF;
4486 IF flag='N' THEN
4487 -- open check_pps(i.pps_number);
4488 -- FETCH check_pps INTO p_check_pps
4489 -- if check_pps%found then
4490 fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||lpad(substr(nvl(i.works_number,' '),1,12),13,' ')
4491 || 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');
4492 -- else
4493 -- else
4494 --fnd_file.put_line(fnd_file.output,lpad(i.pps_number,11,' ')||'Please check the employee''s PPS number');
4495 -- end if;
4496 -- close check_pps;
4497 END IF;
4498 END LOOP;
4499 --end of bug 6376140
4500
4501 -- Bug Fix 3500192
4502 -- Writes the trailer record in the log file
4503 IF l_header_count = 1 THEN
4504 log_ie_paye_footer(l_record_count);
4505 END IF;
4506
4507 /* If user selects the mode to be 'Validate and Commit' then
4508 p_validate_mode = 'IE_VALIDATE_COMMIT' then records are committed else records are rolled back */
4509
4510 IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
4511 COMMIT;
4512 ELSE
4513 ROLLBACK;
4514 END IF;
4515
4516
4517 EXCEPTION
4518 WHEN NO_DATA_FOUND THEN
4519 l_error := SQLERRM;
4520 retcode := 1;
4521 FND_FILE.PUT_LINE(fnd_file.log, 'No data found');
4522
4523 INSERT INTO pay_ie_tax_error ( pps_number
4524 , employee_number
4525 , full_name
4526 , payroll_name
4527 , tax_district
4528 , error_stack_message
4529 , error_message
4530 , request_id
4531 , error_date)
4532 VALUES (l_pps_number_hr
4533 , substr(l_employee_number_hr,1,12)
4534 , l_last_name_hr ||' '|| l_first_name_hr
4535 , l_payroll_name_hr
4536 , l_tax_district
4537 , l_error_stack
4538 , l_error
4539 , l_request_id
4540 , sysdate
4541 );
4542 COMMIT;
4543
4544 WHEN OTHERS THEN
4545
4546 errbuf := fnd_message.get;
4547 l_error_stack := errbuf;
4548 l_error := SQLERRM;
4549 retcode := 2;
4550 /* The following command will be used to output the exception details to an output file*/
4551
4552 /*FND_FILE.PUT_LINE(fnd_file.output, l_pps_number_hr ||', '||
4553 l_pps_number_int ||', '||
4554 l_employee_number_hr ||', '||
4555 l_employee_number_int ||', '||
4556 l_last_name_hr ||' '||l_first_name_hr ||', '||
4557 l_last_name_int ||' '|| l_first_name_int ||', '||
4558 l_payroll_name_hr ||', '||
4559 l_tax_district
4560 );*/
4561 --IF p_validate_mode = 'IE_VALIDATE' THEN
4562 l_err_tab(err_cnt).p_pps_number := l_pps_number_int;
4563 l_err_tab(err_cnt).p_works_number := substr(l_employee_number_int,1,12);
4564 l_err_tab(err_cnt).p_err_msg := 'Failed : OTHER in Main..'||l_error;
4565 err_cnt := err_cnt + 1;
4566 --ELSE
4567 -- 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);
4568 --END IF;
4569 /* The exception details are written to an error table */
4570 INSERT INTO pay_ie_tax_error ( pps_number
4571 , employee_number
4572 , full_name
4573 , payroll_name
4574 , tax_district
4575 , error_stack_message
4576 , error_message
4577 , request_id
4578 , error_date)
4579 VALUES (l_pps_number_hr
4580 , substr(l_employee_number_hr,1,12)
4581 , l_last_name_hr ||' '||l_first_name_hr
4582 , l_payroll_name_hr
4583 , l_tax_district
4584 , l_error_stack
4585 , l_error
4586 , l_request_id
4587 , sysdate);
4588 COMMIT;
4589 END valinsupd;
4590
4591 -- Bug Fix 3500192
4592 -- This procedures writes the Paye Details of the employee in the log file
4593 PROCEDURE log_ie_paye_header
4594 AS
4595 l_line_1 varchar2(1000) := ' ';
4596 l_line_2 varchar2(1000) := ' ';
4597 l_line_3 varchar2(1000) := ' ';
4598 BEGIN
4599 l_line_1 := rpad('Assignment',10)
4600 || ' '
4601 || rpad('Employee',15)
4602 || ' '
4603 || rpad('PPS',62)
4604 || ' '
4605 || rpad(lpad('Tax Credit',18-length('Tax Credit')/2),17)
4606 || ' '
4607 || rpad('Std Rate Cut Off',17);
4608
4609 l_line_2 := rpad('Number',10)
4610 || ' '
4611 || rpad('Number',15)
4612 || ' '
4613 || rpad('Number',10)
4614 || ' '
4615 || rpad('Information Source',30)
4616 || ' '
4617 || rpad('Tax Basis',20)
4618 || ' '
4619 || rpad('Weekly',8)
4620 || ' '
4621 || rpad('Monthly',8)
4622 || ' '
4623 || rpad('Weekly',8)
4624 || ' '
4625 || rpad('Monthly',8);
4626
4627 l_line_3 := rpad('-',10,'-')
4628 || ' '
4629 || rpad('-',15,'-')
4630 || ' '
4631 || rpad('-',10,'-')
4632 || ' '
4633 || rpad('-',30,'-')
4634 || ' '
4635 || rpad('-',20,'-')
4636 || ' '
4637 || rpad('-',8,'-')
4638 || ' '
4639 || rpad('-',8,'-')
4640 || ' '
4641 || rpad('-',8,'-')
4642 || ' '
4643 || rpad('-',8,'-');
4644
4645 FND_FILE.NEW_LINE(fnd_file.log, 1);
4646 FND_FILE.PUT_LINE(fnd_file.log,l_line_1);
4647 FND_FILE.PUT_LINE(fnd_file.log,l_line_2);
4648 FND_FILE.PUT_LINE(fnd_file.log,l_line_3);
4649
4650 END log_ie_paye_header;
4651
4652 PROCEDURE log_ie_paye_body(
4653 p_paye_details_id IN NUMBER
4654 , p_pps_number IN VARCHAR2
4655 , p_employee_number IN VARCHAR2
4656 )
4657 AS
4658 CURSOR c_paye_details(p_paye_details_id NUMBER)
4659 IS
4660 SELECT assignment_id
4661 , tax_basis
4662 , info_source
4663 , weekly_tax_credit
4664 , weekly_std_rate_cut_off
4665 , monthly_tax_credit
4666 , monthly_std_rate_cut_off
4667 FROM pay_ie_paye_details_f
4668 WHERE paye_details_id = p_paye_details_id;
4669
4670 CURSOR c_lookup_meaning(p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
4671 IS
4672 SELECT meaning
4673 FROM hr_lookups
4674 where lookup_type = p_lookup_type
4675 and lookup_code = p_lookup_code;
4676
4677 r_paye_details c_paye_details%ROWTYPE;
4678 l_line varchar2(1000);
4679 l_info_source varchar2(30);
4680 l_tax_basis varchar2(20);
4681
4682 BEGIN
4683 OPEN c_paye_details(p_paye_details_id);
4684 FETCH c_paye_details INTO r_paye_details;
4685 CLOSE c_paye_details;
4686 --
4687 OPEN c_lookup_meaning('IE_PAYE_INFO_SOURCE',r_paye_details.info_source);
4688 FETCH c_lookup_meaning INTO l_info_source;
4689 CLOSE c_lookup_meaning;
4690 --
4691 OPEN c_lookup_meaning('IE_PAYE_TAX_BASIS',r_paye_details.tax_basis);
4692 FETCH c_lookup_meaning INTO l_tax_basis;
4693 CLOSE c_lookup_meaning;
4694 --
4695 l_line := rpad(nvl(r_paye_details.assignment_id,0),10,' ')
4696 || ' '
4697 || rpad(nvl(p_employee_number,0),15,' ')
4698 || ' '
4699 || rpad(nvl(p_pps_number,' '),10,' ')
4700 || ' '
4701 || rpad(nvl(l_info_source,' '),30,' ')
4702 || ' '
4703 || rpad(nvl(l_tax_basis,' '),20,' ')
4704 || ' '
4705 || rpad(nvl(to_char(r_paye_details.weekly_tax_credit),'-'),8,' ')
4706 || ' '
4707 || rpad(nvl(to_char(r_paye_details.monthly_tax_credit),'-'),8,' ')
4708 || ' '
4709 || rpad(nvl(to_char(r_paye_details.weekly_std_rate_cut_off),'-'),8,' ')
4710 || ' '
4711 || rpad(nvl(to_char(r_paye_details.monthly_std_rate_cut_off),'-'),8,' ');
4712
4713 FND_FILE.PUT_LINE(fnd_file.log,l_line);
4714
4715 END log_ie_paye_body;
4716
4717 PROCEDURE log_ie_paye_footer(p_total IN NUMBER)
4718 AS
4719 l_line varchar2(100);
4720 BEGIN
4721 l_line := 'Number of Records: '
4722 || p_total;
4723 FND_FILE.NEW_LINE(fnd_file.log, 1);
4724 FND_FILE.PUT_LINE(fnd_file.log,l_line);
4725 FND_FILE.NEW_LINE(fnd_file.log, 1);
4726 END log_ie_paye_footer;
4727
4728 END PAY_IE_TAX_VAL;