[Home] [Help]
28: p_report_type IN OUT NOCOPY VARCHAR2,
29: p_business_group_id IN OUT NOCOPY NUMBER
30: ) IS
31: BEGIN
32: --hr_utility.trace_on('Y','RL1MAG');
33: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 10);
34:
35: SELECT ppa.start_date,
36: ppa.effective_date,
29: p_business_group_id IN OUT NOCOPY NUMBER
30: ) IS
31: BEGIN
32: --hr_utility.trace_on('Y','RL1MAG');
33: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 10);
34:
35: SELECT ppa.start_date,
36: ppa.effective_date,
37: ppa.business_group_id,
42: p_report_type
43: FROM pay_payroll_actions ppa
44: WHERE payroll_action_id = p_pactid;
45:
46: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 20);
47:
48: END get_report_parameters;
49:
50: FUNCTION convert_special_char( p_data varchar2)
171:
172: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
173: l_legislative_parameters);
174:
175: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
176: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
177: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
178: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
179:
172: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
173: l_legislative_parameters);
174:
175: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
176: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
177: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
178: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
179:
180: OPEN cur_arch_pactid(l_transmitter_org_id);
173: l_legislative_parameters);
174:
175: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
176: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
177: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
178: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
179:
180: OPEN cur_arch_pactid(l_transmitter_org_id);
181: FETCH cur_arch_pactid
174:
175: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
176: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
177: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
178: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
179:
180: OPEN cur_arch_pactid(l_transmitter_org_id);
181: FETCH cur_arch_pactid
182: INTO l_arch_pactid;
185: l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
186: l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
187: BEGIN
188:
189: hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
190: SELECT substr(l_transmitter_number,1,2)
191: INTO dummy1
192: FROM dual;
193:
201: FROM dual;
202:
203: EXCEPTION
204: WHEN INVALID_NUMBER THEN
205: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
206: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
207: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
208: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
209: hr_utility.raise_error;
202:
203: EXCEPTION
204: WHEN INVALID_NUMBER THEN
205: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
206: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
207: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
208: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
209: hr_utility.raise_error;
210: END;
205: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
206: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
207: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
208: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
209: hr_utility.raise_error;
210: END;
211:
212: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
213:
210: END;
211:
212: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
213:
214: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
215:
216: IF l_type_of_package IS NULL THEN
217: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
218: hr_utility.raise_error;
214: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
215:
216: IF l_type_of_package IS NULL THEN
217: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
218: hr_utility.raise_error;
219: END IF;
220:
221: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
222: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
218: hr_utility.raise_error;
219: END IF;
220:
221: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
222: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
223:
224: IF l_source_of_slips IS NULL THEN
225: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
226: hr_utility.raise_error;
222: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
223:
224: IF l_source_of_slips IS NULL THEN
225: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
226: hr_utility.raise_error;
227: END IF;
228:
229: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
230: hr_utility.trace('l_address_line1 = ' || l_address_line1);
226: hr_utility.raise_error;
227: END IF;
228:
229: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
230: hr_utility.trace('l_address_line1 = ' || l_address_line1);
231:
232: IF l_address_line1 IS NULL THEN
233: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
234: hr_utility.raise_error;
230: hr_utility.trace('l_address_line1 = ' || l_address_line1);
231:
232: IF l_address_line1 IS NULL THEN
233: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
234: hr_utility.raise_error;
235: END IF;
236:
237: END;
238:
260: p_business_group_id NUMBER;
261: p_report_type VARCHAR2(30);
262:
263: BEGIN
264: -- hr_utility.trace_on(null,'PDF');
265: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 10);
266:
267: get_report_parameters(
268: p_pactid,
261: p_report_type VARCHAR2(30);
262:
263: BEGIN
264: -- hr_utility.trace_on(null,'PDF');
265: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 10);
266:
267: get_report_parameters(
268: p_pactid,
269: p_year_start,
271: p_report_type,
272: p_business_group_id
273: );
274:
275: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 20);
276:
277:
278: p_sqlstr := 'select distinct to_number(fai1.value)
279: from ff_archive_items fai1,
302: and hoi.org_information_context =''Prov Reporting Est''
303: and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
304: order by to_number(fai1.value)' ;
305:
306: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor',40);
307:
308: END range_cursor;
309:
310: -------------------------------------------------------------------------------
392: l_return NUMBER;
393:
394: BEGIN
395:
396: -- hr_utility.trace_on('Y','RL1MAG');
397:
398: l_prev_payact := -1;
399: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',10);
400:
395:
396: -- hr_utility.trace_on('Y','RL1MAG');
397:
398: l_prev_payact := -1;
399: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',10);
400:
401: get_report_parameters(
402: p_pactid,
403: l_year_start,
409: validate_transmitter_info(p_pactid,
410: l_business_group_id,
411: l_year_end);
412:
413: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',20);
414:
415: --IF l_report_type = 'PYRL1MAG' THEN
416:
417: OPEN c_all_asg;
422: l_effective_end_date,
423: l_assignment_action_id,
424: l_payroll_act;
425:
426: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 30);
427:
428: EXIT WHEN c_all_asg%NOTFOUND;
429:
430: /* Added by ssmukher for validating the
430: /* Added by ssmukher for validating the
431: Quebec Identification Number */
432:
433: if l_prev_payact <> l_payroll_act then
434: hr_utility.trace('The payroll action id '||l_payroll_act);
435:
436: l_prev_payact := l_payroll_act;
437: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
438: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
436: l_prev_payact := l_payroll_act;
437: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
438: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
439:
440: hr_utility.trace('The Quebec Number is '||l_quebec_val);
441:
442: l_quebec_no := substr(l_quebec_val ,1,10);
443:
444: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
440: hr_utility.trace('The Quebec Number is '||l_quebec_val);
441:
442: l_quebec_no := substr(l_quebec_val ,1,10);
443:
444: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
445: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
446: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
447:
448: end if ;
441:
442: l_quebec_no := substr(l_quebec_val ,1,10);
443:
444: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
445: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
446: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
447:
448: end if ;
449:
448: end if ;
449:
450: --Create the assignment action for the record
451:
452: hr_utility.trace('Assignment Fetched - ');
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
449:
450: --Create the assignment action for the record
451:
452: hr_utility.trace('Assignment Fetched - ');
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
450: --Create the assignment action for the record
451:
452: hr_utility.trace('Assignment Fetched - ');
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
458:
451:
452: hr_utility.trace('Assignment Fetched - ');
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
458:
459: SELECT pay_assignment_actions_s.nextval
452: hr_utility.trace('Assignment Fetched - ');
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
458:
459: SELECT pay_assignment_actions_s.nextval
460: INTO lockingactid
453: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
454: hr_utility.trace('Person Id : '|| to_char(l_person_id));
455: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
456: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
457: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
458:
459: SELECT pay_assignment_actions_s.nextval
460: INTO lockingactid
461: FROM dual;
459: SELECT pay_assignment_actions_s.nextval
460: INTO lockingactid
461: FROM dual;
462:
463: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 50);
464: hr_nonrun_asact.insact(lockingactid,
465: l_assignment_id,
466: p_pactid,
467: p_chunk,
466: p_pactid,
467: p_chunk,
468: l_tax_unit_id);
469:
470: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
471: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473: hr_utility.trace('Interlock Created - ');
474: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
468: l_tax_unit_id);
469:
470: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
471: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473: hr_utility.trace('Interlock Created - ');
474: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
475: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
476: END LOOP;
469:
470: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
471: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473: hr_utility.trace('Interlock Created - ');
474: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
475: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
476: END LOOP;
477: CLOSE c_all_asg;
470: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
471: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473: hr_utility.trace('Interlock Created - ');
474: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
475: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
476: END LOOP;
477: CLOSE c_all_asg;
478:
471: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
472: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
473: hr_utility.trace('Interlock Created - ');
474: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
475: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
476: END LOOP;
477: CLOSE c_all_asg;
478:
479: -- END IF;
556: l_max := 7;
557: l_total := 0;
558:
559: l_act_chk_number := to_number(substr(p_quebec_no,10,1));
560: hr_utility.trace('here1');
561: if TRANSLATE(p_quebec_no,'0123456789','9999999999') = '9999999999RS9999' then
562: l_quebec := to_number(substr(p_quebec_no,1,9));
563: loop
564: if i > 9 then
586: l_chk_digit := 11 - l_modulus;
587: end if;
588:
589: if l_chk_digit <> l_act_chk_number then
590: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
591: hr_utility.set_message_token('PRE_NAME',p_qin_name);
592: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
593: pay_core_utils.push_token('PRE_NAME',p_qin_name);
594: hr_utility.raise_error;
587: end if;
588:
589: if l_chk_digit <> l_act_chk_number then
590: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
591: hr_utility.set_message_token('PRE_NAME',p_qin_name);
592: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
593: pay_core_utils.push_token('PRE_NAME',p_qin_name);
594: hr_utility.raise_error;
595: end if;
590: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
591: hr_utility.set_message_token('PRE_NAME',p_qin_name);
592: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
593: pay_core_utils.push_token('PRE_NAME',p_qin_name);
594: hr_utility.raise_error;
595: end if;
596: else
597:
598: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
594: hr_utility.raise_error;
595: end if;
596: else
597:
598: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
599: hr_utility.set_message_token('PRE_NAME',p_qin_name);
600: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
601: pay_core_utils.push_token('PRE_NAME',p_qin_name);
602: hr_utility.raise_error;
595: end if;
596: else
597:
598: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
599: hr_utility.set_message_token('PRE_NAME',p_qin_name);
600: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
601: pay_core_utils.push_token('PRE_NAME',p_qin_name);
602: hr_utility.raise_error;
603:
598: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
599: hr_utility.set_message_token('PRE_NAME',p_qin_name);
600: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
601: pay_core_utils.push_token('PRE_NAME',p_qin_name);
602: hr_utility.raise_error;
603:
604: end if;
605:
606: return l_chk_digit;
676: l_certification_no VARCHAR2(50);
677:
678: BEGIN
679:
680: hr_utility.trace('XML Transmitter');
681:
682: SELECT
683: fnd_global.local_chr(13) || fnd_global.local_chr(10)
684: INTO EOL
712: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
713: l_context1
714: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
715:
716: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
717: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
718:
719:
720: -- Annee
713: l_context1
714: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
715:
716: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
717: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
718:
719:
720: -- Annee
721: tab_transmitter(lAnnee) := '
718:
719:
720: -- Annee
721: tab_transmitter(lAnnee) := '
722: hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
723:
724: -- TypeEnvoi
725: tab_transmitter(lTypeEnvoi) := '
726: pay_ca_archive_utils.get_archive_value(l_context1,
724: -- TypeEnvoi
725: tab_transmitter(lTypeEnvoi) := '
726: pay_ca_archive_utils.get_archive_value(l_context1,
727: 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE') || '
728: hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
729: tab_transmitter(lTypeEnvoi));
730:
731: tab_transmitter(lProvenance) := '
732: pay_ca_archive_utils.get_archive_value(l_context1,
731: tab_transmitter(lProvenance) := '
732: pay_ca_archive_utils.get_archive_value(l_context1,
733: 'CAEOY_RL1_SOURCE_OF_SLIPS') || '
734:
735: hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
736:
737: tab_transmitter(lNo) := '
738: pay_ca_archive_utils.get_archive_value(l_context1,
739: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
737: tab_transmitter(lNo) := '
738: pay_ca_archive_utils.get_archive_value(l_context1,
739: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
740:
741: hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
742:
743: l_return := pay_ca_archive_utils.get_archive_value(l_context1,
744: 'CAEOY_RL1_TRANSMITTER_TYPE');
745: IF l_return IS NOT NULL THEN
747: ELSE
748: tab_transmitter(lType) := NULL;
749: END IF;
750:
751: hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
752:
753: l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
754: 'CAEOY_RL1_TRANSMITTER_NAME');
755:
755:
756: tab_transmitter(lNom1) := '
757: convert_special_char(substr(l_transmitter_name,1,30)) || '
758:
759: hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
760:
761: l_return := substr(l_transmitter_name,31,30);
762: IF l_return IS NOT NULL THEN
763: tab_transmitter(lNom2) := '
764: ELSE
765: tab_transmitter(lNom2) := NULL;
766: END IF;
767:
768: hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
769:
770: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
771: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
772:
772:
773: tab_transmitter(lLigne1) := '
774: convert_special_char(substr(l_address_line,1,30)) || '
775:
776: hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
777:
778: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
779: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
780:
784: ELSE
785: tab_transmitter(lLigne2) := NULL;
786: END IF;
787:
788: hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
789:
790: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
791: 'CAEOY_RL1_TRANSMITTER_CITY');
792:
796: ELSE
797: tab_transmitter(lVille) := NULL;
798: END IF;
799:
800: hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
801:
802: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
803: 'CAEOY_RL1_TRANSMITTER_PROVINCE');
804:
805: tab_transmitter(lProvince) := '
806: SUBSTR(hr_general.decode_lookup(
807: 'CA_PROVINCE',l_address_line),1,20) || '
808:
809: hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
810:
811: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
812: 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
813:
813:
814: tab_transmitter(lCodePostal) := '
815: substr(replace(l_address_line,' '),1,6) || '
816:
817: hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
818:
819: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
820: 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
821:
825: ELSE
826: tab_transmitter(lNom) := NULL;
827: END IF;
828:
829: hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
830:
831: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
832: 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
833:
837: ELSE
838: tab_transmitter(lIndRegional) := NULL;
839: END IF;
840:
841: hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
842:
843: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
844: 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
845: IF l_contact IS NOT NULL THEN
847: tab_transmitter(lTel) := '
848: ELSE
849: tab_transmitter(lTel) := NULL;
850: END IF;
851: hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
852:
853: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
854: 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
855:
858: '' || EOL;
859: ELSE
860: tab_transmitter(lPosteTel) := NULL;
861: END IF;
862: hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
863: tab_transmitter(lPosteTel));
864:
865: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
866: 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
879: convert_special_char(substr(l_contact,1,30)) || '' || EOL;
880: ELSE
881: tab_transmitter(lANom) := NULL;
882: END IF;
883: hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
884:
885: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
886: 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
887:
890: '' || EOL;
891: ELSE
892: tab_transmitter(lAIndRegional) := NULL;
893: END IF;
894: hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
895:
896: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
897: 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
898:
901: tab_transmitter(lATel) := '
902: ELSE
903: tab_transmitter(lATel) := NULL;
904: END IF;
905: hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
906:
907: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
908: 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
909:
912: '' || EOL;
913: ELSE
914: tab_transmitter(lAPosteTel) := NULL;
915: END IF;
916: hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
917: tab_transmitter(lAPosteTel));
918:
919: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
920: 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
924: ELSE
925: tab_transmitter(lALangue) := NULL;
926: END IF;
927:
928: hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
929:
930: -- For bug 6738509
931: if(l_taxation_year = '2006') then
932: l_certification_no := 'RQ-06-01-098';
969: tab_transmitter(lALangue) || '' || EOL ||
970: tab_transmitter(lNoConcepteur) ||
971: '
989: BEGIN
990:
991: l_final_xml_string := '';
992:
993: hr_utility.trace('end_of_file l_final_xml_string = '
994: || l_final_xml_string );
995: pay_core_files.write_to_magtape_lob(l_final_xml_string);
996:
997: END;
1245: l_check_number number;
1246: BEGIN
1247:
1248: l_check_number := mod(p_curr_seq,7);
1249: hr_utility.trace('l_check_number ='|| l_check_number);
1250: l_seq_number := (p_curr_seq * 10) + l_check_number;
1251: hr_utility.trace('l_seq_number ='|| l_seq_number);
1252: return l_seq_number;
1253: END;
1247:
1248: l_check_number := mod(p_curr_seq,7);
1249: hr_utility.trace('l_check_number ='|| l_check_number);
1250: l_seq_number := (p_curr_seq * 10) + l_check_number;
1251: hr_utility.trace('l_seq_number ='|| l_seq_number);
1252: return l_seq_number;
1253: END;
1254:
1255: BEGIN
1252: return l_seq_number;
1253: END;
1254:
1255: BEGIN
1256: -- hr_utility.trace_on(null,'PDF');
1257: hr_utility.trace('inside xml_employee_record');
1258: /*******************************************************************************/
1259: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1260: l_payroll_actid
1253: END;
1254:
1255: BEGIN
1256: -- hr_utility.trace_on(null,'PDF');
1257: hr_utility.trace('inside xml_employee_record');
1258: /*******************************************************************************/
1259: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1260: l_payroll_actid
1261: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1258: /*******************************************************************************/
1259: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1260: l_payroll_actid
1261: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1262: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1263: SELECT ppa.report_type
1264: INTO l_rep_type
1265: from pay_payroll_actions ppa
1266: where payroll_action_id=l_payroll_actid;
1263: SELECT ppa.report_type
1264: INTO l_rep_type
1265: from pay_payroll_actions ppa
1266: where payroll_action_id=l_payroll_actid;
1267: hr_utility.trace('report_type='||l_rep_type);
1268: /******************************* ************************************/
1269:
1270:
1271: hr_utility.trace('XML Employee');
1267: hr_utility.trace('report_type='||l_rep_type);
1268: /******************************* ************************************/
1269:
1270:
1271: hr_utility.trace('XML Employee');
1272: l_status := 'Success';
1273: l_all_box_0 := TRUE;
1274: l_format_mask := '99999999999999990.99';
1275:
1369:
1370: l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1371: ('TRANSFER_ACT_ID'));
1372:
1373: hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1374: || to_char(l_mag_asg_action_id));
1375:
1376: /**********************************************************************/
1377: IF l_rep_type <> 'RL1PAPERPDF' THEN
1397: close c_province;
1398: END IF;
1399: /**************************************** ******************/
1400:
1401: hr_utility.trace('XML Employee: l_arch_action_id = '
1402: || to_char(l_arch_action_id));
1403: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1404: hr_utility.trace('XML Employee: l_date_earned = '
1405: || to_char(l_date_earned));
1399: /**************************************** ******************/
1400:
1401: hr_utility.trace('XML Employee: l_arch_action_id = '
1402: || to_char(l_arch_action_id));
1403: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1404: hr_utility.trace('XML Employee: l_date_earned = '
1405: || to_char(l_date_earned));
1406: hr_utility.trace('XML Employee: l_province = ' || l_province);
1407:
1400:
1401: hr_utility.trace('XML Employee: l_arch_action_id = '
1402: || to_char(l_arch_action_id));
1403: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1404: hr_utility.trace('XML Employee: l_date_earned = '
1405: || to_char(l_date_earned));
1406: hr_utility.trace('XML Employee: l_province = ' || l_province);
1407:
1408: l_taxation_year
1402: || to_char(l_arch_action_id));
1403: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1404: hr_utility.trace('XML Employee: l_date_earned = '
1405: || to_char(l_date_earned));
1406: hr_utility.trace('XML Employee: l_province = ' || l_province);
1407:
1408: l_taxation_year
1409: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1410:
1417: end if;
1418:
1419: --Annee
1420: tab_employee(lAnnee) := '
1421: hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1422: --NoReleve
1423: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1424: 'CAEOY_RL1_SLIP_NUMBER');
1425:
1431: tab_employee(lNoReleve) := '
1432: '
1433: tab_xml_employee(lNoReleve) := l_return; --
1434: END IF;
1435: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1436: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1437:
1438: -- NAS
1439: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1432: '' || EOL;
1433: tab_xml_employee(lNoReleve) := l_return; --
1434: END IF;
1435: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1436: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1437:
1438: -- NAS
1439: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1440: 'CAEOY_EMPLOYEE_SIN');
1448: l_msg_code := 'SIN';
1449: tab_employee(lNAS) := NULL;
1450: tab_xml_employee(lNAS) := NULL; --
1451: END IF;
1452: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1453: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1454:
1455: -- No
1456: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1449: tab_employee(lNAS) := NULL;
1450: tab_xml_employee(lNAS) := NULL; --
1451: END IF;
1452: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1453: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1454:
1455: -- No
1456: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1457: 'CAEOY_EMPLOYEE_NUMBER');
1461: ELSE
1462: tab_employee(lNo) := NULL;
1463: tab_xml_employee(lNo) := NULL; --
1464: END IF;
1465: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1466: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1467:
1468: -- NomFamille
1469: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1462: tab_employee(lNo) := NULL;
1463: tab_xml_employee(lNo) := NULL; --
1464: END IF;
1465: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1466: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1467:
1468: -- NomFamille
1469: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1470: 'CAEOY_EMPLOYEE_LAST_NAME');
1470: 'CAEOY_EMPLOYEE_LAST_NAME');
1471: tab_employee(lNomFamille) := '
1472: convert_special_char(substr(l_name,1,30)) || '
1473: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1474: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1475: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1476:
1477: -- Prenom
1478: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1471: tab_employee(lNomFamille) := '
1472: convert_special_char(substr(l_name,1,30)) || '
1473: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1474: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1475: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1476:
1477: -- Prenom
1478: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1479: 'CAEOY_EMPLOYEE_FIRST_NAME');
1487: l_msg_code := 'MISSING_EMP_FIRST_NAME';
1488: tab_employee(lPrenom) := NULL;
1489: tab_xml_employee(lPrenom) := NULL ; --
1490: END IF;
1491: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1492: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1493:
1494: -- Initiale
1495: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1488: tab_employee(lPrenom) := NULL;
1489: tab_xml_employee(lPrenom) := NULL ; --
1490: END IF;
1491: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1492: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1493:
1494: -- Initiale
1495: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1496: 'CAEOY_EMPLOYEE_INITIAL');
1502: ELSE
1503: tab_employee(lInitiale) := NULL;
1504: tab_xml_employee(lInitiale) := NULL; --
1505: END IF;
1506: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1507: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1508:
1509: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1510: l_arch_action_id,
1503: tab_employee(lInitiale) := NULL;
1504: tab_xml_employee(lInitiale) := NULL; --
1505: END IF;
1506: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1507: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1508:
1509: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1510: l_arch_action_id,
1511: 'CAEOY_PERSON_ID'));
1515: l_arch_action_id,
1516: 'CAEOY_PERSON_ID'));
1517:
1518: select trunc(sysdate) into l_session_date from dual;
1519: hr_utility.trace('l_person_id1 ='|| l_person_id1);
1520: hr_utility.trace('l_session_date ='|| l_session_date);
1521: addr := pay_ca_rl1_reg.get_primary_address(l_person_id1, l_session_date);
1522: tab_xml_employee(lLigne1) := convert_special_char(substr(addr.addr_line_1,1,40));
1523: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
1516: 'CAEOY_PERSON_ID'));
1517:
1518: select trunc(sysdate) into l_session_date from dual;
1519: hr_utility.trace('l_person_id1 ='|| l_person_id1);
1520: hr_utility.trace('l_session_date ='|| l_session_date);
1521: addr := pay_ca_rl1_reg.get_primary_address(l_person_id1, l_session_date);
1522: tab_xml_employee(lLigne1) := convert_special_char(substr(addr.addr_line_1,1,40));
1523: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
1524: tab_xml_employee(lVille) := convert_special_char(substr(addr.addr_line_4,1,40));
1523: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
1524: tab_xml_employee(lVille) := convert_special_char(substr(addr.addr_line_4,1,40));
1525: tab_xml_employee(lForm_number) := pay_ca_archive_utils.get_archive_value(l_mag_asg_action_id, --l_arch_action_id,
1526: 'CAEOY_RL1_PRE_PRINTED_FORM_NO');
1527: hr_utility.trace('tab_xml_employee(lForm_number) = ' || tab_xml_employee(lForm_number));
1528:
1529:
1530: tab_xml_employee(lAnnee) := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid,
1531: 'CAEOY_TAXATION_YEAR');
1528:
1529:
1530: tab_xml_employee(lAnnee) := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid,
1531: 'CAEOY_TAXATION_YEAR');
1532: hr_utility.trace('tab_xml_employee(lAnnee) = ' || tab_xml_employee(lAnnee)); --
1533: /***************************** *****************************/
1534: else /***if not paper report***/
1535: l_return := pay_ca_emp_address_dtls.get_emp_address(
1536: l_person_id,
1545: -- If Address line 1 is NULL or ' ' then the employee is missing
1546: -- address information - as line 1 is mandatory in the Address form.
1547: -- Need to check data by SS transaction /API.
1548:
1549: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1550: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1551: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1552: hr_utility.trace('l_postal_code = ' || l_postal_code);
1553:
1546: -- address information - as line 1 is mandatory in the Address form.
1547: -- Need to check data by SS transaction /API.
1548:
1549: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1550: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1551: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1552: hr_utility.trace('l_postal_code = ' || l_postal_code);
1553:
1554: -- Address Line 1
1547: -- Need to check data by SS transaction /API.
1548:
1549: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1550: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1551: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1552: hr_utility.trace('l_postal_code = ' || l_postal_code);
1553:
1554: -- Address Line 1
1555: IF l_address_line1 IS NULL OR
1548:
1549: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1550: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1551: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1552: hr_utility.trace('l_postal_code = ' || l_postal_code);
1553:
1554: -- Address Line 1
1555: IF l_address_line1 IS NULL OR
1556: l_address_line1 = ' ' THEN
1572: l_addr_begin_tag := '
1573:
1574: tab_employee(lLigne1) := '
1575: convert_special_char(substr(l_address_line1,1,30)) || '
1576: hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1577:
1578: -- Address Line 2
1579: IF ((l_address_line2 IS NULL OR
1580: l_address_line2 <> ' ') OR
1585: convert_special_char(substr(l_combined_addr,1,30)) || '' || EOL;
1586: ELSE
1587: tab_employee(lLigne2) := NULL;
1588: END IF;
1589: hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1590:
1591: -- Ville (City)
1592: IF l_city IS NULL OR
1593: l_city <> ' ' THEN
1595: substr(l_city,1,30) || '' || EOL;
1596: ELSE
1597: tab_employee(lVille) := NULL;
1598: END IF;
1599: hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1600:
1601: -- Province
1602: IF l_emp_province IS NULL OR
1603: l_emp_province <> ' ' THEN
1612: END IF;
1613: ELSE
1614: tab_employee(lProvince) := NULL;
1615: END IF;
1616: hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1617:
1618: -- Postal Code
1619: IF l_postal_code IS NULL OR
1620: l_postal_code <> ' ' THEN
1622: substr(replace(l_postal_code,' '),1,6) || '' || EOL;
1623: ELSE
1624: tab_employee(lCodePostal) := NULL;
1625: END IF;
1626: hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
1627: l_addr_end_tag := '';
1628:
1629: END IF;
1630:
1656: ELSE
1657: tab_employee(lA_RevenuEmploi) := NULL;
1658: tab_xml_employee(lA_RevenuEmploi) := NULL; --
1659: END IF;
1660: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1661: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1662:
1663: -- Summ (Box B)
1664: l_box := pay_ca_archive_utils.get_archive_value(
1657: tab_employee(lA_RevenuEmploi) := NULL;
1658: tab_xml_employee(lA_RevenuEmploi) := NULL; --
1659: END IF;
1660: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1661: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1662:
1663: -- Summ (Box B)
1664: l_box := pay_ca_archive_utils.get_archive_value(
1665: l_arch_action_id,
1690: tab_employee(lB_CotisationRRQ) := NULL;
1691: tab_xml_employee(lB_CotisationRRQ) := NULL; --
1692:
1693: END IF;
1694: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1695: tab_employee(lB_CotisationRRQ));
1696: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1697: tab_xml_employee(lB_CotisationRRQ)); --
1698:
1692:
1693: END IF;
1694: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1695: tab_employee(lB_CotisationRRQ));
1696: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1697: tab_xml_employee(lB_CotisationRRQ)); --
1698:
1699: -- Summ (Box C)
1700: l_box := pay_ca_archive_utils.get_archive_value(
1723: tab_employee(lC_CotisationAssEmploi) := NULL;
1724: tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
1725: END IF;
1726:
1727: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
1728: tab_employee(lC_CotisationAssEmploi));
1729:
1730: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
1731: tab_xml_employee(lC_CotisationAssEmploi)); --
1726:
1727: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
1728: tab_employee(lC_CotisationAssEmploi));
1729:
1730: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
1731: tab_xml_employee(lC_CotisationAssEmploi)); --
1732: -- Summ (Box D)
1733: l_box := pay_ca_archive_utils.get_archive_value(
1734: l_arch_action_id,
1755: ELSE
1756: tab_employee(lD_CotisationRPA) := NULL;
1757: tab_xml_employee(lD_CotisationRPA) := NULL; --
1758: END IF;
1759: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
1760: tab_employee(lD_CotisationRPA));
1761: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
1762: tab_xml_employee(lD_CotisationRPA));
1763:
1757: tab_xml_employee(lD_CotisationRPA) := NULL; --
1758: END IF;
1759: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
1760: tab_employee(lD_CotisationRPA));
1761: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
1762: tab_xml_employee(lD_CotisationRPA));
1763:
1764:
1765: -- (Box E)
1790: tab_employee(lE_ImpotQue) := NULL ;
1791: tab_xml_employee(lE_ImpotQue) := NULL ; --
1792: END IF;
1793:
1794: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
1795: tab_employee(lE_ImpotQue));
1796: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
1797: tab_xml_employee(lE_ImpotQue)); --
1798:
1792: END IF;
1793:
1794: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
1795: tab_employee(lE_ImpotQue));
1796: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
1797: tab_xml_employee(lE_ImpotQue)); --
1798:
1799: -- (Box F)
1800: l_box := pay_ca_archive_utils.get_archive_value(
1822: ELSE
1823: tab_employee(lF_CotisationSyndicale) := NULL;
1824: tab_xml_employee(lF_CotisationSyndicale) := NULL; --
1825: END IF;
1826: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
1827: tab_employee(lF_CotisationSyndicale));
1828:
1829: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
1830: tab_xml_employee(lF_CotisationSyndicale));
1825: END IF;
1826: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
1827: tab_employee(lF_CotisationSyndicale));
1828:
1829: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
1830: tab_xml_employee(lF_CotisationSyndicale));
1831: -- (Box Q)
1832: l_box := pay_ca_archive_utils.get_archive_value(
1833: l_arch_action_id,
1855: ELSE
1856: tab_employee(lQ_SalaireDiffere) := NULL;
1857: tab_xml_employee(lQ_SalaireDiffere) := NULL; --
1858: END IF;
1859: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
1860: tab_employee(lQ_SalaireDiffere));
1861:
1862: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
1863: tab_xml_employee(lQ_SalaireDiffere));
1858: END IF;
1859: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
1860: tab_employee(lQ_SalaireDiffere));
1861:
1862: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
1863: tab_xml_employee(lQ_SalaireDiffere));
1864: -- (Box U)
1865: l_box := pay_ca_archive_utils.get_archive_value(
1866: l_arch_action_id,
1890: ELSE
1891: tab_employee(lU_RetraiteProgressive) := NULL;
1892: tab_xml_employee(lU_RetraiteProgressive) := NULL; --
1893: END IF;
1894: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
1895: tab_employee(lU_RetraiteProgressive));
1896:
1897: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
1898: tab_xml_employee(lU_RetraiteProgressive));
1893: END IF;
1894: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
1895: tab_employee(lU_RetraiteProgressive));
1896:
1897: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
1898: tab_xml_employee(lU_RetraiteProgressive));
1899: -- (Box G)
1900: l_box := pay_ca_archive_utils.get_archive_value(
1901: l_arch_action_id,
1902: l_province,
1903: 'JURISDICTION_CODE',
1904: 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
1905:
1906: hr_utility.trace('l_box = ' || l_box);
1907: hr_utility.trace('l_boxA = ' || l_BoxA);
1908: hr_utility.trace('l_boxQ = ' || l_BoxQ);
1909: hr_utility.trace('l_boxU = ' || l_boxU);
1910:
1903: 'JURISDICTION_CODE',
1904: 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
1905:
1906: hr_utility.trace('l_box = ' || l_box);
1907: hr_utility.trace('l_boxA = ' || l_BoxA);
1908: hr_utility.trace('l_boxQ = ' || l_BoxQ);
1909: hr_utility.trace('l_boxU = ' || l_boxU);
1910:
1911: IF l_rep_type <>'RL1PAPERPDF' THEN
1904: 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
1905:
1906: hr_utility.trace('l_box = ' || l_box);
1907: hr_utility.trace('l_boxA = ' || l_BoxA);
1908: hr_utility.trace('l_boxQ = ' || l_BoxQ);
1909: hr_utility.trace('l_boxU = ' || l_boxU);
1910:
1911: IF l_rep_type <>'RL1PAPERPDF' THEN
1912: IF l_box IS NOT NULL THEN
1905:
1906: hr_utility.trace('l_box = ' || l_box);
1907: hr_utility.trace('l_boxA = ' || l_BoxA);
1908: hr_utility.trace('l_boxQ = ' || l_BoxQ);
1909: hr_utility.trace('l_boxU = ' || l_boxU);
1910:
1911: IF l_rep_type <>'RL1PAPERPDF' THEN
1912: IF l_box IS NOT NULL THEN
1913:
1956: ELSE
1957: tab_employee(lG_SalaireAdmisRRQ) := NULL;
1958: --tab_xml_employee(lG_SalaireAdmisRRQ) := NULL; --
1959: END IF;
1960: hr_utility.trace('tab_employee(lG_SalaireAdmisRRQ) = ' ||
1961: tab_employee(lG_SalaireAdmisRRQ));
1962: END IF;
1963:
1964: IF l_rep_type ='RL1PAPERPDF' THEN
1976: ELSE
1977: tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
1978: END IF;
1979:
1980: hr_utility.trace('tab_xml_employee(lG_SalaireAdmisRRQ) = ' ||
1981: tab_xml_employee(lG_SalaireAdmisRRQ));
1982:
1983: END IF;
1984:
2010: tab_employee(lV_NourritureLogement) := NULL;
2011: tab_xml_employee(lV_NourritureLogement) := NULL; --
2012: END IF;
2013:
2014: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2015: tab_employee(lV_NourritureLogement));
2016:
2017: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2018: tab_xml_employee(lV_NourritureLogement));
2013:
2014: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2015: tab_employee(lV_NourritureLogement));
2016:
2017: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2018: tab_xml_employee(lV_NourritureLogement));
2019: -- (Box W)
2020: l_box := pay_ca_archive_utils.get_archive_value(
2021: l_arch_action_id,
2042: ELSE
2043: tab_employee(lW_Vehicule) := NULL;
2044: tab_xml_employee(lW_Vehicule) := NULL; --
2045: END IF;
2046: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2047: tab_employee(lW_Vehicule));
2048:
2049: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2050: tab_xml_employee(lW_Vehicule));
2045: END IF;
2046: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2047: tab_employee(lW_Vehicule));
2048:
2049: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2050: tab_xml_employee(lW_Vehicule));
2051:
2052: --(BOX H)
2053: l_box := pay_ca_archive_utils.get_archive_value(
2074: ELSE
2075: tab_employee(lH_CotisationRQAP) := NULL;
2076: tab_xml_employee(lH_CotisationRQAP) := NULL; --
2077: END IF;
2078: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2079: tab_employee(lH_CotisationRQAP));
2080:
2081: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2082: tab_xml_employee(lH_CotisationRQAP));
2077: END IF;
2078: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2079: tab_employee(lH_CotisationRQAP));
2080:
2081: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2082: tab_xml_employee(lH_CotisationRQAP));
2083:
2084: --(BOX I)
2085: l_box := pay_ca_archive_utils.get_archive_value(
2109: tab_employee(lI_SalaireAdmisRQAP) := '
2110: '0.00' || '
2111: tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2112: END IF;
2113: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2114: tab_employee(lI_SalaireAdmisRQAP));
2115:
2116: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2117: tab_xml_employee(lI_SalaireAdmisRQAP));
2112: END IF;
2113: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2114: tab_employee(lI_SalaireAdmisRQAP));
2115:
2116: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2117: tab_xml_employee(lI_SalaireAdmisRQAP));
2118:
2119: -- (Box J)
2120: l_box := pay_ca_archive_utils.get_archive_value(
2142: ELSE
2143: tab_employee(lJ_RegimeAssMaladie) := NULL;
2144: tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2145: END IF;
2146: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2147: tab_employee(lJ_RegimeAssMaladie));
2148:
2149: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2150: tab_xml_employee(lJ_RegimeAssMaladie));
2145: END IF;
2146: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2147: tab_employee(lJ_RegimeAssMaladie));
2148:
2149: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2150: tab_xml_employee(lJ_RegimeAssMaladie));
2151:
2152: -- (Box K)
2153: l_box := pay_ca_archive_utils.get_archive_value(
2175: ELSE
2176: tab_employee(lK_Voyage) := NULL;
2177: tab_xml_employee(lK_Voyage) := NULL; --
2178: END IF;
2179: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2180: tab_employee(lK_Voyage));
2181:
2182: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2183: tab_xml_employee(lK_Voyage));
2178: END IF;
2179: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2180: tab_employee(lK_Voyage));
2181:
2182: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2183: tab_xml_employee(lK_Voyage));
2184: -- (Box L)
2185: l_box := pay_ca_archive_utils.get_archive_value(
2186: l_arch_action_id,
2207: ELSE
2208: tab_employee(lL_AutreAvantage) := NULL;
2209: tab_xml_employee(lL_AutreAvantage) := NULL; --
2210: END IF;
2211: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2212: tab_employee(lL_AutreAvantage));
2213:
2214: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2215: tab_xml_employee(lL_AutreAvantage));
2210: END IF;
2211: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2212: tab_employee(lL_AutreAvantage));
2213:
2214: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2215: tab_xml_employee(lL_AutreAvantage));
2216: -- (Box M)
2217: l_box := pay_ca_archive_utils.get_archive_value(
2218: l_arch_action_id,
2239: ELSE
2240: tab_employee(lM_Commission) := NULL;
2241: tab_xml_employee(lM_Commission) := NULL; --
2242: END IF;
2243: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2244: tab_employee(lM_Commission));
2245:
2246: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2247: tab_xml_employee(lM_Commission));
2242: END IF;
2243: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2244: tab_employee(lM_Commission));
2245:
2246: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2247: tab_xml_employee(lM_Commission));
2248: -- (Box N)
2249: l_box := pay_ca_archive_utils.get_archive_value(
2250: l_arch_action_id,
2272: tab_employee(lN_DonBienfaisance) := NULL;
2273: tab_xml_employee(lN_DonBienfaisance) := NULL; --
2274: END IF;
2275:
2276: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2277: tab_employee(lN_DonBienfaisance));
2278:
2279: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2280: tab_xml_employee(lN_DonBienfaisance));
2275:
2276: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2277: tab_employee(lN_DonBienfaisance));
2278:
2279: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2280: tab_xml_employee(lN_DonBienfaisance));
2281: -- Summ (Box O)
2282: l_box := pay_ca_archive_utils.get_archive_value(
2283: l_arch_action_id,
2305: ELSE
2306: tab_employee(lO_AutreRevenu) := NULL;
2307: tab_xml_employee(lO_AutreRevenu) := NULL; --
2308: END IF;
2309: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2310: tab_employee(lO_AutreRevenu));
2311:
2312: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2313: tab_xml_employee(lO_AutreRevenu));
2308: END IF;
2309: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2310: tab_employee(lO_AutreRevenu));
2311:
2312: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2313: tab_xml_employee(lO_AutreRevenu));
2314:
2315: -- SourceCasem
2316:
2326: ELSE
2327: tab_employee(l_SourceCase) := NULL;
2328: tab_xml_employee(l_SourceCase) := NULL; --
2329: END IF;
2330: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2331: tab_employee(l_SourceCase));
2332:
2333: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2334: tab_xml_employee(l_SourceCase));
2329: END IF;
2330: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2331: tab_employee(l_SourceCase));
2332:
2333: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2334: tab_xml_employee(l_SourceCase));
2335: -- (Box P)
2336: if tab_employee(lO_AutreRevenu) is not null
2337: and tab_employee(l_SourceCase)is not null then
2367: ELSE
2368: tab_employee(lP_RegimeAssInterEntr) := NULL;
2369: tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2370: END IF;
2371: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2372: tab_employee(lP_RegimeAssInterEntr));
2373:
2374: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2375: tab_xml_employee(lP_RegimeAssInterEntr));
2370: END IF;
2371: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2372: tab_employee(lP_RegimeAssInterEntr));
2373:
2374: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2375: tab_xml_employee(lP_RegimeAssInterEntr));
2376: -- (Box R)
2377: l_box := pay_ca_archive_utils.get_archive_value(
2378: l_arch_action_id,
2399: ELSE
2400: tab_employee(lR_RevenuIndien) := NULL;
2401: tab_xml_employee(lR_RevenuIndien) := NULL; --
2402: END IF;
2403: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2404: tab_employee(lR_RevenuIndien));
2405:
2406: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2407: tab_xml_employee(lR_RevenuIndien));
2402: END IF;
2403: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2404: tab_employee(lR_RevenuIndien));
2405:
2406: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2407: tab_xml_employee(lR_RevenuIndien));
2408: -- (Box S)
2409: l_box := pay_ca_archive_utils.get_archive_value(
2410: l_arch_action_id,
2431: ELSE
2432: tab_employee(lS_PourboireRecu) := NULL;
2433: tab_xml_employee(lS_PourboireRecu) := NULL; --
2434: END IF;
2435: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2436: tab_employee(lS_PourboireRecu));
2437:
2438: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2439: tab_xml_employee(lS_PourboireRecu));
2434: END IF;
2435: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2436: tab_employee(lS_PourboireRecu));
2437:
2438: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2439: tab_xml_employee(lS_PourboireRecu));
2440: -- (Box T)
2441: l_box := pay_ca_archive_utils.get_archive_value(
2442: l_arch_action_id,
2463: ELSE
2464: tab_employee(lT_PourboireAttribue) := NULL;
2465: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2466: END IF;
2467: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2468: tab_employee(lT_PourboireAttribue));
2469: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2470: tab_xml_employee(lT_PourboireAttribue));
2471: -- (Box ContisationRPC)
2465: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2466: END IF;
2467: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2468: tab_employee(lT_PourboireAttribue));
2469: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2470: tab_xml_employee(lT_PourboireAttribue));
2471: -- (Box ContisationRPC)
2472: l_box := pay_ca_archive_utils.get_archive_value(
2473: l_arch_action_id,
2492: l_all_box_0 := FALSE;
2493: ELSE
2494: tab_employee(l_ContisationRPC) := NULL;
2495: END IF;
2496: hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2497: tab_employee(l_ContisationRPC));
2498:
2499: -- Negative Balance Exists
2500: hr_utility.trace('finding if neg bal exists');
2496: hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2497: tab_employee(l_ContisationRPC));
2498:
2499: -- Negative Balance Exists
2500: hr_utility.trace('finding if neg bal exists');
2501: l_box := pay_ca_archive_utils.get_archive_value(
2502: l_arch_action_id,
2503: l_province,
2504: 'JURISDICTION_CODE',
2507: IF l_box = 'Y' THEN
2508: l_status := 'Failed';
2509: l_msg_code := 'NEG';
2510: l_neg_bal_exists := TRUE;
2511: hr_utility.trace('neg bal exists');
2512: END IF;
2513:
2514: IF l_all_box_0 THEN
2515: l_status := 'Failed';
2536: l_formatted_box || '' || EOL;
2537: ELSE
2538: tab_employee(lBoxA_01) := NULL;
2539: END IF;
2540: hr_utility.trace('tab_employee(lBoxA_01) = ' ||
2541: tab_employee(lBoxA_01));
2542:
2543: -- Box A, 02
2544: l_box := pay_ca_archive_utils.get_archive_value(
2558: l_formatted_box || '' || EOL;
2559: ELSE
2560: tab_employee(lBoxA_02) := NULL;
2561: END IF;
2562: hr_utility.trace('tab_employee(lBoxA_02) = ' ||
2563: tab_employee(lBoxA_02));
2564:
2565: -- Box A, 15
2566: l_box := pay_ca_archive_utils.get_archive_value(
2580: l_formatted_box || '' || EOL;
2581: ELSE
2582: tab_employee(lBoxA_15) := NULL;
2583: END IF;
2584: hr_utility.trace('tab_employee(lBoxA_15) = ' ||
2585: tab_employee(lBoxA_15));
2586:
2587: -- Box A, 16
2588: l_box := pay_ca_archive_utils.get_archive_value(
2602: l_formatted_box || '' || EOL;
2603: ELSE
2604: tab_employee(lBoxA_16) := NULL;
2605: END IF;
2606: hr_utility.trace('tab_employee(lBoxA_16) = ' ||
2607: tab_employee(lBoxA_16));
2608:
2609: -- Box A, 17
2610: l_box := pay_ca_archive_utils.get_archive_value(
2624: l_formatted_box || '' || EOL;
2625: ELSE
2626: tab_employee(lBoxA_17) := NULL;
2627: END IF;
2628: hr_utility.trace('tab_employee(lBoxA_17) = ' ||
2629: tab_employee(lBoxA_17));
2630:
2631: -- Box A, 18
2632: l_box := pay_ca_archive_utils.get_archive_value(
2646: l_formatted_box || '' || EOL;
2647: ELSE
2648: tab_employee(lBoxA_18) := NULL;
2649: END IF;
2650: hr_utility.trace('tab_employee(lBoxA_18) = ' ||
2651: tab_employee(lBoxA_18));
2652:
2653: -- Box A, 19
2654: l_box := pay_ca_archive_utils.get_archive_value(
2668: l_formatted_box || '' || EOL;
2669: ELSE
2670: tab_employee(lBoxA_19) := NULL;
2671: END IF;
2672: hr_utility.trace('tab_employee(lBoxA_19) = ' ||
2673: tab_employee(lBoxA_19));
2674:
2675: -- Box A, 25
2676: l_box := pay_ca_archive_utils.get_archive_value(
2690: l_formatted_box || '' || EOL;
2691: ELSE
2692: tab_employee(lBoxA_25) := NULL;
2693: END IF;
2694: hr_utility.trace('tab_employee(lBoxA_25) = ' ||
2695: tab_employee(lBoxA_25));
2696:
2697: -- Box A, 26
2698: l_box := pay_ca_archive_utils.get_archive_value(
2712: l_formatted_box || '' || EOL;
2713: ELSE
2714: tab_employee(lBoxA_26) := NULL;
2715: END IF;
2716: hr_utility.trace('tab_employee(lBoxA_26) = ' ||
2717: tab_employee(lBoxA_26));
2718:
2719: -- Box A, 27
2720: l_box := pay_ca_archive_utils.get_archive_value(
2734: l_formatted_box || '' || EOL;
2735: ELSE
2736: tab_employee(lBoxA_27) := NULL;
2737: END IF;
2738: hr_utility.trace('tab_employee(lBoxA_27) = ' ||
2739: tab_employee(lBoxA_27));
2740:
2741: -- Box A, 29
2742: l_box := pay_ca_archive_utils.get_archive_value(
2756: l_formatted_box || '' || EOL;
2757: ELSE
2758: tab_employee(lBoxA_29) := NULL;
2759: END IF;
2760: hr_utility.trace('tab_employee(lBoxA_27) = ' ||
2761: tab_employee(lBoxA_29));
2762:
2763: -- Box A, 30
2764: l_box := pay_ca_archive_utils.get_archive_value(
2778: l_formatted_box || '' || EOL;
2779: ELSE
2780: tab_employee(lBoxA_30) := NULL;
2781: END IF;
2782: hr_utility.trace('tab_employee(lBoxA_30) = ' ||
2783: tab_employee(lBoxA_30));
2784:
2785: -- Box D, 07
2786: l_box := pay_ca_archive_utils.get_archive_value(
2800: l_formatted_box || '' || EOL;
2801: ELSE
2802: tab_employee(lBoxD_07) := NULL;
2803: END IF;
2804: hr_utility.trace('tab_employee(lBoxD_07) = ' ||
2805: tab_employee(lBoxD_07));
2806:
2807: -- Box D, 08
2808: l_box := pay_ca_archive_utils.get_archive_value(
2822: l_formatted_box || '' || EOL;
2823: ELSE
2824: tab_employee(lBoxD_08) := NULL;
2825: END IF;
2826: hr_utility.trace('tab_employee(lBoxD_08) = ' ||
2827: tab_employee(lBoxD_08));
2828:
2829: -- Box D, 20
2830: l_box := pay_ca_archive_utils.get_archive_value(
2844: l_formatted_box || '' || EOL;
2845: ELSE
2846: tab_employee(lBoxD_20) := NULL;
2847: END IF;
2848: hr_utility.trace('tab_employee(lBoxD_20) = ' ||
2849: tab_employee(lBoxD_20));
2850:
2851: -- Box K, 11
2852: l_box := pay_ca_archive_utils.get_archive_value(
2866: l_formatted_box || '' || EOL;
2867: ELSE
2868: tab_employee(lBoxK_11) := NULL;
2869: END IF;
2870: hr_utility.trace('tab_employee(lBoxK_11) = ' ||
2871: tab_employee(lBoxK_11));
2872:
2873: -- Box O, RA
2874: l_box := pay_ca_archive_utils.get_archive_value(
2888: l_formatted_box || '' || EOL;
2889: ELSE
2890: tab_employee(lBoxO_RA) := NULL;
2891: END IF;
2892: hr_utility.trace('tab_employee(lBoxO_RA) = ' ||
2893: tab_employee(lBoxO_RA));
2894:
2895: -- Box O, RB
2896: l_box := pay_ca_archive_utils.get_archive_value(
2910: l_formatted_box || '' || EOL;
2911: ELSE
2912: tab_employee(lBoxO_RB) := NULL;
2913: END IF;
2914: hr_utility.trace('tab_employee(lBoxO_RB) = ' ||
2915: tab_employee(lBoxO_RB));
2916:
2917: -- Box O, RC
2918: l_box := pay_ca_archive_utils.get_archive_value(
2932: l_formatted_box || '' || EOL;
2933: ELSE
2934: tab_employee(lBoxO_RC) := NULL;
2935: END IF;
2936: hr_utility.trace('tab_employee(lBoxO_RC) = ' ||
2937: tab_employee(lBoxO_RC));
2938:
2939: -- Box O, RD
2940: l_box := pay_ca_archive_utils.get_archive_value(
2954: l_formatted_box || '' || EOL;
2955: ELSE
2956: tab_employee(lBoxO_RD) := NULL;
2957: END IF;
2958: hr_utility.trace('tab_employee(lBoxO_RD) = ' ||
2959: tab_employee(lBoxO_RD));
2960:
2961: -- Box O, RE
2962: l_box := pay_ca_archive_utils.get_archive_value(
2976: l_formatted_box || '' || EOL;
2977: ELSE
2978: tab_employee(lBoxO_RE) := NULL;
2979: END IF;
2980: hr_utility.trace('tab_employee(lBoxO_RE) = ' ||
2981: tab_employee(lBoxO_RE));
2982:
2983: -- Box O, RF
2984: l_box := pay_ca_archive_utils.get_archive_value(
2998: l_formatted_box || '' || EOL;
2999: ELSE
3000: tab_employee(lBoxO_RF) := NULL;
3001: END IF;
3002: hr_utility.trace('tab_employee(lBoxO_RF) = ' ||
3003: tab_employee(lBoxO_RF));
3004:
3005: -- Box O, RG
3006: l_box := pay_ca_archive_utils.get_archive_value(
3020: l_formatted_box || '' || EOL;
3021: ELSE
3022: tab_employee(lBoxO_RG) := NULL;
3023: END IF;
3024: hr_utility.trace('tab_employee(lBoxO_RG) = ' ||
3025: tab_employee(lBoxO_RG));
3026:
3027: -- Box O, RH
3028: l_box := pay_ca_archive_utils.get_archive_value(
3042: l_formatted_box || '' || EOL;
3043: ELSE
3044: tab_employee(lBoxO_RH) := NULL;
3045: END IF;
3046: hr_utility.trace('tab_employee(lBoxO_RH) = ' ||
3047: tab_employee(lBoxO_RH));
3048:
3049: -- Box O, RI
3050: l_box := pay_ca_archive_utils.get_archive_value(
3064: l_formatted_box || '' || EOL;
3065: ELSE
3066: tab_employee(lBoxO_RI) := NULL;
3067: END IF;
3068: hr_utility.trace('tab_employee(lBoxO_RI) = ' ||
3069: tab_employee(lBoxO_RI));
3070:
3071: -- Box O, RJ
3072: l_box := pay_ca_archive_utils.get_archive_value(
3086: l_formatted_box || '' || EOL;
3087: ELSE
3088: tab_employee(lBoxO_RJ) := NULL;
3089: END IF;
3090: hr_utility.trace('tab_employee(lBoxO_RJ) = ' ||
3091: tab_employee(lBoxO_RJ));
3092:
3093: -- Box O, RK
3094: l_box := pay_ca_archive_utils.get_archive_value(
3108: l_formatted_box || '' || EOL;
3109: ELSE
3110: tab_employee(lBoxO_RK) := NULL;
3111: END IF;
3112: hr_utility.trace('tab_employee(lBoxO_RK) = ' ||
3113: tab_employee(lBoxO_RK));
3114:
3115: -- Box O, RL
3116: l_box := pay_ca_archive_utils.get_archive_value(
3130: l_formatted_box || '' || EOL;
3131: ELSE
3132: tab_employee(lBoxO_RL) := NULL;
3133: END IF;
3134: hr_utility.trace('tab_employee(lBoxO_RL) = ' ||
3135: tab_employee(lBoxO_RL));
3136:
3137: -- Box O, RL(22)
3138: l_box := pay_ca_archive_utils.get_archive_value(
3152: l_formatted_box || '' || EOL;
3153: ELSE
3154: tab_employee(lBoxO_RL22) := NULL;
3155: END IF;
3156: hr_utility.trace('tab_employee(lBoxO_RL22) = ' ||
3157: tab_employee(lBoxO_RL22));
3158:
3159: -- Box O, RL(28)
3160: l_box := pay_ca_archive_utils.get_archive_value(
3174: l_formatted_box || '' || EOL;
3175: ELSE
3176: tab_employee(lBoxO_RL28) := NULL;
3177: END IF;
3178: hr_utility.trace('tab_employee(lBoxO_RL28) = ' ||
3179: tab_employee(lBoxO_RL28));
3180:
3181: -- Box O, RM
3182: l_box := pay_ca_archive_utils.get_archive_value(
3196: l_formatted_box || '' || EOL;
3197: ELSE
3198: tab_employee(lBoxO_RM) := NULL;
3199: END IF;
3200: hr_utility.trace('tab_employee(lBoxO_RM) = ' ||
3201: tab_employee(lBoxO_RM));
3202:
3203: -- Box O, RN
3204: l_box := pay_ca_archive_utils.get_archive_value(
3218: l_formatted_box || '' || EOL;
3219: ELSE
3220: tab_employee(lBoxO_RN) := NULL;
3221: END IF;
3222: hr_utility.trace('tab_employee(lBoxO_RN) = ' ||
3223: tab_employee(lBoxO_RN));
3224:
3225: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
3226:
3221: END IF;
3222: hr_utility.trace('tab_employee(lBoxO_RN) = ' ||
3223: tab_employee(lBoxO_RN));
3224:
3225: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
3226:
3227: -- Box O, RN 31
3228: l_box := pay_ca_archive_utils.get_archive_value(
3229: l_arch_action_id,
3242: l_formatted_box || '' || EOL;
3243: ELSE
3244: tab_employee(lBoxO_RN_31) := NULL;
3245: END IF;
3246: hr_utility.trace('tab_employee(lBoxO_RN_31) = ' ||
3247: tab_employee(lBoxO_RN_31));
3248: -- Box O, RO
3249: l_box := pay_ca_archive_utils.get_archive_value(
3250: l_arch_action_id,
3263: l_formatted_box || '' || EOL;
3264: ELSE
3265: tab_employee(lBoxO_RO) := NULL;
3266: END IF;
3267: hr_utility.trace('tab_employee(lBoxO_RO) = ' ||
3268: tab_employee(lBoxO_RO));
3269:
3270: -- Box O, RP
3271: l_box := pay_ca_archive_utils.get_archive_value(
3285: l_formatted_box || '' || EOL;
3286: ELSE
3287: tab_employee(lBoxO_RP) := NULL;
3288: END IF;
3289: hr_utility.trace('tab_employee(lBoxO_RP) = ' ||
3290: tab_employee(lBoxO_RP));
3291:
3292: -- Box O, RQ
3293: l_box := pay_ca_archive_utils.get_archive_value(
3307: l_formatted_box || '' || EOL;
3308: ELSE
3309: tab_employee(lBoxO_RQ) := NULL;
3310: END IF;
3311: hr_utility.trace('tab_employee(lBoxO_RQ) = ' ||
3312: tab_employee(lBoxO_RQ));
3313:
3314: -- Box O, RR
3315: l_box := pay_ca_archive_utils.get_archive_value(
3329: l_formatted_box || '' || EOL;
3330: ELSE
3331: tab_employee(lBoxO_RR) := NULL;
3332: END IF;
3333: hr_utility.trace('tab_employee(lBoxO_RR) = ' ||
3334: tab_employee(lBoxO_RR));
3335:
3336: -- Box O, RS
3337: l_box := pay_ca_archive_utils.get_archive_value(
3351: l_formatted_box || '' || EOL;
3352: ELSE
3353: tab_employee(lBoxO_RS) := NULL;
3354: END IF;
3355: hr_utility.trace('tab_employee(lBoxO_RS) = ' ||
3356: tab_employee(lBoxO_RS));
3357:
3358: -- Box O, RT
3359: l_box := pay_ca_archive_utils.get_archive_value(
3373: l_formatted_box || '' || EOL;
3374: ELSE
3375: tab_employee(lBoxO_RT) := NULL;
3376: END IF;
3377: hr_utility.trace('tab_employee(lBoxO_RT) = ' ||
3378: tab_employee(lBoxO_RT));
3379:
3380: -- Box O, RU
3381: l_box := pay_ca_archive_utils.get_archive_value(
3395: l_formatted_box || '' || EOL;
3396: ELSE
3397: tab_employee(lBoxO_RU) := NULL;
3398: END IF;
3399: hr_utility.trace('tab_employee(lBoxO_RU) = ' ||
3400: tab_employee(lBoxO_RU));
3401:
3402: -- Box O, RV
3403: l_box := pay_ca_archive_utils.get_archive_value(
3417: l_formatted_box || '' || EOL;
3418: ELSE
3419: tab_employee(lBoxO_RV) := NULL;
3420: END IF;
3421: hr_utility.trace('tab_employee(lBoxO_RV) = ' ||
3422: tab_employee(lBoxO_RV));
3423:
3424: -- Box O, RW
3425: l_box := pay_ca_archive_utils.get_archive_value(
3439: l_formatted_box || '' || EOL;
3440: ELSE
3441: tab_employee(lBoxO_RW) := NULL;
3442: END IF;
3443: hr_utility.trace('tab_employee(lBoxO_RW) = ' ||
3444: tab_employee(lBoxO_RW));
3445:
3446: -- Box Q, 24
3447: l_box := pay_ca_archive_utils.get_archive_value(
3461: l_formatted_box || '' || EOL;
3462: ELSE
3463: tab_employee(lBoxQ_24) := NULL;
3464: END IF;
3465: hr_utility.trace('tab_employee(lBoxQ_24) = ' ||
3466: tab_employee(lBoxQ_24));
3467:
3468: -- Box R, 14
3469: l_box := pay_ca_archive_utils.get_archive_value(
3483: l_formatted_box || '' || EOL;
3484: ELSE
3485: tab_employee(lBoxR_14) := NULL;
3486: END IF;
3487: hr_utility.trace('tab_employee(lBoxR_14) = ' ||
3488: tab_employee(lBoxR_14));
3489: hr_utility.trace('l_msg_code ='|| l_msg_code);
3490:
3491: OPEN cur_get_meaning(l_msg_code);
3485: tab_employee(lBoxR_14) := NULL;
3486: END IF;
3487: hr_utility.trace('tab_employee(lBoxR_14) = ' ||
3488: tab_employee(lBoxR_14));
3489: hr_utility.trace('l_msg_code ='|| l_msg_code);
3490:
3491: OPEN cur_get_meaning(l_msg_code);
3492: FETCH cur_get_meaning
3493: INTO l_meaning;
3491: OPEN cur_get_meaning(l_msg_code);
3492: FETCH cur_get_meaning
3493: INTO l_meaning;
3494: CLOSE cur_get_meaning;
3495: hr_utility.trace('l_meaning ='|| l_meaning);
3496: tab_employee(lErrorDetails) := '
3497: l_meaning || '
3498:
3499: l_other_details := tab_employee(lBoxA_01) ||
3539: tab_employee(lBoxQ_24) ||
3540: tab_employee(lBoxR_14) ||
3541: tab_employee(lErrorDetails) ;
3542: END IF;
3543: hr_utility.trace('l_other_details ='||l_other_details);
3544: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3545: tab_employee(lH_CotisationRQAP));
3546: hr_utility.trace('l_rep_type ='|| l_rep_type);
3547: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3540: tab_employee(lBoxR_14) ||
3541: tab_employee(lErrorDetails) ;
3542: END IF;
3543: hr_utility.trace('l_other_details ='||l_other_details);
3544: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3545: tab_employee(lH_CotisationRQAP));
3546: hr_utility.trace('l_rep_type ='|| l_rep_type);
3547: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3548: --
3542: END IF;
3543: hr_utility.trace('l_other_details ='||l_other_details);
3544: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3545: tab_employee(lH_CotisationRQAP));
3546: hr_utility.trace('l_rep_type ='|| l_rep_type);
3547: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3548: --
3549:
3550: if l_rep_type = 'RL1PAPERPDF' then
3543: hr_utility.trace('l_other_details ='||l_other_details);
3544: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3545: tab_employee(lH_CotisationRQAP));
3546: hr_utility.trace('l_rep_type ='|| l_rep_type);
3547: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3548: --
3549:
3550: if l_rep_type = 'RL1PAPERPDF' then
3551: /********************************/
3549:
3550: if l_rep_type = 'RL1PAPERPDF' then
3551: /********************************/
3552: if l_neg_bal_exists = TRUE then
3553: hr_utility.trace('inside if');
3554: xml_footnote_boxo(l_arch_action_id
3555: ,l_asg_id
3556: ,l_footnote_boxo1
3557: ,l_footnote_boxo2
3767: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3768: end if; /*if l_neg_balance_exists is TRUE*/
3769: /************/
3770: if l_neg_bal_exists = FALSE then
3771: hr_utility.trace('inside if');
3772: xml_footnote_boxo(l_arch_action_id
3773: ,l_asg_id
3774: ,l_footnote_boxo1
3775: ,l_footnote_boxo2
3778: ,l_emplyr_final1
3779: ,l_emplyr_final2
3780: ,l_emplyr_final3);
3781:
3782: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3779: ,l_emplyr_final2
3780: ,l_emplyr_final3);
3781:
3782: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3780: ,l_emplyr_final3);
3781:
3782: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3788: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3781:
3782: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3788: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3789:
3782: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3788: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3789:
3790: select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;
3783: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3784: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3785: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3786: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3787: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3788: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3789:
3790: select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;
3791: l_sequence_number1:=getnext_seq_num(l_sequence_number);
4035: ||''||EOL
4036: ||''||EOL;
4037: end if;
4038: end loop;
4039: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
4040: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
4041: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
4042: pay_core_files.write_to_magtape_lob(l_final_xml_string1);
4043: pay_core_files.write_to_magtape_lob(l_final_xml_string2);
4036: ||''||EOL;
4037: end if;
4038: end loop;
4039: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
4040: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
4041: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
4042: pay_core_files.write_to_magtape_lob(l_final_xml_string1);
4043: pay_core_files.write_to_magtape_lob(l_final_xml_string2);
4044: pay_core_files.write_to_magtape_lob(l_final_xml_string3);
4037: end if;
4038: end loop;
4039: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
4040: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
4041: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
4042: pay_core_files.write_to_magtape_lob(l_final_xml_string1);
4043: pay_core_files.write_to_magtape_lob(l_final_xml_string2);
4044: pay_core_files.write_to_magtape_lob(l_final_xml_string3);
4045: --l_page_break :=' ';
4046: --l_page_break :='
4047: -- pay_core_files.write_to_magtape_lob(l_page_break);
4048: end if; /* end if of ifl_neg_balance_exists */
4049: else
4050: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
4051: tab_employee(lH_CotisationRQAP));
4052: l_final_xml_string :=
4053: '<' || l_status || '>' || EOL ||
4054: '
4099: '' || EOL || '' || EOL ||
4100: '' ;
4101:
4102:
4103: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
4104: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4105: end if;
4106: hr_utility.trace('end of xml_employee_record');
4107:
4102:
4103: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
4104: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4105: end if;
4106: hr_utility.trace('end of xml_employee_record');
4107:
4108: END;
4109: END xml_employee_record;
4110:
4120: BEGIN
4121:
4122: l_final_xml_string := '
4123:
4124: hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
4125: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4126:
4127:
4128: END;
4164: l_address_end_tag varchar2(10);
4165:
4166: BEGIN
4167:
4168: hr_utility.trace('XML Employer');
4169:
4170: SELECT
4171: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4172: INTO EOL
4189: l_taxation_year
4190: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
4191: l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
4192:
4193: hr_utility.trace ('l_cvontext1 ='||l_context1);
4194:
4195: tab_employer(lAnnee) := '
4196: tab_employer(lNbReleves) := '
4197:
4210: 'CAEOY_RL1_EMPLOYER_NAME');
4211:
4212: tab_employer(lNom1) := '
4213: convert_special_char(substr(l_employer_name,1,30)) || '
4214: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4211:
4212: tab_employer(lNom1) := '
4213: convert_special_char(substr(l_employer_name,1,30)) || '
4214: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4212: tab_employer(lNom1) := '
4213: convert_special_char(substr(l_employer_name,1,30)) || '
4214: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4213: convert_special_char(substr(l_employer_name,1,30)) || '' || EOL;
4214: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4221:
4214: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4221:
4222: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4215: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4221:
4222: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4223: tab_employer(lNom2) := '
4216: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4217: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4218: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4219: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4220: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4221:
4222: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4223: tab_employer(lNom2) := '
4224: convert_special_char(substr(l_employer_name,31,30)) || '
4224: convert_special_char(substr(l_employer_name,31,30)) || '' || EOL;
4225: ELSE
4226: tab_employer(lNom2) := NULL;
4227: END IF;
4228: hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
4229:
4230: -- Address Line 1
4231:
4232: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4247: l_address_begin_tag := '
4248:
4249: tab_employer(lLigne1) := '
4250: convert_special_char(substr(l_address_line,1,30)) || '
4251: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4252:
4253:
4254: -- Address Line 2
4255:
4261: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
4262: ELSE
4263: tab_employer(lLigne2) := NULL;
4264: END IF;
4265: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4266:
4267: -- Ville (City)
4268:
4269: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4273: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
4274: ELSE
4275: tab_employer(lVille) := NULL;
4276: END IF;
4277: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4278:
4279: -- Province
4280:
4281: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4287: l_address_line),1,20) || '' || EOL;
4288: ELSE
4289: tab_employer(lProvince) := NULL;
4290: END IF;
4291: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4292:
4293: -- Postal Code
4294:
4295: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4300: substr(replace(l_address_line,' '),1,6) || '' || EOL;
4301: ELSE
4302: tab_employer(lCodePostal) := NULL;
4303: END IF;
4304: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4305:
4306: l_address_end_tag := '';
4307:
4308: END IF;
4324: l_address_end_tag || EOL || '' || EOL ||
4325: '' || EOL ||
4326: '' || EOL;
4327:
4328: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4329: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4330: END;
4331: END xml_employer_record;
4332:
4338: DECLARE
4339: l_final_xml_string VARCHAR2(32000);
4340:
4341: BEGIN
4342: hr_utility.trace('report ends here..closing RL1PAPER tag');
4343: l_final_xml_string := '';
4344: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4345:
4346: END;
4352: DECLARE
4353: l_final_xml_string VARCHAR2(32000);
4354:
4355: BEGIN
4356: --hr_utility.trace_on(null,'SATI');
4357: hr_utility.trace('inside xml_rl1_report_start');
4358: l_final_xml_string := '
4359: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4360:
4353: l_final_xml_string VARCHAR2(32000);
4354:
4355: BEGIN
4356: --hr_utility.trace_on(null,'SATI');
4357: hr_utility.trace('inside xml_rl1_report_start');
4358: l_final_xml_string := '
4359: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4360:
4361: END;
4452: l_boxo3 :=NULL;
4453:
4454: l_see_attached := hr_general.decode_lookup('PAY_CA_LABELS'
4455: ,'SEE_ATTACHED');
4456: hr_utility.trace('l_see_attached = '||l_see_attached);
4457: SELECT
4458: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4459: INTO EOL
4460: from dual;
4462: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
4463: open c_get_language(p_assgn_id);
4464: fetch c_get_language into l_person_lang;
4465: close c_get_language;
4466: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4467: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4468: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4469: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4470: loop
4463: open c_get_language(p_assgn_id);
4464: fetch c_get_language into l_person_lang;
4465: close c_get_language;
4466: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4467: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4468: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4469: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4470: loop
4471: i:=i+1;
4464: fetch c_get_language into l_person_lang;
4465: close c_get_language;
4466: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4467: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4468: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4469: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4470: loop
4471: i:=i+1;
4472: t_footnote(i) := rec;
4478: t_boxo(i) :=rec;
4479: end loop;
4480: l_count_boxo:=t_boxo.count;
4481: l_count := t_footnote.count;
4482: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
4483: hr_utility.trace(' l_count ='|| l_count);
4484: if l_count > 1 then --more than 1 footnote
4485: p_footnote_boxo1 :='
4486: p_footnote_boxo2 :='
4479: end loop;
4480: l_count_boxo:=t_boxo.count;
4481: l_count := t_footnote.count;
4482: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
4483: hr_utility.trace(' l_count ='|| l_count);
4484: if l_count > 1 then --more than 1 footnote
4485: p_footnote_boxo1 :='
4486: p_footnote_boxo2 :='
4487: p_footnote_boxo3 :='
4500: l_footnote2 :='
4501: ||'
4502: l_footnote3 :='
4503: ||'
4504: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4505: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4506: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4507: if (l_count_boxo=1) then
4508: l_boxo1 :='
4501: ||'
4502: l_footnote3 :='
4503: ||'
4504: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4505: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4506: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4507: if (l_count_boxo=1) then
4508: l_boxo1 :='
4509: ||'
4502: l_footnote3 :='
4503: ||'
4504: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4505: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4506: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4507: if (l_count_boxo=1) then
4508: l_boxo1 :='
4509: ||'
4510: l_boxo2 :='
4517: p_footnote_boxo1 :=l_footnote1||l_boxo1;
4518: p_footnote_boxo2 :=l_footnote2||l_boxo2;
4519: p_footnote_boxo3 :=l_footnote3||l_boxo3;
4520: elsif l_count = 0 and l_count_boxo < 4 then
4521: hr_utility.trace('boxo count less than 4');
4522: for k in 1.. l_count_boxo
4523: loop
4524: l_boxo1 :=l_boxo1||'
4525: ||'
4528: l_boxo3 :=l_boxo3||'
4529: ||'
4530:
4531: end loop;
4532: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4533: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4534: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4535:
4536: p_footnote_boxo1 :=l_boxo1;
4529: ||'
4530:
4531: end loop;
4532: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4533: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4534: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4535:
4536: p_footnote_boxo1 :=l_boxo1;
4537: p_footnote_boxo2 :=l_boxo2;
4530:
4531: end loop;
4532: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4533: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4534: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4535:
4536: p_footnote_boxo1 :=l_boxo1;
4537: p_footnote_boxo2 :=l_boxo2;
4538: p_footnote_boxo3 :=l_boxo3;
4542: p_footnote_boxo2 :='
4543: p_footnote_boxo3 :='
4544: end if;
4545:
4546: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4547: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4548: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4549:
4550: end xml_footnote_boxo;
4543: p_footnote_boxo3 :='
4544: end if;
4545:
4546: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4547: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4548: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4549:
4550: end xml_footnote_boxo;
4551:
4544: end if;
4545:
4546: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4547: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4548: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4549:
4550: end xml_footnote_boxo;
4551:
4552:
4581: l_quebec_bn varchar2(20);
4582:
4583: BEGIN
4584:
4585: hr_utility.trace('XML Employer');
4586:
4587: SELECT
4588: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4589: INTO EOL
4602: open c_get_arch_pay_actid;
4603: fetch c_get_arch_pay_actid
4604: into l_context1;
4605: close c_get_arch_pay_actid;
4606: hr_utility.trace ('l_cvontext1 ='||l_context1);
4607:
4608: l_quebec_bn := pay_ca_archive_utils.get_archive_value
4609: (l_context1,'CAEOY_RL1_QUEBEC_BN');
4610: tab_employer(lNoId) := substr(l_quebec_bn,1,10); -- employer id
4612: l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
4613: 'CAEOY_RL1_EMPLOYER_NAME');
4614:
4615: tab_employer(lNom1) := convert_special_char(l_employer_name);
4616: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4617: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4618:
4619: -- Address Line 1
4620: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4613: 'CAEOY_RL1_EMPLOYER_NAME');
4614:
4615: tab_employer(lNom1) := convert_special_char(l_employer_name);
4616: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4617: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4618:
4619: -- Address Line 1
4620: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4621: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4619: -- Address Line 1
4620: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4621: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4622: tab_employer(lLigne1) := convert_special_char(substr(l_address_line,1,40));
4623: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4624:
4625: -- Address Line 2
4626: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4627: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4625: -- Address Line 2
4626: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4627: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4628: tab_employer(lLigne2) := convert_special_char(substr(l_address_line,1,40));
4629: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4630:
4631: -- Address Line 3
4632: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4633: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
4631: -- Address Line 3
4632: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4633: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
4634: tab_employer(lLigne3) := convert_special_char(substr(l_address_line,1,40));
4635: hr_utility.trace('tab_employer(lLigne3) = ' || tab_employer(lLigne3));
4636:
4637: if(tab_employer(lLigne2) is not null or tab_employer(lLigne3) is not null) then
4638: tab_employer(lLigne2) := tab_employer(lLigne2) ||' '|| tab_employer(lLigne3) ; /*******/
4639: end if;
4641: -- Ville (City)
4642: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4643: 'CAEOY_RL1_EMPLOYER_CITY');
4644: tab_employer(lVille) := convert_special_char(substr(l_address_line,1,30));
4645: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4646:
4647: -- Province
4648:
4649: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4649: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4650: 'CAEOY_RL1_EMPLOYER_PROVINCE');
4651:
4652: tab_employer(lProvince) :=l_address_line;
4653: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4654:
4655: --Country
4656: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4657: 'CAEOY_RL1_EMPLOYER_COUNTRY');
4655: --Country
4656: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4657: 'CAEOY_RL1_EMPLOYER_COUNTRY');
4658: tab_employer(lCountry) :=l_address_line;
4659: hr_utility.trace('tab_employer(lCountry) = ' || tab_employer(lCountry));
4660:
4661: -- Postal Code
4662: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4663: 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
4669: tab_employer(lCountry) ||' '||
4670: substr(tab_employer(lCodePostal),1,3)||' '||
4671: substr(tab_employer(lCodePostal),4,3);
4672:
4673: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4674: for l_index in 1..3 loop
4675: l_final_xml_string := '
4676:
4677: /*********************************************/
4711: end if;
4712: ******************************************************/
4713:
4714:
4715: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4716: if l_index=1 then
4717: p_emplyr_final1 := l_final_xml_string;
4718: end if;
4719: if l_index=2 then