1 PACKAGE BODY PAY_GB_EPS_13 as
2 /* $Header: pygbeps13.pkb 120.5.12020000.2 2013/03/09 06:05:22 ssanjays noship $ *
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_EPS_13
10 Purpose:
11 It fetches the live data and generates RTI EPS Records.
12 This is a UK Specific payroll package.
13
14 History:
15 12-Dec-2012 sampmand 115.0 15890006 Created
16 18-Dec-2012 sampmand 115.1 15890006 Fixed GSCC errors
17 =============================================================================*/
18
19 g_package varchar2(15):='pay_gb_eps_13.';
20
21 PROCEDURE Check_constraints
22 (
23 p_tax_free_pymt IN VARCHAR2,
24 p_emp_third_party IN VARCHAR2,
25 p_exp_ben_othrs IN VARCHAR2,
26 p_p11d_due IN VARCHAR2,
27 p_emp_outside_uk IN VARCHAR2,
28 p_payment_due IN VARCHAR2,
29 p_final_submission_ceased IN VARCHAR2,
30 p_final_submission_year IN VARCHAR2,
31 p_service_company IN VARCHAR2,
32 P_STATUS IN VARCHAR2,
33 L_ERROR_TXT out NOCOPY Varchar2
34 )
35 IS
36
37 BEGIN
38 L_ERROR_TXT :=' ';
39
40 IF nvl(P_TAX_FREE_PYMT,'N') NOT IN ('Y','N') THEN
41 l_error_txt := l_error_txt || 'TAX_FREE_PYMT value is incorrect';
42 END if;
43
44 IF NVL(P_EMP_THIRD_PARTY,'N') NOT IN ('Y','N') THEN
45 l_error_txt := l_error_txt || 'EMP_THIRD_PARTY value is incorrect';
46 END IF;
47
48 IF NVL(P_EXP_BEN_OTHRS,'N') NOT IN ('Y','N') THEN
49 l_error_txt := l_error_txt || 'EXP_BEN_OTHERS value is incorrect';
50 END IF;
51
52 IF NVL(P_P11D_DUE,'N') NOT IN ('Y','N') THEN
53 l_error_txt := l_error_txt || 'P11D_DUE value is incorrect';
54 END IF;
55
56 IF NVL(P_EMP_OUTSIDE_UK,'N') NOT IN ('Y','N') THEN
57 l_error_txt := l_error_txt || 'EMP_OUTSIDE_UK value is incorrect';
58 END IF;
59
60 IF NVL(P_PAYMENT_DUE,'N') NOT IN ('Y','N') THEN
61 l_error_txt := l_error_txt || 'PAYMENT_DUE value is incorrect';
62 END IF;
63
64 IF nvl(P_FINAL_SUBMISSION_CEASED,'N') NOT IN ('Y','N') THEN
65 l_error_txt := l_error_txt || 'FINAL_SUBMISSION_CEASED value is incorrect';
66 END IF;
67
68 IF NVL(P_FINAL_SUBMISSION_YEAR,'N') NOT IN ('Y','N') THEN
69 l_error_txt := l_error_txt || 'FINAL_SUBMISSION_YEAR value is incorrect';
70 END IF;
71
72 IF NVL(P_SERVICE_COMPANY,'N') NOT IN ('Y','N') THEN
73 l_error_txt := l_error_txt || 'SERVICE_COMPANY value is incorrect';
74 END IF;
75
76 IF P_STATUS NOT IN ('Saved','Completed','Verified','Sent') THEN
77 l_error_txt := l_error_txt || 'Status value is incorrect';
78 End IF;
79
80 End;
81
82
83 PROCEDURE insert_eps_record
84 (
85 p_effective_date IN DATE,
86 p_emp_paye_reference IN VARCHAR2,
87 p_account_office_number IN VARCHAR2,
88 p_business_group_id IN NUMBER,
89 p_employer_name IN VARCHAR2,
90 p_econ_number IN VARCHAR2,
91 p_related_tax_year IN VARCHAR2,
92 p_final_submission_ceased IN VARCHAR2,
93 p_final_submission_year IN VARCHAR2,
94 p_smp_sap_spp_recovery IN NUMBER,
95 p_ssp_recovery IN NUMBER,
96 p_nic_recovery IN NUMBER,
97 p_service_company IN VARCHAR2,
98 p_smp_amt_recovered_calc IN NUMBER,
99 p_sap_amt_recovered_calc IN NUMBER,
100 p_ospp_amt_recovered_calc IN NUMBER,
101 p_aspp_amt_recovered_calc IN NUMBER,
102 p_ssp_amt_recovered_calc IN NUMBER,
103 p_nic_comp_smp_calc IN NUMBER,
104 p_nic_comp_sap_calc IN NUMBER,
105 p_nic_comp_ospp_calc IN NUMBER,
106 p_nic_comp_aspp_calc IN NUMBER,
107 p_nic_comp_ssp_calc IN NUMBER,
108 p_status IN VARCHAR2,
109 p_user_id IN NUMBER,
110 p_approver_id IN NUMBER,
111 p_request_id IN NUMBER,
112 p_program_application_id IN NUMBER,
113 p_program_id IN NUMBER,
114 p_update_date IN DATE
115 )
116 IS
117 l_proc varchar2(72) := g_package||'insert_eps_record';
118 l_profile varchar2(10);
119 l_approver_id number;
120
121 BEGIN
122 hr_utility.set_location(' Entering :' || l_proc,160);
123 hr_utility.set_location(' p_effective_date :' || p_effective_date,160);
124
125 l_approver_id := p_approver_id;
126 profile_option_value(l_profile);
127
128 if l_profile = 'N' then
129 l_approver_id := NULL;
130 end if;
131
132 insert into PAY_GB_EPS_DETAILS
133 ( EPS_RECORD_ID,
134 EMP_PAYE_REFERENCE,
135 ACCOUNT_OFFICE_NUMBER,
136 BUSINESS_GROUP_ID,
137 EMPLOYER_NAME,
138 ECON_NUMBER,
139 EFFECTIVE_DATE,
140 RELATED_TAX_YEAR,
141 FINAL_SUBMISSION_CEASED,
142 FINAL_SUBMISSION_YEAR,
143 SMP_SAP_SPP_RECOVERY,
144 SSP_RECOVERY,
145 NIC_RECOVERY,
146 SERVICE_COMPANY,
147 SMP_AMT_RECOVERED,
148 SAP_AMT_RECOVERED,
149 OSPP_AMT_RECOVERED,
150 ASPP_AMT_RECOVERED,
151 SSP_AMT_RECOVERED,
152 NIC_COMP_SMP,
153 NIC_COMP_SAP,
154 NIC_COMP_OSPP,
155 NIC_COMP_ASPP,
156 NIC_COMP_SSP,
157 SMP_AMT_RECOVERED_CALC,
158 SAP_AMT_RECOVERED_CALC,
159 OSPP_AMT_RECOVERED_CALC,
160 ASPP_AMT_RECOVERED_CALC,
161 SSP_AMT_RECOVERED_CALC,
162 NIC_COMP_SMP_CALC,
163 NIC_COMP_SAP_CALC,
164 NIC_COMP_OSPP_CALC,
165 NIC_COMP_ASPP_CALC,
166 NIC_COMP_SSP_CALC,
167 CREATED_DATE,
168 LAST_UPDATED_DATE,
169 STATUS,
170 USER_ID,
171 APPROVER_ID,
172 OBJECT_VERSION_NUMBER,
173 REQUEST_ID,
174 PROGRAM_APPLICATION_ID,
175 PROGRAM_ID,
176 PROGRAM_UPDATE_DATE
177 )
178 VALUES
179 (
180 pay_gb_eps_details_s.nextval,
181 p_emp_paye_reference,
182 p_account_office_number,
183 p_business_group_id,
184 p_employer_name,
185 p_econ_number,
186 p_effective_date,
187 p_related_tax_year,
188 p_final_submission_ceased,
189 p_final_submission_year,
190 p_smp_sap_spp_recovery,
191 p_ssp_recovery,
192 p_nic_recovery,
193 p_service_company,
194 NVL(p_smp_amt_recovered_calc,0),
195 NVL(p_sap_amt_recovered_calc,0),
196 NVL(p_ospp_amt_recovered_calc,0),
197 NVL(p_aspp_amt_recovered_calc,0),
198 NVL(p_ssp_amt_recovered_calc,0),
199 NVL(p_nic_comp_smp_calc,0),
200 NVL(p_nic_comp_sap_calc,0),
201 NVL(p_nic_comp_ospp_calc,0),
202 NVL(p_nic_comp_aspp_calc,0),
203 NVL(p_nic_comp_ssp_calc,0),
204 p_smp_amt_recovered_calc,
205 p_sap_amt_recovered_calc,
206 p_ospp_amt_recovered_calc,
207 p_aspp_amt_recovered_calc,
208 p_ssp_amt_recovered_calc,
209 p_nic_comp_smp_calc,
210 p_nic_comp_sap_calc,
211 p_nic_comp_ospp_calc,
212 p_nic_comp_aspp_calc,
213 p_nic_comp_ssp_calc,
214 to_date(SYSDATE,'DD-MM-RRRR'),
215 SYSDATE,
216 p_status,
217 p_user_id,
218 l_approver_id,
219 1,
220 p_request_id,
221 p_program_application_id,
222 p_program_id,
223 p_update_date
224 );
225
226
227 hr_utility.set_location('Inserted the EPS record',100);
228 HR_UTILITY.SET_LOCATION(' Leaving :' || L_PROC,100);
229
230 END insert_eps_record;
231
232 PROCEDURE delete_eps_record
233 ( p_effective_date IN VARCHAR2,
234 p_emp_paye_reference IN VARCHAR2,
235 p_business_group_id IN VARCHAR2,
236 p_return_status OUT NOCOPY VARCHAR2
237 )
238 --pay_gb_eps.delete_eps_record
239 IS
240
241 l_proc varchar2(72) := g_package||'delete_eps_record';
242
243
244 BEGIN
245
246 hr_utility.set_location(' Entering :' || l_proc,160);
247
248 DELETE
249 FROM pay_gb_eps_details
250 WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
251 AND EFFECTIVE_DATE = p_effective_date
252 and business_group_id = p_business_group_id;
253
254 COMMIT;
255
256 p_return_status := 'S';
257 hr_utility.set_location('Deleted the EPS record',100);
258 hr_utility.set_location(' Leaving :' || l_proc,100);
259
260
261 exception
262 when others then
263 raise_application_error(-20001,SQLERRM);
264
265 END delete_eps_record;
266
267
268 PROCEDURE check_sequence
269 ( p_effective_date IN VARCHAR2,
270 p_emp_paye_reference IN VARCHAR2,
271 p_business_group_id IN VARCHAR2,
272 p_return_status OUT NOCOPY VARCHAR2
273 )
274 IS
275
276 l_proc varchar2(72) := g_package||' check_sequence';
277
278 cursor csr_check_seq is
279 select emp_paye_reference
280 from pay_gb_eps_details
281 where emp_paye_reference = p_emp_paye_reference
282 and business_group_id = p_business_group_id
283 and effective_date > p_effective_date
284 and status <> 'Saved';
285
286 l_paye_ref varchar2(100);
287
288 BEGIN
289
290 hr_utility.set_location(' Entering :' || l_proc,160);
291
292 p_return_status := 'N';
293
294 open csr_check_seq;
295 fetch csr_check_seq into l_paye_ref;
296 if csr_check_seq%found then
297 p_return_status := 'Y';
298 end if;
299 close csr_check_seq;
300
301 hr_utility.set_location(' Leaving :' || l_proc,100);
302 exception
303 when others then
304 raise_application_error(-20001,SQLERRM);
305
306 END check_sequence;
307
308 PROCEDURE profile_option_value
309 ( p_return_status OUT NOCOPY VARCHAR2
310 )
311 IS
312
313 l_proc varchar2(72) := g_package||'profile_option_value';
314
315 /*cursor csr_get_profile is
316 select PROFILE_OPTION_VALUE from
317 fnd_profile_option_values fpov,fnd_profile_options fpo
318 where fpov.PROFILE_OPTION_ID = fpo.PROFILE_OPTION_ID
319 and fpo.PROFILE_OPTION_NAME = 'GB_EPS_APPROVAL';
320 */
321 BEGIN
322
323 hr_utility.set_location(' Entering :' || l_proc,160);
324
325 /* p_return_status := 'Y';
326 open csr_get_profile;
327 fetch csr_get_profile into p_return_status;
328 close csr_get_profile;*/
329
330 fnd_profile.get('GB_EPS_APPROVAL',p_return_status);
331
332 if p_return_status is null then
333 p_return_status := 'Y';
334 end if;
335
336 hr_utility.trace('Profile Value : '||p_return_status);
337 hr_utility.set_location(' Leaving :' || l_proc,100);
338
339 exception
340 when others then
341 raise_application_error(-20001,SQLERRM);
342
343 END profile_option_value;
344
345 PROCEDURE update_status_eps_record
346 ( p_effective_date IN VARCHAR2,
347 p_emp_paye_reference IN VARCHAR2,
348 p_business_group_id IN VARCHAR2,
349 p_status IN VARCHAR2,
350 p_object_version_number IN NUMBER,
351 p_workflow IN VARCHAR2,
352 p_return_status OUT NOCOPY VARCHAR2
353 )
354 IS
355
356
357 cursor c_get_approver is
358 select papf.email_address email_address
359 from per_all_people_f papf ,pay_gb_eps_details pged
360 where pged.effective_date = p_effective_date
361 and pged.emp_paye_reference = p_emp_paye_reference
362 and papf.person_id = pged.approver_id
363 order by papf.effective_start_date desc;
364
365 cursor c_get_user is
366 select papf.email_address email_address
367 from per_all_people_f papf ,pay_gb_eps_details pged
368 where pged.effective_date = p_effective_date
369 and pged.emp_paye_reference = p_emp_paye_reference
370 and papf.person_id = pged.user_id
371 order by papf.effective_start_date desc;
372
373 cursor c_get_user_name(personid number) is
374 select user_name from fnd_user
375 where employee_id = personid;
376
377 cursor csr_get_ovn is
378 select object_version_number
379 from pay_gb_eps_details
380 where emp_paye_reference = p_emp_paye_reference
381 and effective_date = p_effective_date
382 and business_group_id = p_business_group_id;
383
384 cursor csr_get_ids is
385 select user_id , approver_id
386 from pay_gb_eps_details
387 where emp_paye_reference = p_emp_paye_reference
388 and effective_date = p_effective_date
389 and business_group_id = p_business_group_id;
390
391 l_object_version_number NUMBER(9);
392 l_exp Exception;
393
394 p_get_approver c_get_approver%rowtype;
395 p_get_user c_get_user%rowtype;
396
397 email_address varchar2(100);
398 l_proc varchar2(72) := g_package||'update_status_eps_record';
399 l_eps_id VARCHAR2(100);
400 l_person_id number;
401 l_user_name varchar2(100);
402 l_user_id number;
403 l_approver_id number;
404 l_profile varchar(15);
405 l_status varchar2(20);
406
407 BEGIN
408
409
410 hr_utility.set_location(' Entering :' || l_proc,160);
411
412 hr_utility.set_location('PAYE REf : '||p_emp_paye_reference,90);
413 hr_utility.set_location('Eff Date : '||p_effective_date,90);
414
415 l_status := p_status;
416
417 open csr_get_ovn;
418 fetch csr_get_ovn into l_object_version_number;
419 close csr_get_ovn;
420
421 hr_utility.set_location(' p_object_version_number :' || p_object_version_number,160);
422 hr_utility.set_location(' l_object_version_number :' || l_object_version_number,160);
423
424 if (p_object_version_number <> l_object_version_number) then
425 --p_return_status := 'E';
426 raise l_exp;
427 end if;
428
429 profile_option_value(l_profile);
430
431 hr_utility.trace('Profile Value : '||l_profile);
432
433 --if l_profile = 'N' then
434 if l_profile = 'N' AND l_status = 'Completed' then
435 l_status := 'Verified';
436 end if;
437 hr_utility.set_location('Status : '||l_status,40);
438 UPDATE
439 PAY_GB_EPS_DETAILS
440 SET STATUS = l_status,
441 REQUEST_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, request_id, FND_GLOBAL.CONC_REQUEST_ID),
442 PROGRAM_APPLICATION_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_application_id, FND_GLOBAL.PROG_APPL_ID),
443 PROGRAM_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_id, FND_GLOBAL.CONC_PROGRAM_ID),
444 PROGRAM_UPDATE_DATE = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_update_date, SYSDATE)
445 WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
446 AND EFFECTIVE_DATE = p_effective_date
447 AND BUSINESS_GROUP_ID = p_business_group_id;
448
449 COMMIT;
450
451 open csr_get_ids;
452 fetch csr_get_ids into l_user_id,l_approver_id;
453 close csr_get_ids;
454
455 if(p_workflow = 'Y' and (p_status = 'Completed' or p_status = 'Verified') and l_profile <> 'N') then
456 -- If 'Completed' get Approver email address
457 -- If 'Verified' get User Email address
458 if p_status = 'Completed' then
459 l_eps_id := p_emp_paye_reference||'-'||p_effective_date;
460 open c_get_approver;
461 fetch c_get_approver into p_get_approver;
462 email_address := p_get_approver.email_address;
463 close c_get_approver;
464 open c_get_user_name(l_approver_id);
465 fetch c_get_user_name into l_user_name;
466 close c_get_user_name;
467
468 elsif p_status = 'Verified' then
469 l_eps_id := p_emp_paye_reference||'-'||p_effective_date;
470 open c_get_user;
471 fetch c_get_user into p_get_user;
472 email_address := p_get_user.email_address;
473 close c_get_user;
474
475 open c_get_user_name(l_user_id);
476 fetch c_get_user_name into l_user_name;
477 close c_get_user_name;
478 end if;
479
480 hr_utility.set_location('eps_id : '||l_eps_id,40);
481 hr_utility.set_location('Status : '||p_status,40);
482 hr_utility.set_location('email_address : '||email_address,40);
483
484 -- Calling Workflow to send Notification
485 pay_gb_eps.start_eps_wf(eps_id => l_eps_id,status => p_status,email_address => email_address,user_name => l_user_name );
486 end if;
487
488 p_return_status := 'S';
489 hr_utility.set_location('Updated the status of the EPS record',100);
490 hr_utility.set_location(' Leaving :' || l_proc,100);
491
492 exception
493 when l_exp then
494 p_return_status := 'E';
495 when others then
496 raise_application_error(-20001,SQLERRM);
497
498 END update_status_eps_record;
499
500
501 PROCEDURE update_eps_record
502 (
503 p_effective_date IN VARCHAR2,
504 p_emp_paye_reference IN VARCHAR2,
505 p_tax_free_pymt IN VARCHAR2,
506 p_emp_third_party IN VARCHAR2,
507 p_exp_ben_othrs IN VARCHAR2,
508 p_p11d_due IN VARCHAR2,
509 p_emp_outside_uk IN VARCHAR2,
510 p_payment_due IN VARCHAR2,
511 p_final_submission_ceased IN VARCHAR2,
512 p_final_submission_year IN VARCHAR2,
513 p_service_company IN VARCHAR2,
514 p_smp_amt_recovered_calc IN NUMBER,
515 p_sap_amt_recovered_calc IN NUMBER,
516 p_ospp_amt_recovered_calc IN NUMBER,
517 p_aspp_amt_recovered_calc IN NUMBER,
518 p_ssp_amt_recovered_calc IN NUMBER,
519 p_nic_comp_smp_calc IN NUMBER,
520 p_nic_comp_sap_calc IN NUMBER,
521 p_nic_comp_ospp_calc IN NUMBER,
522 p_nic_comp_aspp_calc IN NUMBER,
523 p_nic_comp_ssp_calc IN NUMBER,
524 p_cis_deductions_suff IN NUMBER,
525 p_nic_holiday_val IN NUMBER,
526 p_adv_from_hmrc IN NUMBER,
527 p_status IN VARCHAR2,
528 p_approver_id IN NUMBER,
529 p_object_version_number IN NUMBER,
530 p_business_group_id IN VARCHAR2,
531 p_no_payment_date_from IN VARCHAR2,
532 p_no_payment_date_to IN VARCHAR2,
533 p_period_of_inactivity_from IN VARCHAR2,
534 p_period_of_inactivity_to IN VARCHAR2,
535 p_date_scheme_ceased IN VARCHAR2,
536 p_return_status OUT NOCOPY VARCHAR2
537 )
538 IS
539
540
541
542 l_proc varchar2(72) := g_package||'update_eps_record';
543
544 cursor csr_get_ovn is
545 select object_version_number,related_tax_year
546 from pay_gb_eps_details
547 where emp_paye_reference = p_emp_paye_reference
548 and effective_date = p_effective_date
549 and business_group_id = p_business_group_id;
550
551
552
553 l_object_version_number NUMBER(9);
554 l_related_tax_year number;
555 l_exp Exception;
556 l_approver_id number;
557
558
559 BEGIN
560
561
562 hr_utility.set_location(' Entering :' || l_proc,160);
563 hr_utility.set_location(' p_effective_date :' || p_effective_date,160);
564 hr_utility.set_location(' before update smp recovered amount in pkb:' || p_smp_amt_recovered_calc,160);
565 open csr_get_ovn;
566 fetch csr_get_ovn into l_object_version_number,l_related_tax_year;
567 close csr_get_ovn;
568
569 hr_utility.set_location(' p_object_version_number :' || p_object_version_number,160);
570 hr_utility.set_location(' l_object_version_number :' || l_object_version_number,160);
571
572 --select effective_date into system_date from fnd_sessions where session_id = userenv('SESSIONID');
573 if (p_object_version_number <> l_object_version_number) then
574 --p_return_status := 'E';
575 p_return_status := 'E';
576 raise l_exp;
577 end if;
578
579 if(p_payment_due = 'Y') then
580 if((p_no_payment_date_to = '' or p_no_payment_date_to is null) and (p_no_payment_date_from = '' or p_no_payment_date_from is null)) then
581 p_return_status := 'payment from and to dates are null';
582 raise l_exp;
583
584 elsif(p_no_payment_date_from = '' or p_no_payment_date_from is null) then
585 p_return_status := 'payment from date is null';
586 raise l_exp;
587
588 elsif(p_no_payment_date_to = '' or p_no_payment_date_to is null) then
589 p_return_status := 'payment to date is null';
590 raise l_exp;
591 end if;
592 else
593 if((p_no_payment_date_from <> '' or p_no_payment_date_from is not null) or (p_no_payment_date_to <> '' or p_no_payment_date_to is not null)) then
594 p_return_status := 'payment for period is null';
595 raise l_exp;
596 end if;
597 end if;
598 if(to_date(p_no_payment_date_from) > to_date(p_no_payment_date_to)) then
599 p_return_status := 'payment from more than to';
600 raise l_exp;
601 end if;
602
603 if(to_date(p_no_payment_date_from) > sysdate) then
604 p_return_status := 'payment from after system_date';
605 raise l_exp;
606 end if;
607
608 if(to_date(p_no_payment_date_to) > sysdate) then
609 p_return_status := 'payment to after system_date';
610 raise l_exp;
611 end if;
612
613 if((months_between(to_date(p_no_payment_date_to),to_date(p_no_payment_date_from)) < 0.96) or (months_between(to_date(p_no_payment_date_to),to_date(p_no_payment_date_from)) > 12)) then
614 p_return_status := 'payment dates not between 1 and 12 months';
615 raise l_exp;
616 end if;
617
618 if(substr(p_no_payment_date_from,1,2) <> '06') then
619 p_return_status := 'payment from date is not 6th';
620 raise l_exp;
621 end if;
622
623 if(substr(p_no_payment_date_to,1,2) <> '05') then
624 p_return_status := 'payment to date is not 5th';
625 raise l_exp;
626 end if;
627
628 if((to_date(p_no_payment_date_from) < to_date('06-Mar-'||(l_related_tax_year -1 ))) or (to_date(p_no_payment_date_from) > to_date('05-Apr-'||(l_related_tax_year)))) then
629 p_return_status := 'payment from date not within tax year';
630 raise l_exp;
631 end if;
632
633 if((to_date(p_no_payment_date_to) < to_date('06-Mar-'||(l_related_tax_year -1 ))) or (to_date(p_no_payment_date_to) > to_date('05-Apr-'||(l_related_tax_year)))) then
634 p_return_status := 'payment to date not within tax year';
635 raise l_exp;
636 end if;
637
638 /* if((to_date(p_period_of_inactivity_from) < to_date('06-Mar-'||(l_related_tax_year -1 ))) or (to_date(p_period_of_inactivity_from) > to_date('05-Apr-'||(l_related_tax_year)))) then
639 p_return_status := 'inactive from date not within tax year';
640 raise l_exp;
641 end if;
642
643 if((to_date(p_period_of_inactivity_to) < to_date('06-Mar-'||(l_related_tax_year -1 ))) or (to_date(p_period_of_inactivity_to) > to_date('05-Apr-'||(l_related_tax_year)))) then
644 p_return_status := 'inactive to date not within tax year';
645 raise l_exp;
646 end if;*/
647
648 if (to_char(sysdate,'DD') < 6) then
649 if (to_char(to_date(p_period_of_inactivity_from),'DD') <> 6 or to_char(to_date(p_period_of_inactivity_from),'MM') <> to_char(sysdate,'MM') ) then
650 p_return_status := 'inactive from date is not start of next tax month';
651 raise l_exp;
652 end if;
653 else
654 if (to_char(to_date(p_period_of_inactivity_from),'DD') <> 6 or to_char(to_date(p_period_of_inactivity_from),'MM') <> to_char(add_months(sysdate,1),'MM') ) then
655 p_return_status := 'inactive from date is not start of next tax month';
656 raise l_exp;
657 end if;
658 end if;
659
660
661 if((p_period_of_inactivity_from <> '' or p_period_of_inactivity_from is not null) and (p_period_of_inactivity_to = '' or p_period_of_inactivity_to is null)) then
662 p_return_status := 'inactive to date is null';
663 raise l_exp;
664 end if;
665
666 if((p_period_of_inactivity_to <> '' or p_period_of_inactivity_to is not null) and (p_period_of_inactivity_from = '' or p_period_of_inactivity_from is null)) then
667 p_return_status := 'inactive from date is null';
668 raise l_exp;
669 end if;
670
671 if(to_date(p_period_of_inactivity_from) > to_date(p_period_of_inactivity_to)) then
672 p_return_status := 'inactive from more than to';
673 raise l_exp;
674 end if;
675
676 if((months_between(to_date(p_period_of_inactivity_to),to_date(p_period_of_inactivity_from)) < 0.96) or (months_between(to_date(p_period_of_inactivity_to),to_date(p_period_of_inactivity_from)) > 6)) then
677 p_return_status := 'inactivity dates not between 1 and 6 months';
678 raise l_exp;
679 end if;
680
681 if(substr(p_period_of_inactivity_from,1,2) <> '06') then
682 p_return_status := 'inactivity from date is not 6th';
683 raise l_exp;
684 end if;
685
686 if(substr(p_period_of_inactivity_to,1,2) <> '05') then
687 p_return_status := 'inactivity to date is not 5th';
688 raise l_exp;
689 end if;
690
691
692 if(to_date(p_period_of_inactivity_to) < sysdate) then
693 p_return_status := 'inactive to before system_date';
694 raise l_exp;
695 end if;
696
697 if(p_final_submission_ceased = 'Y' and(p_date_scheme_ceased = '' or p_date_scheme_ceased is null)) then
698 p_return_status := 'date scheme is null';
699 raise l_exp;
700 end if;
701 if((p_final_submission_ceased = 'N' or p_final_submission_year = 'Y') and (p_date_scheme_ceased <> '' or p_date_scheme_ceased is not null)) then
702 p_return_status := 'date scheme is present but final submission ceased is not present';
703 raise l_exp;
704 end if;
705
706 if((to_date(p_date_scheme_ceased) < to_date('06-Mar-'||(l_related_tax_year -1 ))) or (to_date(p_date_scheme_ceased) > to_date('05-Apr-'||(l_related_tax_year)))) then
707 p_return_status := 'date scheme not within tax year';
708 raise l_exp;
709 end if;
710
711 if(to_date(p_date_scheme_ceased) > sysdate) then
712 p_return_status := 'date scheme ceased after system_date';
713 raise l_exp;
714 end if;
715
716 if p_approver_id = 0 then
717 l_approver_id := NULL;
718 else l_approver_id := p_approver_id;
719 end if;
720
721 UPDATE PAY_GB_EPS_DETAILS
722 SET
723 TAX_FREE_PYMT = p_tax_free_pymt,
724 EMP_THIRD_PARTY = p_emp_third_party,
725 EXP_BEN_OTHERS = p_exp_ben_othrs,
726 P11D_DUE = p_p11d_due,
727 EMP_OUTSIDE_UK = p_emp_outside_uk,
728 PAYMENT_DUE = p_payment_due,
729 FINAL_SUBMISSION_CEASED = p_final_submission_ceased,
730 FINAL_SUBMISSION_YEAR = p_final_submission_year,
731 SERVICE_COMPANY = p_service_company,
732 SMP_AMT_RECOVERED_CALC = p_smp_amt_recovered_calc,
733 SAP_AMT_RECOVERED_CALC = p_sap_amt_recovered_calc,
734 OSPP_AMT_RECOVERED_CALC = p_ospp_amt_recovered_calc,
735 ASPP_AMT_RECOVERED_CALC = p_aspp_amt_recovered_calc,
736 SSP_AMT_RECOVERED_CALC = p_ssp_amt_recovered_calc,
737 NIC_COMP_SMP_CALC = p_nic_comp_smp_calc,
738 NIC_COMP_SAP_CALC = p_nic_comp_sap_calc,
739 NIC_COMP_OSPP_CALC = p_nic_comp_ospp_calc,
740 NIC_COMP_ASPP_CALC = p_nic_comp_aspp_calc,
741 NIC_COMP_SSP_CALC = p_nic_comp_ssp_calc,
742 CIS_DEDUCTIONS_STUFF = p_cis_deductions_suff,
743 NIC_HOLIDAY_VAL = p_nic_holiday_val,
744 ADV_FROM_HMRC = p_adv_from_hmrc,
745 APPROVER_ID = l_approver_id,
746 NO_PAYMENT_DATE_FROM = p_no_payment_date_from,
747 NO_PAYMENT_DATE_TO = p_no_payment_date_to,
748 PERIOD_OF_INACTIVITY_FROM = p_period_of_inactivity_from,
749 PERIOD_OF_INACTIVITY_TO = p_period_of_inactivity_to,
750 DATE_SCHEME_CEASED = p_date_scheme_ceased,
751 REQUEST_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, request_id, FND_GLOBAL.CONC_REQUEST_ID),
752 PROGRAM_APPLICATION_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_application_id, FND_GLOBAL.PROG_APPL_ID),
753 PROGRAM_ID = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_id, FND_GLOBAL.CONC_PROGRAM_ID),
754 PROGRAM_UPDATE_DATE = DECODE(FND_GLOBAL.CONC_REQUEST_ID, -1, program_update_date, SYSDATE)
755 WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
756 AND EFFECTIVE_DATE = p_effective_date
757 AND BUSINESS_GROUP_ID = p_business_group_id;
758
759 COMMIT;
760
761 p_return_status := 'S';
762 hr_utility.set_location('Updated the EPS record',100);
763 hr_utility.set_location(' Leaving :' || l_proc,100);
764
765 exception
766 when l_exp then
767
768 hr_utility.set_location(' Exception :' || l_proc,100);
769 when others then
770 raise_application_error(-20001,SQLERRM);
771
772 END update_eps_record;
773
774 procedure update_final_submission(
775 p_effective_date IN VARCHAR2,
776 p_emp_paye_reference IN VARCHAR2,
777 p_business_group_id IN VARCHAR2,
778 p_final_submission_ceased IN VARCHAR2,
779 p_final_submission_year IN VARCHAR2
780 )
781 is
782 l_proc varchar2(72) := g_package||'update_final_submission';
783
784 begin
785 hr_utility.set_location(' Entering :' || l_proc,160);
786
787 UPDATE
788 PAY_GB_EPS_DETAILS
789 SET FINAL_SUBMISSION_CEASED = p_final_submission_ceased,
790 FINAL_SUBMISSION_YEAR = p_final_submission_year
791 WHERE EMP_PAYE_REFERENCE = p_emp_paye_reference
792 AND EFFECTIVE_DATE = p_effective_date
793 AND BUSINESS_GROUP_ID = p_business_group_id;
794
795 COMMIT;
796
797 end;
798
799 PROCEDURE start_eps_wf(eps_id IN VARCHAR2,status IN VARCHAR2,email_address IN VARCHAR2,user_name IN varchar2) IS
800
801 l_itemtype VARCHAR2(50) := 'EPSWF';
802 l_itemkey VARCHAR2(300) := 'EPS-' || eps_id;
803 description VARCHAR2(200);
804 send_email_to VARCHAR2(100);
805 PRAGMA AUTONOMOUS_TRANSACTION;
806
807 BEGIN
808
809
810 send_email_to := email_address;
811
812 if(status = 'Completed') then
813 description := 'You have an EPS record to be Approved';
814 elsif status = 'Verified' then
815 l_itemkey := l_itemkey||'-Done';
816 description := 'Your EPS record has been Approved';
817 end if;
818
819 -- Trace Messages
820 hr_utility.set_location('l_itemtype : '||l_itemtype,50);
821 hr_utility.set_location('l_itemkey : '||l_itemkey,50);
822 hr_utility.set_location('send_email_to : '||send_email_to,50);
823 hr_utility.set_location('description : '||description,50);
824
825
826 wf_engine.createprocess(l_itemtype, l_itemkey, 'MAIN_PROCESS');
827
828 wf_engine.setitemuserkey(itemtype => l_itemtype
829 ,itemkey => l_itemkey
830 ,userkey => 'USERKEY: ' || l_itemkey);
831
832 wf_engine.setitemowner(itemtype => l_itemtype
833 ,itemkey => l_itemkey
834 ,owner => 'SYSADMIN');
835
836 wf_engine.setitemattrtext(itemtype => l_itemtype
837 ,itemkey => l_itemkey
838 ,aname => 'EPS_ID'
839 ,avalue => eps_id);
840
841 wf_engine.setitemattrtext(itemtype => l_itemtype
842 ,itemkey => l_itemkey
843 ,aname => 'SEND_TO_EMAIL'
844 ,avalue => send_email_to);
845
846 wf_engine.setitemattrtext(itemtype => l_itemtype
847 ,itemkey => l_itemkey
848 ,aname => 'EPS_NOTIFICATION'
849 ,avalue => description);
850
851 wf_engine.setitemattrtext(itemtype => l_itemtype
852 ,itemkey => l_itemkey
853 ,aname => 'SEND_TO_ROLE'
854 ,avalue => user_name);
855
856 wf_engine.startprocess(l_itemtype, l_itemkey);
857
858 COMMIT;
859 END start_eps_wf;
860
861 PROCEDURE set_wf_approver_role
862 (
863 itemtype IN VARCHAR2
864 ,itemkey IN VARCHAR2
865 ,actid IN NUMBER
866 ,funcmode IN VARCHAR2
867 ,RESULT IN OUT NOCOPY VARCHAR2
868 ) IS
869
870 v_role_email VARCHAR2(300);
871 n_ctr INTEGER := 0;
872
873 BEGIN
874 v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
875 ,itemkey => itemkey
876 ,aname => 'SEND_TO_EMAIL'));
877
878 hr_utility.set_location('v_role_email : '||v_role_email,50);
879
880 SELECT COUNT(*)
881 INTO n_ctr
882 FROM wf_local_roles
883 WHERE NAME = v_role_email;
884
885 IF n_ctr = 0
886 THEN
887 wf_directory.createadhocrole(role_name => v_role_email
888 ,role_display_name => v_role_email
889 ,role_description => v_role_email
890 ,notification_preference => 'MAILHTML'
891 ,email_address => v_role_email
892 ,status => 'ACTIVE'
893 ,expiration_date => NULL);
894 END IF;
895
896 RESULT := 'COMPLETE:Y';
897 END set_wf_approver_role;
898
899 procedure submit_eps
900 (
901 errbuf OUT NOCOPY VARCHAR2,
902 retcode OUT NOCOPY NUMBER,
903 p_emp_paye_reference in varchar2,
904 p_effective_date in varchar2,
905 p_test_submission in varchar2,
906 p_business_group_id in varchar2)
907
908 is
909
910 cursor csr_eps is
911 select emp_paye_reference,object_version_number ovn,account_office_number,employer_name,econ_number,related_tax_year,final_submission_ceased,final_submission_year from
912 pay_gb_eps_details
913 where emp_paye_reference = p_emp_paye_reference
914 and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
915 and business_group_id = p_business_group_id
916 and status = 'Verified';
917
918
919 cursor csr_eps_all_paye is
920 select emp_paye_reference,object_version_number ovn,account_office_number,employer_name,econ_number,related_tax_year,final_submission_ceased,final_submission_year from
921 pay_gb_eps_details
922 where effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
923 and business_group_id = p_business_group_id
924 and status = 'Verified';
925
926 cursor csr_reqid is
927 select REQUEST_ID from FND_CONC_REQ_SUMMARY_V
928 where PROGRAM_SHORT_NAME='PYGBRTIEPS13'
929 and REQUEST_DATE = (select max(request_date) from FND_CONC_REQ_SUMMARY_V where PROGRAM_SHORT_NAME='PYGBRTIEPS13');
930
931
932 l_proc varchar2(72) := g_package||'submit_eps';
933 xml_layout boolean;
934 l_request_id fnd_concurrent_requests.request_id%TYPE;
935 l_csr_eps csr_eps%rowtype;
936 l_csr_eps_all_paye csr_eps_all_paye%rowtype;
937 l_result varchar(2);
938 l_effective_date varchar2(15);
939 l_test_submission varchar2(1);
940 l_final_submission_ceased VARCHAR2(1) := 'N';
941 l_final_submission_year VARCHAR2(1) := 'N';
942 l_req_id csr_reqid%rowtype;
943 l_valid number;
944 l_count number :=0 ;
945 l_valid_payments number;
946
947 begin
948
949
950 hr_utility.set_location(' Entering :' || l_proc,160);
951
952 hr_utility.set_location(' p_paye_ref :' || p_emp_paye_reference,160);
953 hr_utility.set_location(' p_effective_date :' || p_effective_date,160);
954 hr_utility.set_location(' p_test_submission :' || p_test_submission,160);
955 hr_utility.set_location(' p_business_group_id :' || p_business_group_id,160);
956
957 select to_char(to_date(substr(p_effective_date,0,10),'YYYY-MM-DD'),'DD-MON-YYYY') into l_effective_date from dual;
958
959 open csr_reqid;
960 fetch csr_reqid into l_req_id;
961 close csr_reqid;
962
963 hr_utility.set_location(' l_req_id :'||l_req_id.request_id,160);
964
965 if p_test_submission = 'Y' then
966 l_test_submission := '1';
967 else l_test_submission := ' ';
968 end if;
969
970 /*if p_final_submission = 'Yes - Final for Ceased PAYE Ref' then
971 l_final_submission_ceased := 'Y';
972 elsif p_final_submission = 'Yes - Final for the Year' then
973 l_final_submission_year := 'Y';
974 end if;*/
975
976 fnd_file.put_line(fnd_file.output,rpad('PAYE Reference : ',20) || p_emp_paye_reference);
977 fnd_file.put_line(fnd_file.output,rpad('Effective Date : ',20) || l_effective_date);
978 fnd_file.put_line(fnd_file.output,' ');
979
980 -- If Specific PAYE REF is selected
981 if p_emp_paye_reference is not null then
982
983 -- to get concurrent program request id
984 open csr_eps;
985 fetch csr_eps into l_csr_eps;
986 if csr_eps%found then
987
988 l_valid_payments := validate_payments(l_csr_eps.emp_paye_reference,p_effective_date,p_business_group_id);
989 l_valid := validate(l_csr_eps.emp_paye_reference,l_csr_eps.account_office_number,l_csr_eps.employer_name,l_csr_eps.econ_number,l_csr_eps.related_tax_year,l_csr_eps.final_submission_ceased,l_csr_eps.final_submission_year);
990
991 -- To update Final Submission ceased and Final Submission Year
992 update_final_submission(l_effective_date,p_emp_paye_reference,p_business_group_id,l_csr_eps.final_submission_ceased,l_csr_eps.final_submission_year);
993
994 hr_utility.set_location('This is a eText report, Spawn the BI Publisher process',1);
995 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTIEPSOP13','en','US','ETEXT');
996
997 IF xml_layout = true
998 THEN
999 l_request_id := fnd_request.submit_request
1000 (application => 'PAY'
1001 ,program => 'PYGBRTIEPSOP13'
1002 ,argument1 => p_emp_paye_reference
1003 ,argument2 => p_effective_date
1004 ,argument3 => l_test_submission
1005 ,argument4 => l_req_id.request_id
1006 ,argument5 => p_business_group_id
1007 );
1008 Commit;
1009
1010 --check for process submit error
1011 IF l_request_id = 0
1012 THEN
1013 hr_utility.set_location('Error spawning new process',1);
1014 ELSE
1015 -- Update the status of the EPS Record to 'Sent'
1016 update_status_eps_record(l_effective_date,l_csr_eps.emp_paye_reference,p_business_group_id,'Sent',l_csr_eps.ovn+1,'N',l_result);
1017 if(l_result = 'E') then
1018 -- Someone modified the record
1019 fnd_file.put_line(fnd_file.output,'The EPS Record has been modified.Please resubmit the concurrent program. ');
1020 hr_utility.set_location('The Record has been modified.Please resubmit the concurrent program.',100);
1021 elsif l_result = 'S' then
1022 --Success
1023 fnd_file.put_line(fnd_file.output,'The EPS Record has been processed. ');
1024 hr_utility.set_location('The record has been Updated successfully',100);
1025 end if;
1026
1027 END IF;
1028 END IF;
1029 close csr_eps;
1030 else
1031 fnd_file.put_line(fnd_file.output,'No Records found. ');
1032 end if;
1033
1034 --If all Paye REferences are selected.
1035 else
1036
1037 -- Validation
1038 open csr_eps_all_paye;
1039 fetch csr_eps_all_paye into l_csr_eps_all_paye;
1040 loop
1041 exit when csr_eps_all_paye%notfound;
1042 l_valid_payments := validate_payments(l_csr_eps_all_paye.emp_paye_reference,p_effective_date,p_business_group_id);
1043 l_valid := validate(l_csr_eps_all_paye.emp_paye_reference,l_csr_eps_all_paye.account_office_number,
1044 l_csr_eps_all_paye.employer_name,l_csr_eps_all_paye.econ_number,l_csr_eps_all_paye.related_tax_year,
1045 l_csr_eps_all_paye.final_submission_ceased,l_csr_eps_all_paye.final_submission_year);
1046
1047 fetch csr_eps_all_paye into l_csr_eps_all_paye;
1048 end loop;
1049 close csr_eps_all_paye;
1050
1051 open csr_eps_all_paye;
1052 fetch csr_eps_all_paye into l_csr_eps_all_paye;
1053 fnd_file.put_line(fnd_file.output,'');
1054 -- if csr_eps_all_paye%found then
1055 loop
1056 exit when csr_eps_all_paye%notfound;
1057 l_count := 1;
1058 -- To update Final Submission ceased and Final Submission Year
1059 update_final_submission(l_effective_date,l_csr_eps_all_paye.emp_paye_reference,p_business_group_id,l_csr_eps_all_paye.final_submission_ceased,l_csr_eps_all_paye.final_submission_year);
1060
1061 hr_utility.set_location('PAYE REF : '||l_csr_eps_all_paye.emp_paye_reference,1);
1062 hr_utility.set_location('This is a eText report, Spawn the BI Publisher process',1);
1063
1064 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTIEPSOP13','en','US','ETEXT');
1065
1066 IF xml_layout = true
1067 THEN
1068 l_request_id := fnd_request.submit_request
1069 (application => 'PAY'
1070 ,program => 'PYGBRTIEPSOP13'
1071 ,argument1 => l_csr_eps_all_paye.emp_paye_reference
1072 ,argument2 => p_effective_date
1073 ,argument3 => l_test_submission
1074 ,argument4 => l_req_id.request_id
1075 ,argument5 => p_business_group_id
1076 );
1077 Commit;
1078
1079 --check for process submit error
1080 IF l_request_id = 0
1081 THEN
1082 hr_utility.set_location('Error spawning new process',1);
1083 ELSE
1084 -- Update the status of the EPS Record to 'Sent'
1085 update_status_eps_record(l_effective_date,l_csr_eps_all_paye.emp_paye_reference,p_business_group_id,'Sent',l_csr_eps_all_paye.ovn+1,'N',l_result);
1086 if(l_result = 'E') then
1087 -- Someone modified the record
1088 hr_utility.set_location('The Record has been modified.Please resubmit the concurrent program.',100);
1089 elsif l_result = 'S' then
1090 --Success
1091 fnd_file.put_line(fnd_file.output,'The EPS Record with PAYE Reference '||l_csr_eps_all_paye.emp_paye_reference||' has been processed. ');
1092 hr_utility.set_location('The record has been Updated successfully',100);
1093 end if;
1094
1095 END IF;
1096 END IF;
1097
1098 fetch csr_eps_all_paye into l_csr_eps_all_paye;
1099 end loop;
1100 -- end if;
1101 close csr_eps_all_paye;
1102 if l_count = 0 then
1103 fnd_file.put_line(fnd_file.output,'No Records found. ');
1104 end if;
1105 end if;
1106
1107
1108 end submit_eps;
1109
1110 procedure rollback_eps
1111 (
1112 errbuf OUT NOCOPY VARCHAR2,
1113 retcode OUT NOCOPY NUMBER,
1114 p_emp_paye_reference in varchar2,
1115 p_effective_date in varchar2,
1116 p_business_group_id in varchar2
1117 )
1118 is
1119
1120 cursor csr_eps is
1121 select emp_paye_reference,object_version_number ovn from
1122 pay_gb_eps_details
1123 where emp_paye_reference = p_emp_paye_reference
1124 and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
1125 and business_group_id = p_business_group_id
1126 and status = 'Sent';
1127
1128 l_proc varchar2(72) := g_package||'rollback_eps';
1129 l_csr_eps csr_eps%rowtype;
1130 l_result varchar2(1);
1131 l_effective_date varchar2(15);
1132 begin
1133
1134 hr_utility.set_location(' Entering :' || l_proc,160);
1135 hr_utility.set_location(' p_paye_ref :' || p_emp_paye_reference,160);
1136 hr_utility.set_location(' p_effective_date :' || p_effective_date,160);
1137
1138 select to_char(to_date(substr(p_effective_date,0,10),'YYYY-MM-DD'),'DD-MON-YYYY') into l_effective_date from dual;
1139
1140 fnd_file.put_line(fnd_file.output,rpad('PAYE Reference : ',20) || p_emp_paye_reference);
1141 fnd_file.put_line(fnd_file.output,rpad('Effective Date : ',20) || l_effective_date);
1142 fnd_file.put_line(fnd_file.output,' ');
1143
1144 open csr_eps;
1145 fetch csr_eps into l_csr_eps;
1146 if csr_eps%found then
1147 fnd_file.put_line (fnd_file.LOG,' : Rollback starts');
1148 -- update the status to 'Sent'
1149 -- update final submission ceased and year to 'No'
1150 update_final_submission(l_effective_date,p_emp_paye_reference,p_business_group_id,'N','N');
1151 update_status_eps_record(l_effective_date,p_emp_paye_reference,p_business_group_id,'Verified',l_csr_eps.ovn+1,'N',l_result);
1152
1153 if(l_result = 'E') then
1154 -- Someone modified the record
1155 hr_utility.set_location('The EPS record has been modified.Please resubmit the concurrent program.',100);
1156 fnd_file.put_line(fnd_file.output,'The EPS record has been modified.Please resubmit the concurrent program.');
1157 elsif l_result = 'S' then
1158 --Success
1159 hr_utility.set_location('The record has been rolled back successfully',100);
1160 fnd_file.put_line(fnd_file.output,'The EPS Record has been rolled back. ');
1161 end if;
1162 else
1163 fnd_file.put_line(fnd_file.output,'No Records found. ');
1164 end if;
1165
1166 end rollback_eps;
1167
1168 function validate_payments(p_emp_paye_reference varchar2,p_effective_date varchar2,p_business_group_id in varchar2) return number
1169 is
1170
1171 cursor csr_eps is
1172 select
1173 smp_amt_recovered_calc,
1174 sap_amt_recovered_calc,
1175 ospp_amt_recovered_calc,
1176 aspp_amt_recovered_calc,
1177 ssp_amt_recovered_calc,
1178 nic_comp_smp_calc,
1179 nic_comp_sap_calc,
1180 nic_comp_ospp_calc,
1181 nic_comp_aspp_calc,
1182 nic_comp_ssp_calc,
1183 cis_deductions_stuff,
1184 nic_holiday_val
1185 from pay_gb_eps_details
1186 where emp_paye_reference = p_emp_paye_reference
1187 and effective_date = to_date(substr(p_effective_date,0,10),'YYYY-MM-DD')
1188 and business_group_id = p_business_group_id
1189 and status = 'Verified';
1190
1191 l_csr_eps csr_eps%rowtype;
1192 l_exp EXCEPTION;
1193 l_err boolean :=FALSE;
1194
1195 begin
1196 hr_utility.set_location('r',100);
1197
1198 open csr_eps;
1199 fetch csr_eps into l_csr_eps;
1200 close csr_eps;
1201
1202 if l_csr_eps.smp_amt_recovered_calc < 0 then
1203 hr_utility.set_location('SMP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1204 fnd_file.put_line(fnd_file.output,'SMP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1205 l_err := true;
1206 end if;
1207
1208 if l_csr_eps.sap_amt_recovered_calc < 0 then
1209 hr_utility.set_location('SAP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1210 fnd_file.put_line(fnd_file.output,'SAP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1211 l_err := true;
1212 end if;
1213
1214 if l_csr_eps.ospp_amt_recovered_calc < 0 then
1215 hr_utility.set_location('OSSP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1216 fnd_file.put_line(fnd_file.output,'OSSP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1217 l_err := true;
1218 end if;
1219
1220 if l_csr_eps.aspp_amt_recovered_calc < 0 then
1221 hr_utility.set_location('ASPP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1222 fnd_file.put_line(fnd_file.output,'ASPP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1223 l_err := true;
1224 end if;
1225
1226 if l_csr_eps.ssp_amt_recovered_calc < 0 then
1227 hr_utility.set_location('SSP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1228 fnd_file.put_line(fnd_file.output,'SSP Recovered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1229 l_err := true;
1230 end if;
1231
1232 if l_csr_eps.nic_comp_smp_calc < 0 then
1233 hr_utility.set_location('NIC Compensation on SMP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1234 fnd_file.put_line(fnd_file.output,'NIC Compensation on SMP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1235 l_err := true;
1236 end if;
1237
1238 if l_csr_eps.nic_comp_sap_calc < 0 then
1239 hr_utility.set_location('NIC Compensation on SAP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1240 fnd_file.put_line(fnd_file.output,'NIC Compensation on SAP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1241 l_err := true;
1242 end if;
1243
1244 if l_csr_eps.nic_comp_ospp_calc < 0 then
1245 hr_utility.set_location('NIC Compensation on OSPP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1246 fnd_file.put_line(fnd_file.output,'NIC Compensation on OSPP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1247 l_err := true;
1248 end if;
1249
1250 if l_csr_eps.nic_comp_aspp_calc < 0 then
1251 hr_utility.set_location('NIC Compensation on ASPP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1252 fnd_file.put_line(fnd_file.output,'NIC Compensation on ASPP, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1253 l_err := true;
1254 end if;
1255
1256 if l_csr_eps.cis_deductions_stuff < 0 then
1257 hr_utility.set_location('CIS Deductions Suffered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1258 fnd_file.put_line(fnd_file.output,'CIS Deductions Suffered, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1259 l_err := true;
1260 end if;
1261
1262 if l_csr_eps.nic_holiday_val < 0 then
1263 hr_utility.set_location('NICs Holiday, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.',10);
1264 fnd_file.put_line(fnd_file.output,'NICs Holiday, must be greater than or equal to zero for PAYE Reference '||p_emp_paye_reference||'.');
1265 l_err := true;
1266 end if;
1267
1268
1269 if (l_err) then
1270 raise l_exp;
1271 end if;
1272
1273 return 1;
1274 EXCEPTION
1275 when l_exp then
1276 raise_application_error(-20001,'Errors found while archiving data.');
1277 return 0;
1278
1279 end;
1280
1281
1282 function validate(emp_paye_reference varchar2,acc_off_no varchar2,employer_name varchar2,econ varchar2,tax_year varchar2,
1283 final_submission_ceased varchar2 default null,final_submission_year varchar2 default null) return number
1284 is
1285 l_err boolean :=FALSE;
1286 l_exp EXCEPTION;
1287 hmrc_office_no varchar2(3);
1288 emp_paye_ref varchar2(15);
1289 l_sender_id varchar2(35);
1290 l_current_tax_year varchar2(4);
1291
1292 cursor csr_sender_id is
1293 select nvl(UPPER(substr(hoi.org_information11,1,35)),' ') SENDER_ID
1294 from hr_organization_information hoi
1295 where hoi.org_information_context = 'Tax Details References'
1296 and nvl(hoi.org_information10,'UK') = 'UK'
1297 and hoi.org_information1 = emp_paye_reference;
1298
1299
1300 begin
1301
1302 open csr_sender_id;
1303 fetch csr_sender_id into l_sender_id;
1304 close csr_sender_id;
1305
1306 hmrc_office_no := nvl(substr(emp_paye_reference,0,instr(emp_paye_reference,'/')-1),' ');
1307 emp_paye_ref := nvl(upper(substr(ltrim(substr(emp_paye_reference,4,11),'/'),1,10)),' ');
1308
1309 if hmrc_office_no = ' ' or hmrc_office_no is null then
1310 hr_utility.set_location('The HMRC Office Number is missing.',10);
1311 fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
1312 l_err := true;
1313 elsif validate_input(substr(hmrc_office_no,1,3),'NUMBER') > 0
1314 then
1315 hr_utility.set_location('The HMRC Office Number '||hmrc_office_no||' has invalid characters.',10);
1316 fnd_file.put_line(fnd_file.output,'The HMRC Office Number '||hmrc_office_no||' has invalid characters.');
1317 l_err := true;
1318 end if;
1319
1320 if emp_paye_ref = ' ' or emp_paye_ref is null then
1321 hr_utility.set_location('The Employer PAYE Reference is missing.',10);
1322 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
1323 l_err := true;
1324 elsif validate_input(emp_paye_ref,'FULL_EDI') > 0 then
1325 hr_utility.set_location('The Employer PAYE Reference '||emp_paye_reference|| ' has invalid characters.',10);
1326 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference '||emp_paye_reference||' has invalid characters.');
1327 l_err := true;
1328 end if;
1329
1330 if employer_name = ' ' or employer_name is null then
1331 hr_utility.set_location('The Employer Name for PAYE Reference '||emp_paye_reference||' is missing.',10);
1332 fnd_file.put_line(fnd_file.output,'The Employer Name for PAYE Reference '||emp_paye_reference||' is missing.');
1333 l_err := true;
1334 elsif validate_input(employer_name,'EMP_NAME') > 0 then
1335 hr_utility.set_location('The Employer Name '||employer_name||' for PAYE Reference '||emp_paye_reference||' has invalid characters .',10);
1336 fnd_file.put_line(fnd_file.output,'The Employer Name '||employer_name||' for PAYE Reference '||emp_paye_reference||' has invalid characters .');
1337 l_err := true;
1338 end if;
1339
1340 if acc_off_no = ' ' or acc_off_no is null then
1341 hr_utility.set_location('The Employer Accounts Office Ref for PAYE Reference '||emp_paye_reference||' is missing.',10);
1342 fnd_file.put_line(fnd_file.output,'The Employer Accounts Office Ref for PAYE Reference '||emp_paye_reference||' is missing.');
1343 l_err := true;
1344
1345 elsif (length(acc_off_no) > 13
1346 OR REGEXP_INSTR(acc_off_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
1347
1348 hr_utility.set_location ('Incorrect format entered for Employer Accounts Office Ref field for PAYE Reference '||emp_paye_reference||'.Valid format is NNNPANNNNNNNX.',10);
1349 fnd_file.put_line(fnd_file.output,'Incorrect format entered for Accounts Office Ref Num field for PAYE Reference '||emp_paye_reference||'.Valid format is NNNPANNNNNNNX.');
1350 l_err := true;
1351 END IF;
1352
1353
1354 -- ECON Validations
1355 /* IF (final_submission_ceased = 'Y' OR final_submission_year = 'Y') THEN
1356 IF econ = ' ' OR econ IS NULL THEN
1357 hr_utility.set_location ('ECON for PAYE Reference '||emp_paye_reference||' is missing.',10);
1358 fnd_file.put_line(fnd_file.output,'ECON for PAYE Reference '||emp_paye_reference||' is missing.');
1359 l_err := true;
1360 END IF;
1361 END IF;
1362
1363 IF (econ is not null AND econ <> ' ' ) AND econ_validate(econ) = 0 THEN
1364 hr_utility.set_location ('ECON for PAYE Reference '||emp_paye_reference||' is invalid.',10);
1365 fnd_file.put_line(fnd_file.output,'ECON for PAYE Reference '||emp_paye_reference||' is invalid.');
1366 l_err := true;
1367 END IF;*/
1368
1369 if (final_submission_ceased is not null or final_submission_year is not null) then
1370 if l_sender_id is null or l_sender_id = ' ' then
1371 hr_utility.set_location('The EDI Sender ID for PAYE Reference '||emp_paye_reference||' is missing.',10);
1372 fnd_file.put_line(fnd_file.output,'The EDI Sender ID for PAYE Reference '||emp_paye_reference||' is missing.');
1373 l_err := true;
1374 end if;
1375 end if;
1376
1377 -- Tax Year Validations
1378 /* if tax_year < 2013 then
1379 hr_utility.set_location('The Tax Year of Effective Date must be 2013 or later.',10);
1380 fnd_file.put_line(fnd_file.output,'The Tax Year of Effective Date must be 2013 or later.');
1381 l_err := true;
1382 end if;
1383
1384 -- Calculate Current Tax Year using sysdate
1385 If sysdate >= to_date('06-04-'||substr(to_char(sysdate,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
1386 l_current_tax_year := substr(to_char(sysdate,'YYYY/MON/DD'),1,4)+1;
1387 Else
1388 l_current_tax_year := substr(to_char(sysdate,'YYYY/MON/DD'),1,4) ;
1389 End If;
1390
1391 hr_utility.trace('tax year = '||tax_year);
1392 hr_utility.trace('Current year = '||l_current_tax_year);
1393 if( tax_year < (l_current_tax_year - 2 ) or tax_year > l_current_tax_year ) then
1394 hr_utility.set_location('The Tax Year of Effective Date must be within the Current Tax Year - 2 to Current Tax Year.',10);
1395 fnd_file.put_line(fnd_file.output,'The Tax Year of Effective Date must be within the Current Tax Year - 2 to Current Tax Year.');
1396 l_err := true;
1397 end if; */
1398
1399 if (l_err) then
1400 raise l_exp;
1401 end if;
1402
1403 return 1;
1404 EXCEPTION
1405 when l_exp then
1406 raise_application_error(-20001,'Errors found while archiving data.');
1407 return 0;
1408 end;
1409
1410 --
1411 -- Function to validate the format of the fields
1412 function validate_input(p_input_value varchar2,
1413 p_validate_mode varchar2)
1414 return number is
1415 --
1416 l_valid number := 0;
1417 l_invalid_char constant varchar2(1) := '~'; -- required for translate
1418 l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
1419
1420 --EPS
1421 l_emp_set constant varchar2(36) := '-''.';
1422 l_space constant varchar2(10) := ' ';
1423 l_mix_chars constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
1424 l_number_chk constant varchar2(10) := '0123456789';
1425 l_char_set_A constant varchar2(36) := '.,-()/=!""%&*;<>''+:?\[]@$#^{}_';
1426 l_char_set_B constant varchar2(52) := '.,-()/=!""%&*;<>''+:?';
1427 l_char_set_C constant varchar2(52) := '.-''';
1428 l_char_set_D constant varchar2(52) := '-''';
1429 l_translated_value varchar2(200); -- Required to output failing char.
1430 --
1431 BEGIN
1432 --
1433 hr_utility.trace('Entering validate_input');
1434 hr_utility.trace('p_validate_mode='||p_validate_mode);
1435 hr_utility.trace('p_input_value='||p_input_value);
1436 --
1437
1438 if p_validate_mode = 'EMP_NAME' then
1439 if ( substr(p_input_value,1,1) =' ') then
1440 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1441 l_valid := 2;
1442 else
1443 l_translated_value :=
1444 translate(p_input_value,
1445 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_A||l_space,l_invalid_char);
1446
1447 if l_translated_value is not null then
1448 hr_utility.trace('Invalid chars found: '||l_translated_value);
1449 l_valid := 1; -- Not valid
1450 else
1451 l_valid := 0; -- Valid
1452 end if;
1453 end if ;
1454
1455 elsIF p_validate_mode = 'NUMBER' then
1456 --
1457 -- Check for Valid First Char
1458 --
1459 l_translated_value :=
1460 translate(p_input_value,
1461 l_invalid_char||l_number_chk,l_invalid_char);
1462
1463 if l_translated_value is not null then
1464 hr_utility.trace('Invalid chars found: '||l_translated_value);
1465 l_valid := 1; -- Not valid
1466 else
1467 l_valid := 0; -- Valid
1468 end if;
1469
1470 elsIF p_validate_mode = 'FULL_EDI' then
1471 --
1472 -- Check for Valid First Char
1473 --
1474 if ( substr(p_input_value,1,1) =' ') then
1475 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1476 l_valid := 2;
1477 else
1478 l_translated_value :=
1479 translate(p_input_value,
1480 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1481
1482 if l_translated_value is not null then
1483 hr_utility.trace('Invalid chars found: '||l_translated_value);
1484 l_valid := 1; -- Not valid
1485 else
1486 l_valid := 0; -- Valid
1487 end if;
1488 end if;
1489 END IF;
1490 --
1491 hr_utility.trace('Leaving validate_input');
1492 return l_valid;
1493 end validate_input;
1494
1495
1496 -- Returns 1 on success, 0 on failure
1497 function econ_validate(econ varchar2)
1498 return number
1499 is
1500 l_fixed_value number := 37;
1501 l_temp number := 0;
1502 l_sum number := 0 ;
1503 l_reminder number;
1504 l_check_letters varchar2(19) := 'ABCDEFHJKLMNPQRTWXY';
1505
1506 begin
1507 hr_utility.trace('Entering ECON Validate');
1508
1509 -- Format Check ENNNNNNNA
1510 IF regexp_instr(econ,'^E[[:digit:]]{7}[[:alpha:]]$') = 0 THEN
1511 hr_utility.set_location('ECON Validation Fails',10);
1512 return 0;-- Validation fails
1513 END IF;
1514
1515 -- First Char must be E
1516 IF substr(econ,1,1) <> 'E' THEN
1517 hr_utility.set_location('ECON Validation Fails',10);
1518 return 0;-- Validation fails
1519
1520 -- Next 7 should be numeric ranging 3000000 up to 3999999
1521 ELSIF substr(econ,2,7) < 3000000 OR substr(econ,2,7) > 3999999 THEN
1522 hr_utility.set_location('ECON Validation Fails',20);
1523 return 0;-- Validation fails
1524 END IF;
1525
1526 -- Last char must be alpha satifying modulas 19 rule
1527 for i in reverse 2..8 loop
1528 l_temp := substr(econ,(10-i),1) * i;
1529 l_sum := l_sum + l_temp;
1530 end loop;
1531
1532 l_sum := l_sum + l_fixed_value;
1533 l_reminder := mod(l_sum,19);
1534
1535 IF substr(l_check_letters,l_reminder+1,1) <> substr(econ,9,1) THEN
1536 hr_utility.set_location('ECON Validation Fails',30);
1537 return 0; -- Validation Fails
1538 END IF;
1539
1540 return 1; -- Validation passes
1541 hr_utility.trace('Leaving ECON Validate');
1542 END econ_validate;
1543
1544 function tax_year return number
1545 is
1546 tax_year number;
1547 system_date date;
1548 begin
1549 select effective_date into system_date from fnd_sessions where session_id = userenv('SESSIONID');
1550 if( to_char(system_date,'mmdd')>='0406' ) then
1551 tax_year := to_number(to_char(system_date,'YYYY')) ;
1552 ELSE tax_year := to_number(to_char(system_date,'YYYY'))- 1;
1553 end if;
1554 return tax_year;
1555 end;
1556
1557 END pay_gb_eps_13;
[Home] [Help]