[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_WNU_EDI
Source
1 PACKAGE BODY PAY_GB_WNU_EDI as
2 /* $Header: pygbwnu2.pkb 120.7.12010000.2 2008/08/06 07:21:14 ubhat ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name
9 PAY_GB_WNU_EDI
10 Purpose
11 Package to control archiver process in the creation of assignment actions
12 and the creation of EDI Message files uing the magtape process for EDI
13 Message Types : WNU
14 This is a UK Specific payroll package.
15 Notes
16
17 History
18 01-NOV-2000 ILeath 115.0 Date Created.
19 19-JUN-2001 S.Robinson 115.1 Change to cursor c_state to ensure
20 assignments only selected once and
21 character validation is enforced.
22 06-AUG-2002 A.Mills 115.2 2473608. Added join to periods_of_service
23 from per_all_assignments_f.
24 07-AUG-2002 A.Mills 115.4 Enabled package for Aggregated PAYE.
25 02-DEC-2002 G.Butler 115.7 nocopy qualifier added to range_cursor
26 18-DEC-2003 asengar 115.8 BUG 3294480 Changed code for NI update
27 08-SEP-2004 K.Thampan 115.9 Revert the change for bug 2545016
28 13-JAN-2005 K.Thampan 115.10 Bug 4117609 - Amended the cursor so that
29 it will return employee regardless the
30 NI number.
31 25-MAY-2005 K.Thampan 115.11 Bug 4392220 - Amended the cursor c_state in
32 procedure wnu_cleanse_act_creation to return
33 assignments that does't have a record on the
34 per_assignment_extra_info table. This is
35 because these assignments might have been entered
36 using API, instead of front end (Form).
37 09-FEB-2006 K.Thampan 115.12 Fixed bug 4938724. Set g_stored_asg_id to null
38 16-JUN-2006 K.Thampan 115.13 Code change for EDI Rollback.
39 23-JUN-2006 K.Thampan 115.14 Update deinitilization procedure.
40 27-JUN-2006 K.Thampan 115.15 Added code to clear down data for aggregated
41 assignments
42 29-JUN-2006 K.Thampan 115.16 Fixed GSCC error
43 28-JUL-2006 tukumar 115.13 Enhancement 5398360 : wnu 3.0
44 01-SEP-2006 tukumar 115.14 Performance fix bug 5504855
45 13-MAR-2006 K.Thampan 115.19 Bug fix 5929268
46 ==============================================================================*/
47 --
48 --
49 TYPE act_info_rec IS RECORD
50 ( assignment_id number(20)
51 ,effective_date date
52 ,action_info_category varchar2(50)
53 ,act_info1 varchar2(300)
54 ,act_info2 varchar2(300)
55 ,act_info3 varchar2(300)
56 ,act_info4 varchar2(300)
57 ,act_info5 varchar2(300)
58 ,act_info6 varchar2(300)
59 ,act_info7 varchar2(300)
60 ,act_info8 varchar2(300)
61 ,act_info9 varchar2(300)
62 ,act_info10 varchar2(300)
63 ,act_info11 varchar2(300)
64 ,act_info12 varchar2(300)
65 ,act_info13 varchar2(300)
66 ,act_info14 varchar2(300)
67 ,act_info15 varchar2(300)
68 ,act_info16 varchar2(300)
69 ,act_info17 varchar2(300)
70 ,act_info18 varchar2(300)
71 ,act_info19 varchar2(300)
72 ,act_info20 varchar2(300)
73 ,act_info21 varchar2(300)
74 ,act_info22 varchar2(300)
75 ,act_info23 varchar2(300)
76 ,act_info24 varchar2(300)
77 ,act_info25 varchar2(300)
78 ,act_info26 varchar2(300)
79 ,act_info27 varchar2(300)
80 ,act_info28 varchar2(300)
81 ,act_info29 varchar2(300)
82 ,act_info30 varchar2(300)
83 );
84
85 TYPE action_info_table IS TABLE OF
86 act_info_rec INDEX BY BINARY_INTEGER;
87
88 g_package CONSTANT VARCHAR2(20):= 'PAY_GB_WNU_EDI.';
89 /****************** PRIVATE PROCEDURE ***********************/
90 --
91 PROCEDURE internal_act_creation(pactid in number,
92 stperson in number,
93 endperson in number,
94 chunk in number,
95 p_mode in varchar2) IS
96
97 l_proc CONSTANT VARCHAR2(50):= g_package||'internal_act_creation';
98 l_payroll_id number;
99 l_business_group_id number;
100 l_tax_ref varchar2(20);
101 l_effective_date date;
102 l_stored_asg_id number;
103 l_stored_per_id number;
104 l_locking_act_id number;
105
106 cursor csr_parameter_info is
107 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
108 'PAYROLL_ID')) payroll_id,
109 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
110 'TAX_REF'),1,20) tax_ref,
111 effective_date,
112 business_group_id
113 from pay_payroll_actions
114 where payroll_action_id = pactid;
115
116 cursor asg_act is
117 select /*+ ORDERED */
118 asg.assignment_id assignment_id,
119 decode(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
120 pap.person_id
121 from per_all_people_f pap,
122 per_assignments_f asg,
123 per_periods_of_service serv,
124 pay_all_payrolls_f pay,
125 per_assignment_extra_info aei,
126 hr_soft_coding_keyflex sck
127 where pap.person_id between stperson and endperson
128 and asg.business_group_id = l_business_group_id
129 and asg.person_id = pap.person_id
130 and asg.period_of_service_id = serv.period_of_service_id
131 and asg.payroll_id = pay.payroll_id
132 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
133 and upper(l_tax_ref) = upper(sck.segment1)
134 and (l_payroll_id IS NULL
135 or
136 l_payroll_id = pay.payroll_id)
137 and pap.current_employee_flag = 'Y'
138 and l_effective_date between asg.effective_start_date and asg.effective_end_date
139 and l_effective_date between pap.effective_start_date and pap.effective_end_date
140 and l_effective_date between pay.effective_start_date and pay.effective_end_date
141 and l_effective_date between serv.date_start and nvl(serv.actual_termination_date,hr_general.end_of_time)
142 and aei.assignment_id(+) = asg.assignment_id
143 and aei.information_type(+) = 'GB_WNU'
144 and nvl(aei.aei_information2,'N') <> 'Y'
145 and (p_mode = 'FULL'
146 or
147 ( p_mode = 'UPDATE'
148 and (aei.aei_information1 is not null or aei.aei_information3 = 'Y')))
149 order by pap.person_id;
150 BEGIN
151 hr_utility.set_location('Entering: '||l_proc,1);
152 open csr_parameter_info;
153 fetch csr_parameter_info into l_payroll_id,
154 l_tax_ref,
155 l_effective_date,
156 l_business_group_id;
157 close csr_parameter_info;
158
159 l_stored_asg_id := null;
160 hr_utility.set_location('Before ASG_ACT cursor',10);
161 for asg_rec in asg_act loop
162 hr_utility.set_location('Person ID/Assignment ID: '||
163 asg_rec.person_id || '/' || asg_rec.assignment_id,20);
164 -- First person in the loop, store the details and fetch next.
165 if l_stored_asg_id is null then
166 l_stored_asg_id := asg_rec.assignment_id;
167 l_stored_per_id := asg_rec.person_id;
168 else
169 -- If this is the same person and is aggregated,
170 -- save the assignment with the lowest ID
171 if (l_stored_per_id = asg_rec.person_id and
172 asg_rec.agg_paye_flag = 'Y') then
173 if asg_rec.assignment_id < nvl(l_stored_asg_id,999999999) THEN
174 l_stored_asg_id := asg_rec.assignment_id;
175 end if;
176 else
177 select pay_assignment_actions_s.nextval
178 into l_locking_act_id
179 from dual;
180
181 hr_nonrun_asact.insact(l_locking_act_id,
182 l_stored_asg_id,
183 pactid,
184 chunk,
185 null);
186 l_stored_asg_id := asg_rec.assignment_id;
187 l_stored_per_id := asg_rec.person_id;
188 end if;
189 end if;
190 end loop;
191
192 if l_stored_asg_id is not null then
193 hr_utility.set_location('Person ID/Assignment ID: '||
194 l_stored_per_id || '/' || l_stored_asg_id,20);
195 select pay_assignment_actions_s.nextval
196 into l_locking_act_id
197 from dual;
198
199 hr_nonrun_asact.insact(l_locking_act_id,
200 l_stored_asg_id,
201 pactid,
202 chunk,
203 null);
204 end if;
205 hr_utility.set_location('Leaving: '||l_proc,999);
206 END internal_act_creation;
207 --
208 --
209 FUNCTION validate_data(p_value in varchar2,
210 p_name in varchar2,
211 p_mode in varchar2) return boolean IS
212 l_proc CONSTANT VARCHAR2(50):= g_package||'validate_data';
213 BEGIN
214 hr_utility.set_location('Entering: '||l_proc,1);
215 if pay_gb_eoy_magtape.validate_input(UPPER(p_value),p_mode) > 0 then
216 hr_utility.set_location('Name/Value : ' || p_name || '/' || p_value ,10);
217 pay_core_utils.push_message(800, 'HR_78057_GB_MAGTAPE_VAILDATION', 'F');
218 pay_core_utils.push_token('INPUT_NAME', p_name);
219 pay_core_utils.push_token('INPUT_VALUE', p_value);
220 return false;
221 end if;
222 hr_utility.set_location('Leaving: '||l_proc,999);
223 return true;
224 END validate_data;
225 --
226 --
227 PROCEDURE update_aggregate_asg(p_assact_id in number)
228 IS
229 l_payroll_id number;
230 l_business_group_id number;
231 l_tax_ref varchar2(20);
232 l_effective_date date;
233 l_person_id number;
234 l_assignment_id number;
235 l_wnu_id number;
236 l_ovn number;
237
238 cursor csr_parameter_info is
239 select to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
240 'PAYROLL_ID')) payroll_id,
241 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
242 'TAX_REF'),1,20) tax_ref,
243 effective_date,
244 business_group_id
245 from pay_assignment_actions paa,
246 pay_payroll_actions pay
247 where paa.assignment_action_id = p_assact_id
248 and pay.payroll_action_id = paa.payroll_action_id;
249
250 cursor csr_person_id is
251 select person_id,
252 paa.assignment_id
253 from pay_assignment_actions paa,
254 per_all_assignments_f paf
255 where paa.assignment_action_id = p_assact_id
256 and paa.assignment_id = paf.assignment_id;
257
258 -- 5504855
259 cursor csr_wnu(p_asg_id number) is
260 select aei.assignment_extra_info_id
261 from per_assignment_extra_info aei
262 where aei.assignment_id = p_asg_id
263 and aei.information_type = 'GB_WNU';
264
265 cursor asg_act is
266 select /*+ ORDERED */
267 asg.assignment_id assignment_id
268 from per_all_people_f pap,
269 per_assignments_f asg,
270 per_periods_of_service serv,
271 pay_all_payrolls_f pay,
272 per_assignment_extra_info aei,
273 hr_soft_coding_keyflex sck
274 where pap.person_id = l_person_id
275 and asg.business_group_id = l_business_group_id
276 and asg.person_id = pap.person_id
277 and asg.period_of_service_id = serv.period_of_service_id
278 and asg.payroll_id = pay.payroll_id
279 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
280 and upper(l_tax_ref) = upper(sck.segment1)
281 and (l_payroll_id IS NULL
282 or
283 l_payroll_id = pay.payroll_id)
284 and pap.current_employee_flag = 'Y'
285 and pap.per_information10 = 'Y'
286 and l_effective_date between asg.effective_start_date and asg.effective_end_date
287 and l_effective_date between pap.effective_start_date and pap.effective_end_date
288 and l_effective_date between pay.effective_start_date and pay.effective_end_date
289 and l_effective_date between serv.date_start and nvl(serv.actual_termination_date,hr_general.end_of_time)
290 and aei.assignment_id = asg.assignment_id
291 and aei.information_type = 'GB_WNU'
292 and nvl(aei.aei_information2,'N') <> 'Y'
293 and (aei.aei_information1 is not null or aei.aei_information3 = 'Y')
294 order by pap.person_id;
295 BEGIN
296 open csr_parameter_info;
297 fetch csr_parameter_info into l_payroll_id,
298 l_tax_ref,
299 l_effective_date,
300 l_business_group_id;
301 close csr_parameter_info;
302
303 open csr_person_id;
304 fetch csr_person_id into l_person_id, l_assignment_id;
305 close csr_person_id;
306
307 for all_aggregated in asg_act loop
308 if all_aggregated.assignment_id <> l_assignment_id then
309 l_wnu_id := null;
310 open csr_wnu(all_aggregated.assignment_id);
311 fetch csr_wnu into l_wnu_id;
312 close csr_wnu;
313
314 if l_wnu_id is not null then
315 hr_assignment_extra_info_api.update_assignment_extra_info
316 (p_validate => false,
317 p_object_version_number => l_ovn,
318 p_assignment_extra_info_id => l_wnu_id,
319 p_aei_information_category => 'GB_WNU',
320 p_aei_information1 => null,
321 p_aei_information2 => 'N',
322 p_aei_information3 => 'N');
323 end if;
324 end if;
325 end loop;
326 END update_aggregate_asg;
327 --
328 --
329 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
330 p_effective_date IN DATE,
331 p_person_rec OUT nocopy act_info_rec) return boolean IS
332
333 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
334 l_person_id number;
335 l_ret boolean;
336
337 cursor csr_person_details is
338 select /*+ ORDERED */
339 pap.title,
340 pap.first_name,
341 pap.middle_names,
342 pap.last_name,
343 paa.ASSIGNMENT_NUMBER,
344 pap.national_identifier,
345 paa.assignment_id
346 from pay_assignment_actions pact,
347 per_assignments_f paa,
348 per_people_f pap
349 where pact.assignment_action_id = p_assactid
350 and pact.assignment_id = paa.assignment_id
351 and paa.person_id = pap.person_id
352 and p_effective_date between paa.effective_start_date and paa.effective_end_date
353 and p_effective_date between pap.effective_start_date and pap.effective_end_date;
354
355 l_person_rec csr_person_details%rowtype;
356 BEGIN
357 hr_utility.set_location('Entering: '||l_proc,1);
358 l_ret := false;
359
360 open csr_person_details;
361 fetch csr_person_details into l_person_rec;
362 close csr_person_details;
363
364 if validate_data(l_person_rec.first_name,'Full Name','EDI_SURNAME') and
365 validate_data(l_person_rec.last_name,'Last Name','EDI_SURNAME') and
366 validate_data(l_person_rec.middle_names,'Middle Name','EDI_SURNAME') and
367 validate_data(l_person_rec.national_identifier,'NI Number','FULL_EDI') and
368 validate_data(l_person_rec.assignment_number,'Assignment Number','FULL_EDI') then
369 l_ret := true;
370 end if;
371 p_person_rec.assignment_id := l_person_rec.assignment_id;
372 p_person_rec.effective_date := p_effective_date;
373 p_person_rec.action_info_category := 'GB EMPLOYEE DETAILS';
374 p_person_rec.act_info6 := l_person_rec.first_name;
375 p_person_rec.act_info7 := l_person_rec.middle_names;
376 p_person_rec.act_info8 := l_person_rec.last_name;
377 p_person_rec.act_info11 := l_person_rec.assignment_number;
378 p_person_rec.act_info12 := l_person_rec.national_identifier;
379 p_person_rec.act_info14 := l_person_rec.title;
380
381 hr_utility.set_location('Leaving: '||l_proc,999);
382 return l_ret;
383 END fetch_person_rec;
384 --
385 --
386 FUNCTION fetch_wnu_rec(p_assactid IN NUMBER,
387 p_effective_date IN DATE,
388 p_wnu_rec OUT nocopy act_info_rec) return boolean IS
389
390 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
391 l_assignment_id number;
392 l_ovn number;
393 l_ret boolean;
394
395 cursor csr_wnu_details is
396 select aei.assignment_extra_info_id,
397 aei.aei_information1 old_asg_number,
398 aei.aei_information2 not_flag,
399 aei.aei_information3 ni_update,
400 aei.object_version_number,
401 paa.assignment_id
402 from pay_assignment_actions paa,
403 per_assignment_extra_info aei
404 where paa.assignment_action_id = p_assactid
405 and paa.assignment_id = aei.assignment_id
406 and aei.information_type = 'GB_WNU';
407
408 l_wnu_rec csr_wnu_details%rowtype;
409 BEGIN
410 hr_utility.set_location('Entering: '||l_proc,1);
411 l_ret := true;
412
413 open csr_wnu_details;
414 fetch csr_wnu_details into l_wnu_rec;
415
416 if csr_wnu_details%FOUND then
417 hr_utility.set_location('Data found',20);
418 l_ret := validate_data(l_wnu_rec.old_asg_number,'Old Assignment Number','FULL_EDI');
419
420 l_ovn := l_wnu_rec.object_version_number;
421
422 hr_utility.set_location('Clear Flag',30);
423
424 if l_ret then
425 hr_assignment_extra_info_api.update_assignment_extra_info
426 (p_validate => false,
427 p_object_version_number => l_ovn,
428 p_assignment_extra_info_id => l_wnu_rec.assignment_extra_info_id,
429 p_aei_information_category => 'GB_WNU',
430 p_aei_information1 => null,
431 p_aei_information2 => 'N',
432 p_aei_information3 => 'N');
433
434 update_aggregate_asg(p_assactid);
435 end if;
436
437 p_wnu_rec.assignment_id := l_wnu_rec.assignment_id;
438 p_wnu_rec.effective_date := p_effective_date;
439 p_wnu_rec.action_info_category := 'GB WNU EDI';
440 p_wnu_rec.act_info1 := l_ovn;
441 p_wnu_rec.act_info2 := l_wnu_rec.old_asg_number;
442 p_wnu_rec.act_info3 := l_wnu_rec.not_flag;
443 p_wnu_rec.act_info4 := l_wnu_rec.ni_update;
444 end if;
445
446 close csr_wnu_details;
447 hr_utility.set_location('Leaving: '||l_proc,999);
448
449 return l_ret;
450 END fetch_wnu_rec;
451 --
452 --
453 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
454 p_effective_date IN DATE,
455 p_tab_rec_data IN action_info_table) IS
456 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
457 l_ovn number;
458 l_action_id number;
459 BEGIN
460 hr_utility.set_location('Entering: '||l_proc,1);
461 if p_tab_rec_data.count > 0 then
462 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
463 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
464 hr_utility.trace('action_context_id = '|| p_assactid);
465 if p_tab_rec_data(i).action_info_category is not null then
466 pay_action_information_api.create_action_information(
467 p_action_information_id => l_action_id,
468 p_object_version_number => l_ovn,
469 p_action_information_category => p_tab_rec_data(i).action_info_category,
470 p_action_context_id => p_assactid,
471 p_action_context_type => 'AAP',
472 p_assignment_id => p_tab_rec_data(i).assignment_id,
473 p_effective_date => p_effective_date,
474 p_action_information1 => p_tab_rec_data(i).act_info1,
475 p_action_information2 => p_tab_rec_data(i).act_info2,
476 p_action_information3 => p_tab_rec_data(i).act_info3,
477 p_action_information4 => p_tab_rec_data(i).act_info4,
478 p_action_information5 => p_tab_rec_data(i).act_info5,
479 p_action_information6 => p_tab_rec_data(i).act_info6,
480 p_action_information7 => p_tab_rec_data(i).act_info7,
481 p_action_information8 => p_tab_rec_data(i).act_info8,
482 p_action_information9 => p_tab_rec_data(i).act_info9,
483 p_action_information10 => p_tab_rec_data(i).act_info10,
484 p_action_information11 => p_tab_rec_data(i).act_info11,
485 p_action_information12 => p_tab_rec_data(i).act_info12,
486 p_action_information13 => p_tab_rec_data(i).act_info13,
487 p_action_information14 => p_tab_rec_data(i).act_info14,
488 p_action_information15 => p_tab_rec_data(i).act_info15,
489 p_action_information16 => p_tab_rec_data(i).act_info16,
490 p_action_information17 => p_tab_rec_data(i).act_info17,
491 p_action_information18 => p_tab_rec_data(i).act_info18,
492 p_action_information19 => p_tab_rec_data(i).act_info19,
493 p_action_information20 => p_tab_rec_data(i).act_info20,
494 p_action_information21 => p_tab_rec_data(i).act_info21,
495 p_action_information22 => p_tab_rec_data(i).act_info22,
496 p_action_information23 => p_tab_rec_data(i).act_info23,
497 p_action_information24 => p_tab_rec_data(i).act_info24,
498 p_action_information25 => p_tab_rec_data(i).act_info25,
499 p_action_information26 => p_tab_rec_data(i).act_info26,
500 p_action_information27 => p_tab_rec_data(i).act_info27,
501 p_action_information28 => p_tab_rec_data(i).act_info28,
502 p_action_information29 => p_tab_rec_data(i).act_info29,
503 p_action_information30 => p_tab_rec_data(i).act_info30
504 );
505 end if;
506 end loop;
507 end if;
508 hr_utility.set_location('Leaving: '||l_proc,999);
509 END insert_archive_row;
510 --
511 --
512 /****************** PUBLIC PROCEDURE ***********************/
513 --
514 --
515 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
516 IS
517 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
518 l_sender_id VARCHAR2(30);
519 l_tax_ref VARCHAR2(30);
520 l_err EXCEPTION;
521
522 cursor csr_sender_id is
523 select hoi.org_information11,
524 hoi.org_information1
525 from pay_payroll_actions pact,
526 hr_organization_information hoi
527 where pact.payroll_action_id = p_payroll_action_id
528 and pact.business_group_id = hoi.organization_id
529 and hoi.org_information_context = 'Tax Details References'
530 and (hoi.org_information10 is null
531 OR
532 hoi.org_information10 = 'UK')
533 and hoi.org_information1 =
534 substr(pact.legislative_parameters,
535 instr(pact.legislative_parameters,'TAX_REF=') + 8,
536 instr(pact.legislative_parameters||' ',' ',
537 instr(pact.legislative_parameters,'TAX_REF=')+8)
538 - instr(pact.legislative_parameters,'TAX_REF=') - 8);
539 BEGIN
540 hr_utility.set_location('Entering '|| l_proc, 10);
541 open csr_sender_id;
542 fetch csr_sender_id into l_sender_id, l_tax_ref;
543 close csr_sender_id;
544
545 if l_sender_id is null then
546 pay_core_utils.push_message(800, 'HR_78087_EDI_SENDER_ID_MISSING', 'F');
547 pay_core_utils.push_token('TAX_REF', l_tax_ref);
548 raise l_err;
549 else
550 if (not validate_data(l_sender_id,'Sender ID','FULL_EDI')) then
551 raise l_err;
552 end if;
553 end if;
554
555 hr_utility.set_location('Leaving '|| l_proc, 10);
556 EXCEPTION
557 when others then
558 hr_utility.raise_error;
559 END archinit;
560 --
561 --
562 PROCEDURE range_cursor (pactid IN NUMBER,
563 sqlstr OUT nocopy VARCHAR2) IS
564 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
565 BEGIN
566 hr_utility.set_location('Entering: '||l_proc,1);
567 sqlstr := 'select distinct person_id '||
568 'from per_people_f ppf, '||
569 'pay_payroll_actions ppa '||
570 'where ppa.payroll_action_id = :payroll_action_id '||
571 'and ppa.business_group_id = ppf.business_group_id '||
572 'order by ppf.person_id';
573 hr_utility.set_location('Leaving: '||l_proc,999);
574 END range_cursor;
575 --
576 --
577 PROCEDURE wnu_cleanse_act_creation(pactid in number,
578 stperson in number,
579 endperson in number,
580 chunk in number) IS
581 BEGIN
582 internal_act_creation(pactid, stperson, endperson, chunk, 'FULL');
583 END wnu_cleanse_act_creation;
584 --
585 --
586 PROCEDURE wnu_update_action_creation(pactid in number,
587 stperson in number,
588 endperson in number,
589 chunk in number) IS
590 BEGIN
591 internal_act_creation(pactid, stperson, endperson, chunk, 'UPDATE');
592 END wnu_update_action_creation;
593 --
594 --
595 PROCEDURE archive_code(p_assactid IN NUMBER,
596 p_effective_date IN DATE) IS
597 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
598 error_found EXCEPTION;
599 l_archive_tab action_info_table;
600 l_archive_person boolean;
601 l_archive_wnu boolean;
602
603 BEGIN
604 hr_utility.set_location('Entering: '||l_proc,1);
605
606 hr_utility.set_location('Fetching person details ',10);
607 l_archive_person := fetch_person_rec(p_assactid, p_effective_date, l_archive_tab(0));
608
609 hr_utility.set_location('Fetching wnu details ',20);
610 l_archive_wnu := fetch_wnu_rec(p_assactid, p_effective_date, l_archive_tab(1));
611
612 if l_archive_person and l_archive_wnu then
613 insert_archive_row(p_assactid, p_effective_date, l_archive_tab);
614 else
615 raise error_found;
616 end if;
617 hr_utility.set_location('Leaving: '||l_proc,999);
618 EXCEPTION
619 when error_found then
620 hr_utility.raise_error;
621 END archive_code;
622 --
623 --
624 PROCEDURE deinitialization_code(pactid IN NUMBER)
625 IS
626 cursor csr_get_wnu_version is
627 select substr(pact.legislative_parameters,
628 instr(pact.legislative_parameters,'VERSION=') + 8,
629 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters,'VERSION=')+8)
630 - instr(pact.legislative_parameters,'VERSION=') - 8) version
631 from pay_payroll_actions pact where pact.payroll_action_id = pactid;
632
633 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
634 l_counter number;
635 l_wnu_version VARCHAR2(4);
636
637
638 procedure write_header( p_wnu_version in VARCHAR2) is
639 l_token varchar2(255);
640 l_addr1 varchar2(255);
641 l_addr2 varchar2(255);
642 l_addr3 varchar2(255);
643 l_addr4 varchar2(255);
644 l_form varchar2(40);
645 l_tax_ref varchar2(20);
646 l_urgent varchar2(2);
647 l_test varchar2(2);
648 l_temp number;
649
650 cursor csr_leg_param is
651 select legislative_parameters para,
652 fnd_number.number_to_canonical(request_id) control_id,
653 report_type,
654 business_group_id
655 from pay_payroll_actions
656 where payroll_action_id = pactid;
657
658 cursor csr_header_det(p_bus_id number,
659 p_tax_ref varchar2) is
660 select nvl(hoi.org_information11,' ') sender_id,
661 nvl(upper(hoi.org_information2),' ') hrmc_office,
662 nvl(upper(hoi.org_information4),' ') er_addr,
663 nvl(upper(hoi.org_information3),' ') er_name
664 from hr_organization_information hoi
665 where hoi.organization_id = p_bus_id
666 and hoi.org_information_context = 'Tax Details References'
667 and nvl(hoi.org_information10,'UK') = 'UK'
668 and upper(hoi.org_information1) = upper(p_tax_ref);
669
670 l_param csr_leg_param%rowtype;
671 l_det csr_header_det%rowtype;
672
673 begin
674 open csr_leg_param;
675 fetch csr_leg_param into l_param;
676 close csr_leg_param;
677
678 l_token := 'TAX_REF';
679 l_temp := instr(l_param.para,l_token);
680 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
681 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
682 l_token := 'URGENT';
683 l_temp := instr(l_param.para,l_token);
684 l_urgent := substr(l_param.para, l_temp + length(l_token) + 1,
685 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
686 l_token := 'TEST';
687 l_temp := instr(l_param.para,l_token);
688 l_test := substr(l_param.para, l_temp + length(l_token) + 1,
689 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
690
691 open csr_header_det(l_param.business_group_id, l_tax_ref);
692 fetch csr_header_det into l_det;
693 close csr_header_det;
694
695 l_addr1 := l_det.er_addr;
696 if length(l_addr1) > 35 then
697 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
698 if l_temp = 0 then
699 l_temp := 35;
700 end if;
701 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
702 l_addr1 := substr(l_addr1,1,l_temp);
703 end if;
704 if length(l_addr2) > 35 then
705 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
706 if l_temp = 0 then
707 l_temp := 35;
708 end if;
709 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
710 l_addr2 := substr(l_addr2,1,l_temp);
711 end if;
712 if length(l_addr3) > 35 then
713 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
714 if l_temp = 0 then
715 l_temp := 35;
716 end if;
717 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
718 l_addr4 := substr(l_addr3,1,l_temp);
719 end if;
720
721 l_form := 'WNU ( WNU '||p_wnu_version||' )';
722
723 fnd_file.put_line(fnd_file.output,'EDI Transmission Report:');
724 fnd_file.put_line(fnd_file.output,' ');
725 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form );
726 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
727 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
728 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
729 fnd_file.put_line(fnd_file.output,rpad('Test Transmission : ',32) || l_test);
730 fnd_file.put_line(fnd_file.output,rpad('Urgent : ',32) || l_urgent);
731 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
732 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
733 fnd_file.put_line(fnd_file.output,rpad('HRMC Office : ',32) || l_det.hrmc_office);
734 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
735 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
736 if length(l_addr2) > 0 then
737 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
738 end if;
739 if length(l_addr3) > 0 then
740 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
741 end if;
742 if length(l_addr4) > 0 then
743 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
744 end if;
745 end write_header;
746
747 procedure write_sub_header(p_type varchar2 , p_wnu_version VARCHAR2) is
748
749 begin
750 fnd_file.put_line(fnd_file.output,null);
751 if p_type = 'E' then
752 fnd_file.put_line(fnd_file.output,'The following assignments have completed with error');
753 else
754 fnd_file.put_line(fnd_file.output,'The following assignments have completed successfully');
755 end if;
756 -- Bug 5398360
757
758 IF p_wnu_version = '1.0' THEN
759 fnd_file.put_line(fnd_file.output,rpad('Assignment Number',19) ||
760 rpad('NI Number',11) ||
761 rpad('Employee Name', 50));
762 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
763 rpad('-',10,'-') || ' ' ||
764 rpad('-',50,'-'));
765 ELSE
766 fnd_file.put_line(fnd_file.output,rpad('Works Number',19) ||
767 rpad('NI Number',11) ||
768 rpad('Employee Name', 51)||
769 rpad('Old Works Number',18) ) ;
770 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
771 rpad('-',10,'-') || ' ' ||
772 rpad('-',50,'-')|| ' ' ||
773 rpad('-',18,'-') );
774
775 END IF;
776
777 end write_sub_header;
778
779 procedure write_body(p_type varchar2 , p_wnu_version varchar2) is
780 l_count number;
781 l_temp varchar2(255);
782 l_ni varchar2(20);
783 cursor csr_asg is
784 select /*+ ORDERED */
785 peo.first_name f_name ,
786 peo.middle_names m_name,
787 peo.last_name l_name,
788 peo.title title,
789 paf.assignment_number emp_no,
790 peo.national_identifier ni_no ,
791 pai.action_information2 old_works_number
792 from pay_payroll_actions pay,
793 pay_assignment_actions paa,
794 per_all_assignments_f paf,
795 per_all_people_f peo,
796 pay_action_information pai
797 where pay.payroll_action_id = pactid
798 and paa.payroll_action_id = pay.payroll_action_id
799 and paa.action_status = p_type
800 and pai.action_context_id(+) = paa.assignment_action_id
801 and pai.action_context_type(+) = 'AAP'
802 and pai.action_information_category(+) = 'GB WNU EDI'
803 and paf.assignment_id = paa.assignment_id
804 and peo.person_id = paf.person_id
805 and pay.effective_date between paf.effective_start_date and paf.effective_end_date
806 and pay.effective_date between peo.effective_start_date and peo.effective_end_date;
807 begin
808 l_count := 0;
809 if p_wnu_version = '1.0' then
810 for asg_rec in csr_asg loop
811 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
812 asg_rec.f_name || ' ' || asg_rec.m_name;
813 l_ni := asg_rec.ni_no;
814 if l_ni is null then
815 l_ni := '-MISSING-';
816 end if;
817 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
818 rpad(l_ni ,10) || ' ' ||
819 rpad(l_temp,50));
820 l_count := l_count + 1;
821 end loop;
822 ELSE
823 for asg_rec in csr_asg loop
824 l_temp := asg_rec.l_name || ', '|| asg_rec.title || ' ' ||
825 asg_rec.f_name || ' ' || asg_rec.m_name;
826 l_ni := asg_rec.ni_no;
827 if l_ni is null then
828 l_ni := '-MISSING-';
829 end if;
830 fnd_file.put_line(fnd_file.output,rpad(asg_rec.emp_no, 18) || ' ' ||
831 rpad(l_ni ,10) || ' ' ||
832 rpad(l_temp,50) || ' ' ||
833 rpad(asg_rec.old_works_number,17)); -- 5398360
834 l_count := l_count + 1;
835 end loop;
836 END IF;
837
838
839
840 fnd_file.put_line(fnd_file.output,null);
841 if p_type = 'E' then
842 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed with error : ' || l_count);
843 else
844 fnd_file.put_line(fnd_file.output,'Total Number of assignments completed successfully :' || l_count);
845 end if;
846 l_counter := l_counter + l_count;
847 end write_body;
848
849 procedure write_footer is
850 begin
851 fnd_file.put_line(fnd_file.output,null);
852 fnd_file.put_line(fnd_file.output,'Total Number Of Records : ' || l_counter);
853 end write_footer;
854 BEGIN
855 hr_utility.set_location('Entering: '||l_proc,1);
856 l_counter := 0;
857 OPEN csr_get_wnu_version ;
858 FETCH csr_get_wnu_version INTO l_wnu_version;
859 CLOSE csr_get_wnu_version ;
860
861 write_header(l_wnu_version);
862 write_sub_header('C',l_wnu_version);
863 write_body('C',l_wnu_version);
864 write_sub_header('E',l_wnu_version);
865 write_body('E',l_wnu_version);
866 write_footer;
867 hr_utility.set_location('Leaving: '||l_proc,999);
868 END deinitialization_code;
869 --
870 --
871 END PAY_GB_WNU_EDI;