[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RTI_FPS_OUTPUT
Source
1 PACKAGE BODY PAY_GB_RTI_FPS_OUTPUT as
2 /* $Header: pygbrtifpsop.pkb 120.0.12020000.2 2013/03/09 06:32:53 ssanjays noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2012 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI_FPS_OUTPUT
10 Purpose:
11 It fetches the archived data for RTI FPS Processes, converts it into XML format
12 and merges it with the EText file to generate the output.
13 This is a UK Specific payroll package.
14
15 History:
16 05-Apr-2012 rajganga 115.0 13918120 Created.
17 08-May-2012 rajganga 115.1 13918120 Created.
18 07-Jun-2012 rajganga 115.2 13918120 Modified EXTRA_DET and FOOTER.
19 =============================================================================*/
20 --
21 --
22 g_package CONSTANT VARCHAR2(30):= 'pay_gb_rti_fps_output.';
23 EOL VARCHAR2(5) := fnd_global.local_chr(10);
24
25 /*------------- PRIVATE PROCEDURE -----------------*/
26
27 /**************************************************************************/
28 PROCEDURE range_cursor (pactid IN NUMBER,
29 sqlstr OUT NOCOPY VARCHAR2)
30 IS
31 l_test_indicator varchar2(1);
32 l_test_id varchar2(8);
33 l_report_type varchar2(15);
34 test_indicator_error EXCEPTION;
35 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
36 BEGIN
37
38 hr_utility.set_location('Entering: '||l_proc,1);
39
40 sqlstr := 'select distinct person_id '||
41 'from per_people_f ppf, '||
42 'pay_payroll_actions ppa '||
43 'where ppa.payroll_action_id = :pactid '||
44 'and ppa.business_group_id = ppf.business_group_id '||
45 'order by ppf.person_id';
46
47 hr_utility.set_location(' Leaving: '||l_proc,100);
48 END range_cursor;
49 --
50 --
51
52 FUNCTION convert_2_xml(p_data IN VARCHAR2,
53 p_tag IN VARCHAR2,
54 p_datatype IN CHAR DEFAULT 'T',
55 p_format IN VARCHAR2 DEFAULT NULL,
56 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
57 RETURN VARCHAR2 IS
58
59 l_data VARCHAR2(4000);
60 l_output VARCHAR2(4000);
61 EOL VARCHAR2(5);
62 l_proc CONSTANT VARCHAR2(50):= g_package||'convert_2_xml';
63 BEGIN
64 hr_utility.set_location('Entering: '||l_proc,1);
65
66 SELECT
67 fnd_global.local_chr(13) || fnd_global.local_chr(10)
68 INTO EOL
69 FROM dual;
70 IF p_null_allowed = 'N'
71 AND (TRIM(p_data) IS NULL
72 OR (p_datatype IN ('N','C')
73 AND to_number(p_data) = 0)) THEN
74 RETURN ' ';
75 END IF;
76 l_data := trim(p_data);
77 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
78 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
79 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
80 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
81 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
82 --------------------------------------------------------
83 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
84 --------------------------------------------------------
85
86 IF p_datatype = 'T' OR p_datatype = 'D' THEN
87 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
88 || '</' || trim(p_tag) || '>'||EOL;
89
90 ELSIF p_datatype = 'N' OR p_datatype = 'C' THEN
91 IF TRIM(p_format) IS NOT NULL THEN
92 SELECT to_char(to_number(p_data), p_format)
93 INTO l_data FROM dual;
94 ELSIF p_datatype = 'C' THEN -- Currency should be two decimal places
95 SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
96 INTO l_data FROM dual;
97 END IF;
98 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
99 || '</' || trim(p_tag) || '>'||EOL;
100 END IF;
101 hr_utility.set_location(' Leaving: '||l_proc,2);
102 RETURN l_output;
103 END convert_2_xml;
104
105 /*****************************************************************************/
106
107 PROCEDURE end_of_file IS
108
109 BEGIN
110 DECLARE
111 l_proc CONSTANT VARCHAR2(50):= g_package||'end_of_file';
112 l_final_xml_string VARCHAR2(32000);
113
114 l_final_submission_ceased VARCHAR2(50);
115 l_final_submission_year VARCHAR2(50);
116 l_econ VARCHAR2(50);
117 l_free_tax_payments VARCHAR2(50);
118 l_benefits_from_others VARCHAR2(50);
119 l_emp_outside_uk VARCHAR2(50);
120 l_pay_to_someone VARCHAR2(50);
121 l_p11d_due VARCHAR2(50);
122 l_service_company VARCHAR2(50);
123
124 EOL VARCHAR2(5) := fnd_global.local_chr(13)
125 || fnd_global.local_chr(10);
126
127 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
128 select
129 to_number(substr(pact.legislative_parameters,
130 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
131 (instr(pact.legislative_parameters,'REP_GROUP=')
132 -
133 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
134 )) arch_payroll_action_id
135 from pay_payroll_actions pact
136 where pact.payroll_action_id = c_mag_payroll_actid;
137
138 -- Cursor to get Employer footer details
139 cursor get_footer_details(c_arch_pay_action_id number) is
140 select pai.action_information1,
141 pai.action_information2,
142 pai.action_information3,
143 pai.action_information4,
144 pai.action_information5,
145 pai.action_information6,
146 pai.action_information7,
147 pai.action_information8,
148 pai.action_information9
149 from pay_payroll_actions ppa,
150 pay_action_information pai
151 where ppa.payroll_action_id = c_arch_pay_action_id
152 and ppa.payroll_action_id = pai.action_context_id
153 and pai.action_information_category='GB_RTI_FPS_EXTRA_DET'
154 and pai.action_context_type='PA';
155
156 TYPE footer_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
157 tab_footer footer_info;
158
159 final_submission_ceased number :=1;
160 final_submission_year number :=2;
161 econ number :=3;
162 free_tax_payments number :=4;
163 benefits_from_others number :=5;
164 emp_outside_uk number :=6;
165 pay_to_someone number :=7;
166 p11d_due number :=8;
167 service_company number :=9;
168
169 l_mag_payroll_actid number;
170 l_arch_pay_action_id number;
171
172 BEGIN
173
174 hr_utility.set_location('Entering : '||l_proc,4);
175
176
177 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
178 hr_utility.trace('RTI FPS Mag Payroll action ='||l_mag_payroll_actid);
179
180 open csr_arch_payroll_action_id(l_mag_payroll_actid);
181 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
182 close csr_arch_payroll_action_id;
183
184 hr_utility.trace('RTI FPS Archive Payroll action ='||l_arch_pay_action_id);
185 open get_footer_details(l_arch_pay_action_id);
186 fetch get_footer_details
187 into l_final_submission_ceased,
188 l_final_submission_year,
189 l_econ,
190 l_free_tax_payments,
191 l_benefits_from_others,
192 l_emp_outside_uk,
193 l_pay_to_someone,
194 l_p11d_due,
195 l_service_company;
196 close get_footer_details;
197
198 tab_footer(final_submission_ceased)
199 := convert_2_xml(l_final_submission_ceased,'FINAL_SUB_CEASED');
200
201 tab_footer(final_submission_year)
202 := convert_2_xml(l_final_submission_year,'FINAL_SUB_YEAR');
203
204 tab_footer(econ)
205 := convert_2_xml(l_econ,'ECON');
206
207 tab_footer(free_tax_payments)
208 := convert_2_xml(l_free_tax_payments,'FREE_TAX_PAYMENTS');
209
210 tab_footer(benefits_from_others)
211 := convert_2_xml(l_benefits_from_others,'BENEFITS_FROM_OTHERS');
212
213 tab_footer(emp_outside_uk)
214 := convert_2_xml(l_emp_outside_uk,'EMPLOYED_OUTSIDE_UK');
215
216 tab_footer(pay_to_someone)
217 := convert_2_xml(l_pay_to_someone,'PAY_TO_SOMEONE_ELSE');
218
219 tab_footer(p11d_due)
220 := convert_2_xml(l_p11d_due,'P11D_DUE');
221
222 tab_footer(service_company)
223 := convert_2_xml(l_service_company,'SERVICE_COMPANY');
224
225 l_final_xml_string := --'<G_FOOTER>' || EOL||
226 tab_footer(final_submission_ceased)||
227 tab_footer(final_submission_year)||
228 tab_footer(econ)||
229 tab_footer(free_tax_payments)||
230 tab_footer(benefits_from_others)||
231 tab_footer(emp_outside_uk)||
232 tab_footer(pay_to_someone)||
233 tab_footer(p11d_due)||
234 tab_footer(service_company)||EOL||
235 --'</G_FOOTER>'||EOL||
236 '</G>';
237
238 pay_core_files.write_to_magtape_lob(l_final_xml_string);
239 hr_utility.trace('Final output : '||l_final_xml_string );
240 hr_utility.set_location(' Leaving: '||l_proc,4);
241
242
243 END;
244 END end_of_file;
245 /*****************************************************************************/
246
247 /*****************************************************************************/
248
249
250 PROCEDURE fps_header_proc is
251 BEGIN
252 DECLARE
253 l_final_xml_string VARCHAR2(32000);
254
255 l_sender_id VARCHAR2(50);
256 l_test_indicator VARCHAR2(50);
257 l_header_value VARCHAR2(50);
258 l_payroll_prod_ver VARCHAR2(50);
259 l_employer_name VARCHAR2(50);
260 l_employer_paye_ref VARCHAR2(50);
261 l_employer_ao_ref VARCHAR2(50);
262 l_hmrc_office_no VARCHAR2(50);
263 l_tax_year VARCHAR2(50);
264
265 EOL VARCHAR2(5) := fnd_global.local_chr(13)
266 || fnd_global.local_chr(10);
267
268 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
269 select
270 to_number(substr(pact.legislative_parameters,
271 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
272 (instr(pact.legislative_parameters,'REP_GROUP=')
273 -
274 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
275 )) arch_payroll_action_id
276 from pay_payroll_actions pact
277 where pact.payroll_action_id = c_mag_payroll_actid;
278
279 cursor get_header_details(c_arch_pay_action_id number) is
280 select pai.action_information8 PAYROLL_PROD_VER,
281 pai.action_information4 EMPLOYER_NAME,
282 pai.action_information6 HMRC_OFFICE_NO,
283 pai.action_information7 EMPLOYER_PAYE_REF,
284 pai.action_information11 EMPLOYER_AO_REF,
285 pai.action_information12 SENDER_ID,
286 pai.action_information13 TEST_INDICATOR,
287 pai.action_information14 HEADER_VALUE,
291 where ppa.payroll_action_id = c_arch_pay_action_id
288 pai.action_information15 TAX_YEAR
289 from pay_payroll_actions ppa,
290 pay_action_information pai
292 and ppa.payroll_action_id = pai.action_context_id
293 and pai.action_information_category='RTI PAYROLL INFO'
294 and pai.action_context_type='PA';
295
296 TYPE header_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
297 tab_header header_info;
298
299 sender_id number :=1;
300 test_indicator number :=2;
301 header_value number :=3;
302 payroll_prod_ver number :=4;
303 employer_name number :=5;
304 employer_paye_ref number :=6;
305 employer_ao_ref number :=7;
306 hmrc_office_no number :=8;
307 tax_year number :=9;
308
309 l_mag_payroll_actid number;
310 l_arch_pay_action_id number;
311
312
313 BEGIN
314
315 hr_utility.trace('+==========================================================+');
316 hr_utility.trace('Entering fps_header_proc');
317
318 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
319 hr_utility.trace('RTI FPS Mag Payroll action ='||l_mag_payroll_actid);
320
321 open csr_arch_payroll_action_id(l_mag_payroll_actid);
322 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
323 close csr_arch_payroll_action_id;
324
325 hr_utility.trace('RTI FPS Archive Payroll action ='||l_arch_pay_action_id);
326 open get_header_details(l_arch_pay_action_id);
327 fetch get_header_details
328 into l_payroll_prod_ver,
329 l_employer_name,
330 l_hmrc_office_no,
331 l_employer_paye_ref,
332 l_employer_ao_ref,
333 l_sender_id,
334 l_test_indicator,
335 l_header_value,
336 l_tax_year;
337 close get_header_details;
338
339
340 tab_header(sender_id)
341 := convert_2_xml(l_sender_id,'SENDER_ID');
342
343 tab_header(test_indicator)
344 := convert_2_xml(l_test_indicator,'TEST_INDICATOR');
345
346 tab_header(header_value)
347 := convert_2_xml(l_header_value,'HEADER_VALUE');
348
349 tab_header(payroll_prod_ver)
350 := convert_2_xml(l_payroll_prod_ver,'PAYROLL_PROD_VER');
351
352 tab_header(employer_name)
353 := convert_2_xml(l_employer_name,'EMPLOYER_NAME');
354
355 tab_header(employer_paye_ref)
356 := convert_2_xml(l_employer_paye_ref,'EMPLOYER_PAYE_REF');
357
358 tab_header(employer_ao_ref)
359 := convert_2_xml(l_employer_ao_ref,'EMPLOYER_AO_REF');
360
361 tab_header(hmrc_office_no)
362 := convert_2_xml(l_hmrc_office_no,'HMRC_OFFICE_NO');
363
364 tab_header(tax_year)
365 := convert_2_xml(l_tax_year,'TAX_YEAR');
366
367 l_final_xml_string := '<G>
368 <G_HEADER>' || EOL||
369 tab_header(sender_id)||
370 tab_header(test_indicator)||
371 tab_header(header_value)||
372 tab_header(payroll_prod_ver)||
373 tab_header(employer_name)||
374 tab_header(employer_paye_ref)||
375 tab_header(employer_ao_ref)||
376 tab_header(hmrc_office_no)||
377 tab_header(tax_year)||EOL||
378 '</G_HEADER>';
379
380 pay_core_files.write_to_magtape_lob(l_final_xml_string);
381 hr_utility.trace('Final output : '||l_final_xml_string );
382 hr_utility.trace('X==========================================================X');
383
384 END;
385 END fps_header_proc;
386
387 /*****************************************************************************/
388
389
390 PROCEDURE fps_employee_proc IS
391
392 /*
393 This procedure has the following sections:
394 1. Generate Employee data related Tags
395 This set of tags repeat for each Employee record.
396 2. Generate Assignment data related Tags
397 This set of tags repeat for each assignment for the above Employee record.
398 3. Generate the Final XML
399 Final XML tag will have the below structure:
400 <EMP1>
401 <ASG1>
402 <NI>
403 <ASG2>
404 <NI>
405 </EMP1>
406 <EMP2>
407 <ASG1>
408 <NI1>
409 <NI2>
410 <ASG2>
411 <NI>
412 </EMP2>
413 */
414
415 l_proc CONSTANT VARCHAR2(50):= g_package||'fps_employee_proc';
416 BEGIN
417 DECLARE
418 l_mag_payroll_actid number;
419 l_mag_asg_action_id number;
420 l_arch_asg_action_id number;
421 l_arch_pay_action_id number;
422 EOL varchar2(5) := fnd_global.local_chr(13)
423 || fnd_global.local_chr(10);
424
425 l_other_asg_arch_id number;
426 l_other_asg_act_arch_id number;
427
428 TYPE employee_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
429 tab_employee employee_info;
430
431 l_address_line1 varchar2(100);
432 l_address_line2 varchar2(100);
433 l_address_line3 varchar2(100);
434 l_address_line4 varchar2(100);
435 l_country varchar2(100);
436
437 l_last_name varchar2(100);
441 l_postal_code varchar2(100);
438 l_first_name varchar2(100);
439 l_second_name varchar2(100);
440 l_title varchar2(100);
442 l_ni_number varchar2(100);
443 l_dob varchar2(100);
444 l_gender varchar2(100);
445
446 l_passport_number varchar2(100);
447 l_partner_last_name varchar2(100);
448 l_partner_first_name varchar2(100);
449 l_partner_second_name varchar2(100);
450 l_partner_ni_number varchar2(100);
451
452 l_start_empl_date varchar2(100);
453 l_date_of_leaving varchar2(100);
454 l_payment_date varchar2(100);
455 l_starter_declaration varchar2(100);
456 l_student_loan_indicator varchar2(100);
457 l_stay_over_six_months varchar2(100);
458 l_stay_less_than_six_months varchar2(100);
459 l_living_abroad varchar2(100);
460 l_eea_cwc varchar2(100);
461 l_epm6 varchar2(100);
462 l_occ_pension_indicator varchar2(100);
463 l_payroll_id varchar2(100);
464 l_payroll_id_changed_ind varchar2(100);
465 l_old_payroll_id varchar2(100);
466 l_irregular_emp_indicator varchar2(100);
467 l_pay_frequency varchar2(100);
468 l_week_number varchar2(100);
469 l_month_number varchar2(100);
470 l_periods_covered varchar2(100);
471 l_agg_earnings_indicator varchar2(100);
472 l_payments_after_leaving_ind varchar2(100);
473 l_hours_worked varchar2(100);
474 l_nic_calc_method varchar2(100);
475 l_tax_week_number varchar2(100);
476 l_annual_occ_pension varchar2(100);
477 l_taxable_pay_to_date varchar2(100);
478 l_total_tax varchar2(100);
479 l_student_loan_rec varchar2(100);
480 l_taxable_pay varchar2(100);
481 l_payments_not_to_nic varchar2(100);
482 l_deductions_from_netpay varchar2(100);
483 l_statutory_deductions varchar2(100);
484 l_benefits_taxed_payroll varchar2(100);
485 l_benefits_class1_nic varchar2(100);
486 l_benefits_not_class1_nic varchar2(100);
487 l_emp_pension_contri_net_pay varchar2(100);
488 l_items_class1_nic varchar2(100);
489 l_emp_pen_contri_not_net_pay varchar2(100);
490 l_student_loan_repay_rec varchar2(100);
491 l_tax_deducted varchar2(100);
492 l_ssp varchar2(100);
493 l_smp varchar2(100);
494 l_ospp varchar2(100);
495 l_sap varchar2(100);
496 l_aspp varchar2(100);
497 l_tax_code varchar2(100);
498 l_week_month_indicator varchar2(100);
499 l_bacs_hash_code varchar2(100);
500
501 l_ni_letter varchar2(100);
502 l_pay_to_ni varchar2(100);
503 l_earnings_upto_lel varchar2(100);
504 l_earnings_upto_pt varchar2(100);
505 l_earnings_upto_uap varchar2(100);
506 l_earnings_upto_uel varchar2(100);
507 l_employer_contrib varchar2(100);
508 l_employer_contrib_todate varchar2(100);
509 l_employee_contrib varchar2(100);
510 l_employee_contrib_todate varchar2(100);
511 -------------------------------------------------------------------------------------------
512 address_line1 number :=1;
513 address_line2 number :=2;
514 address_line3 number :=3;
515 address_line4 number :=4;
516 country number :=5;
517
518 last_name number :=6;
519 first_name number :=7;
520 second_name number :=8;
521 title number :=9;
522 postal_code number :=10;
523 ni_number number :=11;
524 dob number :=12;
525 gender number :=13;
526
527
528 passport_number number :=14;
529 partner_last_name number :=15;
530 partner_first_name number :=16;
531 partner_second_name number :=17;
532 partner_ni_number number :=18;
533
534
535 start_empl_date number :=19;
536 date_of_leaving number :=20;
537 payment_date number :=21;
538 starter_declaration number :=22;
539 student_loan_indicator number :=23;
540 stay_over_six_months number :=24;
541 stay_less_than_six_months number :=25;
542 living_abroad number :=26;
543 eea_cwc number :=27;
544 epm6 number :=28;
545 occ_pension_indicator number :=29;
546 payroll_id number :=30;
547 payroll_id_changed_ind number :=31;
548 old_payroll_id number :=32;
549 irregular_emp_indicator number :=33;
550 pay_frequency number :=34;
551 week_number number :=35;
552 month_number number :=36;
553 periods_covered number :=37;
554 agg_earnings_indicator number :=38;
555 payments_after_leaving_ind number :=39;
556 hours_worked number :=40;
557 nic_calc_method number :=41;
558 tax_week_number number :=42;
559 annual_occ_pension number :=43;
560 taxable_pay_to_date number :=44;
561 total_tax number :=45;
562 student_loan_rec number :=46;
563 taxable_pay number :=47;
564 payments_not_to_nic number :=48;
568 benefits_class1_nic number :=52;
565 deductions_from_netpay number :=49;
566 statutory_deductions number :=50;
567 benefits_taxed_payroll number :=51;
569 benefits_not_class1_nic number :=53;
570 emp_pension_contri_net_pay number :=54;
571 items_class1_nic number :=55;
572 emp_pen_contri_not_net_pay number :=56;
573 student_loan_repay_rec number :=57;
574 tax_deducted number :=58;
575 ssp number :=59;
576 smp number :=60;
577 ospp number :=61;
578 sap number :=62;
579 aspp number :=63;
580 tax_code number :=64;
581 week_month_indicator number :=65;
582 bacs_hash_code number :=66;
583
584 ni_letter number :=67;
585 pay_to_ni number :=68;
586 earnings_upto_lel number :=69;
587 earnings_upto_pt number :=70;
588 earnings_upto_uap number :=71;
589 earnings_upto_uel number :=72;
590 employer_contrib number :=73;
591 employer_contrib_todate number :=74;
592 employee_contrib number :=75;
593 employee_contrib_todate number :=76;
594
595
596 l_final_xml_string varchar2(32000);
597 l_process_flag varchar2(1);
598
599
600 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
601 select
602 to_number(substr(pact.legislative_parameters,
603 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
604 (instr(pact.legislative_parameters,'REP_GROUP=')
605 -
606 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
607 )) arch_payroll_action_id
608 from pay_payroll_actions pact
609 where pact.payroll_action_id = c_mag_payroll_actid;
610
611 /*
612 Below cursor gets corresponding archiver assignment_action_id for the current magtape assignment_action_id
613 */
614 cursor get_arch_asg_actid
615 (c_mag_payroll_actid number,
616 c_mag_asg_action_id number,
617 c_arch_pay_action_id number) is
618 select paa_arch.assignment_action_id
619 from pay_assignment_actions paa_arch,
620 pay_assignment_actions paa_mag,
621 pay_action_information pai
622 where paa_mag.payroll_action_id = c_mag_payroll_actid
623 and paa_mag.assignment_action_id = c_mag_asg_action_id
624 and paa_arch.payroll_action_id = c_arch_pay_action_id
625
626 and paa_arch.assignment_id = paa_mag.assignment_id
627 and paa_arch.assignment_action_id = pai.action_context_id
628 and pai.action_information_category = 'GB_RTI_FPS_ASG_DET1' -- to check whether that asg is archived or not
629 and pai.action_context_type = 'AAP';
630
631 /*
632 Below cursor gets other archiver assignment_action_id for the current archiver assignment_action_id, if any exists
633 */
634 cursor get_other_asg_act_ids (c_arch_pay_action_id number,
635 c_arch_asg_action_id number) is
636
637 select distinct paa_other.assignment_id,
638 paa_other.assignment_action_id
639 from pay_assignment_actions paa_current,
640 per_all_assignments_f paaf_current,
641 pay_assignment_actions paa_other,
642 per_all_assignments_f paaf_other
643 where paa_current.payroll_action_id = c_arch_pay_action_id
644 and paa_current.assignment_action_id = c_arch_asg_action_id
645 and paaf_current.assignment_id = paa_current.assignment_id
646 and paa_other.payroll_action_id = c_arch_pay_action_id
647 and paa_other.assignment_action_id <> c_arch_asg_action_id
648 and paaf_other.assignment_id = paa_other.assignment_id
649 and paaf_other.person_id = paaf_current.person_id
650 and paa_current.ACTION_STATUS = 'C'
651 and paa_other.ACTION_STATUS = 'C';
652
653
654 /*
655 Below cursor gets employee details for current archiver assignment_action_id
656 */
657
658 -- To get both Employee and Address Details
659
660 cursor get_emp_details (c_arch_asg_action_id number) is
661 select pai_addr.action_information5,
662 pai_addr.action_information6,
663 pai_addr.action_information7,
664 pai_addr.action_information8,
665 pai_addr.action_information13,
666 pai_empl.action_information3,
667 pai_empl.action_information4,
668 pai_empl.action_information5,
669 pai_empl.action_information6,
670 pai_addr.action_information12,
671 pai_empl.action_information7,
672 pai_empl.action_information8,
673 pai_empl.action_information9,
674 pai_empl.action_information16,
675 pai_empl.action_information12,
676 pai_empl.action_information13,
677 pai_empl.action_information14,
678 pai_empl.action_information15
679
680 from pay_action_information pai_addr,
681 pay_action_information pai_empl
682 where pai_addr.action_context_id = c_arch_asg_action_id
683 and pai_addr.action_information_category = 'ADDRESS DETAILS'
684 and pai_addr.action_context_type = 'AAP'
685 and pai_empl.action_context_id = c_arch_asg_action_id
686 and pai_empl.action_information_category = 'GB RTI EMPLOYEE DETAILS'
687 and pai_empl.action_context_type = 'AAP';
688
689 /*
690 Below cursor gets asg details for current archiver assignment_action_id
691 */
692
693 cursor get_asg_details1 (c_arch_asg_action_id number) is
694 select pai_asg.ACTION_INFORMATION1,
698 pai_asg.ACTION_INFORMATION5,
695 pai_asg.ACTION_INFORMATION2,
696 pai_asg.ACTION_INFORMATION3,
697 pai_asg.ACTION_INFORMATION4,
699 pai_asg.ACTION_INFORMATION6,
700 pai_asg.ACTION_INFORMATION7,
701 pai_asg.ACTION_INFORMATION8,
702 pai_asg.ACTION_INFORMATION9,
703 pai_asg.ACTION_INFORMATION10,
704 pai_asg.ACTION_INFORMATION12,
705 pai_asg.ACTION_INFORMATION13,
706 pai_asg.ACTION_INFORMATION14,
707 pai_asg.ACTION_INFORMATION15,
708 pai_asg.ACTION_INFORMATION16,
709 pai_asg.ACTION_INFORMATION17,
710 pai_asg.ACTION_INFORMATION18,
711 pai_asg.ACTION_INFORMATION19,
712 pai_asg.ACTION_INFORMATION20,
713 pai_asg.ACTION_INFORMATION21,
714 pai_asg.ACTION_INFORMATION22,
715 pai_asg.ACTION_INFORMATION23,
716 pai_asg.ACTION_INFORMATION24,
717 pai_asg.ACTION_INFORMATION25,
718 pai_asg.ACTION_INFORMATION26,
719 pai_asg.ACTION_INFORMATION28
720
721 from pay_action_information pai_asg
722 where pai_asg.action_context_id = c_arch_asg_action_id
723 and pai_asg.action_information_category = 'GB_RTI_FPS_ASG_DET1'
724 and pai_asg.action_context_type = 'AAP';
725
726 cursor get_asg_details2 (c_arch_asg_action_id number) is
727 select pai_asg.ACTION_INFORMATION1,
728 pai_asg.ACTION_INFORMATION2,
729 pai_asg.ACTION_INFORMATION3,
730 pai_asg.ACTION_INFORMATION6,
731 pai_asg.ACTION_INFORMATION7,
732 pai_asg.ACTION_INFORMATION8,
733 pai_asg.ACTION_INFORMATION9,
734 pai_asg.ACTION_INFORMATION10,
735 pai_asg.ACTION_INFORMATION11,
736 pai_asg.ACTION_INFORMATION12,
737 pai_asg.ACTION_INFORMATION13,
738 pai_asg.ACTION_INFORMATION14,
739 pai_asg.ACTION_INFORMATION15,
740 pai_asg.ACTION_INFORMATION16,
741 pai_asg.ACTION_INFORMATION17,
742 pai_asg.ACTION_INFORMATION18,
743 pai_asg.ACTION_INFORMATION19,
744 pai_asg.ACTION_INFORMATION20,
745 pai_asg.ACTION_INFORMATION21,
746 pai_asg.ACTION_INFORMATION22,
747 pai_asg.ACTION_INFORMATION25,
748 pai_asg.ACTION_INFORMATION27
749
750 from pay_action_information pai_asg
751 where pai_asg.action_context_id = c_arch_asg_action_id
752 and pai_asg.action_information_category = 'GB_RTI_FPS_ASG_DET2'
753 and pai_asg.action_context_type = 'AAP';
754
755
756 --Cursor to fetch NI details
757 cursor get_ni_details(l_arch_asg_action_id number) is
758 select
759 ACTION_INFORMATION1,
760 ACTION_INFORMATION2,
761 ACTION_INFORMATION3,
762 ACTION_INFORMATION4,
763 ACTION_INFORMATION5,
764 ACTION_INFORMATION6,
765 ACTION_INFORMATION7,
766 ACTION_INFORMATION8,
767 ACTION_INFORMATION9,
768 ACTION_INFORMATION10
769 from pay_action_information where action_context_id = l_arch_asg_action_id
770 and ACTION_INFORMATION_CATEGORY = 'GB_RTI_FPS_NI_DET'
771 and ACTION_CONTEXT_TYPE = 'AAP';
772
773
774
775 BEGIN
776 hr_utility.set_location(' Entering: '||l_proc,15);
777 hr_utility.set_location('Employee Record',16);
778
779 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
780 hr_utility.set_location('RTI FPS Mag Payroll action ='||l_mag_payroll_actid,17);
781
782 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
783 hr_utility.set_location('RTI FPS Mag Assignment action ='|| to_char(l_mag_asg_action_id),18);
784
785
786
787 open csr_arch_payroll_action_id(l_mag_payroll_actid);
788 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
789 close csr_arch_payroll_action_id;
790
791 hr_utility.set_location('RTI FPS Archive Payroll action ='|| to_char(l_arch_pay_action_id),19);
792
793 open get_arch_asg_actid
794 (l_mag_payroll_actid,
795 l_mag_asg_action_id,
796 l_arch_pay_action_id);
797 fetch get_arch_asg_actid
798 into l_arch_asg_action_id;
799 close get_arch_asg_actid;
800
801 hr_utility.set_location('RTI FPS Archive Assignment action ='|| to_char(l_arch_asg_action_id),20);
802
803 l_process_flag := 'Y';
804
805 open get_other_asg_act_ids (l_arch_pay_action_id, l_arch_asg_action_id);
806 loop
807 fetch get_other_asg_act_ids
808 into l_other_asg_arch_id,
809 l_other_asg_act_arch_id;
810
811 if l_other_asg_act_arch_id < l_arch_asg_action_id then
812 hr_utility.set_location('Current assignment_action_id '|| to_char(l_arch_asg_action_id)|| 'is already processed,
813 while processing the assignment_id '||l_other_asg_arch_id,25);
814 l_process_flag := 'N';
815 end if;
816
817 if (l_process_flag='N') then
818 exit;
819 end if;
820
821 exit when get_other_asg_act_ids%notfound;
822 end loop;
823 close get_other_asg_act_ids;
824
825 -- Below if will be for complete reporting logic
826 if l_process_flag = 'Y' then
827
828 open get_emp_details (l_arch_asg_action_id);
829 hr_utility.set_location('Inside get_emp_details l_arch_asg_action_id :'||l_arch_asg_action_id,32);
830 fetch get_emp_details
831 into l_address_line1,
832 l_address_line2,
833 l_address_line3,
834 l_address_line4,
835 l_country,
836 l_last_name,
840 l_postal_code,
837 l_first_name,
838 l_second_name,
839 l_title,
841 l_ni_number,
842 l_dob,
843 l_gender,
844 l_passport_number,
845 l_partner_last_name,
846 l_partner_first_name,
847 l_partner_second_name,
848 l_partner_ni_number;
849 close get_emp_details;
850 -- Embedding Strings into XML tags
851
852 tab_employee(address_line1)
853 := convert_2_xml(l_address_line1,'ADD_L1');
854 tab_employee(address_line2)
855 := convert_2_xml(l_address_line2,'ADD_L2');
856 tab_employee(address_line3)
857 := convert_2_xml(l_address_line3,'ADD_L3');
858 tab_employee(address_line4)
859 := convert_2_xml(l_address_line4,'ADD_L4');
860 tab_employee(country)
861 := convert_2_xml(l_country,'COUNTRY');
862
863 tab_employee(last_name)
864 := convert_2_xml(l_last_name,'LAST_N');
865 tab_employee(first_name)
866 := convert_2_xml(l_first_name,'FIR_N');
867 tab_employee(second_name)
868 := convert_2_xml(l_second_name,'SEC_N');
869 tab_employee(title)
870 := convert_2_xml(l_title,'TITLE');
871 tab_employee(postal_code)
872 := convert_2_xml(l_postal_code,'POSTAL_CODE');
873 tab_employee(ni_number)
874 := convert_2_xml(l_ni_number,'NINO');
875 tab_employee(dob)
876 := convert_2_xml(l_dob,'DOB');
877 tab_employee(gender)
878 := convert_2_xml(l_gender,'GENDER');
879
880 --Added
881 tab_employee(passport_number)
882 := convert_2_xml(l_passport_number,'PASSPORT');
883 tab_employee(partner_last_name)
884 := convert_2_xml(l_partner_last_name,'P_LASTN');
885 tab_employee(partner_first_name)
886 := convert_2_xml(l_partner_first_name,'P_FIRN');
887 tab_employee(partner_second_name)
888 := convert_2_xml(l_partner_second_name,'P_SECN');
889 tab_employee(partner_ni_number)
890 := convert_2_xml(l_partner_ni_number,'P_NINO');
891
892
893
894 open get_asg_details1 (l_arch_asg_action_id);
895 fetch get_asg_details1
896 into l_start_empl_date,
897 l_date_of_leaving,
898 l_payment_date,
899 l_starter_declaration,
900 l_student_loan_indicator,
901 l_stay_over_six_months,
902 l_stay_less_than_six_months,
903 l_living_abroad,
904 l_eea_cwc,
905 l_epm6,
906 l_occ_pension_indicator,
907 l_payroll_id,
908 l_payroll_id_changed_ind,
909 l_old_payroll_id,
910 l_irregular_emp_indicator,
911 l_pay_frequency,
912 l_week_number,
913 l_month_number,
914 l_periods_covered,
915 l_agg_earnings_indicator,
916 l_payments_after_leaving_ind,
917 l_hours_worked,
918 l_nic_calc_method,
919 l_tax_week_number,
920 l_annual_occ_pension,
921 l_bacs_hash_code;
922 close get_asg_details1;
923
924 open get_asg_details2 (l_arch_asg_action_id);
925 fetch get_asg_details2
926 into l_taxable_pay_to_date,
927 l_total_tax,
928 l_student_loan_rec,
929 l_taxable_pay,
930 l_payments_not_to_nic,
931 l_deductions_from_netpay,
932 l_statutory_deductions,
933 l_benefits_taxed_payroll,
934 l_benefits_class1_nic,
935 l_benefits_not_class1_nic,
936 l_emp_pension_contri_net_pay,
937 l_items_class1_nic,
938 l_emp_pen_contri_not_net_pay,
939 l_student_loan_repay_rec,
940 l_tax_deducted,
941 l_ssp,
942 l_smp,
943 l_ospp,
944 l_sap,
945 l_aspp,
946 l_tax_code,
947 l_week_month_indicator;
948
949
950 close get_asg_details2;
951
952
953 tab_employee(start_empl_date)
954 := convert_2_xml(l_start_empl_date,'S_DATE');
955
956 tab_employee(date_of_leaving)
957 := convert_2_xml(l_date_of_leaving,'E_DATE');
958
959 tab_employee(payment_date)
960 := convert_2_xml(l_payment_date,'PAY_D');
961
962 tab_employee(starter_declaration)
963 := convert_2_xml(l_starter_declaration,'STARTER_DEC');
964
965 tab_employee(student_loan_indicator)
966 := convert_2_xml(l_student_loan_indicator,'STUD_LOAN_IND');
967
968 tab_employee(stay_over_six_months)
969 := convert_2_xml(l_stay_over_six_months,'OVER_SIX');
970
971 tab_employee(stay_less_than_six_months)
972 := convert_2_xml(l_stay_less_than_six_months,'LESS_SIX');
973
974 tab_employee(living_abroad)
975 := convert_2_xml(l_living_abroad,'LIV_ABR');
976
977 tab_employee(eea_cwc)
978 := convert_2_xml(l_eea_cwc,'EEA_CWC');
979
980 tab_employee(epm6)
981 := convert_2_xml(l_epm6,'EPM6');
982
983 tab_employee(occ_pension_indicator)
984 := convert_2_xml(l_occ_pension_indicator,'OCC_IND');
985
986 tab_employee(payroll_id)
987 := convert_2_xml(l_payroll_id,'PAYID');
988
989 tab_employee(payroll_id_changed_ind)
990 := convert_2_xml(l_payroll_id_changed_ind,'PAY_CHAN_IND');
991
992 tab_employee(old_payroll_id)
993 := convert_2_xml(l_old_payroll_id,'OPAYID');
994
998 tab_employee(pay_frequency)
995 tab_employee(irregular_emp_indicator)
996 := convert_2_xml(l_irregular_emp_indicator,'IRR_IND');
997
999 := convert_2_xml(l_pay_frequency,'PAY_FRE');
1000
1001 tab_employee(week_number)
1002 := convert_2_xml(l_week_number,'WEEK_NO');
1003
1004 tab_employee(month_number)
1005 := convert_2_xml(l_month_number,'MONTH_NO');
1006
1007 tab_employee(periods_covered)
1008 := convert_2_xml(l_periods_covered,'PER_COV');
1009
1010 tab_employee(agg_earnings_indicator)
1011 := convert_2_xml(l_agg_earnings_indicator,'AGG_IND');
1012
1013 tab_employee(payments_after_leaving_ind)
1014 := convert_2_xml(l_payments_after_leaving_ind,'PAY_LEAV_IND');
1015
1016 tab_employee(hours_worked)
1017 := convert_2_xml(l_hours_worked,'HRS');
1018
1019 tab_employee(nic_calc_method)
1020 := convert_2_xml(l_nic_calc_method,'NIC_CALC');
1021
1022 tab_employee(tax_week_number)
1023 := convert_2_xml(l_tax_week_number,'TAX_WEEK');
1024
1025 tab_employee(annual_occ_pension)
1026 := convert_2_xml(l_annual_occ_pension,'ANNUAL_OCC');
1027
1028 tab_employee(taxable_pay_to_date)
1029 := convert_2_xml(l_taxable_pay_to_date,'TAX_PAYD');
1030
1031 tab_employee(total_tax)
1032 := convert_2_xml(l_total_tax,'T_TAX');
1033
1034 tab_employee(student_loan_rec)
1035 := convert_2_xml(l_student_loan_rec,'STUD_LOAN_REC');
1036
1037 tab_employee(taxable_pay)
1038 := convert_2_xml(l_taxable_pay,'TAX_PAY');
1039
1040 tab_employee(payments_not_to_nic)
1041 := convert_2_xml(l_payments_not_to_nic,'PAY_NNIC');
1042
1043 tab_employee(deductions_from_netpay)
1044 := convert_2_xml(l_deductions_from_netpay,'DED_PAY');
1045
1046 tab_employee(statutory_deductions)
1047 := convert_2_xml(l_statutory_deductions,'STA_DED');
1048
1049 tab_employee(benefits_taxed_payroll)
1050 := convert_2_xml(l_benefits_taxed_payroll,'B_PAY');
1051
1052 tab_employee(benefits_class1_nic)
1053 := convert_2_xml(l_benefits_class1_nic,'B_NIC');
1054
1055 tab_employee(benefits_not_class1_nic)
1056 := convert_2_xml(l_benefits_not_class1_nic,'B_NNIC');
1057
1058 tab_employee(emp_pension_contri_net_pay)
1059 := convert_2_xml(l_emp_pension_contri_net_pay,'EMP_PAY');
1060
1061 tab_employee(items_class1_nic)
1062 := convert_2_xml(l_items_class1_nic,'I_NIC');
1063
1064 tab_employee(emp_pen_contri_not_net_pay)
1065 := convert_2_xml(l_emp_pen_contri_not_net_pay,'EMP_PEN_NOT');
1066
1067 tab_employee(student_loan_repay_rec)
1068 := convert_2_xml(l_student_loan_repay_rec,'STUD_LOAN_R_REC');
1069
1070 tab_employee(tax_deducted)
1071 := convert_2_xml(l_tax_deducted,'TAX_DED');
1072
1073 tab_employee(ssp)
1074 := convert_2_xml(l_ssp,'SSP');
1075
1076 tab_employee(smp)
1077 := convert_2_xml(l_smp,'SMP');
1078
1079 tab_employee(ospp)
1080 := convert_2_xml(l_ospp,'OSPP');
1081
1082 tab_employee(sap)
1083 := convert_2_xml(l_sap,'SAP');
1084
1085 tab_employee(aspp)
1086 := convert_2_xml(l_aspp,'ASPP');
1087
1088 tab_employee(tax_code)
1089 := convert_2_xml(l_tax_code,'TAX_CODE');
1090
1091 tab_employee(week_month_indicator)
1092 := convert_2_xml(l_week_month_indicator,'W1M1_IND');
1093
1094 tab_employee(bacs_hash_code)
1095 := convert_2_xml(l_bacs_hash_code,'BACS');
1096
1097
1098
1099 hr_utility.set_location('Creating FPS Employee XML String',34);
1100 l_final_xml_string := '<G_EMP>'
1101 ||EOL||
1102 tab_employee(address_line1)||
1103 tab_employee(address_line2)||
1104 tab_employee(address_line3)||
1105 tab_employee(address_line4)||
1106 tab_employee(country)||
1107 tab_employee(last_name)||
1108 tab_employee(first_name)||
1109 tab_employee(second_name)||
1110 tab_employee(title)||
1111 tab_employee(postal_code)||
1112 tab_employee(dob)||
1113 tab_employee(ni_number)||
1114 tab_employee(passport_number)||
1115 tab_employee(partner_last_name)||
1116 tab_employee(partner_first_name)||
1117 tab_employee(partner_second_name)||
1118 tab_employee(partner_ni_number);
1119
1120 hr_utility.set_location('Creating FPS Assignment XML String',35);
1121 l_final_xml_string := l_final_xml_string||'<G_ASG>'||
1122 tab_employee(start_empl_date)||
1123 tab_employee(date_of_leaving)||
1124 tab_employee(payment_date)||
1125 tab_employee(starter_declaration)||
1126 tab_employee(student_loan_indicator)||
1127 tab_employee(stay_over_six_months)||
1128 tab_employee(stay_less_than_six_months)||
1129 tab_employee(living_abroad)||
1130 tab_employee(eea_cwc)||
1131 tab_employee(epm6)||
1132 tab_employee(occ_pension_indicator)||
1133 tab_employee(payroll_id)||
1134 tab_employee(payroll_id_changed_ind)||
1135 tab_employee(old_payroll_id)||
1136 tab_employee(irregular_emp_indicator)||
1137 tab_employee(pay_frequency)||
1138 tab_employee(week_number)||
1139 tab_employee(month_number)||
1140 tab_employee(periods_covered)||
1141 tab_employee(agg_earnings_indicator)||
1142 tab_employee(payments_after_leaving_ind)||
1143 tab_employee(hours_worked)||
1147
1144 tab_employee(nic_calc_method)||
1145 tab_employee(tax_week_number)||
1146 tab_employee(annual_occ_pension)||
1148 tab_employee(taxable_pay_to_date)||
1149 tab_employee(total_tax)||
1150 tab_employee(student_loan_rec)||
1151 tab_employee(taxable_pay)||
1152 tab_employee(payments_not_to_nic)||
1153 tab_employee(deductions_from_netpay)||
1154 tab_employee(statutory_deductions)||
1155 tab_employee(benefits_taxed_payroll)||
1156 tab_employee(benefits_class1_nic)||
1157 tab_employee(benefits_not_class1_nic)||
1158 tab_employee(emp_pension_contri_net_pay)||
1159 tab_employee(items_class1_nic)||
1160 tab_employee(emp_pen_contri_not_net_pay)||
1161 tab_employee(student_loan_repay_rec)||
1162 tab_employee(tax_deducted)||
1163 tab_employee(ssp)||
1164 tab_employee(smp)||
1165 tab_employee(ospp)||
1166 tab_employee(sap)||
1167 tab_employee(aspp)||
1168 tab_employee(tax_code)||
1169 tab_employee(week_month_indicator)||
1170 tab_employee(bacs_hash_code);
1171
1172
1173 -- To get NI Details of the asg action id
1174 open get_ni_details(l_arch_asg_action_id);
1175 loop
1176 fetch get_ni_details into
1177 l_ni_letter,
1178 l_pay_to_ni,
1179 l_earnings_upto_lel,
1180 l_earnings_upto_pt,
1181 l_earnings_upto_uap,
1182 l_earnings_upto_uel,
1183 l_employer_contrib,
1184 l_employer_contrib_todate,
1185 l_employee_contrib,
1186 l_employee_contrib_todate;
1187 exit when get_ni_details%notfound;
1188
1189 tab_employee(ni_letter)
1190 := convert_2_xml(l_ni_letter,'NI');
1191
1192 tab_employee(pay_to_ni)
1193 := convert_2_xml(l_pay_to_ni,'PAY_NI');
1194
1195 tab_employee(earnings_upto_lel)
1196 := convert_2_xml(l_earnings_upto_lel,'UPTO_LEL');
1197
1198 tab_employee(earnings_upto_pt)
1199 := convert_2_xml(l_earnings_upto_pt,'UPTO_PT');
1200
1201 tab_employee(earnings_upto_uap)
1202 := convert_2_xml(l_earnings_upto_uap,'UPTO_UAP');
1203
1204 tab_employee(earnings_upto_uel)
1205 := convert_2_xml(l_earnings_upto_uel,'UPTO_UEL');
1206
1207 tab_employee(employer_contrib)
1208 := convert_2_xml(l_employer_contrib,'EMR_CON');
1209
1210 tab_employee(employer_contrib_todate)
1211 := convert_2_xml(l_employer_contrib_todate,'EMR_CON_D');
1212
1213 tab_employee(employee_contrib)
1214 := convert_2_xml(l_employee_contrib,'EMP_CON');
1215
1216 tab_employee(employee_contrib_todate)
1217 := convert_2_xml(l_employee_contrib_todate,'EMP_CON_D');
1218
1219
1220
1221 l_final_xml_string := l_final_xml_string||'<G_NI>'||
1222 tab_employee(ni_letter)||
1223 tab_employee(pay_to_ni)||
1224 tab_employee(earnings_upto_lel)||
1225 tab_employee(earnings_upto_pt)||
1226 tab_employee(earnings_upto_uap)||
1227 tab_employee(earnings_upto_uel)||
1228 tab_employee(employer_contrib)||
1229 tab_employee(employer_contrib_todate)||
1230 tab_employee(employee_contrib)||
1231 tab_employee(employee_contrib_todate)||
1232 '</G_NI>';
1233
1234 end loop;
1235 l_final_xml_string := l_final_xml_string||'</G_ASG>';
1236 close get_ni_details;
1237
1238 ------------
1239
1240 open get_other_asg_act_ids(l_arch_pay_action_id, l_arch_asg_action_id );
1241 hr_utility.set_location('Inside get_other_asg_act_ids 2nd time l_arch_pay_action_id :'||l_arch_pay_action_id,36);
1242 hr_utility.set_location('Inside get_other_asg_act_ids 2nd time l_arch_asg_action_id :'||l_arch_asg_action_id,37);
1243
1244 loop
1245 fetch get_other_asg_act_ids
1246 into l_other_asg_arch_id,
1247 l_other_asg_act_arch_id;
1248 hr_utility.set_location('l_other_asg_arch_id :'||l_other_asg_arch_id,38);
1249 hr_utility.set_location('l_other_asg_act_arch_id :'||l_other_asg_act_arch_id,39);
1250
1251 exit when get_other_asg_act_ids%notfound;
1252
1253
1254 open get_asg_details1 (l_other_asg_act_arch_id);
1255 hr_utility.set_location('Inside get_asg_details1 2nd Time l_other_asg_act_arch_id :'||l_other_asg_act_arch_id,33);
1256 fetch get_asg_details1
1257 into l_start_empl_date,
1258 l_date_of_leaving,
1259 l_payment_date,
1260 l_starter_declaration,
1261 l_student_loan_indicator,
1262 l_stay_over_six_months,
1263 l_stay_less_than_six_months,
1264 l_living_abroad,
1265 l_eea_cwc,
1266 l_epm6,
1267 l_occ_pension_indicator,
1268 l_payroll_id,
1269 l_payroll_id_changed_ind,
1270 l_old_payroll_id,
1271 l_irregular_emp_indicator,
1272 l_pay_frequency,
1273 l_week_number,
1274 l_month_number,
1275 l_periods_covered,
1276 l_agg_earnings_indicator,
1277 l_payments_after_leaving_ind,
1278 l_hours_worked,
1279 l_nic_calc_method,
1280 l_tax_week_number,
1281 l_annual_occ_pension,
1282 l_bacs_hash_code;
1283 close get_asg_details1;
1284
1285 open get_asg_details2 (l_other_asg_act_arch_id);
1286 hr_utility.set_location('Inside get_asg_details2 2nd Time l_other_asg_act_arch_id :'||l_other_asg_act_arch_id,33);
1287 fetch get_asg_details2
1288 into l_taxable_pay_to_date,
1289 l_total_tax,
1290 l_student_loan_rec,
1291 l_taxable_pay,
1292 l_payments_not_to_nic,
1293 l_deductions_from_netpay,
1294 l_statutory_deductions,
1295 l_benefits_taxed_payroll,
1299 l_items_class1_nic,
1296 l_benefits_class1_nic,
1297 l_benefits_not_class1_nic,
1298 l_emp_pension_contri_net_pay,
1300 l_emp_pen_contri_not_net_pay,
1301 l_student_loan_repay_rec,
1302 l_tax_deducted,
1303 l_ssp,
1304 l_smp,
1305 l_ospp,
1306 l_sap,
1307 l_aspp,
1308 l_tax_code,
1309 l_week_month_indicator;
1310
1311 close get_asg_details2;
1312
1313 hr_utility.set_location('Creating FPS Assignment XML String',41);
1314
1315
1316 tab_employee(start_empl_date)
1317 := convert_2_xml(l_start_empl_date,'S_DATE');
1318
1319 tab_employee(date_of_leaving)
1320 := convert_2_xml(l_date_of_leaving,'E_DATE');
1321
1322 tab_employee(payment_date)
1323 := convert_2_xml(l_payment_date,'PAY_D');
1324
1325 tab_employee(starter_declaration)
1326 := convert_2_xml(l_starter_declaration,'STARTER_DEC');
1327
1328 tab_employee(student_loan_indicator)
1329 := convert_2_xml(l_student_loan_indicator,'STUD_LOAN_IND');
1330
1331 tab_employee(stay_over_six_months)
1332 := convert_2_xml(l_stay_over_six_months,'OVER_SIX');
1333
1334 tab_employee(stay_less_than_six_months)
1335 := convert_2_xml(l_stay_less_than_six_months,'LESS_SIX');
1336
1337 tab_employee(living_abroad)
1338 := convert_2_xml(l_living_abroad,'LIV_ABR');
1339
1340 tab_employee(eea_cwc)
1341 := convert_2_xml(l_eea_cwc,'EEA_CWC');
1342
1343 tab_employee(epm6)
1344 := convert_2_xml(l_epm6,'EPM6');
1345
1346 tab_employee(occ_pension_indicator)
1347 := convert_2_xml(l_occ_pension_indicator,'OCC_IND');
1348
1349 tab_employee(payroll_id)
1350 := convert_2_xml(l_payroll_id,'PAYID');
1351
1352 tab_employee(payroll_id_changed_ind)
1353 := convert_2_xml(l_payroll_id_changed_ind,'PAY_CHAN_IND');
1354
1355 tab_employee(old_payroll_id)
1356 := convert_2_xml(l_old_payroll_id,'OPAYID');
1357
1358 tab_employee(irregular_emp_indicator)
1359 := convert_2_xml(l_irregular_emp_indicator,'IRR_IND');
1360
1361 tab_employee(pay_frequency)
1362 := convert_2_xml(l_pay_frequency,'PAY_FRE');
1363
1364 tab_employee(week_number)
1365 := convert_2_xml(l_week_number,'WEEK_NO');
1366
1367 tab_employee(month_number)
1368 := convert_2_xml(l_month_number,'MONTH_NO');
1369
1370 tab_employee(periods_covered)
1371 := convert_2_xml(l_periods_covered,'PER_COV');
1372
1373 tab_employee(agg_earnings_indicator)
1374 := convert_2_xml(l_agg_earnings_indicator,'AGG_IND');
1375
1376 tab_employee(payments_after_leaving_ind)
1377 := convert_2_xml(l_payments_after_leaving_ind,'PAY_LEAV_IND');
1378
1379 tab_employee(hours_worked)
1380 := convert_2_xml(l_hours_worked,'HRS');
1381
1382 tab_employee(nic_calc_method)
1383 := convert_2_xml(l_nic_calc_method,'NIC_CALC');
1384
1385 tab_employee(tax_week_number)
1386 := convert_2_xml(l_tax_week_number,'TAX_WEEK');
1387
1388 tab_employee(annual_occ_pension)
1389 := convert_2_xml(l_annual_occ_pension,'ANNUAL_OCC');
1390
1391 tab_employee(taxable_pay_to_date)
1392 := convert_2_xml(l_taxable_pay_to_date,'TAX_PAYD');
1393
1394 tab_employee(total_tax)
1395 := convert_2_xml(l_total_tax,'T_TAX');
1396
1397 tab_employee(student_loan_rec)
1398 := convert_2_xml(l_student_loan_rec,'STUD_LOAN_REC');
1399
1400 tab_employee(taxable_pay)
1401 := convert_2_xml(l_taxable_pay,'TAX_PAY');
1402
1403 tab_employee(payments_not_to_nic)
1404 := convert_2_xml(l_payments_not_to_nic,'PAY_NNIC');
1405
1406 tab_employee(deductions_from_netpay)
1407 := convert_2_xml(l_deductions_from_netpay,'DED_PAY');
1408
1409 tab_employee(statutory_deductions)
1410 := convert_2_xml(l_statutory_deductions,'STA_DED');
1411
1412 tab_employee(benefits_taxed_payroll)
1413 := convert_2_xml(l_benefits_taxed_payroll,'B_PAY');
1414
1415 tab_employee(benefits_class1_nic)
1416 := convert_2_xml(l_benefits_class1_nic,'B_NIC');
1417
1418 tab_employee(benefits_not_class1_nic)
1419 := convert_2_xml(l_benefits_not_class1_nic,'B_NNIC');
1420
1421 tab_employee(emp_pension_contri_net_pay)
1422 := convert_2_xml(l_emp_pension_contri_net_pay,'EMP_PAY');
1423
1424 tab_employee(items_class1_nic)
1425 := convert_2_xml(l_items_class1_nic,'I_NIC');
1426
1427 tab_employee(emp_pen_contri_not_net_pay)
1428 := convert_2_xml(l_emp_pen_contri_not_net_pay,'EMP_PEN_NOT');
1429
1430 tab_employee(student_loan_repay_rec)
1431 := convert_2_xml(l_student_loan_repay_rec,'STUD_LOAN_R_REC');
1432
1433 tab_employee(tax_deducted)
1434 := convert_2_xml(l_tax_deducted,'TAX_DED');
1435
1436 tab_employee(ssp)
1437 := convert_2_xml(l_ssp,'SSP');
1438
1439 tab_employee(smp)
1440 := convert_2_xml(l_smp,'SMP');
1441
1442 tab_employee(ospp)
1443 := convert_2_xml(l_ospp,'OSPP');
1444
1445 tab_employee(sap)
1446 := convert_2_xml(l_sap,'SAP');
1447
1448 tab_employee(aspp)
1449 := convert_2_xml(l_aspp,'ASPP');
1450
1451 tab_employee(tax_code)
1455 := convert_2_xml(l_week_month_indicator,'W1M1_IND');
1452 := convert_2_xml(l_tax_code,'TAX_CODE');
1453
1454 tab_employee(week_month_indicator)
1456
1457 tab_employee(bacs_hash_code)
1458 := convert_2_xml(l_bacs_hash_code,'BACS');
1459
1460
1461 l_final_xml_string := l_final_xml_string||'<G_ASG>'||
1462 tab_employee(start_empl_date)||
1463 tab_employee(date_of_leaving)||
1464 tab_employee(payment_date)||
1465 tab_employee(starter_declaration)||
1466 tab_employee(student_loan_indicator)||
1467 tab_employee(stay_over_six_months)||
1468 tab_employee(stay_less_than_six_months)||
1469 tab_employee(living_abroad)||
1470 tab_employee(eea_cwc)||
1471 tab_employee(epm6)||
1472 tab_employee(occ_pension_indicator)||
1473 tab_employee(payroll_id)||
1474 tab_employee(payroll_id_changed_ind)||
1475 tab_employee(old_payroll_id)||
1476 tab_employee(irregular_emp_indicator)||
1477 tab_employee(pay_frequency)||
1478 tab_employee(week_number)||
1479 tab_employee(month_number)||
1480 tab_employee(periods_covered)||
1481 tab_employee(agg_earnings_indicator)||
1482 tab_employee(payments_after_leaving_ind)||
1483 tab_employee(hours_worked)||
1484 tab_employee(nic_calc_method)||
1485 tab_employee(tax_week_number)||
1486 tab_employee(annual_occ_pension)||
1487
1488 tab_employee(taxable_pay_to_date)||
1489 tab_employee(total_tax)||
1490 tab_employee(student_loan_rec)||
1491 tab_employee(taxable_pay)||
1492 tab_employee(payments_not_to_nic)||
1493 tab_employee(deductions_from_netpay)||
1494 tab_employee(statutory_deductions)||
1495 tab_employee(benefits_taxed_payroll)||
1496 tab_employee(benefits_class1_nic)||
1497 tab_employee(benefits_not_class1_nic)||
1498 tab_employee(emp_pension_contri_net_pay)||
1499 tab_employee(items_class1_nic)||
1500 tab_employee(emp_pen_contri_not_net_pay)||
1501 tab_employee(student_loan_repay_rec)||
1502 tab_employee(tax_deducted)||
1503 tab_employee(ssp)||
1504 tab_employee(smp)||
1505 tab_employee(ospp)||
1506 tab_employee(sap)||
1507 tab_employee(aspp)||
1508 tab_employee(tax_code)||
1509 tab_employee(week_month_indicator)||
1510 tab_employee(bacs_hash_code);
1511
1512 -- To get NI Details of the asg action id
1513 open get_ni_details(l_arch_asg_action_id);
1514 loop
1515 fetch get_ni_details into
1516 l_ni_letter,
1517 l_pay_to_ni,
1518 l_earnings_upto_lel,
1519 l_earnings_upto_pt,
1520 l_earnings_upto_uap,
1521 l_earnings_upto_uel,
1522 l_employer_contrib,
1523 l_employer_contrib_todate,
1524 l_employee_contrib,
1525 l_employee_contrib_todate;
1526 exit when get_ni_details%notfound;
1527
1528 tab_employee(ni_letter)
1529 := convert_2_xml(l_ni_letter,'NI');
1530
1531 tab_employee(pay_to_ni)
1532 := convert_2_xml(l_pay_to_ni,'PAY_NI');
1533
1534 tab_employee(earnings_upto_lel)
1535 := convert_2_xml(l_earnings_upto_lel,'UPTO_LEL');
1536
1537 tab_employee(earnings_upto_pt)
1538 := convert_2_xml(l_earnings_upto_pt,'UPTO_PT');
1539
1540 tab_employee(earnings_upto_uap)
1541 := convert_2_xml(l_earnings_upto_uap,'UPTO_UAP');
1542
1543 tab_employee(earnings_upto_uel)
1544 := convert_2_xml(l_earnings_upto_uel,'UPTO_UEL');
1545
1546 tab_employee(employer_contrib)
1547 := convert_2_xml(l_employer_contrib,'EMR_CON');
1548
1549 tab_employee(employer_contrib_todate)
1550 := convert_2_xml(l_employer_contrib_todate,'EMR_CON_D');
1551
1552 tab_employee(employee_contrib)
1553 := convert_2_xml(l_employee_contrib,'EMP_CON');
1554
1555 tab_employee(employee_contrib_todate)
1556 := convert_2_xml(l_employee_contrib_todate,'EMP_CON_D');
1557
1558
1559
1560 l_final_xml_string := l_final_xml_string||'<G_NI>'||
1561 tab_employee(ni_letter)||
1562 tab_employee(pay_to_ni)||
1563 tab_employee(earnings_upto_lel)||
1564 tab_employee(earnings_upto_pt)||
1565 tab_employee(earnings_upto_uap)||
1566 tab_employee(earnings_upto_uel)||
1567 tab_employee(employer_contrib)||
1568 tab_employee(employer_contrib_todate)||
1569 tab_employee(employee_contrib)||
1570 tab_employee(employee_contrib_todate)||
1571 '</G_NI>';
1572
1573 end loop;
1574 l_final_xml_string := l_final_xml_string||'</G_ASG>';
1575 close get_ni_details;
1576
1577 ------------
1578
1579 end loop;
1580 l_final_xml_string := l_final_xml_string||'</G_EMP>';
1581 hr_utility.set_location('Created Final FPS XML Output:'||l_final_xml_string,42);
1582 close get_other_asg_act_ids;
1583
1584 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1585
1586 end if;
1587
1588 hr_utility.set_location(' Leaving: '||l_proc,43);
1589 END;
1590
1591 END fps_employee_proc;
1592
1593 /**************************************************************************/
1594 PROCEDURE action_creation (pactid in number,
1595 stperson in number,
1596 endperson in number,
1597 chunk in number) IS
1598
1599 l_payroll_id number;
1600 l_business_group_id number;
1601 l_effective_date date;
1602 l_tax_ref varchar2(20);
1603 l_proc CONSTANT VARCHAR2(50):= g_package||'action_creation';
1604 cursor csr_arch_payroll_action_id is
1608 (instr(pact.legislative_parameters,'REP_GROUP=')
1605 select
1606 to_number(substr(pact.legislative_parameters,
1607 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
1609 -
1610 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
1611 )) arch_payroll_action_id
1612 from pay_payroll_actions pact
1613 where pact.payroll_action_id=pactid;
1614
1615
1616
1617 cursor csr_asg (c_arch_payroll_action_id number) is
1618 select distinct paa.assignment_id
1619 from pay_assignment_actions paa,
1620 pay_action_information pai,
1621 per_all_assignments_f paaf
1622 where paa.payroll_action_id = c_arch_payroll_action_id
1623 and pai.action_context_id = paa.assignment_action_id
1624 and pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
1625 and pai.action_context_type = 'AAP'
1626 and paaf.assignment_id = paa.assignment_id
1627 and paaf.person_id between stperson and endperson;
1628
1629 lockingactid number;
1630 lv_assignment_id number;
1631 l_arch_payroll_action_id number;
1632 BEGIN
1633 hr_utility.set_location('Entering: '||l_proc,53);
1634 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),54);
1635
1636 open csr_arch_payroll_action_id;
1637 fetch csr_arch_payroll_action_id into l_arch_payroll_action_id;
1638 close csr_arch_payroll_action_id;
1639
1640 hr_utility.set_location('RTI FPS Archive Payroll action ='|| to_char(l_arch_payroll_action_id),55);
1641
1642 open csr_asg(l_arch_payroll_action_id);
1643 loop
1644 fetch csr_asg into lv_assignment_id;
1645 exit when csr_asg%notfound;
1646 hr_utility.set_location('Assignment ID :' || lv_assignment_id,56);
1647
1648 -- Create one assignment action for every assignment
1649 hr_utility.set_location('Archiving for assignment_id '||lv_assignment_id, 57);
1650 select pay_assignment_actions_s.nextval
1651 into lockingactid
1652 from dual;
1653
1654 -- Insert assignment into pay_assignment_actions
1655 hr_nonrun_asact.insact
1656 (
1657 lockingactid,
1658 lv_assignment_id,
1659 pactid,
1660 chunk,
1661 null
1662 );
1663
1664 end loop;
1665 close csr_asg;
1666
1667 hr_utility.set_location('Leaving: '||l_proc,58);
1668 --
1669 END action_creation;
1670
1671 end PAY_GB_RTI_FPS_OUTPUT;