[Home] [Help]
29: p_business_group_id IN OUT NOCOPY NUMBER,
30: p_legislative_parameters IN OUT NOCOPY VARCHAR2
31: ) IS
32: BEGIN
33: --hr_utility.trace_on('Y','RL1MAG');
34: hr_utility.set_location('pay_ca_rl1_can_mag.get_report_parameters', 10);
35:
36: SELECT ppa.start_date,
37: ppa.effective_date,
30: p_legislative_parameters IN OUT NOCOPY VARCHAR2
31: ) IS
32: BEGIN
33: --hr_utility.trace_on('Y','RL1MAG');
34: hr_utility.set_location('pay_ca_rl1_can_mag.get_report_parameters', 10);
35:
36: SELECT ppa.start_date,
37: ppa.effective_date,
38: ppa.business_group_id,
45: p_legislative_parameters
46: FROM pay_payroll_actions ppa
47: WHERE payroll_action_id = p_pactid;
48:
49: hr_utility.set_location('pay_ca_rl1_can_mag.get_report_parameters', 20);
50:
51: END get_report_parameters;
52:
53: FUNCTION convert_special_char( p_data varchar2)
184:
185: l_transmitter_org_id := pay_ca_rl1_can_mag.get_parameter('TRANSMITTER_PRE',
186: l_legislative_parameters);
187:
188: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
189: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
190: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
191: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
192:
185: l_transmitter_org_id := pay_ca_rl1_can_mag.get_parameter('TRANSMITTER_PRE',
186: l_legislative_parameters);
187:
188: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
189: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
190: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
191: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
192:
193: l_mag_pactid := pay_ca_rl1_can_mag.get_parameter('PAY_ACT',
186: l_legislative_parameters);
187:
188: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
189: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
190: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
191: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
192:
193: l_mag_pactid := pay_ca_rl1_can_mag.get_parameter('PAY_ACT',
194: l_legislative_parameters);
187:
188: hr_utility.trace('l_transmitter_org_id = ' || to_char(l_transmitter_org_id));
189: hr_utility.trace('p_bg_id = ' || to_char(p_bg_id));
190: hr_utility.trace('p_payroll_action_id = ' || to_char(p_payroll_action_id));
191: hr_utility.trace('p_effective_date = ' || to_char(p_effective_date));
192:
193: l_mag_pactid := pay_ca_rl1_can_mag.get_parameter('PAY_ACT',
194: l_legislative_parameters);
195:
197: FETCH c_ppa_report_type
198: INTO l_locked_report_type;
199: CLOSE c_ppa_report_type;
200:
201: hr_utility.trace('l_locked_report_type = ' || l_locked_report_type);
202:
203: OPEN cur_arch_pactid(l_transmitter_org_id, l_locked_report_type);
204: FETCH cur_arch_pactid
205: INTO l_arch_pactid;
208: l_transmitter_number := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NUMBER');
209: l_transmitter_name := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_NAME');
210: BEGIN
211:
212: hr_utility.trace('l_transmitter_number = ' || l_transmitter_number);
213: SELECT substr(l_transmitter_number,1,2)
214: INTO dummy1
215: FROM dual;
216:
224: FROM dual;
225:
226: EXCEPTION
227: WHEN INVALID_NUMBER THEN
228: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
229: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
230: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
231: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
232: hr_utility.raise_error;
225:
226: EXCEPTION
227: WHEN INVALID_NUMBER THEN
228: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
229: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
230: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
231: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
232: hr_utility.raise_error;
233: END;
228: hr_utility.set_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER');
229: hr_utility.set_message_token('PRE_NAME',l_transmitter_name);
230: pay_core_utils.push_message(800,'PAY_CA_RL1_INVALID_TRANSMITTER','P');
231: pay_core_utils.push_token('PRE_NAME',l_transmitter_name);
232: hr_utility.raise_error;
233: END;
234:
235: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
236:
233: END;
234:
235: l_type_of_package := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_PACKAGE_TYPE');
236:
237: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
238:
239: IF l_type_of_package IS NULL THEN
240: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
241: hr_utility.raise_error;
237: hr_utility.trace('l_type_of_package = ' || l_type_of_package);
238:
239: IF l_type_of_package IS NULL THEN
240: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TYPE_OF_PKG','P');
241: hr_utility.raise_error;
242: END IF;
243:
244: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
245: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
241: hr_utility.raise_error;
242: END IF;
243:
244: l_source_of_slips := get_arch_val(l_arch_pactid,'CAEOY_RL1_SOURCE_OF_SLIPS');
245: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
246:
247: IF l_source_of_slips IS NULL THEN
248: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
249: hr_utility.raise_error;
245: hr_utility.trace('l_source_of_slips = ' || l_source_of_slips);
246:
247: IF l_source_of_slips IS NULL THEN
248: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_RLSLIP_SRC','P');
249: hr_utility.raise_error;
250: END IF;
251:
252: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
253: hr_utility.trace('l_address_line1 = ' || l_address_line1);
249: hr_utility.raise_error;
250: END IF;
251:
252: l_address_line1 := get_arch_val(l_arch_pactid,'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
253: hr_utility.trace('l_address_line1 = ' || l_address_line1);
254:
255: IF l_address_line1 IS NULL THEN
256: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
257: hr_utility.raise_error;
253: hr_utility.trace('l_address_line1 = ' || l_address_line1);
254:
255: IF l_address_line1 IS NULL THEN
256: pay_core_utils.push_message(800,'PAY_CA_RL1_MISSING_TRNMTR_ADDR','P');
257: hr_utility.raise_error;
258: END IF;
259:
260: END;
261:
319: ,'CAEOY_CPP_EE_WITHHELD_PER_YTD');
320: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
321: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
322:
323: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
324: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
325: g_further_info_list.delete;
326: for rec in cur_boxo
327: loop
320: l_cpp_taxable := pay_ca_archive_utils.get_archive_value(p_arch_assact_id
321: ,'CAEOY_CPP_EE_TAXABLE_PER_YTD');
322:
323: hr_utility.trace(' l_cpp_withheld ='|| l_cpp_withheld);
324: hr_utility.trace(' p_arch_assact_id ='|| p_arch_assact_id);
325: g_further_info_list.delete;
326: for rec in cur_boxo
327: loop
328: i:=i+1;
325: g_further_info_list.delete;
326: for rec in cur_boxo
327: loop
328: i:=i+1;
329: hr_utility.trace('i '||i);
330: hr_utility.trace('rec.code '||rec.code);
331: hr_utility.trace('rec.value '||rec.value);
332: g_further_info_list(i).code := rec.code;
333: g_further_info_list(i).amount := rec.value;
326: for rec in cur_boxo
327: loop
328: i:=i+1;
329: hr_utility.trace('i '||i);
330: hr_utility.trace('rec.code '||rec.code);
331: hr_utility.trace('rec.value '||rec.value);
332: g_further_info_list(i).code := rec.code;
333: g_further_info_list(i).amount := rec.value;
334: end loop;
327: loop
328: i:=i+1;
329: hr_utility.trace('i '||i);
330: hr_utility.trace('rec.code '||rec.code);
331: hr_utility.trace('rec.value '||rec.value);
332: g_further_info_list(i).code := rec.code;
333: g_further_info_list(i).amount := rec.value;
334: end loop;
335:
335:
336: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
337: loop
338: i:=i+1;
339: hr_utility.trace('i '||i);
340: hr_utility.trace('rec.code '||rec.code);
341: hr_utility.trace('rec.value '||rec.value);
342: g_further_info_list(i).code := rec.code;
343: g_further_info_list(i).amount := rec.value;
336: for rec in cur_further_info(l_cpp_withheld,l_cpp_taxable)
337: loop
338: i:=i+1;
339: hr_utility.trace('i '||i);
340: hr_utility.trace('rec.code '||rec.code);
341: hr_utility.trace('rec.value '||rec.value);
342: g_further_info_list(i).code := rec.code;
343: g_further_info_list(i).amount := rec.value;
344: end loop;
337: loop
338: i:=i+1;
339: hr_utility.trace('i '||i);
340: hr_utility.trace('rec.code '||rec.code);
341: hr_utility.trace('rec.value '||rec.value);
342: g_further_info_list(i).code := rec.code;
343: g_further_info_list(i).amount := rec.value;
344: end loop;
345:
350: p_amount in out nocopy varchar2)
351: is
352: begin
353:
354: hr_utility.trace('p_index '||p_index);
355: if g_further_info_list.exists(p_index) then
356: p_code := g_further_info_list(p_index).code;
357: p_amount := g_further_info_list(p_index).amount;
358: else
383: p_business_group_id NUMBER;
384: p_report_type VARCHAR2(30);
385:
386: BEGIN
387: -- hr_utility.trace_on(null,'PDF');
388: hr_utility.set_location( 'pay_ca_rl1_can_mag.range_cursor', 10);
389:
390: p_sqlstr := 'select distinct paaf.person_id
391: from per_all_assignments_f paaf,
384: p_report_type VARCHAR2(30);
385:
386: BEGIN
387: -- hr_utility.trace_on(null,'PDF');
388: hr_utility.set_location( 'pay_ca_rl1_can_mag.range_cursor', 10);
389:
390: p_sqlstr := 'select distinct paaf.person_id
391: from per_all_assignments_f paaf,
392: pay_payroll_actions ppa,
401: nvl(pay_ca_rl1_can_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
402: order by paaf.person_id';
403: -- order by paaf.person_id clause added for bug 9211063
404:
405: hr_utility.set_location( 'pay_ca_rl1_can_mag.range_cursor',20);
406:
407: END range_cursor;
408:
409: -------------------------------------------------------------------------------
541:
542:
543: BEGIN
544:
545: -- hr_utility.trace_on('Y','RL1MAG');
546:
547: l_prev_payact := -1;
548: hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',10);
549:
544:
545: -- hr_utility.trace_on('Y','RL1MAG');
546:
547: l_prev_payact := -1;
548: hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',10);
549:
550: get_report_parameters(
551: p_pactid,
552: l_year_start,
560: l_business_group_id,
561: l_year_end);
562:
563:
564: hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',20);
565:
566: l_asg_set_id := to_number(pay_ca_rl1_can_mag.get_parameter('ASG_SET_ID',l_legislative_parameters));
567: hr_utility.trace('Assignment Set Id : '|| to_char(l_asg_set_id));
568:
563:
564: hr_utility.set_location( 'pay_ca_rl1_can_mag.create_assignement_act',20);
565:
566: l_asg_set_id := to_number(pay_ca_rl1_can_mag.get_parameter('ASG_SET_ID',l_legislative_parameters));
567: hr_utility.trace('Assignment Set Id : '|| to_char(l_asg_set_id));
568:
569: IF l_asg_set_id IS NOT NULL THEN
570: OPEN c_all_asg_in_asgset;
571: ELSE
571: ELSE
572: OPEN c_all_asg;
573: END IF;
574:
575: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 23);
576:
577: LOOP
578: IF l_asg_set_id IS NOT NULL THEN
579: FETCH c_all_asg_in_asgset INTO l_person_id,
582: l_effective_end_date,
583: l_assignment_action_id,
584: l_payroll_act;
585:
586: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 25);
587:
588: EXIT WHEN c_all_asg_in_asgset%NOTFOUND;
589: ELSE
590: FETCH c_all_asg INTO l_person_id,
593: l_effective_end_date,
594: l_assignment_action_id,
595: l_payroll_act;
596:
597: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 30);
598:
599: EXIT WHEN c_all_asg%NOTFOUND;
600: END IF;
601:
602: -- Added by ssmukher for validating the
603: -- Quebec Identification Number
604:
605: if l_prev_payact <> l_payroll_act then
606: hr_utility.trace('The payroll action id '||l_payroll_act);
607:
608: l_prev_payact := l_payroll_act;
609: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
610: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
608: l_prev_payact := l_payroll_act;
609: l_quebec_val := get_arch_val(l_payroll_act,'CAEOY_RL1_QUEBEC_BN');
610: l_quebec_name := get_arch_val(l_payroll_act,'CAEOY_RL1_EMPLOYER_NAME');
611:
612: hr_utility.trace('The Quebec Number is '||l_quebec_val);
613:
614: l_quebec_no := substr(l_quebec_val ,1,10);
615:
616: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
612: hr_utility.trace('The Quebec Number is '||l_quebec_val);
613:
614: l_quebec_no := substr(l_quebec_val ,1,10);
615:
616: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
617: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
618: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
619:
620: end if ;
613:
614: l_quebec_no := substr(l_quebec_val ,1,10);
615:
616: hr_utility.trace('First 10 digits of the QIN: '||l_quebec_no);
617: hr_utility.trace('l_quebec_name ='|| l_quebec_name);
618: l_return := validate_quebec_number(l_quebec_val,l_quebec_name);
619:
620: end if ;
621:
622:
623:
624: --Create the assignment action for the record
625:
626: hr_utility.trace('Assignment Fetched - ');
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
623:
624: --Create the assignment action for the record
625:
626: hr_utility.trace('Assignment Fetched - ');
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
624: --Create the assignment action for the record
625:
626: hr_utility.trace('Assignment Fetched - ');
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
632:
625:
626: hr_utility.trace('Assignment Fetched - ');
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
632:
633: SELECT pay_assignment_actions_s.nextval
626: hr_utility.trace('Assignment Fetched - ');
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
632:
633: SELECT pay_assignment_actions_s.nextval
634: INTO lockingactid
627: hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
628: hr_utility.trace('Person Id : '|| to_char(l_person_id));
629: hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
630: hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
631: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 40);
632:
633: SELECT pay_assignment_actions_s.nextval
634: INTO lockingactid
635: FROM dual;
633: SELECT pay_assignment_actions_s.nextval
634: INTO lockingactid
635: FROM dual;
636:
637: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 50);
638: hr_nonrun_asact.insact(lockingactid,
639: l_assignment_id,
640: p_pactid,
641: p_chunk,
640: p_pactid,
641: p_chunk,
642: l_tax_unit_id);
643:
644: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 60);
645:
646: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
647:
648: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
644: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 60);
645:
646: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
647:
648: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
649: hr_utility.trace('Interlock Created - ');
650: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
651: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
652: END LOOP;
645:
646: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
647:
648: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
649: hr_utility.trace('Interlock Created - ');
650: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
651: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
652: END LOOP;
653:
646: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
647:
648: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
649: hr_utility.trace('Interlock Created - ');
650: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
651: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
652: END LOOP;
653:
654: IF l_asg_set_id IS NOT NULL THEN
647:
648: hr_utility.set_location('pay_ca_rl1_can_mag.create_assignement_act', 70);
649: hr_utility.trace('Interlock Created - ');
650: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
651: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
652: END LOOP;
653:
654: IF l_asg_set_id IS NOT NULL THEN
655: CLOSE c_all_asg_in_asgset;
736: l_max := 7;
737: l_total := 0;
738:
739: l_act_chk_number := to_number(substr(p_quebec_no,10,1));
740: hr_utility.trace('here1');
741: if TRANSLATE(p_quebec_no,'0123456789','9999999999') = '9999999999RS9999' then
742: l_quebec := to_number(substr(p_quebec_no,1,9));
743: loop
744: if i > 9 then
766: l_chk_digit := 11 - l_modulus;
767: end if;
768:
769: if l_chk_digit <> l_act_chk_number then
770: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
771: hr_utility.set_message_token('PRE_NAME',p_qin_name);
772: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
773: pay_core_utils.push_token('PRE_NAME',p_qin_name);
774: hr_utility.raise_error;
767: end if;
768:
769: if l_chk_digit <> l_act_chk_number then
770: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
771: hr_utility.set_message_token('PRE_NAME',p_qin_name);
772: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
773: pay_core_utils.push_token('PRE_NAME',p_qin_name);
774: hr_utility.raise_error;
775: end if;
770: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
771: hr_utility.set_message_token('PRE_NAME',p_qin_name);
772: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
773: pay_core_utils.push_token('PRE_NAME',p_qin_name);
774: hr_utility.raise_error;
775: end if;
776: else
777:
778: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
774: hr_utility.raise_error;
775: end if;
776: else
777:
778: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
779: hr_utility.set_message_token('PRE_NAME',p_qin_name);
780: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
781: pay_core_utils.push_token('PRE_NAME',p_qin_name);
782: hr_utility.raise_error;
775: end if;
776: else
777:
778: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
779: hr_utility.set_message_token('PRE_NAME',p_qin_name);
780: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
781: pay_core_utils.push_token('PRE_NAME',p_qin_name);
782: hr_utility.raise_error;
783:
778: hr_utility.set_message(801,'PAY_74156_INCORRECT_QIN_INFO');
779: hr_utility.set_message_token('PRE_NAME',p_qin_name);
780: pay_core_utils.push_message(801,'PAY_74156_INCORRECT_QIN_INFO','P');
781: pay_core_utils.push_token('PRE_NAME',p_qin_name);
782: hr_utility.raise_error;
783:
784: end if;
785:
786: return l_chk_digit;
864: l_rl_package_type VARCHAR2(20);
865:
866: BEGIN
867:
868: hr_utility.trace('XML Transmitter');
869:
870: SELECT
871: fnd_global.local_chr(13) || fnd_global.local_chr(10)
872: INTO EOL
912: where int.locking_action_id = paa.assignment_action_id
913: AND paa1.assignment_action_id = int.locked_action_id
914: and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
915:
916: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
917: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
918: --hr_utility.trace('XML Transmitter: l_pre_id = ' || l_pre_id);
919:
920: -- Annee
913: AND paa1.assignment_action_id = int.locked_action_id
914: and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
915:
916: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
917: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
918: --hr_utility.trace('XML Transmitter: l_pre_id = ' || l_pre_id);
919:
920: -- Annee
921: tab_transmitter(lAnnee) := '
914: and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
915:
916: hr_utility.trace('XML Transmitter: l_taxation_year = ' || l_taxation_year);
917: hr_utility.trace('XML Transmitter: l_context1 = ' || to_char(l_context1));
918: --hr_utility.trace('XML Transmitter: l_pre_id = ' || l_pre_id);
919:
920: -- Annee
921: tab_transmitter(lAnnee) := '
922: hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
918: --hr_utility.trace('XML Transmitter: l_pre_id = ' || l_pre_id);
919:
920: -- Annee
921: tab_transmitter(lAnnee) := '
922: hr_utility.trace('tab_transmitter(lAnnee) = ' || tab_transmitter(lAnnee));
923:
924: -- TypeEnvoi
925: OPEN c_rl_package_type;
926: FETCH c_rl_package_type INTO l_rl_package_type;
927: CLOSE c_rl_package_type;
928:
929: tab_transmitter(lTypeEnvoi) := '
930:
931: hr_utility.trace('tab_transmitter(lTypeEnvoi) = ' ||
932: tab_transmitter(lTypeEnvoi));
933:
934: tab_transmitter(lProvenance) := '
935: pay_ca_archive_utils.get_archive_value(l_context1,
934: tab_transmitter(lProvenance) := '
935: pay_ca_archive_utils.get_archive_value(l_context1,
936: 'CAEOY_RL1_SOURCE_OF_SLIPS') || '
937:
938: hr_utility.trace('tab_transmitter(lProvenance) = ' || tab_transmitter(lProvenance));
939:
940: tab_transmitter(lNo) := '
941: pay_ca_archive_utils.get_archive_value(l_context1,
942: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
940: tab_transmitter(lNo) := '
941: pay_ca_archive_utils.get_archive_value(l_context1,
942: 'CAEOY_RL1_TRANSMITTER_NUMBER') || '
943:
944: hr_utility.trace('tab_transmitter(lNo) = ' || tab_transmitter(lNo));
945:
946: l_return := pay_ca_archive_utils.get_archive_value(l_context1,
947: 'CAEOY_RL1_TRANSMITTER_TYPE');
948: IF l_return IS NOT NULL THEN
950: ELSE
951: tab_transmitter(lType) := NULL;
952: END IF;
953:
954: hr_utility.trace('tab_transmitter(lType) = ' || tab_transmitter(lType));
955:
956: l_transmitter_name := pay_ca_archive_utils.get_archive_value(l_context1,
957: 'CAEOY_RL1_TRANSMITTER_NAME');
958:
958:
959: tab_transmitter(lNom1) := '
960: convert_special_char(substr(l_transmitter_name,1,30)) || '
961:
962: hr_utility.trace('tab_transmitter(lNom1) = ' || tab_transmitter(lNom1));
963:
964: l_return := substr(l_transmitter_name,31,30);
965: IF l_return IS NOT NULL THEN
966: tab_transmitter(lNom2) := '
967: ELSE
968: tab_transmitter(lNom2) := NULL;
969: END IF;
970:
971: hr_utility.trace('tab_transmitter(lNom2) = ' || tab_transmitter(lNom2));
972:
973: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
974: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE1');
975:
975:
976: tab_transmitter(lLigne1) := '
977: convert_special_char(substr(l_address_line,1,30)) || '
978:
979: hr_utility.trace('tab_transmitter(lLigne1) = ' || tab_transmitter(lLigne1));
980:
981: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
982: 'CAEOY_RL1_TRANSMITTER_ADDRESS_LINE2');
983:
987: ELSE
988: tab_transmitter(lLigne2) := NULL;
989: END IF;
990:
991: hr_utility.trace('tab_transmitter(lLigne2) = ' || tab_transmitter(lLigne2));
992:
993: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
994: 'CAEOY_RL1_TRANSMITTER_CITY');
995:
999: ELSE
1000: tab_transmitter(lVille) := NULL;
1001: END IF;
1002:
1003: hr_utility.trace('tab_transmitter(lVille) = ' || tab_transmitter(lVille));
1004:
1005: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1006: 'CAEOY_RL1_TRANSMITTER_PROVINCE');
1007:
1008: tab_transmitter(lProvince) := '
1009: SUBSTR(hr_general.decode_lookup(
1010: 'CA_PROVINCE',l_address_line),1,20) || '
1011:
1012: hr_utility.trace('tab_transmitter(lProvince) = ' || tab_transmitter(lProvince));
1013:
1014: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
1015: 'CAEOY_RL1_TRANSMITTER_POSTAL_CODE');
1016:
1016:
1017: tab_transmitter(lCodePostal) := '
1018: substr(replace(l_address_line,' '),1,6) || '
1019:
1020: hr_utility.trace('tab_transmitter(lCodePostal) = ' || tab_transmitter(lCodePostal));
1021:
1022: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1023: 'CAEOY_RL1_TECHNICAL_CONTACT_NAME');
1024:
1028: ELSE
1029: tab_transmitter(lNom) := NULL;
1030: END IF;
1031:
1032: hr_utility.trace('tab_transmitter(lNom) = ' || tab_transmitter(lNom));
1033:
1034: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1035: 'CAEOY_RL1_TECHNICAL_CONTACT_AREA_CODE');
1036:
1040: ELSE
1041: tab_transmitter(lIndRegional) := NULL;
1042: END IF;
1043:
1044: hr_utility.trace('tab_transmitter(lIndRegional) = ' || tab_transmitter(lIndRegional));
1045:
1046: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1047: 'CAEOY_RL1_TECHNICAL_CONTACT_PHONE');
1048: IF l_contact IS NOT NULL THEN
1050: tab_transmitter(lTel) := '
1051: ELSE
1052: tab_transmitter(lTel) := NULL;
1053: END IF;
1054: hr_utility.trace('tab_transmitter(lTel) = ' || tab_transmitter(lTel));
1055:
1056: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1057: 'CAEOY_RL1_TECHNICAL_CONTACT_EXTENSION');
1058:
1061: '' || EOL;
1062: ELSE
1063: tab_transmitter(lPosteTel) := NULL;
1064: END IF;
1065: hr_utility.trace('tab_transmitter(lPosteTel) = ' ||
1066: tab_transmitter(lPosteTel));
1067:
1068: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1069: 'CAEOY_RL1_TECHNICAL_CONTACT_LANGUAGE');
1082: convert_special_char(substr(l_contact,1,30)) || '' || EOL;
1083: ELSE
1084: tab_transmitter(lANom) := NULL;
1085: END IF;
1086: hr_utility.trace('tab_transmitter(lANom) = ' || tab_transmitter(lANom));
1087:
1088: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1089: 'CAEOY_RL1_ACCOUNTING_CONTACT_AREA_CODE');
1090:
1093: '' || EOL;
1094: ELSE
1095: tab_transmitter(lAIndRegional) := NULL;
1096: END IF;
1097: hr_utility.trace('tab_transmitter(lAIndRegional) = ' || tab_transmitter(lAIndRegional));
1098:
1099: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1100: 'CAEOY_RL1_ACCOUNTING_CONTACT_PHONE');
1101:
1104: tab_transmitter(lATel) := '
1105: ELSE
1106: tab_transmitter(lATel) := NULL;
1107: END IF;
1108: hr_utility.trace('tab_transmitter(lATel) = ' || tab_transmitter(lATel));
1109:
1110: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1111: 'CAEOY_RL1_ACCOUNTING_CONTACT_EXTENSION');
1112:
1115: '' || EOL;
1116: ELSE
1117: tab_transmitter(lAPosteTel) := NULL;
1118: END IF;
1119: hr_utility.trace('tab_transmitter(lAPosteTel) = ' ||
1120: tab_transmitter(lAPosteTel));
1121:
1122: l_contact := pay_ca_archive_utils.get_archive_value(l_context1,
1123: 'CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE');
1127: ELSE
1128: tab_transmitter(lALangue) := NULL;
1129: END IF;
1130:
1131: hr_utility.trace('tab_transmitter(lALangue) = ' || tab_transmitter(lALangue));
1132:
1133: -- For bug 6738509
1134: if(l_taxation_year = '2006') then
1135: l_certification_no := 'RQ-06-01-098';
1199: tab_transmitter(lALangue) || '' || EOL ||
1200: tab_transmitter(lNoConcepteur) ||
1201: '
1219: BEGIN
1220:
1221: l_final_xml_string := '';
1222:
1223: hr_utility.trace('end_of_file l_final_xml_string = '
1224: || l_final_xml_string );
1225: pay_core_files.write_to_magtape_lob(l_final_xml_string);
1226:
1227: END;
1546: l_original_slip_no varchar2(30);
1547: l_pre_id number;
1548:
1549: BEGIN
1550: -- hr_utility.trace_on(null,'PDF');
1551: hr_utility.trace('inside xml_employee_record');
1552:
1553: l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1554: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1547: l_pre_id number;
1548:
1549: BEGIN
1550: -- hr_utility.trace_on(null,'PDF');
1551: hr_utility.trace('inside xml_employee_record');
1552:
1553: l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1554: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1555:
1550: -- hr_utility.trace_on(null,'PDF');
1551: hr_utility.trace('inside xml_employee_record');
1552:
1553: l_payroll_actid := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'));
1554: hr_utility.trace('l_payroll_actid='||l_payroll_actid);
1555:
1556: SELECT ppa.report_type
1557: INTO l_rep_type
1558: from pay_payroll_actions ppa
1556: SELECT ppa.report_type
1557: INTO l_rep_type
1558: from pay_payroll_actions ppa
1559: where payroll_action_id=l_payroll_actid;
1560: hr_utility.trace('report_type='||l_rep_type);
1561:
1562: hr_utility.trace('XML Employee');
1563: l_status := 'Success';
1564: l_all_box_0 := TRUE;
1558: from pay_payroll_actions ppa
1559: where payroll_action_id=l_payroll_actid;
1560: hr_utility.trace('report_type='||l_rep_type);
1561:
1562: hr_utility.trace('XML Employee');
1563: l_status := 'Success';
1564: l_all_box_0 := TRUE;
1565: l_format_mask := '99999999999999990.99';
1566:
1664: lBoxO_CC := 96;
1665: --
1666: l_mag_asg_action_id := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'));
1667:
1668: hr_utility.trace('XML Employee: l_mag_asg_action_id = '
1669: || to_char(l_mag_asg_action_id));
1670:
1671: OPEN cur_parameters(l_mag_asg_action_id);
1672: FETCH cur_parameters
1677: l_province,
1678: l_pre_id;
1679: CLOSE cur_parameters;
1680:
1681: hr_utility.trace('XML Employee: l_arch_action_id = '
1682: || to_char(l_arch_action_id));
1683: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684: hr_utility.trace('XML Employee: l_date_earned = '
1685: || to_char(l_date_earned));
1679: CLOSE cur_parameters;
1680:
1681: hr_utility.trace('XML Employee: l_arch_action_id = '
1682: || to_char(l_arch_action_id));
1683: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684: hr_utility.trace('XML Employee: l_date_earned = '
1685: || to_char(l_date_earned));
1686: hr_utility.trace('XML Employee: l_province = ' || l_province);
1687: hr_utility.trace('XML Employee: l_pre_id = ' || to_char(l_pre_id));
1680:
1681: hr_utility.trace('XML Employee: l_arch_action_id = '
1682: || to_char(l_arch_action_id));
1683: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684: hr_utility.trace('XML Employee: l_date_earned = '
1685: || to_char(l_date_earned));
1686: hr_utility.trace('XML Employee: l_province = ' || l_province);
1687: hr_utility.trace('XML Employee: l_pre_id = ' || to_char(l_pre_id));
1688:
1682: || to_char(l_arch_action_id));
1683: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684: hr_utility.trace('XML Employee: l_date_earned = '
1685: || to_char(l_date_earned));
1686: hr_utility.trace('XML Employee: l_province = ' || l_province);
1687: hr_utility.trace('XML Employee: l_pre_id = ' || to_char(l_pre_id));
1688:
1689: l_taxation_year
1690: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1683: hr_utility.trace('XML Employee: l_asg_id = ' || to_char(l_asg_id));
1684: hr_utility.trace('XML Employee: l_date_earned = '
1685: || to_char(l_date_earned));
1686: hr_utility.trace('XML Employee: l_province = ' || l_province);
1687: hr_utility.trace('XML Employee: l_pre_id = ' || to_char(l_pre_id));
1688:
1689: l_taxation_year
1690: := pay_magtape_generic.get_parameter_value('REPORTING_YEAR');
1691:
1694: l_year := pay_ca_archive_utils.get_archive_value(l_arch_pay_actid, 'CAEOY_TAXATION_YEAR');
1695:
1696: --Annee
1697: tab_employee(lAnnee) := '
1698: hr_utility.trace('tab_employee(lAnnee) = ' || tab_employee(lAnnee));
1699:
1700: --NoReleve
1701: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1702: 'CAEOY_PERSON_ID'));
1700: --NoReleve
1701: l_person_id := to_number(pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1702: 'CAEOY_PERSON_ID'));
1703:
1704: hr_utility.trace('XML Employee: l_person_id = ' || to_char(l_person_id));
1705: /*Bug 13564765 starts here*/
1706: l_k := 0;
1707: lv_org_slip_no := NULL;
1708: OPEN cur_rl1_slip_no(l_person_id, l_taxation_year, l_pre_id);
1729: '' || EOL;
1730: tab_xml_employee(lNoReleve) := l_return; --
1731: END IF;
1732: /*Bug 13564765 ends here*/
1733: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve)|| lv_org_slip_no);
1734: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1735:
1736: -- NAS
1737: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1730: tab_xml_employee(lNoReleve) := l_return; --
1731: END IF;
1732: /*Bug 13564765 ends here*/
1733: hr_utility.trace('tab_employee(lNoReleve) = ' || tab_employee(lNoReleve)|| lv_org_slip_no);
1734: hr_utility.trace('tab_xml_employee(lNoReleve) = ' || tab_xml_employee(lNoReleve)); --
1735:
1736: -- NAS
1737: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1738: 'CAEOY_EMPLOYEE_SIN');
1746: l_msg_code := 'SIN';
1747: tab_employee(lNAS) := NULL;
1748: tab_xml_employee(lNAS) := NULL; --
1749: END IF;
1750: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1751: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1752:
1753: -- No
1754: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1747: tab_employee(lNAS) := NULL;
1748: tab_xml_employee(lNAS) := NULL; --
1749: END IF;
1750: hr_utility.trace('tab_employee(lNAS) = ' || tab_employee(lNAS));
1751: hr_utility.trace('tab_xml_employee(lNAS) = ' || tab_xml_employee(lNAS));
1752:
1753: -- No
1754: l_return := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1755: 'CAEOY_EMPLOYEE_NUMBER');
1759: ELSE
1760: tab_employee(lNo) := NULL;
1761: tab_xml_employee(lNo) := NULL; --
1762: END IF;
1763: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1764: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1765:
1766: -- NomFamille
1767: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1760: tab_employee(lNo) := NULL;
1761: tab_xml_employee(lNo) := NULL; --
1762: END IF;
1763: hr_utility.trace('tab_employee(lNo) = ' || tab_employee(lNo));
1764: hr_utility.trace('tab_xml_employee(lNo) = ' || tab_xml_employee(lNo)); --
1765:
1766: -- NomFamille
1767: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1768: 'CAEOY_EMPLOYEE_LAST_NAME');
1768: 'CAEOY_EMPLOYEE_LAST_NAME');
1769: tab_employee(lNomFamille) := '
1770: convert_special_char(substr(l_name,1,30)) || '
1771: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1772: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1773: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1774:
1775: -- Prenom
1776: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1769: tab_employee(lNomFamille) := '
1770: convert_special_char(substr(l_name,1,30)) || '
1771: tab_xml_employee(lNomFamille) := convert_special_char(substr(l_name,1,20)); --
1772: hr_utility.trace('tab_employee(lNomFamille) = ' || tab_employee(lNomFamille));
1773: hr_utility.trace('tab_xml_employee(lNomFamille) = ' || tab_xml_employee(lNomFamille)); --
1774:
1775: -- Prenom
1776: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1777: 'CAEOY_EMPLOYEE_FIRST_NAME');
1785: l_msg_code := 'MISSING_EMP_FIRST_NAME';
1786: tab_employee(lPrenom) := NULL;
1787: tab_xml_employee(lPrenom) := NULL ; --
1788: END IF;
1789: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1790: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1791:
1792: -- Initiale
1793: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1786: tab_employee(lPrenom) := NULL;
1787: tab_xml_employee(lPrenom) := NULL ; --
1788: END IF;
1789: hr_utility.trace('tab_employee(lPrenom) = ' || tab_employee(lPrenom));
1790: hr_utility.trace('tab_xml_employee(lPrenom) = ' || tab_xml_employee(lPrenom)); --
1791:
1792: -- Initiale
1793: l_name := pay_ca_archive_utils.get_archive_value(l_arch_action_id,
1794: 'CAEOY_EMPLOYEE_INITIAL');
1800: ELSE
1801: tab_employee(lInitiale) := NULL;
1802: tab_xml_employee(lInitiale) := NULL; --
1803: END IF;
1804: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1805: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1806:
1807: l_return := pay_ca_emp_address_dtls.get_emp_address(
1808: l_person_id,
1801: tab_employee(lInitiale) := NULL;
1802: tab_xml_employee(lInitiale) := NULL; --
1803: END IF;
1804: hr_utility.trace('tab_employee(lInitiale) = ' || tab_employee(lInitiale));
1805: hr_utility.trace('tab_xml_employee(lInitiale) = ' || tab_xml_employee(lInitiale)); --
1806:
1807: l_return := pay_ca_emp_address_dtls.get_emp_address(
1808: l_person_id,
1809: l_address_line1,
1817: -- If Address line 1 is NULL or ' ' then the employee is missing
1818: -- address information - as line 1 is mandatory in the Address form.
1819: -- Need to check data by SS transaction /API.
1820:
1821: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1822: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1823: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1824: hr_utility.trace('l_postal_code = ' || l_postal_code);
1825:
1818: -- address information - as line 1 is mandatory in the Address form.
1819: -- Need to check data by SS transaction /API.
1820:
1821: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1822: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1823: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1824: hr_utility.trace('l_postal_code = ' || l_postal_code);
1825:
1826: -- Address Line 1
1819: -- Need to check data by SS transaction /API.
1820:
1821: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1822: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1823: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1824: hr_utility.trace('l_postal_code = ' || l_postal_code);
1825:
1826: -- Address Line 1
1827: IF l_address_line1 IS NULL OR
1820:
1821: hr_utility.trace('l_person_id = ' || to_char(l_person_id));
1822: hr_utility.trace('l_address_line1 = ' || l_address_line1);
1823: hr_utility.trace('l_address_line2 = ' || l_address_line2);
1824: hr_utility.trace('l_postal_code = ' || l_postal_code);
1825:
1826: -- Address Line 1
1827: IF l_address_line1 IS NULL OR
1828: l_address_line1 = ' ' THEN
1844: l_addr_begin_tag := '
1845:
1846: tab_employee(lLigne1) := '
1847: convert_special_char(substr(l_address_line1,1,30)) || '
1848: hr_utility.trace('tab_employee(lLigne1) = ' || tab_employee(lLigne1));
1849:
1850: -- Address Line 2
1851: IF ((l_address_line2 IS NULL OR
1852: l_address_line2 <> ' ') OR
1857: convert_special_char(substr(l_combined_addr,1,30)) || '' || EOL;
1858: ELSE
1859: tab_employee(lLigne2) := NULL;
1860: END IF;
1861: hr_utility.trace('tab_employee(lLigne2) = ' || tab_employee(lLigne2));
1862:
1863: -- Ville (City)
1864: IF l_city IS NULL OR
1865: l_city <> ' ' THEN
1867: substr(l_city,1,30) || '' || EOL;
1868: ELSE
1869: tab_employee(lVille) := NULL;
1870: END IF;
1871: hr_utility.trace('tab_employee(lVille) = ' || tab_employee(lVille));
1872:
1873: -- Province
1874: IF l_emp_province IS NULL OR
1875: l_emp_province <> ' ' THEN
1884: END IF;
1885: ELSE
1886: tab_employee(lProvince) := NULL;
1887: END IF;
1888: hr_utility.trace('tab_employee(lProvince) = ' || tab_employee(lProvince));
1889:
1890: -- Postal Code
1891: IF l_postal_code IS NULL OR
1892: l_postal_code <> ' ' THEN
1894: substr(replace(l_postal_code,' '),1,6) || '' || EOL;
1895: ELSE
1896: tab_employee(lCodePostal) := NULL;
1897: END IF;
1898: hr_utility.trace('tab_employee(lCodePostal) = ' || tab_employee(lCodePostal));
1899: l_addr_end_tag := '';
1900:
1901: END IF;
1902:
1928: ELSE
1929: tab_employee(lA_RevenuEmploi) := NULL;
1930: tab_xml_employee(lA_RevenuEmploi) := NULL; --
1931: END IF;
1932: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1933: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1934:
1935: -- Summ (Box B)
1936: l_box := pay_ca_archive_utils.get_archive_value(
1929: tab_employee(lA_RevenuEmploi) := NULL;
1930: tab_xml_employee(lA_RevenuEmploi) := NULL; --
1931: END IF;
1932: hr_utility.trace('tab_employee(lA_RevenuEmploi) = ' || tab_employee(lA_RevenuEmploi));
1933: hr_utility.trace('tab_xml_employee(lA_RevenuEmploi) = ' || tab_xml_employee(lA_RevenuEmploi));
1934:
1935: -- Summ (Box B)
1936: l_box := pay_ca_archive_utils.get_archive_value(
1937: l_arch_action_id,
1962: tab_employee(lB_CotisationRRQ) := NULL;
1963: tab_xml_employee(lB_CotisationRRQ) := NULL; --
1964:
1965: END IF;
1966: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1967: tab_employee(lB_CotisationRRQ));
1968: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1969: tab_xml_employee(lB_CotisationRRQ)); --
1970:
1964:
1965: END IF;
1966: hr_utility.trace('tab_employee(lB_CotisationRRQ) = ' ||
1967: tab_employee(lB_CotisationRRQ));
1968: hr_utility.trace('tab_xml_employee(lB_CotisationRRQ) = ' ||
1969: tab_xml_employee(lB_CotisationRRQ)); --
1970:
1971: -- Summ (Box C)
1972: l_box := pay_ca_archive_utils.get_archive_value(
1995: tab_employee(lC_CotisationAssEmploi) := NULL;
1996: tab_xml_employee(lC_CotisationAssEmploi) := NULL; --
1997: END IF;
1998:
1999: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2000: tab_employee(lC_CotisationAssEmploi));
2001:
2002: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2003: tab_xml_employee(lC_CotisationAssEmploi)); --
1998:
1999: hr_utility.trace('tab_employee(lC_CotisationAssEmploi) = ' ||
2000: tab_employee(lC_CotisationAssEmploi));
2001:
2002: hr_utility.trace('tab_xml_employee(lC_CotisationAssEmploi) = ' ||
2003: tab_xml_employee(lC_CotisationAssEmploi)); --
2004: -- Summ (Box D)
2005: l_box := pay_ca_archive_utils.get_archive_value(
2006: l_arch_action_id,
2027: ELSE
2028: tab_employee(lD_CotisationRPA) := NULL;
2029: tab_xml_employee(lD_CotisationRPA) := NULL; --
2030: END IF;
2031: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2032: tab_employee(lD_CotisationRPA));
2033: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2034: tab_xml_employee(lD_CotisationRPA));
2035:
2029: tab_xml_employee(lD_CotisationRPA) := NULL; --
2030: END IF;
2031: hr_utility.trace('tab_employee(lD_CotisationRPA) = ' ||
2032: tab_employee(lD_CotisationRPA));
2033: hr_utility.trace('tab_xml_employee(lD_CotisationRPA) = ' ||
2034: tab_xml_employee(lD_CotisationRPA));
2035:
2036:
2037: -- (Box E)
2062: tab_employee(lE_ImpotQue) := NULL ;
2063: tab_xml_employee(lE_ImpotQue) := NULL ; --
2064: END IF;
2065:
2066: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2067: tab_employee(lE_ImpotQue));
2068: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2069: tab_xml_employee(lE_ImpotQue)); --
2070:
2064: END IF;
2065:
2066: hr_utility.trace('tab_employee(lE_ImpotQue) = ' ||
2067: tab_employee(lE_ImpotQue));
2068: hr_utility.trace('tab_xml_employee(lE_ImpotQue) = ' ||
2069: tab_xml_employee(lE_ImpotQue)); --
2070:
2071: -- (Box F)
2072: l_box := pay_ca_archive_utils.get_archive_value(
2094: ELSE
2095: tab_employee(lF_CotisationSyndicale) := NULL;
2096: tab_xml_employee(lF_CotisationSyndicale) := NULL; --
2097: END IF;
2098: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2099: tab_employee(lF_CotisationSyndicale));
2100:
2101: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2102: tab_xml_employee(lF_CotisationSyndicale));
2097: END IF;
2098: hr_utility.trace('tab_employee(lF_CotisationSyndicale) = ' ||
2099: tab_employee(lF_CotisationSyndicale));
2100:
2101: hr_utility.trace('tab_xml_employee(lF_CotisationSyndicale) = ' ||
2102: tab_xml_employee(lF_CotisationSyndicale));
2103: -- (Box Q)
2104: l_box := pay_ca_archive_utils.get_archive_value(
2105: l_arch_action_id,
2127: ELSE
2128: tab_employee(lQ_SalaireDiffere) := NULL;
2129: tab_xml_employee(lQ_SalaireDiffere) := NULL; --
2130: END IF;
2131: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2132: tab_employee(lQ_SalaireDiffere));
2133:
2134: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2135: tab_xml_employee(lQ_SalaireDiffere));
2130: END IF;
2131: hr_utility.trace('tab_employee(lQ_SalaireDiffere) = ' ||
2132: tab_employee(lQ_SalaireDiffere));
2133:
2134: hr_utility.trace('tab_xml_employee(lQ_SalaireDiffere) = ' ||
2135: tab_xml_employee(lQ_SalaireDiffere));
2136: -- (Box U)
2137: l_box := pay_ca_archive_utils.get_archive_value(
2138: l_arch_action_id,
2162: ELSE
2163: tab_employee(lU_RetraiteProgressive) := NULL;
2164: tab_xml_employee(lU_RetraiteProgressive) := NULL; --
2165: END IF;
2166: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2167: tab_employee(lU_RetraiteProgressive));
2168:
2169: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2170: tab_xml_employee(lU_RetraiteProgressive));
2165: END IF;
2166: hr_utility.trace('tab_employee(lU_RetraiteProgressive) = ' ||
2167: tab_employee(lU_RetraiteProgressive));
2168:
2169: hr_utility.trace('tab_xml_employee(lU_RetraiteProgressive) = ' ||
2170: tab_xml_employee(lU_RetraiteProgressive));
2171: -- (Box G)
2172: l_box := pay_ca_archive_utils.get_archive_value(
2173: l_arch_action_id,
2176: --commented for bug 13360872
2177: -- 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2178: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2179:
2180: hr_utility.trace('l_box = ' || l_box);
2181: hr_utility.trace('l_boxA = ' || l_BoxA);
2182: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2183: hr_utility.trace('l_boxU = ' || l_boxU);
2184:
2177: -- 'CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD');
2178: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2179:
2180: hr_utility.trace('l_box = ' || l_box);
2181: hr_utility.trace('l_boxA = ' || l_BoxA);
2182: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2183: hr_utility.trace('l_boxU = ' || l_boxU);
2184:
2185: IF l_rep_type <>'RL1PAPERPDF' THEN
2178: 'CAEOY_QPP_EE_TAXABLE_PER_JD_YTD');
2179:
2180: hr_utility.trace('l_box = ' || l_box);
2181: hr_utility.trace('l_boxA = ' || l_BoxA);
2182: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2183: hr_utility.trace('l_boxU = ' || l_boxU);
2184:
2185: IF l_rep_type <>'RL1PAPERPDF' THEN
2186: IF l_box IS NOT NULL THEN
2179:
2180: hr_utility.trace('l_box = ' || l_box);
2181: hr_utility.trace('l_boxA = ' || l_BoxA);
2182: hr_utility.trace('l_boxQ = ' || l_BoxQ);
2183: hr_utility.trace('l_boxU = ' || l_boxU);
2184:
2185: IF l_rep_type <>'RL1PAPERPDF' THEN
2186: IF l_box IS NOT NULL THEN
2187:
2240:
2241: tab_employee(lG_SalaireAdmisRRQ) := '
2242: '0.00
2243: END IF;
2244: hr_utility.trace('tab_employee(lG_SalaireAdmisRRQ) = ' ||
2245: tab_employee(lG_SalaireAdmisRRQ));
2246: END IF;
2247:
2248: -- (Box V)
2272: tab_employee(lV_NourritureLogement) := NULL;
2273: tab_xml_employee(lV_NourritureLogement) := NULL; --
2274: END IF;
2275:
2276: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2277: tab_employee(lV_NourritureLogement));
2278:
2279: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2280: tab_xml_employee(lV_NourritureLogement));
2275:
2276: hr_utility.trace('tab_employee(lV_NourritureLogement) = ' ||
2277: tab_employee(lV_NourritureLogement));
2278:
2279: hr_utility.trace('tab_xml_employee(lV_NourritureLogement) = ' ||
2280: tab_xml_employee(lV_NourritureLogement));
2281: -- (Box W)
2282: l_box := pay_ca_archive_utils.get_archive_value(
2283: l_arch_action_id,
2304: ELSE
2305: tab_employee(lW_Vehicule) := NULL;
2306: tab_xml_employee(lW_Vehicule) := NULL; --
2307: END IF;
2308: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2309: tab_employee(lW_Vehicule));
2310:
2311: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2312: tab_xml_employee(lW_Vehicule));
2307: END IF;
2308: hr_utility.trace('tab_employee(lW_Vehicule) = ' ||
2309: tab_employee(lW_Vehicule));
2310:
2311: hr_utility.trace('tab_xml_employee(lW_Vehicule) = ' ||
2312: tab_xml_employee(lW_Vehicule));
2313:
2314: --(BOX H)
2315: l_box := pay_ca_archive_utils.get_archive_value(
2336: ELSE
2337: tab_employee(lH_CotisationRQAP) := NULL;
2338: tab_xml_employee(lH_CotisationRQAP) := NULL; --
2339: END IF;
2340: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2341: tab_employee(lH_CotisationRQAP));
2342:
2343: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2344: tab_xml_employee(lH_CotisationRQAP));
2339: END IF;
2340: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||
2341: tab_employee(lH_CotisationRQAP));
2342:
2343: hr_utility.trace('tab_xml_employee(lH_CotisationRQAP) = ' ||
2344: tab_xml_employee(lH_CotisationRQAP));
2345:
2346: --(BOX I)
2347: l_box := pay_ca_archive_utils.get_archive_value(
2371: tab_employee(lI_SalaireAdmisRQAP) := '
2372: '0.00' || '
2373: tab_xml_employee(lI_SalaireAdmisRQAP) := '0.00';--
2374: END IF;
2375: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2376: tab_employee(lI_SalaireAdmisRQAP));
2377:
2378: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2379: tab_xml_employee(lI_SalaireAdmisRQAP));
2374: END IF;
2375: hr_utility.trace('tab_employee(lI_SalaireAdmisRQAP) = ' ||
2376: tab_employee(lI_SalaireAdmisRQAP));
2377:
2378: hr_utility.trace('tab_xml_employee(lI_SalaireAdmisRQAP) = ' ||
2379: tab_xml_employee(lI_SalaireAdmisRQAP));
2380:
2381: -- (Box J)
2382: l_box := pay_ca_archive_utils.get_archive_value(
2404: ELSE
2405: tab_employee(lJ_RegimeAssMaladie) := NULL;
2406: tab_xml_employee(lJ_RegimeAssMaladie) := NULL; --
2407: END IF;
2408: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2409: tab_employee(lJ_RegimeAssMaladie));
2410:
2411: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2412: tab_xml_employee(lJ_RegimeAssMaladie));
2407: END IF;
2408: hr_utility.trace('tab_employee(lJ_RegimeAssMaladie) = ' ||
2409: tab_employee(lJ_RegimeAssMaladie));
2410:
2411: hr_utility.trace('tab_xml_employee(lJ_RegimeAssMaladie) = ' ||
2412: tab_xml_employee(lJ_RegimeAssMaladie));
2413:
2414: -- (Box K)
2415: l_box := pay_ca_archive_utils.get_archive_value(
2437: ELSE
2438: tab_employee(lK_Voyage) := NULL;
2439: tab_xml_employee(lK_Voyage) := NULL; --
2440: END IF;
2441: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2442: tab_employee(lK_Voyage));
2443:
2444: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2445: tab_xml_employee(lK_Voyage));
2440: END IF;
2441: hr_utility.trace('tab_employee(lK_Voyage) = ' ||
2442: tab_employee(lK_Voyage));
2443:
2444: hr_utility.trace('tab_xml_employee(lK_Voyage) = ' ||
2445: tab_xml_employee(lK_Voyage));
2446: -- (Box L)
2447: l_box := pay_ca_archive_utils.get_archive_value(
2448: l_arch_action_id,
2469: ELSE
2470: tab_employee(lL_AutreAvantage) := NULL;
2471: tab_xml_employee(lL_AutreAvantage) := NULL; --
2472: END IF;
2473: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2474: tab_employee(lL_AutreAvantage));
2475:
2476: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2477: tab_xml_employee(lL_AutreAvantage));
2472: END IF;
2473: hr_utility.trace('tab_employee(lL_AutreAvantage) = ' ||
2474: tab_employee(lL_AutreAvantage));
2475:
2476: hr_utility.trace('tab_xml_employee(lL_AutreAvantage) = ' ||
2477: tab_xml_employee(lL_AutreAvantage));
2478: -- (Box M)
2479: l_box := pay_ca_archive_utils.get_archive_value(
2480: l_arch_action_id,
2501: ELSE
2502: tab_employee(lM_Commission) := NULL;
2503: tab_xml_employee(lM_Commission) := NULL; --
2504: END IF;
2505: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2506: tab_employee(lM_Commission));
2507:
2508: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2509: tab_xml_employee(lM_Commission));
2504: END IF;
2505: hr_utility.trace('tab_employee(lM_Commission) = ' ||
2506: tab_employee(lM_Commission));
2507:
2508: hr_utility.trace('tab_xml_employee(lM_Commission) = ' ||
2509: tab_xml_employee(lM_Commission));
2510: -- (Box N)
2511: l_box := pay_ca_archive_utils.get_archive_value(
2512: l_arch_action_id,
2534: tab_employee(lN_DonBienfaisance) := NULL;
2535: tab_xml_employee(lN_DonBienfaisance) := NULL; --
2536: END IF;
2537:
2538: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2539: tab_employee(lN_DonBienfaisance));
2540:
2541: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2542: tab_xml_employee(lN_DonBienfaisance));
2537:
2538: hr_utility.trace('tab_employee(lN_DonBienfaisance) = ' ||
2539: tab_employee(lN_DonBienfaisance));
2540:
2541: hr_utility.trace('tab_xml_employee(lN_DonBienfaisance) = ' ||
2542: tab_xml_employee(lN_DonBienfaisance));
2543: -- Summ (Box O)
2544: l_box := pay_ca_archive_utils.get_archive_value(
2545: l_arch_action_id,
2567: ELSE
2568: tab_employee(lO_AutreRevenu) := NULL;
2569: tab_xml_employee(lO_AutreRevenu) := NULL; --
2570: END IF;
2571: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2572: tab_employee(lO_AutreRevenu));
2573:
2574: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2575: tab_xml_employee(lO_AutreRevenu));
2570: END IF;
2571: hr_utility.trace('tab_employee(lO_AutreRevenu) = ' ||
2572: tab_employee(lO_AutreRevenu));
2573:
2574: hr_utility.trace('tab_xml_employee(lO_AutreRevenu) = ' ||
2575: tab_xml_employee(lO_AutreRevenu));
2576:
2577: -- SourceCasem
2578:
2588: ELSE
2589: tab_employee(l_SourceCase) := NULL;
2590: tab_xml_employee(l_SourceCase) := NULL; --
2591: END IF;
2592: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2593: tab_employee(l_SourceCase));
2594:
2595: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2596: tab_xml_employee(l_SourceCase));
2591: END IF;
2592: hr_utility.trace('tab_employee(l_SourceCase) = ' ||
2593: tab_employee(l_SourceCase));
2594:
2595: hr_utility.trace('tab_xml_employee(l_SourceCase) = ' ||
2596: tab_xml_employee(l_SourceCase));
2597: -- (Box P)
2598: if tab_employee(lO_AutreRevenu) is not null
2599: and tab_employee(l_SourceCase)is not null then
2629: ELSE
2630: tab_employee(lP_RegimeAssInterEntr) := NULL;
2631: tab_xml_employee(lP_RegimeAssInterEntr) := NULL; --
2632: END IF;
2633: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2634: tab_employee(lP_RegimeAssInterEntr));
2635:
2636: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2637: tab_xml_employee(lP_RegimeAssInterEntr));
2632: END IF;
2633: hr_utility.trace('tab_employee(lP_RegimeAssInterEntr) = ' ||
2634: tab_employee(lP_RegimeAssInterEntr));
2635:
2636: hr_utility.trace('tab_xml_employee(lP_RegimeAssInterEntr) = ' ||
2637: tab_xml_employee(lP_RegimeAssInterEntr));
2638: -- (Box R)
2639: l_box := pay_ca_archive_utils.get_archive_value(
2640: l_arch_action_id,
2661: ELSE
2662: tab_employee(lR_RevenuIndien) := NULL;
2663: tab_xml_employee(lR_RevenuIndien) := NULL; --
2664: END IF;
2665: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2666: tab_employee(lR_RevenuIndien));
2667:
2668: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2669: tab_xml_employee(lR_RevenuIndien));
2664: END IF;
2665: hr_utility.trace('tab_employee(lR_RevenuIndien) = ' ||
2666: tab_employee(lR_RevenuIndien));
2667:
2668: hr_utility.trace('tab_xml_employee(lR_RevenuIndien) = ' ||
2669: tab_xml_employee(lR_RevenuIndien));
2670: -- (Box S)
2671: l_box := pay_ca_archive_utils.get_archive_value(
2672: l_arch_action_id,
2693: ELSE
2694: tab_employee(lS_PourboireRecu) := NULL;
2695: tab_xml_employee(lS_PourboireRecu) := NULL; --
2696: END IF;
2697: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2698: tab_employee(lS_PourboireRecu));
2699:
2700: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2701: tab_xml_employee(lS_PourboireRecu));
2696: END IF;
2697: hr_utility.trace('tab_employee(lS_PourboireRecu) = ' ||
2698: tab_employee(lS_PourboireRecu));
2699:
2700: hr_utility.trace('tab_xml_employee(lS_PourboireRecu) = ' ||
2701: tab_xml_employee(lS_PourboireRecu));
2702: -- (Box T)
2703: l_box := pay_ca_archive_utils.get_archive_value(
2704: l_arch_action_id,
2725: ELSE
2726: tab_employee(lT_PourboireAttribue) := NULL;
2727: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2728: END IF;
2729: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2730: tab_employee(lT_PourboireAttribue));
2731: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2732: tab_xml_employee(lT_PourboireAttribue));
2733: -- (Box ContisationRPC)
2727: tab_xml_employee(lT_PourboireAttribue) := NULL; --
2728: END IF;
2729: hr_utility.trace('tab_employee(lT_PourboireAttribue) = ' ||
2730: tab_employee(lT_PourboireAttribue));
2731: hr_utility.trace('tab_xml_employee(lT_PourboireAttribue) = ' ||
2732: tab_xml_employee(lT_PourboireAttribue));
2733: -- (Box ContisationRPC)
2734: l_box := pay_ca_archive_utils.get_archive_value(
2735: l_arch_action_id,
2755: l_all_box_0 := FALSE;
2756: ELSE
2757: tab_employee(l_ContisationRPC) := NULL;
2758: END IF;
2759: hr_utility.trace('tab_employee(lR_ContisationRPC) = ' ||
2760: tab_employee(l_ContisationRPC));
2761:
2762: /* Changes for bug 13567075 starts here -- sbachu*/
2763:
2784: l_all_box_0 := FALSE;
2785: ELSE
2786: tab_employee(l_FraisGarde) := NULL;
2787: END IF;
2788: hr_utility.trace('tab_employee(l_FraisGarde) = ' ||
2789: tab_employee(l_FraisGarde));
2790:
2791: l_box := fnd_number.number_to_canonical(fnd_number.canonical_to_number(pay_ca_archive_utils.get_archive_value(
2792: l_arch_action_id,
2817: l_all_box_0 := FALSE;
2818: ELSE
2819: tab_employee(l_DeductionForce) := NULL;
2820: END IF;
2821: hr_utility.trace('tab_employee(l_DeductionForce) = ' ||
2822: tab_employee(l_DeductionForce));
2823:
2824: /*Changes for bug 13567075 ends here --sbachu*/
2825:
2824: /*Changes for bug 13567075 ends here --sbachu*/
2825:
2826:
2827: -- Negative Balance Exists
2828: hr_utility.trace('finding if neg bal exists');
2829: l_box := pay_ca_archive_utils.get_archive_value(
2830: l_arch_action_id,
2831: l_province,
2832: 'JURISDICTION_CODE',
2835: IF l_box = 'Y' THEN
2836: l_status := 'Failed';
2837: l_msg_code := 'NEG';
2838: l_neg_bal_exists := TRUE;
2839: hr_utility.trace('neg bal exists');
2840: END IF;
2841:
2842: IF to_number(l_taxation_year) < 2011 AND l_all_box_0 THEN /*Bug 13633422*/
2843: l_status := 'Failed';
2877: OPEN cur_get_meaning(l_msg_code);
2878: FETCH cur_get_meaning
2879: INTO l_meaning;
2880: CLOSE cur_get_meaning;
2881: hr_utility.trace('l_meaning ='|| l_meaning);
2882: tab_employee(lErrorDetails) := '
2883: l_meaning || '
2884:
2885: l_other_details := l_other_details||tab_employee(lErrorDetails);
2883: l_meaning || '' || EOL;
2884:
2885: l_other_details := l_other_details||tab_employee(lErrorDetails);
2886: END IF;
2887: hr_utility.trace('l_other_details ='||l_other_details);
2888: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||tab_employee(lH_CotisationRQAP));
2889: hr_utility.trace('l_rep_type ='|| l_rep_type);
2890: /*bug 14701466 starts here sbachu*/
2891: lv_ident_addr_string := '
2884:
2885: l_other_details := l_other_details||tab_employee(lErrorDetails);
2886: END IF;
2887: hr_utility.trace('l_other_details ='||l_other_details);
2888: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||tab_employee(lH_CotisationRQAP));
2889: hr_utility.trace('l_rep_type ='|| l_rep_type);
2890: /*bug 14701466 starts here sbachu*/
2891: lv_ident_addr_string := '
2892: || '
2885: l_other_details := l_other_details||tab_employee(lErrorDetails);
2886: END IF;
2887: hr_utility.trace('l_other_details ='||l_other_details);
2888: hr_utility.trace('tab_employee(lH_CotisationRQAP) = ' ||tab_employee(lH_CotisationRQAP));
2889: hr_utility.trace('l_rep_type ='|| l_rep_type);
2890: /*bug 14701466 starts here sbachu*/
2891: lv_ident_addr_string := '
2892: || '
2893: tab_employee(lNAS) ||
3016: end if;
3017: end if;
3018: END IF; /* if l_status = 'Failed' */
3019: /*bug 14701466 ends here sbachu*/
3020: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3021: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3022: --end if;
3023: hr_utility.trace('end of xml_employee_record');
3024:
3019: /*bug 14701466 ends here sbachu*/
3020: hr_utility.trace('rl1_xml_employee: l_final_xml_string = ' || l_final_xml_string);
3021: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3022: --end if;
3023: hr_utility.trace('end of xml_employee_record');
3024:
3025: END;
3026: END xml_employee_record;
3027:
3037: BEGIN
3038:
3039: l_final_xml_string := '
3040:
3041: hr_utility.trace('rl1_xml_employee_start: l_final_xml_string = ' || l_final_xml_string);
3042: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3043:
3044:
3045: END;
3083: --l_pre_id_t number;
3084:
3085: BEGIN
3086:
3087: hr_utility.trace('XML Employer');
3088:
3089: SELECT
3090: fnd_global.local_chr(13) || fnd_global.local_chr(10)
3091: INTO EOL
3123:
3124: --l_pre_id := to_number(pay_magtape_generic.get_parameter_value('PRE_ID'));
3125: --l_pre_id_t := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PRE_ID'));
3126:
3127: --hr_utility.trace('l_pre_id = ' || l_pre_id);
3128: --hr_utility.trace('l_pre_id_t = ' || l_pre_id_t);
3129:
3130: hr_utility.trace ('l_context1 ='||l_context1);
3131:
3124: --l_pre_id := to_number(pay_magtape_generic.get_parameter_value('PRE_ID'));
3125: --l_pre_id_t := to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PRE_ID'));
3126:
3127: --hr_utility.trace('l_pre_id = ' || l_pre_id);
3128: --hr_utility.trace('l_pre_id_t = ' || l_pre_id_t);
3129:
3130: hr_utility.trace ('l_context1 ='||l_context1);
3131:
3132: tab_employer(lAnnee) := '
3126:
3127: --hr_utility.trace('l_pre_id = ' || l_pre_id);
3128: --hr_utility.trace('l_pre_id_t = ' || l_pre_id_t);
3129:
3130: hr_utility.trace ('l_context1 ='||l_context1);
3131:
3132: tab_employer(lAnnee) := '
3133: tab_employer(lNbReleves) := '
3134:
3147: 'CAEOY_RL1_EMPLOYER_NAME');
3148:
3149: tab_employer(lNom1) := '
3150: convert_special_char(substr(l_employer_name,1,30)) || '
3151: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3148:
3149: tab_employer(lNom1) := '
3150: convert_special_char(substr(l_employer_name,1,30)) || '
3151: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3149: tab_employer(lNom1) := '
3150: convert_special_char(substr(l_employer_name,1,30)) || '
3151: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3150: convert_special_char(substr(l_employer_name,1,30)) || '' || EOL;
3151: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3158:
3151: hr_utility.trace('tab_employer(lAnnee) = ' || tab_employer(lAnnee));
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3158:
3159: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3152: hr_utility.trace('tab_employer(lNbReleves) = ' || tab_employer(lNbReleves));
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3158:
3159: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3160: tab_employer(lNom2) := '
3153: hr_utility.trace('tab_employer(lNoId) = ' || tab_employer(lNoId));
3154: hr_utility.trace('tab_employer(lTypeDossier) = ' || tab_employer(lTypeDossier));
3155: hr_utility.trace('tab_employer(lNoDossier) = ' || tab_employer(lNoDossier));
3156: hr_utility.trace('tab_employer(lNEQ) = ' || tab_employer(lNEQ));
3157: hr_utility.trace('tab_employer(lNom1) = ' || tab_employer(lNom1));
3158:
3159: IF SUBSTR(l_employer_name,31,30) IS NOT NULL THEN
3160: tab_employer(lNom2) := '
3161: convert_special_char(substr(l_employer_name,31,30)) || '
3161: convert_special_char(substr(l_employer_name,31,30)) || '' || EOL;
3162: ELSE
3163: tab_employer(lNom2) := NULL;
3164: END IF;
3165: hr_utility.trace('tab_employer(lNom2) = ' || tab_employer(lNom2));
3166:
3167: -- Address Line 1
3168:
3169: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3184: l_address_begin_tag := '
3185:
3186: tab_employer(lLigne1) := '
3187: convert_special_char(substr(l_address_line,1,30)) || '
3188: hr_utility.trace('tab_employer(lLigne1) = ' || tab_employer(lLigne1));
3189:
3190:
3191: -- Address Line 2
3192:
3198: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
3199: ELSE
3200: tab_employer(lLigne2) := NULL;
3201: END IF;
3202: hr_utility.trace('tab_employer(lLigne2) = ' || tab_employer(lLigne2));
3203:
3204: -- Ville (City)
3205:
3206: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3210: convert_special_char(substr(l_address_line,1,30)) || '' || EOL;
3211: ELSE
3212: tab_employer(lVille) := NULL;
3213: END IF;
3214: hr_utility.trace('tab_employer(lVille) = ' || tab_employer(lVille));
3215:
3216: -- Province
3217:
3218: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3224: l_address_line),1,20) || '' || EOL;
3225: ELSE
3226: tab_employer(lProvince) := NULL;
3227: END IF;
3228: hr_utility.trace('tab_employer(lProvince) = ' || tab_employer(lProvince));
3229:
3230: -- Postal Code
3231:
3232: l_address_line := pay_ca_archive_utils.get_archive_value(l_context1,
3237: substr(replace(l_address_line,' '),1,6) || '' || EOL;
3238: ELSE
3239: tab_employer(lCodePostal) := NULL;
3240: END IF;
3241: hr_utility.trace('tab_employer(lCodePostal) = ' || tab_employer(lCodePostal));
3242:
3243: l_address_end_tag := '';
3244:
3245: END IF;
3261: l_address_end_tag || EOL || '' || EOL ||
3262: '' || EOL ||
3263: '' || EOL;
3264:
3265: hr_utility.trace('rl1_xml_employer_start: l_final_xml_string = ' || l_final_xml_string);
3266: pay_core_files.write_to_magtape_lob(l_final_xml_string);
3267: END;
3268: END xml_employer_record;
3269: