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