[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RTI_OUTPUT
Source
1 PACKAGE BODY PAY_GB_RTI_OUTPUT as
2 /* $Header: pygbrtio.pkb 120.2.12020000.2 2013/03/09 06:36:45 ssanjays noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name:
9 PAY_GB_RTI_OUTPUT
10 Purpose:
11 It fetches the archived data for RTI 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 21-Oct-2011 krreddy 115.0 13076448 Created.
17 22-Oct-2011 krreddy 115.1 13076448 Modified not equals check to <> to
18 resolve GSCC Error.
19 12-Jan-2012 rajganga 115.2 13076448 Modified ADDRESS_LINE5 to COUNTRY.
20 20-Apr-2012 rajganga 115.3 13076448 Modified the contexts.
21 26-Apr-2012 rajganga 115.4 13813786 Added checks in other asg cursor
22 to fetch archived asgs.
23 =============================================================================*/
24 --
25 --
26
27 g_debug BOOLEAN := hr_utility.debug_enabled;
28 EOL VARCHAR2(5) := fnd_global.local_chr(10);
29
30 TYPE act_info_rec IS RECORD
31 ( assignment_id number(20)
32 ,person_id number(20)
33 ,effective_date date
34 ,action_info_category varchar2(50)
35 ,act_info1 varchar2(300)
36 ,act_info2 varchar2(300)
37 ,act_info3 varchar2(300)
38 ,act_info4 varchar2(300)
39 ,act_info5 varchar2(300)
40 ,act_info6 varchar2(300)
41 ,act_info7 varchar2(300)
42 ,act_info8 varchar2(300)
43 ,act_info9 varchar2(300)
44 ,act_info10 varchar2(300)
45 ,act_info11 varchar2(300)
46 ,act_info12 varchar2(300)
47 ,act_info13 varchar2(300)
48 ,act_info14 varchar2(300)
49 ,act_info15 varchar2(300)
50 ,act_info16 varchar2(300)
51 ,act_info17 varchar2(300)
52 ,act_info18 varchar2(300)
53 ,act_info19 varchar2(300)
54 ,act_info20 varchar2(300)
55 ,act_info21 varchar2(300)
56 ,act_info22 varchar2(300)
57 ,act_info23 varchar2(300)
58 ,act_info24 varchar2(300)
59 ,act_info25 varchar2(300)
60 ,act_info26 varchar2(300)
61 ,act_info27 varchar2(300)
62 ,act_info28 varchar2(300)
63 ,act_info29 varchar2(300)
64 ,act_info30 varchar2(300)
65 );
66
67 TYPE action_info_table IS TABLE OF
68 act_info_rec INDEX BY BINARY_INTEGER;
69 ---
70
71
72 TYPE g_tax_rec IS RECORD(
73 tax_code VARCHAR2(20),
74 tax_basis VARCHAR2(20),
75 prev_paid VARCHAR2(20),
76 prev_tax VARCHAR2(20));
77
78 g_package CONSTANT VARCHAR2(20):= 'pay_gb_rti_output.';
79 --
80 --
81 /*------------- PRIVATE PROCEDURE -----------------*/
82 --
83 --
84
85 ---------------------
86 /**************************************************************************/
87 PROCEDURE range_cursor (pactid IN NUMBER,
88 sqlstr OUT NOCOPY VARCHAR2)
89 IS
90 l_test_indicator varchar2(1);
91 l_test_id varchar2(8);
92 l_report_type varchar2(15);
93 test_indicator_error EXCEPTION;
94 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
95 BEGIN
96 hr_utility.set_location('Entering: '||l_proc,1);
97
98 sqlstr := 'select distinct person_id '||
99 'from per_people_f ppf, '||
100 'pay_payroll_actions ppa '||
101 'where ppa.payroll_action_id = :pactid '||
102 'and ppa.business_group_id = ppf.business_group_id '||
103 'order by ppf.person_id';
104
105
106 hr_utility.trace(' Range Cursor Statement : '||sqlstr);
107 hr_utility.set_location(' Leaving: '||l_proc,100);
108 END range_cursor;
109 --
110 --
111
112
113 FUNCTION convert_2_xml(p_data IN VARCHAR2,
114 p_tag IN VARCHAR2,
115 p_datatype IN CHAR DEFAULT 'T',
116 p_format IN VARCHAR2 DEFAULT NULL,
117 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
118 RETURN VARCHAR2 IS
119
120 l_data VARCHAR2(4000);
121 l_output VARCHAR2(4000);
122 EOL VARCHAR2(5);
123 l_proc CONSTANT VARCHAR2(35):= g_package||'convert_2_xml';
124 BEGIN
125 hr_utility.set_location('Entering: '||l_proc,1);
126
127 SELECT
128 fnd_global.local_chr(13) || fnd_global.local_chr(10)
129 INTO EOL
130 FROM dual;
131 IF p_null_allowed = 'N'
132 AND (TRIM(p_data) IS NULL
133 OR (p_datatype IN ('N','C')
134 AND to_number(p_data) = 0)) THEN
135 RETURN ' ';
136 END IF;
137 l_data := trim(p_data);
138 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
139 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
140 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
141 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
142 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
143 --------------------------------------------------------
144 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
145 --------------------------------------------------------
146
147 IF p_datatype = 'T' OR p_datatype = 'D' THEN
148 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
149 || '</' || trim(p_tag) || '>'||EOL;
150
151 ELSIF p_datatype = 'N' OR p_datatype = 'C' THEN
152 IF TRIM(p_format) IS NOT NULL THEN
153 SELECT to_char(to_number(p_data), p_format)
154 INTO l_data FROM dual;
155 ELSIF p_datatype = 'C' THEN -- Currency should be two decimal places
156 SELECT to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
157 INTO l_data FROM dual;
158 END IF;
159 l_output := '<' || trim(p_tag) || '>' || trim(l_data)
160 || '</' || trim(p_tag) || '>'||EOL;
161 END IF;
162 hr_utility.set_location(' Leaving: '||l_proc,2);
163 RETURN l_output;
164 END convert_2_xml;
165
166 /*****************************************************************************/
167
168 PROCEDURE end_of_file IS
169 l_proc CONSTANT VARCHAR2(35):= g_package||'end_of_file';
170 BEGIN
171 DECLARE
172 l_final_xml_string VARCHAR2(32000);
173 BEGIN
174
175 hr_utility.set_location('Entering: '||l_proc,3);
176
177 l_final_xml_string := '</G>';
178
179 hr_utility.trace(l_final_xml_string );
180 pay_core_files.write_to_magtape_lob(l_final_xml_string);
181 hr_utility.set_location(' Leaving: '||l_proc,4);
182
183 END;
184 END end_of_file;
185 /*****************************************************************************/
186
187 /*****************************************************************************/
188 PROCEDURE eas_header_proc is
189 BEGIN
190 DECLARE
191 l_final_xml_string VARCHAR2(32000);
192 l_payroll_prod_ver VARCHAR2(50);
193 l_employer_name VARCHAR2(50);
194 l_hmrc_office_no VARCHAR2(50);
195 l_employer_paye_ref VARCHAR2(50);
196 l_employer_ao_ref VARCHAR2(50);
197 l_sender_id VARCHAR2(50);
198 l_test_indicator VARCHAR2(50);
199 l_header_value VARCHAR2(50);
200 l_system_date VARCHAR2(50);
201 l_system_time VARCHAR2(50);
202 EOL VARCHAR2(5) := fnd_global.local_chr(13)
203 || fnd_global.local_chr(10);
204 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
205 select
206 to_number(substr(pact.legislative_parameters,
207 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
208 (instr(pact.legislative_parameters,'REP_GROUP=')
209 -
210 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
211 )) arch_payroll_action_id
212 from pay_payroll_actions pact
213 where pact.payroll_action_id = c_mag_payroll_actid;
214
215 cursor get_heder_details(c_arch_pay_action_id number) is
216 select pai.action_information8 PAYROLL_PROD_VER,
217 pai.action_information4 EMPLOYER_NAME,
218 pai.action_information6 HMRC_OFFICE_NO,
219 pai.action_information7 EMPLOYER_PAYE_REF,
220 pai.action_information11 EMPLOYER_AO_REF,
221 pai.action_information12 SENDER_ID,
222 pai.action_information13 TEST_INDICATOR,
223 pai.action_information14 HEADER_VALUE
224 from pay_payroll_actions ppa,
225 pay_action_information pai
226 where ppa.payroll_action_id = c_arch_pay_action_id
227 and ppa.payroll_action_id = pai.action_context_id
228 and pai.action_information_category='RTI PAYROLL INFO'
229 and pai.action_context_type='PA';
230
231 TYPE header_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
232 tab_header header_info;
233
234 sender_id number :=1;
235 test_indicator number :=2;
236 system_date number :=3;
237 system_time number :=4;
238 header_value number :=5;
239 vendor_id number :=6;
240 developer_name number :=7;
241 payroll_prod_name number :=8;
242 payroll_prod_ver number :=9;
243 employer_name number :=10;
244 hmrc_office_no number :=11;
245 employer_paye_ref number :=12;
246 employer_ao_ref number :=13;
247 --system_date number :=14;
248 l_mag_payroll_actid number;
249 l_arch_pay_action_id number;
250
251
252 BEGIN
253 hr_utility.trace('+==========================================================+');
254 hr_utility.trace('Entering eas_header_proc');
255
256 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
257 hr_utility.trace('RTI EAS Mag Payroll action ='||l_mag_payroll_actid);
258
259 open csr_arch_payroll_action_id(l_mag_payroll_actid);
260 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
261 close csr_arch_payroll_action_id;
262
263 hr_utility.trace('RTI EAS Archive Payroll action ='||l_arch_pay_action_id);
264 open get_heder_details(l_arch_pay_action_id);
265 fetch get_heder_details
266 into l_payroll_prod_ver,
267 l_employer_name,
268 l_hmrc_office_no,
269 l_employer_paye_ref,
270 l_employer_ao_ref,
271 l_sender_id,
272 l_test_indicator,
273 l_header_value;
274 close get_heder_details;
275
276 select substr(to_char(sysdate, 'YYYYMMDD'),1,8),substr(to_char(sysdate, 'HH24MISS'),1,6) into l_system_date,l_system_time from dual;
277
278 tab_header(payroll_prod_ver)
279 := convert_2_xml(l_payroll_prod_ver,'PAYROLL_PROD_VER');
280
281 tab_header(employer_name)
282 := convert_2_xml(l_employer_name,'EMPLOYER_NAME');
283
284 tab_header(hmrc_office_no)
285 := convert_2_xml(l_hmrc_office_no,'HMRC_OFFICE_NO');
286
287 tab_header(employer_paye_ref)
288 := convert_2_xml(l_employer_paye_ref,'EMPLOYER_PAYE_REF');
289
290 tab_header(employer_ao_ref)
291 := convert_2_xml(l_employer_ao_ref,'EMPLOYER_AO_REF');
292
293 tab_header(sender_id)
294 := convert_2_xml(l_sender_id,'SENDER_ID');
295
296 tab_header(test_indicator)
297 := convert_2_xml(l_test_indicator,'TEST_INDICATOR');
298
299 tab_header(header_value)
300 := convert_2_xml(l_header_value,'HEADER_VALUE');
301
302 tab_header(system_date)
303 := convert_2_xml(l_system_date,'SYSTEM_DATE');
304
305 tab_header(system_time)
306 := convert_2_xml(l_system_time,'SYSTEM_TIME');
307
308 l_final_xml_string := '<G>
309 <G_HEADER>' || EOL||
310 tab_header(sender_id)||
311 tab_header(test_indicator)||
312 tab_header(system_date)||
313 tab_header(system_time)||
314 tab_header(header_value)||
315 '<VENDOR_ID>1234</VENDOR_ID>
316 ' || EOL||
317 tab_header(payroll_prod_ver)||
318 tab_header(employer_name)||
319 tab_header(employer_paye_ref)||
320 tab_header(employer_ao_ref)||
321 tab_header(hmrc_office_no)||EOL||
322 tab_header(system_date)||
323 '</G_HEADER>';
324
325 pay_core_files.write_to_magtape_lob(l_final_xml_string);
326 hr_utility.trace('Final output : '||l_final_xml_string );
327 hr_utility.trace('X==========================================================X');
328
329 END;
330 END eas_header_proc;
331
332 /*****************************************************************************/
333
334
335 /*****************************************************************************/
336 PROCEDURE nino_header_proc IS
337 l_proc CONSTANT VARCHAR2(35):= g_package||'nino_header_proc';
338 BEGIN
339 DECLARE
340 l_final_xml_string VARCHAR2(32000);
341 l_payroll_prod_ver VARCHAR2(50);
342 l_employer_name VARCHAR2(50);
343 l_hmrc_office_no VARCHAR2(50);
344 l_employer_paye_ref VARCHAR2(50);
345 l_employer_ao_ref VARCHAR2(50);
346 l_sender_id VARCHAR2(50);
347 l_test_indicator VARCHAR2(50);
348 l_header_value VARCHAR2(50);
349 l_system_date VARCHAR2(50);
350 l_system_time VARCHAR2(50);
351 EOL VARCHAR2(5) := fnd_global.local_chr(13)
352 || fnd_global.local_chr(10);
353
354 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
355 select
356 to_number(substr(pact.legislative_parameters,
357 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
358 (instr(pact.legislative_parameters,'REP_GROUP=')
359 -
360 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
361 )) arch_payroll_action_id
362 from pay_payroll_actions pact
363 where pact.payroll_action_id = c_mag_payroll_actid;
364
365 cursor get_heder_details(c_arch_pay_action_id number) is
366 select pai.action_information8 PAYROLL_PROD_VER,
367 pai.action_information4 EMPLOYER_NAME,
368 pai.action_information6 HMRC_OFFICE_NO,
369 pai.action_information7 EMPLOYER_PAYE_REF,
370 pai.action_information11 EMPLOYER_AO_REF,
371 pai.action_information12 SENDER_ID,
372 pai.action_information13 TEST_INDICATOR,
373 pai.action_information14 HEADER_VALUE
374 from pay_payroll_actions ppa,
375 pay_action_information pai
376 where ppa.payroll_action_id = c_arch_pay_action_id
377 and ppa.payroll_action_id = pai.action_context_id
378 and pai.action_information_category='RTI PAYROLL INFO'
379 and pai.action_context_type='PA';
380
381 TYPE header_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
382 tab_header header_info;
383
384 sender_id number :=1;
385 test_indicator number :=2;
386 system_date number :=3;
387 system_time number :=4;
388 header_value number :=5;
389 vendor_id number :=6;
390 developer_name number :=7;
391 payroll_prod_name number :=8;
392 payroll_prod_ver number :=9;
393 employer_name number :=10;
394 hmrc_office_no number :=11;
395 employer_paye_ref number :=12;
396 employer_ao_ref number :=13;
397 --system_date number :=14;
398 l_mag_payroll_actid number;
399 l_arch_pay_action_id number;
400
401 BEGIN
402 hr_utility.set_location(' Entering: '||l_proc,11);
403
407 open csr_arch_payroll_action_id(l_mag_payroll_actid);
404 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
405 hr_utility.set_location('RTI NINO Mag Payroll action ='||l_mag_payroll_actid,12);
406
408 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
409 close csr_arch_payroll_action_id;
410
411 hr_utility.set_location('RTI NINO Archive Payroll action ='||l_arch_pay_action_id,13);
412 open get_heder_details(l_arch_pay_action_id);
413 fetch get_heder_details
414 into l_payroll_prod_ver,
415 l_employer_name,
416 l_hmrc_office_no,
417 l_employer_paye_ref,
418 l_employer_ao_ref,
419 l_sender_id,
420 l_test_indicator,
421 l_header_value;
422 close get_heder_details;
423 -- Embedding Strings into XML tags
424 tab_header(payroll_prod_ver)
425 := convert_2_xml(l_payroll_prod_ver,'PAYROLL_PROD_VER');
426
427 tab_header(employer_name)
428 := convert_2_xml(l_employer_name,'EMPLOYER_NAME');
429
430 tab_header(hmrc_office_no)
431 := convert_2_xml(l_hmrc_office_no,'HMRC_OFFICE_NO');
432
433 tab_header(employer_paye_ref)
434 := convert_2_xml(l_employer_paye_ref,'EMPLOYER_PAYE_REF');
435
436 tab_header(employer_ao_ref)
437 := convert_2_xml(l_employer_ao_ref,'EMPLOYER_AO_REF');
438
439 tab_header(sender_id)
440 := convert_2_xml(l_sender_id,'SENDER_ID');
441
442 tab_header(test_indicator)
443 := convert_2_xml(l_test_indicator,'TEST_INDICATOR');
444
445 tab_header(header_value)
446 := convert_2_xml(l_header_value,'HEADER_VALUE');
447
448 tab_header(system_date)
449 := convert_2_xml(l_system_date,'SYSTEM_DATE');
450
451 tab_header(system_time)
452 := convert_2_xml(l_system_time,'SYSTEM_TIME');
453
454 -- Concatenation of XML Fields to create the Header
455
456 hr_utility.set_location('Creating NINO Header XML String',12);
457
458 l_final_xml_string := '<G>
459 <G_HEADER>' || EOL||
460 tab_header(sender_id)||
461 tab_header(test_indicator)||
462 tab_header(system_date)||
463 tab_header(system_time)||
464 tab_header(header_value)||
465 '<VENDOR_ID>1234</VENDOR_ID>
466 <DEVELOPER_NAME>Oracle</DEVELOPER_NAME>
467 <PAYROLL_PROD_NAME>EBS</PAYROLL_PROD_NAME>'
468 || EOL||
469 tab_header(payroll_prod_ver)||
470 tab_header(employer_name)||
471 tab_header(employer_paye_ref)||
472 tab_header(employer_ao_ref)||
473 tab_header(hmrc_office_no)||EOL||
474 tab_header(system_date)||
475 '</G_HEADER>';
476
477 hr_utility.set_location('Created NINO Header XML String',13);
478 pay_core_files.write_to_magtape_lob(l_final_xml_string);
479 hr_utility.set_location(' Leaving: '||l_proc,14);
480
481 END;
482 END nino_header_proc;
483 /*****************************************************************************/
484
485 PROCEDURE eas_employee_proc IS
486
487 /*
488 This procedure has the following sections:
489 1. Generate Employee data related Tags
490 This set of tags repeat for each Employee record.
491 2. Generate Assignment data related Tags
492 This set of tags repeat for each assignment for the above Employee record.
493 3. Generate the Final XML
494 Final XML tag will have the below structure:
495 <EMP1>
496 <ASG1>
497 <ASG2>
498 </EMP1>
499 <EMP2>
500 <ASG1>
501 <ASG2>
502 </EMP2>
503 */
504
505 l_proc CONSTANT VARCHAR2(35):= g_package||'eas_employee_proc';
506 BEGIN
507 DECLARE
508 l_mag_payroll_actid number;
509 l_mag_asg_action_id number;
510 l_arch_asg_action_id number;
511 l_arch_pay_action_id number;
512 EOL varchar2(5) := fnd_global.local_chr(13)
513 || fnd_global.local_chr(10);
514
515 l_other_asg_arch_id number;
516 l_other_asg_act_arch_id number;
517
518 TYPE employee_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
519 tab_employee employee_info;
520
521 l_address_line1 varchar2(100);
522 l_address_line2 varchar2(100);
523 l_address_line3 varchar2(100);
524 l_address_line4 varchar2(100);
525 l_country varchar2(100);
526
527 l_last_name varchar2(100);
528 l_first_name varchar2(100);
529 l_second_name varchar2(100);
530 l_title varchar2(100);
531 l_postal_code varchar2(100);
532 l_ni_number varchar2(100);
533 l_dob varchar2(100);
534 l_gender varchar2(100);
535 l_payroll_id varchar2(100);
536 l_emp_indicator varchar2(100);
537 l_start_empl_date varchar2(100);
538 l_date_of_leaving varchar2(100);
539 l_tax_code varchar2(100);
540 l_tax_basis varchar2(1);
541
542 address_line1 number :=1;
543 address_line2 number :=2;
544 address_line3 number :=3;
545 address_line4 number :=4;
546 country number :=5;
547
548 last_name number :=6;
552 postal_code number :=10;
549 first_name number :=7;
550 second_name number :=8;
551 title number :=9;
553 ni_number number :=11;
554 dob number :=12;
555 gender number :=13;
556 payroll_id number :=14;
557 emp_indicator number :=15;
558 start_empl_date number :=16;
559 date_of_leaving number :=17;
560 tax_code number :=18;
561 tax_basis number :=19;
562 l_final_xml_string varchar2(32000);
563 l_process_flag varchar2(1);
564
565
566 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
567 select
568 to_number(substr(pact.legislative_parameters,
569 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
570 (instr(pact.legislative_parameters,'REP_GROUP=')
571 -
572 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
573 )) arch_payroll_action_id
574 from pay_payroll_actions pact
575 where pact.payroll_action_id = c_mag_payroll_actid;
576
577 /*
578 Below cursor gets corresponding archiver assignment_action_id for the current magtape assignment_action_id
579 */
580 cursor get_arch_asg_actid
581 (c_mag_payroll_actid number,
582 c_mag_asg_action_id number,
583 c_arch_pay_action_id number) is
584 select paa_arch.assignment_action_id
585 from pay_assignment_actions paa_arch,
586 pay_assignment_actions paa_mag,
587 pay_action_information pai
588 where paa_mag.payroll_action_id = c_mag_payroll_actid
589 and paa_mag.assignment_action_id = c_mag_asg_action_id
590 and paa_arch.payroll_action_id = c_arch_pay_action_id
591
592 and paa_arch.assignment_id = paa_mag.assignment_id
593 and paa_arch.assignment_action_id = pai.action_context_id
594 and pai.action_information_category = 'GB RTI ASG DETAILS'
595 and pai.action_context_type = 'AAP';
596
597 /*
598 Below cursor gets other archiver assignment_action_id for the current archiver assignment_action_id, if any exists
599 */
600 cursor get_other_asg_act_ids (c_arch_pay_action_id number,
601 c_arch_asg_action_id number) is
602
603 select distinct paa_other.assignment_id,
604 paa_other.assignment_action_id
605 from pay_assignment_actions paa_current,
606 per_all_assignments_f paaf_current,
607 pay_assignment_actions paa_other,
608 per_all_assignments_f paaf_other
609 where paa_current.payroll_action_id = c_arch_pay_action_id
610 and paa_current.assignment_action_id = c_arch_asg_action_id
611 and paaf_current.assignment_id = paa_current.assignment_id
612 and paa_other.payroll_action_id = c_arch_pay_action_id
613 and paa_other.assignment_action_id <> c_arch_asg_action_id
614 and paaf_other.assignment_id = paa_other.assignment_id
615 and paaf_other.person_id = paaf_current.person_id
616 and paa_current.ACTION_STATUS = 'C'
617 and paa_other.ACTION_STATUS = 'C';
618
619
620 /*
621 Below cursor gets employee details for current archiver assignment_action_id
622 */
623 cursor get_emp_details (c_arch_asg_action_id number) is
624 select pai_addr.action_information5,
625 pai_addr.action_information6,
626 pai_addr.action_information7,
627 pai_addr.action_information8,
628 pai_addr.action_information13,
629 pai_empl.action_information3,
630 pai_empl.action_information4,
631 pai_empl.action_information5,
632 pai_empl.action_information6,
633 pai_addr.action_information12,
634 pai_empl.action_information7,
635 pai_empl.action_information8,
636 pai_empl.action_information9
637
638 from pay_action_information pai_addr,
639 pay_action_information pai_empl
640 where pai_addr.action_context_id = c_arch_asg_action_id
641 and pai_addr.action_information_category = 'ADDRESS DETAILS'
642 and pai_addr.action_context_type = 'AAP'
643 and pai_empl.action_context_id = c_arch_asg_action_id
644 and pai_empl.action_information_category = 'GB RTI EMPLOYEE DETAILS'
645 and pai_empl.action_context_type = 'AAP';
646
647 /*
648 Below cursor gets asg details for current archiver assignment_action_id
649 */
650 cursor get_asg_details (c_arch_asg_action_id number) is
651 select pai_asg.ACTION_INFORMATION3,
652 pai_asg.ACTION_INFORMATION4,
653 pai_asg.ACTION_INFORMATION5,
654 pai_asg.ACTION_INFORMATION6,
655 pai_asg.ACTION_INFORMATION7,
656 pai_asg.ACTION_INFORMATION8
657 from pay_action_information pai_asg
658 where pai_asg.action_context_id = c_arch_asg_action_id
659 and pai_asg.action_information_category = 'GB RTI ASG DETAILS'
660 and pai_asg.action_context_type = 'AAP';
661
662 BEGIN
663 hr_utility.set_location(' Entering: '||l_proc,15);
664 hr_utility.set_location('Employee Record',16);
665
666 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
667 hr_utility.set_location('RTI EAS Mag Payroll action ='||l_mag_payroll_actid,17);
668
669 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
670 hr_utility.set_location('RTI EAS Mag Assignment action ='|| to_char(l_mag_asg_action_id),18);
671
672
673
674 open csr_arch_payroll_action_id(l_mag_payroll_actid);
678 hr_utility.set_location('RTI EAS Archive Payroll action ='|| to_char(l_arch_pay_action_id),19);
675 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
676 close csr_arch_payroll_action_id;
677
679
680 open get_arch_asg_actid
681 (l_mag_payroll_actid,
682 l_mag_asg_action_id,
683 l_arch_pay_action_id);
684 fetch get_arch_asg_actid
685 into l_arch_asg_action_id;
686 close get_arch_asg_actid;
687
688 hr_utility.set_location('RTI EAS Archive Assignment action ='|| to_char(l_arch_asg_action_id),20);
689
690 l_process_flag := 'Y';
691
692 open get_other_asg_act_ids (l_arch_pay_action_id, l_arch_asg_action_id);
693 hr_utility.set_location('l_arch_pay_action_id = '|| to_char(l_arch_pay_action_id),27);
694 hr_utility.set_location('l_arch_asg_action_id = '|| to_char(l_arch_asg_action_id),28);
695
696 hr_utility.set_location('Inside open get_other_asg_act_ids',21);
697
698 hr_utility.set_location('rowcount check: '||get_other_asg_act_ids%rowcount,23);
699
700
701
702 loop
703 hr_utility.set_location('Inside if1',24);
704 fetch get_other_asg_act_ids
705 into l_other_asg_arch_id,
706 l_other_asg_act_arch_id;
707
708 if l_other_asg_act_arch_id < l_arch_asg_action_id then
709 hr_utility.set_location('Current assignment_action_id '|| to_char(l_arch_asg_action_id)|| 'is already processed,
710 while processing the assignment_id '||l_other_asg_arch_id,25);
711 hr_utility.set_location('Inside if2',26);
712 l_process_flag := 'N';
713 end if;
714
715 if (l_process_flag='N') then
716 exit;
717 end if;
718
719 exit when get_other_asg_act_ids%notfound;
720 end loop;
721 hr_utility.set_location('Outside if1',29);
722
723
724
725 hr_utility.set_location('Outside if2',30);
726 close get_other_asg_act_ids;
727
728 -- Below if will be for complete reporting logic
729 if l_process_flag = 'Y' then
730 hr_utility.set_location('Inside if l_process_flag=Y',31);
731
732
733 open get_emp_details (l_arch_asg_action_id);
734 hr_utility.set_location('Inside get_emp_details l_arch_asg_action_id :'||l_arch_asg_action_id,32);
735 fetch get_emp_details
736 into l_address_line1,
737 l_address_line2,
738 l_address_line3,
739 l_address_line4,
740 l_country,
741 l_last_name,
742 l_first_name,
743 l_second_name,
744 l_title,
745 l_postal_code,
746 l_ni_number,
747 l_dob,
748 l_gender;
749 close get_emp_details;
750 -- Embedding Strings into XML tags
751
752 tab_employee(address_line1)
753 := convert_2_xml(l_address_line1,'ADDRESS_LINE1');
754 tab_employee(address_line2)
755 := convert_2_xml(l_address_line2,'ADDRESS_LINE2');
756 tab_employee(address_line3)
757 := convert_2_xml(l_address_line3,'ADDRESS_LINE3');
758 tab_employee(address_line4)
759 := convert_2_xml(l_address_line4,'ADDRESS_LINE4');
760 tab_employee(country)
761 := convert_2_xml(l_country,'COUNTRY');
762
763 tab_employee(last_name)
764 := convert_2_xml(l_last_name,'LAST_NAME');
765 tab_employee(first_name)
766 := convert_2_xml(l_first_name,'FIRST_NAME');
767 tab_employee(second_name)
768 := convert_2_xml(l_second_name,'SECOND_NAME');
769 tab_employee(title)
770 := convert_2_xml(l_title,'TITLE');
771 tab_employee(postal_code)
772 := convert_2_xml(l_postal_code,'POSTAL_CODE');
773 tab_employee(ni_number)
774 := convert_2_xml(l_ni_number,'NI_NUMBER');
775 tab_employee(dob)
776 := convert_2_xml(l_dob,'DOB');
777 tab_employee(gender)
778 := convert_2_xml(l_gender,'GENDER');
779
780 open get_asg_details (l_arch_asg_action_id);
781 hr_utility.set_location('Inside get_asg_details l_arch_asg_action_id :'||l_arch_asg_action_id,33);
782 fetch get_asg_details
783 into l_payroll_id,
784 l_emp_indicator,
785 l_start_empl_date,
786 l_date_of_leaving,
787 l_tax_code,
788 l_tax_basis;
789 close get_asg_details;
790
791 tab_employee(payroll_id)
792 := convert_2_xml(l_payroll_id,'PAYROLL_ID');
793 tab_employee(emp_indicator)
794 := convert_2_xml(l_emp_indicator,'EMP_INDICATOR');
795 tab_employee(start_empl_date)
796 := convert_2_xml(l_start_empl_date,'START_EMPL_DATE');
797 tab_employee(date_of_leaving)
798 := convert_2_xml(l_date_of_leaving,'DATE_OF_LEAVING');
799 tab_employee(tax_code)
800 := convert_2_xml(l_tax_code,'TAX_CODE');
801 tab_employee(tax_basis)
802 := convert_2_xml(l_tax_basis,'TAX_BASIS');
803
804 hr_utility.set_location('Creating EAS Employee XML String',34);
805 l_final_xml_string := '<G_EMP>'
806 ||EOL||
807 tab_employee(address_line1)||
808 tab_employee(address_line2)||
809 tab_employee(address_line3)||
810 tab_employee(address_line4)||
811 tab_employee(country)||
812 tab_employee(last_name)||
813 tab_employee(first_name)||
814 tab_employee(second_name)||
815 tab_employee(title)||
816 tab_employee(postal_code)||
817 tab_employee(dob)||
821 hr_utility.set_location('Creating EAS Assignment XML String',35);
818 tab_employee(ni_number)||
819 tab_employee(gender);
820
822 l_final_xml_string := l_final_xml_string||'<G_ASG>'||
823 tab_employee(payroll_id)||
824 tab_employee(emp_indicator)||
825 tab_employee(start_empl_date)||
826 tab_employee(date_of_leaving)||
827 tab_employee(tax_code)||
828 tab_employee(tax_basis)||
829 '</G_ASG>';
830
831 open get_other_asg_act_ids(l_arch_pay_action_id, l_arch_asg_action_id );
832 hr_utility.set_location('Inside get_other_asg_act_ids 2nd time l_arch_pay_action_id :'||l_arch_pay_action_id,36);
833 hr_utility.set_location('Inside get_other_asg_act_ids 2nd time l_arch_asg_action_id :'||l_arch_asg_action_id,37);
834
835 loop
836 fetch get_other_asg_act_ids
837 into l_other_asg_arch_id,
838 l_other_asg_act_arch_id;
839 hr_utility.set_location('l_other_asg_arch_id :'||l_other_asg_arch_id,38);
840 hr_utility.set_location('l_other_asg_act_arch_id :'||l_other_asg_act_arch_id,39);
841
842 exit when get_other_asg_act_ids%notfound;
843 open get_asg_details (l_other_asg_act_arch_id);
844 hr_utility.set_location('Inside get_asg_details 2nd time l_other_asg_act_arch_id :'||l_other_asg_act_arch_id,40);
845 fetch get_asg_details
846 into l_payroll_id,
847 l_emp_indicator,
848 l_start_empl_date,
849 l_date_of_leaving,
850 l_tax_code,
851 l_tax_basis;
852 close get_asg_details;
853 hr_utility.set_location('Creating EAS Assignment XML String',41);
854 tab_employee(payroll_id)
855 := convert_2_xml(l_payroll_id,'PAYROLL_ID');
856 tab_employee(emp_indicator)
857 := convert_2_xml(l_emp_indicator,'EMP_INDICATOR');
858 tab_employee(start_empl_date)
859 := convert_2_xml(l_start_empl_date,'START_EMPL_DATE');
860 tab_employee(date_of_leaving)
861 := convert_2_xml(l_date_of_leaving,'DATE_OF_LEAVING');
862 tab_employee(tax_code)
863 := convert_2_xml(l_tax_code,'TAX_CODE');
864 tab_employee(tax_basis)
865 := convert_2_xml(l_tax_basis,'TAX_BASIS');
866
867 l_final_xml_string := l_final_xml_string||'<G_ASG>'||
868 tab_employee(payroll_id)||
869 tab_employee(emp_indicator)||
870 tab_employee(start_empl_date)||
871 tab_employee(date_of_leaving)||
872 tab_employee(tax_code)||
873 tab_employee(tax_basis)||
874 '</G_ASG>';
875 end loop;
876 l_final_xml_string := l_final_xml_string||'</G_EMP>';
877 hr_utility.set_location('Created Final EAS XML Output:'||l_final_xml_string,42);
878 close get_other_asg_act_ids;
879
880
881 pay_core_files.write_to_magtape_lob(l_final_xml_string);
882
883 end if;
884
885 hr_utility.set_location(' Leaving: '||l_proc,43);
886 END;
887
888 END eas_employee_proc;
889
890
891 /**************************************************************************/
892
893
894 PROCEDURE nino_employee_proc IS
895
896 /*
897 This procedure has the following sections:
898 1. Generate Employee data related Tags
899 This set of tags repeat for each Employee record.
900 2. Generate the Final XML
901 */
902 l_proc CONSTANT VARCHAR2(50):= g_package||'nino_employee_proc';
903 BEGIN
904 DECLARE
905 l_mag_payroll_actid number;
906 l_mag_asg_action_id number;
907 l_arch_asg_action_id number;
908 l_arch_pay_action_id number;
909 EOL varchar2(5) := fnd_global.local_chr(13)
910 || fnd_global.local_chr(10);
911
912 l_other_asg_arch_id number;
913 l_other_asg_act_arch_id number;
914
915 TYPE employee_info IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
916 tab_employee employee_info;
917
918 l_address_line1 varchar2(100);
919 l_address_line2 varchar2(100);
920 l_address_line3 varchar2(100);
921 l_address_line4 varchar2(100);
922 l_country varchar2(100);
923 l_last_name varchar2(100);
924 l_first_name varchar2(100);
925 l_second_name varchar2(100);
926 l_title varchar2(100);
927 l_postal_code varchar2(100);
928 l_ni_number varchar2(100);
929 l_dob varchar2(100);
930 l_gender varchar2(100);
931 address_line1 number :=1;
932 address_line2 number :=2;
933 address_line3 number :=3;
934 address_line4 number :=4;
935 country number :=5;
936 last_name number :=6;
937 first_name number :=7;
938 second_name number :=8;
939 title number :=9;
940 postal_code number :=10;
941 ni_number number :=11;
942 dob number :=12;
943 gender number :=13;
944 l_final_xml_string varchar2(32000);
945 l_process_flag varchar2(1);
946
947
948 cursor csr_arch_payroll_action_id(c_mag_payroll_actid number) is
949 select
950 to_number(substr(pact.legislative_parameters,
951 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
952 (instr(pact.legislative_parameters,'REP_GROUP=')
953 -
954 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
958
955 )) arch_payroll_action_id
956 from pay_payroll_actions pact
957 where pact.payroll_action_id = c_mag_payroll_actid;
959 /*
960 Below cursor gets corresponding archiver assignment_action_id for the current magtape assignment_action_id
961 */
962 cursor get_arch_asg_actid
963 (c_mag_payroll_actid number,
964 c_mag_asg_action_id number,
965 c_arch_pay_action_id number) is
966 select paa_arch.assignment_action_id
967 from pay_assignment_actions paa_arch,
968 pay_assignment_actions paa_mag,
969 pay_action_information pai
970 where paa_mag.payroll_action_id = c_mag_payroll_actid
971 and paa_mag.assignment_action_id = c_mag_asg_action_id
972 and paa_arch.payroll_action_id = c_arch_pay_action_id
973
974 and paa_arch.assignment_id = paa_mag.assignment_id
975 and paa_arch.assignment_action_id = pai.action_context_id
976 and pai.action_information_category = 'GB RTI EMPLOYEE DETAILS'
977 and pai.action_context_type = 'AAP';
978
979
980
981 /*
982 Below cursor gets employee details for current archiver assignment_action_id
983 */
984 cursor get_emp_details (c_arch_asg_action_id number) is
985 select pai_addr.action_information5,
986 pai_addr.action_information6,
987 pai_addr.action_information7,
988 pai_addr.action_information8,
989 pai_addr.action_information13,
990 pai_empl.action_information3,
991 pai_empl.action_information4,
992 pai_empl.action_information5,
993 pai_empl.action_information6,
994 pai_addr.action_information12,
995 pai_empl.action_information7,
996 pai_empl.action_information8,
997 pai_empl.action_information9
998
999 from pay_action_information pai_addr,
1000 pay_action_information pai_empl
1001 where pai_addr.action_context_id = c_arch_asg_action_id
1002 and pai_addr.action_information_category = 'ADDRESS DETAILS'
1003 and pai_addr.action_context_type = 'AAP'
1004 and pai_empl.action_context_id = c_arch_asg_action_id
1005 and pai_empl.action_information_category = 'GB RTI EMPLOYEE DETAILS'
1006 and pai_empl.action_context_type = 'AAP';
1007
1008
1009 BEGIN
1010 hr_utility.set_location(' Entering: '||l_proc,44);
1011 hr_utility.set_location('Employee Record',45);
1012
1013 l_mag_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1014 hr_utility.set_location('RTI NINO Mag Payroll action ='||l_mag_payroll_actid,46);
1015
1016 l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
1017 hr_utility.set_location('RTI NINO Mag Assignment action ='|| to_char(l_mag_asg_action_id),47);
1018
1019
1020
1021 open csr_arch_payroll_action_id(l_mag_payroll_actid);
1022 fetch csr_arch_payroll_action_id into l_arch_pay_action_id;
1023 close csr_arch_payroll_action_id;
1024
1025 hr_utility.set_location('RTI NINO Archive Payroll action ='|| to_char(l_arch_pay_action_id),48);
1026
1027 open get_arch_asg_actid
1028 (l_mag_payroll_actid,
1029 l_mag_asg_action_id,
1030 l_arch_pay_action_id);
1031 fetch get_arch_asg_actid
1032 into l_arch_asg_action_id;
1033 close get_arch_asg_actid;
1034
1035 hr_utility.set_location('RTI NINO Archive Assignment action ='|| to_char(l_arch_asg_action_id),49);
1036
1037 l_process_flag := 'Y';
1038
1039
1040 open get_emp_details (l_arch_asg_action_id);
1041 hr_utility.set_location('Inside get_emp_details l_arch_asg_action_id :'||l_arch_asg_action_id,50);
1042 fetch get_emp_details
1043 into l_address_line1,
1044 l_address_line2,
1045 l_address_line3,
1046 l_address_line4,
1047 l_country,
1048 l_last_name,
1049 l_first_name,
1050 l_second_name,
1051 l_title,
1052 l_postal_code,
1053 l_ni_number,
1054 l_dob,
1055 l_gender;
1056 close get_emp_details;
1057 -- Embedding Strings into XML tags
1058 tab_employee(address_line1)
1059 := convert_2_xml(l_address_line1,'ADDRESS_LINE1');
1060 tab_employee(address_line2)
1061 := convert_2_xml(l_address_line2,'ADDRESS_LINE2');
1062 tab_employee(address_line3)
1063 := convert_2_xml(l_address_line3,'ADDRESS_LINE3');
1064 tab_employee(address_line4)
1065 := convert_2_xml(l_address_line4,'ADDRESS_LINE4');
1066 tab_employee(country)
1067 := convert_2_xml(l_country,'COUNTRY');
1068
1069 tab_employee(last_name)
1070 := convert_2_xml(l_last_name,'LAST_NAME');
1071 tab_employee(first_name)
1072 := convert_2_xml(l_first_name,'FIRST_NAME');
1073 tab_employee(second_name)
1074 := convert_2_xml(l_second_name,'SECOND_NAME');
1075 tab_employee(title)
1076 := convert_2_xml(l_title,'TITLE');
1077 tab_employee(postal_code)
1078 := convert_2_xml(l_postal_code,'POSTAL_CODE');
1079 tab_employee(ni_number)
1080 := convert_2_xml(l_ni_number,'NI_NUMBER');
1081 tab_employee(dob)
1082 := convert_2_xml(l_dob,'DOB');
1083 tab_employee(gender)
1084 := convert_2_xml(l_gender,'GENDER');
1085
1086 l_final_xml_string := '<G_EMP>
1087 <EMPLOYEE_NO>000001</EMPLOYEE_NO>'||EOL||
1088 tab_employee(address_line1)||
1089 tab_employee(address_line2)||
1090 tab_employee(address_line3)||
1094 tab_employee(first_name)||
1091 tab_employee(address_line4)||
1092 tab_employee(country)||
1093 tab_employee(last_name)||
1095 tab_employee(second_name)||
1096 tab_employee(title)||
1097 tab_employee(postal_code)||
1098 tab_employee(ni_number)||
1099 tab_employee(gender)||
1100 tab_employee(dob);
1101
1102
1103
1104
1105 l_final_xml_string := l_final_xml_string||'</G_EMP>';
1106 hr_utility.set_location('Created Final NINO XML String'||l_final_xml_string,51 );
1107 pay_core_files.write_to_magtape_lob(l_final_xml_string);
1108
1109
1110 hr_utility.set_location(' Leaving: '||l_proc,52);
1111 END;
1112
1113 END nino_employee_proc;
1114 /**************************************************************************/
1115
1116
1117 PROCEDURE action_creation (pactid in number,
1118 stperson in number,
1119 endperson in number,
1120 chunk in number) IS
1121
1122 l_payroll_id number;
1123 l_business_group_id number;
1124 l_effective_date date;
1125 l_tax_ref varchar2(20);
1126 l_proc CONSTANT VARCHAR2(35):= g_package||'action_creation';
1127 cursor csr_arch_payroll_action_id is
1128 select
1129 to_number(substr(pact.legislative_parameters,
1130 instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19,
1131 (instr(pact.legislative_parameters,'REP_GROUP=')
1132 -
1133 (instr(pact.legislative_parameters,'ARCHIVE_REQUEST_ID=') + 19))
1134 )) arch_payroll_action_id
1135 from pay_payroll_actions pact
1136 where pact.payroll_action_id=pactid;
1137
1138
1139
1140 cursor csr_asg (c_arch_payroll_action_id number) is
1141 select distinct paa.assignment_id
1142 from pay_assignment_actions paa,
1143 pay_action_information pai,
1144 per_all_assignments_f paaf
1145 where paa.payroll_action_id = c_arch_payroll_action_id
1146 and pai.action_context_id = paa.assignment_action_id
1147 -- and pai.action_information_category = 'GB_RTI_ASG_DETAILS'
1148 and pai.action_information_category = 'GB RTI EMPLOYEE DETAILS'
1149 and pai.action_context_type = 'AAP'
1150 and paaf.assignment_id = paa.assignment_id
1151 and paaf.person_id between stperson and endperson;
1152
1153
1154
1155
1156
1157 lockingactid number;
1158 lv_assignment_id number;
1159 l_arch_payroll_action_id number;
1160 BEGIN
1161 hr_utility.set_location('Entering: '||l_proc,53);
1162
1163 hr_utility.set_location('Before CSR_ASG cursor effective_date '|| to_char(l_effective_date),54);
1164
1165 open csr_arch_payroll_action_id;
1166 fetch csr_arch_payroll_action_id into l_arch_payroll_action_id;
1167 close csr_arch_payroll_action_id;
1168
1169 hr_utility.set_location('RTI EAS Archive Payroll action ='|| to_char(l_arch_payroll_action_id),55);
1170
1171 open csr_asg(l_arch_payroll_action_id);
1172 loop
1173 fetch csr_asg into lv_assignment_id;
1174 exit when csr_asg%notfound;
1175 hr_utility.set_location('Assignment ID :' || lv_assignment_id,56);
1176
1177 -- Create one assignment action for every assignment
1178 hr_utility.set_location('Archiving for assignment_id '||lv_assignment_id, 57);
1179 select pay_assignment_actions_s.nextval
1180 into lockingactid
1181 from dual;
1182
1183 -- Insert assignment into pay_assignment_actions
1184 hr_nonrun_asact.insact
1185 (
1186 lockingactid,
1187 lv_assignment_id,
1188 pactid,
1189 chunk,
1190 null
1191 );
1192
1193 end loop;
1194 close csr_asg;
1195
1196 hr_utility.set_location('Leaving: '||l_proc,58);
1197 --
1198 END action_creation;
1199
1200 end PAY_GB_RTI_OUTPUT;