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