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