[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_MOVDED_EDI
Source
1 PACKAGE BODY PAY_GB_MOVDED_EDI as
2 /* $Header: pygbmedi.pkb 120.45.12020000.2 2012/07/06 10:32:51 vmaripal ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name
9 PAY_GB_MOVDED_EDI
10 Purpose
11 Package to control archiver process in the creation of assignment actions
12 and the creation of EDI Message files uing the magtape process for EDI
13 Message Types : P45(3), P46, P46PENNOT
14 This is a UK Specific payroll package.
15 Notes
16
17 History
18 10-OCT-2000 S.Robinson 115.0 Date Created.
19 06-NOV-2002 BTHAMMIN 115.1 Bug 2657976
20 A new procedure get_job_segment
21 is added.
22 09-DEC-2002 BTHAMMIN 115.2 Check for enabled and displayed
23 segments.
24 23-DEC-2002 NSUGAVAN 115.3 To be R8.0 compliant, commented out
25 function get_job_segment as it has been
26 moved to a different file(pygbjseg.pkh)
27 24-DEC-2002 AMILLS 115.4 Bug 2677022. Performance fix for three
28 c_state cursors.
29 23-DEC-2002 NSUGAVAN 115.5 Removed commented code.
30 21-MAY-2003 ASENGAR 115.6 Changed the cursor c_state of the
31 procedure p46_action_creation for picking
32 assignments on basis on P46_SEND_EDI_FLAG
33 and P46_SEND_EDI for Bug 1843915.
34 27-AUG-2003 SSEKHAR 115.7 Bug 1843915: Changed the cursor c_state of
35 p45_3_action_creation to pick assignments
36 on basis of P45_3_SEND_EDI_FLAG and
37 P45_3_SEND_EDI. Also the cursor c_state of
38 p46_pennot_action_creation has been changed
39 to pick up assignments based on
40 P46_PENNOT_SEND_EDI_FLAG and
41 P46_PENNOT_SEND_EDI
42 07-OCT-2004 ALIKHAR 115.8 Bug 3891351: Changed the cursors c_state of
43 p45_3_action_creation, p46_action_creation
44 and p46_pennot_action_creation to join
45 assignment table with period of service
46 table based on period_of_service_id column.
47 16-JUN-2006 KTHAMPAN 115.9 Code change for EDI Rollback.
48 23-JUN-2006 KTHAMPAN 115.10 Not to create asg action if no rows is found
49 on the per_assignment_extra_info table
50 (internal_action_creation)
51 29-JUN-2006 KTHAMPAN 115.11 Fix gscc error.
52 16-AUG-2006 TUKUMAR 115.12 Bug 5469122 : changed csr_student_loan in
53 function fetch_p45_3_rec
54 21-NOV-2006 TUKUMAR 115.13 Bug 5660011 : changed function fetch_tax_rec
55 31-Oct-2007 ABHGANGU 115.14 6345375 UK EOY Changes..
56 Added
57 fetch_45_46_pennot_rec
58 ,p46_5_pennot_action_creation
59 ,p45pt_3_action_creation
60 ,date_validate
61 Modified
62 archive_code
63 1-NOV-07 PARUSIA 115.15 Added check in range_cursor to log
64 error in Logfile for P45PT3 if Test Submission
65 is Yes and Test_ID is not provided. It also
66 raises an unhandled exception
67 1-NOV-07 PARUSIA 115.16 Added the same check of Test_ID for
68 P46_Pennot too.
69 19-Nov-2007 ABHGANGU 115.17 6345375 Added fetch_p46_5_rec,p46_5_action_creation
70 Changed internal_action_creation for P46 EDI
71 19-Nov-2007 PARUSIA 115.19 6345375 Changed fetch_p45_3_rec
72 to archive continue_student_loan_deductions
73 12-Dec-2007 PARUSIA 115.20 6643668 In fetch_tax_rec, commented the code
74 which was getting tax data from
75 run result values, prior to element element
76 values. Tax data should be picked only
77 from element entry values only irrespective
78 of run results. Also commented the check
79 in csr_paye_details of link's max effective
80 date.
81 27-Dec-2007 rlingama 115.23 6710197 Added function get_territory_short_name for
82 fetching country name for the country code
83 30-Jan-2007 parusia 115.24 6770200 Changed check_action to check if the employee
84 has already been processed by the old process
85 also. Like for P45PT3, check for P45(3) (old
86 process) also.
87 05-Feb-2007 apmishra 115.24 6652235 P46 Car Edi enhancement.
88 8-feb-2008 parusia 115.26 6804206 Corrected the l_tax_year_start conversion line.
89 8-feb-2008 parusia 115.27 6804206 Corrected the l_tax_year_start conversion line.
90 30-Apr-2008 rlingama 115.28 6994632 P45(3) minor enhancements for UK EOY Changes APR08
91 20-Oct-2008 dwkrishn 115.29 7433580 IYF Changes for Year 2009
92 20-Oct-2008 dwkrishn 115.30 7433580 Corrected check_action parameters
93 23-Oct-2008 dwkrishn 115.31 7433580 Added l_mode Parameter for P46
94 30-Oct-2008 dwkrishn 115.32 7433580 P45PT3 Changes Incorporated
95 03-Nov-2008 dwkrishn 115.33 7519033 Minor Changes for Bug 7519033
96 04-Nov-2008 dwkrishn 115.34 7433580 Fixed Pension start Date for v6
97 11-Nov-2008 dwkrishn 115.35 7433580 Modified Display of Displaying O/P
98 Added a PL/SQL Table,Populate it Via Formula Function,Display
99 in deinitialization_code Procedure
100 28-Nov-2008 dwkrishn 115.36 7433580 Minor Changes to internal_action_creation
101 07-Jan-2009 krreddy 115.37 7633799 Modified 3 data types in archinit procedure
102 05-Feb-2009 krreddy 115.38 8216080 Modified the code to implement P46Expat Notification
103 13-Feb-2009 krreddy 115.39 8216080 Modified the code to implement P46Expat Notification
104 26-Mar-2009 dwkrishn 115.40 8315067 Modified characterset for Job Title from FULL_EDI to P14_FULL_EDI
105 25-Jun-2009 dwkrishn 115.41 8609586 Modified characterset for address from EDI_SURNAME to P14_FULL_EDI
106 09-Jul-2009 dwkrishn 115.42 8609586 Modified characterset for employer address from FULL_EDI to P14_FULL_EDI
107 22-Jul-2009 rlingama 115.43 8574855 Added hint PER_ASSIGNMENTS_F_N12 in csr_asg cursor for tuning the query.
108 31-Jul-2009 namgoyal 115.44 8704601 Modified date_validate logic for P46_CAR
109 10-Aug-2009 namgoyal 115.45 8704601 Modified date_validate logic for P46_CAR
110 10-Aug-2009 namgoyal 115.46 8830306 Added Logic to display P46 ( MOVDED 6.0 ) in output file
111 22-Jan-2010 namgoyal 115.47 9255173,9255183 Updated for P46 V6 and P46 Expat eText reports
112 29-Jan-2010 namgoyal 115.48 9255173,9255183 Updated O/P file logic for P46 V6 and P46 Expat eText reports
113 24-Jan-2010 rlingama 115.49 9495487 Added upper for all columns of cursor csr_et_asg in write_body procedure
114 25-Mar-2010 krreddy 115.50 9503248 Modified the case of the parameter in validate_input function call
115 inside movded6_asg_etext_validations procedure.
116 28-Dec-2010 krreddy 115.51 10095492 Modified the movded6_asg_etext_validations procedure to accomodate below changes:
117 1. 10409668 - Implemented PAYE 2010-11 related changes in etext reports.
118 2. 10402719 - Address line2 issue.
119 27-Jan-2011 krreddy 115.52 10409668 Modified the movded6_asg_etext_validations and p46exp_asg_etext_validations
120 procedures to make etext version of Expat Process to work inline with magtape version.
121 06-Dec-2011 rajganga 115.53 13402234 Modified for Address Line2 issue.
122 22-Mar-2011 rajganga 115.54 13726927 Add Tax Basis Validation
123
124 ==============================================================================*/
125 --
126 --
127 TYPE act_info_rec IS RECORD
128 ( assignment_id number(20)
129 ,person_id number(20)
130 ,effective_date date
131 ,action_info_category varchar2(50)
132 ,act_info1 varchar2(300)
133 ,act_info2 varchar2(300)
134 ,act_info3 varchar2(300)
135 ,act_info4 varchar2(300)
136 ,act_info5 varchar2(300)
137 ,act_info6 varchar2(300)
138 ,act_info7 varchar2(300)
139 ,act_info8 varchar2(300)
140 ,act_info9 varchar2(300)
141 ,act_info10 varchar2(300)
142 ,act_info11 varchar2(300)
143 ,act_info12 varchar2(300)
144 ,act_info13 varchar2(300)
145 ,act_info14 varchar2(300)
146 ,act_info15 varchar2(300)
147 ,act_info16 varchar2(300)
148 ,act_info17 varchar2(300)
149 ,act_info18 varchar2(300)
150 ,act_info19 varchar2(300)
151 ,act_info20 varchar2(300)
152 ,act_info21 varchar2(300)
153 ,act_info22 varchar2(300)
154 ,act_info23 varchar2(300)
155 ,act_info24 varchar2(300)
156 ,act_info25 varchar2(300)
157 ,act_info26 varchar2(300)
158 ,act_info27 varchar2(300)
159 ,act_info28 varchar2(300)
160 ,act_info29 varchar2(300)
161 ,act_info30 varchar2(300)
162 );
163
164 TYPE action_info_table IS TABLE OF
165 act_info_rec INDEX BY BINARY_INTEGER;
166 ---
167
168
169 TYPE g_tax_rec IS RECORD(
170 tax_code VARCHAR2(20),
171 tax_basis VARCHAR2(20),
172 prev_paid VARCHAR2(20),
173 prev_tax VARCHAR2(20));
174
175 g_package CONSTANT VARCHAR2(20):= 'pay_gb_movded_edi.';
176 --
177 --
178 /*------------- PRIVATE PROCEDURE -----------------*/
179 --
180 --
181 FUNCTION validate_data(p_value in varchar2,
182 p_name in varchar2,
183 p_mode in varchar2) return boolean IS
184 l_proc CONSTANT VARCHAR2(50):= g_package||'validate_data';
185 BEGIN
186 hr_utility.set_location('Entering: '||l_proc,1);
187 if pay_gb_eoy_magtape.validate_input(UPPER(p_value),p_mode) > 0 then
188 hr_utility.set_location('Name/Value : ' || p_name || '/' || p_value ,10);
189 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
190 pay_core_utils.push_token('INPUT_NAME', p_name);
191 pay_core_utils.push_token('INPUT_VALUE', p_value);
192 return false;
193 end if;
194 hr_utility.set_location('Leaving: '||l_proc,999);
195 return true;
196 END validate_data;
197 --
198 --
199 FUNCTION check_action(p_mode varchar2,
200 p_assignment_id number) RETURN boolean
201 IS
202 l_proc CONSTANT VARCHAR2(50):= g_package||'check_action';
203 l_action number;
204 l_ret boolean;
205
206 cursor csr_check_action(l_mode1 varchar2, l_mode2 varchar2 , l_mode3 varchar2, l_mode4 varchar2) is --Changed for bug 9255173
207 select 1
208 from pay_payroll_actions pay,
209 pay_assignment_actions paa
210 where (pay.report_type like l_mode1
211 or
212 pay.report_type like l_mode2 -- Bug 6770200.
213 or
214 pay.report_type like l_mode3 -- Added for Version 6
215 or
216 pay.report_type like l_mode4)
217 and pay.action_status ='C'
218 and pay.report_qualifier = 'GB'
219 and pay.report_category = 'EDI'
220 and pay.payroll_action_id = paa.payroll_action_id
221 and paa.action_status = 'C'
222 and paa.assignment_id = p_assignment_id;
223
224 l_mode1 varchar2(15) ;
225 l_mode2 varchar2 (15) ;
226 l_mode3 varchar2 (15) ;
227 l_mode4 varchar2 (15) :='NO_VALUE' ; --For bug 9255173
228
229 BEGIN
230 l_ret := true;
231 hr_utility.set_location('Entering: '||l_proc,1);
232
233 -- Bug 6770200
234 -- Additional modes added. Like while checking for P45PT3,
235 -- also check if the employee has been picked by a P45PT3(new) or
236 -- P45(3) - pre 06-Apr-08 process. Similar checks done for P46 and Pennot
237 -- also.
238 -- Added Additional modes l_mode3 for P45PT_3_VER6 for Post 06-APR-08
239 if p_mode = 'P45PT_3' or p_mode = 'P45_3' or p_mode = 'P45PT_3_VER6' then
240 l_mode1 := 'P45_3' ;
241 l_mode2 := 'P45PT_3';
242 l_mode3 := 'P45PT_3_VER6';
243 elsif p_mode = 'P45PT1' or p_mode = 'P45' then
244 l_mode1 := 'P45' ;
245 l_mode2 := 'P45PT1' ;
246 l_mode3 := 'P45PT1_VER6';
247 elsif p_mode = 'P46_5' or p_mode = 'P46' or p_mode = 'P46_VER6'
248 or p_mode = 'P46_VER6ET' then --Added for bug 9255173
249 l_mode1 := 'P46' ;
250 l_mode2 := 'P46_5';
251 l_mode3 := 'P46_VER6';
252 l_mode4 := 'P46_VER6ET';
253 /* changes for P46_ver6_pennot starts **/
254 elsif p_mode = 'P46_PENNOT' or p_mode = 'P46_5_PENNOT' or p_mode= 'P46_VER6_PENNOT' then
255 l_mode1 := 'P46_PENNOT';
256 l_mode2 := 'P46_5_PENNOT' ;
257 l_mode3 := 'P46_VER6_PENNOT';
258 /* changes for P46_ver6_pennot ends **/
259
260 /*Changes for P46EXP_Ver6 starts*/
261 elsif p_mode = 'P46_EXPAT'
262 or p_mode = 'P46EXP_VER6ET' then --Added for bug 9255183
263 l_mode1 := 'P46_EXPAT';
264 l_mode2 := 'P46EXP_VER6ET';
265 l_mode3 := 'P46_EXPAT';
266 /*Changes for P46EXP_Ver6 End*/
267
268
269 else
270 l_mode1 := p_mode ;
271 l_mode2 := p_mode ;
272 l_mode3 := p_mode ;
273 end if ;
274
275 hr_utility.set_location('p_mode: '||p_mode,1);
276 hr_utility.set_location('l_mode1: '||l_mode1,1);
277 hr_utility.set_location('l_mode2: '||l_mode2,1);
278 hr_utility.set_location('l_mode3: '||l_mode3,1);
279 hr_utility.set_location('l_mode4: '||l_mode4,1);
280
281 open csr_check_action(l_mode1, l_mode2 , l_mode3, l_mode4);
282 fetch csr_check_action into l_action;
283 if csr_check_action%FOUND then
284 hr_utility.set_location('Assignment action complete',5);
285 l_ret := false;
286 end if;
287 close csr_check_action;
288 hr_utility.set_location('Leaving: '||l_proc,999);
289 return l_ret;
290 END;
291 --
292 --
293 PROCEDURE reset_flag(p_type varchar2,
294 p_assact number)
295 IS
296 l_proc CONSTANT VARCHAR2(50):= g_package||'reset_flag';
297 l_ovn number;
298
299 cursor csr_aei_details is
300 select aei.assignment_extra_info_id,
301 aei.object_version_number,
302 aei.aei_information1
303 from pay_assignment_actions paa,
304 per_assignment_extra_info aei
305 where paa.assignment_action_id = p_assact
306 and aei.assignment_id = paa.assignment_id
307 and aei.information_type = p_type;
308
309 l_aei_rec csr_aei_details%rowtype;
310 BEGIN
311 open csr_aei_details;
312 fetch csr_aei_details into l_aei_rec;
313 close csr_aei_details;
314
315 if l_aei_rec.aei_information1 = 'N' then
316 hr_assignment_extra_info_api.update_assignment_extra_info
317 (p_validate => false,
318 p_object_version_number => l_ovn,
319 p_assignment_extra_info_id => l_aei_rec.assignment_extra_info_id,
320 p_aei_information_category => p_type,
321 p_aei_information1 => 'Y');
322 end if;
323 END;
324 --
325 --
326 PROCEDURE internal_action_creation(pactid in number,
327 stperson in number,
328 endperson in number,
329 chunk in number,
330 p_info_type in varchar2,
331 p_rep_type in varchar2)
332 IS
333 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
334 l_payroll_id number;
335 l_business_group_id number;
336 l_ass_act_id number;
337 l_assignment_id number;
338 l_effective_date date;
339 l_arch boolean;
340 l_send_flag varchar2(2);
341 l_static_flag varchar2(2);
342 l_tax_ref varchar2(20);
343 /**** for p46_5 ***/
344 l_def_send_flag varchar2(2);
345 l_def_static_flag varchar2(2);
346 l_reason varchar2(2);
347 l_p45_not_run boolean;
348 l_p46_5_def number; /**** l_p46_5_def : 0 -> no default to be run; 1 -> default for P46_5 to be run; 2 -> default has been run,now the normal P46_5 *****/
349 l_locked_action_id number;
350 l_exist number;
351 cursor csr_parameter_info is
352 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
353 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
354 effective_date,
355 business_group_id
356 from pay_payroll_actions
357 where payroll_action_id = pactid;
358
359 -- Bug 8574855 : Added hint PER_ASSIGNMENTS_F_N12 for tuning the query.
360 cursor csr_asg is
361 select /*+ ordered index(ASG PER_ASSIGNMENTS_F_N12) */ asg.assignment_id
362 from per_all_people_f pap,
363 per_assignments_f asg,
364 per_periods_of_service serv,
365 pay_all_payrolls_f pay,
366 hr_soft_coding_keyflex sck
367 where pap.person_id between stperson and endperson
368 and pap.current_employee_flag = 'Y'
369 and pap.person_id = asg.person_id
370 and asg.business_group_id = l_business_group_id
371 and asg.payroll_id = pay.payroll_id
372 and asg.period_of_service_id = serv.period_of_service_id
373 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
374 and upper(l_tax_ref) = upper(sck.segment1)
375 and (l_payroll_id IS NULL
376 or
377 l_payroll_id = pay.payroll_id)
378 and serv.date_start <= l_effective_date
379 and l_effective_date between asg.effective_start_date and asg.effective_end_date
380 and l_effective_date between pap.effective_start_date and pap.effective_end_date
381 and l_effective_date between pay.effective_start_date and pay.effective_end_date;
382
383 cursor csr_aei_flag(p_assignment_id number) is
384 select aei_information1,
385 decode(p_info_type,'GB_P45_3', aei_information8
386 ,'GB_P46PENNOT', aei_information4
387 ,'GB_P46', aei_information3
388 ,'GB_P46EXP', aei_information3 ) --Added for P46EXP_Ver6 Changes
389 from per_assignment_extra_info
390 where assignment_id = p_assignment_id
391 and information_type = p_info_type;
392
393 cursor csr_p46_5_default(p_assignment_id number) is /*** open this cursor only for rep_type=P46_5 ***/
394 select aei_information5,
395 aei_information6
396 from per_assignment_extra_info
397 where assignment_id = p_assignment_id
398 and information_type = p_info_type;
399
400 cursor csr_p46_5_def_det(p_assignment_id number,default_archive varchar2)
401 is
402 select 1
403 from pay_action_information pa
404 ,pay_payroll_actions ppa
405 ,pay_assignment_actions paa
406 where pa.action_information_category in 'GB P46_5 EDI'
407 and pa.action_context_type = 'AAP'
408 and pa.action_information4 = default_archive
409 and pa.assignment_id = p_assignment_id
410 and paa.assignment_action_id = pa.action_context_id
411 and ppa.payroll_action_id = paa.payroll_action_id
412 and ppa.action_status = 'C';
413
414 cursor csr_p46_5_def_assact(p_assignment_id number) is
415 select act.assignment_action_id
416 from pay_payroll_actions pact,
417 pay_assignment_actions act
418 where pact.report_type = p_rep_type -- Changed to handle P46_VER6 also
419 and pact.action_status ='C'
420 and pact.report_qualifier = 'GB'
421 and pact.report_category = 'EDI'
422 and pact.payroll_action_id = act.payroll_action_id
423 and act.action_status = 'C'
424 and act.assignment_id = p_assignment_id;
425
426 BEGIN
427 hr_utility.set_location('Entering: '||l_proc,1);
428 open csr_parameter_info;
429 fetch csr_parameter_info into l_payroll_id,
430 l_tax_ref,
431 l_effective_date,
432 l_business_group_id;
433 close csr_parameter_info;
434
435 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
436 for asg_rec in csr_asg loop
437 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
438 l_arch := false;
439 l_p46_5_def := 0;
440 open csr_aei_flag(asg_rec.assignment_id);
441 fetch csr_aei_flag into l_send_flag, l_static_flag;
442
443 -- only create asg action if rows is found
444 if csr_aei_flag%FOUND then
445 hr_utility.set_location('\n l_send_flag = ' || l_send_flag || ' l_static_flag = ' || l_static_flag,20);
446
447 if p_rep_type = 'P46_5' then
448 l_p45_not_run := check_action('P45%3', asg_rec.assignment_id);
449 if l_p45_not_run then
450 open csr_p46_5_default(asg_rec.assignment_id);
451 fetch csr_p46_5_default into l_def_send_flag,l_def_static_flag;
452 close csr_p46_5_default;
453 /*** checking if the default is to be run *****/
454 if l_def_send_flag = 'Y' then
455 l_arch := true;
456 l_p46_5_def := 1;
457 else
458 if l_def_send_flag = 'N' and l_def_static_flag = 'Y' then
459 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
460 if l_arch then
461 l_p46_5_def := 1; /** if def not archived then archive ***/
462 else
463 l_p46_5_def := 0;
464 end if;
465 end if;
466 end if;
467 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def)||l_def_send_flag||l_def_static_flag||l_send_flag||l_static_flag||asg_rec.assignment_id);
468 /**** checking if the default has been run or not enabled to run ****/
469 if l_p46_5_def = 0 and nvl(l_def_send_flag,'N') = 'N' /*and nvl(l_def_static_flag,'Y') = 'Y'*/ then
470 if l_send_flag = 'Y' then
471 l_arch := true;
472 l_p46_5_def := 2; /**** diff b/n normal run for other report types and P46_5 normal run ***/
473 else
474 if l_send_flag = 'N' and l_static_flag = 'Y' then /** checking if P46 normal is rolled back ***/
475 fnd_file.put_line(fnd_file.LOG,'11111');
476 open csr_p46_5_def_det(asg_rec.assignment_id,'N');
477 fetch csr_p46_5_def_det into l_exist;
478 if csr_p46_5_def_det%notfound then
479 -- Bug 6770200
480 l_arch := check_action('P46_5', asg_rec.assignment_id);
481 end if;
482 close csr_p46_5_def_det;
483 if l_arch then
484 l_p46_5_def := 2;
485 end if;
486 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def));
487 end if;
488 end if;
489 end if;
490 /*else
491 l_reason := 'X'; */
492 end if;
493 else
494 if l_send_flag = 'Y' then
495 l_arch := true;
496 else
497 if l_send_flag = 'N' and l_static_flag = 'Y' then
498 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
499 end if;
500 end if;
501 end if;
502
503 /* EOY Changes for P46_VER6 Start*/
504 if p_rep_type = 'P46_VER6' then
505 l_p45_not_run := check_action('P45%3%', asg_rec.assignment_id); -- Changed to handle version 6 Reports also
506 if l_p45_not_run then
507 open csr_p46_5_default(asg_rec.assignment_id);
508 fetch csr_p46_5_default into l_def_send_flag,l_def_static_flag;
509 close csr_p46_5_default;
510 /*** checking if the default is to be run *****/
511 if l_def_send_flag = 'Y' then
512 l_arch := true;
513 l_p46_5_def := 1;
514 else
515 if l_def_send_flag = 'N' and l_def_static_flag = 'Y' then
516 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
517 if l_arch then
518 l_p46_5_def := 1; /** if def not archived then archive ***/
519 else
520 l_p46_5_def := 0;
521 end if;
522 end if;
523 end if;
524 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def)||l_def_send_flag||l_def_static_flag||l_send_flag||l_static_flag||asg_rec.assignment_id);
525 /**** checking if the default has been run or not enabled to run ****/
526 if l_p46_5_def = 0 and nvl(l_def_send_flag,'N') = 'N' /*and nvl(l_def_static_flag,'Y') = 'Y'*/ then
527 if l_send_flag = 'Y' then
528 l_arch := true;
529 l_p46_5_def := 2; /**** diff b/n normal run for other report types and P46_5 normal run ***/
530 else
531 if l_send_flag = 'N' and l_static_flag = 'Y' then /** checking if P46 normal is rolled back ***/
532 fnd_file.put_line(fnd_file.LOG,'11111');
533 open csr_p46_5_def_det(asg_rec.assignment_id,'N');
534 fetch csr_p46_5_def_det into l_exist;
535 if csr_p46_5_def_det%notfound then
536 -- Bug 6770200
537 l_arch := check_action('P46_VER6', asg_rec.assignment_id);
538 end if;
539 close csr_p46_5_def_det;
540 if l_arch then
541 l_p46_5_def := 2;
542 end if;
543 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def));
544 end if;
545 end if;
546 end if;
547 /*else
548 l_reason := 'X'; */
549 end if;
550 else
551 if l_send_flag = 'Y' then
552 l_arch := true;
553 else
554 if l_send_flag = 'N' and l_static_flag = 'Y' then
555 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
556 end if;
557 end if;
558 end if;
559 /*EOY Changes for P46_Ver6 End*/
560
561 --Added for bug 9255173
562 /* Changes for P46_VER6 eTextStart*/
563 if p_rep_type = 'P46_VER6ET' then
564 l_p45_not_run := check_action('P45%3%', asg_rec.assignment_id); -- Changed to handle version 6 Reports also
565 if l_p45_not_run then
566 open csr_p46_5_default(asg_rec.assignment_id);
567 fetch csr_p46_5_default into l_def_send_flag,l_def_static_flag;
568 close csr_p46_5_default;
569 /*** checking if the default is to be run *****/
570 if l_def_send_flag = 'Y' then
571 l_arch := true;
572 l_p46_5_def := 1;
573 else
574 if l_def_send_flag = 'N' and l_def_static_flag = 'Y' then
575 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
576 if l_arch then
577 l_p46_5_def := 1; /** if def not archived then archive ***/
578 else
579 l_p46_5_def := 0;
580 end if;
581 end if;
582 end if;
583 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def)||l_def_send_flag||l_def_static_flag||l_send_flag||l_static_flag||asg_rec.assignment_id);
584 /**** checking if the default has been run or not enabled to run ****/
585 if l_p46_5_def = 0 and nvl(l_def_send_flag,'N') = 'N' /*and nvl(l_def_static_flag,'Y') = 'Y'*/ then
586 if l_send_flag = 'Y' then
587 l_arch := true;
588 l_p46_5_def := 2; /**** diff b/n normal run for other report types and P46_5 normal run ***/
589 else
590 if l_send_flag = 'N' and l_static_flag = 'Y' then /** checking if P46 normal is rolled back ***/
591 fnd_file.put_line(fnd_file.LOG,'11111');
592 open csr_p46_5_def_det(asg_rec.assignment_id,'N');
593 fetch csr_p46_5_def_det into l_exist;
594 if csr_p46_5_def_det%notfound then
595 -- Bug 6770200
596 l_arch := check_action('P46_VER6ET', asg_rec.assignment_id);
597 end if;
598 close csr_p46_5_def_det;
599 if l_arch then
600 l_p46_5_def := 2;
601 end if;
602 fnd_file.put_line(fnd_file.LOG,to_char(l_p46_5_def));
603 end if;
604 end if;
605 end if;
606 /*else
607 l_reason := 'X'; */
608 end if;
609 else
610 if l_send_flag = 'Y' then
611 l_arch := true;
612 else
613 if l_send_flag = 'N' and l_static_flag = 'Y' then
614 l_arch := check_action(p_rep_type, asg_rec.assignment_id);
615 end if;
616 end if;
617 end if;
618 /*Changes for P46_Ver6 eText End*/
619
620 if l_arch then
621 hr_utility.set_location('Creating assignment action for ' || asg_rec.assignment_id,30);
622 select pay_assignment_actions_s.nextval
623 into l_ass_act_id
624 from dual;
625 --
626 -- insert into pay_assignment_actions.
627 hr_nonrun_asact.insact(l_ass_act_id,
628 asg_rec.assignment_id,
629 pactid,
630 chunk,
631 null);
632
633 if l_p46_5_def = 2 then
634 open csr_p46_5_def_assact(asg_rec.assignment_id);
635 fetch csr_p46_5_def_assact into l_locked_action_id;
636 if csr_p46_5_def_assact%NOTFOUND then /*** condition happens only when default is not run prior to normal P46 ****/
637 l_locked_action_id := -1;
638 end if;
639 close csr_p46_5_def_assact;
640
641 fnd_file.put_line(fnd_file.LOG,to_char(l_locked_action_id));
642 if l_locked_action_id > 0 then
643 delete pay_action_interlocks where locked_action_id = l_locked_action_id;
644 hr_nonrun_asact.insint(l_ass_act_id,l_locked_action_id);
645 end if;
646 end if;
647
648 end if;
649 end if;
650 close csr_aei_flag;
651
652 end loop;
653
654 hr_utility.set_location('Leaving: '||l_proc,999);
655 END internal_action_creation;
656 --
657 --
658 --
659 --
660 /*** EOY 07-08 ****/
661 FUNCTION fetch_45_46_pennot_rec(p_effective_date IN DATE,
662 p_tax_rec IN g_tax_rec,
663 p_person_rec IN act_info_rec,
664 p_info_type IN VARCHAR2,
665 p_assact_id IN NUMBER,
666 p_45_46_pennot_rec OUT nocopy act_info_rec) return boolean IS
667 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_45_46_pennot_rec';
668 l_assignment_id number;
669 l_ovn number;
670 l_arch boolean;
671
672
673 cursor csr_45_46_pennot_details is
674 select aei.assignment_extra_info_id,
675 aei.aei_information1 send_edi,
676 aei.aei_information2 annual_pension,
677 aei.aei_information3 date_pension_start,
678 aei.aei_information4 static_flag,
679 aei.aei_information5 prev_emp_paye_ref,
680 aei.aei_information6 date_left_prev_emp,
681 aei.aei_information7 prev_tax_code,
682 aei.aei_information8 prev_tax_basis,
683 aei.aei_information9 prev_last_pay_period_type,
684 aei.aei_information10 prev_last_pay_period,
685 aei.aei_information11 recently_bereaved,
686 aei.object_version_number
687 from per_assignment_extra_info aei
688 where aei.assignment_id = p_person_rec.assignment_id
689 and aei.information_type = p_info_type;
690
691
692
693 l_45_46_pennot_rec csr_45_46_pennot_details%rowtype;
694 BEGIN
695 hr_utility.set_location('Entering: '||l_proc,1);
696 l_arch := true;
697
698 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
699 open csr_45_46_pennot_details;
700 fetch csr_45_46_pennot_details into l_45_46_pennot_rec;
701 close csr_45_46_pennot_details;
702
703
704 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
705 --l_arch := false;
706 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
707 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
708 pay_core_utils.push_token('MAX_VALUE', '6 characters');
709 hr_utility.set_location('Tax Code error',20);
710 fnd_file.put_line(fnd_file.LOG,'l_arch3: ');
711
712 end if;
713 if length(ltrim(l_45_46_pennot_rec.prev_tax_code,'S')) > 6 then
714 --l_arch := false;
715 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
716 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax Code');
717 pay_core_utils.push_token('MAX_VALUE', '6 characters');
718 hr_utility.set_location('Prev Tax Code',40);
719 fnd_file.put_line(fnd_file.LOG,'l_arch4: ');
720 end if;
721
722 if not validate_data(substr(ltrim(substr(l_45_46_pennot_rec.prev_emp_paye_ref,4,8),'/'),1,7),'Previous Tax Reference','FULL_EDI') then
723 --l_arch := false;
724 hr_utility.set_location('Previous Tax Reference error',50);
725 fnd_file.put_line(fnd_file.LOG,'l_arch5: ');
726 end if;
727
728 if not validate_data(substr(l_45_46_pennot_rec.prev_emp_paye_ref,1,3),'Previous Tax District','FULL_EDI') then
729 --l_arch := false;
730 hr_utility.set_location('Previous Tax District error',60);
731 fnd_file.put_line(fnd_file.LOG,'l_arch6: ');
732 end if;
733
734 if not validate_data(p_tax_rec.prev_paid,'Previous Pay','FULL_EDI') then
735 -- l_arch := false;
736 hr_utility.set_location('Prev Pay Valiation',70);
737 fnd_file.put_line(fnd_file.LOG,'l_arch7: ');
738 end if;
739
740 if not validate_data(p_tax_rec.prev_tax,'Previous Tax','FULL_EDI') then
741 --l_arch := false;
742 hr_utility.set_location('Prev Tax Validation',80);
743 fnd_file.put_line(fnd_file.LOG,'l_arch8: ');
744 end if;
745
746 if not validate_data(l_45_46_pennot_rec.prev_last_pay_period,'Previous Last Payment Period','FULL_EDI') then
747 --l_arch := false;
748 hr_utility.set_location('Previous period error',90);
749 fnd_file.put_line(fnd_file.LOG,'l_arch9: ');
750 end if;
751
752 l_ovn := l_45_46_pennot_rec.object_version_number;
753 if l_arch then
754 hr_utility.set_location('Clear Flag',20);
755 hr_assignment_extra_info_api.update_assignment_extra_info
756 (p_validate => false,
757 p_object_version_number => l_ovn,
758 p_assignment_extra_info_id => l_45_46_pennot_rec.assignment_extra_info_id,
759 p_aei_information_category => 'GB_P46PENNOT',
760 p_aei_information1 => 'N');
761 end if;
762
763 if p_info_type = 'GB_P46PENNOT' then
764 p_45_46_pennot_rec.action_info_category := 'GB P46 PENNOT EDI';
765 end if;
766
767 p_45_46_pennot_rec.assignment_id := p_person_rec.assignment_id;
768 p_45_46_pennot_rec.effective_date := p_effective_date;
769 p_45_46_pennot_rec.act_info1 := l_ovn;
770 p_45_46_pennot_rec.act_info2 := trim(l_45_46_pennot_rec.annual_pension);
771 p_45_46_pennot_rec.act_info3 := l_45_46_pennot_rec.date_pension_start;
772 p_45_46_pennot_rec.act_info4 := l_45_46_pennot_rec.prev_emp_paye_ref;
773 p_45_46_pennot_rec.act_info5 := l_45_46_pennot_rec.date_left_prev_emp;
774 p_45_46_pennot_rec.act_info6 := l_45_46_pennot_rec.prev_tax_code;
775 p_45_46_pennot_rec.act_info7 := l_45_46_pennot_rec.prev_tax_basis;
776 p_45_46_pennot_rec.act_info8 := l_45_46_pennot_rec.prev_last_pay_period_type;
777 p_45_46_pennot_rec.act_info9 := l_45_46_pennot_rec.prev_last_pay_period;
778 p_45_46_pennot_rec.act_info10 := l_45_46_pennot_rec.recently_bereaved;
779 p_45_46_pennot_rec.act_info11 := p_tax_rec.prev_paid;
780 p_45_46_pennot_rec.act_info12 := p_tax_rec.prev_tax;
781
782 hr_utility.set_location('Leaving: '||l_proc,999);
783 return l_arch;
784
785
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 fnd_file.put_line(fnd_file.LOG,'2435*****');
790 return false;
791 END fetch_45_46_pennot_rec;
792 --
793
794 PROCEDURE fetch_tax_rec(p_assactid IN NUMBER,
795 p_effective_date IN DATE,
796 p_tax_rec OUT nocopy g_tax_rec) IS
797
798 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_tax_rec';
799 l_paye_id number;
800 l_paye_details_id number;
801 l_paye_rr_id number;
802 l_paye_details_rr_id number;
803 l_assignment_id number;
804 l_element_id number;
805 l_asg_start date;
806 l_asg_end date;
807
808 cursor csr_element_id(p_name varchar2) is
809 select element_type_id
810 from pay_element_types_f
811 where element_name = p_name
812 and legislation_code = 'GB';
813
814 cursor csr_assignment_details is
815 select /*+ ORDERED */
816 asg.assignment_id,
817 asg.effective_start_date,
818 asg.effective_end_date
819 from pay_assignment_actions paa,
820 per_assignments_f asg
821 where paa.assignment_action_id = p_assactid
822 and paa.assignment_id = asg.assignment_id
823 and p_effective_date between asg.effective_start_date and asg.effective_end_date;
824
825 -- Bug 6643668
826 -- Tax data should be picked only from element entry values.
827 -- Earlier code was checking run results first, then if values not found
828 -- there, then it was going for element entry values.
829 /*
830 cursor csr_max_run_result(p_element_id number) is
831 select /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
832 pact PAY_PAYROLL_ACTIONS_PK,
833 r2 PAY_RUN_RESULTS_N50)
834 USE_NL(assact2, pact, r2) */
835 /* to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type|| r2.run_result_id),17))
836 from pay_assignment_actions assact2,
837 pay_payroll_actions pact,
838 pay_run_results r2
839 where assact2.assignment_id = l_assignment_id
840 and r2.element_type_id+0 = p_element_id
841 and r2.assignment_action_id = assact2.assignment_action_id
842 and r2.status IN ('P', 'PA')
843 and pact.payroll_action_id = assact2.payroll_action_id
844 and pact.action_type IN ( 'Q','R','B','I')
845 and assact2.action_status = 'C'
846 and pact.effective_date between l_asg_start and l_asg_end
847 and not exists(
848 select '1'
849 from pay_action_interlocks pai,
850 pay_assignment_actions assact3,
851 pay_payroll_actions pact3
852 where pai.locked_action_id = assact2.assignment_action_id
853 and pai.locking_action_id = assact3.assignment_action_id
854 and pact3.payroll_action_id = assact3.payroll_action_id
855 and pact3.action_type = 'V'
856 and assact3.action_status = 'C');
857
858 cursor csr_run_result(l_run_result_id number,l_element_type_id number) is
859 select max(decode(name,'Tax Code',result_value,NULL)) tax_code,
860 max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
861 to_number(max(decode(name,'Pay Previous',
862 fnd_number.canonical_to_number(result_value),NULL))) pay_previous,
863 to_number(max(decode(name,'Tax Previous',
864 fnd_number.canonical_to_number(result_value),NULL))) tax_previous
865 from pay_input_values_f v,
866 pay_run_result_values rrv
867 where rrv.run_result_id = l_run_result_id
868 and v.input_value_id = rrv.input_value_id
869 and v.element_type_id = l_element_type_id;
870 */
871
872 cursor csr_paye_details is
873 select max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
874 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
875 max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
876 max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
877 from pay_element_entries_f e,
878 pay_element_entry_values_f v,
879 pay_input_values_f iv,
880 pay_element_links_f link
881 where e.assignment_id = l_assignment_id
882 and link.element_type_id = l_paye_details_id
883 and e.element_link_id = link.element_link_id
884 and e.element_entry_id = v.element_entry_id
885 and iv.input_value_id = v.input_value_id
886 and p_effective_date between e.effective_start_date and e.effective_end_date -- 5660011
887 and p_effective_date between v.effective_start_date and v.effective_end_date
888 and p_effective_date between link.effective_start_date and link.effective_end_date
889 and e.effective_end_date between link.effective_start_date and link.effective_end_date
890 and e.effective_end_date between iv.effective_start_date and iv.effective_end_date
891 and e.effective_end_date between v.effective_start_date and v.effective_end_date ;
892 -- Bug 6643668 - this check is not reuqired
893 /*and e.effective_end_date = (select max(e1.effective_end_date)
894 from pay_element_entries_f e1,
895 pay_element_links_f link1
896 where link1.element_type_id = l_paye_details_id
897 and e1.assignment_id = l_assignment_id
898 and e1.element_link_id = link1.element_link_id);
899 */
900
901 BEGIN
902 hr_utility.set_location('Entering: '||l_proc,1);
903
904
905
906 open csr_element_id('PAYE');
907 fetch csr_element_id into l_paye_id;
908 close csr_element_id;
909
910 open csr_element_id('PAYE Details');
911 fetch csr_element_id into l_paye_details_id;
912 close csr_element_id;
913
914 open csr_assignment_details;
915 fetch csr_assignment_details into l_assignment_id,
916 l_asg_start,
917 l_asg_end;
918 close csr_assignment_details;
919
920 -- Bug 6643668
921 -- Tax data should be picked only from element entry values.
922 -- Earlier code was checking run results first, then if values not found
923 -- there, then it was going for element entry values.
924 /*
925 open csr_max_run_result(l_paye_id);
926 fetch csr_max_run_result into l_paye_rr_id;
927 close csr_max_run_result;
928
929 open csr_max_run_result(l_paye_details_id);
930 fetch csr_max_run_result into l_paye_details_rr_id;
931 close csr_max_run_result;
932
933 open csr_run_result(l_paye_rr_id, l_paye_id);
934 fetch csr_run_result into p_tax_rec.tax_code,
935 p_tax_rec.tax_basis,
936 p_tax_rec.prev_paid,
937 p_tax_rec.prev_tax;
938 close csr_run_result;
939 -- if Tax code is not found, fetch from the latest PAYE Details run results
940
941 -- Bug 5660011
942 if ( p_tax_rec.prev_tax is null and p_tax_rec.prev_paid is null ) or
943 ( p_tax_rec.prev_tax = 0 and p_tax_rec.prev_paid = 0 ) then
944 */
945 open csr_paye_details;
946 fetch csr_paye_details into p_tax_rec.tax_code,
947 p_tax_rec.tax_basis,
948 p_tax_rec.prev_paid,
949 p_tax_rec.prev_tax;
950 close csr_paye_details;
951 /* -- Bug 6643668 continued
952 end if;
953
954 if p_tax_rec.tax_code is null then
955 open csr_run_result(l_paye_details_rr_id, l_paye_details_id);
956 fetch csr_run_result into p_tax_rec.tax_code,
957 p_tax_rec.tax_basis,
958 p_tax_rec.prev_paid,
959 p_tax_rec.prev_tax;
960 close csr_run_result;
961
962 -- 3. Still not found, fetch the value from the PAYE
963 if p_tax_rec.tax_code is null then
964 hr_utility.trace('Fetching run result 3');
965 open csr_paye_details;
966 fetch csr_paye_details into p_tax_rec.tax_code,
967 p_tax_rec.tax_basis,
968 p_tax_rec.prev_paid,
969 p_tax_rec.prev_tax;
970 close csr_paye_details;
971 end if;
972 end if;
973 */
974 hr_utility.set_location('Leaving: '||l_proc,999);
975 END fetch_tax_rec;
976 --
977 --
978 FUNCTION fetch_address_rec(p_person_id IN NUMBER,
979 p_assignment_id IN NUMBER,
980 p_effective_date IN DATE,
981 p_addr_rec OUT nocopy act_info_rec) return boolean IS
982 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_address_rec';
983 l_arch boolean;
984 l_temp varchar2(200);
985 cursor csr_address is
986 select upper(substr(addr.address_line1,1,35)) addr1,
987 upper(substr(addr.address_line2,1,35)) addr2,
988 upper(substr(addr.address_line3,1,35)) addr3,
989 upper(hr_general.decode_lookup('GB_COUNTY', substr(addr.region_1,1,35))) county,
990 addr.postal_code post_code,
991 upper(addr.town_or_city) town_or_city,
992 upper(addr.country) country
993 from per_addresses addr
994 where addr.person_id(+) = p_person_id
995 and ( addr.primary_flag = 'Y'
996 or addr.primary_flag is null)
997 and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
998 and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
999 l_addr_rec csr_address%rowtype;
1000 BEGIN
1001 hr_utility.set_location('Entering: '||l_proc,1);
1002 l_arch := true;
1003
1004 open csr_address;
1005 fetch csr_address into l_addr_rec;
1006 close csr_address;
1007
1008 l_temp := l_addr_rec.addr1 || ' ' || l_addr_rec.addr2 ||
1009 l_addr_rec.addr3 || ' ' || l_addr_rec.town_or_city ||
1010 l_addr_rec.county;
1011
1012 --For bugs 9255173 and 9255183
1013 --Following validations are not required for eText reports here
1014 --as they have been moved to a different procedure
1015 IF g_archive_type <> 'P46_VER6ET' AND g_archive_type <> 'P46EXP_VER6ET' THEN
1016 if l_addr_rec.addr1 is null then
1017 pay_core_utils.push_message(800, 'HR_78088_MISSING_DATA_ERR', 'F');
1018 pay_core_utils.push_token('TOKEN', 'Address');
1019 l_arch := false;
1020 hr_utility.set_location('Address missing',10);
1021 end if;
1022
1023 if not validate_data(l_temp,'Address','P14_FULL_EDI') then
1024 l_arch := false;
1025 hr_utility.set_location('Address Validation',20);
1026 end if;
1027
1028 if not validate_data(l_addr_rec.post_code,'Post Code','FULL_EDI') then
1029 l_arch := false;
1030 hr_utility.set_location('Post Code error',20);
1031 end if;
1032 END IF;
1033
1034 p_addr_rec.assignment_id := p_assignment_id;
1035 p_addr_rec.effective_date := p_effective_date;
1036 p_addr_rec.action_info_category := 'ADDRESS DETAILS';
1037 p_addr_rec.act_info5 := l_addr_rec.addr1;
1038 p_addr_rec.act_info6 := l_addr_rec.addr2;
1039 p_addr_rec.act_info7 := l_addr_rec.addr3;
1040 p_addr_rec.act_info8 := l_addr_rec.town_or_city;
1041 p_addr_rec.act_info9 := l_addr_rec.county;
1042 p_addr_rec.act_info12 := l_addr_rec.post_code;
1043 p_addr_rec.act_info13 := l_addr_rec.country;
1044
1045 hr_utility.set_location('Leaving: '||l_proc,999);
1046 return l_arch;
1047 END fetch_address_rec;
1048 --
1049 --
1050 --
1051 --
1052 -- Function to fetch country name for the given country code
1053 --
1054
1055 FUNCTION get_territory_short_name(prm_name in varchar2)
1056 return varchar2 is
1057 --
1058 -- Cursor to fetch country name for the country code
1059 --
1060 Cursor csr_territory_short_name (p_code varchar2) is
1061 select territory_short_name
1062 from fnd_territories_vl
1063 where territory_code = p_code;
1064
1065 l_code varchar2(200);
1066 BEGIN
1067 open csr_territory_short_name(prm_name);
1068 fetch csr_territory_short_name into l_code;
1069 close csr_territory_short_name;
1070
1071 return l_code;
1072
1073 END get_territory_short_name;
1074 --
1075 --
1076 --
1077 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
1078 p_effective_date IN DATE,
1079 p_tax_rec IN g_tax_rec,
1080 p_person_rec OUT nocopy act_info_rec) return boolean IS
1081
1082 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
1083 l_job varchar2(70);
1084 l_person_id number;
1085 l_arch boolean;
1086 l_temp varchar2(30);
1087
1088 cursor csr_person_details is
1089 select /*+ ORDERED */
1090 pap.person_id,
1091 paa.assignment_id,
1092 pap.title,
1093 pap.first_name,
1094 pap.last_name,
1095 pap.middle_names,
1096 paa.ASSIGNMENT_NUMBER,
1097 pap.national_identifier,
1098 pap.sex,
1099 fnd_date.date_to_canonical(pap.date_of_birth) date_of_birth,
1100 fnd_date.date_to_canonical(decode(pap.current_employee_flag, 'Y', serv.date_start, null)) hire_date
1101 from pay_assignment_actions act,
1102 per_assignments_f paa,
1103 per_people_f pap,
1104 per_periods_of_service serv
1105 where act.assignment_action_id = p_assactid
1106 and act.assignment_id = paa.assignment_id
1107 and paa.person_id = pap.person_id
1108 and paa.period_of_service_id = serv.period_of_service_id
1109 and serv.date_start <= p_effective_date
1110 and p_effective_date between paa.effective_start_date and paa.effective_end_date
1111 and p_effective_date between pap.effective_start_date and pap.effective_end_date;
1112
1113 cursor csr_job is
1114 select pay_get_job_segment_pkg.get_job_segment(paa.business_group_id,job.job_definition_id,act.payroll_action_id) job
1115 from pay_assignment_actions act,
1116 per_assignments_f paa,
1117 per_jobs job
1118 where act.assignment_action_id = p_assactid
1119 and act.assignment_id = paa.assignment_id
1120 and paa.job_id = job.job_id(+)
1121 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
1122
1123 l_person_rec csr_person_details%rowtype;
1124 BEGIN
1125 hr_utility.set_location('Entering: '||l_proc,1);
1126 l_arch := true;
1127
1128 open csr_person_details;
1129 fetch csr_person_details into l_person_rec;
1130 close csr_person_details;
1131
1132 open csr_job;
1133 fetch csr_job into l_job;
1134 close csr_job;
1135
1136 --For bugs 9255173 and 9255183
1137 --Following validations are not required for eText reports here
1138 --as they have been moved to a different procedure
1139 IF g_archive_type <> 'P46_VER6ET' AND g_archive_type <> 'P46EXP_VER6ET' THEN
1140
1141 if not validate_data(l_person_rec.first_name,'First Name','EDI_SURNAME') then
1142 l_arch := false;
1143 hr_utility.set_location('First Name error',10);
1144 end if;
1145
1146 if not validate_data(l_person_rec.last_name,'Last Name','EDI_SURNAME') then
1147 l_arch := false;
1148 hr_utility.set_location('Last Name error',20);
1149 end if;
1150
1151 if not validate_data(l_person_rec.assignment_number,'Assignment Number','FULL_EDI') then
1152 l_arch := false;
1153 hr_utility.set_location('Assignment Number error',30);
1154 end if;
1155
1156 if not validate_data(l_person_rec.sex,'Sex','FULL_EDI') then
1157 l_arch := false;
1158 hr_utility.set_location('Sex error',40);
1159 end if;
1160
1161 if not validate_data(l_job,'Job Title','P14_FULL_EDI') then -- Bug 8315067
1162 l_arch := false;
1163 hr_utility.set_location('Job Title error',50);
1164 end if;
1165
1166 if l_person_rec.national_identifier is not null and
1167 hr_gb_utility.ni_validate(l_person_rec.national_identifier,sysdate) <> 0 then
1168 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1169 pay_core_utils.push_token('INPUT_NAME', 'NI Number');
1170 pay_core_utils.push_token('INPUT_VALUE', l_person_rec.national_identifier);
1171 l_arch := false;
1172 hr_utility.set_location('NI error',60);
1173 end if;
1174 END IF;
1175
1176 /** -- NO Tax code validation yet as it is different between P45(3),P46 and P46P --**
1177 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,l_person_rec.assignment_id);
1178 if l_temp <> ' ' then
1179 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1180 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
1181 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1182 l_arch := false;
1183 hr_utility.set_location('Tax Code error',30);
1184 end if;
1185 */
1186 p_person_rec.person_id := l_person_rec.person_id;
1187 p_person_rec.assignment_id := l_person_rec.assignment_id;
1188 p_person_rec.effective_date := p_effective_date;
1189 p_person_rec.action_info_category := 'GB EMPLOYEE DETAILS';
1190 p_person_rec.act_info6 := l_person_rec.first_name;
1191 p_person_rec.act_info7 := l_person_rec.middle_names;
1192 p_person_rec.act_info8 := l_person_rec.last_name;
1193 p_person_rec.act_info11 := l_person_rec.assignment_number;
1194 p_person_rec.act_info12 := l_person_rec.national_identifier;
1195 p_person_rec.act_info14 := l_person_rec.title;
1196 p_person_rec.act_info15 := l_person_rec.date_of_birth;
1197 p_person_rec.act_info16 := l_person_rec.hire_date;
1198 p_person_rec.act_info17 := l_person_rec.sex;
1199 p_person_rec.act_info18 := l_job;
1200 p_person_rec.act_info21 := p_tax_rec.tax_code;
1201 p_person_rec.act_info22 := p_tax_rec.tax_basis;
1202 hr_utility.set_location('Leaving: '||l_proc,999);
1203 return l_arch;
1204 END fetch_person_rec;
1205 --
1206 --
1207 FUNCTION fetch_p45_3_rec(p_effective_date IN DATE,
1208 p_tax_rec IN g_tax_rec,
1209 p_person_rec IN act_info_rec,
1210 p_p45_3_rec OUT nocopy act_info_rec) return boolean IS
1211 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p45_3_rec';
1212 l_assignment_id number;
1213 l_ovn number;
1214 l_arch boolean;
1215 l_temp varchar2(30);
1216
1217 cursor csr_p45_3_details is
1218 select aei.assignment_extra_info_id,
1219 aei.aei_information1 send_edi,
1220 aei.aei_information2 prev_tax_district,
1221 aei.aei_information3 date_left,
1222 aei.aei_information4 prev_tax_code,
1223 aei.aei_information5 prev_tax_basis,
1224 aei.aei_information6 prev_period_type,
1225 aei.aei_information7 prev_period,
1226 aei.aei_information8 static_flag,
1227 /*changes for P45PT_3 start*/
1228 aei.aei_information9 prev_tax_paid_notified,
1229 aei.aei_information10 not_paid_between_start_and5apr,
1230 aei.aei_information11 continue_sl_deductions,
1231 /*changes for P45PT_3 start*/
1232 --Bug 6994632 fetching Prev Tax Pay Notified value
1233 aei.aei_information12 prev_tax_pay_notified,
1234 aei.object_version_number
1235 from per_assignment_extra_info aei
1236 where aei.assignment_id = p_person_rec.assignment_id
1237 and aei.information_type = 'GB_P45_3';
1238
1239 cursor csr_student_loan is
1240 select nvl(min(decode(inv.name, 'Start Date', eev.screen_entry_value, 'X')),'X') s_date,
1241 nvl(min(decode(inv.name, 'End Date', eev.screen_entry_value, null)),'4712/12/31 00:00:00') e_date,
1242 fnd_date.date_to_canonical(min(decode(inv.name, 'End Date', eev.effective_end_date, fnd_date.canonical_to_date('4712/12/31 00:00:00')))) eff_date
1243 from pay_element_types_f elt,
1244 pay_element_entries_f ele,
1245 pay_input_values_f inv,
1246 pay_element_entry_values_f eev
1247 where elt.element_name = 'Student Loan'
1248 and ele.element_type_id = elt.element_type_id
1249 and ele.assignment_id = p_person_rec.assignment_id
1250 and inv.element_type_id = elt.element_type_id
1251 and eev.input_value_id + 0 = inv.input_value_id
1252 and eev.element_entry_id = ele.element_entry_id -- Bug 5469122
1253 and p_effective_date between elt.effective_start_date and elt.effective_end_date
1254 and p_effective_date between ele.effective_start_date and ele.effective_end_date
1255 and p_effective_date between inv.effective_start_date and inv.effective_end_date
1256 and p_effective_date between eev.effective_start_date and eev.effective_end_date;
1257
1258 l_p45_3_rec csr_p45_3_details%rowtype;
1259 l_student_rec csr_student_loan%rowtype;
1260 BEGIN
1261 hr_utility.set_location('Entering: '||l_proc,1);
1262 l_arch := true;
1263
1264 open csr_p45_3_details;
1265 fetch csr_p45_3_details into l_p45_3_rec;
1266 close csr_p45_3_details;
1267
1268 open csr_student_loan;
1269 fetch csr_student_loan into l_student_rec;
1270 close csr_student_loan;
1271
1272 if l_p45_3_rec.date_left is null then
1273 pay_core_utils.push_message(800, 'HR_78088_MISSING_DATA_ERR', 'F');
1274 pay_core_utils.push_token('TOKEN', 'Date Left Previous Employer');
1275 l_arch := false;
1276 hr_utility.set_location('Date Left null',30);
1277 end if;
1278
1279 if to_number(p_tax_rec.prev_paid) > 999999.99 then
1280 l_arch := false;
1281 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1282 pay_core_utils.push_token('ITEM_NAME', 'Previous Pay');
1283 pay_core_utils.push_token('MAX_VALUE', '999999.99');
1284 hr_utility.set_location('Prev Paid > 999999.99',10);
1285 end if;
1286
1287 if to_number(p_tax_rec.prev_tax) > 999999.99 then
1288 l_arch := false;
1289 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1290 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax');
1291 pay_core_utils.push_token('MAX_VALUE', '999999.99');
1292 hr_utility.set_location('Prev Tax > 999999.99',20);
1293 end if;
1294
1295 /** -- Validate using the orignal, will use new one when requirement comes out --
1296 l_temp := hr_gb_utility.tax_code_validate(l_p45_3_rec.prev_tax_code,sysdate,p_person_rec.assignment_id);
1297 if l_temp <> ' ' then
1298 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1299 pay_core_utils.push_token('INPUT_NAME', 'Previous Tax Code');
1300 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1301 l_arch := false;
1302 hr_utility.set_location('Tax Code error',30);
1303 end if;
1304 */
1305 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
1306 l_arch := false;
1307 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1308 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
1309 pay_core_utils.push_token('MAX_VALUE', '6 characters');
1310 hr_utility.set_location('Tax Code error',30);
1311 end if;
1312
1313 if length(ltrim(l_p45_3_rec.prev_tax_code,'S')) > 6 then
1314 l_arch := false;
1315 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
1316 pay_core_utils.push_token('ITEM_NAME', 'Previous Tax Code');
1317 pay_core_utils.push_token('MAX_VALUE', '6 characters');
1318 hr_utility.set_location('Prev Tax Code',40);
1319 end if;
1320
1321 if not validate_data(substr(ltrim(substr(l_p45_3_rec.prev_tax_district,4,8),'/'),1,7),'Previous Tax Reference','FULL_EDI') then
1322 l_arch := false;
1323 hr_utility.set_location('Previous Tax Reference error',50);
1324 end if;
1325
1326 if not validate_data(substr(l_p45_3_rec.prev_tax_district,1,3),'Previous Tax District','FULL_EDI') then
1327 l_arch := false;
1328 hr_utility.set_location('Previous Tax District error',60);
1329 end if;
1330
1331 if not validate_data(p_tax_rec.prev_paid,'Previous Pay','FULL_EDI') then
1332 l_arch := false;
1333 hr_utility.set_location('Prev Pay Valiation',70);
1334 end if;
1335
1336 if not validate_data(p_tax_rec.prev_tax,'Previous Tax','FULL_EDI') then
1337 l_arch := false;
1338 hr_utility.set_location('Prev Tax Validation',80);
1339 end if;
1340
1341 if not validate_data(l_p45_3_rec.prev_period,'Previous Last Payment Period','FULL_EDI') then
1342 l_arch := false;
1343 hr_utility.set_location('Previous period error',90);
1344 end if;
1345
1346 l_ovn := l_p45_3_rec.object_version_number;
1347 if l_arch then
1348 hr_utility.set_location('Clear Flag',100);
1349 hr_assignment_extra_info_api.update_assignment_extra_info
1350 (p_validate => false,
1351 p_object_version_number => l_ovn,
1352 p_assignment_extra_info_id => l_p45_3_rec.assignment_extra_info_id,
1353 p_aei_information_category => 'GB_P45_3',
1354 p_aei_information1 => 'N');
1355 end if;
1356
1357 p_p45_3_rec.assignment_id := p_person_rec.assignment_id;
1358 p_p45_3_rec.effective_date := p_effective_date;
1359 p_p45_3_rec.action_info_category := 'GB P45(3) EDI';
1360 p_p45_3_rec.act_info1 := l_ovn;
1361 p_p45_3_rec.act_info2 := l_p45_3_rec.prev_tax_district;
1362 p_p45_3_rec.act_info3 := l_p45_3_rec.date_left;
1363 p_p45_3_rec.act_info4 := l_p45_3_rec.prev_tax_code;
1364 p_p45_3_rec.act_info5 := l_p45_3_rec.prev_tax_basis;
1365 p_p45_3_rec.act_info6 := l_p45_3_rec.prev_period_type;
1366 p_p45_3_rec.act_info7 := l_p45_3_rec.prev_period;
1367 p_p45_3_rec.act_info8 := p_tax_rec.prev_paid;
1368 p_p45_3_rec.act_info9 := p_tax_rec.prev_tax;
1369 p_p45_3_rec.act_info10:= l_student_rec.s_date;
1370 p_p45_3_rec.act_info11:= l_student_rec.e_date;
1371 p_p45_3_rec.act_info12:= l_student_rec.eff_date;
1372 /*changes for P45PT_3 start*/
1373 p_p45_3_rec.act_info13:= l_p45_3_rec.prev_tax_paid_notified;
1374 p_p45_3_rec.act_info14:= l_p45_3_rec.not_paid_between_start_and5apr;
1375 p_p45_3_rec.act_info15:= l_p45_3_rec.continue_sl_deductions;
1376 /*changes for P45PT_3 end*/
1377 -- Bug 6994632 passing Prev Tax Pay Notified value to archive function
1378 p_p45_3_rec.act_info16:= l_p45_3_rec.prev_tax_pay_notified;
1379
1380 hr_utility.set_location('Leaving: '||l_proc,999);
1381 return l_arch;
1382 END fetch_p45_3_rec;
1383 --
1384 --
1385 FUNCTION fetch_p46_rec(p_effective_date IN DATE,
1386 p_tax_rec IN g_tax_rec,
1387 p_person_rec IN act_info_rec,
1388 p_p46_rec OUT nocopy act_info_rec) return boolean IS
1389 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46_rec';
1390 l_assignment_id number;
1391 l_ovn number;
1392 l_arch boolean;
1393 l_temp varchar2(50);
1394
1395 cursor csr_p46_details is
1396 select aei.assignment_extra_info_id,
1397 aei.aei_information1 send_edi,
1398 aei.aei_information2 p46_statement,
1399 aei.aei_information3 static_flag,
1400 aei.aei_information4 student_loan,
1401 aei.object_version_number
1402 from per_assignment_extra_info aei
1403 where aei.assignment_id = p_person_rec.assignment_id
1404 and aei.information_type = 'GB_P46';
1405
1406 l_p46_rec csr_p46_details%rowtype;
1407 BEGIN
1408 hr_utility.set_location('Entering: '||l_proc,1);
1409 l_arch := true;
1410
1411 open csr_p46_details;
1412 fetch csr_p46_details into l_p46_rec;
1413 close csr_p46_details;
1414
1415 if not validate_data(l_p46_rec.p46_statement,'P46 Statement','FULL_EDI') then
1416 l_arch := false;
1417 hr_utility.set_location('P46 Statement validation',10);
1418 end if;
1419
1420 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,p_person_rec.assignment_id);
1421 if l_temp <> ' ' then
1422 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1423 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
1424 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1425 l_arch := false;
1426 hr_utility.set_location('Tax Code error',20);
1427 end if;
1428
1429 l_ovn := l_p46_rec.object_version_number;
1430 if l_arch then
1431 hr_utility.set_location('Clear Flag',30);
1432 hr_assignment_extra_info_api.update_assignment_extra_info
1433 (p_validate => false,
1434 p_object_version_number => l_ovn,
1435 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
1436 p_aei_information_category => 'GB_P46',
1437 p_aei_information1 => 'N');
1438 end if;
1439
1440 p_p46_rec.assignment_id := p_person_rec.assignment_id;
1441 p_p46_rec.effective_date := p_effective_date;
1442 p_p46_rec.action_info_category := 'GB P46 EDI';
1443 p_p46_rec.act_info1 := l_ovn;
1444 p_p46_rec.act_info2 := l_p46_rec.p46_statement;
1445 p_p46_rec.act_info3 := l_p46_rec.student_loan;
1446
1447 hr_utility.set_location('Leaving: '||l_proc,999);
1448 return l_arch;
1449 END fetch_p46_rec;
1450 --
1451
1452 /*Changes for P46EXP_Ver6 starts*/
1453 FUNCTION fetch_p46exp_rec(p_effective_date IN DATE,
1454 p_tax_rec IN g_tax_rec,
1455 p_person_rec IN act_info_rec,
1456 p_p46_rec OUT nocopy act_info_rec) return boolean IS
1457 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46exp_rec';
1458 l_assignment_id number;
1459 l_ovn number;
1460 l_arch boolean;
1461 l_temp varchar2(50);
1462
1463 cursor csr_p46_details is
1464 select aei.assignment_extra_info_id,
1465 aei.aei_information1 send_edi,
1466 aei.aei_information2 p46_statement,
1467 aei.aei_information3 static_flag,
1468 aei.aei_information4 student_loan,
1469 aei.object_version_number,
1470 aei.aei_information5 eea_cw_citizen,
1471 aei.aei_information6 em6_scheme,
1472 aei.aei_information7 date_started_uk
1473 from per_assignment_extra_info aei
1474 where aei.assignment_id = p_person_rec.assignment_id
1475 and aei.information_type = 'GB_P46EXP';
1476
1477 l_p46_rec csr_p46_details%rowtype;
1478 BEGIN
1479 hr_utility.set_location('Entering: '||l_proc,1);
1480 l_arch := true;
1481
1482 open csr_p46_details;
1483 fetch csr_p46_details into l_p46_rec;
1484 close csr_p46_details;
1485
1486 if not validate_data(l_p46_rec.p46_statement,'P46(Expat) Statement','FULL_EDI') then
1487 l_arch := false;
1488 hr_utility.set_location('P46(Expat) Statement validation',10);
1489 end if;
1490
1491 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,p_person_rec.assignment_id);
1492 /* if l_temp <> ' ' then
1493 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
1494 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
1495 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
1496 l_arch := false;
1497 hr_utility.set_location('Tax Code error',20);
1498 end if;*/
1499
1500 l_ovn := l_p46_rec.object_version_number;
1501 if l_arch then
1502 hr_utility.set_location('Clear Flag',30);
1503 hr_assignment_extra_info_api.update_assignment_extra_info
1504 (p_validate => false,
1505 p_object_version_number => l_ovn,
1506 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
1507 p_aei_information_category => 'GB_P46EXP',
1508 p_aei_information1 => 'N');
1509 end if;
1510
1511 p_p46_rec.assignment_id := p_person_rec.assignment_id;
1512 p_p46_rec.effective_date := p_effective_date;
1513 p_p46_rec.action_info_category := 'GB P46EXP EDI';
1514 p_p46_rec.act_info1 := l_ovn;
1515 p_p46_rec.act_info2 := l_p46_rec.p46_statement;
1516 p_p46_rec.act_info3 := l_p46_rec.student_loan;
1517 p_p46_rec.act_info4 := l_p46_rec.eea_cw_citizen;
1518 p_p46_rec.act_info5 := l_p46_rec.date_started_uk;
1519 p_p46_rec.act_info6 := l_p46_rec.em6_scheme;
1520
1521 hr_utility.set_location('Leaving: '||l_proc,999);
1522 return l_arch;
1523 END fetch_p46exp_rec;
1524
1525 /*Changes for P46EXP_Ver6 End*/
1526
1527 --For bugs 9255173 and 9255183
1528 --This procedure is used to collect all the eText report validation failures
1529 --they would then be written to the o/p file
1530 PROCEDURE populate_run_msg(
1531 p45_assignment_action_id IN NUMBER
1532 ,p_message_text IN varchar2
1533 )
1534 IS
1535 PRAGMA AUTONOMOUS_TRANSACTION;
1536 BEGIN
1537 hr_utility.set_location(' Entering: populate_run_msg',111);
1538
1539 INSERT INTO pay_message_lines(line_sequence,
1540 payroll_id,
1541 message_level,
1542 source_id,
1543 source_type,
1544 line_text)
1545 VALUES(
1546 pay_message_lines_s.nextval
1547 ,null
1548 ,'F'
1549 ,p45_assignment_action_id
1550 ,'A'
1551 ,substr(p_message_text,1,240)
1552 );
1553
1554 hr_utility.set_location(' Leaving: populate_run_msg',999);
1555 COMMIT;
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 hr_utility.trace('Error occured in populate_run_msg');
1559 RAISE;
1560 END populate_run_msg;
1561
1562
1563 --For bug 9255183
1564 --This procedure implements P46Expat formula validations
1565 PROCEDURE p46exp_asg_etext_validations(p_assactid IN NUMBER,
1566 p_effective_date IN DATE,
1567 p_tab_rec_data IN action_info_table,
1568 edi_validation_fail out nocopy varchar2) IS
1569
1570 CURSOR get_effective_date IS
1571 SELECT ppa.effective_date
1572 FROM pay_payroll_actions ppa, pay_assignment_actions paa
1573 WHERE ppa.payroll_action_id = paa.payroll_action_id
1574 AND paa.assignment_action_id = p_assactid;
1575
1576 l_proc CONSTANT VARCHAR2(50):= g_package||'p46exp_asg_etext_validations';
1577 l_ovn number;
1578 l_action_id number;
1579 --edi_validation_fail varchar2(50);
1580
1581 l_sex per_people_f.sex%TYPE;
1582 l_assignment_number per_assignments_f.assignment_number%TYPE;
1583 l_date_of_birth varchar2(100);
1584 l_tax_code_in_use varchar2(100);
1585 l_tax_basis_in_use varchar2(100);
1586 l_msg_value varchar2(100);
1587 l_eff_date date;
1588
1589 l_p46_expat_statement per_assignment_extra_info.aei_information2%TYPE;
1590 l_p46_expat_start_empl_date per_assignment_extra_info.aei_information7%TYPE;
1591 l_p46_expat_eea_citizen per_assignment_extra_info.aei_information5%TYPE;
1592
1593 BEGIN
1594 l_sex := p_tab_rec_data(0).act_info17;
1595 hr_utility.set_location('Etext41 l_sex'||l_sex,111);
1596 l_assignment_number := p_tab_rec_data(0).act_info11;
1597 hr_utility.set_location('Etext41 l_assignment_number'||l_assignment_number,111);
1598
1599 l_date_of_birth := p_tab_rec_data(0).act_info15;
1600 hr_utility.set_location('Etext41 l_date_of_birth'||l_date_of_birth,111);
1601 l_tax_code_in_use := p_tab_rec_data(0).act_info21;
1602 l_tax_basis_in_use := p_tab_rec_data(0).act_info22;
1603 hr_utility.set_location('Etext42'||l_tax_basis_in_use,111);
1604
1605 l_p46_expat_eea_citizen := p_tab_rec_data(2).act_info4;
1606 l_p46_expat_start_empl_date := p_tab_rec_data(2).act_info5;
1607 l_p46_expat_statement := p_tab_rec_data(2).act_info2;
1608
1609 hr_utility.set_location('Etext43'||l_p46_expat_statement,111);
1610
1611 IF l_p46_expat_eea_citizen = 'Y' AND (l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL) THEN
1612 populate_run_msg(p_assactid,'You have not entered a tax code for the EEA Citizen ' || l_assignment_number || '. Ensure you enter Emergency Tax Code on a Cumulative Basis.');
1613 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : You have not entered a tax code for the EEA Citizen ' || l_assignment_number || '. Ensure you enter Emergency Tax Code on a Cumulative Basis.');
1614 edi_validation_fail := 'Y';
1615 END IF;
1616
1617 IF l_p46_expat_eea_citizen = 'Y' and (l_tax_code_in_use <> ' ' OR l_tax_code_in_use IS NOT NULL) and l_tax_basis_in_use = 'N' THEN
1618 populate_run_msg(p_assactid,' The tax basis cannot be Week1/Month1 for an EEA Citizen ' || l_assignment_number || ' .');
1619 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The tax basis cannot be Week1/Month1 for an EEA Citizen ' || l_assignment_number || ' .');
1620 edi_validation_fail := 'Y';
1621 END IF;
1622
1623 IF l_p46_expat_start_empl_date = ' ' OR l_p46_expat_start_empl_date IS NULL THEN
1624 populate_run_msg(p_assactid,' The start date of employment in UK (P46EXPAT) for the assignment ' || l_assignment_number || ' is blank. Enter a valid start date.');
1625 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The start date of employment in UK (P46EXPAT) for the assignment ' || l_assignment_number || ' is blank. Enter a valid start date.');
1626 edi_validation_fail := 'Y';
1627 ELSIF PAY_GB_MOVDED_EDI.date_validate(p_assactid,'UK_EMPL_DATE',to_date(l_p46_expat_start_empl_date,'YYYY/MM/DD HH24:MI:SS')) = 0 THEN
1628 populate_run_msg(p_assactid,' The start date of employment in UK (P46EXPAT) for the assignment ' || l_assignment_number || ' is invalid.');
1629 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The start date of employment in UK (P46EXPAT) for the assignment ' || l_assignment_number || ' is blank. Enter a valid start date.');
1630 edi_validation_fail := 'Y';
1631 END IF;
1632
1633 IF l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL THEN
1634 populate_run_msg(p_assactid,' The Tax Code in use of the assignment ' || l_assignment_number || ' is missing.');
1635 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Code in use of the assignment ' || l_assignment_number || ' is missing.');
1636 edi_validation_fail := 'Y';
1637 END IF;
1638
1639 IF ((l_assignment_number <> ' ' OR l_assignment_number IS NOT NULL)
1640 and pay_gb_eoy_magtape.validate_input(l_assignment_number,'P14_FULL_EDI') > 0) THEN
1641 populate_run_msg(p_assactid,' Assignment Number has invalid character(s).' || l_assignment_number);
1642 fnd_file.put_line (fnd_file.LOG,' : The Assignment Number has invalid character(s). ' || l_assignment_number);
1643 edi_validation_fail := 'Y';
1644 END IF;
1645
1646 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
1647 populate_run_msg(p_assactid,' Sex is undefined for the assignment.' || l_assignment_number);
1648 fnd_file.put_line (fnd_file.LOG,' : The sex ' || l_sex || ' is undefined for the assignment' || l_assignment_number);
1649 edi_validation_fail := 'Y';
1650 END IF;
1651
1652 IF l_date_of_birth = ' ' OR l_date_of_birth IS NULL THEN
1653 hr_utility.set_location('Etext43 l_date_of_birth'||l_date_of_birth,111);
1654 populate_run_msg(p_assactid,' The Date of Birth of the assignment ' || l_assignment_number || ' is missing.');
1655 fnd_file.put_line (fnd_file.LOG,' : The Date of Birth of the assignment '|| l_assignment_number || ' is missing.');
1656 edi_validation_fail := 'Y';
1657 END IF;
1658
1659 IF l_p46_expat_statement = ' ' OR l_p46_expat_statement IS NULL OR (l_p46_expat_statement <> 'A' AND l_p46_expat_statement <> 'B' AND l_p46_expat_statement <> 'C') THEN
1660 populate_run_msg(p_assactid,' P46EXPAT statement for the assignment ' || l_assignment_number || ' is invalid. The P46EXPAT statement must be A, B or C.');
1661 fnd_file.put_line (fnd_file.LOG,' : P46EXPAT statement for the assignment ' || l_assignment_number || ' is invalid. The P46EXPAT statement must be A, B or C.');
1662 edi_validation_fail := 'Y';
1663 END IF;
1664
1665 OPEN get_effective_date;
1666 FETCH get_effective_date
1667 INTO l_eff_date;
1668 CLOSE get_effective_date;
1669
1670 --Modifications for the bug fix 10409668 starts here
1671 --l_eff_date := to_date(to_char(to_date(l_eff_date,'RRRR/MM/DD HH24:MI:SS'),'RRRR/MM/DD'),'RRRR/MM/DD');
1672 l_eff_date := trunc (l_eff_date);
1673 hr_utility.trace('l_eff_date = '|| l_eff_date);
1674 --Modifications for the bug fix 10409668 ends here
1675
1676 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code_in_use,l_eff_date);
1677
1678 IF (l_tax_code_in_use <> ' ' OR l_tax_code_in_use IS NOT NULL)
1679 AND (l_msg_value <> ' ') THEN
1680 populate_run_msg(p_assactid, l_msg_value || 'tax code, ' || l_tax_code_in_use || ', for assignment ' || l_assignment_number);
1681 fnd_file.put_line (fnd_file.LOG,' : The ' || l_msg_value || ':tax code, ' || l_tax_code_in_use || ', for assignment ' || l_assignment_number);
1682 edi_validation_fail := 'Y';
1683 END IF;
1684
1685
1686 IF (l_tax_code_in_use <>' ' OR l_tax_code_in_use IS NOT NULL) AND (l_tax_basis_in_use = ' ' OR l_tax_basis_in_use IS NULL)THEN
1687 populate_run_msg(p_assactid, 'The Tax Basis in use is not present for Tax code in use, for assignment ' || l_assignment_number);
1688 fnd_file.put_line (fnd_file.LOG,' : The Tax Basis in use is not present for Tax code in use, for assignment ' || l_assignment_number);
1689 edi_validation_fail := 'Y';
1690 END IF;
1691 hr_utility.set_location('Etext44 Leaving',111);
1692
1693 END p46exp_asg_etext_validations;
1694
1695 --For bugs 9255173 and 9255183
1696 --This procedure implements validations of formula PAY_GB_EDI_MOVDED6_ASG
1697 PROCEDURE movded6_asg_etext_validations(p_assactid IN NUMBER,
1698 p_effective_date IN DATE,
1699 p_tab_rec_data IN action_info_table,
1700 p_archive_type IN varchar2 default null, --Added for the bug fix 10409668
1701 edi_validation_fail out nocopy varchar2) IS
1702 l_proc CONSTANT VARCHAR2(50):= g_package||'p46exp_asg_etext_validations';
1703 l_ovn number;
1704 l_action_id number;
1705 --edi_validation_fail varchar2(50);
1706
1707
1708 l_national_insurance_number per_people_f.national_identifier%TYPE;
1709 l_assignment_number per_assignments_f.assignment_number%TYPE;
1710 l_address_line1 per_addresses.address_line1%TYPE;
1711 l_address_line2 per_addresses.address_line2%TYPE;
1712 l_address_line3 per_addresses.address_line3%TYPE;
1713 l_address_line4 per_addresses.address_line3%TYPE;
1714 l_town_or_city per_addresses.town_or_city%TYPE;
1715 l_county per_addresses.region_1%TYPE;
1716 l_postal_code per_addresses.postal_code%TYPE;
1717 l_last_name per_people_f.last_name%TYPE;
1718 l_first_name per_people_f.first_name%TYPE;
1719 l_middle_name per_people_f.middle_names%TYPE;
1720 l_title per_people_f.title%TYPE;
1721 l_job_title varchar(100);
1722 l_tax_code varchar(100);
1723 l_tax_basis varchar(10);
1724 l_assignment_id number;
1725 l_national_identifier per_people_f.national_identifier%TYPE;
1726
1727 l_session_date date;
1728 l_count_char_errors number;
1729 l_count_missing_val number;
1730
1731 /* Added below declarations and cursors for the bug fix: 10095492 */
1732 /* Modifications start here */
1733
1734 l_default_p46 varchar2(100);
1735 l_p46_statement varchar2(100);
1736 l_effective_date date;
1737 l_start_date date;
1738 l_end_date date;
1739
1740 Cursor emp_address_line is
1741 select nvl(upper(substr(addr.action_information5,1,35)),' '),
1742 nvl(upper(substr(addr.action_information6,1,35)),' '),
1743 nvl(upper(substr(addr.action_information7,1,35)),' '),
1744 nvl(upper(addr.action_information8),' ')
1745 from pay_action_information addr
1746 where addr.action_context_id = p_assactid
1747 and addr.action_information_category = 'ADDRESS DETAILS'
1748 and addr.action_context_type = 'AAP';
1749
1750 Cursor get_effective_date is
1751 select ppa.effective_date
1752 from pay_payroll_actions ppa, pay_assignment_actions paa
1753 where paa.payroll_action_id = ppa.payroll_action_id
1754 and paa.assignment_action_id = p_assactid;
1755
1756 /* Modifications end here */
1757
1758 BEGIN
1759 hr_utility.set_location('Etext50',111);
1760
1761 l_count_char_errors := 0;
1762 l_count_missing_val := 0;
1763
1764 l_national_insurance_number := p_tab_rec_data(0).act_info12;
1765 l_assignment_number := p_tab_rec_data(0).act_info11;
1766 l_address_line1 := p_tab_rec_data(1).act_info5;
1767 l_address_line2 := p_tab_rec_data(1).act_info6;
1768 l_address_line3 := p_tab_rec_data(1).act_info7;
1769 l_town_or_city := p_tab_rec_data(1).act_info8;
1770 l_county := p_tab_rec_data(1).act_info9;
1771 l_postal_code := p_tab_rec_data(1).act_info12;
1772 l_last_name := p_tab_rec_data(0).act_info8;
1773 l_first_name := p_tab_rec_data(0).act_info6;
1774 l_middle_name := p_tab_rec_data(0).act_info7;
1775 l_title := p_tab_rec_data(0).act_info14;
1776 l_job_title := p_tab_rec_data(0).act_info18;
1777 l_tax_code := p_tab_rec_data(0).act_info21;
1778 l_tax_basis := p_tab_rec_data(0).act_info22;
1779 l_national_identifier := p_tab_rec_data(0).act_info12;
1780
1781 l_assignment_id := p_tab_rec_data(0).assignment_id;
1782
1783 /* Modifications for the bug 10095492 starts here */
1784
1785 l_default_p46 := p_tab_rec_data(2).act_info4;
1786 l_p46_statement := p_tab_rec_data(2).act_info2;
1787 l_start_date := to_date('06-04-2011','DD-MM-YYYY');
1788 l_end_date := to_date('31-12-4712','DD-MM-YYYY');
1789 l_address_line4 := l_town_or_city; /*Added for the bug fix 13402234*/
1790
1791 open emp_address_line;
1792 fetch emp_address_line into l_address_line1, l_address_line2, l_address_line3, l_address_line4;
1793 close emp_address_line;
1794
1795 if l_address_line3 = ' '
1796 then
1797 l_address_line3 := l_address_line4;
1798 l_address_line4 := ' ';
1799 end if;
1800
1801 if l_address_line2 = ' '
1802 then
1803 l_address_line2 := l_address_line3;
1804 l_address_line3 := l_address_line4;
1805 l_address_line4 := ' ';
1806 end if;
1807
1808 if LENGTH(TRIM(l_address_line4)) > 0
1809 then
1810 l_address_line4 := l_address_line4;
1811 else
1812 l_address_line4 := ' ';
1813 end if;
1814
1815 if LENGTH(TRIM(l_address_line3)) > 0
1816 then
1817 l_address_line3 := l_address_line3;
1818 else
1819 l_address_line3 := l_address_line4;
1820 l_address_line4 := ' ';
1821 end if;
1822
1823 if LENGTH(TRIM(l_address_line2)) > 0
1824 then
1825 l_address_line2 := l_address_line2;
1826 else
1827 l_address_line2 := l_address_line3;
1828 l_address_line3 := l_address_line4;
1829 l_address_line4 := ' ';
1830 end if;
1831
1832 ---
1833
1834 open get_effective_date;
1835 fetch get_effective_date into l_effective_date;
1836 close get_effective_date;
1837
1838 IF p_archive_type = 'P46_VER6ET' THEN --Added for the bug fix 10409668
1839
1840 if (trunc(l_effective_date) between l_start_date and l_end_date) then
1841 if l_p46_statement ='C' and l_tax_code <> ' ' then
1842 if l_tax_code <>'BR' and l_tax_code <>'0T' then
1843 populate_run_msg(p_assactid,'The tax code for assignment '|| l_assignment_number ||', must be either BR or 0T.');
1844 fnd_file.put_line (fnd_file.LOG,' : The tax code for assignment '|| l_assignment_number ||', must be either BR or 0T.');
1845 edi_validation_fail := 'Y';
1846 l_count_char_errors := l_count_char_errors + 1;
1847 end if;
1848 end if;
1849
1850 if l_default_p46 = 'Y' and l_tax_code <> ' ' then
1851 if l_tax_code <>'0T' then
1852 populate_run_msg(p_assactid,'The tax code for assignment '|| l_assignment_number ||', must be 0T.');
1853 fnd_file.put_line (fnd_file.LOG,' : The tax code for assignment '|| l_assignment_number ||', must be 0T.');
1854 edi_validation_fail := 'Y';
1855 l_count_char_errors := l_count_char_errors + 1;
1856 end if;
1857 if l_tax_basis <> 'N' then
1858 populate_run_msg(p_assactid,'The tax basis for assignment '|| l_assignment_number ||', must be Non Cumulative.');
1859 fnd_file.put_line (fnd_file.LOG,' : The tax basis for assignment '|| l_assignment_number ||', must be Non Cumulative.');
1860 edi_validation_fail := 'Y';
1861 l_count_char_errors := l_count_char_errors + 1;
1862 end if;
1863 end if;
1864 end if;
1865
1866 END IF; --Added for the bug fix 10409668
1867
1868 /* Modifications for the bug 10095492 ends here */
1869
1870 IF l_national_insurance_number <> ' ' THEN
1871 l_session_date := HR_GBNICAR.NICAR_SESSION_DATE(0);
1872 IF hr_gb_utility.ni_validate(l_national_insurance_number,l_session_date)<>0 THEN
1873 populate_run_msg(p_assactid,'The National Insurance Number ' || l_national_insurance_number || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1874 fnd_file.put_line (fnd_file.LOG,' : The National Insurance Number ' || l_national_insurance_number || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1875 edi_validation_fail := 'Y';
1876 l_count_char_errors := l_count_char_errors + 1;
1877 END IF;
1878 END IF;
1879
1880 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
1881 populate_run_msg(p_assactid,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
1882 fnd_file.put_line (fnd_file.LOG,' : The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
1883 edi_validation_fail := 'Y';
1884 l_count_missing_val := l_count_missing_val + 1;
1885 ELSIF pay_gb_eoy_magtape.validate_input(l_address_line1,'P14_FULL_EDI') > 0 THEN
1886 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1887 fnd_file.put_line (fnd_file.LOG,' : The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1888 edi_validation_fail := 'Y';
1889 l_count_char_errors := l_count_char_errors + 1;
1890 END IF;
1891
1892 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
1893 populate_run_msg(p_assactid,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
1894 fnd_file.put_line (fnd_file.LOG,' : The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
1895 edi_validation_fail := 'Y';
1896 l_count_missing_val := l_count_missing_val + 1;
1897 ELSIF pay_gb_eoy_magtape.validate_input(l_address_line2,'P14_FULL_EDI') > 0 THEN
1898 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid character(s).');
1899 fnd_file.put_line (fnd_file.LOG,' : The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid character(s).');
1900 edi_validation_fail := 'Y';
1901 l_count_char_errors := l_count_char_errors + 1;
1902 END IF;
1903
1904 IF l_address_line3 <> ' ' OR l_address_line3 IS NOT NULL THEN
1905 IF pay_gb_eoy_magtape.validate_input(l_address_line3,'P14_FULL_EDI') > 0 THEN
1906 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1907 fnd_file.put_line (fnd_file.LOG,' : The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1908 edi_validation_fail := 'Y';
1909 l_count_char_errors := l_count_char_errors + 1;
1910 END IF;
1911 END IF;
1912
1913 IF l_town_or_city <> ' ' OR l_town_or_city IS NOT NULL THEN
1914 IF pay_gb_eoy_magtape.validate_input(l_town_or_city,'P14_FULL_EDI') > 0 THEN
1915 populate_run_msg(p_assactid,'The Town Or City ' || l_town_or_city || ' of the assignment '|| l_assignment_number || ' has invalid character(s).');
1916 fnd_file.put_line (fnd_file.LOG,' : The Town Or City ' || l_town_or_city ||' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1917 edi_validation_fail := 'Y';
1918 l_count_char_errors := l_count_char_errors + 1;
1919 END IF;
1920 END IF;
1921
1922 IF l_county <> ' ' OR l_county IS NOT NULL THEN
1923 IF pay_gb_eoy_magtape.validate_input(l_county,'P14_FULL_EDI') > 0 THEN
1924 populate_run_msg(p_assactid,'The County ' || l_county || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1925 fnd_file.put_line (fnd_file.LOG,' : The County ' || l_county || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1926 edi_validation_fail := 'Y';
1927 l_count_char_errors := l_count_char_errors + 1;
1928 END IF;
1929 END IF;
1930
1931 IF l_last_name = ' ' OR per_formula_functions.isnull(l_last_name)='Y' THEN
1932 populate_run_msg(p_assactid,'The Last Name of the assignment ' || l_assignment_number || ' is missing.');
1933 fnd_file.put_line (fnd_file.LOG,' : The Last Name of the assignment '|| l_assignment_number || ' is missing.');
1934 edi_validation_fail := 'Y';
1935 l_count_missing_val := l_count_missing_val + 1;
1936 elsif pay_gb_eoy_magtape.validate_input(l_last_name,'P45_46_LAST_NAME') > 0 then
1937 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1938 fnd_file.put_line (fnd_file.LOG,' : The Last Name ' + l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1939 edi_validation_fail := 'Y';
1940 l_count_char_errors := l_count_char_errors + 1;
1941 END IF;
1942
1943 IF l_first_name = ' ' OR per_formula_functions.isnull(l_first_name)='Y' THEN
1944 populate_run_msg(p_assactid,'The First Name of the assignment '|| l_assignment_number || ' is missing.');
1945 fnd_file.put_line (fnd_file.LOG,' : The First Name of the assignment ' || l_assignment_number || ' is missing.');
1946 edi_validation_fail := 'Y';
1947 l_count_missing_val := l_count_missing_val + 1;
1948
1949 ELSIF pay_gb_eoy_magtape.validate_input(l_first_name,'P45_46_FIRST_NAME') > 0 THEN
1950 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1951 fnd_file.put_line (fnd_file.LOG,' : The First Name ' || l_first_name || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1952 edi_validation_fail := 'Y';
1953 l_count_char_errors := l_count_char_errors + 1;
1954 END IF;
1955
1956 IF l_middle_name <> ' ' AND per_formula_functions.isnull(l_middle_name)<>'Y' THEN
1957 IF pay_gb_eoy_magtape.validate_input(l_middle_name,'P45_46_FIRST_NAME') > 0 THEN
1958 populate_run_msg(p_assactid,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1959 fnd_file.put_line (fnd_file.LOG,' : The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1960 edi_validation_fail := 'Y';
1961 l_count_char_errors := l_count_char_errors + 1;
1962 END IF;
1963 END IF;
1964
1965 IF l_title <> ' ' AND per_formula_functions.isnull(l_title)<>'Y' THEN
1966 IF pay_gb_eoy_magtape.validate_input(l_title,'P45_46_TITLE') > 0 THEN
1967 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1968 fnd_file.put_line (fnd_file.LOG,' : The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1969 edi_validation_fail := 'Y';
1970 l_count_char_errors := l_count_char_errors + 1;
1971 END IF;
1972 END IF;
1973
1974 IF l_postal_code <> ' ' OR l_postal_code IS NOT NULL THEN
1975 IF pay_gb_eoy_magtape.validate_input(l_postal_code,'P45_46_POSTCODE') > 0 THEN
1976 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1977 fnd_file.put_line (fnd_file.LOG,' : The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1978 edi_validation_fail := 'Y';
1979 l_count_char_errors := l_count_char_errors + 1;
1980 END IF;
1981 END IF;
1982
1983 -- below validations are from fetch_person_rec
1984 IF l_assignment_number <> ' ' OR l_assignment_number IS NOT NULL THEN
1985 IF pay_gb_eoy_magtape.validate_input(upper(l_assignment_number),'FULL_EDI') > 0 THEN --Added 'upper' to fix the bug 9503248
1986 populate_run_msg(p_assactid,'The Assignment Number ' || l_assignment_number || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1987 fnd_file.put_line (fnd_file.LOG,' : The Assignment Number ' || l_assignment_number || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1988 edi_validation_fail := 'Y';
1989 l_count_char_errors := l_count_char_errors + 1;
1990 END IF;
1991 END IF;
1992
1993 IF l_job_title <> ' ' OR l_job_title IS NOT NULL THEN
1994 IF pay_gb_eoy_magtape.validate_input(l_job_title,'P14_FULL_EDI') > 0 THEN
1995 populate_run_msg(p_assactid,'The Job Title ' || l_job_title || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1996 fnd_file.put_line (fnd_file.LOG,' : The Job Title ' || l_job_title || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
1997 edi_validation_fail := 'Y';
1998 END IF;
1999 END IF;
2000
2001 IF l_national_identifier is not null AND
2002 hr_gb_utility.ni_validate(l_national_identifier,sysdate) <> 0 THEN
2003 populate_run_msg(p_assactid,'The National Identifier ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2004 fnd_file.put_line (fnd_file.LOG,' : The National Identifier ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2005 edi_validation_fail := 'Y';
2006 END IF;
2007
2008 /* 10095492 - Commented out below check inorder to make etext validations in sync with other in-year filing reports */
2009 /*
2010 IF (hr_gb_utility.tax_code_validate(l_tax_code,sysdate,l_assignment_id) <> ' ') THEN
2011 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2012 fnd_file.put_line (fnd_file.LOG,' : The Tax Code ' || l_tax_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2013 edi_validation_fail := 'Y';
2014 END IF;
2015 */
2016
2017 --Added below validation for the bug fix 10409668
2018 --This is same as the above validation, but here we are passing l_effective_date instead of sysdate
2019 IF (hr_gb_utility.tax_code_validate(l_tax_code,l_effective_date,l_assignment_id) <> ' ') THEN
2020 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2021 fnd_file.put_line (fnd_file.LOG,' : The Tax Code ' || l_tax_code || ' of the assignment ' || l_assignment_number || ' has invalid character(s).');
2022 edi_validation_fail := 'Y';
2023 END IF;
2024
2025 hr_utility.set_location('Etext50 Leaving',111);
2026 END movded6_asg_etext_validations;
2027
2028
2029 FUNCTION fetch_p46_5_rec(p_effective_date IN DATE,
2030 p_tax_rec IN g_tax_rec,
2031 p_person_rec IN act_info_rec,
2032 p_p46_rec OUT nocopy act_info_rec) return boolean IS
2033 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46_rec';
2034 l_assignment_id number;
2035 l_ovn number;
2036 l_arch boolean;
2037 l_temp varchar2(50);
2038 l_def_archive varchar2(2);
2039 l_exist number;
2040
2041 cursor csr_p46_details is
2042 select aei.assignment_extra_info_id,
2043 aei.aei_information1 send_edi,
2044 aei.aei_information2 p46_statement,
2045 aei.aei_information3 static_flag,
2046 aei.aei_information4 student_loan,
2047 aei.aei_information5 default_send_edi,
2048 aei.aei_information6 default_static_edi,
2049 aei.object_version_number
2050 from per_assignment_extra_info aei
2051 where aei.assignment_id = p_person_rec.assignment_id
2052 and aei.information_type = 'GB_P46';
2053
2054 cursor csr_p46_5_def_det
2055 is
2056 select 1
2057 from pay_action_information pa
2058 ,pay_payroll_actions ppa
2059 ,pay_assignment_actions paa
2060 where pa.action_information_category = 'GB P46_5 EDI'
2061 and pa.action_context_type = 'AAP'
2062 and pa.action_information4 = 'Y'
2063 and pa.assignment_id = p_person_rec.assignment_id
2064 and paa.assignment_action_id = pa.action_context_id
2065 and ppa.payroll_action_id = paa.payroll_action_id
2066 and ppa.action_status = 'C';
2067
2068
2069 l_p46_rec csr_p46_details%rowtype;
2070 BEGIN
2071 hr_utility.set_location('Entering: '||l_proc,1);
2072 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
2073 l_arch := true;
2074
2075 open csr_p46_details;
2076 fetch csr_p46_details into l_p46_rec;
2077 close csr_p46_details;
2078 l_def_archive := 'N';
2079
2080 if l_p46_rec.default_send_edi = 'Y' then
2081 l_def_archive := 'Y';
2082 else
2083 if l_p46_rec.default_send_edi = 'N' and l_p46_rec.default_static_edi = 'Y' then
2084 open csr_p46_5_def_det;
2085 fetch csr_p46_5_def_det into l_exist;
2086 if csr_p46_5_def_det%found then
2087 l_def_archive := 'N';
2088 else
2089 l_def_archive := 'Y';
2090 end if;
2091 close csr_p46_5_def_det;
2092 /*else
2093 l_def_archive := 'N';*/
2094 end if;
2095 end if;
2096
2097 /* if not validate_data(l_p46_rec.p46_statement,'P46 Statement','FULL_EDI') then
2098 l_arch := false;
2099 hr_utility.set_location('P46 Statement validation',10);
2100 end if;
2101
2102 l_temp := hr_gb_utility.tax_code_validate(p_tax_rec.tax_code,sysdate,p_person_rec.assignment_id);
2103 if l_temp <> ' ' then
2104 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
2105 pay_core_utils.push_token('INPUT_NAME', 'Tax Code');
2106 pay_core_utils.push_token('INPUT_VALUE', p_tax_rec.tax_code);
2107 l_arch := false;
2108 hr_utility.set_location('Tax Code error',20);
2109 end if;*/
2110
2111 l_ovn := l_p46_rec.object_version_number;
2112 if l_arch then
2113 hr_utility.set_location('Clear Flag',30);
2114 if l_def_archive = 'N' then
2115 hr_assignment_extra_info_api.update_assignment_extra_info
2116 (p_validate => false,
2117 p_object_version_number => l_ovn,
2118 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
2119 p_aei_information_category => 'GB_P46',
2120 p_aei_information1 => 'N');
2121 else
2122 hr_assignment_extra_info_api.update_assignment_extra_info
2123 (p_validate => false,
2124 p_object_version_number => l_ovn,
2125 p_assignment_extra_info_id => l_p46_rec.assignment_extra_info_id,
2126 p_aei_information_category => 'GB_P46',
2127 p_aei_information5 => 'N');
2128
2129 end if;
2130 end if;
2131
2132 p_p46_rec.assignment_id := p_person_rec.assignment_id;
2133 p_p46_rec.effective_date := p_effective_date;
2134 p_p46_rec.action_info_category := 'GB P46_5 EDI';
2135 p_p46_rec.act_info1 := l_ovn;
2136 p_p46_rec.act_info2 := l_p46_rec.p46_statement;
2137 p_p46_rec.act_info3 := l_p46_rec.student_loan;
2138 p_p46_rec.act_info4 := l_def_archive;
2139 hr_utility.set_location('Leaving: '||l_proc,999);
2140 fnd_file.put_line(fnd_file.LOG,'Leaving: '||l_proc);
2141 return l_arch;
2142 END fetch_p46_5_rec;
2143 --
2144 FUNCTION fetch_p46p_rec(p_effective_date IN DATE,
2145 p_tax_rec IN g_tax_rec,
2146 p_person_rec IN act_info_rec,
2147 p_p46p_rec OUT nocopy act_info_rec) return boolean IS
2148 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_p46p_rec';
2149 l_assignment_id number;
2150 l_ovn number;
2151 l_arch boolean;
2152
2153 cursor csr_p46p_details is
2154 select aei.assignment_extra_info_id,
2155 aei.aei_information1 send_edi,
2156 aei.aei_information2 annual_pension,
2157 aei.aei_information3 date_pension_start,
2158 aei.aei_information4 static_flag,
2159 aei.object_version_number
2160 from per_assignment_extra_info aei
2161 where aei.assignment_id = p_person_rec.assignment_id
2162 and aei.information_type = 'GB_P46PENNOT';
2163
2164 l_p46p_rec csr_p46p_details%rowtype;
2165 BEGIN
2166 hr_utility.set_location('Entering: '||l_proc,1);
2167 l_arch := true;
2168
2169 open csr_p46p_details;
2170 fetch csr_p46p_details into l_p46p_rec;
2171 close csr_p46p_details;
2172
2173 if not validate_data(l_p46p_rec.annual_pension,'Annual Pension','FULL_EDI') then
2174 l_arch := false;
2175 hr_utility.set_location('Annaul Pension',10);
2176 end if;
2177
2178 if length(ltrim(p_tax_rec.tax_code,'S')) > 6 then
2179 l_arch := false;
2180 pay_core_utils.push_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX', 'F');
2181 pay_core_utils.push_token('ITEM_NAME', 'Tax Code');
2182 pay_core_utils.push_token('MAX_VALUE', '6 characters');
2183 hr_utility.set_location('Tax Code error',20);
2184 end if;
2185
2186 l_ovn := l_p46p_rec.object_version_number;
2187 if l_arch then
2188 hr_utility.set_location('Clear Flag',20);
2189 hr_assignment_extra_info_api.update_assignment_extra_info
2190 (p_validate => false,
2191 p_object_version_number => l_ovn,
2192 p_assignment_extra_info_id => l_p46p_rec.assignment_extra_info_id,
2193 p_aei_information_category => 'GB_P46PENNOT',
2194 p_aei_information1 => 'N');
2195 end if;
2196
2197 p_p46p_rec.assignment_id := p_person_rec.assignment_id;
2198 p_p46p_rec.effective_date := p_effective_date;
2199 p_p46p_rec.action_info_category := 'GB P46 Pension EDI';
2200 p_p46p_rec.act_info1 := l_ovn;
2201 p_p46p_rec.act_info2 := l_p46p_rec.annual_pension;
2202 p_p46p_rec.act_info3 := l_p46p_rec.date_pension_start;
2203
2204 hr_utility.set_location('Leaving: '||l_proc,999);
2205 return l_arch;
2206 END fetch_p46p_rec;
2207 --
2208 --
2209 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
2210 p_effective_date IN DATE,
2211 p_tab_rec_data IN action_info_table) IS
2212 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
2213 l_ovn number;
2214 l_action_id number;
2215 BEGIN
2216 hr_utility.set_location('Entering: '||l_proc,1);
2217 if p_tab_rec_data.count > 0 then
2218 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
2219 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
2220 hr_utility.trace('action_context_id = '|| p_assactid);
2221 if p_tab_rec_data(i).action_info_category is not null then
2222 pay_action_information_api.create_action_information(
2223 p_action_information_id => l_action_id,
2224 p_object_version_number => l_ovn,
2225 p_action_information_category => p_tab_rec_data(i).action_info_category,
2226 p_action_context_id => p_assactid,
2227 p_action_context_type => 'AAP',
2228 p_assignment_id => p_tab_rec_data(i).assignment_id,
2229 p_effective_date => p_effective_date,
2230 p_action_information1 => p_tab_rec_data(i).act_info1,
2231 p_action_information2 => p_tab_rec_data(i).act_info2,
2232 p_action_information3 => p_tab_rec_data(i).act_info3,
2233 p_action_information4 => p_tab_rec_data(i).act_info4,
2234 p_action_information5 => p_tab_rec_data(i).act_info5,
2235 p_action_information6 => p_tab_rec_data(i).act_info6,
2236 p_action_information7 => p_tab_rec_data(i).act_info7,
2237 p_action_information8 => p_tab_rec_data(i).act_info8,
2238 p_action_information9 => p_tab_rec_data(i).act_info9,
2239 p_action_information10 => p_tab_rec_data(i).act_info10,
2240 p_action_information11 => p_tab_rec_data(i).act_info11,
2241 p_action_information12 => p_tab_rec_data(i).act_info12,
2242 p_action_information13 => p_tab_rec_data(i).act_info13,
2243 p_action_information14 => p_tab_rec_data(i).act_info14,
2244 p_action_information15 => p_tab_rec_data(i).act_info15,
2245 p_action_information16 => p_tab_rec_data(i).act_info16,
2246 p_action_information17 => p_tab_rec_data(i).act_info17,
2247 p_action_information18 => p_tab_rec_data(i).act_info18,
2248 p_action_information19 => p_tab_rec_data(i).act_info19,
2249 p_action_information20 => p_tab_rec_data(i).act_info20,
2250 p_action_information21 => p_tab_rec_data(i).act_info21,
2251 p_action_information22 => p_tab_rec_data(i).act_info22,
2252 p_action_information23 => p_tab_rec_data(i).act_info23,
2253 p_action_information24 => p_tab_rec_data(i).act_info24,
2254 p_action_information25 => p_tab_rec_data(i).act_info25,
2255 p_action_information26 => p_tab_rec_data(i).act_info26,
2256 p_action_information27 => p_tab_rec_data(i).act_info27,
2257 p_action_information28 => p_tab_rec_data(i).act_info28,
2258 p_action_information29 => p_tab_rec_data(i).act_info29,
2259 p_action_information30 => p_tab_rec_data(i).act_info30
2260 );
2261 end if;
2262 end loop;
2263 end if;
2264 hr_utility.set_location('Leaving: '||l_proc,999);
2265 END insert_archive_row;
2266 --
2267 --
2268 /*------------ PUBLIC PROCEDURE --------------*/
2269 --
2270 --
2271 function edi_errors_log(assignment_number IN varchar2,
2272 ni_number IN varchar2,
2273 first_name IN varchar2,
2274 last_name IN varchar2,
2275 middle_name IN varchar2,
2276 title IN varchar2,
2277 status IN varchar2)
2278 RETURN NUMBER
2279 IS
2280 i NUMBER;
2281 BEGIN
2282 i := g_edi_errors_table.count + 1;
2283 g_edi_errors_table(i).assignment_number := assignment_number;
2284 g_edi_errors_table(i).ni_number := ni_number;
2285 g_edi_errors_table(i).first_name := first_name;
2286 g_edi_errors_table(i).last_name := last_name;
2287 g_edi_errors_table(i).middle_name := middle_name;
2288 g_edi_errors_table(i).title := title;
2289 g_edi_errors_table(i).status := status;
2290
2291 Return 0;
2292 END;
2293
2294 --For bug 9255173
2295 --This function implements validations of fast formula PAY_GB_EDI_MOVDED6_TAX_DIST
2296 --It would be called only for eText reports
2297 FUNCTION tax_dist_etext_vals(p_tst_indi in varchar2,
2298 p_tst_id in varchar2,
2299 p_tax_ref in varchar2,
2300 p_employer_name in varchar2)
2301 Return BOOLEAN
2302 IS
2303 l_proc CONSTANT VARCHAR2(50):= g_package||'tax_dist_etext_vals';
2304 l_tax_dist_no VARCHAR2(5);
2305 l_tax_dist_ref VARCHAR2(15);
2306 l_err BOOLEAN := False;
2307
2308 BEGIN
2309 hr_utility.set_location('Entering: '||l_proc,1);
2310
2311 l_tax_dist_no := substr(p_tax_ref,1,3);
2312 l_tax_dist_ref := substr(ltrim(substr(p_tax_ref,4,11),'/'),1,10);
2313
2314 hr_utility.set_location('l_tax_dist_no: '||l_tax_dist_no,1);
2315 hr_utility.set_location('l_tax_dist_ref: '||l_tax_dist_ref,1);
2316
2317 --Tax Reference validations
2318 IF l_tax_dist_no is null
2319 THEN
2320 l_err := TRUE;
2321 hr_utility.set_location('The HMRC Office Number is missing.',10);
2322 fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
2323 END IF;
2324
2325
2326 IF l_tax_dist_ref is null
2327 THEN
2328 l_err := TRUE;
2329 hr_utility.set_location('The employer''s PAYE Reference is missing.',10);
2330 fnd_file.put_line(fnd_file.output,'The employer''s PAYE Reference is missing.');
2331
2332 ELSIF pay_gb_eoy_magtape.validate_input(l_tax_dist_ref,'P14_FULL_EDI') > 0
2333 THEN
2334 l_err := TRUE;
2335 hr_utility.set_location('The employer''s PAYE Reference contains invalid characters.',10);
2336 fnd_file.put_line(fnd_file.output,'The employer''s PAYE Reference contains invalid characters.');
2337 END IF;
2338
2339 --Employer name validations
2340 IF p_employer_name IS NULL
2341 THEN
2342 l_err := TRUE;
2343 hr_utility.set_location('The employer''s name is missing for employer''s PAYE Reference '||p_tax_ref,10);
2344 fnd_file.put_line(fnd_file.output,'The employer''s name is missing for employer''s PAYE Reference '||p_tax_ref);
2345
2346 ELSIF pay_gb_eoy_magtape.validate_input(p_employer_name,'P14_FULL_EDI') > 0
2347 THEN
2348 l_err := TRUE;
2349 hr_utility.set_location('The employer''s name '||p_employer_name||' contains invalid character(s) for the employer''s PAYE Reference '||p_tax_ref,10);
2350 fnd_file.put_line(fnd_file.output,'The employer''s name '||p_employer_name||' contains invalid character(s) for the employer''s PAYE Reference '||p_tax_ref);
2351 END IF;
2352
2353 --Test Indicator validations
2354 IF p_tst_indi = 'Y'
2355 THEN
2356 IF pay_gb_eoy_magtape.validate_input(p_tst_id,'MIXED_CHAR_ALPHA_NUM') > 0
2357 THEN
2358 l_err := TRUE;
2359 hr_utility.set_location('The Test ID '||p_tst_id||' contains invalid character(s).',10);
2360 fnd_file.put_line(fnd_file.output,'The Test ID '||p_tst_id||' contains invalid character(s).');
2361 END IF;
2362 END IF;
2363
2364 hr_utility.set_location('Leaving: '||l_proc,999);
2365 RETURN l_err;
2366
2367 END tax_dist_etext_vals;
2368
2369 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
2370 IS
2371 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
2372
2373 /*Start Modifications for bug 7633799 fix*/
2374 /* l_sender_id VARCHAR2(30);
2375 l_tax_ref VARCHAR2(30);
2376 l_tax_dist VARCHAR2(30);*/
2377 l_sender_id hr_organization_information.org_information11%TYPE;
2378 l_tax_ref hr_organization_information.org_information1%TYPE;
2379 l_tax_dist hr_organization_information.org_information2%TYPE;
2380 /*End Modifications for bug 7633799 fix*/
2381
2382 l_employer_addr VARCHAR2(255);
2383 l_employer_name VARCHAR2(150);
2384 l_err BOOLEAN;
2385 l_exp EXCEPTION;
2386
2387 cursor csr_sender_id is
2388 select upper(hoi.org_information11),
2389 upper(hoi.org_information1),
2390 upper(hoi.org_information2),
2391 upper(hoi.org_information3),
2392 upper(hoi.org_information4)
2393 from pay_payroll_actions pact,
2394 hr_organization_information hoi
2395 where pact.payroll_action_id = p_payroll_action_id
2396 and pact.business_group_id = hoi.organization_id
2397 and hoi.org_information_context = 'Tax Details References'
2398 and (hoi.org_information10 is null
2399 OR
2400 hoi.org_information10 = 'UK')
2401 and upper(hoi.org_information1) =
2402 upper(substr(pact.legislative_parameters,
2403 instr(pact.legislative_parameters,'TAX_REF=') + 8,
2404 instr(pact.legislative_parameters||' ',' ',
2405 instr(pact.legislative_parameters,'TAX_REF=')+8)
2406 - instr(pact.legislative_parameters,'TAX_REF=') - 8));
2407
2408 --For bugs 9255173 and 9255183
2409 Cursor csr_cp_info
2410 IS
2411 SELECT substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2412 'TEST'),1,1) test_indicator,
2413 trim(substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2414 'TEST_ID'),1,8)) test_id,
2415 report_type
2416 FROM pay_payroll_actions
2417 WHERE payroll_action_id = p_payroll_action_id;
2418
2419 l_tst_indi varchar2(1);
2420 l_tst_id varchar2(10);
2421 l_rep_typ varchar2(15);
2422
2423 BEGIN
2424 hr_utility.set_location('Entering '|| l_proc, 10);
2425 l_err := FALSE;
2426
2427 open csr_sender_id;
2428 fetch csr_sender_id into l_sender_id, l_tax_ref, l_tax_dist, l_employer_name, l_employer_addr;
2429 close csr_sender_id;
2430
2431 --For bugs 9255173 and 9255183
2432 --header validations
2433 hr_utility.set_location('l_sender_id '|| l_sender_id, 10);
2434
2435 OPEN csr_cp_info;
2436 FETCH csr_cp_info into l_tst_indi,l_tst_id,l_rep_typ;
2437 CLOSE csr_cp_info;
2438
2439 hr_utility.set_location('l_tst_indi '|| l_tst_indi, 10);
2440 hr_utility.set_location('l_tst_id '|| l_tst_id, 10);
2441 hr_utility.set_location('l_rep_typ '|| l_rep_typ, 10);
2442 hr_utility.set_location('l_tax_ref: '||l_tax_ref,1);
2443 hr_utility.set_location('l_employer_name: '||l_employer_name,1);
2444
2445 IF l_rep_typ in ('P46_VER6ET','P46EXP_VER6ET')
2446 THEN
2447 hr_utility.set_location('Call header validations', 10);
2448 l_err := tax_dist_etext_vals(l_tst_indi,
2449 l_tst_id,
2450 l_tax_ref,
2451 l_employer_name);
2452
2453 END IF;
2454
2455 if l_sender_id is null then
2456 pay_core_utils.push_message(800, 'HR_78087_EDI_SENDER_ID_MISSING', 'F');
2457 pay_core_utils.push_token('TAX_REF', l_tax_ref);
2458 l_err := true;
2459 else
2460 if (not validate_data(l_sender_id,'Sender ID','FULL_EDI')) then
2461 l_err := true;
2462 end if;
2463 end if;
2464
2465 if pay_gb_eoy_magtape.validate_input(substr(l_tax_ref,1,3),'NUMBER') > 0
2466 OR
2467 pay_gb_eoy_magtape.validate_input(l_tax_ref,'FULL_EDI') > 0 then
2468 pay_core_utils.push_message(800, 'HR_GB_78049_INV_EMP_PAYE_REF', 'F');
2469 l_err := true;
2470 end if;
2471
2472 if (not validate_data(l_tax_dist,'IR Office Name ','FULL_EDI')) then
2473 l_err := true;
2474 end if;
2475
2476 if (not validate_data(l_employer_name,'Employers Name','FULL_EDI')) then
2477 l_err := true;
2478 end if;
2479
2480 if (not validate_data(l_employer_addr,'Employers Address','P14_FULL_EDI')) then
2481 l_err := true;
2482 end if;
2483
2484 if (l_err) then
2485 raise l_exp;
2486 end if;
2487 hr_utility.set_location('Leaving '|| l_proc, 10);
2488 EXCEPTION
2489 when others then
2490 hr_utility.raise_error;
2491 END archinit;
2492 --
2493 --
2494 PROCEDURE range_cursor (pactid IN NUMBER,
2495 sqlstr OUT NOCOPY VARCHAR2)
2496 IS
2497 /* Changes for P45PT3 start*/
2498 cursor csr_parameter_info IS
2499 SELECT
2500 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2501 'TEST'),1,1) test_indicator,
2502 trim(substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2503 'TEST_ID'),1,8)) test_id,
2504 report_type
2505 FROM pay_payroll_actions
2506 WHERE payroll_action_id = pactid;
2507
2508
2509 l_test_indicator varchar2(1);
2510 l_test_id varchar2(8);
2511 l_report_type varchar2(15);
2512 test_indicator_error EXCEPTION;
2513 /* Changes for P45PT3 end*/
2514 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
2515 BEGIN
2516 hr_utility.set_location('Entering: '||l_proc,1);
2517
2518 /* Changes for P45PT3 start*/
2519 OPEN csr_parameter_info;
2520 fetch csr_parameter_info into l_test_indicator,l_test_id,l_report_type;
2521 CLOSE csr_parameter_info ;
2522 /* changes for P46_ver6_pennot starts **/
2523 IF l_report_type = 'P45PT_3' or l_report_type='P46_5_PENNOT' or
2524 l_report_type='P46_VER6_PENNOT' or l_report_type='P46_5' or
2525 l_report_type = 'P45PT_3_VER6' or l_report_type = 'P46_VER6' or
2526 l_report_type = 'P46EXP_VER6' or l_report_type = 'P46EXP_VER6ET' or
2527 l_report_type = 'P46_VER6ET' THEN --Bugs 9255173 and 9255183
2528 /* changes for P46_ver6_pennot ends **/
2529 IF (l_test_indicator = 'Y' AND l_test_id IS NULL) THEN
2530 fnd_file.put_line (fnd_file.LOG,'Enter the Test ID as EDI Test Indicator is Yes.');
2531 RAISE test_indicator_error;
2532 END IF;
2533 END IF;
2534 /* Changes for P45PT3 end*/
2535
2536 sqlstr := 'select distinct person_id '||
2537 'from per_people_f ppf, '||
2538 'pay_payroll_actions ppa '||
2539 'where ppa.payroll_action_id = :payroll_action_id '||
2540 'and ppa.business_group_id = ppf.business_group_id '||
2541 'order by ppf.person_id';
2542 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
2543 hr_utility.set_location(' Leaving: '||l_proc,100);
2544 /* Changes for P45PT3 start*/
2545 EXCEPTION
2546 WHEN test_indicator_error THEN
2547 RAISE; -- reraise the error
2548 /* Changes for P45PT3 end*/
2549 END range_cursor;
2550 --
2551 --
2552 PROCEDURE p45_3_action_creation (pactid in number,
2553 stperson in number,
2554 endperson in number,
2555 chunk in number) IS
2556 BEGIN
2557 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P45_3', 'P45_3');
2558 END p45_3_action_creation;
2559 --
2560 --
2561 /*changes for P45PT_3 start*/
2562 PROCEDURE p45pt_3_action_creation (pactid in number,
2563 stperson in number,
2564 endperson in number,
2565 chunk in number) IS
2566 BEGIN
2567 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P45_3', 'P45PT_3');
2568 END p45pt_3_action_creation;
2569 /*changes for P45PT_3 end*/
2570
2571 /*changes for P45PT_3_ver6 start*/
2572 PROCEDURE p45pt_3_ver6_action_creation(pactid in number,
2573 stperson in number,
2574 endperson in number,
2575 chunk in number) IS
2576 BEGIN
2577 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P45_3', 'P45PT_3_VER6');
2578 END p45pt_3_ver6_action_creation;
2579 /*changes for P45PT_3_ver6 end*/
2580 --
2581 --
2582 PROCEDURE p46_action_creation (pactid in number,
2583 stperson in number,
2584 endperson in number,
2585 chunk in number) IS
2586 BEGIN
2587 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46');
2588 END p46_action_creation;
2589 --
2590 /*** Changes for P46 EOY *****/
2591 PROCEDURE p46_5_action_creation (pactid in number,
2592 stperson in number,
2593 endperson in number,
2594 chunk in number) IS
2595 BEGIN
2596 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46_5');
2597 END p46_5_action_creation;
2598 /*** End ***/
2599 --
2600 --
2601 PROCEDURE p46_ver6_action_creation (pactid in number,
2602 stperson in number,
2603 endperson in number,
2604 chunk in number) IS
2605 BEGIN
2606 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46_VER6');
2607 END p46_ver6_action_creation;
2608
2609 --Added for bug 9255173
2610 PROCEDURE p46_ver6et_action_creation (pactid in number,
2611 stperson in number,
2612 endperson in number,
2613 chunk in number) IS
2614 BEGIN
2615 hr_utility.set_location('Entering:p46_ver6et_action_creation',1);
2616 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46', 'P46_VER6ET');
2617 END p46_ver6et_action_creation;
2618
2619
2620 --Added for bug 9255183
2621 PROCEDURE p46exp_ver6et_action_creation (pactid in number,
2622 stperson in number,
2623 endperson in number,
2624 chunk in number) IS
2625 BEGIN
2626 hr_utility.set_location('Entering:p46exp_ver6et_action_creation',1);
2627 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46EXP', 'P46EXP_VER6ET');
2628 hr_utility.set_location('Leaving:p46exp_ver6et_action_creation',1);
2629 END p46exp_ver6et_action_creation;
2630
2631 --
2632 PROCEDURE p46_pennot_action_creation (pactid in number,
2633 stperson in number,
2634 endperson in number,
2635 chunk in number) IS
2636 BEGIN
2637 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46PENNOT', 'P46_PENNOT');
2638 END p46_pennot_action_creation;
2639 --
2640
2641 /**UK EOY07-08 P46 PENNOT --- Corresponds to CP PENNOT EDI Process **/
2642 PROCEDURE p46_5_pennot_action_creation (pactid in number,
2643 stperson in number,
2644 endperson in number,
2645 chunk in number) IS
2646 BEGIN
2647 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46PENNOT', 'P46_5_PENNOT');
2648 END p46_5_pennot_action_creation;
2649
2650 --
2651 /* changes for P46_ver6_pennot starts **/
2652 PROCEDURE P46_VER6_PENNOT_ACT_CREATION (pactid in number,
2653 stperson in number,
2654 endperson in number,
2655 chunk in number) IS
2656 BEGIN
2657 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46PENNOT', 'P46_VER6_PENNOT');
2658 END P46_VER6_PENNOT_ACT_CREATION;
2659 /* changes for P46_ver6_pennot ends **/
2660
2661
2662 /*Changes for P46EXP_Ver6 starts*/
2663 PROCEDURE P46EXP_VER6_ACTION_CREATION (pactid in number,
2664 stperson in number,
2665 endperson in number,
2666 chunk in number) IS
2667 BEGIN
2668 internal_action_creation(pactid, stperson, endperson, chunk,'GB_P46EXP', 'P46EXP_VER6');
2669 END P46EXP_VER6_ACTION_CREATION;
2670 /*Changes for P46EXP_Ver6 End*/
2671
2672 --
2673 --
2674 --
2675 --
2676 --For bug 9255173
2677 --this function implements validations of formula PAY_GB_EDI_P46_6_ASG
2678 FUNCTION p46_v6_asg_etext_vals(p_assactid IN NUMBER,
2679 p_effective_date IN DATE,
2680 p_tab_rec_data IN action_info_table)
2681 Return BOOLEAN
2682 IS
2683 l_proc CONSTANT VARCHAR2(50):= g_package||'p46_v6_asg_etext_vals';
2684 l_err BOOLEAN := False;
2685
2686 l_tax_code_in_use VARCHAR2(50);
2687 l_assignment_number per_assignments_f.assignment_number%TYPE;
2688 l_sex per_people_f.sex%TYPE;
2689 l_date_of_birth VARCHAR2(100);
2690 l_hire_date VARCHAR2(100);
2691 l_tax_basis_in_use VARCHAR2(50);
2692
2693 l_default_p46 VARCHAR2(5);
2694 l_p46_statement VARCHAR2(5);
2695
2696 l_msg_value VARCHAR2(1000);
2697
2698
2699 BEGIN
2700 hr_utility.set_location('Entering: '||l_proc,1);
2701
2702 l_tax_code_in_use := p_tab_rec_data(0).act_info21;
2703 l_assignment_number := p_tab_rec_data(0).act_info11;
2704 l_sex := p_tab_rec_data(0).act_info17;
2705 l_date_of_birth := p_tab_rec_data(0).act_info15;
2706 l_hire_date := p_tab_rec_data(0).act_info16;
2707 l_tax_basis_in_use := p_tab_rec_data(0).act_info22;
2708
2709 l_default_p46 := p_tab_rec_data(2).act_info4;
2710 l_p46_statement := p_tab_rec_data(2).act_info2;
2711
2712 hr_utility.set_location('l_tax_code_in_use '||l_tax_code_in_use,111);
2713 hr_utility.set_location('l_assignment_number '||l_assignment_number,111);
2714 hr_utility.set_location('l_sex '||l_sex,111);
2715 hr_utility.set_location('l_date_of_birth '||l_date_of_birth,111);
2716 hr_utility.set_location('l_hire_date '||l_hire_date,111);
2717 hr_utility.set_location('l_tax_basis_in_use '||l_tax_basis_in_use,111);
2718 hr_utility.set_location('l_default_p46 '||l_default_p46,111);
2719 hr_utility.set_location('l_p46_statement '||l_p46_statement,111);
2720
2721 --Validations
2722 IF l_tax_code_in_use IS NULL
2723 THEN
2724 l_err := TRUE;
2725 populate_run_msg(p_assactid,'The Tax Code in use of the assignment '||l_assignment_number||' is missing.');
2726 hr_utility.set_location('The Tax Code in use of the assignment '||l_assignment_number||' is missing.',10);
2727 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The Tax Code in use of the assignment '||l_assignment_number||' is missing.');
2728 END IF;
2729
2730 IF l_assignment_number IS NOT NULL
2731 AND pay_gb_eoy_magtape.validate_input(l_assignment_number,'P14_FULL_EDI') > 0
2732 THEN
2733 l_err := TRUE;
2734 populate_run_msg(p_assactid,'The Assignment Number '||l_assignment_number||' has invalid character(s).');
2735 hr_utility.set_location('The Assignment Number '||l_assignment_number||' has invalid character(s).',10);
2736 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The Assignment Number '||l_assignment_number||' has invalid character(s).');
2737 END IF;
2738
2739 IF l_sex IS NULL
2740 THEN
2741 l_err := TRUE;
2742 populate_run_msg(p_assactid,'The Sex of the assignment '||l_assignment_number||' is missing.');
2743 hr_utility.set_location('The Sex of the assignment '||l_assignment_number||' is missing.',10);
2744 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The Sex of the assignment '||l_assignment_number||' is missing.');
2745 ELSIF l_sex NOT IN ('M', 'F')
2746 THEN
2747 l_err := TRUE;
2748 populate_run_msg(p_assactid,'The sex '||l_sex||' is undefined for the assignment'||l_assignment_number);
2749 hr_utility.set_location('The sex '||l_sex||' is undefined for the assignment'||l_assignment_number,10);
2750 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The sex '||l_sex||' is undefined for the assignment'||l_assignment_number);
2751 END IF;
2752
2753 IF l_date_of_birth IS NULL
2754 THEN
2755 l_err := TRUE;
2756 populate_run_msg(p_assactid,'The Date of Birth of the assignment '||l_assignment_number||' is missing.');
2757 hr_utility.set_location('The Date of Birth of the assignment '||l_assignment_number||' is missing.',10);
2758 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The Date of Birth of the assignment '||l_assignment_number||' is missing.');
2759 END IF;
2760
2761 IF l_default_p46 = 'N' AND l_p46_statement IS NULL
2762 THEN
2763 l_err := TRUE;
2764 populate_run_msg(p_assactid,'The assignment, '||l_assignment_number||', does not have a P46 Statement for a Normal P46 Process.');
2765 hr_utility.set_location('The assignment, '||l_assignment_number||', does not have a P46 Statement for a Normal P46 Process.',10);
2766 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The assignment, '||l_assignment_number||', does not have a P46 Statement for a Normal P46 Process.');
2767 END IF;
2768
2769 IF l_hire_date IS NULL
2770 THEN
2771 l_err := TRUE;
2772 populate_run_msg(p_assactid,'The assignment, '||l_assignment_number||', does not have a Hire Date.');
2773 hr_utility.set_location('The assignment, '||l_assignment_number||', does not have a Hire Date.',10);
2774 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The assignment, '||l_assignment_number||', does not have a Hire Date.');
2775 END IF;
2776
2777
2778 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code_in_use,p_effective_date);
2779
2780 IF l_tax_code_in_use IS NOT NULL
2781 AND l_msg_value <> ' '
2782 THEN
2783 l_err := TRUE;
2784 populate_run_msg(p_assactid,'The '||l_msg_value||':tax code, '||l_tax_code_in_use||', for assignment '||l_assignment_number);
2785 hr_utility.set_location('The '||l_msg_value||':tax code, '||l_tax_code_in_use||', for assignment '||l_assignment_number,10);
2786 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The '||l_msg_value||':tax code, '||l_tax_code_in_use||', for assignment '||l_assignment_number);
2787 END IF;
2788
2789 IF l_tax_code_in_use IS NOT NULL
2790 AND l_tax_basis_in_use IS NULL
2791 THEN
2792 l_err := TRUE;
2793 populate_run_msg(p_assactid,'The Tax Basis in use is not present for Tax code in use, for assignment '||l_assignment_number);
2794 hr_utility.set_location('The Tax Basis in use is not present for Tax code in use, for assignment '||l_assignment_number,10);
2795 fnd_file.put_line (fnd_file.LOG,l_assignment_number||': The Tax Basis in use is not present for Tax code in use, for assignment '||l_assignment_number);
2796 END IF;
2797
2798 hr_utility.set_location('Leaving: '||l_proc,999);
2799 RETURN l_err;
2800
2801 END p46_v6_asg_etext_vals;
2802
2803 PROCEDURE archive_code(p_assactid IN NUMBER,
2804 p_effective_date IN DATE) IS
2805 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
2806 error_found EXCEPTION;
2807 l_archive_tab action_info_table;
2808 l_tax_rec g_tax_rec;
2809 l_archive_person boolean;
2810 l_archive_addr boolean;
2811 l_archive_data boolean;
2812 l_archive_type VARCHAR2(20);
2813
2814 --For bugs 9255173 and 9255183
2815 l_p46exp_etext_asg_flag varchar2(1);
2816 l_movded6_etext_asg_flag varchar2(1);
2817 l_p46exp_val_err boolean := False;
2818
2819 l_p46_val_err boolean := False;
2820 l_asg_val_err boolean := False;
2821 l_err_log number;
2822
2823 l_assignment_number VARCHAR2(50);
2824 l_national_insurance_number VARCHAR2(50);
2825 l_first_name VARCHAR2(50);
2826 l_last_name VARCHAR2(50);
2827 l_middle_name VARCHAR2(50);
2828 l_title VARCHAR2(10);
2829
2830 cursor csr_archive_type is
2831 select report_type
2832 from pay_assignment_actions paa,
2833 pay_payroll_actions ppa
2834 where paa.assignment_action_id = p_assactid
2835 and paa.payroll_action_id = ppa.payroll_action_id;
2836
2837 BEGIN
2838 hr_utility.trace('\n xxxx Test Indicator='||pay_magtape_generic.get_parameter_value('TEST'));
2839 --hr_utility.trace_on(null,'TKP');
2840 fnd_file.put_line(fnd_file.LOG,'Entering: '||l_proc);
2841 --hr_utility.trace('Tushar effective date is '|| to_char(p_effective_date,'DD-MON-YYYY') );
2842 --hr_utility.set_location('Entering: '||l_proc,1);
2843 open csr_archive_type;
2844 fetch csr_archive_type into l_archive_type;
2845 close csr_archive_type;
2846
2847 --For bugs 9255173 and 9255183
2848 g_archive_type := l_archive_type;
2849
2850 fetch_tax_rec(p_assactid,p_effective_date,l_tax_rec);
2851
2852 hr_utility.set_location('Fetching person details ',10);
2853 l_archive_person := fetch_person_rec(p_assactid, p_effective_date, l_tax_rec, l_archive_tab(0));
2854
2855 hr_utility.set_location('Fetching address details ',20);
2856 l_archive_addr := fetch_address_rec(l_archive_tab(0).person_id,
2857 l_archive_tab(0).assignment_id,
2858 p_effective_date,
2859 l_archive_tab(1));
2860
2861 hr_utility.set_location('Fetching P45(3) details ',30);
2862 if l_archive_type = 'P45_3' then
2863 l_archive_data := fetch_p45_3_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2864 /*changes for P45PT_3 start*/
2865 elsif l_archive_type = 'P45PT_3' then
2866 l_archive_data := fetch_p45_3_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2867 /*changes for P45PT_3 end*/
2868 /*changes for P45PT_3 Version 6 start*/
2869 elsif l_archive_type = 'P45PT_3_VER6' then
2870 l_archive_data := fetch_p45_3_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2871 /*changes for P45PT_3 Version 6 end*/
2872 elsif l_archive_type = 'P46' then
2873 l_archive_data := fetch_p46_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2874 elsif l_archive_type = 'P46_5' then
2875 l_archive_data := fetch_p46_5_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2876 elsif l_archive_type = 'P46_VER6' or l_archive_type = 'P46_VER6ET' then --Added for bug 9255173
2877 l_archive_data := fetch_p46_5_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2878 elsif l_archive_type = 'P46_PENNOT' then
2879 l_archive_data := fetch_p46p_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2880 /**** EOY 07-08 ****/
2881 elsif l_archive_type = 'P46_5_PENNOT' then
2882 l_archive_data := fetch_45_46_pennot_rec(p_effective_date,l_tax_rec, l_archive_tab(0),'GB_P46PENNOT',p_assactid,l_archive_tab(2));
2883 /* changes for P46_ver6_pennot starts **/
2884 elsif l_archive_type = 'P46_VER6_PENNOT' then
2885 l_archive_data := fetch_45_46_pennot_rec(p_effective_date,l_tax_rec, l_archive_tab(0),'GB_P46PENNOT',p_assactid,l_archive_tab(2));
2886 /* changes for P46_ver6_pennot ends **/
2887
2888 /*Changes for P46EXP_Ver6 starts*/
2889 elsif l_archive_type = 'P46EXP_VER6' or l_archive_type = 'P46EXP_VER6ET' then --Added for bug 9255183
2890 l_archive_data := fetch_p46exp_rec(p_effective_date,l_tax_rec, l_archive_tab(0),l_archive_tab(2));
2891 /*Changes for P46EXP_Ver6 End*/
2892
2893 END IF;
2894
2895 --For bugs 9255173 and 9255183
2896 IF l_archive_type = 'P46_VER6ET' or l_archive_type = 'P46EXP_VER6ET'
2897 THEN
2898
2899 --Modifications for the bug fix 10409668 starts here
2900 --movded6_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_movded6_etext_asg_flag);
2901 movded6_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_movded6_etext_asg_flag);
2902 --Modifications for the bug fix 10409668 ends here
2903
2904 IF l_movded6_etext_asg_flag = 'Y' THEN
2905 l_asg_val_err := TRUE;
2906 END IF;
2907
2908 IF l_archive_type = 'P46_VER6ET'
2909 THEN
2910 hr_utility.set_location('Call P46 validations', 10);
2911 l_p46_val_err := p46_v6_asg_etext_vals(p_assactid, p_effective_date, l_archive_tab);
2912
2913 ELSIF l_archive_type = 'P46EXP_VER6ET'
2914 THEN
2915 hr_utility.set_location('Call P46Expat validations', 10);
2916 p46exp_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_p46exp_etext_asg_flag);
2917
2918 IF l_p46exp_etext_asg_flag = 'Y' THEN
2919 l_p46exp_val_err := TRUE;
2920 END IF;
2921
2922 END IF;
2923
2924 /* --Section removed as it is not needed with new logic of writing to O/P file
2925 --Write to error log
2926 l_assignment_number := nvl(l_archive_tab(0).act_info11, ' ');
2927 l_national_insurance_number := nvl(l_archive_tab(0).act_info12,' ');
2928 l_first_name := nvl(upper(substr(l_archive_tab(0).act_info6,1,35)),' ');
2929 l_last_name := nvl(upper(substr(l_archive_tab(0).act_info8,1,35)),' ');
2930 l_middle_name := nvl(upper(substr(l_archive_tab(0).act_info7,1,35)),' ');
2931 l_title := nvl(substr(l_archive_tab(0).act_info14,1,4),' ');
2932
2933 hr_utility.set_location('l_assignment_number '||l_assignment_number,111);
2934 hr_utility.set_location('l_national_insurance_number '||l_national_insurance_number,111);
2935 hr_utility.set_location('l_first_name '||l_first_name,111);
2936 hr_utility.set_location('l_last_name '||l_last_name,111);
2937 hr_utility.set_location('l_middle_name '||l_middle_name,111);
2938 hr_utility.set_location('l_title '||l_title,111);
2939
2940 IF (not l_p46_val_err) AND (not l_asg_val_err) AND (not l_p46exp_val_err)
2941 AND l_archive_person AND l_archive_addr AND l_archive_data
2942 THEN
2943 l_err_log := edi_errors_log(l_assignment_number,l_national_insurance_number,
2944 l_first_name,l_last_name,
2945 l_middle_name,l_title,'C');
2946 END IF;
2947 */
2948 END IF;
2949
2950 if l_archive_person and l_archive_addr and l_archive_data then
2951 --For bugs 9255173 and 9255183
2952 IF l_archive_type = 'P46_VER6ET' OR l_archive_type = 'P46EXP_VER6ET'
2953 THEN
2954 IF l_asg_val_err OR l_p46_val_err OR l_p46exp_val_err
2955 THEN
2956 hr_utility.set_location('Validation failed, raise error.',999);
2957 fnd_file.put_line(fnd_file.LOG,'Archiving');
2958 raise error_found;
2959 ELSE
2960 hr_utility.set_location('Validation successful, archive data.',999);
2961 insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
2962 END IF;
2963 ELSE
2964 insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
2965 END IF;
2966 else
2967 fnd_file.put_line(fnd_file.LOG,'Archiving');
2968 raise error_found;
2969 end if;
2970
2971 hr_utility.set_location('Leaving: '||l_proc,999);
2972
2973 EXCEPTION
2974 when error_found then
2975 if l_archive_type = 'P45_3' then
2976 reset_flag('GB_P45_3',p_assactid);
2977 /* changes for P45PT_3 start */
2978 elsif l_archive_type = 'P45PT_3' then
2979 reset_flag('GB_P45_3',p_assactid);
2980 /* changes for P45PT_3 end */
2981 /* changes for P45PT_3 Version 6 start */
2982 elsif l_archive_type = 'P45PT_3_VER6' then
2983 reset_flag('GB_P45_3',p_assactid);
2984 /* changes for P45PT_3 Version 6 end */
2985 elsif l_archive_type = 'P46' then
2986 reset_flag('GB_P46',p_assactid);
2987 elsif l_archive_type = 'P46_PENNOT' then
2988 reset_flag('GB_P46PENNOT',p_assactid);
2989 elsif l_archive_type = 'P46_5_PENNOT' then
2990 reset_flag('GB_P46PENNOT',p_assactid);
2991 /* changes for P46_ver6_pennot starts **/
2992 elsif l_archive_type = 'P46_VER6_PENNOT' then
2993 reset_flag('GB_P46PENNOT',p_assactid);
2994 /* changes for P46_ver6_pennot ends **/
2995 /*Changes for P46EXP_Ver6 starts*/
2996 elsif l_archive_type = 'P46EXP_VER6' then
2997 reset_flag('GB_P46EXP',p_assactid);
2998 /*Changes for P46EXP_Ver6 End*/
2999 end if;
3000
3001 --For bugs 9255173 and 9255183
3002 IF l_archive_type in ('P46EXP_VER6ET','P46_VER6ET')
3003 THEN
3004 raise_application_error(-20001,'Error(s) found while archiving data.');
3005 ELSE
3006 hr_utility.raise_error;
3007 END IF;
3008 END archive_code;
3009 --
3010 --
3011 PROCEDURE deinitialization_code(pactid IN NUMBER)
3012 IS
3013 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
3014 l_counter number;
3015
3016 --For bugs 9255173 and 9255183
3017 Cursor csr_is_etext_report IS
3018 Select report_type
3019 From pay_payroll_actions pact
3020 Where pact.payroll_action_id = pactid;
3021
3022 l_is_etext_report varchar2(50);
3023 l_request_id fnd_concurrent_requests.request_id%TYPE;
3024 xml_layout boolean;
3025
3026 procedure write_header is
3027 l_token varchar2(255);
3028 l_addr1 varchar2(255);
3029 l_addr2 varchar2(255);
3030 l_addr3 varchar2(255);
3031 l_addr4 varchar2(255);
3032 l_form varchar2(40);
3033 l_tax_ref varchar2(20);
3034 l_urgent varchar2(2);
3035 l_test varchar2(2);
3036 l_temp number;
3037
3038 cursor csr_leg_param is
3039 select legislative_parameters para,
3040 fnd_number.number_to_canonical(request_id) control_id,
3041 report_type,
3042 business_group_id
3043 from pay_payroll_actions
3044 where payroll_action_id = pactid;
3045
3046 cursor csr_header_det(p_bus_id number,
3047 p_tax_ref varchar2) is
3048 select nvl(hoi.org_information11,' ') sender_id,
3049 nvl(upper(hoi.org_information2),' ') hrmc_office,
3050 nvl(upper(hoi.org_information4),' ') er_addr,
3051 nvl(upper(hoi.org_information3),' ') er_name
3052 from hr_organization_information hoi
3053 where hoi.organization_id = p_bus_id
3054 and hoi.org_information_context = 'Tax Details References'
3055 and nvl(hoi.org_information10,'UK') = 'UK'
3056 and upper(hoi.org_information1) = upper(p_tax_ref);
3057
3058 --For bugs 9255173 and 9255183
3059 Cursor csr_act_actions
3060 Is
3061 Select assignment_action_id
3062 From pay_assignment_actions paa
3063 Where paa.payroll_action_id = pactid
3064 Order by assignment_action_id;
3065
3066 Cursor messages (p_asg_act_id in number)
3067 Is
3068 Select pml.line_text error_text
3069 From pay_message_lines pml
3070 Where pml.source_id = p_asg_act_id
3071 and pml.MESSAGE_LEVEL = 'F'
3072 and pml.line_sequence < (select line_sequence
3073 from pay_message_lines pml1
3074 where pml1.source_id = p_asg_act_id
3075 and pml1.line_text like 'Error ORA-20001: Error(s) found while archiving data.')
3076 UNION ALL
3077 Select pml.line_text error_text
3078 From pay_message_lines pml
3079 Where pml.source_id = p_asg_act_id
3080 and pml.message_level = 'W';
3081
3082 l_param csr_leg_param%rowtype;
3083 l_det csr_header_det%rowtype;
3084 begin
3085 open csr_leg_param;
3086 fetch csr_leg_param into l_param;
3087 close csr_leg_param;
3088
3089 l_token := 'TAX_REF';
3090 l_temp := instr(l_param.para,l_token);
3091 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
3092 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3093 l_token := 'URGENT';
3094 l_temp := instr(l_param.para,l_token);
3095 l_urgent := substr(l_param.para, l_temp + length(l_token) + 1,
3096 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3097 l_token := 'TEST';
3098 l_temp := instr(l_param.para,l_token);
3099 l_test := substr(l_param.para, l_temp + length(l_token) + 1,
3100 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
3101
3102 open csr_header_det(l_param.business_group_id, l_tax_ref);
3103 fetch csr_header_det into l_det;
3104 close csr_header_det;
3105
3106 l_addr1 := l_det.er_addr;
3107 if length(l_addr1) > 35 then
3108 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
3109 if l_temp = 0 then
3110 l_temp := 35;
3111 end if;
3112 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
3113 l_addr1 := substr(l_addr1,1,l_temp);
3114 end if;
3115 if length(l_addr2) > 35 then
3116 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
3117 if l_temp = 0 then
3118 l_temp := 35;
3119 end if;
3120 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
3121 l_addr2 := substr(l_addr2,1,l_temp);
3122 end if;
3123 if length(l_addr3) > 35 then
3124 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
3125 if l_temp = 0 then
3126 l_temp := 35;
3127 end if;
3128 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
3129 l_addr4 := substr(l_addr3,1,l_temp);
3130 end if;
3131
3132
3133 if l_param.report_type = 'P45_3' then
3134 l_form := 'P45(3) ( MOVDED 3.0 )';
3135 /* changes for P45PT_3 start */
3136 elsif l_param.report_type = 'P45PT_3' then
3137 l_form := 'P45(3) ( MOVDED 5.0 )';
3138 /* changes for P45PT_3 end */
3139
3140 /* changes for P45PT_3 Version 6 start */
3141 elsif l_param.report_type = 'P45PT_3_VER6' then
3142 l_form := 'P45(3) ( MOVDED 6.0 )';
3143 /* changes for P45PT_3 Version 6 end */
3144 elsif l_param.report_type = 'P46' then
3145 l_form := 'P46 ( P46 4.0 )';
3146 elsif l_param.report_type = 'P46_PENNOT' then
3147 l_form := 'P46 Pension Notification ( MOVDED 3.0 )';
3148 elsif l_param.report_type = 'P46_5_PENNOT' then
3149 l_form := 'P46 Pension Notification ( MOVDED 5.0 )';
3150 /* changes for P46_ver6_pennot starts **/
3151 elsif l_param.report_type = 'P46_VER6_PENNOT' then
3152 l_form := 'P46 Pension Notification ( MOVDED 6.0 )';
3153 /* changes for P46_ver6_pennot ends **/
3154 elsif l_param.report_type = 'P46_5' then
3155 l_form := 'P46 ( MOVDED 5.0 )';
3156 /*Changes for P46EXP_Ver6 starts*/
3157 elsif l_param.report_type = 'P46EXP_VER6' or l_param.report_type = 'P46EXP_VER6ET' then --For bug 9255183
3158 l_form := 'P46Exp ( MOVDED 6.0 )';
3159 /*Changes for P46EXP_Ver6 End*/
3160 elsif l_param.report_type = 'P46_VER6' or l_param.report_type = 'P46_VER6ET' then --For bug 9255173
3161 l_form := 'P46 ( MOVDED 6.0 )'; -- Bug 8830306
3162 end if;
3163
3164 --For bugs 9255173 and 9255183
3165 IF l_param.report_type = 'P46_VER6ET' or l_param.report_type = 'P46EXP_VER6ET'
3166 THEN
3167 fnd_file.put_line(fnd_file.log,'Inside Deinit. Print error msgs');
3168 FOR act_actions IN csr_act_actions
3169 LOOP
3170 FOR msg_rec IN messages(act_actions.assignment_action_id)
3171 LOOP
3172 fnd_file.put_line(fnd_file.output,substr(msg_rec.error_text,1,255));
3173 END LOOP;
3174 END LOOP;
3175 END IF;
3176
3177 fnd_file.put_line(fnd_file.output,' ');
3178 fnd_file.put_line(fnd_file.output,'EDI Transmission Report:');
3179 fnd_file.put_line(fnd_file.output,' ');
3180 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form );
3181 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
3182 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
3183 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
3184 fnd_file.put_line(fnd_file.output,rpad('Test Transmission : ',32) || l_test);
3185 fnd_file.put_line(fnd_file.output,rpad('Urgent : ',32) || l_urgent);
3186 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
3187 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
3188 fnd_file.put_line(fnd_file.output,rpad('HRMC Office : ',32) || l_det.hrmc_office);
3189 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
3190 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
3191 if length(l_addr2) > 0 then
3192 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
3193 end if;
3194 if length(l_addr3) > 0 then
3195 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
3196 end if;
3197 if length(l_addr4) > 0 then
3198 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
3199 end if;
3200 end write_header;
3201
3202 procedure write_sub_header(p_type varchar2) is
3203 begin
3204 fnd_file.put_line(fnd_file.output,null);
3205 if p_type = 'E' then
3206 fnd_file.put_line(fnd_file.output,'The following assignments have completed with error');
3207 else
3208 fnd_file.put_line(fnd_file.output,'The following assignments have completed successfully');
3209 end if;
3210 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
3211 rpad('NI Number',11) ||
3212 rpad('Employee Name', 50));
3213 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
3214 rpad('-',10,'-') || ' ' ||
3215 rpad('-',50,'-'));
3216 end write_sub_header;
3217
3218 procedure write_body(p_type varchar2) is
3219 l_count number;
3220 i number;
3221 l_temp varchar2(255);
3222 cursor csr_asg is
3223 select /*+ ORDERED */
3224 peo.first_name f_name ,
3225 peo.middle_names m_name,
3226 peo.last_name l_name,
3227 peo.title title,
3228 paf.assignment_number emp_no,
3229 peo.national_identifier ni_no
3230 from pay_payroll_actions pay,
3231 pay_assignment_actions paa,
3232 per_all_assignments_f paf,
3233 per_all_people_f peo
3234 where pay.payroll_action_id = pactid
3235 and paa.payroll_action_id = pay.payroll_action_id
3236 and paa.action_status = 'E'
3237 and paf.assignment_id = paa.assignment_id
3238 and peo.person_id = paf.person_id
3239 and pay.effective_date between paf.effective_start_date and paf.effective_end_date
3240 and pay.effective_date between peo.effective_start_date and peo.effective_end_date;
3241
3242 --For bugs 9255173 and 9255183: Modified logic for writing to O/P file
3243 --For bug 9495487 Added upper function for all columns to make P46 output sync with P46 Magtape output
3244 cursor csr_et_asg is
3245 select /*+ ORDERED */
3246 upper(peo.first_name) f_name ,
3247 upper(peo.middle_names) m_name,
3248 upper(peo.last_name) l_name,
3249 upper(peo.title) title,
3250 upper(paf.assignment_number) emp_no,
3251 upper(peo.national_identifier) ni_no
3252 from pay_payroll_actions pay,
3253 pay_assignment_actions paa,
3254 per_all_assignments_f paf,
3255 per_all_people_f peo
3256 where pay.payroll_action_id = pactid
3257 and paa.payroll_action_id = pay.payroll_action_id
3258 and paa.action_status = 'C'
3259 and paf.assignment_id = paa.assignment_id
3260 and peo.person_id = paf.person_id
3261 and pay.effective_date between paf.effective_start_date and paf.effective_end_date
3262 and pay.effective_date between peo.effective_start_date and peo.effective_end_date;
3263
3264 l_et_temp varchar2(255);
3265
3266 begin
3267 l_count := 0;
3268 i := g_edi_errors_table.count + 1;
3269
3270 FOR i IN 1 .. g_edi_errors_table.count LOOP
3271 IF g_edi_errors_table(i).status = p_type THEN
3272 l_temp := g_edi_errors_table(i).last_name || ', '|| g_edi_errors_table(i).title || ' ' ||
3273 g_edi_errors_table(i).first_name || ' ' || g_edi_errors_table(i).middle_name ;
3274
3275 fnd_file.put_line(fnd_file.output,rpad(g_edi_errors_table(i).assignment_number, 18) || ' ' ||
3276 rpad(g_edi_errors_table(i).ni_number ,10) || ' ' ||
3277 rpad(l_temp,50));
3278 l_count := l_count + 1;
3279 END IF;
3280 END LOOP;
3281
3282 --For bugs 9255173 and 9255183: Modified logic for writing to O/P file
3283 IF p_type = 'ET'THEN
3284 FOR et_asg_rec IN csr_et_asg LOOP
3285 l_et_temp := et_asg_rec.l_name || ', '|| et_asg_rec.title || ' ' ||
3286 et_asg_rec.f_name || ' ' || et_asg_rec.m_name;
3287 fnd_file.put_line(fnd_file.output,rpad(et_asg_rec.emp_no, 18) || ' ' ||
3288 rpad(et_asg_rec.ni_no ,10) || ' ' ||
3289 rpad(l_et_temp,50));
3290 l_count := l_count + 1;
3291 END LOOP;
3292 END IF;
3293
3294 IF p_type = 'E'THEN
3295 FOR asg_rec IN csr_asg LOOP
3296 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
3297 asg_rec.f_name || ' ' || asg_rec.m_name;
3298 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3299 rpad(asg_rec.ni_no ,10) || ' ' ||
3300 rpad(l_temp,50));
3301 l_count := l_count + 1;
3302 END LOOP;
3303 END IF;
3304
3305 fnd_file.put_line(fnd_file.output,null);
3306 if p_type = 'E' then
3307 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed with error : ' || l_count);
3308 else
3309 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed successfully :' || l_count);
3310 end if;
3311 l_counter := l_counter + l_count;
3312 end write_body;
3313
3314 procedure write_footer is
3315 begin
3316 fnd_file.put_line(fnd_file.output,null);
3317 fnd_file.put_line(fnd_file.output,'Total Number Of Records : ' || l_counter);
3318 end write_footer;
3319 BEGIN
3320 hr_utility.set_location('Entering: '||l_proc,1);
3321
3322 --For bugs 9255173 and 9255183: Modified logic for O/P file
3323 /*
3324 l_counter := 0;
3325 write_header;
3326 write_sub_header('C');
3327 write_body('C');
3328 write_sub_header('E');
3329 write_body('E');
3330 write_footer;
3331 */
3332
3333 OPEN csr_is_etext_report;
3334 FETCH csr_is_etext_report INTO l_is_etext_report;
3335 CLOSE csr_is_etext_report;
3336
3337 l_counter := 0;
3338 write_header;
3339 write_sub_header('C');
3340
3341 IF l_is_etext_report IN ('P46_VER6ET', 'P46EXP_VER6ET')
3342 THEN
3343 write_body('ET');
3344 ELSE
3345 write_body('C');
3346 END IF;
3347
3348 write_sub_header('E');
3349 write_body('E');
3350 write_footer;
3351
3352 IF l_is_etext_report = 'P46_VER6ET'
3353 THEN
3354 --this is a eText report, Spawn the BI Publisher process
3355 hr_utility.set_location('This is a eText report, Spawn the BI Publisher process',1);
3356
3357 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','GB_P46_V6_ETO','en','US','ETEXT');
3358
3359 IF xml_layout = true
3360 THEN
3361 l_request_id := fnd_request.submit_request
3362 (application => 'PAY'
3363 ,program => 'GB_P46_V6_ETO'
3364 ,argument1 => pactid
3365 );
3366 Commit;
3367
3368 --check for process submit error
3369 IF l_request_id = 0
3370 THEN
3371 hr_utility.set_location('Error spawning new process',1);
3372 END IF;
3373 END IF;
3374 END IF;
3375
3376 IF l_is_etext_report = 'P46EXP_VER6ET'
3377 THEN
3378 --this is a eText report, Spawn the BI Publisher process
3379 hr_utility.set_location('This is a eText report, Spawn the BI Publisher process',1);
3380
3381 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','GB_P46EXP_V6_ETO','en','US','ETEXT');
3382
3383 IF xml_layout = true
3384 THEN
3385 l_request_id := fnd_request.submit_request
3386 (application => 'PAY'
3387 ,program => 'GB_P46EXP_V6_ETO'
3388 ,argument1 => pactid
3389 );
3390 Commit;
3391
3392 --check for process submit error
3393 IF l_request_id = 0
3394 THEN
3395 hr_utility.set_location('Error spawning new process',1);
3396 END IF;
3397 END IF;
3398 END IF;
3399
3400 hr_utility.set_location('Leaving: '||l_proc,999);
3401 END deinitialization_code;
3402 --
3403 --
3404 FUNCTION date_validate (c_assignment_action_id NUMBER,
3405 p_mode VARCHAR2,
3406 p_validate_date DATE)
3407 RETURN NUMBER
3408 IS
3409
3410
3411 cursor csr_parameter_info is
3412 select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST_ID'),
3413 pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
3414 /*ppa.effective_date*/
3415 sysdate
3416 from pay_payroll_actions ppa
3417 ,pay_assignment_actions paa
3418 where paa.assignment_action_id = c_assignment_action_id
3419 and ppa.payroll_action_id = paa.payroll_action_id;
3420
3421
3422 --For bug 8704601:Added new cursor
3423 cursor csr_parameter_info_p46_car is
3424 select ppa.effective_date
3425 from pay_payroll_actions ppa
3426 ,pay_assignment_actions paa
3427 where paa.assignment_action_id = c_assignment_action_id
3428 and ppa.payroll_action_id = paa.payroll_action_id;
3429
3430
3431 l_date_valid DATE;
3432 l_return_valid NUMBER;
3433 l_test_id VARCHAR2(8);
3434 l_test_submission VARCHAR2(1);
3435 l_tax_date DATE;
3436 l_tax_year VARCHAR2(4);
3437 l_tax_year_start DATE ;
3438 BEGIN
3439 l_return_valid := 1;
3440 open csr_parameter_info;
3441 fetch csr_parameter_info into l_test_id,l_test_submission,l_tax_date;
3442 close csr_parameter_info;
3443
3444
3445 l_tax_year := to_char(l_tax_date,'RRRR');
3446 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD')) THEN
3447 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
3448 else
3449 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
3450 end if;
3451
3452 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
3453
3454 l_tax_year_start := add_months(l_tax_date,-12)+1 ; -- 6804206
3455
3456 l_date_valid := p_validate_date;
3457
3458 if (p_mode = 'LEFT_DATE') then
3459 if (l_date_valid < add_months(l_tax_date,-72)+1) then /*vrn : 36*/
3460 l_return_valid := 0;
3461 else
3462 if (l_test_submission = 'N') then
3463 if (l_date_valid > l_tax_date+30) then
3464 l_return_valid := 0;
3465 end if;
3466 else
3467 if (l_date_valid > add_months(l_tax_date,12)) then
3468 l_return_valid := 0;
3469 end if;
3470 end if;
3471 end if;
3472 elsif (p_mode = 'LEFT_DATE_V6') then -- Added for version 6 validation
3473 if (l_date_valid < add_months(l_tax_date,-72)+1) then /*vrn : 36*/
3474 l_return_valid := 0;
3475 else
3476 if (l_test_submission = 'N') then
3477 if (l_date_valid > l_tax_date+30) then
3478 l_return_valid := 0;
3479 end if;
3480 if (l_date_valid > sysdate+30) then -- Added for version 6 validation
3481 l_return_valid := 0;
3482 end if;
3483 else
3484 if (l_date_valid > add_months(l_tax_date,12)) then
3485 l_return_valid := 0;
3486 end if;
3487 end if;
3488 end if;
3489 elsif (p_mode = 'PENSION_DATE') then
3490 if (l_test_submission = 'N') then
3491 if (l_date_valid > l_tax_date) then
3492 l_return_valid := 0;
3493 end if;
3494 else
3495 if l_date_valid > add_months(l_tax_date,12) then
3496 l_return_valid := 0;
3497 end if;
3498 end if;
3499 elsif (p_mode = 'PENSION_DATE_V6') then
3500 if (l_test_submission = 'N') then
3501 if (l_date_valid > l_tax_date+30) then
3502 l_return_valid := 0;
3503 end if;
3504 else
3505 if l_date_valid > add_months(l_tax_date,12) then
3506 l_return_valid := 0;
3507 end if;
3508 end if;
3509 elsif (p_mode = 'HIRE_DATE') then
3510 if (l_test_submission = 'N') then
3511 if (l_date_valid > l_tax_date) then
3512 l_return_valid := 0;
3513 end if;
3514 else
3515 if (l_date_valid > add_months(l_tax_date,12)) then
3516 l_return_valid := 0;
3517 end if;
3518 end if;
3519 --
3520 elsif (p_mode = 'HIRE_DATE_V6') then -- Added for version 6 validation
3521 if (l_test_submission = 'N') then
3522 if (l_date_valid > l_tax_date) then
3523 l_return_valid := 0;
3524 end if;
3525 if (l_date_valid > sysdate+30) then -- Added for version 6 validation
3526 l_return_valid := 0;
3527 end if;
3528 else
3529 if (l_date_valid > add_months(l_tax_date,12)) then
3530 l_return_valid := 0;
3531 end if;
3532 end if;
3533
3534 /*Changes for P46EXP_Ver6 starts*/
3535 elsif (p_mode = 'UK_EMPL_DATE') then
3536 if (l_test_submission = 'N') then
3537 if (l_date_valid > sysdate+30) then
3538 l_return_valid := 0;
3539 end if;
3540 else
3541 if (l_date_valid > add_months(l_tax_date,12)) then
3542 l_return_valid := 0;
3543 end if;
3544 end if;
3545 /*Changes for P46EXP_Ver6 end*/
3546
3547 elsif (p_mode = 'DOB') then
3548 if (l_date_valid > sysdate) then
3549 l_return_valid := 0;
3550 end if;
3551
3552 elsif (p_mode = 'SOY_CHECK') then
3553 if l_date_valid <= l_tax_year_start then
3554 l_return_valid := 0;
3555 end if;
3556
3557 --For bug 8704601:Added logic for P46_CAR
3558 elsif (p_mode = 'P46_CAR')
3559 then
3560 open csr_parameter_info_p46_car;
3561 fetch csr_parameter_info_p46_car into l_tax_date;
3562 close csr_parameter_info_p46_car;
3563
3564 l_tax_year := to_char(l_tax_date,'RRRR');
3565
3566 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD'))
3567 then
3568 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
3569 else
3570 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
3571 end if;
3572
3573 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
3574
3575 l_tax_year_start := add_months(l_tax_date,-12)+1 ;
3576
3577 if l_date_valid < l_tax_year_start then
3578 l_return_valid := 0;
3579 end if;
3580 --
3581 end if;
3582
3583 return l_return_valid;
3584 END date_validate;
3585
3586 end PAY_GB_MOVDED_EDI;