[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RTI
Source
1 PACKAGE BODY PAY_GB_RTI as
2 /* $Header: pygbrtia.pkb 120.72.12020000.3 2013/03/25 14:43:37 ssarap noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI
10 Purpose:
11 It fetches the live data and archives it for RTI Processes.
12 This is a UK Specific payroll package.
13
14 History:
15 21-Oct-2011 krreddy 115.0 13076448 Created.
16 22-Oct-2011 krreddy 115.1 13076448 Removed special characters to
17 resolve GSCC Error.
18 28-Oct-2011 rajganga 115.2 13076448 Modified for the new parameters
19 start and end date.
20 28-Oct-2011 rajganga 115.3 13076448 Changed the name from PYGBRTITMP
21 to PYGBRTIEAS.
22 1-Nov-2011 rajganga 115.4 13076448 Changed the value from PAYROLL_ID
23 to PAYROLL.
24 2-Nov-2011 rajganga 115.5 13076448 Changed the max of Start and End date.
25 2-Nov-2011 rajganga 115.6 13076448 Modified the Start and End date check.
26 3-Nov-2011 rajganga 115.7 13076448 Modified the Person Addr validations
27 for NINO.
28 9-Jan-2012 rsadhana 115.8 13076480 Modified the cursor csr_asg for
29 NINO-Verified Check
30 10-Jan-2012 rsadhana 115.9 13076480 Fixed the issues in the report
31 10-Jan-2012 rsadhana 115.9 13076480 Fixed the GSCC errors
32 19-Jan-2012 rsadhana 115.10 13076480 Fixed the Issues with AO Ref Number
33 19-Jan-2012 rsadhana 115.11 13076480 Changed Messages - 'Middle Name' to
34 'First Name'
35 09-Feb-2012 rajganga 115.12 13076480 Modified for EAS - parameter change.
36 09-Apr-2012 rsadhana 115.13 13622219 Added 'order by' for assignmnent Set
37 case in RTI_NINO_REP in range_cursor.
38 12-Apr-2012 achandwa 115.14 13813931 MF File not displaying correct date.
39 12-Apr-2012 rsadhana 115.15 13626488 Fix for wrong Date being reported.
40 12-Apr-2012 rsadhana 115.16 Changed '<> NULL' to 'IS NOT NULL'.
41 18-Apr-2012 ssarap 115.26 13965309 Fix for QA logged bug removed the to_char
42 in cursor get_person_details
43 19-Apr-2012 ssarap 115.27 Removed the hard coded assignment_status_type_id
44 in cursors get_person_details,csr_assignment_info_aggregated,
45 csr_assignment_info_nonagg.
46 20-Apr-2012 rajganga 115.28 13076448 Modified Validation messages and context.
47 21-Apr-2012 rajganga 115.29 13979664 Modified action creation.
48 23-Apr-2012 rajganga 115.30 13979664 Modified action creation and fetch asg.
49 24-Apr-2012 rajganga 115.31 13979664 Modified for Terminated Assignment logic.
50 25-Apr-2012 rajganga 115.32 13999823 Added logic to show employee count in output.
51 26-Apr-2012 rajganga 115.33 13813786 Modified for start date and end date of Terminated asgs.
52 26-Apr-2012 rajganga 115.34 13813786 Modified to implement Terminated asgs.
53 26-Apr-2012 rajganga 115.35 13813786 Modified Person fetch cursor.
54 27-Apr-2012 rajganga 115.36 13813786 Modified to get Terminated Asg.
55 27-Apr-2012 rsadhana 115.37 13634965 Modified the procedure internal_action_creation_nino
56 procedure to take care of terminated cases.
57 11-May-2012 rajganga 115.38 Modified for output incomplete and EIT Changes.
58 28-May-2012 rajganga 115.39 14086377 Modified l_flag,assignment status,date in submit request.
59 30-May-2012 rajganga 115.40 14086055 Modified Start and End Date Logic.
60 30-May-2012 rajganga 115.41 14086055 Modified get_eff_end_date.
61 31-May-2012 rsadhana 115.42 14086055 Modified the cursor csr_get_start_end_dates.
62 31-May-2012 rsadhana 115.43 14086055 Added Logic to handle Aggregation in case of Terminated
63 Employees.
64 11-Jun-2012 rajganga Assignment Number Change.
65 19-Jun-2012 rajganga 115.44 End Date Changes.
66 20-Jun-2012 rajganga 115.45 14219738 Modified Effective Date in the output file
67 20-Jun-2012 rajganga 115.46 14219738 Removed all global variables.
68 27-Jun-2012 rajganga 115.47 14231920 Modified to fetch middle name for second forename.
69 05-Jul-2012 rajganga 115.48 14231920 Restructured xml generation for EAS.
70 17-Jul-2012 rajganga 115.49 14309887 Terminated emps before start of the tax year will not be included.
71 24-Jul-2012 rajganga 115.50 14309887 NINO changes.
72 09-Aug-2012 rajganga 115.51 14476621 Fixed Invalid Tax code Issue.
73 14-Aug-2012 ssarap 115.52 14480715 Corrected the validation message for last name.
74 24-Aug-2012 rajganga 115.53 14531814 Fixed Invalid End date issue.
75 11-Sep-2012 rajganga 115.54 14576477 Fixed Action Information Issues.
76 27-Sep-2012 krreddy 115.55 14650604 Modified to stop spawning output process when any validation error occurs.
77 28-Sep-2012 rajganga 115.56 14650604 Fixed Tax code issue.
78 03-Oct-2012 rajganga 115.57 14650604 New Starter RTI Sent flag will be set for all successful employees.
79 04-Oct-2012 rajganga 115.58 14650604 Fixed OVN Issue.
80 15-Oct-2012 rajganga 115.59 14737182 Fixed missing Tax code issue.
81 16-Nov-2012 rajganga 115.60 15860147 Fixed missing Tax code issue for terminated assignments.
82 19-Nov-2012 rajganga 115.61 15860147 Fixed missing Tax code issue for terminated assignments.
83 20-Nov-2012 rajganga 115.62 15860147 Fixed termination date for aggregated assignments.
84 20-Nov-2012 rajganga 115.63 15860147 Fixed retry duplicate header issue.
85 29-Nov-2012 rajganga 115.64 15914459 Fixed Rti sent for other asgs of PAYE Agg.
86 03-Jan-2013 ssarap 115.65 15959015 Employees not pickup up. Modified the cursor to
87 fix the issue.
88 09-Jan-2013 achandwa 115.66 15989627 Employees not pickup up. Modified the range cursor to
89 to use per_all_people_f instead of per_people_f
90 10-Jan-2013 achandwa 115.70 Modified date_of_leaving validation.
91 28-Jan-2013 ssarap 115.71 16221548 Changed the csr_asg cursor.
92 08-Mar-2013 ssarap 115.72 16456256 Added Date track check to sql statement in procedure person_addr_validations_nino
93 25-Mar-2013 ssarap 115.74 16536044 Modified csr_asg
94 =========================================================================================================*/
95
96 g_package CONSTANT VARCHAR2(20):= 'pay_gb_rti.';
97 g_paye_details_id number;
98 g_validation_check VARCHAR2(1) := 'Y';
99
100 -- Change the code numbers eas
101 /*
102 EAS Data Archive Structure
103 --------------------------------------
104 Action Context Type : PA
105 Action_Info_Category: RTI PAYROLL INFO
106
107 Action_Information4 : Employer Name
108 Action_Information6 : Hmrc Office Number
109 Action_Information7 : Employer Paye Reference
110 Action_Information8: Payroll Product Version
111 Action_Information11: Employer AO Reference
112 Action_Information12: Sender ID
113 Action_Information13: Test Indicator
114 Action_Information14: Request ID
115
116
117 Action Context Type : AAP
118 Action_Info_Category: GB RTI EMPLOYEE DETAILS
119
120 Action_Information3 : Last Name
121 Action_Information4 : First Name
122 Action_Information5 : Middle Name
123 Action_Information6 : Title
124 Action_Information7 : Ni Number
125 Action_Information8 : Date Of Birth
126 Action_Information9 : Gender
127 Action_Information10 : Aggregate PAYE flag
128 Action_Information11 : Multiple Asg flag
129 Action_Information17 : Employee Number
130 Action_Information18 : Current Employee Flag
131 Action_Information19 : Actual Termination Date
132
133 Action Context Type : AAP
134 Action_Info_Category: ADDRESS DETAILS
135
136 Action_Information5 : Address Line1
137 Action_Information6 : Address Line2
138 Action_Information7 : Address Line3
139 Action_Information8 : Address Line4 : City/Town
140 Action_Information12: Postal Code
141 Action_Information13: Country
142
143
144 Action Context Type : AAP
145 Action_Info_Category: GB RTI ASG DETAILS
146
147 Action_Information3 : Payroll Id
148 Action_Information4 : Irregular Employment Indicator
149 Action_Information5 : Start Date
150 Action_Information6 : End Date
151 Action_Information7: Tax Code
152 Action_Information8: Tax Basis
153 */
154
155
156 -- This table will be used as a transit variable to pass information column values to
157 -- archive api
158 TYPE act_info_rec IS RECORD
159 ( assignment_id number(20)
160 ,person_id number(20)
161 ,effective_date date
162 ,action_info_category varchar2(50)
163 ,act_info1 varchar2(300)
164 ,act_info2 varchar2(300)
165 ,act_info3 varchar2(300)
166 ,act_info4 varchar2(300)
167 ,act_info5 varchar2(300)
168 ,act_info6 varchar2(300)
169 ,act_info7 varchar2(300)
170 ,act_info8 varchar2(300)
171 ,act_info9 varchar2(300)
172 ,act_info10 varchar2(300)
173 ,act_info11 varchar2(300)
174 ,act_info12 varchar2(300)
175 ,act_info13 varchar2(300)
176 ,act_info14 varchar2(300)
177 ,act_info15 varchar2(300)
178 ,act_info16 varchar2(300)
179 ,act_info17 varchar2(300)
180 ,act_info18 varchar2(300)
181 ,act_info19 varchar2(300)
182 ,act_info20 varchar2(300)
183 ,act_info21 varchar2(300)
184 ,act_info22 varchar2(300)
185 ,act_info23 varchar2(300)
186 ,act_info24 varchar2(300)
187 ,act_info25 varchar2(300)
188 ,act_info26 varchar2(300)
189 ,act_info27 varchar2(300)
190 ,act_info28 varchar2(300)
191 ,act_info29 varchar2(300)
192 ,act_info30 varchar2(300)
193 );
194
195 TYPE action_info_table IS TABLE OF act_info_rec INDEX BY BINARY_INTEGER;
196 ---
197 --
198 --
199 -- Function to fetch address details (CONTEXT - ADDRESS DETAILS)
200 FUNCTION fetch_address_rec(p_person_id IN NUMBER,
201 p_assignment_id IN NUMBER,
202 p_effective_date IN DATE,
203 p_addr_rec OUT nocopy act_info_rec) return boolean IS
204
205
206 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_address_rec';
207 l_arch boolean;
208 --Address Details
209 l_address_line1 per_addresses.address_line1%TYPE;
210 l_address_line2 per_addresses.address_line2%TYPE;
211 l_address_line3 per_addresses.address_line3%TYPE;
212 l_address_line4 per_addresses.address_line3%TYPE;
213
214 cursor csr_address is
215 select upper(substr(trim(addr.address_line1),1,35)) addr1,
216 upper(substr(trim(addr.address_line2),1,35)) addr2,
217 upper(substr(trim(addr.address_line3),1,35)) addr3,
218 substr(addr.postal_code,1,10) post_code,
219 upper(substr(trim(addr.town_or_city),1,35)) addr4,
220 upper(substr(trim(addr.country),1,35)) country
221 from per_addresses addr
222 where addr.person_id = p_person_id
223 and ( addr.primary_flag = 'Y'
224 or addr.primary_flag is null)
225 and p_effective_date between nvl(addr.date_from,fnd_date.canonical_to_date('0001/01/01 00:00:00'))
226 and nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31 00:00:00'));
227
228
229 l_addr_rec csr_address%rowtype;
230
231 BEGIN
232 hr_utility.set_location('Entering : '||l_proc,111);
233 l_arch := true;
234
235 open csr_address;
236 fetch csr_address into l_addr_rec;
237 close csr_address;
238
239 l_address_line1 := l_addr_rec.addr1;
240 l_address_line2 := l_addr_rec.addr2;
241 l_address_line3 := l_addr_rec.addr3;
242 l_address_line4 := l_addr_rec.addr4;
243
244 -- Swapping from Address line4 to line2
245 if l_address_line3 = ' '
246 then
247 l_address_line3 := l_address_line4;
248 l_address_line4 := ' ';
249 end if;
250
251 if l_address_line2 = ' '
252 then
253 l_address_line2 := l_address_line3;
254 l_address_line3 := l_address_line4;
255 l_address_line4 := ' ';
256 end if;
257
258 if LENGTH(TRIM(l_address_line4)) > 0
259 then
260 l_address_line4 := l_address_line4;
261 else
262 l_address_line4 := ' ';
263 end if;
264
265 if LENGTH(TRIM(l_address_line3)) > 0
266 then
267 l_address_line3 := l_address_line3;
268 else
269 l_address_line3 := l_address_line4;
270 l_address_line4 := ' ';
271 end if;
272
273 if LENGTH(TRIM(l_address_line2)) > 0
274 then
275 l_address_line2 := l_address_line2;
276 else
277 l_address_line2 := l_address_line3;
278 l_address_line3 := l_address_line4;
279 l_address_line4 := ' ';
280 end if;
281
282 -- For Foreign Country, postal code will not be reported , where as Country will be reported
283 -- For GB , postal code will be reported
284 IF l_addr_rec.country = 'GB' THEN
285 if l_addr_rec.post_code <> ' ' or l_addr_rec.post_code is not null then
286 l_addr_rec.country := ' ';
287 end if;
288 else
289 l_addr_rec.post_code:= ' ';
290 end if;
291
292 --Trace Messages
293 hr_utility.trace('Address Line 1 : '||l_address_line1);
294 hr_utility.trace('Address Line 2 : '||l_address_line2);
295 hr_utility.trace('Address Line 3 : '||l_address_line3);
296 hr_utility.trace('Address Line 4 : '||l_address_line4);
297
298 p_addr_rec.assignment_id := p_assignment_id;
299 p_addr_rec.action_info_category := 'ADDRESS DETAILS';
300 p_addr_rec.act_info5 := l_address_line1;
301 p_addr_rec.act_info6 := l_address_line2;
302 p_addr_rec.act_info7 := l_address_line3;
303 p_addr_rec.act_info8 := l_address_line4;
304 p_addr_rec.act_info12 := l_addr_rec.post_code;
305 p_addr_rec.act_info13 := l_addr_rec.country;
306
307 hr_utility.set_location('Leaving: '||l_proc,999);
308 return l_arch;
309
310 END fetch_address_rec;
311 --
312 -- Function to fetch person details (CONTEXT - GB RTI EMPLOYEE DETAILS)
313 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
314 p_effective_date IN DATE,
315 p_person_rec OUT nocopy act_info_rec) return boolean IS
316
317 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
318 l_arch boolean;
319 l_employee_flag varchar2(10);
320 l_start_date date;
321 l_end_date date;
322
323 cursor c_get_employee_flag(asg_id number) is
324 select distinct pap.effective_start_date , pap.effective_end_date , pap.current_employee_flag current_employee_flag
325 from per_all_assignments_f paa,
326 per_all_people_f pap,
327 per_periods_of_service serv
328 where paa.assignment_id = asg_id
329 and pap.person_id = paa.person_id
330 and paa.period_of_service_id = serv.period_of_service_id and
331 (serv.actual_termination_date is not null and serv.actual_termination_date+1 between pap.effective_start_date and pap.effective_end_date);
332
333
334 cursor csr_person_details_active IS
335 SELECT
336 pap.person_id p_person_id,
337 paa.assignment_id,
338 paa.assignment_number emp_no,
339 substr(trim(pap.last_name), 1,35) last_name,
340 substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
341 substr(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
342 pap.title title,
343 substr(pap.national_identifier,1,9) national_identifier,
344 pap.date_of_birth date_of_birth,
345 substr(pap.sex,1,1) sex ,
346 decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
347 decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
348 current_employee_flag,
349 pap.effective_start_date effective_start_date,
350 serv.actual_termination_date actual_termination_date
351 FROM pay_assignment_actions act,
352 per_all_assignments_f paa,
353 per_all_people_f pap,
354 per_periods_of_service serv,
355 per_assignment_status_types past
356 where act.assignment_action_id = p_assactid
357 and act.assignment_id = paa.assignment_id
358 and paa.assignment_status_type_id = past.assignment_status_type_id
359 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
360 and paa.person_id = pap.person_id
361 and paa.period_of_service_id = serv.period_of_service_id
362 and p_effective_date between pap.effective_start_date and pap.effective_end_date
363 and p_effective_date between paa.effective_start_date and paa.effective_end_date
364 order by pap.effective_start_date desc;
365
366
367 cursor csr_person_details_terminated IS
368 SELECT
369 pap.person_id p_person_id,
370 paa.assignment_id,
371 paa.assignment_number emp_no,
372 substr(trim(pap.last_name), 1,35) last_name,
373 substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
374 substr(trim(pap.middle_names), 1,least(decode(instr(trim(pap.middle_names),' '),0,35,instr(trim(pap.middle_names),' ')),35)) middle_name,
375 pap.title title,
376 substr(pap.national_identifier,1,9) national_identifier,
377 pap.date_of_birth date_of_birth,
378 substr(pap.sex,1,1) sex ,
379 decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
380 decode(pap.per_information9,'Y','Y',NULL) multiple_asg_flag,
381 current_employee_flag,
382 pap.effective_start_date effective_start_date,
383 serv.actual_termination_date actual_termination_date
384 FROM pay_assignment_actions act,
385 per_all_assignments_f paa,
386 per_all_people_f pap,
387 per_periods_of_service serv,
388 per_assignment_status_types past
389 where act.assignment_action_id = p_assactid
390 and act.assignment_id = paa.assignment_id
391 and paa.assignment_status_type_id = past.assignment_status_type_id
392 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
393 and paa.person_id = pap.person_id
394 and paa.period_of_service_id = serv.period_of_service_id
395 order by pap.effective_start_date desc;
396
397 l_person_rec csr_person_details_active%rowtype;
398 BEGIN
399
400 hr_utility.set_location('Entering: '||l_proc,1);
401 l_arch := true;
402
403 open csr_person_details_active;
404 fetch csr_person_details_active into l_person_rec;
405
406 if csr_person_details_active%notfound then
407 open csr_person_details_terminated;
408 fetch csr_person_details_terminated into l_person_rec;
409 if csr_person_details_terminated%notfound then
410 l_arch := false;
411 end if;
412 if l_person_rec.actual_termination_date is null then
413 l_person_rec.current_employee_flag := 'Y';
414 end if;
415 close csr_person_details_terminated;
416 end if;
417 close csr_person_details_active;
418
419 hr_utility.trace('Termination date ' ||l_person_rec.actual_termination_date);
420 hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
421 hr_utility.trace('Current employee flag' ||l_person_rec.current_employee_flag);
422
423 p_person_rec.person_id := l_person_rec.p_person_id;
424 p_person_rec.assignment_id := l_person_rec.assignment_id;
425 p_person_rec.action_info_category := 'GB RTI EMPLOYEE DETAILS';
426 p_person_rec.act_info3 := l_person_rec.last_name;
427 p_person_rec.act_info4 := l_person_rec.first_name;
428 p_person_rec.act_info5 := l_person_rec.middle_name;
429 p_person_rec.act_info6 := l_person_rec.title;
430 p_person_rec.act_info7 := l_person_rec.national_identifier;
431 p_person_rec.act_info8 := to_char(l_person_rec.date_of_birth,'YYYY-MM-DD');
432 p_person_rec.act_info9 := l_person_rec.sex;
433 p_person_rec.act_info10 := l_person_rec.agg_paye_flag;
434 p_person_rec.act_info11 := l_person_rec.multiple_asg_flag;
435 p_person_rec.act_info17 := l_person_rec.emp_no;
436 p_person_rec.act_info18 := l_person_rec.current_employee_flag;
437 p_person_rec.act_info19 := to_char(l_person_rec.actual_termination_date,'YYYY-MM-DD');
438
439 hr_utility.set_location('Leaving: '||l_proc,999);
440 return l_arch;
441 END fetch_person_rec;
442 --
443 -- Function to fetch Assignment details (CONTEXT - GB_RTI_ASG_DETAILS)
444 FUNCTION fetch_eas_asg_rec(p_effective_date IN DATE,
445 p_assactid IN NUMBER,
446 p_person_rec IN act_info_rec,
447 p_eas_rec OUT nocopy act_info_rec) return boolean IS
448
449 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_eas_asg_rec';
450 l_arch boolean;
451 l_irregular_emp_ind varchar2(1) := 'N';
452 l_tax_screen_value varchar2(60);
453 l_taxbasis_screen_value varchar2(60);
454 l_active_flag varchar2(1) := 'N';
455 l_effective_date date;
456 l_asg_no varchar2(100);
457 l_tax_ref varchar2(100);
458 l_asg_id number;
459 l_prev_asg_id number;
460 l_eff_date date;
461 l_eff_start_date date;
462 l_eff_end_date date;
463 l_final_eff_start_date date;
464 l_final_eff_end_date date;
465 l_tax_year_start date;
466 l_paye_id number;
467 l_tax_code_ivid number;
468 l_tax_basis_ivid number;
469
470 cursor csr_get_asg_no is
471 select assignment_number
472 from per_all_assignments_f paaf
473 where assignment_id = p_person_rec.assignment_id
474 and paaf.effective_start_date =
475 (select max(paaf1.effective_start_date)
476 from per_all_assignments_f paaf1 where
477 paaf1.assignment_id = paaf.assignment_id
478 and paaf1.assignment_type = 'E'
479 and paaf1.effective_start_date <= l_eff_date
480 );
481
482 cursor csr_tax_code_basis is
483 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
484 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis
485 FROM pay_element_entries_f e,
486 pay_element_entry_values_f v,
487 pay_input_values_f iv,
488 pay_element_links_f link
489 WHERE e.assignment_id = p_person_rec.assignment_id
490 AND link.element_type_id = g_paye_details_id
491 AND e.element_link_id = link.element_link_id
492 AND e.element_entry_id = v.element_entry_id
493 AND iv.input_value_id = v.input_value_id
494 AND iv.legislation_code = 'GB'
495 AND l_effective_date BETWEEN link.effective_start_date AND link.effective_end_date
496 AND l_effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date
497 AND l_effective_date BETWEEN v.effective_start_date AND v.effective_end_date
498 AND l_effective_date between e.effective_start_date and e.effective_end_date;
499
500 cursor get_agg_start_end_dates is
501 select
502 pay_gb_eoy_archive.get_agg_active_start(p_person_rec.assignment_id,l_tax_ref,l_effective_date) start_date,
503 pay_gb_eoy_archive.get_agg_active_end(p_person_rec.assignment_id,l_tax_ref,l_effective_date) end_date
504 from dual;
505
506 cursor csr_get_irregular_details is
507 SELECT AEI_INFORMATION3
508 FROM PER_ASSIGNMENT_EXTRA_INFO
509 WHERE INFORMATION_TYPE = 'GB_PAY_RTI'
510 AND AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI'
511 AND assignment_id = p_person_rec.assignment_id;
512
513 cursor csr_parameter_info is
514 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
515 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date
516 from pay_payroll_actions pact,
517 pay_assignment_actions paa
518 where paa.assignment_action_id = p_assactid
519 and pact.payroll_action_id = paa.payroll_action_id;
520
521 cursor csr_check_cont_emp(p_person_id number,p_tax_ref varchar2) is
522 select min(paaf.EFFECTIVE_START_DATE) , max(paaf1.EFFECTIVE_END_DATE) , max(paaf.assignment_id) l_prev_asg_id
523 from
524 per_all_people_f peo,
525 per_all_people_f peo1,
526 per_all_assignments_f paaf,
527 per_all_assignments_f paaf1,
528 per_assignment_status_types past,
529 per_assignment_status_types past1,
530 pay_all_payrolls_f papf ,
531 pay_all_payrolls_f papf1,
532 hr_soft_coding_keyflex flex
533 where
534 peo.person_id = p_person_id
535 and paaf.person_id = peo.person_id
536 and paaf.assignment_status_type_id = past.assignment_status_type_id
537 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
538 and papf.payroll_id = paaf.payroll_id
539 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
540 and upper(p_tax_ref) = upper(flex.segment1)
541 and peo1.person_id = p_person_id
542 and paaf1.person_id = peo1.person_id
543 and paaf1.assignment_id = p_person_rec.assignment_id
544 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
545 and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
546 and paaf.assignment_type = 'E'
547 and paaf1.assignment_type = 'E'
548 and papf1.payroll_id = paaf1.payroll_id
549 and flex.SOFT_CODING_KEYFLEX_ID = papf1.SOFT_CODING_KEYFLEX_ID
550 and upper(p_tax_ref) = upper(flex.segment1)
551 and paaf.EFFECTIVE_END_DATE = paaf1.EFFECTIVE_START_DATE - 1
552 and paaf.assignment_id <> paaf1.assignment_id
553 and exists
554 (select '1' from per_all_assignments_f paafs,
555 per_assignment_status_types pasts
556 where paafs.assignment_id = paaf.assignment_id
557 and paafs.effective_start_date = paaf1.effective_start_date
558 and paafs.assignment_status_type_id = pasts.assignment_status_type_id
559 and pasts.per_system_status in ('TERM_ASSIGN')
560 ) ;
561
562
563 cursor csr_get_start_end_dates(p_eff_date date,p_tax_ref varchar2) is
564 select paaf.effective_start_date,paaf.effective_end_date
565 from
566 per_all_assignments_f paaf,
567 pay_all_payrolls_f papf ,
568 hr_soft_coding_keyflex flex,
569 per_assignment_status_types past
570 where
571 paaf.assignment_id = p_person_rec.assignment_id
572 and papf.payroll_id = paaf.payroll_id
573 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
574 and upper(p_tax_ref) = upper(flex.segment1)
575 and paaf.assignment_status_type_id = past.assignment_status_type_id
576 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
577 and paaf.effective_start_date =
578 (select max(paaf1.effective_start_date) from per_all_assignments_f paaf1,per_assignment_status_types past1
579 where paaf1.assignment_id = paaf.assignment_id
580 and paaf1.assignment_type = 'E'
581 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
582 and past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
583 and paaf1.effective_start_date <= p_eff_date
584 )
585 and p_eff_date between papf.effective_start_date and papf.effective_end_date;
586
587
588 BEGIN
589 hr_utility.set_location('Entering: '||l_proc,1);
590 l_arch := true;
591
592 SELECT distinct element_type_id
593 into g_paye_details_id
594 FROM pay_element_types_f
595 WHERE element_name = 'PAYE Details';
596
597 open csr_parameter_info;
598 fetch csr_parameter_info into l_tax_ref,l_eff_date;
599 close csr_parameter_info;
600
601
602 open csr_check_cont_emp(p_person_rec.person_id,l_tax_ref);
603 fetch csr_check_cont_emp into l_final_eff_start_date,l_final_eff_end_date,l_prev_asg_id;
604
605 if l_final_eff_start_date is not null then
606 close csr_check_cont_emp;
607 hr_utility.trace('Continous emp l_final_eff_start_date : '||l_final_eff_start_date);
608 hr_utility.trace('Continous emp l_final_eff_end_date : '||l_final_eff_end_date);
609
610 l_final_eff_start_date := get_eff_start_date(l_prev_asg_id,l_final_eff_start_date,l_tax_ref);
611 l_final_eff_end_date := get_eff_end_date(p_person_rec.assignment_id,l_final_eff_end_date,l_tax_ref);
612 hr_utility.trace('Asg l_final_eff_start_date : '||l_final_eff_start_date);
613 hr_utility.trace('Asg l_final_eff_end_date : '||l_final_eff_end_date);
614 else
615 close csr_check_cont_emp;
616 open csr_get_start_end_dates(l_eff_date,l_tax_ref);
617 fetch csr_get_start_end_dates into l_eff_start_date,l_eff_end_date;
618 if csr_get_start_end_dates%found then
619 l_final_eff_start_date := get_eff_start_date(p_person_rec.assignment_id,l_eff_start_date,l_tax_ref);
620 l_final_eff_end_date := get_eff_end_date(p_person_rec.assignment_id,l_eff_end_date,l_tax_ref);
621 hr_utility.trace('Asg l_final_eff_start_date : '||l_final_eff_start_date);
622 hr_utility.trace('Asg l_final_eff_end_date : '||l_final_eff_end_date);
623 end if;
624 close csr_get_start_end_dates;
625 end if;
626
627
628 l_effective_date := p_effective_date;
629
630
631 -- To fetch Terminated Employee Details
632 if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
633 l_effective_date := least(l_eff_date,(to_date(substr(p_person_rec.act_info19,1,10),'YYYY-MM-DD')));
634 hr_utility.trace('Effective date : '||l_eff_date||' Termination Date : '||p_person_rec.act_info19);
635 hr_utility.trace('l_effective_date : '||l_effective_date);
636 else
637 l_effective_date := least (p_effective_date, l_final_eff_end_date);
638 hr_utility.trace('l_effective_date : '||l_effective_date);
639 end if;
640
641 open csr_get_asg_no;
642 fetch csr_get_asg_no into l_asg_no;
643 close csr_get_asg_no;
644
645 open csr_tax_code_basis;
646 fetch csr_tax_code_basis into l_tax_screen_value,l_taxbasis_screen_value;
647 close csr_tax_code_basis;
648
649 -- To get Agg Start and End date
650 if p_person_rec.act_info10 = 'Y' then
651 open get_agg_start_end_dates;
652 fetch get_agg_start_end_dates into l_final_eff_start_date, l_final_eff_end_date;
653 close get_agg_start_end_dates;
654 end if;
655
656 -- To fetch Terminated Employee Details
657 if p_person_rec.act_info18 is null or p_person_rec.act_info18 <> 'Y' then
658 l_final_eff_end_date := l_effective_date;
659 end if;
660
661 -- Trace Messages
662 hr_utility.set_location('Asg id : '||p_person_rec.assignment_id,20);
663 hr_utility.set_location('Asg no : '||l_asg_no,20);
664 hr_utility.set_location('Tax code : '||l_tax_screen_value,20);
665 hr_utility.set_location('Tax basis : '||l_taxbasis_screen_value,20);
666 hr_utility.set_location('Start Date: '||l_final_eff_start_date,20);
667 hr_utility.set_location('End Date : '||l_final_eff_end_date,20);
668
669 -- If tax basis is non cumulative('N') then making it as 'Y' else ' '
670 if l_taxbasis_screen_value = 'N' then
671 l_taxbasis_screen_value := 'Y';
672 else
673 l_taxbasis_screen_value := ' ';
674 end if;
675
676 -- To Fetch Irregular Employment Indicator from EIT
677 open csr_get_irregular_details;
678 fetch csr_get_irregular_details into l_irregular_emp_ind;
679 close csr_get_irregular_details;
680
681 -- if end date is 31 Dec 4712 make it as null
682 if l_final_eff_end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') then
683 l_final_eff_end_date := null;
684 End if;
685
686 --if Termination Date is in future make it as null
687 if l_final_eff_end_date > l_eff_date then
688 l_final_eff_end_date := null;
689 end if;
690
691 p_eas_rec.assignment_id := p_person_rec.assignment_id;
692 p_eas_rec.action_info_category := 'GB RTI ASG DETAILS';
693 p_eas_rec.act_info3 := l_asg_no;
694 p_eas_rec.act_info4 := l_irregular_emp_ind;
695 p_eas_rec.act_info5 := to_char(l_final_eff_start_date,'YYYY-MM-DD');
696 p_eas_rec.act_info6 := to_char(l_final_eff_end_date,'YYYY-MM-DD');
697 p_eas_rec.act_info7 := l_tax_screen_value;
698 p_eas_rec.act_info8 := l_taxbasis_screen_value;
699
700
701 hr_utility.set_location('Leaving: '||l_proc,999);
702 return l_arch;
703 END fetch_eas_asg_rec;
704 --
705 --
706 PROCEDURE populate_run_msg(
707 p_assignment_action_id IN NUMBER
708 ,p_message_text IN varchar2
709 )
710 IS
711 PRAGMA AUTONOMOUS_TRANSACTION;
712 BEGIN
713 INSERT INTO pay_message_lines(line_sequence,
714 payroll_id,
715 message_level,
716 source_id,
717 source_type,
718 line_text)
719 VALUES(
720 pay_message_lines_s.nextval
721 ,100
722 ,'F'
723 ,p_assignment_action_id
724 ,'A'
725 ,substr(p_message_text,1,240)
726 );
727 COMMIT;
728 EXCEPTION
729 WHEN OTHERS THEN
730 hr_utility.trace('Error occured in populate_run_msg');
731 RAISE;
732 END populate_run_msg;
733
734 -- Function to validate assignment details for EAS report
735 PROCEDURE eas_asg_etext_validations(p_assactid IN NUMBER,
736 p_effective_date IN DATE,
737 p_tab_rec_data IN action_info_table,
738 edi_validation_fail out nocopy varchar2) IS
739
740 CURSOR get_effective_date IS
741 SELECT ppa.effective_date
742 FROM pay_payroll_actions ppa, pay_assignment_actions paa
743 WHERE ppa.payroll_action_id = paa.payroll_action_id
744 AND paa.assignment_action_id = p_assactid;
745
746 l_proc CONSTANT VARCHAR2(50):= g_package||'eas_asg_etext_validations';
747 l_ovn number;
748 l_action_id number;
749
750 l_assignment_number per_assignments_f.assignment_number%TYPE;
751 l_payroll_id varchar2(100);
752 l_process_type_in_use varchar2(100);
753 l_tax_code_in_use varchar2(100);
754 l_tax_basis_in_use varchar2(100);
755 l_msg_value varchar2(100);
756 l_eff_date date;
757 l_eas_start_asg_date date;
758 l_eas_end_asg_date date;
759
760 BEGIN
761 hr_utility.set_location('Entering: '||l_proc,1);
762
763 edi_validation_fail := 'N'; -- Initialized validation fail to N
764 l_assignment_number := p_tab_rec_data(0).act_info17;
765 l_payroll_id := p_tab_rec_data(2).act_info3;
766 l_process_type_in_use := p_tab_rec_data(2).act_info4;
767 l_tax_code_in_use := p_tab_rec_data(2).act_info7;
768 l_tax_basis_in_use := p_tab_rec_data(2).act_info8;
769 l_eas_start_asg_date := (to_date(substr(p_tab_rec_data(2).act_info5,1,10),'YYYY-MM-DD'));
770 l_eas_end_asg_date := (to_date(substr(p_tab_rec_data(2).act_info6,1,10),'YYYY-MM-DD'));
771
772 -- Trace Messages
773 hr_utility.set_location('Payroll Id : '||l_payroll_id,10);
774 hr_utility.set_location('Process type : '||l_process_type_in_use,20);
775 hr_utility.set_location('Tax code : '||l_tax_code_in_use,30);
776 hr_utility.set_location('Tax basis : '||l_tax_basis_in_use,40);
777 hr_utility.set_location('Start date : '||l_eas_start_asg_date,50);
778 hr_utility.set_location('End date : '||l_eas_end_asg_date,60);
779
780
781 IF ((l_payroll_id <> ' ' and l_payroll_id IS NOT NULL)
782 and validate_input(l_payroll_id,'PAYROLLID') > 0) THEN
783 populate_run_msg(p_assactid,'Payroll ID has invalid characters for the assignment ' || l_assignment_number||'.');
784 fnd_file.put_line (fnd_file.LOG,'Payroll ID has invalid characters for the assignment ' || l_assignment_number||'.');
785 edi_validation_fail := 'Y';
786 END IF;
787
788 IF l_eas_start_asg_date IS NOT NULL
789 and date_validate(p_assactid,'UK_EMPL_DATE',l_eas_start_asg_date) = 0 THEN
790 populate_run_msg(p_assactid,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
791 fnd_file.put_line (fnd_file.LOG,'The start date of employment for the assignment ' || l_assignment_number || ' is invalid.');
792 edi_validation_fail := 'Y';
793 END IF;
794
795
796 IF l_eas_end_asg_date IS NOT NULL
797 and date_validate(p_assactid,'UK_EMPL_DATE_LEAVING',l_eas_end_asg_date) = 0 THEN
798 populate_run_msg(p_assactid,' The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
799 fnd_file.put_line (fnd_file.LOG,'The end date of employment for the assignment ' || l_assignment_number || ' is invalid.');
800 edi_validation_fail := 'Y';
801 END IF;
802
803 OPEN get_effective_date;
804 FETCH get_effective_date
805 INTO l_eff_date;
806 CLOSE get_effective_date;
807
808
809 l_eff_date := trunc (l_eff_date);
810 hr_utility.trace('l_eff_date = '|| l_eff_date);
811
812 l_msg_value := pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code_in_use,l_eff_date); -- Tax code validation
813
814 --Tax code
815
816 IF (l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL) then
817 populate_run_msg(p_assactid, 'The Tax Code is missing for assignment ' || l_assignment_number||'.');
818 fnd_file.put_line (fnd_file.LOG,'The Tax Code is missing for assignment ' || l_assignment_number||'.');
819 edi_validation_fail := 'Y';
820 END IF;
821
822
823 IF (l_tax_code_in_use <> ' ' and l_tax_code_in_use IS NOT NULL)
824 AND (l_msg_value <> ' ') THEN
825 populate_run_msg(p_assactid,'The Tax Code ' || l_tax_code_in_use || ' for assignment ' || l_assignment_number||' is invalid.');
826 fnd_file.put_line (fnd_file.LOG,'The Tax Code ' || l_tax_code_in_use || ' for assignment ' || l_assignment_number||' is invalid.');
827 edi_validation_fail := 'Y';
828 END IF;
829
830 -- Tax basis validations
831
832 --Tax basis cannot be present if tax code is absent
833 IF (l_tax_basis_in_use <>' ' and l_tax_basis_in_use IS NOT NULL) AND (l_tax_code_in_use = ' ' OR l_tax_code_in_use IS NULL)THEN
834 populate_run_msg(p_assactid, 'The Tax Code is not present although, Tax Basis is present for assignment ' || l_assignment_number||'.');
835 fnd_file.put_line (fnd_file.LOG,'The Tax Code is not present although, Tax Basis is present for assignment ' || l_assignment_number||'.');
836 edi_validation_fail := 'Y';
837 END IF;
838
839 IF (l_tax_basis_in_use <>' ' AND l_tax_basis_in_use IS NOT NULL) AND (l_tax_basis_in_use <> 'Y' )THEN
840 populate_run_msg(p_assactid, 'Tax Basis ' ||l_tax_basis_in_use ||' for assignment ' || l_assignment_number||' is invalid.');
841 fnd_file.put_line (fnd_file.LOG,'Tax Basis ' ||l_tax_basis_in_use ||' for assignment ' || l_assignment_number||' is invalid.');
842 edi_validation_fail := 'Y';
843 END IF;
844
845 hr_utility.set_location('Leaving: '||l_proc,111);
846
847 END eas_asg_etext_validations;
848 --
849 -- Person Address validations
850 PROCEDURE person_addr_validations(p_assactid IN NUMBER,
851 p_effective_date IN DATE,
852 p_tab_rec_data IN action_info_table,
853 p_archive_type IN varchar2 default null,
854 edi_validation_fail out nocopy varchar2) IS
855
856 l_proc CONSTANT VARCHAR2(50):= g_package||'person_addr_validations';
857 l_ovn number;
858 l_action_id number;
859
860
861 l_assignment_number per_assignments_f.assignment_number%TYPE;
862
863 --address details
864 l_address_line1 per_addresses.address_line1%TYPE;
865 l_address_line2 per_addresses.address_line2%TYPE;
866 l_address_line3 per_addresses.address_line3%TYPE;
867 l_address_line4 per_addresses.address_line3%TYPE;
868 l_town_or_city per_addresses.town_or_city%TYPE;
869 l_postal_code per_addresses.postal_code%TYPE;
870 l_country per_addresses.country%TYPE; --R
871
872
873 --person details
874 l_last_name per_people_f.last_name%TYPE;
875 l_first_name per_people_f.first_name%TYPE;
876 l_middle_name per_people_f.middle_names%TYPE;
877 l_national_identifier per_people_f.national_identifier%TYPE;
878 l_title per_people_f.title%TYPE;
879 l_date_of_birth per_people_f.date_of_birth%TYPE;
880 l_sex per_people_f.sex%TYPE;
881
882
883 l_count_char_errors number;
884 l_count_missing_val number;
885
886 l_effective_date date;
887 l_start_date date;
888 l_end_date date;
889
890 l_year1 number;
891 l_year2 number;
892
893 BEGIN
894 hr_utility.set_location('Entering '||l_proc,10);
895
896 l_count_char_errors := 0;
897 l_count_missing_val := 0;
898
899 l_address_line1 := p_tab_rec_data(1).act_info5;
900 l_address_line2 := p_tab_rec_data(1).act_info6;
901 l_address_line3 := p_tab_rec_data(1).act_info7;
902 l_town_or_city := p_tab_rec_data(1).act_info8;
903 l_country := p_tab_rec_data(1).act_info13;
904 l_postal_code := p_tab_rec_data(1).act_info12;
905
906 l_last_name := p_tab_rec_data(0).act_info3;
907 l_first_name := p_tab_rec_data(0).act_info4;
908 l_middle_name := p_tab_rec_data(0).act_info5;
909 l_national_identifier := p_tab_rec_data(0).act_info7;
910 l_title := p_tab_rec_data(0).act_info6;
911 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
912 l_sex := p_tab_rec_data(0).act_info9;
913
914 l_assignment_number := p_tab_rec_data(0).act_info17;
915
916 l_year1 := to_number(to_char(p_effective_date,'YYYY'));
917 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
918 l_year1 := l_year1- l_year2;
919
920
921 --address validation
922
923 --Address line 1 and 2 are mandatory as Start date is not null
924
925 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
926 populate_run_msg(p_assactid,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
927 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the assignment ' || l_assignment_number || ' is missing.');
928 edi_validation_fail := 'Y';
929 l_count_missing_val := l_count_missing_val + 1;
930
931 elsIF validate_input(l_address_line1,'ADDRESS') > 0 THEN
932 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
933 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
934 edi_validation_fail := 'Y';
935 l_count_char_errors := l_count_char_errors + 1;
936 end if;
937
938 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
939 populate_run_msg(p_assactid,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
940 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the assignment ' || l_assignment_number || ' is missing.');
941 edi_validation_fail := 'Y';
942 l_count_missing_val := l_count_missing_val + 1;
943
944 elsIF validate_input(l_address_line2,'ADDRESS') > 0 THEN
945 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
946 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
947 edi_validation_fail := 'Y';
948 l_count_char_errors := l_count_char_errors + 1;
949 end if;
950
951 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
952 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
953 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
954 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
955 edi_validation_fail := 'Y';
956 l_count_char_errors := l_count_char_errors + 1;
957 END IF;
958 END IF;
959
960 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
961 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
962 populate_run_msg(p_assactid,'The Town or City ' || l_town_or_city || ' of the assignment '|| l_assignment_number || ' has invalid characters.');
963 fnd_file.put_line (fnd_file.LOG,'The Town or City ' || l_town_or_city ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
964 edi_validation_fail := 'Y';
965 l_count_char_errors := l_count_char_errors + 1;
966 END IF;
967 END IF;
968
969 IF l_country <> ' ' and l_country IS NOT NULL THEN
970 IF validate_input(l_country,'COUNTRY') > 0 THEN
971 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
972 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
973 edi_validation_fail := 'Y';
974 l_count_char_errors := l_count_char_errors + 1;
975 END IF;
976 END IF;
977
978 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
979 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
980 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
981 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
982 edi_validation_fail := 'Y';
983 l_count_char_errors := l_count_char_errors + 1;
984 END IF;
985 END IF;
986
987 --person validation
988
989 IF l_last_name = ' ' OR l_last_name is null THEN
990 populate_run_msg(p_assactid,'The Last Name of the assignment ' || l_assignment_number || ' is missing.');
991 fnd_file.put_line (fnd_file.LOG,'The Last Name of the assignment '|| l_assignment_number || ' is missing.');
992 edi_validation_fail := 'Y';
993 l_count_missing_val := l_count_missing_val + 1;
994
995 elsif validate_input(l_last_name,'NAME') > 0 then
996 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
997 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
998 edi_validation_fail := 'Y';
999 l_count_char_errors := l_count_char_errors + 1;
1000 END IF;
1001
1002 IF l_first_name = ' ' OR l_first_name is null THEN
1003 populate_run_msg(p_assactid,'The First Name of the assignment '|| l_assignment_number || ' is missing.');
1004 fnd_file.put_line (fnd_file.LOG,'The First Name of the assignment ' || l_assignment_number || ' is missing.');
1005 edi_validation_fail := 'Y';
1006 l_count_missing_val := l_count_missing_val + 1;
1007
1008 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
1009 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the assignment ' || l_assignment_number || ' has invalid characters.');
1010 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1011 edi_validation_fail := 'Y';
1012 l_count_char_errors := l_count_char_errors + 1;
1013 END IF;
1014
1015 IF l_middle_name <> ' ' AND l_middle_name is not null THEN
1016 IF validate_input(l_middle_name,'NAME') > 0 THEN
1017 populate_run_msg(p_assactid,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1018 fnd_file.put_line (fnd_file.LOG,'The Middle Name ' || l_middle_name || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1019 edi_validation_fail := 'Y';
1020 l_count_char_errors := l_count_char_errors + 1;
1021 END IF;
1022 END IF;
1023
1024
1025 IF l_title <> ' ' AND l_title is not null THEN
1026 IF validate_input(l_title,'TITLE') > 0 THEN
1027 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1028 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1029 edi_validation_fail := 'Y';
1030 l_count_char_errors := l_count_char_errors + 1;
1031 END IF;
1032 END IF;
1033
1034 IF l_national_identifier is not null AND
1035 hr_gb_utility.ni_validate(l_national_identifier,p_effective_date) <> 0 THEN
1036 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1037 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the assignment ' || l_assignment_number || ' has invalid characters.');
1038 edi_validation_fail := 'Y';
1039 END IF;
1040
1041 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
1042 populate_run_msg(p_assactid,'Gender is not specified for the assignment ' || l_assignment_number||'.');
1043 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the assignment ' || l_assignment_number||'.');
1044 edi_validation_fail := 'Y';
1045 END IF;
1046
1047 IF l_date_of_birth IS NULL THEN
1048 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is missing.');
1049 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is missing.');
1050 edi_validation_fail := 'Y';
1051 END IF;
1052
1053 IF ((l_date_of_birth > p_effective_date) OR (l_year1 > 130)) THEN
1054 populate_run_msg(p_assactid,'The Date of Birth of the assignment ' || l_assignment_number || ' is invalid.');
1055 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the assignment '|| l_assignment_number || ' is invalid.');
1056 edi_validation_fail := 'Y';
1057 END IF;
1058
1059 hr_utility.set_location('Leaving'||l_proc,10);
1060 END person_addr_validations;
1061 --
1062
1063 -- Person Address validations
1064 PROCEDURE person_addr_validations_nino(p_assactid IN NUMBER,
1065 p_effective_date IN DATE,
1066 p_tab_rec_data IN action_info_table,
1067 p_archive_type IN varchar2 default null,
1068 edi_validation_fail out nocopy varchar2) IS
1069
1070 l_proc CONSTANT VARCHAR2(50):= g_package||'person_addr_validations';
1071 l_ovn number;
1072 l_action_id number;
1073 l_employee_number per_all_people_f.employee_number%TYPE;
1074
1075
1076 l_assignment_number per_assignments_f.assignment_number%TYPE;
1077
1078 --address details
1079 l_address_line1 per_addresses.address_line1%TYPE;
1080 l_address_line2 per_addresses.address_line2%TYPE;
1081 l_address_line3 per_addresses.address_line3%TYPE;
1082 l_address_line4 per_addresses.address_line3%TYPE;
1083 l_town_or_city per_addresses.town_or_city%TYPE;
1084 l_postal_code per_addresses.postal_code%TYPE;
1085 l_country per_addresses.country%TYPE; --R
1086
1087
1088 --person details
1089 l_last_name per_people_f.last_name%TYPE;
1090 l_first_name per_people_f.first_name%TYPE;
1091 l_middle_name per_people_f.middle_names%TYPE;
1092 l_national_identifier per_people_f.national_identifier%TYPE;
1093 l_title per_people_f.title%TYPE;
1094 l_date_of_birth per_people_f.date_of_birth%TYPE;
1095 l_sex per_people_f.sex%TYPE;
1096
1097
1098 l_count_char_errors number;
1099 l_count_missing_val number;
1100
1101 l_effective_date date;
1102 l_start_date date;
1103 l_end_date date;
1104
1105 l_year1 number;
1106 l_year2 number;
1107
1108 BEGIN
1109 hr_utility.set_location('Entering '||l_proc,10);
1110
1111 l_count_char_errors := 0;
1112 l_count_missing_val := 0;
1113
1114 l_address_line1 := p_tab_rec_data(1).act_info5;
1115 l_address_line2 := p_tab_rec_data(1).act_info6;
1116 l_address_line3 := p_tab_rec_data(1).act_info7;
1117 l_town_or_city := p_tab_rec_data(1).act_info8;
1118 l_country := p_tab_rec_data(1).act_info13;
1119 l_postal_code := p_tab_rec_data(1).act_info12;
1120
1121 l_last_name := p_tab_rec_data(0).act_info3;
1122 l_first_name := p_tab_rec_data(0).act_info4;
1123 l_middle_name := p_tab_rec_data(0).act_info5;
1124 l_national_identifier := p_tab_rec_data(0).act_info7;
1125 l_title := p_tab_rec_data(0).act_info6;
1126 l_date_of_birth := (to_date(substr(p_tab_rec_data(0).act_info8,1,10),'YYYY-MM-DD'));
1127 l_sex := p_tab_rec_data(0).act_info9;
1128
1129 l_assignment_number := p_tab_rec_data(0).assignment_id;
1130 l_year1 := to_number(to_char(sysdate,'YYYY'));
1131 l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
1132 l_year1 := l_year1- l_year2;
1133
1134 --address validation
1135 SELECT DISTINCT employee_number into l_employee_number from per_all_people_f where EMPLOYEE_NUMBER IS NOT NULL AND person_id=p_tab_rec_data(0).person_id
1136 and p_effective_date between effective_start_date and effective_end_date; -- added for bug fix# 16456256
1137
1138
1139 IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
1140 populate_run_msg(p_assactid,'The Address Line 1 of the employee ' || l_employee_number || ' is missing.');
1141 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 of the employee ' || l_employee_number || ' is missing.');
1142 edi_validation_fail := 'Y';
1143 l_count_missing_val := l_count_missing_val + 1;
1144
1145 elsIF validate_input(l_address_line1,'ADDRESS') > 0 THEN
1146 populate_run_msg(p_assactid,'The Address Line 1 ' || l_address_line1 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1147 fnd_file.put_line (fnd_file.LOG,'The Address Line 1 ' || l_address_line1 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1148 edi_validation_fail := 'Y';
1149 l_count_char_errors := l_count_char_errors + 1;
1150 end if;
1151
1152 IF l_address_line2 = ' ' OR l_address_line2 IS NULL THEN
1153 populate_run_msg(p_assactid,'The Address Line 2 of the employee ' || l_employee_number || ' is missing.');
1154 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 of the employee ' || l_employee_number || ' is missing.');
1155 edi_validation_fail := 'Y';
1156 l_count_missing_val := l_count_missing_val + 1;
1157
1158 elsIF validate_input(l_address_line2,'ADDRESS') > 0 THEN
1159 populate_run_msg(p_assactid,'The Address Line 2 ' || l_address_line2 || ' of the employee '|| l_employee_number || ' has invalid characters.');
1160 fnd_file.put_line (fnd_file.LOG,'The Address Line 2 ' || l_address_line2 || ' of the employee '|| l_employee_number || ' has invalid characters.');
1161 edi_validation_fail := 'Y';
1162 l_count_char_errors := l_count_char_errors + 1;
1163 end if;
1164
1165 IF l_address_line3 <> ' ' and l_address_line3 IS NOT NULL THEN
1166 IF validate_input(l_address_line3,'ADDRESS') > 0 THEN
1167 populate_run_msg(p_assactid,'The Address Line 3 ' || l_address_line3 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1168 fnd_file.put_line (fnd_file.LOG,'The Address Line 3 ' || l_address_line3 || ' of the employee ' || l_employee_number || ' has invalid characters.');
1169 edi_validation_fail := 'Y';
1170 l_count_char_errors := l_count_char_errors + 1;
1171 END IF;
1172 END IF;
1173
1174 IF l_town_or_city <> ' ' and l_town_or_city IS NOT NULL THEN
1175 IF validate_input(l_town_or_city,'ADDRESS') > 0 THEN
1176 populate_run_msg(p_assactid,'The Town or City ' || l_town_or_city || ' of the employee '|| l_employee_number || ' has invalid characters.');
1177 fnd_file.put_line (fnd_file.LOG,'The Town or City ' || l_town_or_city ||' of the employee ' || l_employee_number || ' has invalid characters.');
1178 edi_validation_fail := 'Y';
1179 l_count_char_errors := l_count_char_errors + 1;
1180 END IF;
1181 END IF;
1182
1183 IF l_country <> ' ' and l_country IS NOT NULL THEN
1184 IF validate_input(l_country,'COUNTRY') > 0 THEN
1185 populate_run_msg(p_assactid,'The Country ' || l_country || ' of the employee ' || l_employee_number || ' has invalid characters.');
1186 fnd_file.put_line (fnd_file.LOG,'The Country ' || l_country || ' of the employee ' || l_employee_number || ' has invalid characters.');
1187 edi_validation_fail := 'Y';
1188 l_count_char_errors := l_count_char_errors + 1;
1189 END IF;
1190 END IF;
1191
1192 IF l_postal_code <> ' ' and l_postal_code IS NOT NULL THEN
1193 IF validate_input(l_postal_code,'POSTALCODE') > 0 THEN
1194 populate_run_msg(p_assactid,'The Postal Code ' || l_postal_code || ' of the employee ' || l_employee_number || ' has invalid characters.');
1195 fnd_file.put_line (fnd_file.LOG,'The Postal Code ' || l_postal_code || ' of the employee ' || l_employee_number || ' has invalid characters.');
1196 edi_validation_fail := 'Y';
1197 l_count_char_errors := l_count_char_errors + 1;
1198 END IF;
1199 END IF;
1200
1201 --person validation
1202
1203 IF l_last_name = ' ' OR l_last_name is null THEN
1204 populate_run_msg(p_assactid,'The Last Name of the employee ' || l_employee_number || ' is missing.');
1205 fnd_file.put_line (fnd_file.LOG,'The Last Name of the employee '|| l_employee_number || ' is missing.');
1206 edi_validation_fail := 'Y';
1207 l_count_missing_val := l_count_missing_val + 1;
1208
1209 elsif validate_input(l_last_name,'NAME') > 0 then
1210 populate_run_msg(p_assactid,'The Last Name ' || l_last_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1211 fnd_file.put_line (fnd_file.LOG,'The Last Name ' || l_last_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1212 edi_validation_fail := 'Y';
1213 l_count_char_errors := l_count_char_errors + 1;
1214 END IF;
1215
1216 IF l_first_name = ' ' OR l_first_name is null THEN
1217 populate_run_msg(p_assactid,'The First Name of the employee '|| l_employee_number || ' is missing.');
1218 fnd_file.put_line (fnd_file.LOG,'The First Name of the employee ' || l_employee_number || ' is missing.');
1219 edi_validation_fail := 'Y';
1220 l_count_missing_val := l_count_missing_val + 1;
1221
1222 ELSIF validate_input(l_first_name,'NAME') > 0 THEN
1223 populate_run_msg(p_assactid,'The First Name ' || l_first_name ||' of the employee ' || l_employee_number || ' has invalid characters.');
1224 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_first_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1225 edi_validation_fail := 'Y';
1226 l_count_char_errors := l_count_char_errors + 1;
1227 END IF;
1228
1229 IF l_middle_name <> ' ' AND l_middle_name is not null THEN
1230 IF validate_input(l_middle_name,'NAME') > 0 THEN
1231 populate_run_msg(p_assactid,'The First Name ' || l_middle_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1232 fnd_file.put_line (fnd_file.LOG,'The First Name ' || l_middle_name || ' of the employee ' || l_employee_number || ' has invalid characters.');
1233 edi_validation_fail := 'Y';
1234 l_count_char_errors := l_count_char_errors + 1;
1235 END IF;
1236 END IF;
1237
1238
1239 IF l_title <> ' ' AND l_title is not null THEN
1240 IF validate_input(l_title,'TITLE') > 0 THEN
1241 populate_run_msg(p_assactid,'The Title ' || l_title || ' of the employee ' || l_employee_number || ' has invalid characters.');
1242 fnd_file.put_line (fnd_file.LOG,'The Title ' || l_title || ' of the employee ' || l_employee_number || ' has invalid characters.');
1243 edi_validation_fail := 'Y';
1244 l_count_char_errors := l_count_char_errors + 1;
1245 END IF;
1246 END IF;
1247
1248 IF l_national_identifier is not null AND
1249 hr_gb_utility.ni_validate(l_national_identifier,sysdate) <> 0 THEN
1250 populate_run_msg(p_assactid,'The National Insurance number ' || l_national_identifier || ' of the employee ' || l_employee_number || ' has invalid characters.');
1251 fnd_file.put_line (fnd_file.LOG,'The National Insurance number ' || l_national_identifier || ' of the employee ' || l_employee_number || ' has invalid characters.');
1252 edi_validation_fail := 'Y';
1253 END IF;
1254
1255 IF ((l_sex <> 'M' AND l_sex <> 'F') OR (l_sex = ' ' OR l_sex IS NULL)) THEN
1256 populate_run_msg(p_assactid,'Gender is not specified for the employee ' || l_employee_number||'.');
1257 fnd_file.put_line (fnd_file.LOG,'Gender is not specified for the employee ' || l_employee_number||'.');
1258 edi_validation_fail := 'Y';
1259 END IF;
1260
1261 IF l_date_of_birth IS NULL THEN
1262 populate_run_msg(p_assactid,'The Date of Birth of the employee ' || l_employee_number || ' is missing.');
1263 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the employee '|| l_employee_number || ' is missing.');
1264 edi_validation_fail := 'Y';
1265 END IF;
1266
1267 IF ((l_date_of_birth > sysdate) OR (l_year1 > 130)) THEN
1268 populate_run_msg(p_assactid,'The Date of Birth of the employee ' || l_employee_number || ' is invalid.');
1269 fnd_file.put_line (fnd_file.LOG,'The Date of Birth of the employee '|| l_employee_number || ' is invalid.');
1270 edi_validation_fail := 'Y';
1271 END IF;
1272
1273 hr_utility.set_location('Leaving'||l_proc,10);
1274 END person_addr_validations_nino;
1275 --
1276
1277 --
1278 -- Function to validate the format of the fields
1279 function validate_input(p_input_value varchar2,
1280 p_validate_mode varchar2)
1281 return number is
1282 --
1283 l_valid number := 0;
1284 l_invalid_char constant varchar2(1) := '~'; -- required for translate
1285 l_char_chk constant varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
1286
1287 --EAS
1288 l_emp_set constant varchar2(36) := '-''.';
1289 l_space constant varchar2(10) := ' ';
1290 l_mix_chars constant varchar2(52) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
1291 l_number_chk constant varchar2(10) := '0123456789';
1292 l_char_set_A constant varchar2(36) := '.,-()/=!""%&*;<>''+:?\[]@$#^{}_';
1293 l_char_set_B constant varchar2(52) := '.,-()/=!""%&*;<>''+:?';
1294 l_char_set_C constant varchar2(52) := '.-''';
1295 l_char_set_D constant varchar2(52) := '-''';
1296 l_translated_value varchar2(200); -- Required to output failing char.
1297 --
1298 BEGIN
1299 --
1300 hr_utility.trace('Entering validate_input');
1301 hr_utility.trace('p_validate_mode='||p_validate_mode);
1302 hr_utility.trace('p_input_value='||p_input_value);
1303 --
1304
1305 if p_validate_mode = 'EMP_NAME' then
1306 if ( substr(p_input_value,1,1) =' ') then
1307 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1308 l_valid := 2;
1309 else
1310 l_translated_value :=
1311 translate(p_input_value,
1312 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_A||l_space,l_invalid_char);
1313
1314 if l_translated_value is not null then
1315 hr_utility.trace('Invalid chars found: '||l_translated_value);
1316 l_valid := 1; -- Not valid
1317 else
1318 l_valid := 0; -- Valid
1319 end if;
1320 end if ;
1321
1322 elsIF p_validate_mode = 'NUMBER' then
1323 --
1324 -- Check for Valid First Char
1325 --
1326 l_translated_value :=
1327 translate(p_input_value,
1328 l_invalid_char||l_number_chk,l_invalid_char);
1329
1330 if l_translated_value is not null then
1331 hr_utility.trace('Invalid chars found: '||l_translated_value);
1332 l_valid := 1; -- Not valid
1333 else
1334 l_valid := 0; -- Valid
1335 end if;
1336
1337 elsIF p_validate_mode = 'FULL_EDI' then
1338 --
1339 -- Check for Valid First Char
1340 --
1341 if ( substr(p_input_value,1,1) =' ') then
1342 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1343 l_valid := 2;
1344 else
1345 l_translated_value :=
1346 translate(p_input_value,
1347 l_invalid_char||l_char_chk||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1348
1349 if l_translated_value is not null then
1350 hr_utility.trace('Invalid chars found: '||l_translated_value);
1351 l_valid := 1; -- Not valid
1352 else
1353 l_valid := 0; -- Valid
1354 end if;
1355 end if;
1356
1357 elsIF p_validate_mode = 'NAME' then
1358 --
1359 -- Check for Valid First Char
1360 --
1361 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1362 or substr(p_input_value,1,1) between 'a' and 'z') then
1363 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1364 l_valid := 2;
1365 else
1366 l_translated_value :=
1367 translate(p_input_value,
1368 l_invalid_char||l_mix_chars||l_char_set_D||l_space,l_invalid_char);
1369
1370 if l_translated_value is not null then
1371 hr_utility.trace('Invalid chars found: '||l_translated_value);
1372 l_valid := 1; -- Not valid
1373 else
1374 l_valid := 0; -- Valid
1375 end if;
1376 end if;
1377
1378 elsIF p_validate_mode = 'TITLE' then
1379 --
1380 -- Check for Valid First Char
1381 --
1382 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1383 or substr(p_input_value,1,1) between 'a' and 'z') then
1384 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1385 l_valid := 2;
1386 else
1387 l_translated_value :=
1388 translate(p_input_value,
1389 l_invalid_char||l_mix_chars||l_char_set_C||l_space,l_invalid_char);
1390
1391 if l_translated_value is not null then
1392 hr_utility.trace('Invalid chars found: '||l_translated_value);
1393 l_valid := 1; -- Not valid
1394 else
1395 l_valid := 0; -- Valid
1396 end if;
1397 end if;
1398
1399 ELSIF p_validate_mode = 'ADDRESS' THEN
1400 --
1401 -- Check for Valid First Char
1402 --
1403 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1404 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1405 -- First char invalid
1406 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1407 l_valid := 2;
1408 else
1409 l_translated_value :=
1410 translate(p_input_value,
1411 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1412
1413 if l_translated_value is not null then
1414 hr_utility.trace('Invalid chars found: '||l_translated_value);
1415 l_valid := 1; --Invalid
1416 else
1417 l_valid := 0; -- Valid
1418 end if;
1419 end if;
1420
1421 elsif p_validate_mode = 'COUNTRY' then
1422 if not (substr(p_input_value,1,1) between 'A' and 'Z' or
1423 substr(p_input_value,1,1) between 'a' and 'z' ) then
1424 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1425 l_valid := 2;
1426 else
1427 l_translated_value :=
1428 translate(p_input_value,
1429 l_invalid_char||l_mix_chars||l_number_chk||l_space||l_char_set_B,l_invalid_char);
1430
1431 if l_translated_value is not null then
1432 hr_utility.trace('Invalid chars found: '||l_translated_value);
1433 l_valid := 1; -- Not valid
1434 else
1435 l_valid := 0; -- Valid
1436 end if;
1437 end if ;
1438
1439 -- character set E has alpha,numerals,space
1440 elsif p_validate_mode = 'POSTALCODE' then
1441
1442 if not (substr(p_input_value,1,1) between 'A' and 'Z'
1443 or substr(p_input_value,1,1) between 'a' and 'z' or substr(p_input_value,1,1) between '0' and '9') then
1444 -- First char invalid
1445 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1446 l_valid := 2;
1447 else
1448 l_translated_value := translate(p_input_value,
1449 l_invalid_char||l_mix_chars||l_number_chk||l_space,l_invalid_char);
1450 --
1451 if l_translated_value is not null then
1452 hr_utility.trace('Invalid chars found: '||l_translated_value);
1453 l_valid := 1; -- Not valid
1454 else
1455 l_valid := 0; -- Valid
1456 end if;
1457 end if;
1458
1459 elsif p_validate_mode = 'PAYROLLID' then
1460 if ( substr(p_input_value,1,1) =' ') then
1461 hr_utility.trace('Invalid first char: '||substr(p_input_value,1,1));
1462 l_valid := 2;
1463 else
1464 l_translated_value :=
1465 translate(p_input_value,
1466 l_invalid_char||l_mix_chars||l_number_chk||l_char_set_B||l_space,l_invalid_char);
1467
1468 if l_translated_value is not null then
1469 hr_utility.trace('Invalid chars found: '||l_translated_value);
1470 l_valid := 1; -- Not valid
1471 else
1472 l_valid := 0; -- Valid
1473 end if;
1474 end if ;
1475
1476 END IF;
1477 --
1478 hr_utility.trace('Leaving validate_input');
1479 return l_valid;
1480 end validate_input;
1481
1482 -- Procedure to set New Starter RTI Sent flag
1483 procedure set_new_starter_rti_sent(p_asg_id number) is
1484
1485 cursor csr_asg_extra_info is
1486 select ASSIGNMENT_EXTRA_INFO_ID, AEI_INFORMATION8,object_version_number
1487 FROM per_assignment_extra_info
1488 WHERE assignment_id = p_asg_id
1489 AND information_type = 'GB_RTI_ASG_DETAILS';
1490
1491 l_asg_extra_info_id number;
1492 l_starter_flag varchar2(1);
1493 l_object_version_number number;
1494
1495 begin
1496 hr_utility.trace('Entering set_new_starter_rti_sent');
1497
1498 open csr_asg_extra_info;
1499 fetch csr_asg_extra_info into l_asg_extra_info_id,l_starter_flag,l_object_version_number;
1500 close csr_asg_extra_info;
1501
1502 if l_asg_extra_info_id is null then
1503 -- Create the extra info and set the RTI Sent flag to Yes
1504 hr_assignment_extra_info_api.create_assignment_extra_info
1505 (p_validate => false,
1506 p_assignment_id => p_asg_id,
1507 p_information_type => 'GB_RTI_ASG_DETAILS',
1508 p_aei_information_category => 'GB_RTI_ASG_DETAILS',
1509 p_aei_information8 => 'Y',
1510 p_aei_information9 => 'N',
1511 p_aei_information19 => 'N',
1512 p_object_version_number => l_object_version_number,
1513 p_assignment_extra_info_id => l_asg_extra_info_id
1514 );
1515
1516 hr_utility.trace('New Assignment extra info id : '||l_asg_extra_info_id);
1517 elsif l_starter_flag <> 'Y' then
1518 -- Update the flag to Yes
1519 hr_assignment_extra_info_api.update_assignment_extra_info
1520 (p_validate => false,
1521 p_object_version_number => l_object_version_number,
1522 p_assignment_extra_info_id => l_asg_extra_info_id,
1523 p_aei_information_category => 'GB_RTI_ASG_DETAILS',
1524 p_aei_information8 => 'Y'
1525 );
1526
1527 hr_utility.trace('Existing Assignment extra info id : '||l_asg_extra_info_id);
1528 end if;
1529
1530 hr_utility.trace('Leaving set_new_starter_rti_sent');
1531 end set_new_starter_rti_sent;
1532
1533 -- Procedure to insert data to the archive table pay_action_information
1534 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
1535 p_effective_date IN DATE,
1536 p_tab_rec_data IN action_info_table) IS
1537 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
1538 l_ovn number;
1539 l_action_id number;
1540 BEGIN
1541 hr_utility.set_location('Entering: '||l_proc,1);
1542 if p_tab_rec_data.count > 0 then
1543 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
1544 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
1545 hr_utility.trace('action_context_id = '|| p_assactid);
1546 if p_tab_rec_data(i).action_info_category is not null then
1547 pay_action_information_api.create_action_information(
1548 p_action_information_id => l_action_id,
1549 p_object_version_number => l_ovn,
1550 p_action_information_category => p_tab_rec_data(i).action_info_category,
1551 p_action_context_id => p_assactid,
1552 p_action_context_type => 'AAP',
1553 p_assignment_id => p_tab_rec_data(i).assignment_id,
1554 p_effective_date => p_effective_date,
1555 p_action_information1 => p_tab_rec_data(i).act_info1,
1556 p_action_information2 => p_tab_rec_data(i).act_info2,
1557 p_action_information3 => p_tab_rec_data(i).act_info3,
1558 p_action_information4 => p_tab_rec_data(i).act_info4,
1559 p_action_information5 => p_tab_rec_data(i).act_info5,
1560 p_action_information6 => p_tab_rec_data(i).act_info6,
1561 p_action_information7 => p_tab_rec_data(i).act_info7,
1562 p_action_information8 => p_tab_rec_data(i).act_info8,
1563 p_action_information9 => p_tab_rec_data(i).act_info9,
1564 p_action_information10 => p_tab_rec_data(i).act_info10,
1565 p_action_information11 => p_tab_rec_data(i).act_info11,
1566 p_action_information12 => p_tab_rec_data(i).act_info12,
1567 p_action_information13 => p_tab_rec_data(i).act_info13,
1568 p_action_information14 => p_tab_rec_data(i).act_info14,
1569 p_action_information15 => p_tab_rec_data(i).act_info15,
1570 p_action_information16 => p_tab_rec_data(i).act_info16,
1571 p_action_information17 => p_tab_rec_data(i).act_info17,
1572 p_action_information18 => p_tab_rec_data(i).act_info18,
1573 p_action_information19 => p_tab_rec_data(i).act_info19,
1574 p_action_information20 => p_tab_rec_data(i).act_info20,
1575 p_action_information21 => p_tab_rec_data(i).act_info21,
1576 p_action_information22 => p_tab_rec_data(i).act_info22,
1577 p_action_information23 => p_tab_rec_data(i).act_info23,
1578 p_action_information24 => p_tab_rec_data(i).act_info24,
1579 p_action_information25 => p_tab_rec_data(i).act_info25,
1580 p_action_information26 => p_tab_rec_data(i).act_info26,
1581 p_action_information27 => p_tab_rec_data(i).act_info27,
1582 p_action_information28 => p_tab_rec_data(i).act_info28,
1583 p_action_information29 => p_tab_rec_data(i).act_info29,
1584 p_action_information30 => p_tab_rec_data(i).act_info30
1585 );
1586 end if;
1587 end loop;
1588 end if;
1589 hr_utility.set_location('Leaving: '||l_proc,999);
1590 END insert_archive_row;
1591 --
1592 --
1593 /*------------ PUBLIC PROCEDURES --------------*/
1594
1595 /*--------------------------------------------------------------------------
1596 Name : range_cursor
1597 Purpose : This returns the select statement that is used to create the
1598 range rows.
1599 Arguments :
1600 Notes : The range cursor determines which people should be processed.
1601 The normal practice is to include everyone, and then limit
1602 the list during the assignment action creation.
1603 --------------------------------------------------------------------------*/
1604 PROCEDURE range_cursor (pactid IN NUMBER,
1605 sqlstr OUT NOCOPY VARCHAR2)
1606 IS
1607
1608 cursor csr_parameter_info IS
1609 SELECT report_type
1610 FROM pay_payroll_actions
1611 WHERE payroll_action_id = pactid;
1612
1613 l_report_type varchar2(15);
1614 --NINO New CP addition
1615 l_payroll_id varchar2(25);
1616
1617 -- l_employee_number will have the Employee ID parameter from the NINO Program
1618 l_employee_number varchar2(25);
1619 -- l_assignment_set will have the Assignment Set parameter from the NINO Program
1620 l_assignment_set varchar2(25);
1621
1622 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
1623 BEGIN
1624 hr_utility.set_location('Entering: '||l_proc,1);
1625
1626 OPEN csr_parameter_info;
1627 fetch csr_parameter_info into l_report_type;
1628 CLOSE csr_parameter_info ;
1629
1630 IF l_report_type = 'RTI_EAS_REP' THEN
1631
1632 sqlstr := 'select distinct person_id '||
1633 'from per_all_people_f ppf, '||
1634 'pay_payroll_actions ppa '||
1635 'where ppa.payroll_action_id = :payroll_action_id '||
1636 'and ppa.business_group_id = ppf.business_group_id '||
1637 'order by ppf.person_id';
1638 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
1639 hr_utility.set_location(' Leaving: '||l_proc,100);
1640 END IF;
1641
1642
1643
1644 IF l_report_type = 'RTI_NINO_REP' THEN
1645 -- Getting the Payroll ID
1646 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
1647 p_payroll_action_id => pactid,
1648 p_token_name => 'PAYROLL',
1649 p_token_value => l_payroll_id);
1650
1651 -- Getting the Assignment Set
1652 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
1653 p_payroll_action_id => pactid,
1654 p_token_name => 'ASSET',
1655 p_token_value => l_assignment_set);
1656
1657 -- Getting the Employee Numnber (Emp ID)
1658 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
1659 p_payroll_action_id => pactid,
1660 p_token_name => 'EMPID',
1661 p_token_value => l_employee_number);
1662
1663 hr_utility.trace(' Payroll : '||l_payroll_id);
1664 hr_utility.trace(' Assignment Set : '||l_assignment_set);
1665 hr_utility.trace(' Employee ID : '||l_employee_number);
1666
1667 IF (l_payroll_id is null) THEN
1668 sqlstr := 'select distinct person_id '||
1669 'from per_all_people_f ppf, '||
1670 'pay_payroll_actions ppa '||
1671 'where ppa.payroll_action_id = :payroll_action_id '||
1672 'and ppa.business_group_id = ppf.business_group_id '||
1673 'order by ppf.person_id';
1674
1675 END IF;
1676
1677 IF (l_payroll_id is not null) THEN
1678
1679 IF ((l_employee_number is null) and (l_assignment_set is null)) THEN
1680 sqlstr := 'select distinct PERSON_ID '||
1681 'from per_all_assignments_f paaf, '||
1682 'pay_payroll_actions ppa '||
1683 'where ppa.payroll_action_id = :payroll_action_id '||
1684 'and ppa.business_group_id = paaf.business_group_id '||
1685 ' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
1686 ' order by person_id';
1687 END IF;
1688
1689 IF (l_employee_number is not null ) THEN
1690 sqlstr := 'select distinct ppf.person_id '||
1691 'from per_all_people_f ppf, '||
1692 'per_all_assignments_f paaf, '||
1693 'pay_payroll_actions ppa '||
1694 'where ppa.payroll_action_id = :payroll_action_id '||
1695 ' and paaf.person_id=ppf.person_id '||
1696 'and ppa.business_group_id = ppf.business_group_id '||
1697 ' and ppf.EMPLOYEE_NUMBER = ' ||''''||l_employee_number||''''||
1698 ' and paaf.PAYROLL_ID = ' ||''''||l_payroll_id||''''||
1699 'order by ppf.person_id';
1700 END IF;
1701
1702 IF (l_assignment_set is not null)THEN
1703
1704 sqlstr := 'select distinct person_id '||
1705 'from per_all_people_f ppf, '||
1706 'pay_payroll_actions ppa '||
1707 'where ppa.payroll_action_id = :payroll_action_id '||
1708 'and ppa.business_group_id = ppf.business_group_id '||
1709 'order by ppf.person_id';
1710 END IF;
1711
1712
1713
1714 END IF;
1715 END IF;
1716 END range_cursor;
1717 --
1718 --
1719 /*--------------------------------------------------------------------------
1720 Name : action_creation
1721 Purpose : This creates the assignment actions for a specific chunk.
1722 Arguments :
1723 Notes :
1724 --------------------------------------------------------------------------*/
1725
1726 PROCEDURE rti_eas_action_creation (pactid in number,
1727 stperson in number,
1728 endperson in number,
1729 chunk in number) IS
1730 BEGIN
1731 internal_action_creation_eas(pactid, stperson, endperson, chunk,'GB_RTI_EAS', 'RTI_EAS_REP');
1732 END rti_eas_action_creation;
1733
1734 --
1735 PROCEDURE rti_nino_action_creation (pactid in number,
1736 stperson in number,
1737 endperson in number,
1738 chunk in number) IS
1739 BEGIN
1740 internal_action_creation_nino(pactid, stperson, endperson, chunk,'GB_RTI_NINO', 'RTI_NINO_REP');
1741 END rti_nino_action_creation;
1742
1743
1744 PROCEDURE internal_action_creation_eas(pactid in number,
1745 stperson in number,
1746 endperson in number,
1747 chunk in number,
1748 p_info_type in varchar2,
1749 p_rep_type in varchar2)
1750 IS
1751 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
1752 l_payroll_id number;
1753 l_tax_ref varchar2(20);
1754 l_business_group_id number;
1755 l_effective_date date;
1756 l_tax_year_start_date DATE;
1757
1758 l_ass_act_id number;
1759 l_assignment_id number;
1760
1761 lockingactid number;
1762 l_locked_action_id number;
1763 l_exist number;
1764 cursor csr_parameter_info is
1765 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
1766 to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD') effective_date,
1767 business_group_id
1768 from pay_payroll_actions pact
1769 where payroll_action_id = pactid;
1770
1771 -- Cursor to fetch Active Assignments
1772 cursor csr_asg_active is
1773 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
1774 pap.person_id person_id,past.per_system_status status
1775 from per_all_people_f pap,
1776 per_all_assignments_f asg,
1777 per_assignment_status_types past,
1778 per_periods_of_service serv,
1779 pay_all_payrolls_f pay,
1780 hr_soft_coding_keyflex sck
1781 where pap.person_id between stperson and endperson
1782 and pap.current_employee_flag = 'Y'
1783 and pap.person_id = asg.person_id
1784 and asg.business_group_id = l_business_group_id
1785 and asg.assignment_status_type_id = past.assignment_status_type_id
1786 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN','TERM_ASSIGN')
1787 and asg.payroll_id = pay.payroll_id
1788 and asg.period_of_service_id = serv.period_of_service_id
1789 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1790 and upper(l_tax_ref) = upper(sck.segment1)
1791 --and l_effective_date between asg.effective_start_date and asg.effective_end_date
1792 and asg.effective_start_date =
1793 ( select max(asg2.effective_start_date)
1794 from per_all_assignments_f asg2
1795 where asg2.assignment_id = asg.assignment_id
1796 and asg2.assignment_type = 'E'
1797 and asg2.effective_start_date <= l_effective_date
1798 )
1799 and l_effective_date between pap.effective_start_date and pap.effective_end_date
1800 and l_effective_date between pay.effective_start_date and pay.effective_end_date
1801 order by person_id,assignment_id;
1802
1803 --Cursor to fetch Terminated Employees
1804 cursor csr_asg_terminated is
1805 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,
1806 pap.person_id person_id, serv.actual_termination_date actual_termination_date
1807 from per_all_people_f pap,
1808 per_all_assignments_f asg,
1809 per_periods_of_service serv,
1810 pay_all_payrolls_f pay,
1811 hr_soft_coding_keyflex sck
1812 where pap.person_id between stperson and endperson
1813 and serv.actual_termination_date is not null
1814 and pap.person_id = asg.person_id
1815 and asg.business_group_id = l_business_group_id
1816 and asg.payroll_id = pay.payroll_id
1817 and asg.period_of_service_id = serv.period_of_service_id
1818 and asg.assignment_type = 'E'
1819 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1820 and upper(l_tax_ref) = upper(sck.segment1)
1821 and pap.effective_start_date =
1822 ( select max(pap2.effective_start_date) from
1823 per_all_people_f pap2
1824 where pap2.person_id = pap.person_id
1825 and pap2.effective_start_date <= l_effective_date
1826 )
1827 and asg.effective_start_date =
1828 ( select max(asg2.effective_start_date)
1829 from per_all_assignments_f asg2
1830 where asg2.assignment_id = asg.assignment_id
1831 and asg2.assignment_type = 'E'
1832 and asg2.effective_start_date <= l_effective_date
1833 )
1834 and asg.effective_end_date >= l_tax_year_start_date
1835 and l_effective_date between pay.effective_start_date and pay.effective_end_date
1836 and serv.actual_termination_date >= l_tax_year_start_date
1837 and serv.actual_termination_date < l_effective_date
1838 order by person_id,assignment_id;
1839
1840 -- Cursor to fetch Agg Flag for a Person
1841 cursor csr_get_agg_flag (p_person_id number) is
1842 select per_information10 per_agg_flag from per_all_people_f
1843 where PERSON_ID = p_person_id
1844 and l_effective_date between effective_start_date and effective_end_date;
1845
1846 cursor csr_find_next_asg(p_person_id number,p_assignment_id number) is
1847 select paaf.assignment_id
1848 from per_all_people_f peo,
1849 per_all_assignments_f paaf,
1850 per_all_assignments_f paaf1,
1851 per_assignment_status_types past,
1852 per_assignment_status_types past1,
1853 pay_all_payrolls_f papf ,
1854 hr_soft_coding_keyflex flex
1855 where
1856 peo.person_id = p_person_id
1857 and paaf.person_id = peo.person_id
1858 and paaf.assignment_id <> p_assignment_id
1859 and paaf1.assignment_id = p_assignment_id
1860 and paaf.assignment_status_type_id = past.assignment_status_type_id
1861 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1862 and paaf1.assignment_status_type_id = past1.assignment_status_type_id
1863 and past1.per_system_status in ('TERM_ASSIGN')
1864 and papf.payroll_id = paaf.payroll_id
1865 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
1866 and upper(l_tax_ref) = upper(flex.segment1)
1867 and paaf.effective_start_date = paaf1.effective_start_date;
1868
1869 cursor csr_get_terminated_date(p_asg_id number) is
1870 select max(effective_end_date)
1871 from per_all_assignments_f paaf ,
1872 per_assignment_status_types past
1873 where assignment_id = p_asg_id
1874 --and paaf.assignment_status_type_id in ( 1,2)
1875 and paaf.assignment_status_type_id = past.assignment_status_type_id
1876 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1877 and paaf.effective_start_date <= l_effective_date;
1878
1879
1880 l_next_asg number;
1881 l_flag varchar2(1) :='Y';
1882 l_prev_asg number:=0;
1883 l_status number;
1884 l_per_agg_flag varchar2(1);
1885 l_asg_end_date date;
1886
1887 BEGIN
1888
1889 hr_utility.set_location('Entering: '||l_proc,1);
1890
1891 open csr_parameter_info;
1892 fetch csr_parameter_info into l_tax_ref,
1893 l_effective_date,
1894 l_business_group_id;
1895 close csr_parameter_info;
1896 hr_utility.set_location('Process effective date'||l_effective_date,20);
1897 l_tax_year_start_date := fetch_soy(l_effective_date);
1898
1899 hr_utility.set_location('Active Assignments',15);
1900 -- To fetch Active Assignments
1901 for asg_rec in csr_asg_active loop
1902
1903 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
1904 l_flag := 'Y';
1905 -- If Aggregate flag is set then ignore non primary assignments
1906 if asg_rec.per_agg_flag = 'Y' then
1907 if asg_rec.asg_primary_flag <> 'Y' then
1908 hr_utility.trace(' Aggregation Found. This is not primary assignment.');
1909 l_flag:= 'N';
1910 end if;
1911 end if;
1912 /*
1913 -- P45 check.If issued do not pick the assignment
1914 if asg_rec.status = 'TERM_ASSIGN' and pay_p45_pkg.return_p45_issued_flag(asg_rec.assignment_id) = 'Y' then
1915 fnd_file.put_line (fnd_file.LOG,'Terminated Asg '||asg_rec.assignment_id||' found.P45 issued.');
1916 l_flag:= 'N';
1917 end if;*/
1918
1919 -- If Terminated before start of the Tax Year. Ignore the assignment
1920 open csr_get_terminated_date(asg_rec.assignment_id);
1921 fetch csr_get_terminated_date into l_asg_end_date;
1922 close csr_get_terminated_date;
1923
1924 hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
1925
1926 if l_asg_end_date < l_tax_year_start_date then
1927 hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
1928 l_flag:= 'N';
1929 end if;
1930
1931 -- To check whether continous employment exist for this assignment
1932 if asg_rec.status = 'TERM_ASSIGN' and l_flag = 'Y' then
1933 open csr_find_next_asg(asg_rec.person_id,asg_rec.assignment_id);
1934 fetch csr_find_next_asg into l_next_asg;
1935 if csr_find_next_asg%found then
1936 hr_utility.trace('Continous Employment.Ignore this assignment.');
1937 l_flag:= 'N';
1938 end if;
1939 close csr_find_next_asg;
1940 end if;
1941
1942 if l_flag = 'Y' then
1943
1944 -- Create one assignment action for every assignment
1945 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
1946 select pay_assignment_actions_s.nextval
1947 into lockingactid
1948 from dual;
1949
1950 -- Insert assignment into pay_assignment_actions
1951 hr_nonrun_asact.insact
1952 (
1953 lockingactid,
1954 asg_rec.assignment_id,
1955 pactid,
1956 chunk,
1957 null
1958 );
1959
1960 end if;
1961 end loop;
1962
1963 -- To fetch Terminated assignments
1964 hr_utility.set_location('Terminated Assignments',15);
1965 for asg_rec in csr_asg_terminated loop
1966 l_flag := 'Y';
1967 /* Check the cached value of person id .
1968 If the assignment has been archived already, skip the assignment again
1969 when appers in date tracked records.
1970 */
1971 if l_prev_asg <> asg_rec.assignment_id then
1972 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
1973
1974 -- If Aggregate flag is set then ignore non primary assignments
1975
1976 open csr_get_agg_flag(asg_rec.person_id);
1977 fetch csr_get_agg_flag into l_per_agg_flag;
1978 close csr_get_agg_flag;
1979 hr_utility.set_location('Aggregation Flag:'||l_per_agg_flag,1515);
1980 if l_per_agg_flag = 'Y' then
1981 if asg_rec.asg_primary_flag <> 'Y' then
1982 hr_utility.trace(' Aggregation Found. This is not primary assignment.');
1983 l_flag:= 'N';
1984 end if;
1985 end if;
1986
1987 -- If Terminated before start of the Tax Year. Ignore the assignment
1988 open csr_get_terminated_date(asg_rec.assignment_id);
1989 fetch csr_get_terminated_date into l_asg_end_date;
1990 close csr_get_terminated_date;
1991
1992 hr_utility.trace('Assignment actual end date : '||l_asg_end_date);
1993
1994 if l_asg_end_date < l_tax_year_start_date then
1995 hr_utility.trace(' Assignment terminated before start of the Tax Year.Ignore the assignment.');
1996 l_flag:= 'N';
1997 end if;
1998
1999 if l_flag = 'Y' then
2000
2001 -- Create one assignment action for every assignment
2002 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2003 select pay_assignment_actions_s.nextval
2004 into lockingactid
2005 from dual;
2006
2007 -- Insert assignment into pay_assignment_actions
2008 hr_nonrun_asact.insact
2009 (
2010 lockingactid,
2011 asg_rec.assignment_id,
2012 pactid,
2013 chunk,
2014 null
2015 );
2016 -- Cache the archived person id
2017 l_prev_asg := asg_rec.assignment_id;
2018 end if;
2019 end if; -- cache check end
2020 end loop;
2021
2022 hr_utility.set_location('Leaving: '||l_proc,999);
2023 END internal_action_creation_eas;
2024
2025 --
2026 --
2027
2028 PROCEDURE internal_action_creation_nino (pactid in number,
2029 stperson in number,
2030 endperson in number,
2031 chunk in number,
2032 p_info_type in varchar2,
2033 p_rep_type in varchar2)
2034 IS
2035 l_proc CONSTANT VARCHAR2(90):= g_package||'internal_action_creation';
2036 l_payroll_id number;
2037 l_tax_ref varchar2(20);
2038 l_business_group_id number;
2039 l_effective_date date;
2040 l_emp_num varchar2(20);
2041 l_ass_act_id number;
2042 l_assignment_id number;
2043 l_arch boolean;
2044 l_assignment_set number;
2045 l_formula_id number;
2046 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
2047 lockingactid number;
2048 l_locked_action_id number;
2049 l_exist number;
2050 l_exclude_flag char := 'I';
2051 cursor csr_parameter_info is
2052 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL')) payroll_id,
2053 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
2054 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'EMPID'),1,20) emp_num,
2055 effective_date,
2056 business_group_id
2057 from pay_payroll_actions
2058 where payroll_action_id = pactid;
2059
2060 cursor csr_asg is
2061 select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
2062 pap.person_id person_id,asg.effective_start_date effective_start_date
2063 from per_all_people_f pap,
2064 per_all_assignments_f asg,
2065 per_periods_of_service serv,
2066 pay_all_payrolls_f pay,
2067 hr_soft_coding_keyflex sck,
2068 per_people_extra_info ppei
2069 where pap.person_id between stperson and endperson
2070 and pap.person_id =ppei.person_id (+)
2071 /* and nvl(ppei.pei_information_category,'RTI_NINO') = 'RTI_NINO'
2072 and nvl(ppei.pei_information1,'No') = 'No'
2073 and ppei.pei_information5 is null
2074 */
2075 -- above 3 lines commented and added the below condition Bug -16536044
2076 and not exists( select 'Y' from per_people_extra_info ppei1
2077 where ppei1.person_id = pap.person_id
2078 and ppei1.pei_information_category = 'RTI_NINO'
2079 and nvl(ppei1.pei_information1,'Yes') <> 'No'
2080 and ( (ppei1.pei_information1 is null and ppei1.pei_information5 is not null)
2081 or
2082 ( nvl(ppei1.pei_information1,'Yes') = 'Yes - HMRC' and ppei1.pei_information5 is not null)
2083 or
2084 ( nvl(ppei1.pei_information1,'Yes') = 'Yes - Pre RTI' and ppei1.pei_information5 is null)
2085 )
2086 )
2087
2088 --and pap.current_employee_flag = 'Y'
2089 --and nvl(pap.current_employee_flag,'N') = decode(l_emp_num,null,'Y', nvl(pap.current_employee_flag,'N'))
2090 and asg.primary_flag = 'Y'
2091 and nvl(pap.current_employee_flag,'N') = decode(l_emp_num, null,decode(l_assignment_set,null,'Y',nvl(pap.current_employee_flag,'N')),nvl(pap.current_employee_flag,'N'))
2092 and pap.person_id = asg.person_id
2093 and asg.business_group_id = l_business_group_id
2094 and asg.payroll_id = pay.payroll_id
2095 and asg.period_of_service_id = serv.period_of_service_id
2096 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2097 and upper(l_tax_ref) = upper(sck.segment1)
2098 and (l_payroll_id IS NULL
2099 or
2100 l_payroll_id = pay.payroll_id)
2101 and not exists (select 1
2102 from hr_assignment_set_amendments hasa
2103 where hasa.assignment_set_id = l_assignment_set
2104 and hasa.assignment_id = asg.assignment_id
2105 and hasa.include_or_exclude = 'E')
2106 and serv.date_start <= l_effective_date
2107 and l_effective_date between asg.effective_start_date and asg.effective_end_date
2108 and l_effective_date between pap.effective_start_date and pap.effective_end_date
2109 and l_effective_date between pay.effective_start_date and pay.effective_end_date
2110 order by person_id,effective_start_date;
2111
2112 cursor csr_exc_inc is
2113 -- Assuming Only one type of include_or_exclude will be there for an assignment set
2114 select nvl(include_or_exclude,'I')
2115 from hr_assignment_set_amendments
2116 where assignment_set_id = l_assignment_set;
2117
2118 l_prev_person number:=0;
2119 l_flag varchar2(1) :='Y';
2120 BEGIN
2121 hr_utility.set_location('Entering: '||l_proc,1);
2122 open csr_parameter_info;
2123 fetch csr_parameter_info into l_payroll_id,
2124 l_tax_ref,
2125 l_emp_num,
2126 l_effective_date,
2127 l_business_group_id;
2128 close csr_parameter_info;
2129
2130 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2131 p_payroll_action_id => pactid,
2132 p_token_name => 'ASSET',
2133 p_token_value => l_assignment_set);
2134 hr_utility.set_location('Assignment Set Id '||l_assignment_set,11);
2135
2136 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),10);
2137 for asg_rec in csr_asg loop
2138 l_flag := 'Y';
2139 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
2140
2141
2142
2143 open csr_exc_inc ;
2144 fetch csr_exc_inc into l_exclude_flag;
2145 IF csr_exc_inc%notfound then
2146 l_exclude_flag :='I';
2147 end if;
2148 close csr_exc_inc;
2149 --Check for Assignment set
2150 If l_assignment_set is not null and l_exclude_flag ='I' then
2151 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => l_assignment_set
2152 ,p_formula_id => l_formula_id
2153 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2154 );
2155 hr_utility.set_location('Assignment Set -FormulaID :' || l_formula_id,11);
2156 hr_utility.set_location('Assignment Set -Amendments Count :' || l_tab_asg_set_amnds.count,11);
2157
2158 If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
2159 hr_utility.set_location('Assignment Set -FormulaID :' || asg_rec.assignment_id,15);
2160 l_flag := 'N'; -- Assignment Action not to be created
2161 end if;
2162
2163
2164
2165 l_flag := pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => asg_rec.assignment_id
2166 ,p_formula_id => l_formula_id
2167 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
2168 ,p_effective_date => l_effective_date
2169 );
2170 hr_utility.set_location('Assignment Id - l_flag :' || l_flag,11);
2171 End if;
2172
2173
2174 if l_flag = 'Y' then
2175
2176 -- Create one assignment action for every assignment
2177 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
2178 select pay_assignment_actions_s.nextval
2179 into lockingactid
2180 from dual;
2181
2182 -- Insert assignment into pay_assignment_actions
2183 hr_nonrun_asact.insact
2184 (
2185 lockingactid,
2186 asg_rec.assignment_id,
2187 pactid,
2188 chunk,
2189 null
2190 );
2191
2192 end if;
2193 --l_prev_person := asg_rec.person_id;
2194 end loop;
2195
2196 hr_utility.set_location('Leaving: '||l_proc,999);
2197 END internal_action_creation_nino;
2198 --
2199
2200 --
2201
2202 /*--------------------------------------------------------------------------
2203 Name : archinit
2204 Purpose : This procedure can be used to perform an initialisation
2205 section and validation of Employer details
2206 Arguments :
2207 Notes :
2208 --------------------------------------------------------------------------*/
2209 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
2210 IS
2211 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
2212
2213 l_sender_id hr_organization_information.org_information11%TYPE;
2214 l_tax_ref hr_organization_information.org_information1%TYPE;
2215 l_tax_dist hr_organization_information.org_information2%TYPE;
2216
2217 l_employer_addr VARCHAR2(255);
2218 l_employer_name VARCHAR2(150);
2219 l_err BOOLEAN;
2220 l_exp EXCEPTION;
2221 l_rep_typ varchar2(100);
2222
2223 cursor csr_payroll_details(pactid NUMBER) is
2224 select
2225 nvl(UPPER(hoi.org_information11),' ') sender_id,
2226 decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
2227 fnd_number.number_to_canonical(pact.request_id) request_id,
2228 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
2229 nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
2230 lpad(nvl(substr(hoi.org_information1,1,3),' '),3,0) tax_office_no,
2231 decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
2232 nvl(upper(substr(hoi.org_information6,1,13)),' ') acc_ref_no,
2233 report_type
2234 from pay_payroll_actions pact,
2235 hr_organization_information hoi
2236 where pact.payroll_action_id=pactid
2237 and pact.business_group_id = hoi.organization_id
2238 and hoi.org_information_context = 'Tax Details References'
2239 and nvl(hoi.org_information10,'UK') = 'UK'
2240 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
2241 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
2242 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
2243
2244 l_payroll_rec csr_payroll_details%rowtype;
2245
2246 BEGIN
2247 hr_utility.set_location('Entering '|| l_proc, 10);
2248 l_err := FALSE;
2249
2250 open csr_payroll_details(p_payroll_action_id);
2251 fetch csr_payroll_details into l_payroll_rec;
2252 close csr_payroll_details;
2253
2254 l_rep_typ := l_payroll_rec.report_type;
2255
2256 hr_utility.set_location('l_sender_id '|| l_sender_id, 10);
2257
2258 IF l_rep_typ in ('RTI_EAS_REP','RTI_NINO_REP')
2259 THEN
2260 hr_utility.set_location('Calling header validations', 10);
2261
2262
2263 if l_payroll_rec.tax_office_no = ' ' or l_payroll_rec.tax_office_no is null then
2264 hr_utility.set_location('The HMRC Office Number is missing.',10);
2265 fnd_file.put_line(fnd_file.output,'The HMRC Office Number is missing.');
2266 l_err := true;
2267 elsif validate_input(substr(l_payroll_rec.tax_office_no,1,3),'NUMBER') > 0
2268 then
2269 hr_utility.set_location('The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.',10);
2270 fnd_file.put_line(fnd_file.output,'The HMRC Office Number '||l_payroll_rec.tax_office_no||' has invalid characters.');
2271 l_err := true;
2272 end if;
2273
2274 if l_payroll_rec.tax_ref_no = ' ' or l_payroll_rec.tax_ref_no is null then
2275 hr_utility.set_location('The Employer PAYE Reference is missing.',10);
2276 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference is missing.');
2277 l_err := true;
2278 elsif validate_input(l_payroll_rec.tax_ref_no,'FULL_EDI') > 0 then
2279 hr_utility.set_location('The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.',10);
2280 fnd_file.put_line(fnd_file.output,'The Employer PAYE Reference '||l_payroll_rec.tax_ref_no||' has invalid characters.');
2281 l_err := true;
2282 end if;
2283
2284 if l_payroll_rec.employer_name = ' ' or l_payroll_rec.employer_name is null then
2285 hr_utility.set_location('The Employer Name is missing.',10);
2286 fnd_file.put_line(fnd_file.output,'The Employer Name is missing.');
2287 l_err := true;
2288 elsif validate_input(l_payroll_rec.employer_name,'EMP_NAME') > 0 then
2289 hr_utility.set_location('The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.',10);
2290 fnd_file.put_line(fnd_file.output,'The Employer Name '||l_payroll_rec.employer_name||' has invalid characters.');
2291 l_err := true;
2292 end if;
2293
2294 if l_payroll_rec.acc_ref_no = ' ' or l_payroll_rec.acc_ref_no is null then
2295 hr_utility.set_location('The Account Office Reference Number is missing.',10);
2296 fnd_file.put_line(fnd_file.output,'The Account Office Reference Number is missing.');
2297 l_err := true;
2298
2299 elsif (length(l_payroll_rec.acc_ref_no) > 13
2300 OR REGEXP_INSTR(l_payroll_rec.acc_ref_no,'^([[:digit:]]{1,3})P([[:alpha:]]{1})([[:digit:]]{1,7})([[:digit:]]|X)$') = 0 ) THEN
2301
2302 hr_utility.set_location ('Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.',10);
2303 fnd_file.put_line(fnd_file.output,'Incorrect format entered for Accounts Office Ref Num field. Valid format is NNNPANNNNNNNX.');
2304 l_err := true;
2305 END IF;
2306
2307 END IF;
2308
2309 if (l_err) then
2310 raise l_exp;
2311 end if;
2312
2313 hr_utility.set_location('Leaving '|| l_proc, 10);
2314 EXCEPTION
2315 when l_exp then
2316 g_validation_check := 'N';
2317 raise_application_error(-20001,'Errors found while archiving data.');
2318
2319 END archinit;
2320 --
2321 --
2322 --
2323 /*--------------------------------------------------------------------------
2324 Name : archive_data
2325 Purpose : This sets up the contexts needed for the live (non-archive)
2326 database items
2327 Arguments :
2328 Notes : Every possible context for a specific assignment action has to
2329 be added to the PL/SQL table
2330 --------------------------------------------------------------------------*/
2331 --
2332 --
2333 PROCEDURE archive_code(p_assactid IN NUMBER,
2334 p_effective_date IN DATE) IS
2335
2336 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
2337 error_found EXCEPTION;
2338 l_archive_tab action_info_table;
2339 l_archive_person boolean;
2340 l_archive_addr boolean;
2341 l_archive_type VARCHAR2(20);
2342 l_archive_asg boolean;
2343
2344 l_eas_etext_asg_flag varchar2(1);
2345 l_per_addr_val_flag varchar2(1);
2346 l_eas_val_err boolean := False;
2347 l_personaddr_val_err boolean := False;
2348 l_err_log number;
2349 l_eff_temp_date varchar2(25);
2350 l_eff_date Date;
2351 l_ter_eff_date date;
2352 l_tax_ref varchar2(30);
2353 l_assignment_id number;
2354
2355 l_effective_date date;
2356 l_pact_id number;
2357
2358 cursor csr_archive_type is
2359 select report_type,paa.payroll_action_id,
2360 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref
2361 from pay_assignment_actions paa,
2362 pay_payroll_actions ppa
2363 where paa.assignment_action_id = p_assactid
2364 and paa.payroll_action_id = ppa.payroll_action_id;
2365
2366 -- To fetch other assignments of the PAYE Aggregation
2367 cursor fetch_other_paye_agg_asg
2368 is
2369 select paaf.assignment_id
2370 from per_all_assignments_f paaf,
2371 pay_payrolls_f pay,
2372 hr_soft_coding_keyflex sck
2373 where paaf.person_id = l_archive_tab(0).person_id
2374 and paaf.assignment_id <> l_archive_tab(0).assignment_id
2375 and pay.payroll_id = paaf.payroll_id
2376 and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
2377 and sck.SEGMENT1 = l_tax_ref
2378 and paaf.EFFECTIVE_START_DATE =
2379 ( select max(paaf1.effective_start_date)
2380 from per_all_assignments_f paaf1
2381 where paaf1.assignment_id = paaf.assignment_id
2382 and paaf1.assignment_type = 'E'
2383 and paaf1.effective_start_date <= l_effective_date
2384 )
2385 and l_effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;
2386
2387 BEGIN
2388 hr_utility.set_location('Entering: '||l_proc,1);
2389
2390 open csr_archive_type;
2391 fetch csr_archive_type into l_archive_type,l_pact_id,l_tax_ref;
2392 close csr_archive_type;
2393
2394 -- For EAS
2395 IF l_archive_type = 'RTI_EAS_REP' THEN
2396 PAY_GB_P11D_ARCHIVE_SS.get_parameters(
2397 p_payroll_action_id => l_pact_id,
2398 p_token_name => 'START',
2399 p_token_value => l_eff_temp_date);
2400 l_eff_date := fnd_date.canonical_to_date(l_eff_temp_date);
2401
2402 END IF;
2403 l_effective_date := p_effective_date; -- For NINO
2404
2405
2406 -- For EAS
2407 IF l_archive_type = 'RTI_EAS_REP'
2408 THEN l_effective_date := l_eff_date;
2409 END IF;
2410 hr_utility.set_location('Archive Code l_effective_date Value:'||l_effective_date,909);
2411 l_archive_person := fetch_person_rec(p_assactid, l_effective_date,l_archive_tab(0));
2412
2413 l_archive_addr := fetch_address_rec(l_archive_tab(0).person_id,
2414 l_archive_tab(0).assignment_id,
2415 l_effective_date,
2416 l_archive_tab(1));
2417
2418 -- Fetching element details
2419
2420 hr_utility.trace('employee flag '||l_archive_tab(0).act_info18);
2421
2422 --Fetching assignments details for EAS
2423 IF l_archive_type = 'RTI_EAS_REP'
2424 THEN
2425 hr_utility.set_location('Fetching Assignment details ',30);
2426 l_archive_asg := fetch_eas_asg_rec(l_eff_date,p_assactid,l_archive_tab(0),l_archive_tab(2));
2427 END IF;
2428
2429 -- Validating fetched Person,Address,Assignment details for EAS
2430 IF l_archive_type = 'RTI_EAS_REP'
2431 THEN
2432 hr_utility.set_location('Calling Person Address validations', 10);
2433 person_addr_validations(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
2434 hr_utility.trace('person address' ||l_per_addr_val_flag);
2435 IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
2436 l_personaddr_val_err := TRUE;
2437 END IF;
2438
2439 hr_utility.set_location('Calling EAS validations', 10);
2440 eas_asg_etext_validations(p_assactid, p_effective_date, l_archive_tab, l_eas_etext_asg_flag);
2441 IF l_eas_etext_asg_flag = 'Y' THEN -- If validation fails
2442 l_eas_val_err := TRUE;
2443 END IF;
2444
2445 END IF;
2446 -- Validating fetched Person,Address details for NINO
2447 IF l_archive_type = 'RTI_NINO_REP'
2448 THEN
2449 hr_utility.set_location('Calling Person Address validations', 10);
2450 person_addr_validations_nino(p_assactid, p_effective_date, l_archive_tab, l_archive_type, l_per_addr_val_flag);
2451 hr_utility.trace('person address' ||l_per_addr_val_flag);
2452 IF l_per_addr_val_flag = 'Y' THEN -- If validation fails
2453 l_personaddr_val_err := TRUE;
2454 END IF;
2455 END IF;
2456
2457 if l_archive_person and l_archive_addr then
2458
2459 IF l_archive_type = 'RTI_EAS_REP'
2460 THEN
2461 IF l_personaddr_val_err OR l_eas_val_err -- If validation fails , that record will not be archived.
2462 THEN
2463 hr_utility.set_location('Validation failed, raise error.',999);
2464 raise error_found;
2465 ELSE
2466 hr_utility.set_location('Validation successful, archive data.',999);
2467 set_new_starter_rti_sent(l_archive_tab(0).assignment_id);
2468
2469 -- Set the RTI Sent for the other assignments of the PAYE Aggregation
2470 open fetch_other_paye_agg_asg;
2471 loop
2472 fetch fetch_other_paye_agg_asg into l_assignment_id;
2473 exit when fetch_other_paye_agg_asg%notfound;
2474 hr_utility.set_location('Checking the RTI Sent flag for other assignments in PAYE Aggregation.',999);
2475 set_new_starter_rti_sent(l_assignment_id);
2476 end loop;
2477 close fetch_other_paye_agg_asg;
2478
2479 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
2480 END IF;
2481 END IF;
2482
2483 IF l_archive_type = 'RTI_NINO_REP'
2484 THEN
2485 IF l_personaddr_val_err -- If validation fails , that record will not be archived.
2486 THEN
2487 hr_utility.set_location('Validation failed, raise error.',999);
2488 raise error_found;
2489 ELSE
2490 hr_utility.set_location('Validation successful, archive data.',999);
2491 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
2492 END IF;
2493 END IF;
2494
2495 else
2496 raise error_found;
2497 end if;
2498
2499 hr_utility.set_location('Leaving: '||l_proc,999);
2500
2501 EXCEPTION
2502 when error_found then
2503 IF l_archive_type in ('RTI_EAS_REP','RTI_NINO_REP')
2504 THEN
2505 raise_application_error(-20001,'Errors found while archiving data.');
2506 ELSE
2507 hr_utility.raise_error;
2508 END IF;
2509 END archive_code;
2510 --
2511 --
2512 PROCEDURE deinitialization_code(pactid IN NUMBER)
2513 IS
2514 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
2515 l_counter number;
2516
2517 Cursor csr_is_etext_report IS
2518 Select report_type
2519 From pay_payroll_actions pact
2520 Where pact.payroll_action_id = pactid;
2521
2522 l_is_etext_report varchar2(50);
2523 l_request_id fnd_concurrent_requests.request_id%TYPE;
2524 xml_layout boolean;
2525
2526 -- Cursor to fetch Employer details
2527 cursor csr_payroll_details(pactid NUMBER) is
2528 select
2529 nvl(UPPER(hoi.org_information11),' ') sender_id,
2530 decode(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TEST=') + 5,1) ,'N',' ','Y','1') test_indicator,
2531 fnd_number.number_to_canonical(pact.request_id) request_id,
2532 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
2533 nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' ') tax_ref_no,
2534 lpad(substr(hoi.ORG_INFORMATION1,0,instr(hoi.ORG_INFORMATION1,'/')-1),3,0) tax_office_no,
2535 decode(PAY_GB_EOY_MAGTAPE.get_payroll_version, ' ', '0', PAY_GB_EOY_MAGTAPE.get_payroll_version) payroll_ver,
2536 (lpad(substr(hoi.ORG_INFORMATION6,0,instr(hoi.ORG_INFORMATION6,'P')-1),3,0)
2537 || 'P'
2538 || substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+1,1)
2539 || lpad(substr(hoi.ORG_INFORMATION6,instr(hoi.ORG_INFORMATION6,'P')+2,length(hoi.ORG_INFORMATION6)-3-(instr(hoi.ORG_INFORMATION6,'P')-1)),7,0)
2540 || substr(hoi.ORG_INFORMATION6,length(hoi.ORG_INFORMATION6),1)
2541 )
2542 as acc_ref_no,
2543
2544 pact.business_group_id bus_grp_id,
2545 pact.action_parameter_group_id act_param_grp_id
2546 from pay_payroll_actions pact,
2547 hr_organization_information hoi
2548 where pact.payroll_action_id=pactid
2549 and pact.business_group_id = hoi.organization_id
2550 and hoi.org_information_context = 'Tax Details References'
2551 and nvl(hoi.org_information10,'UK') = 'UK'
2552 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
2553 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
2554 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
2555
2556 l_payroll_rec csr_payroll_details%rowtype;
2557 l_acc_ref_no hr_organization_information.org_information6%type;
2558 l_action_info_id NUMBER(15);
2559 l_ovn NUMBER;
2560 l_exp exception;
2561 ---------------------
2562 ---------------------
2563 procedure write_header(report_type varchar2) is
2564 l_token varchar2(255);
2565 l_addr1 varchar2(255);
2566 l_addr2 varchar2(255);
2567 l_addr3 varchar2(255);
2568 l_addr4 varchar2(255);
2569 l_form varchar2(40);
2570 l_tax_ref varchar2(20);
2571 l_urgent varchar2(2);
2572 l_test varchar2(2);
2573 l_temp number;
2574 l_form_name varchar2(100);
2575
2576 cursor csr_leg_param is
2577 select legislative_parameters para,
2578 fnd_number.number_to_canonical(request_id) control_id,
2579 report_type,
2580 business_group_id
2581 from pay_payroll_actions pact
2582 where payroll_action_id = pactid;
2583
2584 cursor csr_date is
2585 select to_char(to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'START=') + 6,10),'YYYY-MM-DD'),'DD-MON-RRRR') effective_date
2586 from pay_payroll_actions pact
2587 where payroll_action_id = pactid;
2588
2589 cursor csr_header_det(p_bus_id number,
2590 p_tax_ref varchar2) is
2591 select nvl(hoi.org_information11,' ') sender_id,
2592 nvl(upper(hoi.org_information2),' ') hrmc_office,
2593 nvl(upper(hoi.org_information4),' ') er_addr,
2594 nvl(upper(hoi.org_information3),' ') er_name
2595 from hr_organization_information hoi
2596 where hoi.organization_id = p_bus_id
2597 and hoi.org_information_context = 'Tax Details References'
2598 and nvl(hoi.org_information10,'UK') = 'UK'
2599 and upper(hoi.org_information1) = upper(p_tax_ref);
2600
2601 l_param csr_leg_param%rowtype;
2602 l_det csr_header_det%rowtype;
2603 l_eff_date date;
2604 begin
2605
2606 open csr_leg_param;
2607 fetch csr_leg_param into l_param;
2608 close csr_leg_param;
2609
2610 if l_param.report_type = 'RTI_EAS_REP' then
2611 open csr_date;
2612 fetch csr_date into l_eff_date;
2613 close csr_date;
2614 end if;
2615
2616 l_token := 'TAX_REF';
2617 l_temp := instr(l_param.para,l_token);
2618 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
2619 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
2620
2621 l_token := 'TEST';
2622 l_temp := instr(l_param.para,l_token);
2623 l_test := substr(l_param.para, l_temp + length(l_token) + 1,
2624 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
2625
2626 open csr_header_det(l_param.business_group_id, l_tax_ref);
2627 fetch csr_header_det into l_det;
2628 close csr_header_det;
2629
2630 l_addr1 := l_det.er_addr;
2631 if length(l_addr1) > 35 then
2632 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
2633 if l_temp = 0 then
2634 l_temp := 35;
2635 end if;
2636 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
2637 l_addr1 := substr(l_addr1,1,l_temp);
2638 end if;
2639 if length(l_addr2) > 35 then
2640 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
2641 if l_temp = 0 then
2642 l_temp := 35;
2643 end if;
2644 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
2645 l_addr2 := substr(l_addr2,1,l_temp);
2646 end if;
2647 if length(l_addr3) > 35 then
2648 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
2649 if l_temp = 0 then
2650 l_temp := 35;
2651 end if;
2652 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
2653 l_addr4 := substr(l_addr3,1,l_temp);
2654 end if;
2655
2656
2657 if l_param.report_type = 'RTI_EAS_REP' then
2658 l_form := 'RTI_EAS_REP';
2659 l_form_name :='Employer Alignment Submission';
2660 elsif l_param.report_type = 'RTI_NINO_REP' then
2661 l_form := 'RTI_NINO_REP';
2662 l_form_name :='NINO Verification Request Process';
2663 end if;
2664
2665 fnd_file.put_line(fnd_file.output,' ');
2666 fnd_file.put_line(fnd_file.output,'EDI Transmission Report:');
2667 fnd_file.put_line(fnd_file.output,' ');
2668 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form_name);
2669 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
2670 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
2671 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
2672 fnd_file.put_line(fnd_file.output,rpad('Test Transmission : ',32) || l_test);
2673 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
2674 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
2675 fnd_file.put_line(fnd_file.output,rpad('HRMC Office : ',32) || l_det.hrmc_office);
2676 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
2677 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
2678
2679 if length(l_addr2) > 0 then
2680 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
2681 end if;
2682 if length(l_addr3) > 0 then
2683 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
2684 end if;
2685 if length(l_addr4) > 0 then
2686 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
2687 end if;
2688 if report_type = 'RTI_EAS_REP' then
2689 fnd_file.put_line(fnd_file.output,rpad('Effective Date : ',32) || l_eff_date);
2690 end if;
2691
2692 end write_header;
2693
2694 procedure write_sub_header(p_type varchar2,report_type varchar2) is
2695 begin
2696 fnd_file.put_line(fnd_file.output,null);
2697 if p_type = 'E' then
2698 fnd_file.put_line(fnd_file.output,'The following employments have completed with error');
2699 else
2700 fnd_file.put_line(fnd_file.output,'The following employments have completed successfully');
2701 end if;
2702
2703 if report_type = 'RTI_EAS_REP' then
2704
2705 if p_type <> 'E' then --Completed records
2706 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
2707 rpad('NI Number',11) ||
2708 rpad('Employee Name', 51) ||
2709 rpad('Start Date',16) ||
2710 rpad('End Date',16));
2711 else -- errored records
2712 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
2713 rpad('NI Number',11) ||
2714 rpad('Employee Name', 51) ||
2715 rpad('Start Date',16) ||
2716 rpad('End Date',16) ||
2717 rpad('Error Messages',30));
2718 end if;
2719
2720
2721 if p_type <> 'E' then -- Completed records
2722 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
2723 rpad('-',10,'-') || ' ' ||
2724 rpad('-',50,'-') || ' ' ||
2725 rpad('-',15,'-') || ' ' ||
2726 rpad('-',15,'-') );
2727
2728 else -- errored records
2729 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
2730 rpad('-',10,'-') || ' ' ||
2731 rpad('-',50,'-') || ' ' ||
2732 rpad('-',15,'-') || ' ' ||
2733 rpad('-',15,'-') || ' ' ||
2734 rpad('-',30,'-'));
2735 end if;
2736 end if;
2737
2738 if report_type = 'RTI_NINO_REP' then
2739
2740 if p_type <> 'E' then -- completed records
2741 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
2742 rpad('NI Number',11) ||
2743 rpad('Employee Name', 52));
2744 else
2745 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
2746 rpad('NI Number',11) ||
2747 rpad('Employee Name', 51) ||
2748 rpad('Error Messages',30));
2749 end if;
2750
2751
2752 if p_type <> 'E' then
2753 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
2754 rpad('-',10,'-') || ' ' ||
2755 rpad('-',52,'-') );
2756
2757 else
2758 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
2759 rpad('-',10,'-') || ' ' ||
2760 rpad('-',50,'-') || ' ' ||
2761 rpad('-',30,'-'));
2762 end if;
2763 end if;
2764
2765 end write_sub_header;
2766
2767 procedure write_body(p_type varchar2,pactid IN NUMBER,report_type varchar2) is
2768 l_count number;
2769 i number;
2770 l_temp varchar2(255);
2771 l_emp_count number;
2772 l_number varchar2(30);
2773
2774 l_start_date date;
2775 l_end_date date;
2776 l_nino_eff_date date;
2777
2778 /*
2779 -- Cursor to fetch Completed records
2780 cursor get_asg_action_id is
2781 select distinct paa.assignment_action_id asg_action_id, assignment_id
2782 from pay_payroll_actions ppa,
2783 pay_assignment_actions paa
2784 where ppa.payroll_action_id = pactid -- pact_id
2785 and paa.payroll_action_id = ppa.payroll_action_id
2786 and paa.action_status = 'C';
2787 -- Cursor to fetch Errored Records
2788 cursor get_asg_action_id_error is
2789 select distinct paa.assignment_action_id asg_action_id, assignment_id
2790 from pay_payroll_actions ppa,
2791 pay_assignment_actions paa
2792 where ppa.payroll_action_id = pactid -- pact_id
2793 and paa.payroll_action_id = ppa.payroll_action_id
2794 and paa.action_status = 'E';
2795 */
2796 -- Cursor to fetch Completed records
2797 cursor get_asg_action_id is
2798 select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
2799 , paaf.assignment_number
2800 from pay_payroll_actions ppa,
2801 pay_assignment_actions paa
2802 , per_all_assignments_f paaf
2803 where ppa.payroll_action_id = pactid -- pact_id
2804 and paa.payroll_action_id = ppa.payroll_action_id
2805 and paa.action_status = 'C'
2806 and paaf.assignment_id = paa.assignment_id
2807 and paaf.effective_start_date =
2808 (
2809 SELECT MAX(paaf2.effective_start_date)
2810 FROM per_all_assignments_f paaf2
2811 WHERE paaf2.assignment_id = paaf.assignment_id
2812 )
2813 ORDER BY
2814 paaf.assignment_number;
2815
2816
2817 -- Cursor to fetch Errored Records
2818 cursor get_asg_action_id_error is
2819 select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
2820 , paaf.assignment_number
2821 from pay_payroll_actions ppa,
2822 pay_assignment_actions paa
2823 , per_all_assignments_f paaf
2824 where ppa.payroll_action_id = pactid -- pact_id
2825 and paa.payroll_action_id = ppa.payroll_action_id
2826 and paa.action_status = 'E'
2827 and paaf.assignment_id = paa.assignment_id
2828 and paaf.effective_start_date =
2829 (
2830 SELECT MAX(paaf2.effective_start_date)
2831 FROM per_all_assignments_f paaf2
2832 WHERE paaf2.assignment_id = paaf.assignment_id
2833 )
2834 ORDER BY
2835 paaf.assignment_number;
2836
2837
2838 --Cursor to get effective date for NINO
2839 cursor csr_parameter_date is
2840 select effective_date
2841 from pay_payroll_actions
2842 where payroll_action_id = pactid;
2843
2844
2845 cursor get_person_details_archive(c_assignment_id number) is
2846 select pai_emp.action_information3 l_name,
2847 pai_emp.action_information4 f_name,
2848 pai_emp.action_information5 m_name,
2849 pai_emp.action_information6 title,
2850 pai_emp.action_information17 emp_no,
2851 nvl(pai_emp.action_information7,' ')ni_no,
2852 pai_emp.action_information17 emp_no1,
2853 to_char(to_date(substr(pai_asg.action_information5,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') start_date,
2854 to_char(to_date(substr(pai_asg.action_information6,1,10),'YYYY-MM-DD'),'DD-MON-RRRR') end_date,
2855 pai_emp.action_information17 emp_no2
2856 from pay_action_information pai_emp,
2857 pay_action_information pai_asg,
2858 pay_assignment_actions paa
2859 where paa.payroll_action_id = pactid and paa.ACTION_STATUS = 'C' and paa.assignment_id = c_assignment_id
2860 and pai_emp.action_context_id = paa.assignment_action_id
2861 and pai_emp.action_information_category = 'GB RTI EMPLOYEE DETAILS'
2862 and pai_emp.action_context_type = 'AAP'
2863 and pai_asg.action_context_id = paa.assignment_action_id
2864 and pai_asg.action_information_category = 'GB RTI ASG DETAILS'
2865 and pai_asg.action_context_type = 'AAP';
2866
2867 /*
2868 cursor get_person_details(c_assignment_id number) is
2869 select distinct pap.first_name f_name ,
2870 pap.middle_names m_name,
2871 pap.last_name l_name,
2872 pap.title title,
2873 paa.assignment_number emp_no,
2874 nvl(pap.national_identifier,' ')ni_no,
2875 nvl(pap.employee_number,' ') employee_number,
2876 to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
2877 paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
2878 pap.person_id
2879 from per_all_assignments_f paa,
2880 per_assignment_status_types past,
2881 per_all_people_f pap
2882 where paa.person_id = pap.person_id
2883 and paa.assignment_id = c_assignment_id
2884 and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
2885 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Added for Bug#13626488
2886 order by end_date desc;
2887 */
2888 cursor get_person_details(c_assignment_id number) is
2889 select distinct pap.first_name f_name ,
2890 pap.middle_names m_name,
2891 pap.last_name l_name,
2892 pap.title title,
2893 paa.assignment_number emp_no,
2894 nvl(pap.national_identifier,' ')ni_no,
2895 nvl(pap.employee_number,' ') employee_number,
2896 to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
2897 paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
2898 pap.person_id
2899 from per_all_assignments_f paa,
2900 per_assignment_status_types past,
2901 per_all_people_f pap
2902 ,pay_payroll_actions ppa
2903 where paa.person_id = pap.person_id
2904 and paa.assignment_id = c_assignment_id
2905 and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
2906 AND ppa.payroll_action_id = pactid
2907 AND ppa.effective_date BETWEEN pap.effective_start_date
2908 AND pap.effective_end_date
2909 AND paa.assignment_type = 'E'
2910 AND paa.effective_start_date =
2911 (
2912 SELECT MAX(paa2.effective_start_date)
2913 FROM per_all_assignments_f paa2
2914 WHERE paa2.assignment_id = paa.assignment_id
2915 AND paa2.effective_start_date <= ppa.effective_date
2916 )
2917 order by end_date desc;
2918
2919 cursor get_person_details_nino(c_assignment_id number) is
2920 select distinct pap.first_name f_name ,
2921 pap.middle_names m_name,
2922 pap.last_name l_name,
2923 pap.title title,
2924 paa.assignment_number emp_no,
2925 nvl(substr(pap.national_identifier,1,9),' ')ni_no,
2926 nvl(pap.employee_number,' ') employee_number,
2927 to_char(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
2928 paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
2929 pap.person_id
2930 from per_all_assignments_f paa,
2931 per_assignment_status_types past,
2932 per_all_people_f pap
2933 where paa.person_id = pap.person_id
2934 and paa.assignment_id = c_assignment_id
2935 and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
2936 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2937 and l_nino_eff_date between pap.EFFECTIVE_START_DATE and pap.EFFECTIVE_END_DATE
2938 order by end_date desc;
2939 -- Cursor to fetch error Messages from pay_message lines table
2940 cursor csr_err_msg(asg_action_id number) is
2941 select distinct line_text from pay_message_lines where source_id = asg_action_id and payroll_id = 100;
2942
2943 err_msg csr_err_msg%rowtype;
2944
2945 et_asg_rec get_person_details%rowtype;
2946 asg_rec get_person_details%rowtype;
2947 asg_rec1 get_person_details_archive%rowtype;
2948
2949 l_et_temp varchar2(255);
2950 begin
2951
2952 l_count := 0;
2953 if report_type = 'RTI_NINO_REP' then
2954 open csr_parameter_date;
2955 fetch csr_parameter_date into l_nino_eff_date;
2956 close csr_parameter_date;
2957
2958 hr_utility.set_location('l_nino_eff_date '||l_nino_eff_date,009);
2959 end if;
2960 --Completed Records
2961 IF p_type = 'ET' THEN
2962 FOR action_id IN get_asg_action_id LOOP
2963
2964 if report_type = 'RTI_NINO_REP' then
2965 open get_person_details_nino(action_id.assignment_id);
2966 fetch get_person_details_nino into et_asg_rec;
2967 close get_person_details_nino;
2968 elsif report_type = 'RTI_EAS_REP' then
2969 open get_person_details_archive(action_id.assignment_id);
2970 fetch get_person_details_archive into asg_rec1;
2971 close get_person_details_archive;
2972 end if;
2973
2974
2975 if report_type = 'RTI_EAS_REP' then
2976 l_et_temp := asg_rec1.l_name || ', '|| asg_rec1.title || ' ' ||
2977 asg_rec1.f_name || ' ' || asg_rec1.m_name;
2978 elsif report_type = 'RTI_NINO_REP' then
2979 l_et_temp := et_asg_rec.l_name || ', '|| et_asg_rec.title || ' ' ||
2980 et_asg_rec.f_name || ' ' || et_asg_rec.m_name;
2981 end if;
2982
2983
2984 if report_type = 'RTI_EAS_REP' then
2985 fnd_file.put_line(fnd_file.output,rpad(asg_rec1.emp_no, 18) || ' ' ||
2986 rpad(asg_rec1.ni_no ,10) || ' ' ||
2987 rpad(l_et_temp,50) || ' ' ||
2988 rpad(asg_rec1.start_date,15) || ' ' ||
2989 rpad(asg_rec1.end_date,15) );
2990
2991 elsif report_type = 'RTI_NINO_REP' then
2992 fnd_file.put_line(fnd_file.output,rpad(et_asg_rec.employee_number, 18) || ' ' ||
2993 rpad(et_asg_rec.ni_no ,10) || ' ' ||
2994 rpad(l_et_temp,50));
2995 end if;
2996
2997 l_count := l_count + 1;
2998 END LOOP;
2999 END IF;
3000 -- Errored Records
3001 IF p_type = 'E' THEN null;
3002 FOR action_id IN get_asg_action_id_error LOOP
3003
3004 -- Fetching details
3005
3006
3007 if report_type = 'RTI_NINO_REP' then
3008 open get_person_details_nino(action_id.assignment_id);
3009 fetch get_person_details_nino into asg_rec;
3010 close get_person_details_nino;
3011 elsif report_type = 'RTI_EAS_REP' then
3012 open get_person_details(action_id.assignment_id);
3013 fetch get_person_details into asg_rec;
3014 close get_person_details;
3015 end if;
3016
3017 open csr_err_msg(action_id.asg_action_id);
3018 fetch csr_err_msg into err_msg;
3019
3020 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
3021 asg_rec.f_name || ' ' || asg_rec.m_name;
3022
3023 if report_type = 'RTI_EAS_REP' then
3024 if asg_rec.end_date = fnd_date.canonical_to_date('4712/12/31 00:00:00') then
3025 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3026 rpad(asg_rec.ni_no ,10) || ' ' ||
3027 rpad(l_temp,50) || ' ' ||
3028 rpad(asg_rec.start_date,15) || ' ' ||
3029 rpad(' ' ,15) || ' ' ||
3030 rpad(err_msg.line_text,100) ); -- error message
3031 else
3032 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
3033 rpad(asg_rec.ni_no ,10) || ' ' ||
3034 rpad(l_temp,50) || ' ' ||
3035 rpad(asg_rec.start_date,15) || ' ' ||
3036 rpad(asg_rec.end_date,15) || ' ' ||
3037 rpad(err_msg.line_text,100) ); -- error message
3038
3039 end if;
3040 elsif report_type = 'RTI_NINO_REP' then
3041 fnd_file.put_line(fnd_file.output,rpad(asg_rec.employee_number, 18) || ' ' ||
3042 rpad(asg_rec.ni_no ,10) || ' ' ||
3043 rpad(l_temp,50) || ' ' ||
3044 rpad(err_msg.line_text,100) ); -- error message
3045 end if;
3046 -- To print remaining error messgaes if any
3047 loop
3048 fetch csr_err_msg into err_msg;
3049 exit when csr_err_msg%notfound;
3050 if report_type = 'RTI_EAS_REP' then
3051 fnd_file.put_line(fnd_file.output,rpad(' ',113,' ')||rpad(err_msg.line_text,100));
3052 elsif report_type = 'RTI_NINO_REP' then
3053 fnd_file.put_line(fnd_file.output,rpad(' ',81,' ')||rpad(err_msg.line_text,100));
3054 end if;
3055 end loop;
3056 close csr_err_msg;
3057
3058 l_count := l_count + 1;
3059 END LOOP;
3060 END IF;
3061
3062 fnd_file.put_line(fnd_file.output,null);
3063 if p_type = 'E' then
3064 fnd_file.put_line(fnd_file.output,'Total Number of employments completed with error : ' || l_count);
3065 else
3066 fnd_file.put_line(fnd_file.output,'Total Number of employments completed successfully :' || l_count);
3067 end if;
3068 l_counter := l_counter + l_count;
3069 end write_body;
3070
3071 procedure write_footer is
3072
3073 cursor get_person_count is
3074 select count(distinct(paaf.person_id))
3075 from pay_payroll_actions ppa,
3076 pay_assignment_actions paa,
3077 per_all_assignments_f paaf
3078 where ppa.payroll_action_id = pactid -- pact_id
3079 and paa.payroll_action_id = ppa.payroll_action_id
3080 and paa.action_status = 'C'
3081 and paaf.assignment_id = paa.assignment_id;
3082
3083 l_person_count number;
3084 begin
3085 open get_person_count;
3086 fetch get_person_count into l_person_count;
3087 close get_person_count;
3088
3089 fnd_file.put_line(fnd_file.output,null);
3090 fnd_file.put_line(fnd_file.output,'Total Number Of employments : ' || l_counter);
3091 fnd_file.put_line(fnd_file.output,null);
3092 fnd_file.put_line(fnd_file.output,'Total Number of employees successfully processed : ' || l_person_count);
3093 end write_footer;
3094
3095 ---------------------
3096 ---------------------
3097 BEGIN
3098 hr_utility.trace('Entering deinit: '||l_proc);
3099
3100 -- To avoid re-archiving while Retry
3101 delete from pay_action_information pai
3102 where pai.action_context_id = pactid
3103 and pai.action_context_type = 'PA'
3104 and pai.action_information_category in ('RTI PAYROLL INFO');
3105
3106 open csr_payroll_details(pactid);
3107 fetch csr_payroll_details into l_payroll_rec;
3108 close csr_payroll_details;
3109
3110 pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
3111
3112 hr_utility.set_location('l_payroll_rec.bus_grp_id = ' || l_payroll_rec.bus_grp_id,10);
3113 hr_utility.set_location('l_payroll_rec.act_param_grp_id = ' || l_payroll_rec.act_param_grp_id,20);
3114 hr_utility.set_location('l_sender_id = '||l_payroll_rec.sender_id,30);
3115 hr_utility.set_location('l_test_indicator = '||l_payroll_rec.test_indicator,40);
3116 hr_utility.set_location('l_request_id = '||l_payroll_rec.request_id,50);
3117
3118 hr_utility.set_location('Archiving RTI PAYROLL INFO',60);
3119
3120 pay_action_information_api.create_action_information (
3121 p_action_information_id => l_action_info_id
3122 , p_action_context_id => pactid
3123 , p_action_context_type => 'PA'
3124 , p_object_version_number => l_ovn
3125 -- , p_effective_date => l_payroll_rec.effective_date
3126 , p_source_id => NULL
3127 , p_source_text => NULL
3128 , p_action_information_category => 'RTI PAYROLL INFO'
3129 , p_action_information1 => pactid
3130 , p_action_information3 => NULL
3131 , p_action_information4 => l_payroll_rec.employer_name
3132 , p_action_information6 => l_payroll_rec.tax_office_no
3133 , p_action_information7 => l_payroll_rec.tax_ref_no
3134 , p_action_information8 => l_payroll_rec.payroll_ver
3135 , p_action_information11 => l_payroll_rec.acc_ref_no
3136 , p_action_information12 => l_payroll_rec.sender_id
3137 , p_action_information13 => l_payroll_rec.test_indicator
3138 , p_action_information14 => to_char(l_payroll_rec.request_id));
3139
3140 OPEN csr_is_etext_report;
3141 FETCH csr_is_etext_report INTO l_is_etext_report;
3142 CLOSE csr_is_etext_report;
3143
3144 l_counter := 0;
3145 write_header(l_is_etext_report);
3146 write_sub_header('C',l_is_etext_report);
3147
3148 IF l_is_etext_report IN ('RTI_EAS_REP', 'RTI_NINO_REP')
3149 THEN
3150 write_body('ET',pactid,l_is_etext_report);
3151 ELSE
3152 write_body('C',pactid,l_is_etext_report);
3153 END IF;
3154
3155 write_sub_header('E',l_is_etext_report);
3156 write_body('E',pactid,l_is_etext_report);
3157 write_footer;
3158
3159 -- For Spawning the output Concurrent Program
3160 IF (l_is_etext_report='RTI_EAS_REP') THEN
3161
3162 hr_utility.set_location('Spawning RTI Employer Alignment Submission Output process',1);
3163
3164 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTIEASOP','en','US','ETEXT');
3165 IF (xml_layout = true and g_validation_check = 'Y')
3166 THEN
3167
3168 l_request_id := fnd_request.submit_request
3169 (application => 'PAY'
3170 ,program => 'PYGBRTIEASOP'
3171 ,argument1 => pactid
3172 );
3173
3174 Commit;
3175 --check for process submit error
3176 IF l_request_id = 0
3177 THEN
3178 hr_utility.set_location('Error spawning new process',1);
3179 END IF;
3180 END IF;
3181 END IF;
3182 IF (l_is_etext_report='RTI_NINO_REP') THEN
3183
3184 hr_utility.set_location('Spawning RTI NINO verification Output process',1);
3185
3186 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBRTININOOP','en','US','ETEXT');
3187
3188 IF xml_layout = true
3189 THEN
3190
3191 l_request_id := fnd_request.submit_request
3192 (application => 'PAY'
3193 ,program => 'PYGBRTININOOP'
3194 ,argument1 => pactid
3195 );
3196
3197 /*l_request_id := fnd_request.submit_request
3198 (application => 'PAY'
3199 ,program => 'PYGBRTININOOP'
3200 ,argument1 => 'ARCHIVE' --Process Name
3201 ,argument2 => 'RTI_NINO_OUTPUT'
3202 ,argument3 => 'GB'
3203 ,argument4 => '0001/01/01 00:00:00'
3204 ,argument5 => fnd_date.date_to_canonical(sysdate)
3205 ,argument6 => 'XML'
3206
3207 ,argument7 => l_payroll_rec.bus_grp_id
3208 ,argument8 => null --Magnetic File Name
3209 ,argument9 => null --Report File Name
3210 ,argument10 => l_payroll_rec.act_param_grp_id --Action Parameter Group
3211 ,argument11 => 'ARCHIVE_REQUEST_ID='||pactid
3212
3213 ,argument12 => 'REP_GROUP=RTI_NINO_OUTPUT'
3214 ,argument13 => 'REP_CAT=RTI_NINO_OUTPUT');
3215
3216 */
3217 Commit;
3218
3219 --check for process submit error
3220 IF l_request_id = 0
3221 THEN
3222 hr_utility.set_location('Error spawning new process',1);
3223 END IF;
3224 END IF;
3225
3226
3227 END IF;
3228
3229 hr_utility.set_location('Leaving: '||l_proc,999);
3230
3231 END deinitialization_code;
3232 --
3233 -- Function for validating Date fields
3234 FUNCTION date_validate (c_assignment_action_id NUMBER,
3235 p_mode VARCHAR2,
3236 p_validate_date DATE)
3237 RETURN NUMBER
3238 IS
3239
3240
3241 cursor csr_parameter_info is
3242 select pay_gb_eoy_archive.get_parameter(legislative_parameters, 'TEST'),
3243 NVL(fnd_date.canonical_to_date(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'START')),sysdate)
3244 from pay_payroll_actions ppa
3245 ,pay_assignment_actions paa
3246 where paa.assignment_action_id = c_assignment_action_id
3247 and ppa.payroll_action_id = paa.payroll_action_id;
3248
3249 l_date_valid DATE;
3250 l_return_valid NUMBER;
3251 l_test_submission VARCHAR2(1);
3252 l_tax_date DATE;
3253 l_tax_year VARCHAR2(4);
3254 l_tax_year_start DATE ;
3255 l_effective_date DATE;
3256
3257 BEGIN
3258 l_return_valid := 1;
3259 open csr_parameter_info;
3260 fetch csr_parameter_info into l_test_submission,l_tax_date;
3261 close csr_parameter_info;
3262
3263 l_effective_date:= l_tax_date;
3264
3265 l_tax_year := to_char(l_tax_date,'RRRR');
3266 if (l_tax_date > to_date(l_tax_year||'0405','RRRRMMDD')) THEN
3267 l_tax_date := ADD_MONTHS(to_date(l_tax_year||'0405','RRRRMMDD'),12) ; /*tax year end date*/
3268 else
3269 l_tax_date := to_date(l_tax_year||'0405','RRRRMMDD'); /*tax year end date*/
3270 end if;
3271
3272 l_tax_date := fnd_date.canonical_to_date(to_char(l_tax_date,'RRRRMMDD'));
3273
3274 l_tax_year_start := add_months(l_tax_date,-12)+1 ;
3275
3276 l_date_valid := p_validate_date;
3277
3278 hr_utility.set_location('date_validate.l_effective_date '||l_effective_date,1);
3279 hr_utility.set_location('date_validate.l_tax_date'||l_tax_date,1);
3280
3281 -- Start Date Validation
3282 if (p_mode = 'UK_EMPL_DATE') then
3283 if (l_test_submission = 'N') then
3284 if (l_date_valid > (l_effective_date+30)) then
3285 l_return_valid := 0;
3286 end if;
3287 else
3288 if (l_date_valid > add_months(l_tax_date,12)) then
3289 l_return_valid := 0;
3290 end if;
3291 end if;
3292 -- End Date Validation
3293 elsif (p_mode = 'UK_EMPL_DATE_LEAVING') then
3294 if (l_test_submission = 'N') then
3295 if (l_date_valid > (l_effective_date+30)) then
3296 l_return_valid := 0;
3297 end if;
3298 if(l_date_valid < add_months(l_tax_year_start,-12)) then
3299 l_return_valid := 0;
3300 end if;
3301
3302 else
3303 if (l_date_valid > add_months(l_tax_date,12)) then
3304 l_return_valid := 0;
3305 -- elsif(l_date_valid < (add_months(l_tax_date,-24)+1)) then
3306 -- l_return_valid := 0;
3307 end if;
3308 end if;
3309
3310 end if;
3311
3312 return l_return_valid;
3313 END date_validate;
3314
3315 function get_eff_start_date (asg_id number,eff_date date,l_tax_ref varchar2)
3316 return date is
3317 l_eff_date date;
3318 cursor csr_get_start_date is
3319 select paaf.effective_start_date from
3320 per_all_assignments_f paaf,
3321 pay_all_payrolls_f papf ,
3322 hr_soft_coding_keyflex flex,
3323 per_assignment_status_types past
3324 where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
3325 and papf.payroll_id = paaf.payroll_id
3326 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
3327 and upper(l_tax_ref) = upper(flex.segment1)
3328 and paaf.assignment_status_type_id = past.assignment_status_type_id
3329 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
3330 begin
3331 open csr_get_start_date;
3332 fetch csr_get_start_date into l_eff_date;
3333 if csr_get_start_date%notfound then
3334 close csr_get_start_date;
3335 return eff_date;
3336 else
3337 close csr_get_start_date;
3338 l_eff_date := get_eff_start_date(asg_id,l_eff_date,l_tax_ref);
3339 return l_eff_date;
3340 end if;
3341 end;
3342
3343 function get_eff_end_date (asg_id number,eff_date date,l_tax_ref varchar2)
3344 return date is
3345 l_eff_date date;
3346 cursor csr_get_end_date is
3347 select paaf.effective_end_date
3348 from
3349 per_all_assignments_f paaf,
3350 pay_all_payrolls_f papf ,
3351 hr_soft_coding_keyflex flex,
3352 per_assignment_status_types past
3353 where paaf.assignment_id = asg_id and paaf.effective_start_date = eff_date + 1
3354 and papf.payroll_id = paaf.payroll_id
3355 and flex.SOFT_CODING_KEYFLEX_ID = papf.SOFT_CODING_KEYFLEX_ID
3356 and upper(l_tax_ref) = upper(flex.segment1)
3357 and paaf.assignment_status_type_id = past.assignment_status_type_id
3358 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
3359
3360 begin
3361 open csr_get_end_date;
3362 fetch csr_get_end_date into l_eff_date;
3363 if csr_get_end_date%notfound then
3364 close csr_get_end_date;
3365 return eff_date;
3366 else
3367 close csr_get_end_date;
3368 l_eff_date := get_eff_end_date(asg_id,l_eff_date,l_tax_ref);
3369 return l_eff_date;
3370 end if;
3371 end;
3372
3373 --Procedure to fetch Tax year Start date using effective date parameter of concurrent Program.
3374 FUNCTION fetch_soy(l_effective_date date) return date
3375 IS
3376 l_tax_year_start_date DATE;
3377 BEGIN
3378 If l_effective_date >= to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) Then
3379 l_tax_year_start_date := to_date('06-04-'||substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4),'DD-MM-YYYY' ) ;
3380 Else
3381 l_tax_year_start_date := to_date('06-04-'||to_char(to_number(substr(to_char(l_effective_date,'YYYY/MON/DD'),1,4))-1 ),'DD-MM-YYYY') ;
3382 End If;
3383 return l_tax_year_start_date;
3384 END fetch_soy;
3385
3386 end PAY_GB_RTI;