[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_DEO
Source
1 PACKAGE BODY PAY_GB_DEO as
2 /* $Header: pygbdeo.pkb 120.1.12020000.1 2013/02/11 10:57:39 sampmand noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_DEO
10 Purpose:
11 It fetches the live data and archives it for generating DEO payment reports.
12 This is a UK Specific payroll package.
13
14 History:
15 24-Jan-2013 sampmand 115.0 Created for DEO payment report generation.
16 25-Jan-2013 sampmand 115.1 Added layout for CSV file.
17 30-Jan-2013 sampmand 115.3 Added logic to imatch null employee ref values.
18 31-Jan-2013 sampmand 115.4 Added logic to set context to 'Unknown' for null employee ref values.
19 ==========================================================================*/
20
21 g_package CONSTANT VARCHAR2(20):= 'PAY_GB_DEO.';
22 g_paye_details_id number;
23 g_validation_check VARCHAR2(1) := 'Y';
24 g_co_id number := 0;
25 g_co_arrears_id number := 0;
26 g_total_paid number := 0;
27
28 -- Change the code numbers deo
29 /*
30 DEO Data Archive Structure
31 --------------------------------------
32 Action Context Type : PA
33 Action_Info_Category: GB DEO EMPLOYER DETAILS
34
35 Action_Information3 : Employer Name
36 Action_Information4 : Employer Paye Reference
37 Action_Information5 : Employer Reference
38 Action_Information6 : Payroll Name
39 Action_Information7 : Payment from date
40 Action_Information8 : Payment to date
41 Action_Information9 : Total Amount Paid
42 Action_Information10: Request ID
43
44
45
46 Action Context Type : AAP
47 Action_Info_Category: GB DEO EMPLOYEE DETAILS
48
49 Action_Information3 : First Name
50 Action_Information4 : Last Name
51 Action_Information5 : NI Number
52 Action_Information6 : Employee Number
53 Action_Information7 : Person_id
54
55
56 Action Context Type : AAP
57 Action_Info_Category: GB DEO ASG DETAILS
58
59
60 Action_Information3 : Employee Reference Number
61 Action_Information4 : Amount Due
62 Action_Information5 : Actual Amount
63 Action_Information6 : Reason
64 Action_Information7 : Person_id
65
66
67 */
68 -- This table will be used as a transit variable to pass information column values to
69 -- archive api
70 TYPE act_info_rec IS RECORD
71 ( assignment_id number(20)
72 ,person_id number(20)
73 ,effective_date date
74 ,action_info_category varchar2(50)
75 ,act_info1 varchar2(300)
76 ,act_info2 varchar2(300)
77 ,act_info3 varchar2(300)
78 ,act_info4 varchar2(300)
79 ,act_info5 varchar2(300)
80 ,act_info6 varchar2(300)
81 ,act_info7 varchar2(300)
82 ,act_info8 varchar2(300)
83 ,act_info9 varchar2(300)
84 ,act_info10 varchar2(300)
85 ,act_info11 varchar2(300)
86 ,act_info12 varchar2(300)
87 ,act_info13 varchar2(300)
88 ,act_info14 varchar2(300)
89 ,act_info15 varchar2(300)
90 ,act_info16 varchar2(300)
91 ,act_info17 varchar2(300)
92 ,act_info18 varchar2(300)
93 ,act_info19 varchar2(300)
94 ,act_info20 varchar2(300)
95 ,act_info21 varchar2(300)
96 ,act_info22 varchar2(300)
97 ,act_info23 varchar2(300)
98 ,act_info24 varchar2(300)
99 ,act_info25 varchar2(300)
100 ,act_info26 varchar2(300)
101 ,act_info27 varchar2(300)
102 ,act_info28 varchar2(300)
103 ,act_info29 varchar2(300)
104 ,act_info30 varchar2(300)
105 );
106
107 TYPE action_info_table IS TABLE OF act_info_rec INDEX BY BINARY_INTEGER;
108 ---
109 --
110 --
111 -- Function to fetch person details (CONTEXT - GB DEO EMPLOYEE DETAILS)
112 FUNCTION fetch_person_rec(p_assactid IN NUMBER,
113 p_effective_date IN DATE,
114 p_person_rec OUT nocopy act_info_rec) return boolean IS
115
116 l_proc CONSTANT VARCHAR2(50):= g_package||'fetch_person_rec';
117 l_arch boolean;
118
119 cursor csr_person_details IS
120 SELECT
121 distinct pap.person_id p_person_id,
122 paa.assignment_id,
123 pap.employee_number emp_no,
124 substr(trim(pap.last_name), 1,35) last_name,
125 substr(trim(pap.first_name), 1,least(decode(instr(trim(pap.first_name),' '),0,35,instr(trim(pap.first_name),' ')),35)) first_name,
126 substr(pap.national_identifier,1,9) national_identifier,
127 pap.effective_start_date effective_start_date
128 FROM pay_assignment_actions act,
129 per_all_assignments_f paa,
130 per_all_people_f pap
131 where act.assignment_action_id = p_assactid
132 and act.assignment_id = paa.assignment_id
133 and paa.person_id = pap.person_id
134 and pap.effective_start_date <= p_effective_date
135 and paa.effective_start_date <= p_effective_date
136 order by pap.effective_start_date desc;
137
138 l_person_rec csr_person_details%rowtype;
139 BEGIN
140
141 hr_utility.set_location('Entering: '||l_proc,1);
142 l_arch := true;
143
144 open csr_person_details;
145 fetch csr_person_details into l_person_rec;
146
147 if csr_person_details%notfound then
148 l_arch := false;
149 end if;
150 close csr_person_details;
151
152 hr_utility.trace('Start date ' ||l_person_rec.effective_start_date);
153
154
155 p_person_rec.person_id := l_person_rec.p_person_id;
156 p_person_rec.assignment_id := l_person_rec.assignment_id;
157 p_person_rec.action_info_category := 'GB DEO EMPLOYEE DETAILS';
158 p_person_rec.act_info3 := l_person_rec.first_name;
159 p_person_rec.act_info4 := l_person_rec.last_name;
160 p_person_rec.act_info5 := l_person_rec.national_identifier;
161 p_person_rec.act_info6 := l_person_rec.emp_no;
162 p_person_rec.act_info7 := l_person_rec.p_person_id;
163
164 hr_utility.set_location('Leaving: '||l_proc,999);
165 return l_arch;
166 END fetch_person_rec;
167 --
168 --
169 -- Procedure to insert data to the archive table pay_action_information
170 PROCEDURE insert_archive_row(p_assactid IN NUMBER,
171 p_effective_date IN DATE,
172 p_tab_rec_data IN action_info_table) IS
173 l_proc CONSTANT VARCHAR2(50):= g_package||'insert_archive_row';
174 l_ovn number;
175 l_action_id number;
176 BEGIN
177 hr_utility.set_location('Entering: '||l_proc,1);
178 if p_tab_rec_data.count > 0 then
179 for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
180 hr_utility.trace('Defining category '|| p_tab_rec_data(i).action_info_category);
181 hr_utility.trace('action_context_id = '|| p_assactid);
182 if p_tab_rec_data(i).action_info_category is not null then
183 pay_action_information_api.create_action_information(
184 p_action_information_id => l_action_id,
185 p_object_version_number => l_ovn,
186 p_action_information_category => p_tab_rec_data(i).action_info_category,
187 p_action_context_id => p_assactid,
188 p_action_context_type => 'AAP',
189 p_assignment_id => p_tab_rec_data(i).assignment_id,
190 p_effective_date => p_effective_date,
191 p_action_information1 => p_tab_rec_data(i).act_info1,
192 p_action_information2 => p_tab_rec_data(i).act_info2,
193 p_action_information3 => p_tab_rec_data(i).act_info3,
194 p_action_information4 => p_tab_rec_data(i).act_info4,
195 p_action_information5 => p_tab_rec_data(i).act_info5,
196 p_action_information6 => p_tab_rec_data(i).act_info6,
197 p_action_information7 => p_tab_rec_data(i).act_info7,
198 p_action_information8 => p_tab_rec_data(i).act_info8,
199 p_action_information9 => p_tab_rec_data(i).act_info9,
200 p_action_information10 => p_tab_rec_data(i).act_info10,
201 p_action_information11 => p_tab_rec_data(i).act_info11,
202 p_action_information12 => p_tab_rec_data(i).act_info12,
203 p_action_information13 => p_tab_rec_data(i).act_info13,
204 p_action_information14 => p_tab_rec_data(i).act_info14,
205 p_action_information15 => p_tab_rec_data(i).act_info15,
206 p_action_information16 => p_tab_rec_data(i).act_info16,
207 p_action_information17 => p_tab_rec_data(i).act_info17,
208 p_action_information18 => p_tab_rec_data(i).act_info18,
209 p_action_information19 => p_tab_rec_data(i).act_info19,
210 p_action_information20 => p_tab_rec_data(i).act_info20,
211 p_action_information21 => p_tab_rec_data(i).act_info21,
212 p_action_information22 => p_tab_rec_data(i).act_info22,
213 p_action_information23 => p_tab_rec_data(i).act_info23,
214 p_action_information24 => p_tab_rec_data(i).act_info24,
215 p_action_information25 => p_tab_rec_data(i).act_info25,
216 p_action_information26 => p_tab_rec_data(i).act_info26,
217 p_action_information27 => p_tab_rec_data(i).act_info27,
218 p_action_information28 => p_tab_rec_data(i).act_info28,
219 p_action_information29 => p_tab_rec_data(i).act_info29,
220 p_action_information30 => p_tab_rec_data(i).act_info30
221 );
222 end if;
223 end loop;
224 end if;
225 hr_utility.set_location('Leaving: '||l_proc,999);
226 END insert_archive_row;
227 --
228 --
229
230 /*------------ PUBLIC PROCEDURES --------------*/
231
232 /*--------------------------------------------------------------------------
233 Name : range_cursor
234 Purpose : This returns the select statement that is used to create the
235 range rows.
236 Arguments :
237 Notes : The range cursor determines which people should be processed.
238 The normal practice is to include everyone, and then limit
239 the list during the assignment action creation.
240 --------------------------------------------------------------------------*/
241 PROCEDURE range_cursor (pactid IN NUMBER,
242 sqlstr OUT NOCOPY VARCHAR2)
243 IS
244
245 cursor csr_parameter_info IS
246 SELECT report_type
247 FROM pay_payroll_actions
248 WHERE payroll_action_id = pactid;
249
250 l_report_type varchar2(15);
251 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
252 BEGIN
253
254
255 hr_utility.set_location('Entering: '||l_proc,1);
256 fnd_file.put_line(FND_FILE.log, 'Enering Range: pactid: '||pactid);
257 OPEN csr_parameter_info;
258 fetch csr_parameter_info into l_report_type;
259 CLOSE csr_parameter_info ;
260 fnd_file.put_line(FND_FILE.log, 'Rep_type:'||l_report_type);
261 IF l_report_type = 'DEO_PAY_REP' THEN
262
263 sqlstr := 'select distinct person_id '||
264 'from per_all_people_f ppf, '||
265 'pay_payroll_actions ppa '||
266 'where ppa.payroll_action_id = :payroll_action_id '||
267 'and ppa.business_group_id = ppf.business_group_id '||
268 'order by ppf.person_id';
269 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
270
271 END IF;
272 hr_utility.set_location('Leaving '|| l_proc, 10);
273 EXCEPTION
274 when others then
275 hr_utility.set_location(' Leaving: '||l_proc,50);
276 fnd_file.put_line(FND_FILE.log, 'Exception in range: '||sqlcode || '-' || sqlerrm);
277 hr_utility.raise_error;
278 -- raise_application_error(-20001,'Errors found in range cursor.');
279 END range_cursor;
280 --
281 --
282 /*--------------------------------------------------------------------------
283 Name : action_creation
284 Purpose : This creates the assignment actions for a specific chunk.
285 Arguments :
286 Notes :
287 --------------------------------------------------------------------------*/
288
289 PROCEDURE action_creation (pactid in number,
290 stperson in number,
291 endperson in number,
292 chunk in number)
293 IS
294
295 l_proc CONSTANT VARCHAR2(90):= g_package||'action_creation';
296 l_payroll_id number;
297 l_payroll_name varchar2(80);
298 l_tax_ref varchar2(20);
299 l_business_group_id number;
300 l_payment_to_date date;
301 l_payment_from_date date;
302 lockingactid number;
303
304 cursor csr_parameter_info is
305 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
306 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
307 -- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
308 -- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
309 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
310 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
311 business_group_id
312 from pay_payroll_actions pact
313 where payroll_action_id = pactid;
314
315
316 -- Cursor to fetch DEO screen entry values for Reference and Reason for Assignments
317 cursor csr_get_deo_asg(p_asg_tax_ref IN VARCHAR2,p_payroll_id IN number,p_payment_from_date IN DATE,p_payment_to_date IN DATE,p_business_group_id IN number)
318 is
319 select distinct paaf.person_id,max(peef.assignment_id) assignment_id
320 from pay_element_types_f pet,
321 pay_input_values_f piv,
322 pay_element_entry_values_f peev,
323 pay_element_entries_f peef,
324 per_all_assignments_f paaf,
325 hr_soft_coding_keyflex sc,
326 pay_all_payrolls_f ppf,
327 pay_assignment_actions paa,
328 pay_payroll_actions ppa
329 where pet.element_name in ('Court Order','Court Order NTPP')
330 AND pet.business_group_id IS NULL
331 AND pet.legislation_code = 'GB'
332 AND pet.element_type_id = piv.element_type_id
333 AND piv.business_group_id IS NULL
334 AND piv.legislation_code = 'GB'
335 AND piv.name = 'Type'
336 AND piv.input_value_id = peev.input_value_id
337 AND peev.screen_entry_value in ('DEO','DEO_PERCENT','CMS_DEO')
338 AND peev.element_entry_id = peef.element_entry_id
339 AND paaf.person_id between stperson and endperson
340 AND peef.assignment_id = paaf.assignment_id
341 AND ppf.soft_coding_keyflex_id = sc.soft_coding_keyflex_id
342 AND sc.segment1 = p_asg_tax_ref
343 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
344 AND paaf.payroll_id = ppf.payroll_id
345 AND ppa.payroll_id = ppf.payroll_id
346 AND paaf.assignment_id = paa.assignment_id
347 AND paa.assignment_id = peef.assignment_id
348 AND paa.payroll_action_id = ppa.payroll_action_id
349 AND ppa.business_group_id = p_business_group_id
350 AND paaf.business_group_id = ppa.business_group_id
351 AND ppa.action_status = 'C'
352 AND paa.action_status = 'C'
353 AND paa.SOURCE_ACTION_ID is not null
354 AND ppa.action_type in ('Q','R')
355 AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
356 --AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
357 AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
358 AND ppa.EFFECTIVE_DATE between peev.effective_start_date AND peev.effective_end_date
359 AND ppa.EFFECTIVE_DATE between piv.effective_start_date AND piv.effective_end_date
360 AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
361 AND paaf.effective_start_date =
362 (select max(paaf1.effective_start_date)
363 from per_all_assignments_f paaf1 where
364 paaf1.assignment_id = paaf.assignment_id
365 and paaf1.assignment_type = 'E'
366 and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
367 group by paaf.person_id;
368
369
370 BEGIN
371
372 hr_utility.set_location('Entering: '||l_proc,1);
373
374 open csr_parameter_info;
375 fetch csr_parameter_info into l_tax_ref,
376 l_payroll_id,
377 l_payment_from_date,
378 l_payment_to_date,
379 l_business_group_id;
380 close csr_parameter_info;
381
382
383 hr_utility.set_location('Fetching Assignments with DEO type Court Order',15);
384 -- To fetch DEO records
385 for asg_rec in csr_get_deo_asg(l_tax_ref,l_payroll_id,l_payment_from_date,l_payment_to_date,l_business_group_id)
386 loop
387 hr_utility.set_location('Assignment ID :' || asg_rec.assignment_id,15);
388 --hr_utility.set_location('Assignment ID :' || asg_rec.assignment_action_id,15);
389 --hr_utility.set_location('Assignment ID :' || asg_rec.date_paid,15);
390
391 -- Create one assignment action for every assignment
392 hr_utility.set_location('Archiving for assignment_id '||asg_rec.assignment_id, 50);
393 select pay_assignment_actions_s.nextval
394 into lockingactid
395 from dual;
396
397 -- Insert assignment into pay_assignment_actions
398 hr_nonrun_asact.insact
399 (
400 lockingactid,
401 asg_rec.assignment_id,
402 pactid,
403 chunk,
404 null
405 );
406 end loop;
407 hr_utility.set_location('Leaving: '||l_proc,999);
408
409 end action_creation;
410
411
412 /*--------------------------------------------------------------------------
413 Name : archinit
414 Purpose : This procedure can be used to perform an initialisation
415 section and validation of Employer details
416 Arguments :
417 Notes :
418 --------------------------------------------------------------------------*/
419 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
420 IS
421 l_proc CONSTANT VARCHAR2(50) := g_package || ' archinit';
422
423 BEGIN
424 hr_utility.set_location('Entering '|| l_proc, 10);
425
426 hr_utility.set_location('Leaving '|| l_proc, 10);
427 END archinit;
428 --
429 --
430 --
431 /*--------------------------------------------------------------------------
432 Name : archive_data
433 Purpose : This sets up the contexts needed for the live (non-archive)
434 database items
435 Arguments :
436 Notes : Every possible context for a specific assignment action has to
437 be added to the PL/SQL table
438 --------------------------------------------------------------------------*/
439 --
440 --
441 PROCEDURE archive_code(p_assactid IN NUMBER,
442 p_effective_date IN DATE) IS
443
444 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
445 l_payroll_id number;
446 l_payroll_name varchar2(80);
447 l_tax_ref varchar2(20);
448 l_business_group_id number;
449 l_payment_to_date date;
450 l_payment_from_date date;
451 l_reason varchar2(80);
452 l_archive_tab action_info_table;
453 l_archive_person boolean;
454 l_archive_asg boolean;
455
456 cursor csr_parameter_info is
457 select substr(pay_gb_eoy_archive.get_parameter(pact.legislative_parameters,'TAX_REF'),1,20) tax_ref,
458 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
459 --to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
460 --to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
461 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
462 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
463 business_group_id
464 from pay_payroll_actions pact,
465 pay_assignment_actions paa
466 where paa.assignment_action_id = p_assactid
467 and paa.payroll_action_id = pact.payroll_action_id;
468
469
470 cursor get_deo_asg_ref(p_asg_tax_ref IN VARCHAR2,p_person_id IN number,p_payroll_id IN number,p_payment_from_date IN DATE,p_payment_to_date IN DATE,p_business_group_id IN number)
471 is
472 select paaf.person_id person_id,max(peef.assignment_id) assignment_id,max(peef.element_entry_id) element_entry_id,peev2.screen_entry_value employee_ref
473 from pay_element_types_f pet1,
474 pay_input_values_f piv1,
475 pay_input_values_f piv2,
476 pay_element_entry_values_f peev1,
477 pay_element_entry_values_f peev2,
478 pay_element_entries_f peef,
479 per_all_assignments_f paaf,
480 hr_soft_coding_keyflex scl,
481 pay_all_payrolls_f ppf,
482 pay_assignment_actions paa,
483 pay_payroll_actions ppa
484 where pet1.element_name in ('Court Order','Court Order NTPP')
485 AND pet1.business_group_id IS NULL
486 AND pet1.legislation_code = 'GB'
487 AND pet1.element_type_id = piv1.element_type_id
488 AND piv1.business_group_id IS NULL
489 AND piv1.legislation_code = 'GB'
490 AND piv1.name = 'Type'
491 AND piv1.input_value_id = peev1.input_value_id
492 AND peev1.screen_entry_value in ('DEO','DEO_PERCENT','CMS_DEO')
493 AND peev1.element_entry_id = peef.element_entry_id
494 AND paaf.person_id = p_person_id
495 AND peef.assignment_id = paaf.assignment_id
496 AND ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
497 AND scl.segment1 = p_asg_tax_ref
498 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
499 AND paaf.payroll_id = ppf.payroll_id
500 AND ppa.payroll_id = ppf.payroll_id
501 AND peev1.element_entry_id = peev2.element_entry_id
502 AND piv1.element_type_id = piv2.element_type_id
503 AND peev2.input_value_id = piv2.input_value_id
504 AND piv2.name in ('Reference')
505 AND piv2.business_group_id IS NULL
506 AND piv2.legislation_code = 'GB'
507 AND paaf.assignment_id = paa.assignment_id
508 AND paa.assignment_id = peef.assignment_id
509 AND paa.payroll_action_id = ppa.payroll_action_id
510 AND ppa.payroll_id = ppf.payroll_id
511 AND ppa.business_group_id = p_business_group_id
512 AND paaf.business_group_id = ppa.business_group_id
513 AND ppa.action_status = 'C'
514 AND paa.action_status = 'C'
515 AND paa.SOURCE_ACTION_ID is not null
516 AND ppa.action_type in ('Q','R')
517 AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
518 -- AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
519 AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
520 AND ppa.EFFECTIVE_DATE between peev1.effective_start_date AND peev1.effective_end_date
521 AND ppa.EFFECTIVE_DATE between peev2.effective_start_date AND peev2.effective_end_date
522 AND ppa.EFFECTIVE_DATE between piv1.effective_start_date AND piv1.effective_end_date
523 AND ppa.EFFECTIVE_DATE between piv2.effective_start_date AND piv2.effective_end_date
524 AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
525 AND paaf.effective_start_date =
526 (select max(paaf1.effective_start_date)
527 from per_all_assignments_f paaf1 where
528 paaf1.assignment_id = paaf.assignment_id
529 and paaf1.assignment_type = 'E'
530 and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
531 group by paaf.person_id,peev2.screen_entry_value;
532
533 cursor get_reason(p_element_entry_id IN number)
534 is
535 select SCREEN_ENTRY_VALUE
536 from pay_element_entry_values_f
537 where element_entry_id = p_element_entry_id
538 and INPUT_VALUE_ID
539 in (select INPUT_VALUE_ID from pay_input_values_f where name = 'DEO Underpayment Reason');
540
541 cursor get_deo_assactid(p_asg_tax_ref IN VARCHAR2,p_assignment_id IN number,p_element_entry_id IN number,p_employee_ref IN varchar2,
542 p_payroll_id IN number,p_payment_from_date IN DATE,p_payment_to_date IN DATE,p_business_group_id IN number)
543 is
544 select paa.assignment_action_id asgactid,
545 peef.assignment_id,ppa.EFFECTIVE_DATE
546 from pay_element_types_f pet,
547 pay_input_values_f piv,
548 pay_element_entry_values_f peev,
549 pay_element_entries_f peef,
550 per_all_assignments_f paaf,
551 hr_soft_coding_keyflex sc,
552 pay_all_payrolls_f ppf,
553 pay_assignment_actions paa,
554 pay_payroll_actions ppa
555 where pet.element_name in ('Court Order','Court Order NTPP')
556 AND pet.business_group_id IS NULL
557 AND pet.legislation_code = 'GB'
558 AND pet.element_type_id = piv.element_type_id
559 AND piv.business_group_id IS NULL
560 AND piv.legislation_code = 'GB'
561 AND piv.name = 'Reference'
562 AND piv.input_value_id = peev.input_value_id
563 AND nvl(peev.screen_entry_value, ' ') = nvl(p_employee_ref, ' ')
564 AND peev.element_entry_id = p_element_entry_id
565 AND peev.element_entry_id = peef.element_entry_id
566 AND peef.assignment_id = paaf.assignment_id
567 AND ppf.soft_coding_keyflex_id = sc.soft_coding_keyflex_id
568 AND sc.segment1 = p_asg_tax_ref
569 AND ppf.payroll_id = nvl(p_payroll_id,ppf.payroll_id)
570 AND paaf.payroll_id = ppf.payroll_id
571 AND ppa.payroll_id = ppf.payroll_id
572 AND paaf.assignment_id = p_assignment_id
573 AND paaf.assignment_id = paa.assignment_id
574 AND paa.assignment_id = peef.assignment_id
575 AND paa.payroll_action_id = ppa.payroll_action_id
576 AND ppa.business_group_id = p_business_group_id
577 AND paaf.business_group_id = ppa.business_group_id
578 AND ppa.action_status = 'C'
579 AND paa.action_status = 'C'
580 AND paa.SOURCE_ACTION_ID is not null
581 AND ppa.action_type in ('Q','R')
582 AND ppa.EFFECTIVE_DATE between p_payment_from_date and p_payment_to_date
583 --AND ppa.EFFECTIVE_DATE between paaf.effective_start_date AND paaf.effective_end_date
584 AND ppa.EFFECTIVE_DATE between peef.effective_start_date AND peef.effective_end_date
585 AND ppa.EFFECTIVE_DATE between peev.effective_start_date AND peev.effective_end_date
586 AND ppa.EFFECTIVE_DATE between piv.effective_start_date AND piv.effective_end_date
587 AND ppa.EFFECTIVE_DATE between ppf.effective_start_date AND ppf.effective_end_date
588 AND paaf.effective_start_date =
589 (select max(paaf1.effective_start_date)
590 from per_all_assignments_f paaf1 where
591 paaf1.assignment_id = paaf.assignment_id
592 and paaf1.assignment_type = 'E'
593 and paaf1.effective_start_date <= ppa.EFFECTIVE_DATE)
594 order by peef.assignment_id,paa.assignment_action_id;
595
596 CURSOR get_defined_balance_id (p_balance_name VARCHAR2, p_dimension_name VARCHAR2)
597 IS
598 SELECT defined_balance_id
599 FROM pay_defined_balances db,
600 pay_balance_types b,
601 pay_balance_dimensions d
602 WHERE b.balance_name = p_balance_name
603 AND d.dimension_name = p_dimension_name
604 AND db.balance_type_id = b.balance_type_id
605 AND db.balance_dimension_id = d.balance_dimension_id;
606
607 --deo_asg_rec get_deo_asg_actid%rowtype;
608 l_rec get_deo_asg_ref%rowtype;
609 l_asgactid get_deo_assactid%rowtype;
610 l_count number := 0;
611 l_amount number := 0;
612 l_sum_amount number := 0;
613 l_due number := 0;
614 l_sum_due number := 0;
615
616
617 BEGIN
618 hr_utility.set_location('Entering: '||l_proc,1);
619 hr_utility.set_location('p_effective_date: '||p_effective_date,1);
620 open csr_parameter_info;
621 fetch csr_parameter_info into l_tax_ref,
622 l_payroll_id,
623 l_payment_from_date,
624 l_payment_to_date,
625 l_business_group_id;
626 close csr_parameter_info;
627
628
629 hr_utility.set_location('Archive Code ',10);
630 hr_utility.set_location('Fetching Employee details ',20);
631 -- Fetching employee details
632 l_archive_person := fetch_person_rec(p_assactid, p_effective_date,l_archive_tab(0));
633
634 hr_utility.set_location('Fetching Assignment DEO details ',30);
635
636
637 OPEN get_defined_balance_id('Court Order','_PER_CO_TD_REF_PTD');
638 FETCH get_defined_balance_id INTO g_co_id;
639 CLOSE get_defined_balance_id;
640
641 OPEN get_defined_balance_id('Court Order Arrears Deduction','_PER_CO_TD_REF_PTD');
642 FETCH get_defined_balance_id INTO g_co_arrears_id;
643 CLOSE get_defined_balance_id;
644
645 open get_deo_asg_ref(l_tax_ref,l_archive_tab(0).person_id,l_payroll_id,l_payment_from_date,l_payment_to_date,l_business_group_id);
646 loop
647 fetch get_deo_asg_ref into l_rec;
648 exit when get_deo_asg_ref%notfound;
649 l_count := l_count + 1;
650 l_archive_tab(l_count).assignment_id := l_rec.assignment_id;
651 l_archive_tab(l_count).act_info3 := l_rec.employee_ref;
652 l_archive_tab(l_count).act_info7 := l_rec.person_id;
653
654 l_sum_amount := 0;
655 l_sum_due := 0;
656 open get_deo_assactid(l_tax_ref,l_rec.assignment_id,l_rec.element_entry_id,l_rec.employee_ref,l_payroll_id,l_payment_from_date,l_payment_to_date,l_business_group_id);
657 loop
658 fetch get_deo_assactid into l_asgactid;
659 exit when get_deo_assactid%notfound;
660 pay_balance_pkg.set_context('SOURCE_TEXT',nvl(l_rec.employee_ref,'Unknown'));
661 l_amount := pay_balance_pkg.get_value(g_co_id,l_asgactid.asgactid);
662 l_sum_amount := l_sum_amount + l_amount;
663 l_due := pay_balance_pkg.get_value(g_co_arrears_id,l_asgactid.asgactid);
664 l_sum_due := l_sum_due + l_due;
665 end loop;
666 close get_deo_assactid;
667
668 g_total_paid := g_total_paid +l_sum_amount;
669
670 open get_reason(l_rec.element_entry_id);
671 fetch get_reason into l_reason;
672 close get_reason;
673
674 if l_reason = '01' then
675 l_reason := '01 Left Employment';
676 elsif l_reason = '02' then
677 l_reason := '02 Statutory Sick Pay';
678 elsif l_reason = '03' then
679 l_reason := '03 Statutory Maternity Pay';
680 elsif l_reason = '04' then
681 l_reason := '04 Statutory Paternity Pay';
682 elsif l_reason = '05' then
683 l_reason := '05 Protected Earning';
684 elsif l_reason = '06' then
685 l_reason := '06 NRP Change of Income (where NRP stands for Non Resident Parent)';
686 elsif l_reason = '07' then
687 l_reason := '07 Deceased';
688 elsif l_reason = '10' then
689 l_reason := '10 Statutory Adoption Pay';
690 elsif l_reason = '11' then
691 l_reason := '11 Additional Statutory Paternity Pay';
692 elsif l_reason = '12' then
693 l_reason := '12 Multiple Reasons';
694 elsif l_reason = '13' then
695 l_reason := '13 Redundancy';
696 end if;
697
698 l_archive_tab(l_count).act_info6 := l_reason;
699 l_archive_tab(l_count).act_info5 := l_sum_amount;
700 l_archive_tab(l_count).act_info4 := l_sum_due;
701
702 l_archive_tab(l_count).action_info_category := 'GB DEO ASG DETAILS';
703 end loop;
704 close get_deo_asg_ref;
705 if l_archive_person then
706 insert_archive_row(p_assactid, p_effective_date,l_archive_tab);
707 end if;
708
709 hr_utility.set_location('Leaving: '||l_proc,999);
710
711 EXCEPTION
712 when others then
713 raise_application_error(-20001,'Errors found while archiving data.');
714 END archive_code;
715
716
717 --
718 --
719 PROCEDURE deinitialization_code(pactid IN NUMBER)
720 IS
721 l_proc CONSTANT VARCHAR2(50) := g_package || 'deinitialization_code';
722 l_counter number;
723 l_request_id fnd_concurrent_requests.request_id%TYPE;
724 xml_layout boolean;
725 l_payroll_name VARCHAR2(80) := '';
726
727 l_action_info_id NUMBER(15);
728 l_ovn NUMBER;
729 l_is_etext_report varchar2(50);
730
731 Cursor csr_is_etext_report IS
732 Select report_type
733 From pay_payroll_actions pact
734 Where pact.payroll_action_id = pactid;
735
736
737 cursor csr_parameter_info is
738 select substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
739 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
740 --to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
741 --to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
742 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
743 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date,
744 business_group_id,
745 pact.effective_date,
746 pay_gb_eoy_archive.get_parameter(legislative_parameters,'REPORT_FORMAT') report_format,
747 fnd_number.number_to_canonical(pact.request_id) request_id,
748 nvl(upper(substr(ltrim(hoi.org_information3),1,35)),' ') employer_name,
749 nvl(substr(hoi.org_information20,1,12),' ') employer_no
750 from pay_payroll_actions pact,
751 hr_organization_information hoi
752 where payroll_action_id = pactid
753 and pact.business_group_id = hoi.organization_id
754 and hoi.org_information_context = 'Tax Details References'
755 and nvl(hoi.org_information10,'UK') = 'UK'
756 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
757 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
758 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
759
760 l_emp_rec csr_parameter_info%rowtype;
761
762 cursor get_payroll_name(p_payroll_id IN varchar2,p_business_group_id IN number,p_effective_date IN date)
763 is
764 select ppf.payroll_name
765 from pay_payrolls_f ppf
766 where ppf.payroll_id = p_payroll_id
767 and ppf.business_group_id = p_business_group_id
768 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
769
770 ---------------------
771 ---------------------
772 procedure write_header(report_type varchar2) is
773 l_token varchar2(255);
774 l_addr1 varchar2(255);
775 l_addr2 varchar2(255);
776 l_addr3 varchar2(255);
777 l_addr4 varchar2(255);
778 l_tax_ref varchar2(20);
779 l_temp number;
780 l_form_name varchar2(100);
781 l_payroll_name VARCHAR2(80) := '';
782
783 cursor csr_leg_param is
784 select legislative_parameters para,
785 fnd_number.number_to_canonical(request_id) control_id,
786 report_type,
787 business_group_id,
788 pact.effective_date
789 from pay_payroll_actions pact
790 where payroll_action_id = pactid;
791
792 cursor csr_parameter_info is
793 select
794 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
795 -- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_FROM=') + 13,10),'YYYY-MM-DD') payment_from_date,
796 -- to_date(substr(pact.legislative_parameters,instr(pact.legislative_parameters,'PAYMENT_TO=') + 11,10),'YYYY-MM-DD') payment_to_date,
797 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_FROM'),1,10)) payment_from_date,
798 fnd_date.canonical_to_date(SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYMENT_TO'),1,10)) payment_to_date
799 from pay_payroll_actions pact
800 where payroll_action_id = pactid;
801
802 cursor csr_header_det(p_bus_id number,
803 p_tax_ref varchar2) is
804 select nvl(hoi.org_information11,' ') sender_id,
805 nvl(upper(hoi.org_information2),' ') hrmc_office,
806 nvl(upper(hoi.org_information4),' ') er_addr,
807 nvl(upper(hoi.org_information3),' ') er_name
808 from hr_organization_information hoi
809 where hoi.organization_id = p_bus_id
810 and hoi.org_information_context = 'Tax Details References'
811 and nvl(hoi.org_information10,'UK') = 'UK'
812 and upper(hoi.org_information1) = upper(p_tax_ref);
813
814
815 cursor get_payroll_name(p_payroll_id IN varchar2,p_business_group_id IN number,p_effective_date IN date)
816 is
817 select ppf.payroll_name
818 from pay_payrolls_f ppf
819 where ppf.payroll_id = p_payroll_id
820 and ppf.business_group_id = p_business_group_id
821 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
822
823 l_param csr_leg_param%rowtype;
824 l_det csr_header_det%rowtype;
825 l_rec csr_parameter_info%rowtype;
826
827 begin
828
829 open csr_leg_param;
830 fetch csr_leg_param into l_param;
831 close csr_leg_param;
832
833 open csr_parameter_info;
834 fetch csr_parameter_info into l_rec;
835 close csr_parameter_info;
836
837 if l_rec.payroll_id is not null then
838 open get_payroll_name(l_rec.payroll_id,l_param.business_group_id,l_param.effective_date);
839 fetch get_payroll_name into l_payroll_name;
840 close get_payroll_name;
841 end if;
842
843 l_token := 'TAX_REF';
844 l_temp := instr(l_param.para,l_token);
845 l_tax_ref := substr(l_param.para, l_temp + length(l_token) + 1,
846 instr(l_param.para||' ',' ',l_temp) - (l_temp + length(l_token) + 1));
847
848
849 open csr_header_det(l_param.business_group_id, l_tax_ref);
850 fetch csr_header_det into l_det;
851 close csr_header_det;
852
853 l_addr1 := l_det.er_addr;
854 if length(l_addr1) > 35 then
855 l_temp := instr(l_addr1, ',', 34 - length(l_addr1));
856 if l_temp = 0 then
857 l_temp := 35;
858 end if;
859 l_addr2 := ltrim(substr(l_addr1, 1 + l_temp),' ,');
860 l_addr1 := substr(l_addr1,1,l_temp);
861 end if;
862 if length(l_addr2) > 35 then
863 l_temp := instr(l_addr2, ',', 34 - length(l_addr2));
864 if l_temp = 0 then
865 l_temp := 35;
866 end if;
867 l_addr3 := ltrim(substr(l_addr2, 1 + l_temp),' ,');
868 l_addr2 := substr(l_addr2,1,l_temp);
869 end if;
870 if length(l_addr3) > 35 then
871 l_temp := instr(l_addr3, ',', 34 - length(l_addr3));
872 if l_temp = 0 then
873 l_temp := 35;
874 end if;
875 l_addr3 := ltrim(substr(l_addr3, 1 + l_temp),' ,');
876 l_addr4 := substr(l_addr3,1,l_temp);
877 end if;
878
879 l_form_name :='DEO Payment Schedule';
880
881
882
883 fnd_file.put_line(fnd_file.output,' ');
884 fnd_file.put_line(fnd_file.output,'EDI Transmission Report:');
885 fnd_file.put_line(fnd_file.output,' ');
886 fnd_file.put_line(fnd_file.output,rpad('Form Type : ',32) || l_form_name);
887 fnd_file.put_line(fnd_file.output,rpad('Sender : ',32) || l_det.sender_id);
888 fnd_file.put_line(fnd_file.output,rpad('Date : ',32) || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'));
889 fnd_file.put_line(fnd_file.output,rpad('Interchange Control Reference : ',32) || l_param.control_id);
890 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
891 fnd_file.put_line(fnd_file.output,rpad('Employers PAYE Reference : ',32) || l_tax_ref);
892 --fnd_file.put_line(fnd_file.output,rpad('HMRC Office : ',32) || l_det.hrmc_office);
893 fnd_file.put_line(fnd_file.output,rpad('Employer Name : ',32) || l_det.er_name);
894 fnd_file.put_line(fnd_file.output,rpad('Employer Address : ',32) || l_addr1);
895 fnd_file.put_line(fnd_file.output,rpad('Payroll : ',32) || l_payroll_name);
896 fnd_file.put_line(fnd_file.output,rpad('Payment from date : ',32) || l_rec.payment_from_date);
897 fnd_file.put_line(fnd_file.output,rpad('Payment to date : ',32) || l_rec.payment_to_date);
898
899
900 if length(l_addr2) > 0 then
901 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr2);
902 end if;
903 if length(l_addr3) > 0 then
904 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr3);
905 end if;
906 if length(l_addr4) > 0 then
907 fnd_file.put_line(fnd_file.output,rpad(' ',32) || l_addr4);
908 end if;
909
910 end write_header;
911 ---------------------
912 procedure write_sub_header(p_type varchar2,report_type varchar2) is
913 begin
914 fnd_file.put_line(fnd_file.output,null);
915 if p_type = 'E' then
916 fnd_file.put_line(fnd_file.output,'The following employments have completed with error');
917 else
918 fnd_file.put_line(fnd_file.output,'The following employments have completed successfully');
919 end if;
920
921
922 if p_type <> 'E' then --Completed records
923 fnd_file.put_line(fnd_file.output,rpad('Employee Number',19) ||
924 rpad('NI Number',11) ||
925 rpad('Employee Name', 51));
926 end if;
927
928
929 if p_type <> 'E' then -- Completed records
930 fnd_file.put_line(fnd_file.output,rpad('-',18,'-') || ' ' ||
931 rpad('-',10,'-') || ' ' ||
932 rpad('-',50,'-') );
933
934 end if;
935
936 end write_sub_header;
937 ---------------------------
938 procedure write_body(p_type varchar2,pactid IN NUMBER,report_type varchar2) is
939 l_count number;
940 i number;
941 l_temp varchar2(255);
942 l_emp_count number;
943 l_number varchar2(30);
944
945 -- Cursor to fetch Completed records
946 cursor get_asg_action_id is
947 select distinct paa.assignment_action_id asg_action_id, paa.assignment_id
948 , paaf.assignment_number
949 from pay_payroll_actions ppa,
950 pay_assignment_actions paa
951 , per_all_assignments_f paaf
952 where ppa.payroll_action_id = pactid -- pact_id
953 and paa.payroll_action_id = ppa.payroll_action_id
954 and paa.action_status = 'C'
955 and paaf.assignment_id = paa.assignment_id
956 and paaf.effective_start_date =
957 (
958 SELECT MAX(paaf2.effective_start_date)
959 FROM per_all_assignments_f paaf2
960 WHERE paaf2.assignment_id = paaf.assignment_id
961 )
962 ORDER BY
963 paaf.assignment_number;
964
965
966 cursor get_person_details_archive(c_assignment_id number) is
967 select pai_emp.action_information3 f_name,
968 pai_emp.action_information4 l_name,
969 nvl(pai_emp.action_information5,' ')ni_no,
970 nvl(pai_emp.action_information6,' ')emp_no
971 from pay_action_information pai_emp,
972 pay_assignment_actions paa
973 where paa.payroll_action_id = pactid and paa.ACTION_STATUS = 'C' and paa.assignment_id = c_assignment_id
974 and pai_emp.action_context_id = paa.assignment_action_id
975 and pai_emp.action_information_category = 'GB DEO EMPLOYEE DETAILS';
976
977 asg_rec1 get_person_details_archive%rowtype;
978
979 l_et_temp varchar2(255);
980 begin
981
982 l_count := 0;
983
984 --Completed Records
985 IF p_type = 'ET' THEN
986 FOR action_id IN get_asg_action_id
987 LOOP
988
989 open get_person_details_archive(action_id.assignment_id);
990 fetch get_person_details_archive into asg_rec1;
991 close get_person_details_archive;
992
993
994 l_et_temp := asg_rec1.l_name || ', '|| asg_rec1.f_name;
995
996 fnd_file.put_line(fnd_file.output,rpad(asg_rec1.emp_no, 18) || ' ' ||
997 rpad(asg_rec1.ni_no ,10) || ' ' ||
998 rpad(l_et_temp,50)) ;
999
1000 l_count := l_count + 1;
1001 END LOOP;
1002 END IF;
1003
1004 fnd_file.put_line(fnd_file.output,null);
1005 --fnd_file.put_line(fnd_file.output,'Total Number of employments completed successfully :' || l_count);
1006
1007 l_counter := l_counter + l_count;
1008
1009 end write_body;
1010 -----------------------
1011 procedure write_footer is
1012
1013 cursor get_person_count is
1014 select count(distinct(paaf.person_id))
1015 from pay_payroll_actions ppa,
1016 pay_assignment_actions paa,
1017 per_all_assignments_f paaf
1018 where ppa.payroll_action_id = pactid -- pact_id
1019 and paa.payroll_action_id = ppa.payroll_action_id
1020 and paa.action_status = 'C'
1021 and paaf.assignment_id = paa.assignment_id;
1022
1023 l_person_count number;
1024 begin
1025 open get_person_count;
1026 fetch get_person_count into l_person_count;
1027 close get_person_count;
1028
1029 fnd_file.put_line(fnd_file.output,null);
1030 -- fnd_file.put_line(fnd_file.output,'Total Number Of employments : ' || l_counter);
1031 fnd_file.put_line(fnd_file.output,null);
1032 fnd_file.put_line(fnd_file.output,'Total Number of employees successfully processed : ' || l_person_count);
1033
1034 end write_footer;
1035
1036 ---------------------
1037 ---------------------
1038
1039 BEGIN
1040
1041 hr_utility.set_location('Entering deinit: '||l_proc,1);
1042
1043 -- To avoid re-archiving while Retry
1044 delete from pay_action_information pai
1045 where pai.action_context_id = pactid
1046 and pai.action_context_type = 'PA'
1047 and pai.action_information_category in ('GB DEO EMPLOYER DETAILS');
1048
1049 open csr_parameter_info;
1050 fetch csr_parameter_info into l_emp_rec;
1051 close csr_parameter_info;
1052
1053 if l_emp_rec.payroll_id is not null then
1054 open get_payroll_name(l_emp_rec.payroll_id,l_emp_rec.business_group_id,l_emp_rec.effective_date);
1055 fetch get_payroll_name into l_payroll_name;
1056 close get_payroll_name;
1057
1058 end if;
1059
1060 pay_balance_pkg.set_context('PAYROLL_ACTION_ID',pactid);
1061
1062 hr_utility.set_location('Business group id = ' || l_emp_rec.business_group_id,10);
1063 hr_utility.set_location('Employer PAYE Reference = ' || l_emp_rec.tax_ref,20);
1064 hr_utility.set_location('Employer Name = ' || l_emp_rec.employer_name,30);
1065 hr_utility.set_location('Payroll Name = '||l_payroll_name,40);
1066 hr_utility.set_location('Payment from date = '||l_emp_rec.payment_from_date,50);
1067 hr_utility.set_location('Payment to date = '||l_emp_rec.payment_to_date,60);
1068
1069 hr_utility.set_location('Archiving GB DEO EMPLOYER DETAILS',60);
1070
1071 pay_action_information_api.create_action_information (
1072 p_action_information_id => l_action_info_id
1073 , p_action_context_id => pactid
1074 , p_action_context_type => 'PA'
1075 , p_object_version_number => l_ovn
1076 , p_source_id => NULL
1077 , p_source_text => NULL
1078 , p_action_information_category => 'GB DEO EMPLOYER DETAILS'
1079 , p_action_information1 => pactid
1080 , p_action_information3 => l_emp_rec.employer_name
1081 , p_action_information4 => l_emp_rec.tax_ref
1082 , p_action_information5 => l_emp_rec.employer_no
1083 , p_action_information6 => l_payroll_name
1084 , p_action_information7 => l_emp_rec.payment_from_date
1085 , p_action_information8 => l_emp_rec.payment_to_date
1086 , p_action_information9 => g_total_paid
1087 , p_action_information10 => to_char(l_emp_rec.request_id));
1088
1089
1090 OPEN csr_is_etext_report;
1091 FETCH csr_is_etext_report INTO l_is_etext_report;
1092 CLOSE csr_is_etext_report;
1093
1094 l_counter := 0;
1095 write_header(l_is_etext_report);
1096 write_sub_header('C',l_is_etext_report);
1097 write_body('ET',pactid,l_is_etext_report);
1098 write_footer;
1099
1100 -- For Spawning the output Concurrent Program
1101
1102 hr_utility.set_location('Spawning DEO Payment Schedule Output process:'||l_emp_rec.report_format,1);
1103
1104 if (xml_layout) then
1105 fnd_file.put_line(fnd_file.LOG,'xml_layout is false');
1106 end if;
1107
1108 fnd_file.put_line(fnd_file.LOG,'l_emp_rec.report_format'||l_emp_rec.report_format);
1109
1110 if l_emp_rec.report_format = 'XML' then
1111 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBDEOSOP','en','US','XML');
1112 elsif l_emp_rec.report_format = 'Plain' then
1113 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBDEOSOPRTF','en','US','PDF');
1114 elsif l_emp_rec.report_format = 'CSV' then
1115 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PAYGBDEOSOPCSV','en','US','ETEXT');
1116 end if;
1117 if xml_layout then
1118 hr_utility.set_location('xml_layout is true',1);
1119 fnd_file.put_line(fnd_file.LOG,'xml_layout is true');
1120 else
1121 hr_utility.set_location('xml_layout is false',2);
1122 fnd_file.put_line(fnd_file.LOG,'xml_layout is false');
1123 end if;
1124
1125 IF (xml_layout = true and g_validation_check = 'Y')
1126 THEN
1127
1128 l_request_id := fnd_request.submit_request
1129 (application => 'PAY'
1130 ,program => 'PYGBDEOSOP'
1131 ,argument1 => pactid
1132 );
1133
1134 Commit;
1135 --check for process submit error
1136 IF l_request_id = 0
1137 THEN
1138 fnd_file.put_line(fnd_file.LOG,'Error spawning new process');
1139 hr_utility.set_location('Error spawning new process',1);
1140 END IF;
1141 END IF;
1142
1143 hr_utility.set_location('Leaving: '||l_proc,999);
1144
1145 END deinitialization_code;
1146 --
1147 --
1148 end PAY_GB_DEO;