[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P45PART3_P46_PKG
Source
1 PACKAGE BODY PAY_IE_P45PART3_P46_PKG AS
2 /* $Header: pyiep45p3p46.pkb 120.6 2011/12/05 06:59:31 smeduri ship $ */
3 g_package VARCHAR2(50) := 'pay_ie_p45part3_p46_pkg.';
4 EOL VARCHAR2(5) := fnd_global.local_chr(10);
5 l_errflag VARCHAR2(1) := 'N';
6 l_p45_exception exception;
7 error_message boolean;
8 /*-----------------------------------------------------
9 TEST_XML
10 -----------------------------------------------------*/
11
12 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2 AS
13 l_string varchar2(1000);
14
15 FUNCTION replace_xml_symbols(pp_string IN VARCHAR2)
16 RETURN VARCHAR2
17 AS
18
19 ll_string VARCHAR2(1000);
20
21 BEGIN
22
23
24 ll_string := pp_string;
25
26 ll_string := replace(ll_string, '&', '&');
27 ll_string := replace(ll_string, '<', '<');
28 ll_string := replace(ll_string, '>', '>');
29 ll_string := replace(ll_string, '''',''');
30 ll_string := replace(ll_string, '"', '"');
31
32 RETURN ll_string;
33 EXCEPTION when no_data_found then
34 null;
35 END replace_xml_symbols;
36
37 begin
38 l_string := p_string;
39 l_string := replace_xml_symbols(l_string);
40 --7529405
41 l_string := pay_ie_p35_magtape.test_XML(l_string);
42 /*
43 l_string := replace(l_string,COMPOSE ('A'|| UNISTR('\0301')),'Á');
44 l_string := replace(l_string,COMPOSE ('E'|| UNISTR('\0301')),'É');
45 l_string := replace(l_string,COMPOSE ('I'|| UNISTR('\0301')),'Í');
46 l_string := replace(l_string,COMPOSE ('O'|| UNISTR('\0301')),'Ó');
47 l_string := replace(l_string,COMPOSE ('U'|| UNISTR('\0301')),'Ú');
48 l_string := replace(l_string,COMPOSE ('a'|| UNISTR('\0301')),'á');
49 l_string := replace(l_string,COMPOSE ('e'|| UNISTR('\0301')),'é');
50 l_string := replace(l_string,COMPOSE ('i'|| UNISTR('\0301')),'í');
51 l_string := replace(l_string,COMPOSE ('o'|| UNISTR('\0301')),'ó');
52 l_string := replace(l_string,COMPOSE ('u'|| UNISTR('\0301')),'ú');
53 */
54 --l_string := replace_xml_symbols(l_string);
55 --7529405
56
57 RETURN l_string;
58 END ;
59
60 -----------------------------------------------------------------------
61 -- GET_PARAMETERS
62 -----------------------------------------------------------------------
63 PROCEDURE get_parameters
64 (
65 p_payroll_action_id IN NUMBER,
66 p_token_name IN VARCHAR2,
67 p_token_value out nocopy VARCHAR2
68 ) IS
69
70 CURSOR csr_parameter_info
71 (
72 p_pact_id NUMBER,
73 p_token CHAR
74 ) IS
75
76 SELECT TRIM(SUBSTR
77 (
78 legislative_parameters,
79 DECODE(INSTR
80 (
81 legislative_parameters,
82 p_token
83 ),0,LENGTH(legislative_parameters),INSTR
84 (
85 legislative_parameters,
86 p_token
87 )) + (LENGTH(p_token) + 1),
88 DECODE(INSTR
89 (
90 legislative_parameters,
91 ' ',
92 INSTR
93 (
94 legislative_parameters,
95 p_token
96 )),0,LENGTH(legislative_parameters),INSTR
97 (
98 legislative_parameters,
99 ' ',
100 INSTR
101 (
102 legislative_parameters,
103 p_token
104 )))
105 -
106 (
107 INSTR
108 (
109 legislative_parameters,
110 p_token
111 ) + LENGTH(p_token)
112 )
113 )),
114 TRIM(business_group_id)
115 FROM pay_payroll_actions
116 WHERE payroll_action_id = p_pact_id;
117
118 l_business_group_id VARCHAR2(300);
119 l_token_value VARCHAR2(300);
120 l_proc VARCHAR2(50) := g_package ||'get_parameters';
121
122 BEGIN
123
124 hr_utility.set_location('Entering ' || l_proc, 100);
125 hr_utility.set_location('p_token_name ' || TO_CHAR(p_token_name), 110);
126
127 OPEN csr_parameter_info
128 (
129 p_payroll_action_id,
130 p_token_name
131 );
132 FETCH csr_parameter_info INTO l_token_value, l_business_group_id;
133 CLOSE csr_parameter_info;
134
135 hr_utility.set_location('l_token_value ' || TO_CHAR(l_token_value), 115);
136 hr_utility.set_location('l_business_group_id ' || TO_CHAR(l_business_group_id), 120);
137
138 IF p_token_name = 'BG_ID' THEN
139 p_token_value := l_business_group_id;
140 hr_utility.set_location('p_token_name '||p_token_name,125);
141 ELSE
142 p_token_value := l_token_value;
143 hr_utility.set_location('p_token_name '||p_token_name,130);
144 END IF;
145
146 hr_utility.set_location('Leaving ' || l_proc, 135);
147 --
148 EXCEPTION
149 WHEN others THEN
150 hr_utility.set_location('Leaving' || l_proc,140);
151 p_token_value := NULL;
152 --
153 END get_parameters;
154 -----------------------------------------------------------------------
155 -- GET_ALL_PARAMETERS
156 -----------------------------------------------------------------------
157 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
158 ,p_rep_group OUT NOCOPY VARCHAR2
159 ,p_payroll_id OUT NOCOPY VARCHAR2
160 ,p_start_date OUT NOCOPY VARCHAR2
161 ,p_end_date OUT NOCOPY VARCHAR2
162 ,p_file_type OUT NOCOPY VARCHAR2
163 ,p_business_Group_id OUT NOCOPY VARCHAR2
164 ,p_person_id OUT NOCOPY VARCHAR2
165 ,p_employer_id OUT NOCOPY VARCHAR2)
166 IS
167
168 BEGIN
169 hr_utility.set_location(' Entering pay_ie_p45p3_p46_pkg.get_all_parameters ', 145);
170
171 get_parameters(p_payroll_action_id,'BG_ID',p_business_Group_id);
172 --get_parameters(p_payroll_action_id,'ORG_STR_VER_ID',P_Org_Struct_Version_Id);
173 get_parameters(p_payroll_action_id,'REP_GROUP',p_rep_group);
174 get_parameters(p_payroll_action_id,'EMPLOYER',p_employer_id);
175 get_parameters(p_payroll_action_id,'PAYROLL',p_payroll_id);
176 get_parameters(p_payroll_action_id,'EMPLOYEE',p_person_id);
177 get_parameters(p_payroll_action_id,'FILE_TYPE',p_file_type);
178 get_parameters(p_payroll_action_id,'START_DATE',p_start_date);
179 get_parameters(p_payroll_action_id,'END_DATE',p_end_date);
180
181 hr_utility.set_location(' p_business_Group_id = '||p_business_Group_id,150);
182 hr_utility.set_location(' REP_GROUP = '||p_rep_group,155);
183 hr_utility.set_location(' EMPLOYER = '||p_employer_id,160);
184 hr_utility.set_location(' PAYROLL = '||p_payroll_id,165);
185 hr_utility.set_location(' EMPLOYEE = '||p_person_id,170);
186 hr_utility.set_location(' FILE_TYPE = '|| p_file_type,175);
187 hr_utility.set_location(' START_DATE = '||p_start_date,180);
188 hr_utility.set_location(' END_DATE = '||p_end_date,185);
189
190
191 IF p_person_id IS NOT NULL THEN
192 g_where_clause1 :=
193 ' and ppf.person_id = '||to_char(p_person_id);
194 ELSE
195 g_where_clause1 :=' and 1=1 ';
196 END IF;
197
198 IF p_payroll_id IS NOT NULL THEN
199 g_where_clause :=
200 ' and papf.payroll_id = '||to_char(p_payroll_id);
201 ELSE
202 g_where_clause :=' and 1=1 ';
203 END IF;
204
205 hr_utility.set_location(' Inside get_all_parameters:g_where_clause: '||g_where_clause,190);
206 hr_utility.set_location(' Inside get_all_parameters:g_where_clause1: '||g_where_clause1,195);
207 --hr_utility.set_location(' Inside get_all_parameters:g_where_clause1: '||g_where_clause1,200);
208 hr_utility.set_location(' Leaving: pay_ie_p45p3_p46_pkg.get_all_parameters: ', 205);
209
210 EXCEPTION
211 WHEN Others THEN
212 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1215);
213 END get_all_parameters;
214 -----------------------------------------------------------------------
215 -- RANGE_CODE
216 -----------------------------------------------------------------------
217 PROCEDURE range_code(pactid IN NUMBER,
218 sqlstr OUT nocopy VARCHAR2)
219 IS
220 l_procedure_name VARCHAR2(100);
221
222 l_start_date VARCHAR2(50);
223 l_end_date VARCHAR2(50);
224
225 CURSOR csr_archive_effective_date(pactid NUMBER) IS
226 SELECT effective_date
227 FROM pay_payroll_actions
228 WHERE payroll_action_id = pactid;
229
230 CURSOR csr_employer_details(c_org_id hr_organization_information.organization_id%type
231 ,c_bg_id hr_organization_units.business_group_id%type) IS
232 select hoi.org_information2 regst_no
233 ,hou.name employer_name
234 ,hoi.org_information3 trade_name
235 ,hla.address_line_1 addr1
236 ,hla.address_line_2 addr2
237 ,hla.address_line_3 addr3
238 ,hoi.org_information4 contact_name
239 ,hla.telephone_number_1 telphone_no
240 ,hla.telephone_number_2 fax
241 from hr_organization_units hou
242 ,hr_organization_information hoi
243 ,hr_locations_all hla
244 where hoi.org_information_context='IE_EMPLOYER_INFO'
245 and hoi.organization_id=c_org_id
246 and hoi.organization_id=hou.organization_id
247 and hou.business_group_id= c_bg_id
248 and hou.location_id=hla.location_id(+);
249
250 l_employer_details csr_employer_details%rowtype;
251 l_action_info_id NUMBER;
252 l_ovn NUMBER;
253 l_regst_no hr_organization_information.org_information2%type;
254 l_trade_name hr_organization_information.org_information3%type;
255 l_employer_name hr_organization_units.name%type;
256 l_addr1 hr_locations_all.address_line_1%type;
257 l_addr2 hr_locations_all.address_line_2%type;
258 l_addr3 hr_locations_all.address_line_3%type;
259 l_contact_name hr_organization_information.org_information4%type;
260 l_telphone_no hr_locations_all.telephone_number_1%type;
261 l_fax_no hr_locations_all.telephone_number_2%type;
262
263 BEGIN
264
265 l_procedure_name := g_package||'range_code';
266
267 hr_utility.set_location('Entering '||l_procedure_name, 200);
268 hr_utility.set_location('pactid '||TO_CHAR(pactid), 200);
269
270 sqlstr := ' select distinct p.person_id' ||
271 ' from per_people_f p,' ||
272 ' pay_payroll_actions pa' ||
273 ' where pa.payroll_action_id = :payroll_action_id' ||
274 ' and p.business_group_id = pa.business_group_id' ||
275 ' order by p.person_id';
276
277 -------------
278
279 OPEN csr_archive_effective_date(pactid);
280 FETCH csr_archive_effective_date
281 INTO g_archive_effective_date;
282 CLOSE csr_archive_effective_date;
283
284 get_all_parameters(pactid
285 ,g_rep_group
286 ,g_payroll_id
287 ,l_start_date
288 ,l_end_date
289 ,g_file_type
290 ,g_business_group_id
291 ,g_person_id
292 ,g_employer_id);
293
294 g_start_date := fnd_date.canonical_to_date(l_start_date);
295 g_end_date := fnd_date.canonical_to_date(l_end_date);
296
297 OPEN csr_employer_details(g_employer_id, g_business_group_id);
298 FETCH csr_employer_details INTO l_employer_details;
299 CLOSE csr_employer_details;
300
301 l_regst_no := l_employer_details.regst_no;
302 l_trade_name := l_employer_details.trade_name;
303 l_employer_name := l_employer_details.employer_name;
304 l_addr1 := l_employer_details.addr1;
305 l_addr2 := l_employer_details.addr2;
306 l_addr3 := l_employer_details.addr3;
307 l_contact_name := l_employer_details.contact_name;
308 l_telphone_no := l_employer_details.telphone_no;
309 l_fax_no := l_employer_details.fax;
310
311 IF l_regst_no IS NOT NULL AND l_employer_name IS NOT NULL THEN
312 l_errflag := 'N';
313 pay_action_information_api.create_action_information
314 ( p_action_information_id => l_action_info_id
315 ,p_action_context_id => pactid
316 ,p_action_context_type => 'PA'
317 ,p_object_version_number => l_ovn
318 ,p_effective_date => g_end_date
319 ,p_source_id => NULL
320 ,p_source_text => NULL
321 ,p_action_information_category => 'IE P45P3 P46 EMPLOYER'
322 ,p_action_information6 => l_regst_no
323 ,p_action_information7 => l_employer_name
324 ,p_action_information8 => l_trade_name
325 ,p_action_information9 => l_addr1
326 ,p_action_information10 => l_addr2
327 ,p_action_information11 => l_addr3
328 ,p_action_information12 => l_contact_name
329 ,p_action_information13 => l_telphone_no
330 ,p_action_information14 => l_fax_no);
331
332 ELSIF l_regst_no IS NULL THEN
333 Fnd_file.put_line(FND_FILE.LOG,'Employer Registered Number is missing ');
334 l_errflag := 'Y';
335 ELSIF l_employer_name IS NULL THEN
336 Fnd_file.put_line(FND_FILE.LOG,'Employer Name is missing ');
337 l_errflag := 'Y';
338 END IF;
339
340 IF l_errflag = 'Y' THEN
341 Fnd_file.put_line(FND_FILE.LOG,'P45P3 Process Failed. Some mandatory parametors are missing. Please check the whole log for details.');
342 Raise l_p45_exception;
343 END IF;
344
345
346 hr_utility.set_location(' g_start_date: '||g_start_date, 205);
347 hr_utility.set_location(' g_end_date: '||g_end_date, 205);
348 hr_utility.set_location('Leaving '||l_procedure_name, 215);
349
350 EXCEPTION
351 WHEN l_p45_exception THEN
352 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1223);
353 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','P46 part3 XML Process errors out.');
354 WHEN Others THEN
355 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1223);
356 END range_code;
357 -----------------------------------------------------------------------
358 -- ASSIGNMENT_ACTION_CODE
359 -----------------------------------------------------------------------
360
361 PROCEDURE assignment_action_code(pactid in number,
362 stperson in number,
363 endperson in number,
364 chunk in number)
365 IS
366 l_assg_id per_assignments_f.assignment_id%TYPE;
367 l_person_id Number;
368 l_assignment_number per_all_assignments_f.assignment_number%type;
369 l_period_of_service_id per_all_assignments_f.period_of_service_id%type;
370
371 l_prev_person_id Number := 0;
372 l_prev_period_of_service_id per_all_assignments_f.period_of_service_id%type := 0;
373
374 l_start_date VARCHAR2(50);
375 l_end_date VARCHAR2(50);
376 l_select_str VARCHAR2(3000);
377 lockingactid NUMBER;
378 l_valid_assg boolean := False;
379 l_file_type pay_element_entry_values_f.screen_entry_value%TYPE;
380 l_submitted pay_element_entry_values_f.screen_entry_value%TYPE;
381 l_element_name varchar2(50);
382 TYPE asg_ref IS REF CURSOR;
383 csr_get_asg asg_ref;
384
385 -- Cursor to check already archived assignment
386 /*CURSOR csr_already_archived (p_assg_id number) IS
387 SELECT nvl(peev.screen_entry_value,'N')
388 FROM pay_element_types_f pet,
389 pay_input_values_f piv,
390 pay_element_entries_f pee,
391 pay_element_entry_values_f peev,
392 per_all_assignments_f paa
393 WHERE pet.element_name='IE P45P3_P46 Information'
394 AND piv.name='P45P3 Or P46 Processed'
395 AND pet.element_type_id=piv.element_type_id
396 AND paa.assignment_id=p_assg_id
397 AND pee.element_type_id=pet.element_type_id
398 AND pee.assignment_id=paa.assignment_id
399 AND pee.element_entry_id=peev.element_entry_id
400 AND piv.input_value_id=peev.input_value_id
401 --AND peev.effective_start_date between g_start_date and g_end_date
402 --AND pee.effective_start_date between g_start_date and g_end_date
403 Order by paa.assignment_id; */
404 --FOR UPDATE OF screen_entry_value;
405
406 -- Cursor to check already archived assignment
407 CURSOR csr_already_archived (p_assg_id number, p_bg_id in number)
408 IS
409 select 'Y'
410 FROM
411 pay_payroll_actions ppa,
412 pay_assignment_actions paa,
413 PAY_ACTION_INFORMATION pai
414 WHERE
415 paa.payroll_action_id = ppa.payroll_action_id
416 AND ppa.action_type = 'X'
417 AND ppa.business_group_id = p_bg_id
418 AND ppa.action_status = 'C'
419 AND ppa.report_type = 'IE_P45P3_P46'
420 AND ppa.report_qualifier = 'IE'
421 and pai.action_context_id = paa.assignment_action_id
422 and pai.action_information_category = 'IE_P45P3_P46_DETAILS'
423 AND paa.assignment_id = p_assg_id
424 AND pai.action_context_type = 'AAP';
425
426 --cursor to check the file type(P45/P46) of the assignment
427 CURSOR csr_scr_ent_val(c_asg_id IN Number,c_element_name in varchar2) IS
428 SELECT peev.screen_entry_value P45P3_P46_Processed
429 FROM pay_element_types_f pet,
430 pay_input_values_f piv,
431 pay_element_entries_f pee,
432 pay_element_entry_values_f peev
433 WHERE pet.element_name = 'IE P45P3_P46 Information'
434 and piv.name =c_element_name
435 and pet.legislation_code = 'IE'
436 and piv.element_type_id=pet.element_type_id
437 and pee.element_type_id=pet.element_type_id
438 and pee.assignment_id =c_asg_id
439 --and pee.effective_start_date between g_start_date and g_end_date
440 and peev.element_entry_id=pee.element_entry_id
441 and peev.input_value_id=piv.input_value_id;
442 --and peev.effective_start_date between g_start_date and g_end_date
443
444 --and peev.screen_entry_value=g_file_type_v;
445 --to check wheteher the employee has an assg with the paye ref before
446 /* CURSOR csr_ass_check (c_assignment_id IN Number, p_person_id IN Number) IS
447 select 'X'
448 from per_all_assignments_f paa
449 where paa.assignment_id=c_assignment_id
450 and not exists
451 (select 1
452 from per_all_assignments_f paaf, pay_all_payrolls_f papf, hr_soft_coding_keyflex scl
453 where paaf.person_id=paa.person_id
454 and paaf.effective_start_date < paa.effective_start_date
455 and paaf.effective_end_date > g_end_date
456 and paaf.organization_id=paa.organization_id
457 and paaf.person_id = p_person_id
458 AND paaf.payroll_id = papf.payroll_id
459 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
460 AND scl.segment4 = to_char(g_employer_id)
461 ); */
462
463 l_ass_check varchar2(1);
464 l_csr_already_archived pay_element_entry_values_f.screen_entry_value%TYPE := 'N';
465 BEGIN
466 hr_utility.set_location('Entering pay_ie_p45p3_p46_pkg.assignment_action_code',220);
467
468 -- Get all the parameters
469 get_all_parameters(pactid
470 ,g_rep_group
471 ,g_payroll_id
472 ,l_start_date
473 ,l_end_date
474 ,g_file_type
475 ,g_business_group_id
476 ,g_person_id
477 ,g_employer_id);
478
479 g_start_date := fnd_date.canonical_to_date(l_start_date);
480 g_end_date := fnd_date.canonical_to_date(l_end_date);
481 g_pact_id := pactid;
482
483 hr_utility.set_location('after get_all_parameter called',225);
484 hr_utility.set_location('report start date= '||g_start_date,300);
485
486 -- Query to fetch assignment_id.
487 /*l_select_str := 'select distinct paaf.assignment_id, ppf.person_id from
488 per_all_assignments_f paaf,
489 per_all_people_f ppf,
490 pay_all_payrolls_f papf,
491 pay_payroll_actions ppa,
492 hr_soft_coding_keyflex scl
493 where paaf.business_group_id = '|| g_business_group_id
494 ||' and paaf.effective_start_date between '||''''||g_start_date||''''||' and '
495 ||''''||g_end_date||''''
496 ||' and paaf.person_id = ppf.person_id '
497 ||' and ppf.person_id between '|| stperson || ' AND ' || endperson
498 ||g_where_clause1
499 ||' and papf.business_group_id = paaf.business_group_id '
500 ||' and ppa.payroll_action_id = '||pactid
501 ||' and papf.payroll_id = paaf.payroll_id '
502 ||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
503 ||' and scl.segment4 = to_char('||g_employer_id||') '
504 ||g_where_clause
505 ||' Order by paaf.assignment_id '; */
506
507 -- Query to fetch assignment_id.
508 l_select_str := 'select distinct paaf.assignment_id asgid, ppf.person_id perid, pps.period_of_service_id, paaf.assignment_number
509 from
510 per_all_assignments_f paaf,
511 per_all_people_f ppf,
512 pay_all_payrolls_f papf,
513 pay_payroll_actions ppa,
514 hr_soft_coding_keyflex scl,
515 per_periods_of_service pps
516 where paaf.business_group_id = '|| g_business_group_id
517 ||' and paaf.effective_start_date between '||''''||g_start_date||''''||' and '
518 ||''''||g_end_date||''''
519 ||' and paaf.person_id = ppf.person_id '
520 ||' and ppf.person_id between '|| stperson || ' AND ' || endperson
521 ||g_where_clause1
522 ||' and papf.business_group_id = paaf.business_group_id '
523 ||' and ppa.payroll_action_id = '||pactid
524 ||' and papf.payroll_id = paaf.payroll_id '
525 ||' and papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id '
526 ||' and scl.segment4 = to_char('||g_employer_id||') '
527 ||g_where_clause
528 ||' and pps.person_id = ppf.person_id '
529 ||' and pps.business_group_id = paaf.business_group_id '
530 ||' and pps.period_of_service_id = paaf.period_of_service_id '
531 ||' and pps.date_start between '||''''||g_start_date||''''||' and '||''''||g_end_date||''''
532 ||' and paaf.effective_start_date between pps.date_start and '||''''||g_end_date||''''
533 ||' Order by ppf.person_id, paaf.assignment_number, paaf.assignment_id ';
534
535 hr_utility.set_location('l_select_str'||l_select_str,225);
536
537 /*
538 OPEN csr_get_asg FOR l_select_str; -- ref cursor
539 LOOP
540 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',230);
541 FETCH csr_get_asg INTO l_assg_id, l_person_id;
542 EXIT WHEN csr_get_asg%NOTFOUND;
543 hr_utility.set_location(' Inside ass action code,l_assg_id: '||l_assg_id,235);
544 -- Check if the report is already run for this assignment, dont run it again
545 l_csr_already_archived := NULL;
546 l_ass_check :=NULL;
547 l_valid_assg := False;
548 OPEN csr_already_archived(l_assg_id);
549 FETCH csr_already_archived INTO l_csr_already_archived;
550 -- EXIT WHEN csr_already_archived%NOTFOUND;
551 CLOSE csr_already_archived;
552 hr_utility.set_location(' l_csr_already_archived: '||l_csr_already_archived,240);
553 IF l_csr_already_archived ='N' THEN -- means report has not run previously
554 OPEN csr_ass_check(l_assg_id, l_person_id);
555 FETCH csr_ass_check into l_ass_check;
556 CLOSE csr_ass_check;
557 hr_utility.set_location('any assg before:'||l_ass_check, 250);
558 IF (l_ass_check='X') THEN --THERE R NO ASSG BEFORE
559 l_element_name :='Already Submitted';
560 OPEN csr_scr_ent_val(l_assg_id,l_element_name);
561 --hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',250);
562 FETCH csr_scr_ent_val INTO l_submitted;
563 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor'||l_submitted,250);
564 CLOSE csr_scr_ent_val;
565 IF (l_submitted='N') then --not submitted online
566 l_element_name :='P45P3 Or P46';
567 OPEN csr_scr_ent_val(l_assg_id,l_element_name);
568 FETCH csr_scr_ent_val INTO l_file_type;
569 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor'||l_file_type,245);
570 CLOSE csr_scr_ent_val;
571 IF (g_file_type='P46' AND l_file_type='Y') THEN --file type is p46
572 l_valid_assg := TRUE;
573 END IF; --file type is p46
574 IF (g_file_type='P45P3' AND l_file_type='N') THEN --file type is p45p3
575 l_valid_assg := TRUE;
576 END IF; --file type is p45p3
577 END IF; --not submitted online
578 END IF; --THERE R NO ASSG BEFORE
579 IF (l_valid_assg = TRUE) THEN
580 hr_utility.set_location('inserting into ASSIGNMENT_ACTIONS', 255);
581 SELECT pay_assignment_actions_s.nextval
582 INTO lockingactid
583 FROM dual;
584 hr_utility.set_location('assignment_action_code, the assignment id finally picked up: '||l_assg_id, 1083);
585 -- Insert assignment into PAY_ASSIGNMENT_ACTIONS TABLE
586 hr_nonrun_asact.insact(lockingactid => lockingactid
587 ,assignid => l_assg_id
588 ,pactid => pactid
589 ,chunk => chunk
590 ,greid => NULL);
591 END IF; -- processing of assignment
592 -- UPDATE pay_element_entry_values_f SET screen_entry_value='Y' WHERE CURRENT OF csr_already_archived;
593 --ELSE
594 -- hr_utility.set_location('assignment_action_code, the assignment id finally',980);
595 END IF; -- Already processed assignment check
596
597 END LOOP;-- ref cursor
598 */
599 l_prev_person_id := 0;
600 l_prev_period_of_service_id := 0;
601
602 OPEN csr_get_asg FOR l_select_str; -- ref cursor
603 LOOP
604 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',230);
605 FETCH csr_get_asg INTO l_assg_id, l_person_id, l_period_of_service_id, l_assignment_number;
606 EXIT WHEN csr_get_asg%NOTFOUND;
607
608 hr_utility.set_location(' l_assg_id: '||l_assg_id,235);
609 hr_utility.set_location(' l_person_id: '||l_person_id,235);
610 hr_utility.set_location(' l_period_of_service_id: '||l_period_of_service_id,235);
611 hr_utility.set_location(' l_assignment_number: '||l_assignment_number,235);
612 hr_utility.set_location(' l_prev_person_id: '||l_prev_person_id,235);
613 hr_utility.set_location(' l_prev_period_of_service_id: '||l_prev_period_of_service_id,235);
614
615 --IF l_prev_person_id <> l_person_id and l_prev_period_of_service_id <> l_period_of_service_id THEN
616 IF l_prev_period_of_service_id <> l_period_of_service_id THEN
617 l_valid_assg := False;
618 l_csr_already_archived := 'N';
619 l_submitted := 'Y';
620 l_file_type := NULL;
621
622 OPEN csr_already_archived(l_assg_id,g_business_group_id);
623 FETCH csr_already_archived INTO l_csr_already_archived;
624 CLOSE csr_already_archived;
625
626 hr_utility.set_location(' l_csr_already_archived: '||l_csr_already_archived,240);
627 --
628 IF l_csr_already_archived ='N' THEN -- means report has not run previously
629
630 hr_utility.set_location('any assg before:'||l_ass_check, 250);
631 l_element_name :='Already Submitted';
632
633 OPEN csr_scr_ent_val(l_assg_id,l_element_name);
634 --hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',250);
635 FETCH csr_scr_ent_val INTO l_submitted;
636 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor'||l_submitted,250);
637 CLOSE csr_scr_ent_val;
638 --
639 hr_utility.set_location('any assg before:'||l_ass_check, 250);
640 IF (l_submitted='N') then --not submitted online
641 l_element_name :='P45P3 Or P46';
642 OPEN csr_scr_ent_val(l_assg_id,l_element_name);
643 FETCH csr_scr_ent_val INTO l_file_type;
644 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor'||l_file_type,245);
645 CLOSE csr_scr_ent_val;
646
647 IF (g_file_type='P46' AND l_file_type='Y') THEN --file type is p46
648 l_valid_assg := TRUE;
649 ELSIF (g_file_type='P45P3' AND l_file_type='N') THEN --file type is p45p3
650 l_valid_assg := TRUE;
651 END IF;
652 END IF; --not submitted online
653 --
654
655 IF (l_valid_assg = TRUE) THEN
656 hr_utility.set_location('inserting into ASSIGNMENT_ACTIONS', 255);
657 SELECT pay_assignment_actions_s.nextval
658 INTO lockingactid
659 FROM dual;
660
661 hr_utility.set_location('assignment_action_code, the assignment id finally picked up: '||l_assg_id, 1083);
662 -- Insert assignment into PAY_ASSIGNMENT_ACTIONS TABLE
663 hr_nonrun_asact.insact(lockingactid => lockingactid
664 ,assignid => l_assg_id
665 ,pactid => pactid
666 ,chunk => chunk
667 ,greid => NULL);
668 END IF; -- processing of assignment
669 --
670 END IF; -- Already processed assignment check
671 END IF; -- If a person with more assignments having same period of service then pick only once.
672 l_prev_person_id := l_person_id;
673 l_prev_period_of_service_id := l_period_of_service_id;
674 --
675 END LOOP;-- ref cursor
676
677 END assignment_action_code;
678 -----------------------------------------------------------------------
679 -- ARCHIVE_INIT
680 -----------------------------------------------------------------------
681
682 PROCEDURE archive_init(p_payroll_action_id IN NUMBER)
683 IS
684 l_start_date VARCHAR2(50);
685 l_end_date VARCHAR2(50);
686
687
688 CURSOR csr_archive_effective_date(pactid NUMBER) IS
689 SELECT effective_date
690 FROM pay_payroll_actions
691 WHERE payroll_action_id = pactid;
692
693 /*
694 CURSOR csr_employer_details(c_org_id hr_organization_information.organization_id%type
695 ,c_bg_id hr_organization_units.business_group_id%type) IS
696 select hoi.org_information2 regst_no
697 ,hou.name employer_name
698 ,hoi.org_information3 trade_name
699 ,hla.address_line_1 addr1
700 ,hla.address_line_2 addr2
701 ,hla.address_line_3 addr3
702 ,hoi.org_information4 contact_name
703 ,hla.telephone_number_1 telphone_no
704 ,hla.telephone_number_2 fax
705 from hr_organization_units hou
706 ,hr_organization_information hoi
707 ,hr_locations_all hla
708 where hoi.org_information_context='IE_EMPLOYER_INFO'
709 and hoi.organization_id=c_org_id
710 and hoi.organization_id=hou.organization_id
711 and hou.business_group_id= c_bg_id
712 and hou.location_id=hla.location_id(+);
713
714 l_employer_details csr_employer_details%rowtype;
715 l_action_info_id NUMBER;
716 l_ovn NUMBER;
717 l_regst_no hr_organization_information.org_information2%type;
718 l_trade_name hr_organization_information.org_information3%type;
719 l_employer_name hr_organization_units.name%type;
720 l_addr1 hr_locations_all.address_line_1%type;
721 l_addr2 hr_locations_all.address_line_2%type;
722 l_addr3 hr_locations_all.address_line_3%type;
723 l_contact_name hr_organization_information.org_information4%type;
724 l_telphone_no hr_locations_all.telephone_number_1%type;
725 l_fax_no hr_locations_all.telephone_number_2%type;
726 */
727 BEGIN
728 hr_utility.set_location('Entering: pay_ie_p45p3_p46_pkg.archive_init: ',940);
729
730 OPEN csr_archive_effective_date(p_payroll_action_id);
731 FETCH csr_archive_effective_date
732 INTO g_archive_effective_date;
733 CLOSE csr_archive_effective_date;
734
735 get_all_parameters(p_payroll_action_id
736 ,g_rep_group
737 ,g_payroll_id
738 ,l_start_date
739 ,l_end_date
740 ,g_file_type
741 ,g_business_group_id
742 ,g_person_id
743 ,g_employer_id);
744
745 g_start_date := fnd_date.canonical_to_date(l_start_date);
746 g_end_date := fnd_date.canonical_to_date(l_end_date);
747
748 /*
749 OPEN csr_employer_details(g_employer_id, g_business_group_id);
750 FETCH csr_employer_details INTO l_employer_details;
751 CLOSE csr_employer_details;
752 l_regst_no := l_employer_details.regst_no;
753 l_trade_name:= l_employer_details.trade_name;
754 l_employer_name:= l_employer_details.employer_name;
755 l_addr1:= l_employer_details.addr1;
756 l_addr2:= l_employer_details.addr2;
757 l_addr3:= l_employer_details.addr3;
758 l_contact_name := l_employer_details.contact_name;
759 l_telphone_no:= l_employer_details.telphone_no;
760 l_fax_no:= l_employer_details.fax;
761
762 pay_action_information_api.create_action_information
763 ( p_action_information_id => l_action_info_id
764 ,p_action_context_id => p_payroll_action_id
765 ,p_action_context_type => 'PA'
766 ,p_object_version_number => l_ovn
767 ,p_effective_date => g_end_date
768 ,p_source_id => NULL
769 ,p_source_text => NULL
770 ,p_action_information_category => 'IE P45P3 P46 EMPLOYER'
771 ,p_action_information6 => l_regst_no
772 ,p_action_information7 => l_employer_name
773 ,p_action_information8 => l_trade_name
774 ,p_action_information9 => l_addr1
775 ,p_action_information10 => l_addr2
776 ,p_action_information11 => l_addr3
777 ,p_action_information12 => l_contact_name
778 ,p_action_information13 => l_telphone_no
779 ,p_action_information14 => l_fax_no);
780 */
781 hr_utility.set_location(' g_start_date: '||g_start_date, 945);
782 hr_utility.set_location(' g_end_date: '||g_end_date, 950);
783 hr_utility.set_location(' pay_ie_p45p3_p46_pkg.archive_init', 955);
784
785 EXCEPTION
786 WHEN Others THEN
787 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1211);
788
789 END archive_init;
790 -----------------------------------------------------------------------
791 -- ARCHIVE_DATA
792 -----------------------------------------------------------------------
793 PROCEDURE archive_data(p_assactid in number,
794 p_effective_date in date)
795 IS
796 CURSOR csr_get_assg_detail(passactid IN NUMBER)IS
797 SELECT piv.name input_name,peev.screen_entry_value input_value
798 FROM pay_element_types_f pet,
799 pay_input_values_f piv,
800 pay_element_entries_f pee,
801 pay_element_entry_values_f peev,
802 per_all_assignments_f paa,
803 pay_assignment_actions paac
804 WHERE pet.element_name in ('IE P45P3_P46 Information','IE P45 Information')
805 --AND piv.name='P45P3 Or P46 Processed'
806 AND pet.element_type_id=piv.element_type_id
807 AND paa.assignment_id=paac.assignment_id
808 AND pee.element_type_id=pet.element_type_id
809 AND pee.assignment_id=paa.assignment_id
810 AND pee.element_entry_id=peev.element_entry_id
811 AND piv.input_value_id=peev.input_value_id
812 AND paac.assignment_action_id=passactid;
813 --AND peev.effective_start_date between g_start_date and g_end_date
814 --AND peev.effective_end_date > g_end_date;
815
816 CURSOR csr_person_details(passactid IN NUMBER) IS
817 SELECT ppf.national_identifier ppsn
818 ,ppf.first_name firstname
819 ,ppf.last_name surname
820 ,ppf.effective_start_date emp_start_date
821 ,pa.address_line1 addr1
822 ,pa.address_line2 addr2
823 ,pa.address_line3 addr3
824 ,paa.assignment_number unit
825 --,pap.period_type frequency
826 ,decode(pap.period_type,'Lunar Month','W',decode(instr(pap.period_type,'Week'),0,'M','W')) frequency
827 -- Bug# 7005067
828 ,NULL addr4
829 ,pa.TOWN_OR_CITY City
830 --,substr(pa.DERIVED_LOCALE,1,instr(pa.DERIVED_LOCALE,',',-1)-1) COUNTY
831 ,flv.meaning COUNTY
832 ,pc.NAME Country_Name
833 ,pa.country
834 -- Bug# 7005067
835 FROM per_all_people_f ppf,
836 per_addresses pa,
837 per_all_assignments_f paa,
838 pay_all_payrolls_f pap,
839 pay_assignment_actions ppaa,
840 pa_country_v pc, -- Bug# 7005067
841 fnd_lookup_values flv -- Bug# 7005067
842 WHERE ppaa.assignment_action_id=passactid
843 AND ppaa.assignment_id=paa.assignment_id
844 AND paa.person_id=ppf.person_id
845 AND ppf.person_id=pa.person_id(+)
846 AND pap.payroll_id=paa.payroll_id
847 AND pap.business_group_id=paa.business_group_id
848 AND ppf.business_group_id=paa.business_group_id
849 AND pa.country = pc.country_code (+) -- Bug# 7005067
850 AND pa.style(+) LIKE 'IE%' --6817160
851 AND pa.primary_flag(+) = 'Y'
852 AND flv.lookup_type(+) = 'IE_COUNTY' -- Bug# 7005067
853 AND flv.language(+) = 'US' -- Bug# 7005067
854 AND flv.lookup_code(+) = pa.region_1 -- Bug# 7005067
855 AND ppf.effective_start_date between pa.date_from(+) and nvl(pa.date_to, p_effective_date)
856 AND paa.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
857 AND paa.effective_start_date = (select min(paa1.effective_start_date)
858 from per_all_assignments_f paa1
859 where paa1.assignment_id = paa.assignment_id
860 and paa1.effective_start_date between g_start_date and g_end_date );
861
862 TYPE r_get_assg_detail IS RECORD (i_name pay_input_values_f.name%type,
863 i_value pay_element_entry_values_f.screen_entry_value%type);
864 TYPE t_get_assg_detail IS TABLE OF r_get_assg_detail INDEX BY Binary_Integer;
865 /*
866 CURSOR csr_archive_processed(passactid IN NUMBER) IS
867 SELECT peev.screen_entry_value
868 from pay_element_types_f pet,
869 pay_input_values_f piv,
870 pay_element_entries_f pee,
871 pay_element_entry_values_f peev,
872 per_all_assignments_f paa,
873 pay_assignment_actions paac
874 WHERE pet.element_name = 'IE P45P3_P46 Information'
875 AND piv.name='P45P3 Or P46 Processed'
876 AND pet.element_type_id=piv.element_type_id
877 AND paa.assignment_id=paac.assignment_id
878 AND pee.element_type_id=pet.element_type_id
879 AND pee.assignment_id=paa.assignment_id
880 AND pee.element_entry_id=peev.element_entry_id
881 AND piv.input_value_id=peev.input_value_id
882 AND paac.assignment_action_id=passactid
883 FOR UPDATE OF screen_entry_value;
884 */
885
886 l_per_emp_start_date varchar2(50);
887
888 l_archive_processed pay_element_entry_values_f.screen_entry_value%type;
889 l_get_assg_detail t_get_assg_detail;
890 l_person_details csr_person_details%rowtype;
891
892 i number ;
893 l_pay_to_date pay_element_entry_values_f.screen_entry_value%type;
894 l_tax_to_date pay_element_entry_values_f.screen_entry_value%type;
895 l_pay_employment pay_element_entry_values_f.screen_entry_value%type;
896 l_tax_employment pay_element_entry_values_f.screen_entry_value%type;
897 l_paye_employer pay_element_entry_values_f.screen_entry_value%type;
898 l_emp_start_date pay_element_entry_values_f.screen_entry_value%type;
899 l_emp_end_date pay_element_entry_values_f.screen_entry_value%type;
900 l_action_info_id NUMBER(15);
901 l_ovn NUMBER;
902 l_refund varchar2(10);
903 --bug 13359530
904 l_usc_pay_to_date pay_element_entry_values_f.screen_entry_value%type;
905 l_usc_to_date pay_element_entry_values_f.screen_entry_value%type;
906 l_usc_pay_employment pay_element_entry_values_f.screen_entry_value%type;
907 l_usc_employment pay_element_entry_values_f.screen_entry_value%type;
908 l_refund_usc varchar2(10);
909
910 -- Bug# 7005067
911 Type tab_address is table of per_addresses.ADDRESS_LINE1%type index by binary_integer;
912 pl_address tab_address;
913 pl_address_final tab_address;
914 k NUMBER(3) := 0;
915 -- Bug# 7005067
916
917 BEGIN
918 hr_utility.set_location(' Entering pay_ie_p45p3_p46_pkg.ARCHIVE_CODE: ',1100);
919 hr_utility.set_location('g_pact_id '||TO_CHAR(g_pact_id),1105);
920 hr_utility.set_location('p_assignment_action_id '||TO_CHAR(p_assactid),1110);
921
922 OPEN csr_person_details(p_assactid);
923 FETCH csr_person_details into l_person_details;
924 CLOSE csr_person_details;
925
926 -- Bug# 7005067
927 hr_utility.set_location(' Before deleting the PL table pl_address. ',1100);
928 pl_address.delete;
929
930 hr_utility.set_location(' Initializing the PL table pl_address. ',1100);
931 pl_address(1) := l_person_details.addr1;
932 pl_address(2) := l_person_details.addr2;
933 pl_address(3) := l_person_details.addr3;
934 pl_address(4) := l_person_details.City;
935 pl_address(5) := l_person_details.COUNTY;
936 pl_address(6) := l_person_details.Country_Name;
937
938 hr_utility.set_location(' pl_address.COUNT: '||pl_address.COUNT,1100);
939
940 hr_utility.set_location(' pl_address(1): '||pl_address(1),1100);
941 hr_utility.set_location(' pl_address(2): '||pl_address(2),1100);
942 hr_utility.set_location(' pl_address(3): '||pl_address(3),1100);
943 hr_utility.set_location(' pl_address(4): '||pl_address(4),1100);
944 hr_utility.set_location(' pl_address(5): '||pl_address(5),1100);
945 hr_utility.set_location(' pl_address(6): '||pl_address(6),1100);
946
947 hr_utility.set_location(' Before deleting the PL table pl_address_final. ',1100);
948 pl_address_final.delete;
949 hr_utility.set_location(' Initializing the PL table pl_address_final. ',1100);
950
951 FOR j in 1..pl_address.LAST
952 LOOP
953 IF pl_address(j) IS NOT NULL THEN
954 k:=k+1;
955 pl_address_final(k) := pl_address(j);
956 hr_utility.set_location('pl_address_final'||k||'--'||pl_address_final(k),1100);
957 END IF;
958 END LOOP;
959
960 hr_utility.set_location(' Re Initializing the record l_person_details. ',1100);
961
962
963
964 hr_utility.set_location(' Re Initializing the cursor record l_person_details with actual values. ',1100);
965 hr_utility.set_location(' pl_address_final.COUNT: '||pl_address_final.COUNT,1100);
966
967 IF pl_address_final.COUNT > 0 THEN
968
969 l_person_details.addr1 := NULL;
970 l_person_details.addr2 := NULL;
971 l_person_details.addr3 := NULL;
972 l_person_details.addr4 := NULL;
973
974 FOR l in 1..pl_address_final.LAST
975 LOOP
976 hr_utility.set_location(' Inside the loop of PL table pl_address_final',1100);
977 BEGIN
978 IF l = 1 THEN
979 hr_utility.set_location(' l_person_details.addr1 ',1100);
980 l_person_details.addr1 := pl_address_final(1);
981 hr_utility.set_location(' l_person_details.addr1 ',1101);
982 END IF;
983 --
984 IF l = 2 THEN
985 hr_utility.set_location(' l_person_details.addr2 ',1102);
986 l_person_details.addr2 := pl_address_final(2);
987 hr_utility.set_location(' l_person_details.addr2 ',1103);
988 END IF;
989 --
990 IF l = 3 THEN
991 hr_utility.set_location(' l_person_details.addr3 ',1104);
992 l_person_details.addr3 := pl_address_final(3);
993 hr_utility.set_location(' l_person_details.addr3 ',1105);
994 END IF;
995 --
996 IF l = 4 THEN
997 hr_utility.set_location(' l_person_details.addr4 ',1106);
998 l_person_details.addr4 := pl_address_final(4);
999 hr_utility.set_location(' l_person_details.addr4 ',1107);
1000 END IF;
1001 EXCEPTION
1002 WHEN NO_DATA_FOUND THEN
1003 NULL;
1004 END;
1005 END LOOP;
1006 END IF;
1007 hr_utility.set_location(' After Re Initializing the cursor record l_person_details with actual values. ',1100);
1008 -- Bug# 7005067
1009
1010 l_per_emp_start_date := fnd_date.date_to_canonical(l_person_details.emp_start_date);
1011
1012 i:=1;
1013 l_refund :='false';
1014 l_refund_usc :='false'; -- BUG 13359530
1015 OPEN csr_get_assg_detail(p_assactid);
1016 LOOP
1017 hr_utility.set_location(' Inside ass action code, inside loop for ref cursor',230);
1018 FETCH csr_get_assg_detail INTO l_get_assg_detail(i).i_name,l_get_assg_detail(i).i_value;
1019 i:=i+1;
1020 EXIT WHEN csr_get_assg_detail%notfound;
1021 END LOOP;
1022 CLOSE csr_get_assg_detail;
1023
1024 FOR i IN l_get_assg_detail.first..l_get_assg_detail.last
1025 LOOP
1026 CASE WHEN l_get_assg_detail(i).i_name='Pay To Date' THEN
1027 l_pay_to_date:=l_get_assg_detail(i).i_value;
1028 WHEN l_get_assg_detail(i).i_name='Tax Deducted To Date' THEN
1029 l_tax_to_date:=l_get_assg_detail(i).i_value;
1030 WHEN l_get_assg_detail(i).i_name='Pay This Employment' THEN
1031 l_pay_employment:=l_get_assg_detail(i).i_value;
1032 WHEN l_get_assg_detail(i).i_name='Tax This Employment' THEN
1033 IF l_get_assg_detail(i).i_value<0 THEN
1034 l_refund:='true';
1035 END IF;
1036 l_tax_employment:=abs(l_get_assg_detail(i).i_value);
1037 WHEN l_get_assg_detail(i).i_name='PAYE Previous Employer' THEN
1038 l_paye_employer:=l_get_assg_detail(i).i_value;
1039 WHEN l_get_assg_detail(i).i_name='Previous Employment Start Date' THEN
1040 l_emp_start_date:=l_get_assg_detail(i).i_value;
1041 WHEN l_get_assg_detail(i).i_name='Previous Employment End Date'THEN
1042 l_emp_end_date:=l_get_assg_detail(i).i_value;
1043 WHEN l_get_assg_detail(i).i_name='USC Pay To Date' THEN -- BUG 13359530
1044 l_usc_pay_to_date:=l_get_assg_detail(i).i_value;
1045 WHEN l_get_assg_detail(i).i_name='USC Tax To Date' THEN
1046 l_usc_to_date:=l_get_assg_detail(i).i_value;
1047 WHEN l_get_assg_detail(i).i_name='USC Pay This Employment' THEN
1048 l_usc_pay_employment:=l_get_assg_detail(i).i_value;
1049 WHEN l_get_assg_detail(i).i_name='USC Tax This Employment' THEN
1050 IF l_get_assg_detail(i).i_value<0 THEN
1051 l_refund_usc:='true';
1052 END IF;
1053 l_usc_employment:=abs(l_get_assg_detail(i).i_value);
1054 ELSE
1055 null;
1056 END CASE;
1057 END LOOP;
1058 hr_utility.set_location('archive data',1200);
1059 --
1060 -- archive the details
1061 IF l_person_details.ppsn IS NOT NULL
1062 AND l_person_details.surname IS NOT NULL
1063 AND l_person_details.firstname IS NOT NULL
1064 AND l_person_details.emp_start_date IS NOT NULL
1065 AND (g_file_type = 'P45P3' AND l_emp_end_date IS NOT NULL AND l_paye_employer IS NOT NULL
1066 OR (g_file_type = 'P46' AND l_person_details.addr1 IS NOT NULL
1067 AND l_person_details.addr2 IS NOT NULL
1068 )
1069 )
1070 THEN
1071 l_errflag := 'N';
1072 pay_action_information_api.create_action_information (
1073 p_action_information_id => l_action_info_id
1074 , p_action_context_id => p_assactid
1075 , p_action_context_type => 'AAP'
1076 , p_object_version_number => l_ovn
1077 , p_effective_date => g_archive_effective_date
1078 , p_source_id => NULL
1079 , p_source_text => NULL
1080 , p_action_information_category => 'IE_P45P3_P46_DETAILS'
1081 , p_action_information6 => g_file_type
1082 , p_action_information7 => l_person_details.ppsn
1083 , p_action_information8 => l_person_details.surname
1084 , p_action_information9 => l_person_details.firstname
1085 , p_action_information10 => l_person_details.addr1
1086 , p_action_information11 => l_person_details.addr2
1087 , p_action_information12 => l_person_details.addr3
1088 , p_action_information13 => l_person_details.addr4
1089 , p_action_information14 => l_per_emp_start_date --l_person_details.emp_start_date
1090 , p_action_information15 => l_person_details.unit
1091 , p_action_information16 => l_person_details.frequency
1092 , p_action_information17 => l_emp_start_date
1093 , p_action_information18 => l_emp_end_date
1094 , p_action_information19 => l_pay_to_date
1095 , p_action_information20 => l_tax_to_date
1096 , p_action_information21 => l_pay_employment
1097 , p_action_information22 => l_tax_employment
1098 , p_action_information23 => l_refund
1099 , p_action_information24 => l_paye_employer
1100 , p_action_information25 => l_usc_pay_to_date
1101 , p_action_information26 => l_usc_to_date
1102 , p_action_information27 => l_usc_pay_employment
1103 , p_action_information28 => l_usc_employment
1104 , p_action_information29 => l_refund_usc
1105 );
1106
1107 hr_utility.set_location('after archive data',1200);
1108 ELSIF l_person_details.ppsn IS NULL THEN
1109 Fnd_file.put_line(FND_FILE.LOG,'Employee PPSN number is missing. Assignment Number: '||l_person_details.unit);
1110 l_errflag := 'Y';
1111 ELSIF l_person_details.surname IS NULL THEN
1112 Fnd_file.put_line(FND_FILE.LOG,'Employee Surname is missing. Assignment Number: '||l_person_details.unit);
1113 l_errflag := 'Y';
1114 ELSIF l_person_details.firstname IS NULL THEN
1115 Fnd_file.put_line(FND_FILE.LOG,'Employee Firstname is missing. Assignment Number: '||l_person_details.unit);
1116 l_errflag := 'Y';
1117 ELSIF l_person_details.emp_start_date IS NULL THEN
1118 Fnd_file.put_line(FND_FILE.LOG,'Employee New Employment commencement date is missing. Assignment Number: '||l_person_details.unit);
1119 l_errflag := 'Y';
1120 ELSIF g_file_type = 'P46' AND l_person_details.addr1 IS NULL THEN
1121 Fnd_file.put_line(FND_FILE.LOG,'Employee address line one is missing. Assignment Number: '||l_person_details.unit);
1122 l_errflag := 'Y';
1123 ELSIF g_file_type = 'P46' AND l_person_details.addr2 IS NULL THEN
1124 Fnd_file.put_line(FND_FILE.LOG,'Employee address line two is missing. Assignment Number: '||l_person_details.unit);
1125 l_errflag := 'Y';
1126 ELSIF g_file_type = 'P45P3' AND l_emp_end_date IS NULL THEN
1127 Fnd_file.put_line(FND_FILE.LOG,'Employee Previous employment leaving date is missing. Assignment Number: '||l_person_details.unit);
1128 l_errflag := 'Y';
1129 ELSIF g_file_type = 'P45P3' AND l_paye_employer IS NULL THEN
1130 Fnd_file.put_line(FND_FILE.LOG,'Employee Previous employment PAYE Ref. Number is missing. Assignment Number: '||l_person_details.unit);
1131 l_errflag := 'Y';
1132 END IF;
1133
1134 If l_errflag = 'Y' THEN
1135 Fnd_file.put_line(FND_FILE.LOG,'P45P3 Process Failed. Some mandatory parametors are missing. Please check the whole log for details.');
1136 raise l_p45_exception;
1137 END IF;
1138
1139 /*
1140 OPEN csr_archive_processed(p_assactid);
1141 fetch csr_archive_processed into l_archive_processed;
1142 UPDATE pay_element_entry_values_f set screen_entry_value='Y'
1143 WHERE CURRENT OF csr_archive_processed;
1144 close csr_archive_processed;
1145 */ --
1146 hr_utility.set_location('Leaving archive ',20);
1147 --update pay_element_entries_f set screen_entry_value='Y' where
1148 Exception
1149 WHEN l_p45_exception THEN
1150 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1223);
1151 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','P46 part3 XML Process errors out');
1152 END archive_data;
1153 -----------------------------------------------------------------------
1154 --C2B
1155 -----------------------------------------------------------------------
1156 FUNCTION c2b( c IN CLOB ) RETURN BLOB
1157 -- typecasts CLOB to BLOB (binary conversion)
1158 IS
1159 pos PLS_INTEGER := 1;
1160 buffer RAW( 32767 );
1161 res BLOB;
1162 lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
1163 BEGIN
1164 Hr_Utility.set_location('Entering: pay_ie_p45p3_p46_pkg.c2b',260);
1165 DBMS_LOB.createTemporary( res, TRUE );
1166 DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
1167
1168
1169 LOOP
1170 buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos ) );
1171
1172 IF UTL_RAW.LENGTH( buffer ) > 0 THEN
1173 DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
1174 END IF;
1175
1176 pos := pos + 16000;
1177 EXIT WHEN pos > lob_len;
1178 END LOOP;
1179
1180 Hr_Utility.set_location('Leaving: pay_ie_p45p3_p46_pkg.c2b',265);
1181 RETURN res; -- res is OPEN here
1182 END c2b;
1183 -----------------------------------------------------------------------
1184 -- GEN_BODY_XML
1185 -----------------------------------------------------------------------
1186
1187 PROCEDURE gen_body_xml
1188 IS
1189 l_string varchar2(32767) := NULL;
1190 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
1191 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
1192
1193 CURSOR c_action_information(c_action_type VARCHAR2, c_asg_act_id NUMBER) IS
1194 SELECT
1195 action_information6 form_type,
1196 action_information7 ppsn,
1197 action_information8 surname,
1198 action_information9 firstname,
1199 action_information10 addr1,
1200 action_information11 addr2,
1201 action_information12 addr3,
1202 action_information13 addr4,
1203 action_information14 emp_start_dt,
1204 action_information15 unit,
1205 action_information16 frequency,
1206 action_information17 prv_emp_strt_dt,
1207 action_information18 prv_emp_end_dt,
1208 trim(to_char(fnd_number.canonical_to_number(nvl(action_information19,0)) ,'99999990.99')) paya,
1209 trim(to_char(fnd_number.canonical_to_number(nvl(action_information20,0)) ,'99999990.99')) taxa,
1210 trim(to_char(fnd_number.canonical_to_number(action_information21) ,'99999990.99')) payb,
1211 trim(to_char(fnd_number.canonical_to_number(action_information22) ,'99999990.99')) taxb,
1212 action_information23 refunded,
1213 action_information24 paye_regst ,
1214 trim(to_char(fnd_number.canonical_to_number(nvl(action_information25,0)) ,'99999990.99')) totalpaytodate,
1215 trim(to_char(fnd_number.canonical_to_number(nvl(action_information26,0)) ,'99999990.99')) totalusctodate,
1216 trim(to_char(fnd_number.canonical_to_number(nvl(action_information27,0)) ,'99999990.99')) totalpaythisemp,
1217 trim(to_char(fnd_number.canonical_to_number(nvl(action_information28,0)) ,'99999990.99')) totaluscthisemp,
1218 action_information29 uscrefunded
1219 FROM pay_action_information
1220 WHERE
1221 action_information_category = c_action_type
1222 AND Action_context_id = c_asg_act_id;
1223
1224 l_emp_information c_action_information%ROWTYPE;
1225 l_payroll_action_id NUMBER;
1226 l_asg_action_id NUMBER;
1227
1228 l_sur_name varchar2(1000);
1229 l_first_name varchar2(1000);
1230
1231 --7529405
1232 l_addressline1 varchar2(100);
1233 l_addressline2 varchar2(100);
1234 l_addressline3 varchar2(100);
1235 l_addressline4 varchar2(100);
1236 --7529405
1237 l_start_date VARCHAR2(30); -- bug 13359530
1238 BEGIN
1239 hr_utility.set_location(' Entering: pay_ie_p45part3_p46_pkg_test.gen_body_xml: ', 270);
1240
1241 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1242 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
1243
1244 PAY_IE_P45PART3_P46_PKG.get_parameters (
1245 p_payroll_action_id => l_payroll_action_id
1246 , p_token_name => 'START_DATE'
1247 , p_token_value => l_start_date);
1248 hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_payroll_action_id),275);
1249 hr_utility.set_location('l_asg_action_id '||TO_CHAR(l_asg_action_id),280);
1250
1251 FOR l_emp_information IN c_action_information('IE_P45P3_P46_DETAILS',l_asg_action_id)
1252 LOOP
1253 hr_utility.set_location('Inside IE P45P3 P46',285);
1254 IF( g_file_type='P45P3') THEN
1255 l_emp_information.addr1:=NULL;
1256 l_emp_information.addr2:=NULL;
1257 l_emp_information.addr3:=NULL;
1258 l_emp_information.addr4:=NULL;
1259 END IF;
1260
1261 l_sur_name := test_XML(substr(l_emp_information.surname,1,20));
1262 l_first_name := test_XML(substr(l_emp_information.firstname,1,20));
1263 --7529405
1264 l_addressline1 := test_XML(substr(l_emp_information.addr1,1,35));
1265 l_addressline2 := test_XML(substr(l_emp_information.addr2,1,35));
1266 l_addressline3 := test_XML(substr(l_emp_information.addr3,1,35));
1267 l_addressline4 := test_XML(substr(l_emp_information.addr4,1,35));
1268 --7529405
1269 l_string := l_string ||'<P45P3 formtype="'||l_emp_information.form_type||'">'||EOL;
1270 --
1271 l_string := l_string || '<Employee';
1272 l_string := l_string || ' ppsn="'||l_emp_information.ppsn||'"';
1273 l_string := l_string || ' surname="'||l_sur_name||'"';
1274 l_string := l_string || ' firstname="'||l_first_name||'"';
1275 --7529405
1276 l_string := l_string || ' addressline1="'||l_addressline1||'"';
1277 l_string := l_string || ' addressline2="'||l_addressline2||'"';
1278 l_string := l_string || ' addressline3="'||l_addressline3||'"';
1279 l_string := l_string || ' addressline4="'||l_addressline4||'"/>'||EOL ;
1280 --7529405
1281 --l_string := l_string || '</Employee>'||EOL ;
1282 --
1283 l_string := l_string || '<NewEmployment';
1284 l_string := l_string || ' datecommencement="'||to_char(fnd_date.canonical_to_date(l_emp_information.emp_start_dt),'DD/MM/YYYY')||'"';
1285 IF (to_number(to_char(to_date(l_start_date,'yyyy/mm/dd'),'yyyy')) >= 2012) THEN -- BUG 13359530
1286 l_string := l_string || ' works="'||substr(l_emp_information.unit,1,12)||'"';
1287 ELSE
1288 l_string := l_string || ' unit="'||substr(l_emp_information.unit,1,12)||'"';
1289 END IF;
1290 l_string := l_string || ' freq="'||l_emp_information.frequency||'"/>'||EOL ;
1291 --l_string := l_string || '</NewEmployment>'||EOL ;
1292 --
1293 IF l_emp_information.form_type = 'P45P3' THEN
1294 l_string := l_string || '<PrevEmployment';
1295 -- bug 13359530-1 added below if condition
1296 IF fnd_date.canonical_to_date(l_emp_information.prv_emp_strt_dt) >= to_date ('01/01' || to_char (fnd_date.canonical_to_date(l_emp_information.prv_emp_end_dt)
1297 ,'YYYY')
1298 ,'DD/MM/YYYY') THEN
1299 l_string := l_string || ' datecommencement="'||to_char(fnd_date.canonical_to_date(l_emp_information.prv_emp_strt_dt),'DD/MM/YYYY')||'"';
1300 END IF;
1301 l_string := l_string || ' datecessation="'||to_char(fnd_date.canonical_to_date(l_emp_information.prv_emp_end_dt),'DD/MM/YYYY')||'"';
1302 l_string := l_string || ' paya="'||l_emp_information.paya||'"';
1303 l_string := l_string || ' taxa="'||l_emp_information.taxa||'"';
1304 l_string := l_string || ' payb="'||l_emp_information.payb||'"';
1305 l_string := l_string || ' taxb="'||l_emp_information.taxb||'"';
1306 l_string := l_string || ' refunded="'||l_emp_information.refunded||'"';
1307 l_string := l_string || ' payeregistered="'||l_emp_information.paye_regst||'"/>'||EOL ;
1308 -- bug 13359530
1309 IF to_number(to_char(fnd_date.canonical_to_date(l_emp_information.prv_emp_end_dt),'YYYY')) >= 2012 THEN
1310 l_string := l_string || '<USC';
1311 l_string := l_string || ' totalpaytodate="'||l_emp_information.totalpaytodate||'"';
1312 l_string := l_string || ' totalusctodate="'||l_emp_information.totalusctodate||'"';
1313 l_string := l_string || ' totalpaythisemp="'||l_emp_information.totalpaythisemp||'"';
1314 l_string := l_string || ' totaluscthisemp="'||l_emp_information.totaluscthisemp||'"';
1315 l_string := l_string || ' uscrefunded="'||l_emp_information.uscrefunded||'"/>'||EOL ;
1316 END IF;
1317 END IF;
1318 -- l_string := l_string || '</PrevEmployment>'||EOL ;
1319 --
1320 -- l_string := l_string || <formtype>||l_emp_information.action_information6||'</formtype>';
1321 --
1322 l_string := l_string ||'</P45P3>'||EOL ;
1323 --
1324 END LOOP;
1325
1326 hr_utility.set_location('Before leaving gen_body_xml: length(l_string) = '||length(l_string),290);
1327 l_clob := l_clob||l_string;
1328
1329 IF l_clob IS NOT NULL THEN
1330 l_blob := c2b(l_clob);
1331 pay_core_files.write_to_magtape_lob(l_blob);
1332 END IF;
1333
1334 EXCEPTION
1335 WHEN Others THEN
1336 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1213);
1337 END gen_body_xml;
1338 -----------------------------------------------------------------------
1339 -- GEN_HEADER_XML
1340 -----------------------------------------------------------------------
1341 PROCEDURE gen_header_xml
1342 IS
1343 l_string varchar2(32767) := NULL;
1344 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
1345 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
1346
1347 l_proc VARCHAR2(100);
1348 l_buf VARCHAR2(2000);
1349
1350 CURSOR c_get_header(c_pact_id NUMBER) IS
1351 SELECT
1352 action_information6 regt_no,
1353 action_information7 emplyr_name,
1354 action_information8 trade_name,
1355 action_information9 addr1,
1356 action_information10 addr2,
1357 action_information11 addr3,
1358 action_information12 contact_name,
1359 action_information13 phone,
1360 action_information14 fax
1361 FROM pay_action_information
1362 WHERE action_context_id = c_pact_id
1363 AND action_context_type = 'PA'
1364 AND action_information_category ='IE P45P3 P46 EMPLOYER';
1365
1366 l_header c_get_header%rowtype;
1367 l_payroll_action_id number;
1368
1369 l_currency VARCHAR2(1) := 'E';
1370 l_product VARCHAR2(25):= 'ORACLE';
1371 l_formversion VARCHAR2(1) := '1';
1372 l_language VARCHAR2(1) := 'E';
1373
1374 --7529405
1375 l_er_name VARCHAR2(100);
1376 l_er_tradename VARCHAR2(100);
1377 l_er_address1 VARCHAR2(100);
1378 l_er_address2 VARCHAR2(100);
1379 l_er_address3 VARCHAR2(100);
1380 l_er_contact VARCHAR2(100);
1381 --7529405
1382 l_start_date VARCHAR2(30);
1383 BEGIN
1384 l_proc := g_package || 'gen_header_xml';
1385 hr_utility.set_location ('Entering '||l_proc,1500);
1386
1387 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1388 hr_utility.set_location('Inside pay_ie_p45p3_p46_pkg.gen_header_xml,l_payroll_action_id: '||l_payroll_action_id,300);
1389 PAY_IE_P45PART3_P46_PKG.get_parameters (
1390 p_payroll_action_id => l_payroll_action_id
1391 , p_token_name => 'START_DATE'
1392 , p_token_value => l_start_date);
1393 hr_utility.set_location('Inside pay_ie_p45p3_p46_pkg.gen_header_xml,l_payroll_action_id: '||l_start_date,300);
1394
1395 IF (to_number(to_char(to_date(l_start_date,'yyyy/mm/dd'),'yyyy')) >= 2012) THEN -- 13359530
1396 l_formversion := '2';
1397 END IF;
1398
1399 OPEN c_get_header(l_payroll_action_id);
1400 FETCH c_get_header into l_header;
1401 CLOSE c_get_header;
1402
1403 --7529405
1404 l_er_name := test_XML(substr(l_header.emplyr_name,1,30));
1405 l_er_tradename := test_XML(substr(l_header.trade_name,1,30));
1406 l_er_address1 := test_XML(substr(l_header.addr1,1,30));
1407 l_er_address2 := test_XML(substr(l_header.addr2,1,30));
1408 l_er_address3 := test_XML(substr(l_header.addr3,1,30));
1409 l_er_contact := test_XML(substr(l_header.contact_name,1,20));
1410 --7529405
1411
1412 l_string := l_string || '<P45P3File' ;
1413 IF (to_number(to_char(to_date(l_start_date,'yyyy/mm/dd'),'yyyy')) >= 2012) THEN
1414 l_string := l_string || ' xmlns="http://www.revenue.ie/schemas/P45p3/v2/"' ;
1415 END IF;
1416 l_string := l_string || ' currency="'|| l_currency ||'"';
1417 l_string := l_string || ' product="'|| l_product ||'"' ;
1418 l_string := l_string || ' formversion="'|| l_formversion||'"';
1419 l_string := l_string || ' language="'|| l_language ||'">'||EOL ;
1420
1421 l_string := l_string || '<Employer'||EOL ;
1422
1423 l_string := l_string || ' number="'||substr(l_header.regt_no,1,8)||'"';
1424 --7529405
1425 l_string := l_string || ' name="'||l_er_name||'"';
1426 l_string := l_string || ' tradename="'||l_er_tradename||'"';
1427 l_string := l_string || ' address1="'||l_er_address1||'"';
1428 l_string := l_string || ' address2="'||l_er_address2||'"';
1429 l_string := l_string || ' address3="'||l_er_address3||'"';
1430 l_string := l_string || ' contact="'||l_er_contact||'"';
1431 --7529405
1432 -- bug 13359530-1 added below if condition
1433 IF l_header.phone IS NOT NULL THEN
1434 l_string := l_string || ' phone="'||substr(l_header.phone,1,12)||'"';
1435 END IF;
1436
1437 IF l_header.fax IS NOT NULL THEN
1438 l_string := l_string || ' fax="'||substr(l_header.fax,1,12)||'"' ;
1439 END IF;
1440 l_string := l_string || '/>'||EOL ;
1441
1442 -- l_string := l_string || '</Employer>'||EOL ;
1443
1444 l_clob := l_clob||l_string;
1445 IF l_clob IS NOT NULL THEN
1446 l_blob := c2b(l_clob);
1447 pay_core_files.write_to_magtape_lob(l_blob);
1448 END IF;
1449
1450 EXCEPTION
1451 WHEN Others THEN
1452 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1214);
1453
1454 END gen_header_xml;
1455 -----------------------------------------------------------------------
1456 -- GEN_FOOTER_XML
1457 -----------------------------------------------------------------------
1458 PROCEDURE gen_footer_xml
1459 IS
1460 l_buf VARCHAR2(2000);
1461 l_proc VARCHAR2(100);
1462 begin
1463 l_proc := g_package || 'gen_footer_xml';
1464 hr_utility.set_location ('Entering '||l_proc, 1520);
1465 --
1466 --l_buf := l_buf || '<FOOTER>'||EOL ;
1467 --l_buf := l_buf || '</FOOTER>'||EOL ;
1468 l_buf := l_buf || '</P45P3File>'||EOL ;
1469 --
1470 pay_core_files.write_to_magtape_lob(l_buf);
1471 hr_utility.set_location ('Leaving '||l_proc, 1530);
1472
1473 end gen_footer_xml;
1474
1475 END PAY_IE_P45PART3_P46_PKG;