[Home] [Help]
39: text_size,
40: raw_data);
41:
42: IF dbms_lob.ISOPEN(temp_blob)=1 THEN
43: hr_utility.trace('Closing temp_lob' );
44: dbms_lob.close(temp_blob);
45: hr_utility.trace('Closed temp_lob' );
46: END IF;
47:
41:
42: IF dbms_lob.ISOPEN(temp_blob)=1 THEN
43: hr_utility.trace('Closing temp_lob' );
44: dbms_lob.close(temp_blob);
45: hr_utility.trace('Closed temp_lob' );
46: END IF;
47:
48: return temp_blob;
49: END;
126: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
127: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
128: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');/* Bug 14701466,14616599 sbachu*/
129:
130: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
131: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
132: g_further_info_list.delete;
133: for rec in cur_boxo
134: loop
127: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
128: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');/* Bug 14701466,14616599 sbachu*/
129:
130: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
131: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
132: g_further_info_list.delete;
133: for rec in cur_boxo
134: loop
135: i:=i+1;
132: g_further_info_list.delete;
133: for rec in cur_boxo
134: loop
135: i:=i+1;
136: hr_utility.trace('i '||i);
137: hr_utility.trace('rec.code '||rec.code);
138: hr_utility.trace('rec.value '||rec.value);
139: g_further_info_list(i).code := rec.code;
140: g_further_info_list(i).amount := rec.value;
133: for rec in cur_boxo
134: loop
135: i:=i+1;
136: hr_utility.trace('i '||i);
137: hr_utility.trace('rec.code '||rec.code);
138: hr_utility.trace('rec.value '||rec.value);
139: g_further_info_list(i).code := rec.code;
140: g_further_info_list(i).amount := rec.value;
141: end loop;
134: loop
135: i:=i+1;
136: hr_utility.trace('i '||i);
137: hr_utility.trace('rec.code '||rec.code);
138: hr_utility.trace('rec.value '||rec.value);
139: g_further_info_list(i).code := rec.code;
140: g_further_info_list(i).amount := rec.value;
141: end loop;
142:
142:
143: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
144: loop
145: i:=i+1;
146: hr_utility.trace('i '||i);
147: hr_utility.trace('rec.code '||rec.code);
148: hr_utility.trace('rec.value '||rec.value);
149: g_further_info_list(i).code := rec.code;
150: g_further_info_list(i).amount := rec.value;
143: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
144: loop
145: i:=i+1;
146: hr_utility.trace('i '||i);
147: hr_utility.trace('rec.code '||rec.code);
148: hr_utility.trace('rec.value '||rec.value);
149: g_further_info_list(i).code := rec.code;
150: g_further_info_list(i).amount := rec.value;
151: end loop;
144: loop
145: i:=i+1;
146: hr_utility.trace('i '||i);
147: hr_utility.trace('rec.code '||rec.code);
148: hr_utility.trace('rec.value '||rec.value);
149: g_further_info_list(i).code := rec.code;
150: g_further_info_list(i).amount := rec.value;
151: end loop;
152:
157: p_amount in out nocopy varchar2)
158: is
159: begin
160:
161: hr_utility.trace('p_index '||p_index);
162: if g_further_info_list.exists(p_index) then
163: p_code := g_further_info_list(p_index).code;
164: p_amount := g_further_info_list(p_index).amount;
165: else
176: p_report_type IN OUT NOCOPY VARCHAR2,
177: p_business_group_id IN OUT NOCOPY NUMBER
178: ) IS
179: BEGIN
180: --hr_utility.trace_on('Y','RL1MAG');
181: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 10);
182:
183: SELECT ppa.start_date,
184: ppa.effective_date,
177: p_business_group_id IN OUT NOCOPY NUMBER
178: ) IS
179: BEGIN
180: --hr_utility.trace_on('Y','RL1MAG');
181: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 10);
182:
183: SELECT ppa.start_date,
184: ppa.effective_date,
185: ppa.business_group_id,
190: p_report_type
191: FROM pay_payroll_actions ppa
192: WHERE payroll_action_id = p_pactid;
193:
194: hr_utility.set_location('pay_ca_rl1_mag.get_report_parameters', 20);
195:
196: END get_report_parameters;
197:
198: FUNCTION convert_special_char( p_data varchar2)
319:
320: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
321: l_legislative_parameters);
322:
323: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
324: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
325: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
326: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
327:
320: l_transmitter_org_id := pay_ca_rl1_mag.get_parameter('TRANSMITTER_PRE',
321: l_legislative_parameters);
322:
323: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
324: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
325: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
326: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
327:
328: OPEN cur_arch_pactid(l_transmitter_org_id);
321: l_legislative_parameters);
322:
323: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
324: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
325: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
326: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
327:
328: OPEN cur_arch_pactid(l_transmitter_org_id);
329: FETCH cur_arch_pactid
322:
323: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
324: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
325: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
326: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
327:
328: OPEN cur_arch_pactid(l_transmitter_org_id);
329: FETCH cur_arch_pactid
330: INTO l_arch_pactid;
333: l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
334: l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
335: BEGIN
336:
337: hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
338: SELECT substr(l_transmitter_number,1,2)
339: INTO dummy1
340: FROM dual;
341:
349: FROM dual;
350:
351: EXCEPTION
352: WHEN INVALID_NUMBER THEN
353: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
354: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
355: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
356: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
357: hr_utility.raise_error;
350:
351: EXCEPTION
352: WHEN INVALID_NUMBER THEN
353: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
354: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
355: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
356: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
357: hr_utility.raise_error;
358: END;
353: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
354: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
355: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
356: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
357: hr_utility.raise_error;
358: END;
359:
360: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
361:
358: END;
359:
360: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
361:
362: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
363:
364: IF l_type_of_package IS NULL THEN
365: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
366: hr_utility.raise_error;
362: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
363:
364: IF l_type_of_package IS NULL THEN
365: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
366: hr_utility.raise_error;
367: END IF;
368:
369: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
370: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
366: hr_utility.raise_error;
367: END IF;
368:
369: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
370: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
371:
372: IF l_source_of_slips IS NULL THEN
373: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
374: hr_utility.raise_error;
370: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
371:
372: IF l_source_of_slips IS NULL THEN
373: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
374: hr_utility.raise_error;
375: END IF;
376:
377: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
378: hr_utility.trace('l_address_line1 = ' || l_address_line1);
374: hr_utility.raise_error;
375: END IF;
376:
377: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
378: hr_utility.trace('l_address_line1 = ' || l_address_line1);
379:
380: IF l_address_line1 IS NULL THEN
381: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
382: hr_utility.raise_error;
378: hr_utility.trace('l_address_line1 = ' || l_address_line1);
379:
380: IF l_address_line1 IS NULL THEN
381: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
382: hr_utility.raise_error;
383: END IF;
384:
385: END;
386:
408: p_business_group_id NUMBER;
409: p_report_type VARCHAR2(30);
410:
411: BEGIN
412: -- hr_utility.trace_on(null,'PDF');
413: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 10);
414:
415: get_report_parameters(
416: p_pactid,
409: p_report_type VARCHAR2(30);
410:
411: BEGIN
412: -- hr_utility.trace_on(null,'PDF');
413: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 10);
414:
415: get_report_parameters(
416: p_pactid,
417: p_year_start,
419: p_report_type,
420: p_business_group_id
421: );
422:
423: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor', 20);
424:
425:
426: p_sqlstr := 'select distinct to_number(fai1.value)
427: from ff_archive_items fai1,
450: and hoi.org_information_context =''Prov Reporting Est''
451: and hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
452: order by to_number(fai1.value)' ;
453:
454: hr_utility.set_location( 'pay_ca_rl1_mag.range_cursor',40);
455:
456: END range_cursor;
457:
458: -------------------------------------------------------------------------------
540: l_return NUMBER;
541:
542: BEGIN
543:
544: -- hr_utility.trace_on('Y','RL1MAG');
545:
546: l_prev_payact := -1;
547: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',10);
548:
543:
544: -- hr_utility.trace_on('Y','RL1MAG');
545:
546: l_prev_payact := -1;
547: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',10);
548:
549: get_report_parameters(
550: p_pactid,
551: l_year_start,
557: validate_transmitter_info(p_pactid,
558: l_business_group_id,
559: l_year_end);
560:
561: hr_utility.set_location( 'pay_ca_rl1_mag.create_assignement_act',20);
562:
563: --IF l_report_type = 'PYRL1MAG' THEN
564:
565: OPEN c_all_asg;
570: l_effective_end_date,
571: l_assignment_action_id,
572: l_payroll_act;
573:
574: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 30);
575:
576: EXIT WHEN c_all_asg%NOTFOUND;
577:
578: /* Added by ssmukher for validating the
578: /* Added by ssmukher for validating the
579: Quebec Identification Number */
580:
581: if l_prev_payact <> l_payroll_act then
582: hr_utility.trace('The payroll action id '||l_payroll_act);
583:
584: l_prev_payact := l_payroll_act;
585: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
586: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
584: l_prev_payact := l_payroll_act;
585: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
586: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
587:
588: hr_utility.trace('The Quebec Number is '||l_quebec_val);
589:
590: l_quebec_no := substr(l_quebec_val ,1,10);
591:
592: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
588: hr_utility.trace('The Quebec Number is '||l_quebec_val);
589:
590: l_quebec_no := substr(l_quebec_val ,1,10);
591:
592: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
593: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
594: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
595:
596: end if ;
589:
590: l_quebec_no := substr(l_quebec_val ,1,10);
591:
592: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
593: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
594: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
595:
596: end if ;
597:
596: end if ;
597:
598: --Create the assignment action for the record
599:
600: hr_utility.trace('Assignment Fetched - ');
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
597:
598: --Create the assignment action for the record
599:
600: hr_utility.trace('Assignment Fetched - ');
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
605: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
598: --Create the assignment action for the record
599:
600: hr_utility.trace('Assignment Fetched - ');
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
605: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
606:
599:
600: hr_utility.trace('Assignment Fetched - ');
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
605: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
606:
607: SELECT pay_assignment_actions_s.nextval
600: hr_utility.trace('Assignment Fetched - ');
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
605: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
606:
607: SELECT pay_assignment_actions_s.nextval
608: INTO lockingactid
601: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
602: hr_utility.trace('Person Id : '|| to_char(l_person_id));
603: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
604: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
605: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 40);
606:
607: SELECT pay_assignment_actions_s.nextval
608: INTO lockingactid
609: FROM dual;
607: SELECT pay_assignment_actions_s.nextval
608: INTO lockingactid
609: FROM dual;
610:
611: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 50);
612: hr_nonrun_asact.insact(lockingactid,
613: l_assignment_id,
614: p_pactid,
615: p_chunk,
618:
619: /* Update the serial number column with the person id
620: */
621:
622: -- hr_utility.trace('updating asg. action');
623:
624: update pay_assignment_actions aa
625: set aa.serial_number = to_char(l_person_id)
626: where aa.assignment_action_id = lockingactid;
624: update pay_assignment_actions aa
625: set aa.serial_number = to_char(l_person_id)
626: where aa.assignment_action_id = lockingactid;
627:
628: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
629: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
630: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
631: hr_utility.trace('Interlock Created - ');
632: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
626: where aa.assignment_action_id = lockingactid;
627:
628: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
629: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
630: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
631: hr_utility.trace('Interlock Created - ');
632: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
633: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
634: END LOOP;
627:
628: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
629: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
630: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
631: hr_utility.trace('Interlock Created - ');
632: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
633: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
634: END LOOP;
635: CLOSE c_all_asg;
628: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 60);
629: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
630: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
631: hr_utility.trace('Interlock Created - ');
632: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
633: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
634: END LOOP;
635: CLOSE c_all_asg;
636:
629: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
630: hr_utility.set_location('pay_ca_rl1_mag.create_assignement_act', 70);
631: hr_utility.trace('Interlock Created - ');
632: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
633: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
634: END LOOP;
635: CLOSE c_all_asg;
636:
637: -- END IF;
754: l_max := 7;
755: l_total := 0;
756:
757: l_act_chk_number := to_number(substr(p_quebec_no,10,1));
758: hr_utility.trace('here1');
759: if TRANSLATE(p_quebec_no,'0123456789','9999999999') = '9999999999RS9999' then
760: l_quebec := to_number(substr(p_quebec_no,1,9));
761: loop
762: if i > 9 then
784: l_chk_digit := 11 - l_modulus;
785: end if;
786:
787: if l_chk_digit <> l_act_chk_number then
788: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
789: hr_utility.set_message_token('PRE_NAME',p_qin_name);
790: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
791: pay_core_utils.push_token('PRE_NAME',p_qin_name);
792: hr_utility.raise_error;
785: end if;
786:
787: if l_chk_digit <> l_act_chk_number then
788: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
789: hr_utility.set_message_token('PRE_NAME',p_qin_name);
790: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
791: pay_core_utils.push_token('PRE_NAME',p_qin_name);
792: hr_utility.raise_error;
793: end if;
788: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
789: hr_utility.set_message_token('PRE_NAME',p_qin_name);
790: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
791: pay_core_utils.push_token('PRE_NAME',p_qin_name);
792: hr_utility.raise_error;
793: end if;
794: else
795:
796: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
792: hr_utility.raise_error;
793: end if;
794: else
795:
796: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
797: hr_utility.set_message_token('PRE_NAME',p_qin_name);
798: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
799: pay_core_utils.push_token('PRE_NAME',p_qin_name);
800: hr_utility.raise_error;
793: end if;
794: else
795:
796: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
797: hr_utility.set_message_token('PRE_NAME',p_qin_name);
798: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
799: pay_core_utils.push_token('PRE_NAME',p_qin_name);
800: hr_utility.raise_error;
801:
796: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
797: hr_utility.set_message_token('PRE_NAME',p_qin_name);
798: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
799: pay_core_utils.push_token('PRE_NAME',p_qin_name);
800: hr_utility.raise_error;
801:
802: end if;
803:
804: return l_chk_digit;
875: l_VersionSchema VARCHAR2(20);
876:
877: BEGIN
878:
879: hr_utility.trace('XML Transmitter');
880:
881: SELECT
882: fnd_global.local_chr(13) || fnd_global.local_chr(10)
883: INTO EOL
911: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
912: l_context1
913: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
914:
915: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
916: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
917:
918:
919: -- Annee
912: l_context1
913: := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
914:
915: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
916: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
917:
918:
919: -- Annee
920: tab_transmitter(lAnnee) := '
917:
918:
919: -- Annee
920: tab_transmitter(lAnnee) := '
921: hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
922:
923: -- TypeEnvoi
924: tab_transmitter(lTypeEnvoi) := '
925: pay_ca_archive_utils.get_archive_value(l_context1,
923: -- TypeEnvoi
924: tab_transmitter(lTypeEnvoi) := '
925: pay_ca_archive_utils.get_archive_value(l_context1,
926: 'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE') || '
927: hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
928: tab_transmitter(lTypeEnvoi));
929:
930: tab_transmitter(lProvenance) := '
931: pay_ca_archive_utils.get_archive_value(l_context1,
930: tab_transmitter(lProvenance) := '
931: pay_ca_archive_utils.get_archive_value(l_context1,
932: 'CAEOY_RL1_SOURCE_OF_SLIPS') || '
933:
934: hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
935:
936: tab_transmitter(lNo) := '
937: pay_ca_archive_utils.get_archive_value(l_context1,
938: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
936: tab_transmitter(lNo) := '
937: pay_ca_archive_utils.get_archive_value(l_context1,
938: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
939:
940: hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
941:
942: l_return := pay_ca_archive_utils.get_archive_value(l_context1,
943: 'CAEOY_RL1_TRANSMITTER_TYPE');
944: IF l_return IS NOT NULL THEN
946: ELSE
947: tab_transmitter(lType) := NULL;
948: END IF;
949:
950: hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
951:
952: l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
953: 'CAEOY_RL1_TRANSMITTER_NAME');
954:
954:
955: tab_transmitter(lNom1) := '
956: convert_special_char(substr(l_transmitter_name,1,30)) || '
957:
958: hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
959:
960: l_return := substr(l_transmitter_name,31,30);
961: IF l_return IS NOT NULL THEN
962: tab_transmitter(lNom2) := '
963: ELSE
964: tab_transmitter(lNom2) := NULL;
965: END IF;
966:
967: hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
968:
969: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
970: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
971:
971:
972: tab_transmitter(lLigne1) := '
973: convert_special_char(substr(l_address_line,1,30)) || '
974:
975: hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
976:
977: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
978: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
979:
983: ELSE
984: tab_transmitter(lLigne2) := NULL;
985: END IF;
986:
987: hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
988:
989: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
990: 'CAEOY_RL1_TRANSMITTER_CITY');
991:
995: ELSE
996: tab_transmitter(lVille) := NULL;
997: END IF;
998:
999: hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1000:
1001: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1002: 'CAEOY_RL1_TRANSMITTER_PROVINCE');
1003:
1004: tab_transmitter(lProvince) := '
1005: SUBSTR(hr_general.decode_lookup(
1006: 'CA_PROVINCE',l_address_line),1,20) || '
1007:
1008: hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1009:
1010: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1011: 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
1012:
1012:
1013: tab_transmitter(lCodePostal) := '
1014: substr(replace(l_address_line,' '),1,6) || '
1015:
1016: hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1017:
1018: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1019: 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
1020:
1024: ELSE
1025: tab_transmitter(lNom) := NULL;
1026: END IF;
1027:
1028: hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1029:
1030: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1031: 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
1032:
1036: ELSE
1037: tab_transmitter(lIndRegional) := NULL;
1038: END IF;
1039:
1040: hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1041:
1042: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1043: 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
1044: IF l_contact IS NOT NULL THEN
1046: tab_transmitter(lTel) := '
1047: ELSE
1048: tab_transmitter(lTel) := NULL;
1049: END IF;
1050: hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1051:
1052: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1053: 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
1054:
1057: '' || EOL;
1058: ELSE
1059: tab_transmitter(lPosteTel) := NULL;
1060: END IF;
1061: hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1062: tab_transmitter(lPosteTel));
1063:
1064: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1065: 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
1078: convert_special_char(substr(l_contact,1,30)) || '' || EOL;
1079: ELSE
1080: tab_transmitter(lANom) := NULL;
1081: END IF;
1082: hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1083:
1084: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1085: 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
1086:
1089: '' || EOL;
1090: ELSE
1091: tab_transmitter(lAIndRegional) := NULL;
1092: END IF;
1093: hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1094:
1095: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1096: 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
1097:
1100: tab_transmitter(lATel) := '
1101: ELSE
1102: tab_transmitter(lATel) := NULL;
1103: END IF;
1104: hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1105:
1106: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1107: 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
1108:
1111: '' || EOL;
1112: ELSE
1113: tab_transmitter(lAPosteTel) := NULL;
1114: END IF;
1115: hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1116: tab_transmitter(lAPosteTel));
1117:
1118: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1119: 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
1123: ELSE
1124: tab_transmitter(lALangue) := NULL;
1125: END IF;
1126:
1127: hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1128:
1129: -- For bug 6738509
1130: if(l_taxation_year = '2006') then
1131: l_certification_no := 'RQ-06-01-098';
1195: tab_transmitter(lALangue) || '' || EOL ||
1196: tab_transmitter(lNoConcepteur) ||
1197: '
1215: BEGIN
1216:
1217: l_final_xml_string := '';
1218:
1219: hr_utility.trace('end_of_file l_final_xml_string = '
1220: || l_final_xml_string );
1221: pay_core_files.write_to_magtape_lob(l_final_xml_string);
1222:
1223: END;
1268: lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
1269: lookup_code = p_lookup_code;
1270:
1271: BEGIN
1272: hr_utility.trace('In get_final_xml 10');
1273: hr_utility.trace('p_arch_action_id '||p_arch_action_id);
1274: hr_utility.trace('p_taxation_year '||p_taxation_year);
1275: hr_utility.trace('p_rep_type '||p_rep_type);
1276:
1269: lookup_code = p_lookup_code;
1270:
1271: BEGIN
1272: hr_utility.trace('In get_final_xml 10');
1273: hr_utility.trace('p_arch_action_id '||p_arch_action_id);
1274: hr_utility.trace('p_taxation_year '||p_taxation_year);
1275: hr_utility.trace('p_rep_type '||p_rep_type);
1276:
1277: EOL := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1270:
1271: BEGIN
1272: hr_utility.trace('In get_final_xml 10');
1273: hr_utility.trace('p_arch_action_id '||p_arch_action_id);
1274: hr_utility.trace('p_taxation_year '||p_taxation_year);
1275: hr_utility.trace('p_rep_type '||p_rep_type);
1276:
1277: EOL := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1278: hr_utility.trace('In get_final_xml 20');
1271: BEGIN
1272: hr_utility.trace('In get_final_xml 10');
1273: hr_utility.trace('p_arch_action_id '||p_arch_action_id);
1274: hr_utility.trace('p_taxation_year '||p_taxation_year);
1275: hr_utility.trace('p_rep_type '||p_rep_type);
1276:
1277: EOL := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1278: hr_utility.trace('In get_final_xml 20');
1279:
1274: hr_utility.trace('p_taxation_year '||p_taxation_year);
1275: hr_utility.trace('p_rep_type '||p_rep_type);
1276:
1277: EOL := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1278: hr_utility.trace('In get_final_xml 20');
1279:
1280: open c_province(p_arch_action_id);
1281: fetch c_province into l_province;
1282: close c_province;
1300: 'Y',
1301: 'get_final_xml',
1302: p_xml_blob);
1303:
1304: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1305: hr_utility.trace('1. final 1. XML l_final_xml '||
1306: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1307: hr_utility.trace('In get_final_xml 30');
1308:
1301: 'get_final_xml',
1302: p_xml_blob);
1303:
1304: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1305: hr_utility.trace('1. final 1. XML l_final_xml '||
1306: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1307: hr_utility.trace('In get_final_xml 30');
1308:
1309: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1303:
1304: hr_utility.trace('dbms_lob.getlength(p_xml_blob) ' ||dbms_lob.getlength(p_xml_blob));
1305: hr_utility.trace('1. final 1. XML l_final_xml '||
1306: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1307: hr_utility.trace('In get_final_xml 30');
1308:
1309: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1310: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1311:
1306: dbms_lob.substr(l_final_xml,dbms_lob.getlength(l_final_xml),1));
1307: hr_utility.trace('In get_final_xml 30');
1308:
1309: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1310: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1311:
1312: IF l_is_temp_final_xml = 1 THEN
1313: DBMS_LOB.FREETEMPORARY(l_final_xml);
1314: END IF;
1316: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1317: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1318: l_final_xml := append_to_lob(l_header_xml_string);
1319: dbms_lob.append(l_final_xml,p_xml_blob);
1320: hr_utility.trace('In get_final_xml 40');
1321:
1322: l_trailer_xml := ''||EOL;
1323:
1324: hr_utility.trace('In get_final_xml 50');
1320: hr_utility.trace('In get_final_xml 40');
1321:
1322: l_trailer_xml := ''||EOL;
1323:
1324: hr_utility.trace('In get_final_xml 50');
1325: dbms_lob.createtemporary(l_temp_blob,false,DBMS_LOB.CALL);
1326: dbms_lob.open(l_temp_blob,dbms_lob.lob_readwrite);
1327: l_temp_blob := append_to_lob(l_trailer_xml);
1328: dbms_lob.append(l_final_xml,l_temp_blob);
1327: l_temp_blob := append_to_lob(l_trailer_xml);
1328: dbms_lob.append(l_final_xml,l_temp_blob);
1329:
1330: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1331: hr_utility.trace('Closing l_final_xml' );
1332: dbms_lob.close(l_final_xml);
1333: END IF;
1334: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1335: hr_utility.trace('Closing p_xml_blob' );
1331: hr_utility.trace('Closing l_final_xml' );
1332: dbms_lob.close(l_final_xml);
1333: END IF;
1334: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1335: hr_utility.trace('Closing p_xml_blob' );
1336: dbms_lob.close(p_xml_blob);
1337: END IF;
1338: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1339: hr_utility.trace('Closing l_temp_blob' );
1335: hr_utility.trace('Closing p_xml_blob' );
1336: dbms_lob.close(p_xml_blob);
1337: END IF;
1338: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1339: hr_utility.trace('Closing l_temp_blob' );
1340: dbms_lob.close(l_temp_blob);
1341: END IF;
1342: hr_utility.trace('In get_final_xml 60');
1343: else
1338: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1339: hr_utility.trace('Closing l_temp_blob' );
1340: dbms_lob.close(l_temp_blob);
1341: END IF;
1342: hr_utility.trace('In get_final_xml 60');
1343: else
1344: hr_utility.trace('Negative employee');
1345: OPEN cur_get_meaning('NEG');
1346: FETCH cur_get_meaning
1340: dbms_lob.close(l_temp_blob);
1341: END IF;
1342: hr_utility.trace('In get_final_xml 60');
1343: else
1344: hr_utility.trace('Negative employee');
1345: OPEN cur_get_meaning('NEG');
1346: FETCH cur_get_meaning
1347: INTO l_err_msg;
1348: CLOSE cur_get_meaning;
1348: CLOSE cur_get_meaning;
1349:
1350: l_err_msg := 'Error:'||l_err_msg;
1351: l_is_temp_final_xml := dbms_lob.istemporary(l_final_xml);
1352: hr_utility.trace('Istemporary(l_xml_string) ' ||l_is_temp_final_xml );
1353:
1354: IF l_is_temp_final_xml = 1 THEN
1355: DBMS_LOB.FREETEMPORARY(l_final_xml);
1356: END IF;
1357:
1358: dbms_lob.createtemporary(l_final_xml,false,DBMS_LOB.CALL);
1359: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1360: l_final_xml := append_to_lob(l_err_msg);
1361: hr_utility.trace('In get_final_xml 61');
1362: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1363: hr_utility.trace('Closing l_final_xml' );
1364: dbms_lob.close(l_final_xml);
1365: END IF;
1359: dbms_lob.open(l_final_xml,dbms_lob.lob_readwrite);
1360: l_final_xml := append_to_lob(l_err_msg);
1361: hr_utility.trace('In get_final_xml 61');
1362: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1363: hr_utility.trace('Closing l_final_xml' );
1364: dbms_lob.close(l_final_xml);
1365: END IF;
1366: hr_utility.trace('In get_final_xml 62');
1367: end if;
1362: IF DBMS_LOB.isopen(l_final_xml) = 1 THEN
1363: hr_utility.trace('Closing l_final_xml' );
1364: dbms_lob.close(l_final_xml);
1365: END IF;
1366: hr_utility.trace('In get_final_xml 62');
1367: end if;
1368: hr_utility.trace('In get_final_xml 65');
1369:
1370: return l_final_xml;
1364: dbms_lob.close(l_final_xml);
1365: END IF;
1366: hr_utility.trace('In get_final_xml 62');
1367: end if;
1368: hr_utility.trace('In get_final_xml 65');
1369:
1370: return l_final_xml;
1371:
1372: exception
1370: return l_final_xml;
1371:
1372: exception
1373: when others then
1374: hr_utility.trace('In get_final_xml 70');
1375:
1376: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1377: hr_utility.trace('Raising exception and Closing l_final_xml' );
1378: dbms_lob.close(l_final_xml);
1373: when others then
1374: hr_utility.trace('In get_final_xml 70');
1375:
1376: IF dbms_lob.ISOPEN(l_final_xml)=1 THEN
1377: hr_utility.trace('Raising exception and Closing l_final_xml' );
1378: dbms_lob.close(l_final_xml);
1379: END IF;
1380: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1381: hr_utility.trace('Raising exception and Closing p_xml_string' );
1377: hr_utility.trace('Raising exception and Closing l_final_xml' );
1378: dbms_lob.close(l_final_xml);
1379: END IF;
1380: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
1381: hr_utility.trace('Raising exception and Closing p_xml_string' );
1382: dbms_lob.close(p_xml_blob);
1383: END IF;
1384: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1385: hr_utility.trace('Closing l_temp_blob' );
1381: hr_utility.trace('Raising exception and Closing p_xml_string' );
1382: dbms_lob.close(p_xml_blob);
1383: END IF;
1384: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1385: hr_utility.trace('Closing l_temp_blob' );
1386: dbms_lob.close(l_temp_blob);
1387: END IF;
1388: hr_utility.trace('sqleerm ' || SQLERRM);
1389: raise;
1384: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
1385: hr_utility.trace('Closing l_temp_blob' );
1386: dbms_lob.close(l_temp_blob);
1387: END IF;
1388: hr_utility.trace('sqleerm ' || SQLERRM);
1389: raise;
1390: END get_final_xml;
1391:
1392: /***************************************************************/
1465: ORDER BY
1466: ppf.last_name,ppf.first_name,ppf.middle_names;
1467:
1468: BEGIN
1469: --hr_utility.trace_on(null,'PDF');
1470: hr_utility.trace('inside xml_employee_record');
1471: /*******************************************************************************/
1472: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1473: l_payroll_actid
1466: ppf.last_name,ppf.first_name,ppf.middle_names;
1467:
1468: BEGIN
1469: --hr_utility.trace_on(null,'PDF');
1470: hr_utility.trace('inside xml_employee_record');
1471: /*******************************************************************************/
1472: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1473: l_payroll_actid
1474: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1471: /*******************************************************************************/
1472: --l_rep_type:=pay_magtape_generic.get_parameter_value('REPORT_TYPE'); --
1473: l_payroll_actid
1474: := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1475: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1476: SELECT ppa.report_type
1477: INTO l_rep_type
1478: from pay_payroll_actions ppa
1479: where payroll_action_id=l_payroll_actid;
1476: SELECT ppa.report_type
1477: INTO l_rep_type
1478: from pay_payroll_actions ppa
1479: where payroll_action_id=l_payroll_actid;
1480: hr_utility.trace('report_type='||l_rep_type);
1481: /******************************* ************************************/
1482: l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1483: ('TRANSFER_ACT_ID'));
1484:
1481: /******************************* ************************************/
1482: l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value
1483: ('TRANSFER_ACT_ID'));
1484:
1485: hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1486: || to_char(l_mag_asg_action_id));
1487:
1488: /**********************************************************************/
1489: IF l_rep_type not in ('RL1PAPERPDF','PAYCARL1AMPDF','PAYCARL1CLPDF') THEN /*Added for Bug#5046006*/
1510: END IF;
1511: /**************************************** ******************/
1512:
1513:
1514: hr_utility.trace('XML Employee: l_arch_action_id = '
1515: || to_char(l_arch_action_id));
1516: hr_utility.trace('XML Employee: l_arch_pay_actid = '
1517: || to_char(l_arch_pay_actid));
1518: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1512:
1513:
1514: hr_utility.trace('XML Employee: l_arch_action_id = '
1515: || to_char(l_arch_action_id));
1516: hr_utility.trace('XML Employee: l_arch_pay_actid = '
1517: || to_char(l_arch_pay_actid));
1518: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1519: hr_utility.trace('XML Employee: l_date_earned = '
1520: || to_char(l_date_earned));
1514: hr_utility.trace('XML Employee: l_arch_action_id = '
1515: || to_char(l_arch_action_id));
1516: hr_utility.trace('XML Employee: l_arch_pay_actid = '
1517: || to_char(l_arch_pay_actid));
1518: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1519: hr_utility.trace('XML Employee: l_date_earned = '
1520: || to_char(l_date_earned));
1521: hr_utility.trace('XML Employee: l_province = ' || l_province);
1522:
1515: || to_char(l_arch_action_id));
1516: hr_utility.trace('XML Employee: l_arch_pay_actid = '
1517: || to_char(l_arch_pay_actid));
1518: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1519: hr_utility.trace('XML Employee: l_date_earned = '
1520: || to_char(l_date_earned));
1521: hr_utility.trace('XML Employee: l_province = ' || l_province);
1522:
1523: l_taxation_year
1517: || to_char(l_arch_pay_actid));
1518: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1519: hr_utility.trace('XML Employee: l_date_earned = '
1520: || to_char(l_date_earned));
1521: hr_utility.trace('XML Employee: l_province = ' || l_province);
1522:
1523: l_taxation_year
1524: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1525: if l_rep_type in ('RL1PAPERPDF','PAYCARL1AMPDF','PAYCARL1CLPDF') then
1801: lv_pre_pr_form_no varchar2(240);
1802: l_org_form_no varchar2(25);
1803: BEGIN
1804:
1805: hr_utility.trace('Start of fetch_rl1_xml');
1806: l_status := 'Success';
1807: l_all_box_0 := TRUE;
1808: l_format_mask := '99999999999999990.99';
1809:
1914: --Added for bug 13567075 /*sbachu*/
1915: l_FraisGarde := 99;
1916: l_DeductionForce := 100;
1917:
1918: hr_utility.trace('fetch_rl1_xml: p_arch_action_id = '||p_arch_action_id);
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1915: l_FraisGarde := 99;
1916: l_DeductionForce := 100;
1917:
1918: hr_utility.trace('fetch_rl1_xml: p_arch_action_id = '||p_arch_action_id);
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1916: l_DeductionForce := 100;
1917:
1918: hr_utility.trace('fetch_rl1_xml: p_arch_action_id = '||p_arch_action_id);
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1917:
1918: hr_utility.trace('fetch_rl1_xml: p_arch_action_id = '||p_arch_action_id);
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1925: hr_utility.trace('fetch_rl1_xml: called_from = '||called_from);
1918: hr_utility.trace('fetch_rl1_xml: p_arch_action_id = '||p_arch_action_id);
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1925: hr_utility.trace('fetch_rl1_xml: called_from = '||called_from);
1926:
1919: hr_utility.trace('fetch_rl1_xml: p_arch_pay_actid = '||p_arch_pay_actid);
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1925: hr_utility.trace('fetch_rl1_xml: called_from = '||called_from);
1926:
1927: l_authorization_header := 'No d''autorisation :';
1920: hr_utility.trace('fetch_rl1_xml: p_asg_id = '||p_asg_id);
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1925: hr_utility.trace('fetch_rl1_xml: called_from = '||called_from);
1926:
1927: l_authorization_header := 'No d''autorisation :';
1928:
1921: hr_utility.trace('fetch_rl1_xml: p_rep_type = '||p_rep_type);
1922: hr_utility.trace('fetch_rl1_xml: p_province = '||p_province);
1923: hr_utility.trace('fetch_rl1_xml: p_taxation_year = '||p_taxation_year);
1924: hr_utility.trace('fetch_rl1_xml: p_print_instruction = '||p_print_instruction);
1925: hr_utility.trace('fetch_rl1_xml: called_from = '||called_from);
1926:
1927: l_authorization_header := 'No d''autorisation :';
1928:
1929: l_year := pay_ca_archive_utils.get_archive_value(p_arch_pay_actid, 'CAEOY_TAXATION_YEAR');
1941: close c_get_auth_code;
1942:
1943: --Annee
1944: tab_employee(lAnnee) := '
1945: hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1946: --NoReleve
1947: /* Bug 13564765 sbachu*/
1948: lv_rl1_slip_no := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
1949: 'CAEOY_RL1_SLIP_NUMBER');
1956: ELSE
1957: --added for bug 10420127
1958: if ((pay_ca_archive_utils.get_archive_value(p_arch_pay_actid,
1959: 'CAEOY_TAXATION_YEAR')) >= '2010') then
1960: hr_utility.trace('p_taxation_year is greater than or equal 2010');
1961: l_return := substr(l_return,1,3)||
1962: '-'||substr(l_return,4,3)||
1963: '-'||substr(l_return,7);
1964: hr_utility.trace('lNoReleve '||l_return);
1960: hr_utility.trace('p_taxation_year is greater than or equal 2010');
1961: l_return := substr(l_return,1,3)||
1962: '-'||substr(l_return,4,3)||
1963: '-'||substr(l_return,7);
1964: hr_utility.trace('lNoReleve '||l_return);
1965: end if;
1966: tab_employee(lNoReleve) := '
1967: '
1968: tab_xml_employee(lNoReleve) := l_return; --
1966: tab_employee(lNoReleve) := '
1967: '
1968: tab_xml_employee(lNoReleve) := l_return; --
1969: END IF;
1970: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1971: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1972: --Added for bug 5046006
1973: --NoOrigReleve
1974: IF p_rep_type in ('PAYCARL1AMPDF','PAYCARL1CLPDF') THEN
1967: '' || EOL;
1968: tab_xml_employee(lNoReleve) := l_return; --
1969: END IF;
1970: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve));
1971: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1972: --Added for bug 5046006
1973: --NoOrigReleve
1974: IF p_rep_type in ('PAYCARL1AMPDF','PAYCARL1CLPDF') THEN
1975: /* Bug 13564765 sbachu*/
1990: tab_employee(lNoOrigReleve) := '
1991: '
1992: tab_xml_employee(lNoOrigReleve) := l_return; --
1993: END IF;
1994: hr_utility.trace('tab_employee(lNoOrigReleve) = ' || tab_employee(lNoOrigReleve));
1995: hr_utility.trace('tab_xml_employee(lNoOrigReleve) = ' || tab_xml_employee(lNoOrigReleve));
1996: END IF;
1997: -- End bug 5046006
1998: -- NAS
1991: '' || EOL;
1992: tab_xml_employee(lNoOrigReleve) := l_return; --
1993: END IF;
1994: hr_utility.trace('tab_employee(lNoOrigReleve) = ' || tab_employee(lNoOrigReleve));
1995: hr_utility.trace('tab_xml_employee(lNoOrigReleve) = ' || tab_xml_employee(lNoOrigReleve));
1996: END IF;
1997: -- End bug 5046006
1998: -- NAS
1999: l_return := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2008: l_msg_code := 'SIN';
2009: tab_employee(lNAS) := NULL;
2010: tab_xml_employee(lNAS) := NULL; --
2011: END IF;
2012: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
2013: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
2014:
2015: -- No
2016: l_return := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2009: tab_employee(lNAS) := NULL;
2010: tab_xml_employee(lNAS) := NULL; --
2011: END IF;
2012: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
2013: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
2014:
2015: -- No
2016: l_return := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2017: 'CAEOY_EMPLOYEE_NUMBER');
2021: ELSE
2022: tab_employee(lNo) := NULL;
2023: tab_xml_employee(lNo) := NULL; --
2024: END IF;
2025: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
2026: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
2027:
2028: -- NomFamille
2029: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2022: tab_employee(lNo) := NULL;
2023: tab_xml_employee(lNo) := NULL; --
2024: END IF;
2025: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
2026: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
2027:
2028: -- NomFamille
2029: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2030: 'CAEOY_EMPLOYEE_LAST_NAME');
2030: 'CAEOY_EMPLOYEE_LAST_NAME');
2031: tab_employee(lNomFamille) := '
2032: convert_special_char(substr(l_name,1,30)) || '
2033: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
2034: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
2035: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
2036:
2037: -- Prenom
2038: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2031: tab_employee(lNomFamille) := '
2032: convert_special_char(substr(l_name,1,30)) || '
2033: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
2034: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
2035: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
2036:
2037: -- Prenom
2038: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2039: 'CAEOY_EMPLOYEE_FIRST_NAME');
2047: l_msg_code := 'MISSING_EMP_FIRST_NAME';
2048: tab_employee(lPrenom) := NULL;
2049: tab_xml_employee(lPrenom) := NULL ; --
2050: END IF;
2051: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
2052: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
2053:
2054: -- Initiale
2055: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2048: tab_employee(lPrenom) := NULL;
2049: tab_xml_employee(lPrenom) := NULL ; --
2050: END IF;
2051: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
2052: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
2053:
2054: -- Initiale
2055: l_name := pay_ca_archive_utils.get_archive_value(p_arch_action_id,
2056: 'CAEOY_EMPLOYEE_INITIAL');
2062: ELSE
2063: tab_employee(lInitiale) := NULL;
2064: tab_xml_employee(lInitiale) := NULL; --
2065: END IF;
2066: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
2067: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
2068:
2069: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
2070: p_arch_action_id,
2063: tab_employee(lInitiale) := NULL;
2064: tab_xml_employee(lInitiale) := NULL; --
2065: END IF;
2066: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
2067: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
2068:
2069: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(
2070: p_arch_action_id,
2071: 'CAEOY_PERSON_ID'));
2075: p_arch_action_id,
2076: 'CAEOY_PERSON_ID'));
2077:
2078: select trunc(sysdate) into l_session_date from dual;
2079: hr_utility.trace('l_person_id1 ='|| l_person_id1);
2080: hr_utility.trace('l_session_date ='|| l_session_date);
2081: addr := pay_ca_rl1_reg.get_primary_address(l_person_id1, l_session_date);
2082: tab_xml_employee(lLigne1) := convert_special_char(substr(addr.addr_line_1,1,40));
2083: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
2076: 'CAEOY_PERSON_ID'));
2077:
2078: select trunc(sysdate) into l_session_date from dual;
2079: hr_utility.trace('l_person_id1 ='|| l_person_id1);
2080: hr_utility.trace('l_session_date ='|| l_session_date);
2081: addr := pay_ca_rl1_reg.get_primary_address(l_person_id1, l_session_date);
2082: tab_xml_employee(lLigne1) := convert_special_char(substr(addr.addr_line_1,1,40));
2083: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
2084: tab_xml_employee(lVille) := convert_special_char(substr(addr.addr_line_4,1,40));
2083: tab_xml_employee(lLigne2) := convert_special_char(substr(addr.addr_line_2,1,40)||' '||substr(addr.addr_line_3,1,40));
2084: tab_xml_employee(lVille) := convert_special_char(substr(addr.addr_line_4,1,40));
2085: tab_xml_employee(lForm_number) := pay_ca_archive_utils.get_archive_value(p_arch_action_id, --l_mag_asg_action_id
2086: 'CAEOY_RL1_PRE_PRINTED_FORM_NO');
2087: hr_utility.trace('tab_xml_employee(lForm_number) = ' || tab_xml_employee(lForm_number));
2088:
2089:
2090: tab_xml_employee(lAnnee) := pay_ca_archive_utils.get_archive_value(p_arch_pay_actid,
2091: 'CAEOY_TAXATION_YEAR');
2088:
2089:
2090: tab_xml_employee(lAnnee) := pay_ca_archive_utils.get_archive_value(p_arch_pay_actid,
2091: 'CAEOY_TAXATION_YEAR');
2092: hr_utility.trace('tab_xml_employee(lAnnee) = ' || tab_xml_employee(lAnnee)); --
2093: /***************************** *****************************/
2094: else /***if not paper report***/
2095: l_return := pay_ca_emp_address_dtls.get_emp_address(
2096: l_person_id,
2105: -- If Address line 1 is NULL or ' ' then the employee is missing
2106: -- address information - as line 1 is mandatory in the Address form.
2107: -- Need to check data by SS transaction /API.
2108:
2109: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
2110: hr_utility.trace('l_address_line1 = ' || l_address_line1);
2111: hr_utility.trace('l_address_line2 = ' || l_address_line2);
2112: hr_utility.trace('l_postal_code = ' || l_postal_code);
2113:
2106: -- address information - as line 1 is mandatory in the Address form.
2107: -- Need to check data by SS transaction /API.
2108:
2109: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
2110: hr_utility.trace('l_address_line1 = ' || l_address_line1);
2111: hr_utility.trace('l_address_line2 = ' || l_address_line2);
2112: hr_utility.trace('l_postal_code = ' || l_postal_code);
2113:
2114: -- Address Line 1
2107: -- Need to check data by SS transaction /API.
2108:
2109: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
2110: hr_utility.trace('l_address_line1 = ' || l_address_line1);
2111: hr_utility.trace('l_address_line2 = ' || l_address_line2);
2112: hr_utility.trace('l_postal_code = ' || l_postal_code);
2113:
2114: -- Address Line 1
2115: IF l_address_line1 IS NULL OR
2108:
2109: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
2110: hr_utility.trace('l_address_line1 = ' || l_address_line1);
2111: hr_utility.trace('l_address_line2 = ' || l_address_line2);
2112: hr_utility.trace('l_postal_code = ' || l_postal_code);
2113:
2114: -- Address Line 1
2115: IF l_address_line1 IS NULL OR
2116: l_address_line1 = ' ' THEN
2132: l_addr_begin_tag := '
2133:
2134: tab_employee(lLigne1) := '
2135: convert_special_char(substr(l_address_line1,1,30)) || '
2136: hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
2137:
2138: -- Address Line 2
2139: IF ((l_address_line2 IS NULL OR
2140: l_address_line2 <> ' ') OR
2145: convert_special_char(substr(l_combined_addr,1,30)) || '' || EOL;
2146: ELSE
2147: tab_employee(lLigne2) := NULL;
2148: END IF;
2149: hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
2150:
2151: -- Ville (City)
2152: IF l_city IS NULL OR
2153: l_city <> ' ' THEN
2155: substr(l_city,1,30) || '' || EOL;
2156: ELSE
2157: tab_employee(lVille) := NULL;
2158: END IF;
2159: hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
2160:
2161: -- Province
2162: IF l_emp_province IS NULL OR
2163: l_emp_province <> ' ' THEN
2172: END IF;
2173: ELSE
2174: tab_employee(lProvince) := NULL;
2175: END IF;
2176: hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
2177:
2178: -- Postal Code
2179: IF l_postal_code IS NULL OR
2180: l_postal_code <> ' ' THEN
2182: substr(replace(l_postal_code,' '),1,6) || '' || EOL;
2183: ELSE
2184: tab_employee(lCodePostal) := NULL;
2185: END IF;
2186: hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
2187: l_addr_end_tag := '';
2188:
2189: END IF;
2190:
2234: ELSE
2235: tab_employee(lA_RevenuEmploi) := NULL;
2236: tab_xml_employee(lA_RevenuEmploi) := NULL; --
2237: END IF;
2238: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
2239: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
2240:
2241: -- Summ (Box B)
2242: l_box := pay_ca_archive_utils.get_archive_value(
2235: tab_employee(lA_RevenuEmploi) := NULL;
2236: tab_xml_employee(lA_RevenuEmploi) := NULL; --
2237: END IF;
2238: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
2239: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
2240:
2241: -- Summ (Box B)
2242: l_box := pay_ca_archive_utils.get_archive_value(
2243: p_arch_action_id,
2268: tab_employee(lB_CotisationRRQ) := NULL;
2269: tab_xml_employee(lB_CotisationRRQ) := NULL; --
2270:
2271: END IF;
2272: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
2273: tab_employee(lB_CotisationRRQ));
2274: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
2275: tab_xml_employee(lB_CotisationRRQ)); --
2276:
2270:
2271: END IF;
2272: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
2273: tab_employee(lB_CotisationRRQ));
2274: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
2275: tab_xml_employee(lB_CotisationRRQ)); --
2276:
2277: -- Summ (Box C)
2278: l_box := pay_ca_archive_utils.get_archive_value(
2301: tab_employee(lC_CotisationAssEmploi) := NULL;
2302: tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
2303: END IF;
2304:
2305: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2306: tab_employee(lC_CotisationAssEmploi));
2307:
2308: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2309: tab_xml_employee(lC_CotisationAssEmploi)); --
2304:
2305: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2306: tab_employee(lC_CotisationAssEmploi));
2307:
2308: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2309: tab_xml_employee(lC_CotisationAssEmploi)); --
2310: -- Summ (Box D)
2311: l_box := pay_ca_archive_utils.get_archive_value(
2312: p_arch_action_id,
2333: ELSE
2334: tab_employee(lD_CotisationRPA) := NULL;
2335: tab_xml_employee(lD_CotisationRPA) := NULL; --
2336: END IF;
2337: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2338: tab_employee(lD_CotisationRPA));
2339: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2340: tab_xml_employee(lD_CotisationRPA));
2341:
2335: tab_xml_employee(lD_CotisationRPA) := NULL; --
2336: END IF;
2337: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2338: tab_employee(lD_CotisationRPA));
2339: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2340: tab_xml_employee(lD_CotisationRPA));
2341:
2342:
2343: -- (Box E)
2368: tab_employee(lE_ImpotQue) := NULL ;
2369: tab_xml_employee(lE_ImpotQue) := NULL ; --
2370: END IF;
2371:
2372: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2373: tab_employee(lE_ImpotQue));
2374: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2375: tab_xml_employee(lE_ImpotQue)); --
2376:
2370: END IF;
2371:
2372: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2373: tab_employee(lE_ImpotQue));
2374: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2375: tab_xml_employee(lE_ImpotQue)); --
2376:
2377: -- (Box F)
2378: l_box := pay_ca_archive_utils.get_archive_value(
2400: ELSE
2401: tab_employee(lF_CotisationSyndicale) := NULL;
2402: tab_xml_employee(lF_CotisationSyndicale) := NULL; --
2403: END IF;
2404: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2405: tab_employee(lF_CotisationSyndicale));
2406:
2407: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2408: tab_xml_employee(lF_CotisationSyndicale));
2403: END IF;
2404: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2405: tab_employee(lF_CotisationSyndicale));
2406:
2407: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2408: tab_xml_employee(lF_CotisationSyndicale));
2409: -- (Box Q)
2410: l_box := pay_ca_archive_utils.get_archive_value(
2411: p_arch_action_id,
2433: ELSE
2434: tab_employee(lQ_SalaireDiffere) := NULL;
2435: tab_xml_employee(lQ_SalaireDiffere) := NULL; --
2436: END IF;
2437: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2438: tab_employee(lQ_SalaireDiffere));
2439:
2440: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2441: tab_xml_employee(lQ_SalaireDiffere));
2436: END IF;
2437: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2438: tab_employee(lQ_SalaireDiffere));
2439:
2440: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2441: tab_xml_employee(lQ_SalaireDiffere));
2442: -- (Box U)
2443: l_box := pay_ca_archive_utils.get_archive_value(
2444: p_arch_action_id,
2468: ELSE
2469: tab_employee(lU_RetraiteProgressive) := NULL;
2470: tab_xml_employee(lU_RetraiteProgressive) := NULL; --
2471: END IF;
2472: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2473: tab_employee(lU_RetraiteProgressive));
2474:
2475: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2476: tab_xml_employee(lU_RetraiteProgressive));
2471: END IF;
2472: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2473: tab_employee(lU_RetraiteProgressive));
2474:
2475: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2476: tab_xml_employee(lU_RetraiteProgressive));
2477: -- (Box G)
2478: l_box := pay_ca_archive_utils.get_archive_value(
2479: p_arch_action_id,
2482: --'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2483: --commented for bug 13360872
2484: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2485:
2486: hr_utility.trace('l_box = ' || l_box);
2487: hr_utility.trace('l_boxA = ' || l_BoxA);
2488: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2489: hr_utility.trace('l_boxU = ' || l_boxU);
2490:
2483: --commented for bug 13360872
2484: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2485:
2486: hr_utility.trace('l_box = ' || l_box);
2487: hr_utility.trace('l_boxA = ' || l_BoxA);
2488: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2489: hr_utility.trace('l_boxU = ' || l_boxU);
2490:
2491: IF p_rep_type not in ('RL1PAPERPDF','PAYCARL1AMPDF','PAYCARL1CLPDF') THEN /*Added for Bug#5046006*/
2484: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2485:
2486: hr_utility.trace('l_box = ' || l_box);
2487: hr_utility.trace('l_boxA = ' || l_BoxA);
2488: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2489: hr_utility.trace('l_boxU = ' || l_boxU);
2490:
2491: IF p_rep_type not in ('RL1PAPERPDF','PAYCARL1AMPDF','PAYCARL1CLPDF') THEN /*Added for Bug#5046006*/
2492:
2485:
2486: hr_utility.trace('l_box = ' || l_box);
2487: hr_utility.trace('l_boxA = ' || l_BoxA);
2488: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2489: hr_utility.trace('l_boxU = ' || l_boxU);
2490:
2491: IF p_rep_type not in ('RL1PAPERPDF','PAYCARL1AMPDF','PAYCARL1CLPDF') THEN /*Added for Bug#5046006*/
2492:
2493: IF l_box IS NOT NULL THEN
2545: ELSE
2546: tab_employee(lG_SalaireAdmisRRQ) := '
2547: '0.00
2548: END IF;
2549: hr_utility.trace('tab_employee(lG_SalaireAdmisRRQ) = ' ||
2550: tab_employee(lG_SalaireAdmisRRQ));
2551: END IF;
2552:
2553: IF p_rep_type ='RL1PAPERPDF' or p_rep_type ='PAYCARL1AMPDF' or p_rep_type ='PAYCARL1CLPDF'
2584: ELSE
2585: tab_xml_employee(lG_SalaireAdmisRRQ) :='0.00' || EOL;
2586: END IF;
2587:
2588: hr_utility.trace('tab_xml_employee(lG_SalaireAdmisRRQ) = ' ||
2589: tab_xml_employee(lG_SalaireAdmisRRQ));
2590:
2591: END IF;
2592:
2618: tab_employee(lV_NourritureLogement) := NULL;
2619: tab_xml_employee(lV_NourritureLogement) := NULL; --
2620: END IF;
2621:
2622: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2623: tab_employee(lV_NourritureLogement));
2624:
2625: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2626: tab_xml_employee(lV_NourritureLogement));
2621:
2622: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2623: tab_employee(lV_NourritureLogement));
2624:
2625: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2626: tab_xml_employee(lV_NourritureLogement));
2627: -- (Box W)
2628: l_box := pay_ca_archive_utils.get_archive_value(
2629: p_arch_action_id,
2650: ELSE
2651: tab_employee(lW_Vehicule) := NULL;
2652: tab_xml_employee(lW_Vehicule) := NULL; --
2653: END IF;
2654: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2655: tab_employee(lW_Vehicule));
2656:
2657: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2658: tab_xml_employee(lW_Vehicule));
2653: END IF;
2654: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2655: tab_employee(lW_Vehicule));
2656:
2657: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2658: tab_xml_employee(lW_Vehicule));
2659:
2660: --(BOX H)
2661: l_box := pay_ca_archive_utils.get_archive_value(
2682: ELSE
2683: tab_employee(lH_CotisationRQAP) := NULL;
2684: tab_xml_employee(lH_CotisationRQAP) := NULL; --
2685: END IF;
2686: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2687: tab_employee(lH_CotisationRQAP));
2688:
2689: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2690: tab_xml_employee(lH_CotisationRQAP));
2685: END IF;
2686: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2687: tab_employee(lH_CotisationRQAP));
2688:
2689: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2690: tab_xml_employee(lH_CotisationRQAP));
2691:
2692: --(BOX I)
2693: l_box := pay_ca_archive_utils.get_archive_value(
2717: tab_employee(lI_SalaireAdmisRQAP) := '
2718: '0.00' || '
2719: tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2720: END IF;
2721: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2722: tab_employee(lI_SalaireAdmisRQAP));
2723:
2724: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2725: tab_xml_employee(lI_SalaireAdmisRQAP));
2720: END IF;
2721: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2722: tab_employee(lI_SalaireAdmisRQAP));
2723:
2724: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2725: tab_xml_employee(lI_SalaireAdmisRQAP));
2726:
2727: -- (Box J)
2728: l_box := pay_ca_archive_utils.get_archive_value(
2750: ELSE
2751: tab_employee(lJ_RegimeAssMaladie) := NULL;
2752: tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2753: END IF;
2754: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2755: tab_employee(lJ_RegimeAssMaladie));
2756:
2757: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2758: tab_xml_employee(lJ_RegimeAssMaladie));
2753: END IF;
2754: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2755: tab_employee(lJ_RegimeAssMaladie));
2756:
2757: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2758: tab_xml_employee(lJ_RegimeAssMaladie));
2759:
2760: -- (Box K)
2761: l_box := pay_ca_archive_utils.get_archive_value(
2783: ELSE
2784: tab_employee(lK_Voyage) := NULL;
2785: tab_xml_employee(lK_Voyage) := NULL; --
2786: END IF;
2787: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2788: tab_employee(lK_Voyage));
2789:
2790: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2791: tab_xml_employee(lK_Voyage));
2786: END IF;
2787: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2788: tab_employee(lK_Voyage));
2789:
2790: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2791: tab_xml_employee(lK_Voyage));
2792: -- (Box L)
2793: l_box := pay_ca_archive_utils.get_archive_value(
2794: p_arch_action_id,
2815: ELSE
2816: tab_employee(lL_AutreAvantage) := NULL;
2817: tab_xml_employee(lL_AutreAvantage) := NULL; --
2818: END IF;
2819: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2820: tab_employee(lL_AutreAvantage));
2821:
2822: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2823: tab_xml_employee(lL_AutreAvantage));
2818: END IF;
2819: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2820: tab_employee(lL_AutreAvantage));
2821:
2822: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2823: tab_xml_employee(lL_AutreAvantage));
2824: -- (Box M)
2825: l_box := pay_ca_archive_utils.get_archive_value(
2826: p_arch_action_id,
2847: ELSE
2848: tab_employee(lM_Commission) := NULL;
2849: tab_xml_employee(lM_Commission) := NULL; --
2850: END IF;
2851: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2852: tab_employee(lM_Commission));
2853:
2854: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2855: tab_xml_employee(lM_Commission));
2850: END IF;
2851: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2852: tab_employee(lM_Commission));
2853:
2854: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2855: tab_xml_employee(lM_Commission));
2856: -- (Box N)
2857: l_box := pay_ca_archive_utils.get_archive_value(
2858: p_arch_action_id,
2880: tab_employee(lN_DonBienfaisance) := NULL;
2881: tab_xml_employee(lN_DonBienfaisance) := NULL; --
2882: END IF;
2883:
2884: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2885: tab_employee(lN_DonBienfaisance));
2886:
2887: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2888: tab_xml_employee(lN_DonBienfaisance));
2883:
2884: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2885: tab_employee(lN_DonBienfaisance));
2886:
2887: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2888: tab_xml_employee(lN_DonBienfaisance));
2889: -- Summ (Box O)
2890: l_box := pay_ca_archive_utils.get_archive_value(
2891: p_arch_action_id,
2891: p_arch_action_id,
2892: p_province,
2893: 'JURISDICTION_CODE',
2894: 'CAEOY_RL1_BOXO_PER_JD_YTD');
2895: hr_utility.trace('l_box=' ||l_box);
2896: IF TO_NUMBER(l_box) > 9999999.99 THEN
2897: l_status := 'Failed';
2898: l_msg_code := 'AMT_GREATER_THAN_RANGE';
2899: END IF;
2913: ELSE
2914: tab_employee(lO_AutreRevenu) := NULL;
2915: tab_xml_employee(lO_AutreRevenu) := NULL; --
2916: END IF;
2917: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2918: tab_employee(lO_AutreRevenu));
2919:
2920: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2921: tab_xml_employee(lO_AutreRevenu));
2916: END IF;
2917: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2918: tab_employee(lO_AutreRevenu));
2919:
2920: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2921: tab_xml_employee(lO_AutreRevenu));
2922:
2923: -- SourceCasem
2924:
2934: ELSE
2935: tab_employee(l_SourceCase) := NULL;
2936: tab_xml_employee(l_SourceCase) := NULL; --
2937: END IF;
2938: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2939: tab_employee(l_SourceCase));
2940:
2941: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2942: tab_xml_employee(l_SourceCase));
2937: END IF;
2938: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2939: tab_employee(l_SourceCase));
2940:
2941: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2942: tab_xml_employee(l_SourceCase));
2943: -- (Box P)
2944: if tab_employee(lO_AutreRevenu) is not null
2945: and tab_employee(l_SourceCase)is not null then
2975: ELSE
2976: tab_employee(lP_RegimeAssInterEntr) := NULL;
2977: tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2978: END IF;
2979: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2980: tab_employee(lP_RegimeAssInterEntr));
2981:
2982: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2983: tab_xml_employee(lP_RegimeAssInterEntr));
2978: END IF;
2979: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2980: tab_employee(lP_RegimeAssInterEntr));
2981:
2982: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2983: tab_xml_employee(lP_RegimeAssInterEntr));
2984: -- (Box R)
2985: l_box := pay_ca_archive_utils.get_archive_value(
2986: p_arch_action_id,
3007: ELSE
3008: tab_employee(lR_RevenuIndien) := NULL;
3009: tab_xml_employee(lR_RevenuIndien) := NULL; --
3010: END IF;
3011: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
3012: tab_employee(lR_RevenuIndien));
3013:
3014: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
3015: tab_xml_employee(lR_RevenuIndien));
3010: END IF;
3011: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
3012: tab_employee(lR_RevenuIndien));
3013:
3014: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
3015: tab_xml_employee(lR_RevenuIndien));
3016: -- (Box S)
3017: l_box := pay_ca_archive_utils.get_archive_value(
3018: p_arch_action_id,
3039: ELSE
3040: tab_employee(lS_PourboireRecu) := NULL;
3041: tab_xml_employee(lS_PourboireRecu) := NULL; --
3042: END IF;
3043: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
3044: tab_employee(lS_PourboireRecu));
3045:
3046: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
3047: tab_xml_employee(lS_PourboireRecu));
3042: END IF;
3043: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
3044: tab_employee(lS_PourboireRecu));
3045:
3046: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
3047: tab_xml_employee(lS_PourboireRecu));
3048: -- (Box T)
3049: l_box := pay_ca_archive_utils.get_archive_value(
3050: p_arch_action_id,
3071: ELSE
3072: tab_employee(lT_PourboireAttribue) := NULL;
3073: tab_xml_employee(lT_PourboireAttribue) := NULL; --
3074: END IF;
3075: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
3076: tab_employee(lT_PourboireAttribue));
3077: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
3078: tab_xml_employee(lT_PourboireAttribue));
3079:
3073: tab_xml_employee(lT_PourboireAttribue) := NULL; --
3074: END IF;
3075: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
3076: tab_employee(lT_PourboireAttribue));
3077: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
3078: tab_xml_employee(lT_PourboireAttribue));
3079:
3080: -- (Box ContisationRPC)
3081: l_box := pay_ca_archive_utils.get_archive_value(
3102: l_all_box_0 := FALSE;
3103: ELSE
3104: tab_employee(l_ContisationRPC) := NULL;
3105: END IF;
3106: hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
3107: tab_employee(l_ContisationRPC));
3108:
3109: /* Changes for bug 13567075 starts here -- sbachu*/
3110:
3132: l_all_box_0 := FALSE;
3133: ELSE
3134: tab_employee(l_FraisGarde) := NULL;
3135: END IF;
3136: hr_utility.trace('tab_employee(l_FraisGarde) = ' ||
3137: tab_employee(l_FraisGarde));
3138:
3139: l_box := fnd_number.number_to_canonical(fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
3140: p_arch_action_id,
3165: l_all_box_0 := FALSE;
3166: ELSE
3167: tab_employee(l_DeductionForce) := NULL;
3168: END IF;
3169: hr_utility.trace('tab_employee(l_DeductionForce) = ' ||
3170: tab_employee(l_DeductionForce));
3171:
3172: /*Changes for bug 13567075 ends here --sbachu*/
3173:
3171:
3172: /*Changes for bug 13567075 ends here --sbachu*/
3173:
3174: -- Negative Balance Exists
3175: hr_utility.trace('finding if neg bal exists');
3176: l_box := pay_ca_archive_utils.get_archive_value(
3177: p_arch_action_id,
3178: p_province,
3179: 'JURISDICTION_CODE',
3182: IF l_box = 'Y' THEN
3183: l_status := 'Failed';
3184: l_msg_code := 'NEG';
3185: l_neg_bal_exists := TRUE;
3186: hr_utility.trace('neg bal exists');
3187: END IF;
3188:
3189: IF to_number(p_taxation_year) < 2011 AND l_all_box_0 THEN /*Bug 13633422*/
3190: l_status := 'Failed';
3227: ||l_formatted_box||''||EOL;
3228: end if;
3229: end loop;
3230: /* ended further infos bug 13497300,14701466 */
3231: hr_utility.trace('l_msg_code ='|| l_msg_code);
3232:
3233: OPEN cur_get_meaning(l_msg_code);
3234: FETCH cur_get_meaning
3235: INTO l_meaning;
3233: OPEN cur_get_meaning(l_msg_code);
3234: FETCH cur_get_meaning
3235: INTO l_meaning;
3236: CLOSE cur_get_meaning;
3237: hr_utility.trace('l_meaning ='|| l_meaning);
3238: tab_employee(lErrorDetails) := '
3239: l_meaning || '
3240:
3241: l_other_details := l_other_details||tab_employee(lErrorDetails);
3239: l_meaning || '' || EOL;
3240:
3241: l_other_details := l_other_details||tab_employee(lErrorDetails);
3242: END IF;
3243: hr_utility.trace('l_other_details ='||l_other_details);
3244: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3245: tab_employee(lH_CotisationRQAP));
3246: hr_utility.trace('p_rep_type ='|| p_rep_type);
3247: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3240:
3241: l_other_details := l_other_details||tab_employee(lErrorDetails);
3242: END IF;
3243: hr_utility.trace('l_other_details ='||l_other_details);
3244: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3245: tab_employee(lH_CotisationRQAP));
3246: hr_utility.trace('p_rep_type ='|| p_rep_type);
3247: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3248: --
3242: END IF;
3243: hr_utility.trace('l_other_details ='||l_other_details);
3244: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3245: tab_employee(lH_CotisationRQAP));
3246: hr_utility.trace('p_rep_type ='|| p_rep_type);
3247: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3248: --
3249:
3250: if p_rep_type = 'RL1PAPERPDF' or p_rep_type = 'PAYCARL1AMPDF' or p_rep_type = 'PAYCARL1CLPDF'
3243: hr_utility.trace('l_other_details ='||l_other_details);
3244: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
3245: tab_employee(lH_CotisationRQAP));
3246: hr_utility.trace('p_rep_type ='|| p_rep_type);
3247: --hr_utility.trace('l_neg_bal_exists ='|| l_neg_bal_exists);
3248: --
3249:
3250: if p_rep_type = 'RL1PAPERPDF' or p_rep_type = 'PAYCARL1AMPDF' or p_rep_type = 'PAYCARL1CLPDF'
3251: then /*Added for Bug#5046006*/
3250: if p_rep_type = 'RL1PAPERPDF' or p_rep_type = 'PAYCARL1AMPDF' or p_rep_type = 'PAYCARL1CLPDF'
3251: then /*Added for Bug#5046006*/
3252: /********************************/
3253: if l_neg_bal_exists = TRUE and p_rep_type <> 'PAYCARL1CLPDF' then
3254: hr_utility.trace('inside if negative balance true');
3255: xml_footnote_boxo(p_arch_action_id
3256: ,p_asg_id
3257: ,l_footnote_boxo1
3258: ,l_footnote_boxo2
3495: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3496: end if; /*if l_neg_balance_exists is TRUE*/
3497: /************/
3498: if l_neg_bal_exists = FALSE then
3499: hr_utility.trace('inside if');
3500: xml_footnote_boxo(p_arch_action_id
3501: ,p_asg_id
3502: ,l_footnote_boxo1
3503: ,l_footnote_boxo2
3510: ,l_emplyr_final4
3511: ,l_emplyr_final5
3512: ,l_emplyr_final6);
3513:
3514: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3511: ,l_emplyr_final5
3512: ,l_emplyr_final6);
3513:
3514: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3519: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3512: ,l_emplyr_final6);
3513:
3514: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3519: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3520:
3513:
3514: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3519: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3520:
3521: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3514: hr_utility.trace('l_emplyr_final1='||l_emplyr_final1);
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3519: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3520:
3521: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3522:
3515: hr_utility.trace('l_emplyr_final2='||l_emplyr_final2);
3516: hr_utility.trace('l_emplyr_final3='||l_emplyr_final3);
3517: hr_utility.trace('l_footnote_boxo1 ='|| l_footnote_boxo1);
3518: hr_utility.trace('l_footnote_boxo2 ='|| l_footnote_boxo2);
3519: hr_utility.trace('l_footnote_boxo3 ='|| l_footnote_boxo3);
3520:
3521: tab_xml_employee(lNomFamille) := tab_xml_employee(lNomFamille)||','||tab_xml_employee(lPrenom)||' '||tab_xml_employee(lInitiale);
3522:
3523: /*select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;
3529: ,'XMLPROC');
3530: l_sequence_number := get_slip_seq_no('|',lv_rl1_seq_no,1);
3531: --l_sequence_number1 := getnext_seq_num(l_sequence_number);
3532: -- for bug 10420127
3533: hr_utility.trace('p_taxation_year '||tab_xml_employee(lAnnee));
3534: hr_utility.trace('l_sequence_number '||l_sequence_number);
3535: if (tab_xml_employee(lAnnee) >= '2010') then
3536: hr_utility.trace('p_taxation_year is greater than or equal 2010');
3537: l_sequence_number := substr(l_sequence_number,1,3)||
3530: l_sequence_number := get_slip_seq_no('|',lv_rl1_seq_no,1);
3531: --l_sequence_number1 := getnext_seq_num(l_sequence_number);
3532: -- for bug 10420127
3533: hr_utility.trace('p_taxation_year '||tab_xml_employee(lAnnee));
3534: hr_utility.trace('l_sequence_number '||l_sequence_number);
3535: if (tab_xml_employee(lAnnee) >= '2010') then
3536: hr_utility.trace('p_taxation_year is greater than or equal 2010');
3537: l_sequence_number := substr(l_sequence_number,1,3)||
3538: '-'||substr(l_sequence_number,4,3)||
3532: -- for bug 10420127
3533: hr_utility.trace('p_taxation_year '||tab_xml_employee(lAnnee));
3534: hr_utility.trace('l_sequence_number '||l_sequence_number);
3535: if (tab_xml_employee(lAnnee) >= '2010') then
3536: hr_utility.trace('p_taxation_year is greater than or equal 2010');
3537: l_sequence_number := substr(l_sequence_number,1,3)||
3538: '-'||substr(l_sequence_number,4,3)||
3539: '-'||substr(l_sequence_number,7);
3540: hr_utility.trace('l_sequence_number '||l_sequence_number);
3536: hr_utility.trace('p_taxation_year is greater than or equal 2010');
3537: l_sequence_number := substr(l_sequence_number,1,3)||
3538: '-'||substr(l_sequence_number,4,3)||
3539: '-'||substr(l_sequence_number,7);
3540: hr_utility.trace('l_sequence_number '||l_sequence_number);
3541: end if;
3542:
3543: /* added for 5850067*/
3544:
3862: if l_k > ln_no_fi_per_slip then
3863: l_k :=1;
3864: l_m := 1;
3865: l_data_page_count := l_data_page_count+1; /*11066853 */
3866: hr_utility.trace('l_data_page_count2-'||l_data_page_count);
3867: end if;
3868: if l_m = 1 then
3869: /*bug 14701466,13564765 starts here sbachu */
3870: /*if to_number(tab_xml_employee(lAnnee)) >= 2012 then
3932: /*11066853 - End */
3933: l_final_xml_string := l_final_xml_string||'
3934: ||EOL||''||EOL;
3935:
3936: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3933: l_final_xml_string := l_final_xml_string||'
3934: ||EOL||''||EOL;
3935:
3936: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3934: ||EOL||''||EOL;
3935:
3936: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3942: hr_utility.trace('final l_final_xml_string ='|| l_final_xml_string);
3935:
3936: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3942: hr_utility.trace('final l_final_xml_string ='|| l_final_xml_string);
3943:
3936: hr_utility.trace('final l_final_xml_string1 ='|| l_final_xml_string1);
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3942: hr_utility.trace('final l_final_xml_string ='|| l_final_xml_string);
3943:
3944: if trim(called_from) = 'xml_employee_record' then
3937: hr_utility.trace('final l_final_xml_string2 ='|| l_final_xml_string2);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3942: hr_utility.trace('final l_final_xml_string ='|| l_final_xml_string);
3943:
3944: if trim(called_from) = 'xml_employee_record' then
3945: pay_core_files.write_to_magtape_lob(l_final_xml_string1);
3938: hr_utility.trace('final l_final_xml_string3 ='|| l_final_xml_string3);
3939: hr_utility.trace('final l_final_xml_string4 ='|| l_final_xml_string4);
3940: hr_utility.trace('final l_final_xml_string5 ='|| l_final_xml_string5);
3941: hr_utility.trace('final l_final_xml_string6 ='|| l_final_xml_string6);
3942: hr_utility.trace('final l_final_xml_string ='|| l_final_xml_string);
3943:
3944: if trim(called_from) = 'xml_employee_record' then
3945: pay_core_files.write_to_magtape_lob(l_final_xml_string1);
3946: pay_core_files.write_to_magtape_lob(l_final_xml_string2);
3983: l_temp_blob := clob_to_blob(l_final_xml_string);
3984: dbms_lob.append(p_xml_blob,l_temp_blob);
3985:
3986: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3987: hr_utility.trace('Closing p_xml_blob' );
3988: dbms_lob.close(p_xml_blob);
3989: hr_utility.trace('closed p_xml_blob');
3990: END IF;
3991: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3985:
3986: IF dbms_lob.ISOPEN(p_xml_blob)=1 THEN
3987: hr_utility.trace('Closing p_xml_blob' );
3988: dbms_lob.close(p_xml_blob);
3989: hr_utility.trace('closed p_xml_blob');
3990: END IF;
3991: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3992: hr_utility.trace('Closing l_temp_blob' );
3993: dbms_lob.close(l_temp_blob);
3988: dbms_lob.close(p_xml_blob);
3989: hr_utility.trace('closed p_xml_blob');
3990: END IF;
3991: IF dbms_lob.ISOPEN(l_temp_blob)=1 THEN
3992: hr_utility.trace('Closing l_temp_blob' );
3993: dbms_lob.close(l_temp_blob);
3994: END IF;
3995: end if; -- called_from
3996: end if; /* end if of if l_neg_balance_exists */
3995: end if; -- called_from
3996: end if; /* end if of if l_neg_balance_exists */
3997: else
3998:
3999: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
4000: tab_employee(lH_CotisationRQAP));
4001: /*bug 14701466 sbachu starts here*/
4002:
4003: lv_ident_addr_string := '
4131:
4132: end if;
4133: END IF; /* if l_status = 'Failed' */
4134: /*bug 14701466 sbachu ends here*/
4135: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
4136: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4137: end if; /*if p_rep_type = 'RL1PAPERPDF' or p_rep_type = 'PAYCARL1AMPDF' or p_rep_type = 'PAYCARL1CLPDF' */
4138: hr_utility.trace('End of fetch_rl1_xml');
4139: END;
4134: /*bug 14701466 sbachu ends here*/
4135: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
4136: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4137: end if; /*if p_rep_type = 'RL1PAPERPDF' or p_rep_type = 'PAYCARL1AMPDF' or p_rep_type = 'PAYCARL1CLPDF' */
4138: hr_utility.trace('End of fetch_rl1_xml');
4139: END;
4140: exception
4141: when others then
4142: hr_utility.trace('sqleerm ' || SQLERRM);
4138: hr_utility.trace('End of fetch_rl1_xml');
4139: END;
4140: exception
4141: when others then
4142: hr_utility.trace('sqleerm ' || SQLERRM);
4143: raise;
4144: END fetch_rl1_xml;
4145:
4146: /*========================================================*/
4157: BEGIN
4158:
4159: l_final_xml_string := '
4160:
4161: hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
4162: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4163:
4164:
4165: END;
4201: l_address_end_tag varchar2(10);
4202:
4203: BEGIN
4204:
4205: hr_utility.trace('XML Employer');
4206:
4207: SELECT
4208: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4209: INTO EOL
4226: l_taxation_year
4227: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
4228: l_context1 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
4229:
4230: hr_utility.trace ('l_cvontext1 ='||l_context1);
4231:
4232: tab_employer(lAnnee) := '
4233: tab_employer(lNbReleves) := '
4234:
4247: 'CAEOY_RL1_EMPLOYER_NAME');
4248:
4249: tab_employer(lNom1) := '
4250: convert_special_char(substr(l_employer_name,1,30)) || '
4251: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4248:
4249: tab_employer(lNom1) := '
4250: convert_special_char(substr(l_employer_name,1,30)) || '
4251: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4249: tab_employer(lNom1) := '
4250: convert_special_char(substr(l_employer_name,1,30)) || '
4251: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4257: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4250: convert_special_char(substr(l_employer_name,1,30)) || '' || EOL;
4251: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4257: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4258:
4251: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4257: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4258:
4259: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4252: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4257: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4258:
4259: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4260: tab_employer(lNom2) := '
4253: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4254: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
4255: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
4256: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
4257: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4258:
4259: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
4260: tab_employer(lNom2) := '
4261: convert_special_char(substr(l_employer_name,31,30)) || '
4261: convert_special_char(substr(l_employer_name,31,30)) || '' || EOL;
4262: ELSE
4263: tab_employer(lNom2) := NULL;
4264: END IF;
4265: hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
4266:
4267: -- Address Line 1
4268:
4269: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4284: l_address_begin_tag := '
4285:
4286: tab_employer(lLigne1) := '
4287: convert_special_char(substr(l_address_line,1,30)) || '
4288: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4289:
4290:
4291: -- Address Line 2
4292:
4298: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
4299: ELSE
4300: tab_employer(lLigne2) := NULL;
4301: END IF;
4302: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4303:
4304: -- Ville (City)
4305:
4306: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4310: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
4311: ELSE
4312: tab_employer(lVille) := NULL;
4313: END IF;
4314: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4315:
4316: -- Province
4317:
4318: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4324: l_address_line),1,20) || '' || EOL;
4325: ELSE
4326: tab_employer(lProvince) := NULL;
4327: END IF;
4328: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4329:
4330: -- Postal Code
4331:
4332: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4337: substr(replace(l_address_line,' '),1,6) || '' || EOL;
4338: ELSE
4339: tab_employer(lCodePostal) := NULL;
4340: END IF;
4341: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4342:
4343: l_address_end_tag := '';
4344:
4345: END IF;
4361: l_address_end_tag || EOL || '' || EOL ||
4362: '' || EOL ||
4363: '' || EOL;
4364:
4365: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4366: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4367: END;
4368: END xml_employer_record;
4369:
4375: DECLARE
4376: l_final_xml_string VARCHAR2(32000);
4377:
4378: BEGIN
4379: hr_utility.trace('report ends here..closing RL1PAPER tag');
4380: l_final_xml_string := '';
4381: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4382:
4383: END;
4389: DECLARE
4390: l_final_xml_string VARCHAR2(32000);
4391:
4392: BEGIN
4393: --hr_utility.trace_on(null,'SATI');
4394: hr_utility.trace('inside xml_rl1_report_start');
4395: l_final_xml_string := '
4396: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4397:
4390: l_final_xml_string VARCHAR2(32000);
4391:
4392: BEGIN
4393: --hr_utility.trace_on(null,'SATI');
4394: hr_utility.trace('inside xml_rl1_report_start');
4395: l_final_xml_string := '
4396: pay_core_files.write_to_magtape_lob(l_final_xml_string);
4397:
4398: END;
4491: l_boxo3 :=NULL;
4492:
4493: l_see_attached := hr_general.decode_lookup('PAY_CA_LABELS'
4494: ,'SEE_ATTACHED');
4495: hr_utility.trace('l_see_attached = '||l_see_attached);
4496: SELECT
4497: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4498: INTO EOL
4499: from dual;
4501: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
4502: open c_get_language(p_assgn_id);
4503: fetch c_get_language into l_person_lang;
4504: close c_get_language;
4505: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4506: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4507: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4508: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4509: loop
4502: open c_get_language(p_assgn_id);
4503: fetch c_get_language into l_person_lang;
4504: close c_get_language;
4505: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4506: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4507: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4508: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4509: loop
4510: i:=i+1;
4503: fetch c_get_language into l_person_lang;
4504: close c_get_language;
4505: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
4506: hr_utility.trace(' l_person_lang ='|| l_person_lang);
4507: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
4508: for rec in cur_ftnt(l_cpp_withheld,l_person_lang,p_arch_assact_id)
4509: loop
4510: i:=i+1;
4511: t_footnote(i) := rec;
4517: t_boxo(i) :=rec;
4518: end loop;
4519: l_count_boxo:=t_boxo.count;
4520: l_count := t_footnote.count;
4521: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
4522: hr_utility.trace(' l_count ='|| l_count);
4523: if l_count > 1 then --more than 1 footnote
4524: p_footnote_boxo1 :='
4525: p_footnote_boxo2 :='
4518: end loop;
4519: l_count_boxo:=t_boxo.count;
4520: l_count := t_footnote.count;
4521: hr_utility.trace(' l_count_boxo ='|| l_count_boxo);
4522: hr_utility.trace(' l_count ='|| l_count);
4523: if l_count > 1 then --more than 1 footnote
4524: p_footnote_boxo1 :='
4525: p_footnote_boxo2 :='
4526: p_footnote_boxo3 :='
4544: l_footnote2 :='
4545: ||'
4546: l_footnote3 :='
4547: ||'
4548: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4549: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4550: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4551:
4552: -- The following code is commented with reference to Bug 12374647
4545: ||'
4546: l_footnote3 :='
4547: ||'
4548: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4549: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4550: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4551:
4552: -- The following code is commented with reference to Bug 12374647
4553: /*if (l_count_boxo=1) then
4546: l_footnote3 :='
4547: ||'
4548: hr_utility.trace(' l_footnote1 ='|| l_footnote1);
4549: hr_utility.trace(' l_footnote2 ='|| l_footnote2);
4550: hr_utility.trace(' l_footnote3 ='|| l_footnote3);
4551:
4552: -- The following code is commented with reference to Bug 12374647
4553: /*if (l_count_boxo=1) then
4554: l_boxo1 :='
4563: p_footnote_boxo1 :=l_footnote1||l_boxo1;
4564: p_footnote_boxo2 :=l_footnote2||l_boxo2;
4565: p_footnote_boxo3 :=l_footnote3||l_boxo3;
4566: elsif l_count = 0 and l_count_boxo < 4 and l_count_boxo > 1 then --Bug 12374647
4567: hr_utility.trace('boxo count less than 4');
4568: for k in 1.. l_count_boxo
4569: loop
4570: l_boxo1 :=l_boxo1||'
4571: ||'
4574: l_boxo3 :=l_boxo3||'
4575: ||'
4576:
4577: end loop;
4578: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4579: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4580: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4581:
4582: p_footnote_boxo1 :=l_boxo1;
4575: ||'
4576:
4577: end loop;
4578: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4579: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4580: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4581:
4582: p_footnote_boxo1 :=l_boxo1;
4583: p_footnote_boxo2 :=l_boxo2;
4576:
4577: end loop;
4578: hr_utility.trace(' l_boxo1 ='|| l_boxo1);
4579: hr_utility.trace(' l_boxo2 ='|| l_boxo2);
4580: hr_utility.trace(' l_boxo3 ='|| l_boxo3);
4581:
4582: p_footnote_boxo1 :=l_boxo1;
4583: p_footnote_boxo2 :=l_boxo2;
4584: p_footnote_boxo3 :=l_boxo3;
4588: p_footnote_boxo2 :='
4589: p_footnote_boxo3 :='
4590: end if;
4591:
4592: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4593: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4594: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4595:
4596: end xml_footnote_boxo;
4589: p_footnote_boxo3 :='
4590: end if;
4591:
4592: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4593: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4594: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4595:
4596: end xml_footnote_boxo;
4597:
4590: end if;
4591:
4592: hr_utility.trace(' p_footnote_boxo1 ='|| p_footnote_boxo1);
4593: hr_utility.trace(' p_footnote_boxo2 ='|| p_footnote_boxo2);
4594: hr_utility.trace(' p_footnote_boxo3 ='|| p_footnote_boxo3);
4595:
4596: end xml_footnote_boxo;
4597:
4598: PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
4633: l_quebec_bn varchar2(20);
4634:
4635: BEGIN
4636:
4637: hr_utility.trace('XML Employer');
4638:
4639: SELECT
4640: fnd_global.local_chr(13) || fnd_global.local_chr(10)
4641: INTO EOL
4654: open c_get_arch_pay_actid;
4655: fetch c_get_arch_pay_actid
4656: into l_context1;
4657: close c_get_arch_pay_actid;
4658: hr_utility.trace ('l_cvontext1 ='||l_context1);
4659:
4660: l_quebec_bn := pay_ca_archive_utils.get_archive_value
4661: (l_context1,'CAEOY_RL1_QUEBEC_BN');
4662: tab_employer(lNoId) := substr(l_quebec_bn,1,10); -- employer id
4664: l_employer_name := pay_ca_archive_utils.get_archive_value(l_context1,
4665: 'CAEOY_RL1_EMPLOYER_NAME');
4666:
4667: tab_employer(lNom1) := convert_special_char(l_employer_name);
4668: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4669: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4670:
4671: -- Address Line 1
4672: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4665: 'CAEOY_RL1_EMPLOYER_NAME');
4666:
4667: tab_employer(lNom1) := convert_special_char(l_employer_name);
4668: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
4669: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
4670:
4671: -- Address Line 1
4672: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4673: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4671: -- Address Line 1
4672: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4673: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE1');
4674: tab_employer(lLigne1) := convert_special_char(substr(l_address_line,1,40));
4675: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
4676:
4677: -- Address Line 2
4678: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4679: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4677: -- Address Line 2
4678: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4679: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE2');
4680: tab_employer(lLigne2) := convert_special_char(substr(l_address_line,1,40));
4681: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
4682:
4683: -- Address Line 3
4684: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4685: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
4683: -- Address Line 3
4684: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4685: 'CAEOY_RL1_EMPLOYER_ADDRESS_LINE3');
4686: tab_employer(lLigne3) := convert_special_char(substr(l_address_line,1,40));
4687: hr_utility.trace('tab_employer(lLigne3) = ' || tab_employer(lLigne3));
4688:
4689: if(tab_employer(lLigne2) is not null or tab_employer(lLigne3) is not null) then
4690: tab_employer(lLigne2) := tab_employer(lLigne2) ||' '|| tab_employer(lLigne3) ; /*******/
4691: end if;
4693: -- Ville (City)
4694: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4695: 'CAEOY_RL1_EMPLOYER_CITY');
4696: tab_employer(lVille) := convert_special_char(substr(l_address_line,1,30));
4697: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
4698:
4699: -- Province
4700:
4701: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4701: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4702: 'CAEOY_RL1_EMPLOYER_PROVINCE');
4703:
4704: tab_employer(lProvince) :=l_address_line;
4705: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
4706:
4707: --Country
4708: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4709: 'CAEOY_RL1_EMPLOYER_COUNTRY');
4707: --Country
4708: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4709: 'CAEOY_RL1_EMPLOYER_COUNTRY');
4710: tab_employer(lCountry) :=l_address_line;
4711: hr_utility.trace('tab_employer(lCountry) = ' || tab_employer(lCountry));
4712:
4713: -- Postal Code
4714: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
4715: 'CAEOY_RL1_EMPLOYER_POSTAL_CODE');
4721: tab_employer(lCountry) ||' '||
4722: substr(tab_employer(lCodePostal),1,3)||' '||
4723: substr(tab_employer(lCodePostal),4,3);
4724:
4725: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
4726: for l_index in 1..6 loop
4727: l_final_xml_string := '
4728:
4729: /*********************************************/
4763: end if;
4764: ******************************************************/
4765:
4766:
4767: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
4768: if l_index=1 then
4769: p_emplyr_final1 := l_final_xml_string;
4770: end if;
4771: if l_index=2 then