[Home] [Help]
105: p_report_type IN OUT NOCOPY VARCHAR2,
106: p_business_group_id IN OUT NOCOPY NUMBER
107: ) IS
108: BEGIN
109: --hr_utility.trace_on('Y','RL1AMD');
110: hr_utility.set_location('pay_ca_rl1_amend_mag.get_report_parameters', 10);
111:
112: SELECT ppa.start_date,
113: ppa.effective_date,
106: p_business_group_id IN OUT NOCOPY NUMBER
107: ) IS
108: BEGIN
109: --hr_utility.trace_on('Y','RL1AMD');
110: hr_utility.set_location('pay_ca_rl1_amend_mag.get_report_parameters', 10);
111:
112: SELECT ppa.start_date,
113: ppa.effective_date,
114: ppa.business_group_id,
119: p_report_type
120: FROM pay_payroll_actions ppa
121: WHERE payroll_action_id = p_pactid;
122:
123: hr_utility.set_location('pay_ca_rl1_amend_mag.get_report_parameters', 20);
124:
125: END get_report_parameters;
126:
127: PROCEDURE validate_transmitter_info(p_payroll_action_id IN NUMBER,
174:
175: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
176: l_legislative_parameters);
177:
178: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
179: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
180: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
181: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
182:
175: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
176: l_legislative_parameters);
177:
178: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
179: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
180: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
181: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
182:
183: OPEN cur_arch_pactid(l_transmitter_org_id);
176: l_legislative_parameters);
177:
178: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
179: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
180: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
181: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
182:
183: OPEN cur_arch_pactid(l_transmitter_org_id);
184: FETCH cur_arch_pactid
177:
178: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
179: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
180: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
181: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
182:
183: OPEN cur_arch_pactid(l_transmitter_org_id);
184: FETCH cur_arch_pactid
185: INTO l_arch_pactid;
188: l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
189: l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
190: BEGIN
191:
192: hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
193: SELECT substr(l_transmitter_number,1,2)
194: INTO dummy1
195: FROM dual;
196:
204: FROM dual;
205:
206: EXCEPTION
207: WHEN INVALID_NUMBER THEN
208: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
209: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
210: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
211: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
212: hr_utility.raise_error;
205:
206: EXCEPTION
207: WHEN INVALID_NUMBER THEN
208: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
209: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
210: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
211: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
212: hr_utility.raise_error;
213: END;
208: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
209: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
210: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
211: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
212: hr_utility.raise_error;
213: END;
214:
215: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
216:
213: END;
214:
215: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
216:
217: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
218:
219: IF l_type_of_package IS NULL THEN
220: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
221: hr_utility.raise_error;
217: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
218:
219: IF l_type_of_package IS NULL THEN
220: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
221: hr_utility.raise_error;
222: END IF;
223:
224: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
225: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
221: hr_utility.raise_error;
222: END IF;
223:
224: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
225: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
226:
227: IF l_source_of_slips IS NULL THEN
228: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
229: hr_utility.raise_error;
225: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
226:
227: IF l_source_of_slips IS NULL THEN
228: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
229: hr_utility.raise_error;
230: END IF;
231:
232: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
233: hr_utility.trace('l_address_line1 = ' || l_address_line1);
229: hr_utility.raise_error;
230: END IF;
231:
232: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
233: hr_utility.trace('l_address_line1 = ' || l_address_line1);
234:
235: IF l_address_line1 IS NULL THEN
236: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
237: hr_utility.raise_error;
233: hr_utility.trace('l_address_line1 = ' || l_address_line1);
234:
235: IF l_address_line1 IS NULL THEN
236: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
237: hr_utility.raise_error;
238: END IF;
239:
240: END;
241:
299: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
300: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
301: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
302:
303: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
304: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
305: g_further_info_list.delete;
306: for rec in cur_boxo
307: loop
300: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
301: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
302:
303: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
304: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
305: g_further_info_list.delete;
306: for rec in cur_boxo
307: loop
308: i:=i+1;
305: g_further_info_list.delete;
306: for rec in cur_boxo
307: loop
308: i:=i+1;
309: hr_utility.trace('i '||i);
310: hr_utility.trace('rec.code '||rec.code);
311: hr_utility.trace('rec.value '||rec.value);
312: g_further_info_list(i).code := rec.code;
313: g_further_info_list(i).amount := rec.value;
306: for rec in cur_boxo
307: loop
308: i:=i+1;
309: hr_utility.trace('i '||i);
310: hr_utility.trace('rec.code '||rec.code);
311: hr_utility.trace('rec.value '||rec.value);
312: g_further_info_list(i).code := rec.code;
313: g_further_info_list(i).amount := rec.value;
314: end loop;
307: loop
308: i:=i+1;
309: hr_utility.trace('i '||i);
310: hr_utility.trace('rec.code '||rec.code);
311: hr_utility.trace('rec.value '||rec.value);
312: g_further_info_list(i).code := rec.code;
313: g_further_info_list(i).amount := rec.value;
314: end loop;
315:
315:
316: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
317: loop
318: i:=i+1;
319: hr_utility.trace('i '||i);
320: hr_utility.trace('rec.code '||rec.code);
321: hr_utility.trace('rec.value '||rec.value);
322: g_further_info_list(i).code := rec.code;
323: g_further_info_list(i).amount := rec.value;
316: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
317: loop
318: i:=i+1;
319: hr_utility.trace('i '||i);
320: hr_utility.trace('rec.code '||rec.code);
321: hr_utility.trace('rec.value '||rec.value);
322: g_further_info_list(i).code := rec.code;
323: g_further_info_list(i).amount := rec.value;
324: end loop;
317: loop
318: i:=i+1;
319: hr_utility.trace('i '||i);
320: hr_utility.trace('rec.code '||rec.code);
321: hr_utility.trace('rec.value '||rec.value);
322: g_further_info_list(i).code := rec.code;
323: g_further_info_list(i).amount := rec.value;
324: end loop;
325:
338: p_amount in out nocopy varchar2)
339: is
340: begin
341:
342: hr_utility.trace('p_index '||p_index);
343: if g_further_info_list.exists(p_index) then
344: p_code := g_further_info_list(p_index).code;
345: p_amount := g_further_info_list(p_index).amount;
346: else
372: p_business_group_id NUMBER;
373: p_report_type VARCHAR2(30);
374:
375: BEGIN
376: -- hr_utility.trace_on(null,'PDF');
377: hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor', 10);
378:
379: get_report_parameters(
380: p_pactid,
373: p_report_type VARCHAR2(30);
374:
375: BEGIN
376: -- hr_utility.trace_on(null,'PDF');
377: hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor', 10);
378:
379: get_report_parameters(
380: p_pactid,
381: p_year_start,
383: p_report_type,
384: p_business_group_id
385: );
386:
387: hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor', 20);
388:
389:
390: p_sqlstr := 'select distinct to_number(fai1.value)
391: from ff_archive_items fai1,
414: and hoi.org_information_context =''Prov Reporting Est''
415: and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
416: order by to_number(fai1.value)' ;
417:
418: hr_utility.set_location( 'pay_ca_rl1_amend_mag.range_cursor',40);
419:
420: END range_cursor;
421:
422: -------------------------------------------------------------------------------
642: l_paa_update_check pay_assignment_actions.assignment_action_id%TYPE;
643:
644: BEGIN
645:
646: -- hr_utility.trace_on('Y','RL1MAG');
647:
648: l_prev_payact := -1;
649: hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',10);
650:
645:
646: -- hr_utility.trace_on('Y','RL1MAG');
647:
648: l_prev_payact := -1;
649: hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',10);
650:
651: get_report_parameters(
652: p_pactid,
653: l_year_start,
659: validate_transmitter_info(p_pactid,
660: l_business_group_id,
661: l_year_end);
662:
663: hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',20);
664:
665: hr_utility.trace('l_business_group_id ='|| l_business_group_id);
666: hr_utility.trace('l_year_end ='|| l_year_end);
667: --IF l_report_type = 'PYRL1MAG' THEN
661: l_year_end);
662:
663: hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',20);
664:
665: hr_utility.trace('l_business_group_id ='|| l_business_group_id);
666: hr_utility.trace('l_year_end ='|| l_year_end);
667: --IF l_report_type = 'PYRL1MAG' THEN
668:
669: open c_get_ue_id('CAEOY_RL1_AMENDMENT_FLAG');
662:
663: hr_utility.set_location( 'pay_ca_rl1_amend_mag.create_assignement_act',20);
664:
665: hr_utility.trace('l_business_group_id ='|| l_business_group_id);
666: hr_utility.trace('l_year_end ='|| l_year_end);
667: --IF l_report_type = 'PYRL1MAG' THEN
668:
669: open c_get_ue_id('CAEOY_RL1_AMENDMENT_FLAG');
670: fetch c_get_ue_id into ln_rl1_amend_flag_ue_id;
679: l_assignment_action_id,
680: l_payroll_act;
681:
682:
683: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 30);
684:
685: EXIT WHEN c_all_asg%NOTFOUND;
686:
687: /* Added by ssmukher for validating the
687: /* Added by ssmukher for validating the
688: Quebec Identification Number */
689:
690: if l_prev_payact <> l_payroll_act and l_report_type = 'CAEOY_RL1_AMEND_PP' then
691: hr_utility.trace('The payroll action id '||l_payroll_act);
692:
693: l_prev_payact := l_payroll_act;
694: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
695: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
693: l_prev_payact := l_payroll_act;
694: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
695: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
696:
697: hr_utility.trace('The Quebec Number is '||l_quebec_val);
698:
699: l_quebec_no := substr(l_quebec_val ,1,10);
700:
701: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
697: hr_utility.trace('The Quebec Number is '||l_quebec_val);
698:
699: l_quebec_no := substr(l_quebec_val ,1,10);
700:
701: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
702: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
703: l_return := pay_ca_rl1_mag.validate_quebec_number(l_quebec_val,l_quebec_name);
704:
705: end if ;
698:
699: l_quebec_no := substr(l_quebec_val ,1,10);
700:
701: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
702: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
703: l_return := pay_ca_rl1_mag.validate_quebec_number(l_quebec_val,l_quebec_name);
704:
705: end if ;
706:
705: end if ;
706:
707: --Create the assignment action for the record
708:
709: hr_utility.trace('Assignment Fetched - ');
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
706:
707: --Create the assignment action for the record
708:
709: hr_utility.trace('Assignment Fetched - ');
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
707: --Create the assignment action for the record
708:
709: hr_utility.trace('Assignment Fetched - ');
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
715:
708:
709: hr_utility.trace('Assignment Fetched - ');
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
715:
716: /* Create an assignment action for this person */
709: hr_utility.trace('Assignment Fetched - ');
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
715:
716: /* Create an assignment action for this person */
717:
710: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
711: hr_utility.trace('Person Id : '|| to_char(l_person_id));
712: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
713: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
714: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 40);
715:
716: /* Create an assignment action for this person */
717:
718:
718:
719: select pay_assignment_actions_s.nextval
720: into lockingactid
721: from dual;
722: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 50);
723: hr_utility.trace('New RL1 Amend Action = ' ||
724: to_char(lockingactid));
725:
726: open get_latest_rl1_amend_dtls(l_person_id,
719: select pay_assignment_actions_s.nextval
720: into lockingactid
721: from dual;
722: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 50);
723: hr_utility.trace('New RL1 Amend Action = ' ||
724: to_char(lockingactid));
725:
726: open get_latest_rl1_amend_dtls(l_person_id,
727: --l_tax_unit_id,
743: fetch get_warning_dtls_for_ee into lv_full_name
744: ,lv_employee_number;
745: close get_warning_dtls_for_ee;
746:
747: hr_utility.trace('get_latest_rl1_amend_dtls not found');
748: hr_utility.trace('p_person_id :'||to_char(l_person_id));
749:
750:
751: lv_record_name := 'RL1 Amendment Magnetic Media';
744: ,lv_employee_number;
745: close get_warning_dtls_for_ee;
746:
747: hr_utility.trace('get_latest_rl1_amend_dtls not found');
748: hr_utility.trace('p_person_id :'||to_char(l_person_id));
749:
750:
751: lv_record_name := 'RL1 Amendment Magnetic Media';
752:
765: ln_iteration := ln_iteration + 1;
766:
767: if get_latest_rl1_amend_dtls%found then
768:
769: hr_utility.trace('get_latest_rl1_amend_dtls found ');
770: hr_utility.trace('Report Type: '||lv_report_type);
771:
772: if lv_report_type='CAEOY_RL1_AMEND_PP' then
773: begin
766:
767: if get_latest_rl1_amend_dtls%found then
768:
769: hr_utility.trace('get_latest_rl1_amend_dtls found ');
770: hr_utility.trace('Report Type: '||lv_report_type);
771:
772: if lv_report_type='CAEOY_RL1_AMEND_PP' then
773: begin
774:
780:
781: lv_prov_amend_flag := 'N';
782: fetch c_get_prov_amend_flag into lv_prov_amend_flag,
783: lv_prov_of_emp;
784: hr_utility.trace('lv_prov_amend_flag : '||lv_prov_amend_flag);
785: hr_utility.trace('lv_prov_of_emp : '||lv_prov_of_emp);
786: exit when c_get_prov_amend_flag%NOTFOUND;
787:
788: if c_get_prov_amend_flag%FOUND then
781: lv_prov_amend_flag := 'N';
782: fetch c_get_prov_amend_flag into lv_prov_amend_flag,
783: lv_prov_of_emp;
784: hr_utility.trace('lv_prov_amend_flag : '||lv_prov_amend_flag);
785: hr_utility.trace('lv_prov_of_emp : '||lv_prov_of_emp);
786: exit when c_get_prov_amend_flag%NOTFOUND;
787:
788: if c_get_prov_amend_flag%FOUND then
789: --To make sure only the latest amendment details are printed
806: set aa.serial_number = to_char(l_person_id)
807: where aa.assignment_action_id = lockingactid;
808:
809: --Added to lock the Amend Archiver
810: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
811: hr_nonrun_asact.insint(lockingactid
812: ,ln_asg_act_to_lock);
813: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814: hr_utility.trace('Locking Action'||lockingactid);
809: --Added to lock the Amend Archiver
810: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
811: hr_nonrun_asact.insint(lockingactid
812: ,ln_asg_act_to_lock);
813: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814: hr_utility.trace('Locking Action'||lockingactid);
815: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
816: hr_utility.trace('lv_flag_count = ' || to_char(lv_flag_count));
817: lv_flag_count := lv_flag_count + 1;
810: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
811: hr_nonrun_asact.insint(lockingactid
812: ,ln_asg_act_to_lock);
813: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814: hr_utility.trace('Locking Action'||lockingactid);
815: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
816: hr_utility.trace('lv_flag_count = ' || to_char(lv_flag_count));
817: lv_flag_count := lv_flag_count + 1;
818: end if; -- lv_prov_amend_flag = 'Y'
811: hr_nonrun_asact.insint(lockingactid
812: ,ln_asg_act_to_lock);
813: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814: hr_utility.trace('Locking Action'||lockingactid);
815: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
816: hr_utility.trace('lv_flag_count = ' || to_char(lv_flag_count));
817: lv_flag_count := lv_flag_count + 1;
818: end if; -- lv_prov_amend_flag = 'Y'
819:
812: ,ln_asg_act_to_lock);
813: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
814: hr_utility.trace('Locking Action'||lockingactid);
815: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
816: hr_utility.trace('lv_flag_count = ' || to_char(lv_flag_count));
817: lv_flag_count := lv_flag_count + 1;
818: end if; -- lv_prov_amend_flag = 'Y'
819:
820: end if; -- c_get_prov_amend_flag%FOUND
829: if lv_report_type in ('RL1_XML_MAG','RL1_AMEND_MAG') then --To lock previous Mag Reports
830:
831: open c_paa_update_check(lockingactid);
832: fetch c_paa_update_check into l_paa_update_check;
833: hr_utility.trace('l_update_check : '||l_paa_update_check);
834: if c_paa_update_check%FOUND then
835:
836: /***********************************************************
837: ** Update the serial number column with Province_code QC,
843: set aa.serial_number = to_char(l_person_id)
844: where aa.assignment_action_id = lockingactid;
845:
846: --Added to lock previous mag reports
847: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
848: hr_nonrun_asact.insint(lockingactid
849: ,ln_asg_act_to_lock);
850: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
851: hr_utility.trace('Locking Action'||lockingactid);
846: --Added to lock previous mag reports
847: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
848: hr_nonrun_asact.insint(lockingactid
849: ,ln_asg_act_to_lock);
850: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
851: hr_utility.trace('Locking Action'||lockingactid);
852: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
853:
854: end if;--c_paa_update_check%FOUND
847: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 60);
848: hr_nonrun_asact.insint(lockingactid
849: ,ln_asg_act_to_lock);
850: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
851: hr_utility.trace('Locking Action'||lockingactid);
852: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
853:
854: end if;--c_paa_update_check%FOUND
855: close c_paa_update_check;
848: hr_nonrun_asact.insint(lockingactid
849: ,ln_asg_act_to_lock);
850: hr_utility.set_location('pay_ca_rl1_amend_mag.create_assignement_act', 70);
851: hr_utility.trace('Locking Action'||lockingactid);
852: hr_utility.trace('Locked Action = ' || to_char(ln_asg_act_to_lock));
853:
854: end if;--c_paa_update_check%FOUND
855: close c_paa_update_check;
856:
938: l_VersionSchema VARCHAR2(20);
939:
940: BEGIN
941:
942: hr_utility.trace('XML Transmitter');
943:
944: SELECT
945: fnd_global.local_chr(13) || fnd_global.local_chr(10)
946: INTO EOL
974: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
975: l_context1
976: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
977:
978: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
979: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
980:
981:
982: -- Annee
975: l_context1
976: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
977:
978: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
979: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
980:
981:
982: -- Annee
983: tab_transmitter(lAnnee) := '
980:
981:
982: -- Annee
983: tab_transmitter(lAnnee) := '
984: hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
985:
986: -- TypeEnvoi
987: tab_transmitter(lTypeEnvoi) := '
988: pay_ca_archive_utils.get_archive_value(l_context1,
986: -- TypeEnvoi
987: tab_transmitter(lTypeEnvoi) := '
988: pay_ca_archive_utils.get_archive_value(l_context1,
989: 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE') || '
990: hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
991: tab_transmitter(lTypeEnvoi));
992:
993: tab_transmitter(lProvenance) := '
994: pay_ca_archive_utils.get_archive_value(l_context1,
993: tab_transmitter(lProvenance) := '
994: pay_ca_archive_utils.get_archive_value(l_context1,
995: 'CAEOY_RL1_SOURCE_OF_SLIPS') || '
996:
997: hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
998:
999: tab_transmitter(lNo) := '
1000: pay_ca_archive_utils.get_archive_value(l_context1,
1001: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
999: tab_transmitter(lNo) := '
1000: pay_ca_archive_utils.get_archive_value(l_context1,
1001: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
1002:
1003: hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
1004:
1005: l_return := pay_ca_archive_utils.get_archive_value(l_context1,
1006: 'CAEOY_RL1_TRANSMITTER_TYPE');
1007: IF l_return IS NOT NULL THEN
1009: ELSE
1010: tab_transmitter(lType) := NULL;
1011: END IF;
1012:
1013: hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
1014:
1015: l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
1016: 'CAEOY_RL1_TRANSMITTER_NAME');
1017:
1017:
1018: tab_transmitter(lNom1) := '
1019: pay_ca_rl1_mag.convert_special_char(substr(l_transmitter_name,1,30)) || '
1020:
1021: hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
1022:
1023: l_return := substr(l_transmitter_name,31,30);
1024: IF l_return IS NOT NULL THEN
1025: tab_transmitter(lNom2) := '
1026: ELSE
1027: tab_transmitter(lNom2) := NULL;
1028: END IF;
1029:
1030: hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
1031:
1032: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1033: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
1034:
1034:
1035: tab_transmitter(lLigne1) := '
1036: pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '
1037:
1038: hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
1039:
1040: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1041: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
1042:
1046: ELSE
1047: tab_transmitter(lLigne2) := NULL;
1048: END IF;
1049:
1050: hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
1051:
1052: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1053: 'CAEOY_RL1_TRANSMITTER_CITY');
1054:
1058: ELSE
1059: tab_transmitter(lVille) := NULL;
1060: END IF;
1061:
1062: hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1063:
1064: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1065: 'CAEOY_RL1_TRANSMITTER_PROVINCE');
1066:
1067: tab_transmitter(lProvince) := '
1068: SUBSTR(hr_general.decode_lookup(
1069: 'CA_PROVINCE',l_address_line),1,20) || '
1070:
1071: hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1072:
1073: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1074: 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
1075:
1075:
1076: tab_transmitter(lCodePostal) := '
1077: substr(replace(l_address_line,' '),1,6) || '
1078:
1079: hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1080:
1081: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1082: 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
1083:
1087: ELSE
1088: tab_transmitter(lNom) := NULL;
1089: END IF;
1090:
1091: hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1092:
1093: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1094: 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
1095:
1099: ELSE
1100: tab_transmitter(lIndRegional) := NULL;
1101: END IF;
1102:
1103: hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1104:
1105: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1106: 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
1107: IF l_contact IS NOT NULL THEN
1109: tab_transmitter(lTel) := '
1110: ELSE
1111: tab_transmitter(lTel) := NULL;
1112: END IF;
1113: hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1114:
1115: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1116: 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
1117:
1120: '' || EOL;
1121: ELSE
1122: tab_transmitter(lPosteTel) := NULL;
1123: END IF;
1124: hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1125: tab_transmitter(lPosteTel));
1126:
1127: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1128: 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
1141: pay_ca_rl1_mag.convert_special_char(substr(l_contact,1,30)) || '' || EOL;
1142: ELSE
1143: tab_transmitter(lANom) := NULL;
1144: END IF;
1145: hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1146:
1147: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1148: 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
1149:
1152: '' || EOL;
1153: ELSE
1154: tab_transmitter(lAIndRegional) := NULL;
1155: END IF;
1156: hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1157:
1158: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1159: 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
1160:
1163: tab_transmitter(lATel) := '
1164: ELSE
1165: tab_transmitter(lATel) := NULL;
1166: END IF;
1167: hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1168:
1169: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1170: 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
1171:
1174: '' || EOL;
1175: ELSE
1176: tab_transmitter(lAPosteTel) := NULL;
1177: END IF;
1178: hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1179: tab_transmitter(lAPosteTel));
1180:
1181: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1182: 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
1186: ELSE
1187: tab_transmitter(lALangue) := NULL;
1188: END IF;
1189:
1190: hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1191:
1192: -- For bug 6738509
1193: if(l_taxation_year = '2006') then
1194: l_certification_no := 'RQ-06-01-098';
1258: tab_transmitter(lALangue) || '' || EOL ||
1259: tab_transmitter(lNoConcepteur) ||
1260: '
1277: BEGIN
1278:
1279: l_final_xml_string := '';
1280:
1281: hr_utility.trace('end_of_file l_final_xml_string = '
1282: || l_final_xml_string );
1283: pay_core_files.write_to_magtape_lob(l_final_xml_string);
1284:
1285: END;
1631: l_check_number number;
1632: BEGIN
1633:
1634: l_check_number := mod(p_curr_seq,7);
1635: hr_utility.trace('l_check_number ='|| l_check_number);
1636: l_seq_number := (p_curr_seq * 10) + l_check_number;
1637: hr_utility.trace('l_seq_number ='|| l_seq_number);
1638: return l_seq_number;
1639: END; */
1633:
1634: l_check_number := mod(p_curr_seq,7);
1635: hr_utility.trace('l_check_number ='|| l_check_number);
1636: l_seq_number := (p_curr_seq * 10) + l_check_number;
1637: hr_utility.trace('l_seq_number ='|| l_seq_number);
1638: return l_seq_number;
1639: END; */
1640:
1641: BEGIN
1638: return l_seq_number;
1639: END; */
1640:
1641: BEGIN
1642: -- hr_utility.trace_on(null,'PDF');
1643: hr_utility.trace('Inside the Employee record proc');
1644: /*******************************************************************************/
1645: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1646: l_payroll_actid
1639: END; */
1640:
1641: BEGIN
1642: -- hr_utility.trace_on(null,'PDF');
1643: hr_utility.trace('Inside the Employee record proc');
1644: /*******************************************************************************/
1645: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1646: l_payroll_actid
1647: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1644: /*******************************************************************************/
1645: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1646: l_payroll_actid
1647: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1648: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1649: SELECT ppa.report_type
1650: INTO l_rep_type
1651: from pay_payroll_actions ppa
1652: where payroll_action_id=l_payroll_actid;
1649: SELECT ppa.report_type
1650: INTO l_rep_type
1651: from pay_payroll_actions ppa
1652: where payroll_action_id=l_payroll_actid;
1653: hr_utility.trace('report_type='||l_rep_type);
1654: /******************************* ************************************/
1655:
1656:
1657: hr_utility.trace('XML Employee');
1653: hr_utility.trace('report_type='||l_rep_type);
1654: /******************************* ************************************/
1655:
1656:
1657: hr_utility.trace('XML Employee');
1658: l_status := 'Success';
1659: l_all_box_0 := TRUE;
1660: l_format_mask := '99999999999999990.99';
1661:
1766:
1767: l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1768: ('TRANSFER_ACT_ID'));
1769:
1770: hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1771: || to_char(l_mag_asg_action_id));
1772:
1773: /**********************************************************************/
1774:
1782: -- CLOSE cur_parameters;
1783: if cur_parameters%found then
1784: /**************************************** ******************/
1785:
1786: hr_utility.trace('XML Employee: l_arch_action_id = '
1787: || to_char(l_arch_action_id));
1788: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1789: hr_utility.trace('XML Employee: l_date_earned = '
1790: || to_char(l_date_earned));
1784: /**************************************** ******************/
1785:
1786: hr_utility.trace('XML Employee: l_arch_action_id = '
1787: || to_char(l_arch_action_id));
1788: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1789: hr_utility.trace('XML Employee: l_date_earned = '
1790: || to_char(l_date_earned));
1791: hr_utility.trace('XML Employee: l_province = ' || l_province);
1792:
1785:
1786: hr_utility.trace('XML Employee: l_arch_action_id = '
1787: || to_char(l_arch_action_id));
1788: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1789: hr_utility.trace('XML Employee: l_date_earned = '
1790: || to_char(l_date_earned));
1791: hr_utility.trace('XML Employee: l_province = ' || l_province);
1792:
1793: l_taxation_year
1787: || to_char(l_arch_action_id));
1788: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1789: hr_utility.trace('XML Employee: l_date_earned = '
1790: || to_char(l_date_earned));
1791: hr_utility.trace('XML Employee: l_province = ' || l_province);
1792:
1793: l_taxation_year
1794: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1795:
1810: close c_get_auth_code;
1811:
1812: --Annee
1813: tab_employee(lAnnee) := '
1814: hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1815: --NoReleve
1816: /*Bug 13564765 here*/
1817: lv_rl1_slip_no := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1818: 'CAEOY_RL1_SLIP_NUMBER');
1826: tab_employee(lNoReleve) := '
1827: '
1828: tab_xml_employee(lNoReleve) := l_return; --
1829: END IF;
1830: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1831: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1832:
1833: -- NAS
1834: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1827: '' || EOL;
1828: tab_xml_employee(lNoReleve) := l_return; --
1829: END IF;
1830: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1831: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1832:
1833: -- NAS
1834: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1835: 'CAEOY_EMPLOYEE_SIN');
1843: l_msg_code := 'SIN';
1844: tab_employee(lNAS) := NULL;
1845: tab_xml_employee(lNAS) := NULL; --
1846: END IF;
1847: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1848: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1849:
1850: -- No
1851: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1844: tab_employee(lNAS) := NULL;
1845: tab_xml_employee(lNAS) := NULL; --
1846: END IF;
1847: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1848: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1849:
1850: -- No
1851: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1852: 'CAEOY_EMPLOYEE_NUMBER');
1856: ELSE
1857: tab_employee(lNo) := NULL;
1858: tab_xml_employee(lNo) := NULL; --
1859: END IF;
1860: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1861: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1862:
1863: -- NomFamille
1864: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1857: tab_employee(lNo) := NULL;
1858: tab_xml_employee(lNo) := NULL; --
1859: END IF;
1860: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1861: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1862:
1863: -- NomFamille
1864: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1865: 'CAEOY_EMPLOYEE_LAST_NAME');
1865: 'CAEOY_EMPLOYEE_LAST_NAME');
1866: tab_employee(lNomFamille) := '
1867: pay_ca_rl1_mag.convert_special_char(substr(l_name,1,30)) || '
1868: tab_xml_employee(lNomFamille) := pay_ca_rl1_mag.convert_special_char(substr(l_name,1,20)); --
1869: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1870: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1871:
1872: -- Prenom
1873: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1866: tab_employee(lNomFamille) := '
1867: pay_ca_rl1_mag.convert_special_char(substr(l_name,1,30)) || '
1868: tab_xml_employee(lNomFamille) := pay_ca_rl1_mag.convert_special_char(substr(l_name,1,20)); --
1869: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1870: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1871:
1872: -- Prenom
1873: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1874: 'CAEOY_EMPLOYEE_FIRST_NAME');
1882: l_msg_code := 'MISSING_EMP_FIRST_NAME';
1883: tab_employee(lPrenom) := NULL;
1884: tab_xml_employee(lPrenom) := NULL ; --
1885: END IF;
1886: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1887: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1888:
1889: -- Initiale
1890: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1883: tab_employee(lPrenom) := NULL;
1884: tab_xml_employee(lPrenom) := NULL ; --
1885: END IF;
1886: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1887: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1888:
1889: -- Initiale
1890: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1891: 'CAEOY_EMPLOYEE_INITIAL');
1897: ELSE
1898: tab_employee(lInitiale) := NULL;
1899: tab_xml_employee(lInitiale) := NULL; --
1900: END IF;
1901: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1902: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1903:
1904: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1905: l_arch_action_id,
1898: tab_employee(lInitiale) := NULL;
1899: tab_xml_employee(lInitiale) := NULL; --
1900: END IF;
1901: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1902: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1903:
1904: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
1905: l_arch_action_id,
1906: 'CAEOY_PERSON_ID'));
1919: -- If Address line 1 is NULL or ' ' then the employee is missing
1920: -- address information - as line 1 is mandatory in the Address form.
1921: -- Need to check data by SS transaction /API.
1922:
1923: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1924: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1925: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1926: hr_utility.trace('l_postal_code = ' || l_postal_code);
1927:
1920: -- address information - as line 1 is mandatory in the Address form.
1921: -- Need to check data by SS transaction /API.
1922:
1923: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1924: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1925: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1926: hr_utility.trace('l_postal_code = ' || l_postal_code);
1927:
1928: -- Address Line 1
1921: -- Need to check data by SS transaction /API.
1922:
1923: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1924: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1925: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1926: hr_utility.trace('l_postal_code = ' || l_postal_code);
1927:
1928: -- Address Line 1
1929: IF l_address_line1 IS NULL OR
1922:
1923: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1924: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1925: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1926: hr_utility.trace('l_postal_code = ' || l_postal_code);
1927:
1928: -- Address Line 1
1929: IF l_address_line1 IS NULL OR
1930: l_address_line1 = ' ' THEN
1946: l_addr_begin_tag := '
1947:
1948: tab_employee(lLigne1) := '
1949: pay_ca_rl1_mag.convert_special_char(substr(l_address_line1,1,30)) || '
1950: hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1951:
1952: -- Address Line 2
1953: IF ((l_address_line2 IS NULL OR
1954: l_address_line2 <> ' ') OR
1959: pay_ca_rl1_mag.convert_special_char(substr(l_combined_addr,1,30)) || '' || EOL;
1960: ELSE
1961: tab_employee(lLigne2) := NULL;
1962: END IF;
1963: hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1964:
1965: -- Ville (City)
1966: IF l_city IS NULL OR
1967: l_city <> ' ' THEN
1969: substr(l_city,1,30) || '' || EOL;
1970: ELSE
1971: tab_employee(lVille) := NULL;
1972: END IF;
1973: hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1974:
1975: -- Province
1976: IF l_emp_province IS NULL OR
1977: l_emp_province <> ' ' THEN
1986: END IF;
1987: ELSE
1988: tab_employee(lProvince) := NULL;
1989: END IF;
1990: hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1991:
1992: -- Postal Code
1993: IF l_postal_code IS NULL OR
1994: l_postal_code <> ' ' THEN
1996: substr(replace(l_postal_code,' '),1,6) || '' || EOL;
1997: ELSE
1998: tab_employee(lCodePostal) := NULL;
1999: END IF;
2000: hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
2001: l_addr_end_tag := '';
2002:
2003: END IF;
2004:
2030: ELSE
2031: tab_employee(lA_RevenuEmploi) := NULL;
2032: tab_xml_employee(lA_RevenuEmploi) := NULL; --
2033: END IF;
2034: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
2035: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
2036:
2037: -- Summ (Box B)
2038: l_box := pay_ca_archive_utils.get_archive_value(
2031: tab_employee(lA_RevenuEmploi) := NULL;
2032: tab_xml_employee(lA_RevenuEmploi) := NULL; --
2033: END IF;
2034: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
2035: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
2036:
2037: -- Summ (Box B)
2038: l_box := pay_ca_archive_utils.get_archive_value(
2039: l_arch_action_id,
2064: tab_employee(lB_CotisationRRQ) := NULL;
2065: tab_xml_employee(lB_CotisationRRQ) := NULL; --
2066:
2067: END IF;
2068: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
2069: tab_employee(lB_CotisationRRQ));
2070: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
2071: tab_xml_employee(lB_CotisationRRQ)); --
2072:
2066:
2067: END IF;
2068: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
2069: tab_employee(lB_CotisationRRQ));
2070: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
2071: tab_xml_employee(lB_CotisationRRQ)); --
2072:
2073: -- Summ (Box C)
2074: l_box := pay_ca_archive_utils.get_archive_value(
2097: tab_employee(lC_CotisationAssEmploi) := NULL;
2098: tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
2099: END IF;
2100:
2101: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2102: tab_employee(lC_CotisationAssEmploi));
2103:
2104: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2105: tab_xml_employee(lC_CotisationAssEmploi)); --
2100:
2101: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2102: tab_employee(lC_CotisationAssEmploi));
2103:
2104: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2105: tab_xml_employee(lC_CotisationAssEmploi)); --
2106: -- Summ (Box D)
2107: l_box := pay_ca_archive_utils.get_archive_value(
2108: l_arch_action_id,
2129: ELSE
2130: tab_employee(lD_CotisationRPA) := NULL;
2131: tab_xml_employee(lD_CotisationRPA) := NULL; --
2132: END IF;
2133: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2134: tab_employee(lD_CotisationRPA));
2135: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2136: tab_xml_employee(lD_CotisationRPA));
2137:
2131: tab_xml_employee(lD_CotisationRPA) := NULL; --
2132: END IF;
2133: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2134: tab_employee(lD_CotisationRPA));
2135: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2136: tab_xml_employee(lD_CotisationRPA));
2137:
2138:
2139: -- (Box E)
2164: tab_employee(lE_ImpotQue) := NULL ;
2165: tab_xml_employee(lE_ImpotQue) := NULL ; --
2166: END IF;
2167:
2168: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2169: tab_employee(lE_ImpotQue));
2170: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2171: tab_xml_employee(lE_ImpotQue)); --
2172:
2166: END IF;
2167:
2168: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2169: tab_employee(lE_ImpotQue));
2170: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2171: tab_xml_employee(lE_ImpotQue)); --
2172:
2173: -- (Box F)
2174: l_box := pay_ca_archive_utils.get_archive_value(
2196: ELSE
2197: tab_employee(lF_CotisationSyndicale) := NULL;
2198: tab_xml_employee(lF_CotisationSyndicale) := NULL; --
2199: END IF;
2200: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2201: tab_employee(lF_CotisationSyndicale));
2202:
2203: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2204: tab_xml_employee(lF_CotisationSyndicale));
2199: END IF;
2200: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2201: tab_employee(lF_CotisationSyndicale));
2202:
2203: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2204: tab_xml_employee(lF_CotisationSyndicale));
2205: -- (Box Q)
2206: l_box := pay_ca_archive_utils.get_archive_value(
2207: l_arch_action_id,
2229: ELSE
2230: tab_employee(lQ_SalaireDiffere) := NULL;
2231: tab_xml_employee(lQ_SalaireDiffere) := NULL; --
2232: END IF;
2233: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2234: tab_employee(lQ_SalaireDiffere));
2235:
2236: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2237: tab_xml_employee(lQ_SalaireDiffere));
2232: END IF;
2233: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2234: tab_employee(lQ_SalaireDiffere));
2235:
2236: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2237: tab_xml_employee(lQ_SalaireDiffere));
2238: -- (Box U)
2239: l_box := pay_ca_archive_utils.get_archive_value(
2240: l_arch_action_id,
2264: ELSE
2265: tab_employee(lU_RetraiteProgressive) := NULL;
2266: tab_xml_employee(lU_RetraiteProgressive) := NULL; --
2267: END IF;
2268: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2269: tab_employee(lU_RetraiteProgressive));
2270:
2271: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2272: tab_xml_employee(lU_RetraiteProgressive));
2267: END IF;
2268: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2269: tab_employee(lU_RetraiteProgressive));
2270:
2271: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2272: tab_xml_employee(lU_RetraiteProgressive));
2273: -- (Box G)
2274: l_box := pay_ca_archive_utils.get_archive_value(
2275: l_arch_action_id,
2278: --commented for bug 13360872
2279: --'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2280: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2281:
2282: hr_utility.trace('l_box = ' || l_box);
2283: hr_utility.trace('l_boxA = ' || l_BoxA);
2284: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2285: hr_utility.trace('l_boxU = ' || l_boxU);
2286:
2279: --'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2280: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2281:
2282: hr_utility.trace('l_box = ' || l_box);
2283: hr_utility.trace('l_boxA = ' || l_BoxA);
2284: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2285: hr_utility.trace('l_boxU = ' || l_boxU);
2286:
2287:
2280: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2281:
2282: hr_utility.trace('l_box = ' || l_box);
2283: hr_utility.trace('l_boxA = ' || l_BoxA);
2284: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2285: hr_utility.trace('l_boxU = ' || l_boxU);
2286:
2287:
2288: IF l_box IS NOT NULL THEN
2281:
2282: hr_utility.trace('l_box = ' || l_box);
2283: hr_utility.trace('l_boxA = ' || l_BoxA);
2284: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2285: hr_utility.trace('l_boxU = ' || l_boxU);
2286:
2287:
2288: IF l_box IS NOT NULL THEN
2289:
2345:
2346: tab_xml_employee(lG_SalaireAdmisRRQ) := NULL;
2347:
2348:
2349: hr_utility.trace('tab_xml_employee(lG_SalaireAdmisRRQ) = ' ||
2350: tab_xml_employee(lG_SalaireAdmisRRQ));
2351:
2352:
2353: -- (Box V)
2377: tab_employee(lV_NourritureLogement) := NULL;
2378: tab_xml_employee(lV_NourritureLogement) := NULL; --
2379: END IF;
2380:
2381: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2382: tab_employee(lV_NourritureLogement));
2383:
2384: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2385: tab_xml_employee(lV_NourritureLogement));
2380:
2381: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2382: tab_employee(lV_NourritureLogement));
2383:
2384: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2385: tab_xml_employee(lV_NourritureLogement));
2386: -- (Box W)
2387: l_box := pay_ca_archive_utils.get_archive_value(
2388: l_arch_action_id,
2409: ELSE
2410: tab_employee(lW_Vehicule) := NULL;
2411: tab_xml_employee(lW_Vehicule) := NULL; --
2412: END IF;
2413: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2414: tab_employee(lW_Vehicule));
2415:
2416: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2417: tab_xml_employee(lW_Vehicule));
2412: END IF;
2413: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2414: tab_employee(lW_Vehicule));
2415:
2416: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2417: tab_xml_employee(lW_Vehicule));
2418:
2419: --(BOX H)
2420: l_box := pay_ca_archive_utils.get_archive_value(
2441: ELSE
2442: tab_employee(lH_CotisationRQAP) := NULL;
2443: tab_xml_employee(lH_CotisationRQAP) := NULL; --
2444: END IF;
2445: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2446: tab_employee(lH_CotisationRQAP));
2447:
2448: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2449: tab_xml_employee(lH_CotisationRQAP));
2444: END IF;
2445: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2446: tab_employee(lH_CotisationRQAP));
2447:
2448: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2449: tab_xml_employee(lH_CotisationRQAP));
2450:
2451: --(BOX I)
2452: l_box := pay_ca_archive_utils.get_archive_value(
2476: tab_employee(lI_SalaireAdmisRQAP) := '
2477: '0.00' || '
2478: tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2479: END IF;
2480: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2481: tab_employee(lI_SalaireAdmisRQAP));
2482:
2483: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2484: tab_xml_employee(lI_SalaireAdmisRQAP));
2479: END IF;
2480: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2481: tab_employee(lI_SalaireAdmisRQAP));
2482:
2483: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2484: tab_xml_employee(lI_SalaireAdmisRQAP));
2485:
2486: -- (Box J)
2487: l_box := pay_ca_archive_utils.get_archive_value(
2509: ELSE
2510: tab_employee(lJ_RegimeAssMaladie) := NULL;
2511: tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2512: END IF;
2513: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2514: tab_employee(lJ_RegimeAssMaladie));
2515:
2516: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2517: tab_xml_employee(lJ_RegimeAssMaladie));
2512: END IF;
2513: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2514: tab_employee(lJ_RegimeAssMaladie));
2515:
2516: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2517: tab_xml_employee(lJ_RegimeAssMaladie));
2518:
2519: -- (Box K)
2520: l_box := pay_ca_archive_utils.get_archive_value(
2542: ELSE
2543: tab_employee(lK_Voyage) := NULL;
2544: tab_xml_employee(lK_Voyage) := NULL; --
2545: END IF;
2546: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2547: tab_employee(lK_Voyage));
2548:
2549: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2550: tab_xml_employee(lK_Voyage));
2545: END IF;
2546: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2547: tab_employee(lK_Voyage));
2548:
2549: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2550: tab_xml_employee(lK_Voyage));
2551: -- (Box L)
2552: l_box := pay_ca_archive_utils.get_archive_value(
2553: l_arch_action_id,
2574: ELSE
2575: tab_employee(lL_AutreAvantage) := NULL;
2576: tab_xml_employee(lL_AutreAvantage) := NULL; --
2577: END IF;
2578: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2579: tab_employee(lL_AutreAvantage));
2580:
2581: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2582: tab_xml_employee(lL_AutreAvantage));
2577: END IF;
2578: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2579: tab_employee(lL_AutreAvantage));
2580:
2581: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2582: tab_xml_employee(lL_AutreAvantage));
2583: -- (Box M)
2584: l_box := pay_ca_archive_utils.get_archive_value(
2585: l_arch_action_id,
2606: ELSE
2607: tab_employee(lM_Commission) := NULL;
2608: tab_xml_employee(lM_Commission) := NULL; --
2609: END IF;
2610: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2611: tab_employee(lM_Commission));
2612:
2613: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2614: tab_xml_employee(lM_Commission));
2609: END IF;
2610: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2611: tab_employee(lM_Commission));
2612:
2613: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2614: tab_xml_employee(lM_Commission));
2615: -- (Box N)
2616: l_box := pay_ca_archive_utils.get_archive_value(
2617: l_arch_action_id,
2639: tab_employee(lN_DonBienfaisance) := NULL;
2640: tab_xml_employee(lN_DonBienfaisance) := NULL; --
2641: END IF;
2642:
2643: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2644: tab_employee(lN_DonBienfaisance));
2645:
2646: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2647: tab_xml_employee(lN_DonBienfaisance));
2642:
2643: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2644: tab_employee(lN_DonBienfaisance));
2645:
2646: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2647: tab_xml_employee(lN_DonBienfaisance));
2648: -- Summ (Box O)
2649: l_box := pay_ca_archive_utils.get_archive_value(
2650: l_arch_action_id,
2672: ELSE
2673: tab_employee(lO_AutreRevenu) := NULL;
2674: tab_xml_employee(lO_AutreRevenu) := NULL; --
2675: END IF;
2676: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2677: tab_employee(lO_AutreRevenu));
2678:
2679: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2680: tab_xml_employee(lO_AutreRevenu));
2675: END IF;
2676: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2677: tab_employee(lO_AutreRevenu));
2678:
2679: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2680: tab_xml_employee(lO_AutreRevenu));
2681:
2682: -- SourceCasem
2683:
2693: ELSE
2694: tab_employee(l_SourceCase) := NULL;
2695: tab_xml_employee(l_SourceCase) := NULL; --
2696: END IF;
2697: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2698: tab_employee(l_SourceCase));
2699:
2700: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2701: tab_xml_employee(l_SourceCase));
2696: END IF;
2697: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2698: tab_employee(l_SourceCase));
2699:
2700: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2701: tab_xml_employee(l_SourceCase));
2702: -- (Box P)
2703: if tab_employee(lO_AutreRevenu) is not null
2704: and tab_employee(l_SourceCase)is not null then
2734: ELSE
2735: tab_employee(lP_RegimeAssInterEntr) := NULL;
2736: tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2737: END IF;
2738: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2739: tab_employee(lP_RegimeAssInterEntr));
2740:
2741: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2742: tab_xml_employee(lP_RegimeAssInterEntr));
2737: END IF;
2738: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2739: tab_employee(lP_RegimeAssInterEntr));
2740:
2741: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2742: tab_xml_employee(lP_RegimeAssInterEntr));
2743: -- (Box R)
2744: l_box := pay_ca_archive_utils.get_archive_value(
2745: l_arch_action_id,
2766: ELSE
2767: tab_employee(lR_RevenuIndien) := NULL;
2768: tab_xml_employee(lR_RevenuIndien) := NULL; --
2769: END IF;
2770: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2771: tab_employee(lR_RevenuIndien));
2772:
2773: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2774: tab_xml_employee(lR_RevenuIndien));
2769: END IF;
2770: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2771: tab_employee(lR_RevenuIndien));
2772:
2773: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2774: tab_xml_employee(lR_RevenuIndien));
2775: -- (Box S)
2776: l_box := pay_ca_archive_utils.get_archive_value(
2777: l_arch_action_id,
2798: ELSE
2799: tab_employee(lS_PourboireRecu) := NULL;
2800: tab_xml_employee(lS_PourboireRecu) := NULL; --
2801: END IF;
2802: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2803: tab_employee(lS_PourboireRecu));
2804:
2805: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2806: tab_xml_employee(lS_PourboireRecu));
2801: END IF;
2802: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2803: tab_employee(lS_PourboireRecu));
2804:
2805: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2806: tab_xml_employee(lS_PourboireRecu));
2807: -- (Box T)
2808: l_box := pay_ca_archive_utils.get_archive_value(
2809: l_arch_action_id,
2830: ELSE
2831: tab_employee(lT_PourboireAttribue) := NULL;
2832: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2833: END IF;
2834: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2835: tab_employee(lT_PourboireAttribue));
2836: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2837: tab_xml_employee(lT_PourboireAttribue));
2838: -- (Box ContisationRPC)
2832: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2833: END IF;
2834: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2835: tab_employee(lT_PourboireAttribue));
2836: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2837: tab_xml_employee(lT_PourboireAttribue));
2838: -- (Box ContisationRPC)
2839: l_box := pay_ca_archive_utils.get_archive_value(
2840: l_arch_action_id,
2860: l_all_box_0 := FALSE;
2861: ELSE
2862: tab_employee(l_ContisationRPC) := NULL;
2863: END IF;
2864: hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2865: tab_employee(l_ContisationRPC));
2866:
2867: /* Changes for bug 13567075 starts here -- sbachu*/
2868:
2890: l_all_box_0 := FALSE;
2891: ELSE
2892: tab_employee(l_FraisGarde) := NULL;
2893: END IF;
2894: hr_utility.trace('tab_employee(l_FraisGarde) = ' ||
2895: tab_employee(l_FraisGarde));
2896:
2897: l_box := fnd_number.number_to_canonical(fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2898: l_arch_action_id,
2923: l_all_box_0 := FALSE;
2924: ELSE
2925: tab_employee(l_DeductionForce) := NULL;
2926: END IF;
2927: hr_utility.trace('tab_employee(l_DeductionForce) = ' ||
2928: tab_employee(l_DeductionForce));
2929:
2930: /*Changes for bug 13567075 ends here --sbachu*/
2931:
2962: tab_employee(l_origi_slipno) := '
2963: END IF;
2964: /*Bug 13564765 ends here*/
2965: -- Negative Balance Exists
2966: hr_utility.trace('finding if neg bal exists');
2967: l_box := pay_ca_archive_utils.get_archive_value(
2968: l_arch_action_id,
2969: l_province,
2970: 'JURISDICTION_CODE',
2973: IF l_box = 'Y' THEN
2974: l_status := 'Failed';
2975: l_msg_code := 'NEG';
2976: l_neg_bal_exists := TRUE;
2977: hr_utility.trace('neg bal exists');
2978: END IF;
2979:
2980: IF to_number(l_taxation_year) < 2011 AND l_all_box_0 THEN /*Bug 13633422*/
2981: l_status := 'Failed';
3014: OPEN cur_get_meaning(l_msg_code);
3015: FETCH cur_get_meaning
3016: INTO l_meaning;
3017: CLOSE cur_get_meaning;
3018: hr_utility.trace('l_meaning ='|| l_meaning);
3019: tab_employee(lErrorDetails) := '
3020: l_meaning || '
3021: l_other_details := l_other_details||tab_employee(lErrorDetails);
3022: END IF;
3019: tab_employee(lErrorDetails) := '
3020: l_meaning || '
3021: l_other_details := l_other_details||tab_employee(lErrorDetails);
3022: END IF;
3023: hr_utility.trace('l_other_details ='||l_other_details);
3024: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3025: tab_employee(lH_CotisationRQAP));
3026: hr_utility.trace('l_rep_type ='|| l_rep_type);
3027:
3020: l_meaning || '' || EOL;
3021: l_other_details := l_other_details||tab_employee(lErrorDetails);
3022: END IF;
3023: hr_utility.trace('l_other_details ='||l_other_details);
3024: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3025: tab_employee(lH_CotisationRQAP));
3026: hr_utility.trace('l_rep_type ='|| l_rep_type);
3027:
3028: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3022: END IF;
3023: hr_utility.trace('l_other_details ='||l_other_details);
3024: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3025: tab_employee(lH_CotisationRQAP));
3026: hr_utility.trace('l_rep_type ='|| l_rep_type);
3027:
3028: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3029: tab_employee(lH_CotisationRQAP));
3030: /*bug 14701466 starts here sbachu*/
3024: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3025: tab_employee(lH_CotisationRQAP));
3026: hr_utility.trace('l_rep_type ='|| l_rep_type);
3027:
3028: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3029: tab_employee(lH_CotisationRQAP));
3030: /*bug 14701466 starts here sbachu*/
3031: lv_ident_addr_string := '
3032: || '
3157: end if;
3158: end if;
3159: END IF; /* if l_status = 'Failed' */
3160: /*bug 14701466 ends here sbachu*/
3161: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3162: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3163: end if; --if cur_parameters%found
3164: CLOSE cur_parameters;
3165: hr_utility.trace('end of xml_employee_record');
3161: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3162: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3163: end if; --if cur_parameters%found
3164: CLOSE cur_parameters;
3165: hr_utility.trace('end of xml_employee_record');
3166:
3167: END;
3168: END xml_employee_record;
3169:
3178:
3179: BEGIN
3180:
3181: l_final_xml_string := '
3182: hr_utility.trace('Inside the Employer Start record proc');
3183: hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
3184: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3185:
3186:
3179: BEGIN
3180:
3181: l_final_xml_string := '
3182: hr_utility.trace('Inside the Employer Start record proc');
3183: hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
3184: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3185:
3186:
3187: END;
3224:
3225:
3226: BEGIN
3227:
3228: hr_utility.trace('XML Employer');
3229: hr_utility.trace('Inside the Employer Main Record proc');
3230: SELECT
3231: fnd_global.local_chr(13) || fnd_global.local_chr(10)
3232: INTO EOL
3225:
3226: BEGIN
3227:
3228: hr_utility.trace('XML Employer');
3229: hr_utility.trace('Inside the Employer Main Record proc');
3230: SELECT
3231: fnd_global.local_chr(13) || fnd_global.local_chr(10)
3232: INTO EOL
3233: FROM dual;
3249: l_taxation_year
3250: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
3251: l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
3252:
3253: hr_utility.trace ('l_cvontext1 ='||l_context1);
3254:
3255: tab_employer(lAnnee) := '
3256: tab_employer(lNbReleves) := '
3257:
3270: 'CAEOY_RL1_EMPLOYER_NAME');
3271:
3272: tab_employer(lNom1) := '
3273: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,1,30)) || '
3274: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3271:
3272: tab_employer(lNom1) := '
3273: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,1,30)) || '
3274: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3272: tab_employer(lNom1) := '
3273: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,1,30)) || '
3274: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3273: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,1,30)) || '' || EOL;
3274: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3281:
3274: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3281:
3282: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3275: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3281:
3282: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3283: tab_employer(lNom2) := '
3276: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3277: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3278: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3279: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3280: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3281:
3282: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3283: tab_employer(lNom2) := '
3284: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,31,30)) || '
3284: pay_ca_rl1_mag.convert_special_char(substr(l_employer_name,31,30)) || '' || EOL;
3285: ELSE
3286: tab_employer(lNom2) := NULL;
3287: END IF;
3288: hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
3289:
3290: -- Address Line 1
3291:
3292: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3307: l_address_begin_tag := '
3308:
3309: tab_employer(lLigne1) := '
3310: pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '
3311: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3312:
3313:
3314: -- Address Line 2
3315:
3321: pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
3322: ELSE
3323: tab_employer(lLigne2) := NULL;
3324: END IF;
3325: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3326:
3327: -- Ville (City)
3328:
3329: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3333: pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
3334: ELSE
3335: tab_employer(lVille) := NULL;
3336: END IF;
3337: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3338:
3339: -- Province
3340:
3341: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3347: l_address_line),1,20) || '' || EOL;
3348: ELSE
3349: tab_employer(lProvince) := NULL;
3350: END IF;
3351: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3352:
3353: -- Postal Code
3354:
3355: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3360: substr(replace(l_address_line,' '),1,6) || '' || EOL;
3361: ELSE
3362: tab_employer(lCodePostal) := NULL;
3363: END IF;
3364: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3365:
3366: l_address_end_tag := '';
3367:
3368: END IF;
3384: l_address_end_tag || EOL || '' || EOL ||
3385: '' || EOL ||
3386: '' || EOL;
3387:
3388: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3389: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3390: END;
3391: END xml_employer_record;
3392:
3398: DECLARE
3399: l_final_xml_string VARCHAR2(32000);
3400:
3401: BEGIN
3402: hr_utility.trace('report ends here..closing RL1PAPER tag');
3403: l_final_xml_string := '';
3404: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3405:
3406: END;
3412: DECLARE
3413: l_final_xml_string VARCHAR2(32000);
3414:
3415: BEGIN
3416: --hr_utility.trace_on(null,'SATI');
3417: hr_utility.trace('inside xml_rl1_report_start');
3418: l_final_xml_string := '
3419: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3420:
3413: l_final_xml_string VARCHAR2(32000);
3414:
3415: BEGIN
3416: --hr_utility.trace_on(null,'SATI');
3417: hr_utility.trace('inside xml_rl1_report_start');
3418: l_final_xml_string := '
3419: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3420:
3421: END;
3512: l_boxo3 :=NULL;
3513:
3514: l_see_attached := hr_general.decode_lookup('PAY_CA_LABELS'
3515: ,'SEE_ATTACHED');
3516: hr_utility.trace('l_see_attached = '||l_see_attached);
3517: SELECT
3518: fnd_global.local_chr(13) || fnd_global.local_chr(10)
3519: INTO EOL
3520: from dual;
3522: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
3523: open c_get_language(p_assgn_id);
3524: fetch c_get_language into l_person_lang;
3525: close c_get_language;
3526: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
3527: hr_utility.trace(' l_person_lang ='|| l_person_lang);
3528: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
3529: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
3530: loop
3523: open c_get_language(p_assgn_id);
3524: fetch c_get_language into l_person_lang;
3525: close c_get_language;
3526: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
3527: hr_utility.trace(' l_person_lang ='|| l_person_lang);
3528: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
3529: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
3530: loop
3531: i:=i+1;
3524: fetch c_get_language into l_person_lang;
3525: close c_get_language;
3526: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
3527: hr_utility.trace(' l_person_lang ='|| l_person_lang);
3528: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
3529: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
3530: loop
3531: i:=i+1;
3532: t_footnote(i) := rec;
3538: t_boxo(i) :=rec;
3539: end loop;
3540: l_count_boxo:=t_boxo.count;
3541: l_count := t_footnote.count;
3542: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
3543: hr_utility.trace(' l_count ='|| l_count);
3544: if l_count > 1 then --more than 1 footnote
3545: p_footnote_boxo1 :='
3546: p_footnote_boxo2 :='
3539: end loop;
3540: l_count_boxo:=t_boxo.count;
3541: l_count := t_footnote.count;
3542: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
3543: hr_utility.trace(' l_count ='|| l_count);
3544: if l_count > 1 then --more than 1 footnote
3545: p_footnote_boxo1 :='
3546: p_footnote_boxo2 :='
3547: p_footnote_boxo3 :='
3560: l_footnote2 :='
3561: ||'
3562: l_footnote3 :='
3563: ||'
3564: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
3565: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
3566: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
3567: if (l_count_boxo=1) then
3568: l_boxo1 :='
3561: ||'
3562: l_footnote3 :='
3563: ||'
3564: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
3565: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
3566: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
3567: if (l_count_boxo=1) then
3568: l_boxo1 :='
3569: ||'
3562: l_footnote3 :='
3563: ||'
3564: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
3565: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
3566: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
3567: if (l_count_boxo=1) then
3568: l_boxo1 :='
3569: ||'
3570: l_boxo2 :='
3577: p_footnote_boxo1 :=l_footnote1||l_boxo1;
3578: p_footnote_boxo2 :=l_footnote2||l_boxo2;
3579: p_footnote_boxo3 :=l_footnote3||l_boxo3;
3580: elsif l_count = 0 and l_count_boxo < 4 then
3581: hr_utility.trace('boxo count less than 4');
3582: for k in 1.. l_count_boxo
3583: loop
3584: l_boxo1 :=l_boxo1||'
3585: ||'
3588: l_boxo3 :=l_boxo3||'
3589: ||'
3590:
3591: end loop;
3592: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
3593: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
3594: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
3595:
3596: p_footnote_boxo1 :=l_boxo1;
3589: ||'
3590:
3591: end loop;
3592: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
3593: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
3594: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
3595:
3596: p_footnote_boxo1 :=l_boxo1;
3597: p_footnote_boxo2 :=l_boxo2;
3590:
3591: end loop;
3592: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
3593: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
3594: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
3595:
3596: p_footnote_boxo1 :=l_boxo1;
3597: p_footnote_boxo2 :=l_boxo2;
3598: p_footnote_boxo3 :=l_boxo3;
3602: p_footnote_boxo2 :='
3603: p_footnote_boxo3 :='
3604: end if;
3605:
3606: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
3607: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
3608: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
3609:
3610: end xml_footnote_boxo;
3603: p_footnote_boxo3 :='
3604: end if;
3605:
3606: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
3607: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
3608: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
3609:
3610: end xml_footnote_boxo;
3611:
3604: end if;
3605:
3606: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
3607: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
3608: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
3609:
3610: end xml_footnote_boxo;
3611:
3612: PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
3640: l_quebec_bn varchar2(20);
3641:
3642: BEGIN
3643:
3644: hr_utility.trace('XML Employer');
3645:
3646: SELECT
3647: fnd_global.local_chr(13) || fnd_global.local_chr(10)
3648: INTO EOL
3661: open c_get_arch_pay_actid;
3662: fetch c_get_arch_pay_actid
3663: into l_context1;
3664: close c_get_arch_pay_actid;
3665: hr_utility.trace ('l_cvontext1 ='||l_context1);
3666:
3667: l_quebec_bn := pay_ca_archive_utils.get_archive_value
3668: (l_context1,'CAEOY_RL1_QUEBEC_BN');
3669: tab_employer(lNoId) := substr(l_quebec_bn,1,10); -- employer id
3671: l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
3672: 'CAEOY_RL1_EMPLOYER_NAME');
3673:
3674: tab_employer(lNom1) := pay_ca_rl1_mag.convert_special_char(l_employer_name);
3675: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3676: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3677:
3678: -- Address Line 1
3679: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3672: 'CAEOY_RL1_EMPLOYER_NAME');
3673:
3674: tab_employer(lNom1) := pay_ca_rl1_mag.convert_special_char(l_employer_name);
3675: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3676: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3677:
3678: -- Address Line 1
3679: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3680: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
3678: -- Address Line 1
3679: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3680: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
3681: tab_employer(lLigne1) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3682: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3683:
3684: -- Address Line 2
3685: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3686: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
3684: -- Address Line 2
3685: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3686: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
3687: tab_employer(lLigne2) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3688: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3689:
3690: -- Address Line 3
3691: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3692: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
3690: -- Address Line 3
3691: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3692: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
3693: tab_employer(lLigne3) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,40));
3694: hr_utility.trace('tab_employer(lLigne3) = ' || tab_employer(lLigne3));
3695:
3696: if(tab_employer(lLigne2) is not null or tab_employer(lLigne3) is not null) then
3697: tab_employer(lLigne2) := tab_employer(lLigne2) ||' '|| tab_employer(lLigne3) ; /*******/
3698: end if;
3700: -- Ville (City)
3701: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3702: 'CAEOY_RL1_EMPLOYER_CITY');
3703: tab_employer(lVille) := pay_ca_rl1_mag.convert_special_char(substr(l_address_line,1,30));
3704: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3705:
3706: -- Province
3707:
3708: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3708: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3709: 'CAEOY_RL1_EMPLOYER_PROVINCE');
3710:
3711: tab_employer(lProvince) :=l_address_line;
3712: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3713:
3714: --Country
3715: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3716: 'CAEOY_RL1_EMPLOYER_COUNTRY');
3714: --Country
3715: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3716: 'CAEOY_RL1_EMPLOYER_COUNTRY');
3717: tab_employer(lCountry) :=l_address_line;
3718: hr_utility.trace('tab_employer(lCountry) = ' || tab_employer(lCountry));
3719:
3720: -- Postal Code
3721: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3722: 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
3728: tab_employer(lCountry) ||' '||
3729: substr(tab_employer(lCodePostal),1,3)||' '||
3730: substr(tab_employer(lCodePostal),4,3);
3731:
3732: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3733: for l_index in 1..3 loop
3734: l_final_xml_string := '
3735:
3736: /*********************************************/
3770: end if;
3771: ******************************************************/
3772:
3773:
3774: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3775: if l_index=1 then
3776: p_emplyr_final1 := l_final_xml_string;
3777: end if;
3778: if l_index=2 then