[Home] [Help]
305: open csr_defined_balance;
306: fetch csr_defined_balance into l_defined_balance_id;
307: if csr_defined_balance%notfound then
308: close csr_defined_balance;
309: raise hr_utility.hr_error;
310: else
311: close csr_defined_balance;
312: end if;
313:
418: if p_report_type = 'RL1' then
419:
420: /* Default settings for Year End Preprocess. */
421:
422: hr_utility.trace('in getting selection information ');
423: p_period_start := p_year_start;
424: p_period_end := p_year_end;
425: /* p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'); */
426: p_defined_balance_id := 0;
433:
434: /* An invalid report type has been passed so fail. */
435:
436: else
437: hr_utility.trace('in error of getting selection information ');
438:
439: raise hr_utility.hr_error;
440:
441: end if;
435:
436: else
437: hr_utility.trace('in error of getting selection information ');
438:
439: raise hr_utility.hr_error;
440:
441: end if;
442:
443: end get_selection_information;
469: open csr_jurisdiction_code;
470: fetch csr_jurisdiction_code into l_jurisdiction_code;
471: if csr_jurisdiction_code%notfound then
472: close csr_jurisdiction_code;
473: raise hr_utility.hr_error;
474: else
475: close csr_jurisdiction_code;
476: end if;
477:
501: return l_user_entity_id;
502:
503: exception
504: when others then
505: hr_utility.trace('Error while getting the user_entity_id'
506: || p_dbi_name);
507: raise hr_utility.hr_error;
508:
509: end get_user_entity_id;
503: exception
504: when others then
505: hr_utility.trace('Error while getting the user_entity_id'
506: || p_dbi_name);
507: raise hr_utility.hr_error;
508:
509: end get_user_entity_id;
510:
511:
533: return l_user_entity_id;
534:
535: exception
536: when others then
537: hr_utility.trace('skipping the record because no dbi of name:'
538: || p_dbi_name);
539: return 0;
540: end;
541: end if;
699: ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_EMPLOYEE_POSTAL_CODE');
700:
701: /* Populate RL1 Amendment Box Data for an assignment_action */
702: open c_get_emp_rl1box_data(p_assignment_action_id);
703: hr_utility.trace('Populating RL1 Amendment Box Data ');
704: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
705: ln_amend_box := 0;
706: loop
707: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
700:
701: /* Populate RL1 Amendment Box Data for an assignment_action */
702: open c_get_emp_rl1box_data(p_assignment_action_id);
703: hr_utility.trace('Populating RL1 Amendment Box Data ');
704: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
705: ln_amend_box := 0;
706: loop
707: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
708: exit when c_get_emp_rl1box_data%NOTFOUND;
706: loop
707: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
708: exit when c_get_emp_rl1box_data%NOTFOUND;
709:
710: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
711: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
712: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
713: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
714: ln_amend_box := ln_amend_box + 1;
707: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
708: exit when c_get_emp_rl1box_data%NOTFOUND;
709:
710: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
711: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
712: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
713: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
714: ln_amend_box := ln_amend_box + 1;
715: end loop;
708: exit when c_get_emp_rl1box_data%NOTFOUND;
709:
710: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
711: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
712: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
713: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
714: ln_amend_box := ln_amend_box + 1;
715: end loop;
716:
709:
710: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
711: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
712: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
713: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
714: ln_amend_box := ln_amend_box + 1;
715: end loop;
716:
717: close c_get_emp_rl1box_data;
716:
717: close c_get_emp_rl1box_data;
718:
719: /* Populate RL1 Amendment Employee Data for an assignment_action */
720: hr_utility.trace('Populating Amendment Employee Data ');
721: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
722: for i in 0 .. j
723: loop
724: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
717: close c_get_emp_rl1box_data;
718:
719: /* Populate RL1 Amendment Employee Data for an assignment_action */
720: hr_utility.trace('Populating Amendment Employee Data ');
721: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
722: for i in 0 .. j
723: loop
724: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
725: fetch c_get_employee_data into ltr_amend_emp_data(i);
723: loop
724: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
725: fetch c_get_employee_data into ltr_amend_emp_data(i);
726:
727: hr_utility.trace('I :'||to_char(i));
728: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
729: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
730: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
731:
724: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
725: fetch c_get_employee_data into ltr_amend_emp_data(i);
726:
727: hr_utility.trace('I :'||to_char(i));
728: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
729: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
730: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
731:
732: close c_get_employee_data;
725: fetch c_get_employee_data into ltr_amend_emp_data(i);
726:
727: hr_utility.trace('I :'||to_char(i));
728: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
729: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
730: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
731:
732: close c_get_employee_data;
733: end loop;
726:
727: hr_utility.trace('I :'||to_char(i));
728: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
729: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
730: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
731:
732: close c_get_employee_data;
733: end loop;
734:
734:
735:
736: /* Populate RL1 YEPP Box Data for an assignment_action */
737: open c_get_emp_rl1box_data(p_locked_action_id);
738: hr_utility.trace('Populating RL1 YEPP Box Data ');
739: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
740: ln_box := 0;
741: loop
742: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
735:
736: /* Populate RL1 YEPP Box Data for an assignment_action */
737: open c_get_emp_rl1box_data(p_locked_action_id);
738: hr_utility.trace('Populating RL1 YEPP Box Data ');
739: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
740: ln_box := 0;
741: loop
742: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
743: exit when c_get_emp_rl1box_data%NOTFOUND;
741: loop
742: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
743: exit when c_get_emp_rl1box_data%NOTFOUND;
744:
745: hr_utility.trace('ln_box :'||to_char(ln_box));
746: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
747: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
748: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
749: ln_box := ln_box + 1;
742: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
743: exit when c_get_emp_rl1box_data%NOTFOUND;
744:
745: hr_utility.trace('ln_box :'||to_char(ln_box));
746: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
747: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
748: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
749: ln_box := ln_box + 1;
750: end loop;
743: exit when c_get_emp_rl1box_data%NOTFOUND;
744:
745: hr_utility.trace('ln_box :'||to_char(ln_box));
746: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
747: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
748: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
749: ln_box := ln_box + 1;
750: end loop;
751:
744:
745: hr_utility.trace('ln_box :'||to_char(ln_box));
746: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
747: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
748: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
749: ln_box := ln_box + 1;
750: end loop;
751:
752: close c_get_emp_rl1box_data;
751:
752: close c_get_emp_rl1box_data;
753:
754: /* Populate RL1 YEPP Employee Data for an assignment_action */
755: hr_utility.trace('Populating YEPP Employee Data ');
756: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
757:
758: for i in 0 .. j
759: loop
752: close c_get_emp_rl1box_data;
753:
754: /* Populate RL1 YEPP Employee Data for an assignment_action */
755: hr_utility.trace('Populating YEPP Employee Data ');
756: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
757:
758: for i in 0 .. j
759: loop
760: open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
760: open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
761: fetch c_get_employee_data into ltr_yepp_emp_data(i);
762: exit when c_get_employee_data%NOTFOUND;
763:
764: hr_utility.trace('I :'||to_char(i));
765: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
766: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
767: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
768:
761: fetch c_get_employee_data into ltr_yepp_emp_data(i);
762: exit when c_get_employee_data%NOTFOUND;
763:
764: hr_utility.trace('I :'||to_char(i));
765: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
766: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
767: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
768:
769: close c_get_employee_data;
762: exit when c_get_employee_data%NOTFOUND;
763:
764: hr_utility.trace('I :'||to_char(i));
765: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
766: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
767: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
768:
769: close c_get_employee_data;
770: end loop;
763:
764: hr_utility.trace('I :'||to_char(i));
765: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
766: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
767: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
768:
769: close c_get_employee_data;
770: end loop;
771:
771:
772: /* Populate RL1 Amendment Footnotes */
773: open c_get_nonbox_footnote(p_assignment_action_id);
774:
775: hr_utility.trace('Populating RL1 Amendment Footnote ');
776:
777: ln_amend_footnote_count := 0;
778: loop
779: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
778: loop
779: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
780: exit when c_get_nonbox_footnote%NOTFOUND;
781:
782: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
783: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
784:
785: ln_amend_footnote_count := ln_amend_footnote_count + 1;
786: end loop;
779: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
780: exit when c_get_nonbox_footnote%NOTFOUND;
781:
782: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
783: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
784:
785: ln_amend_footnote_count := ln_amend_footnote_count + 1;
786: end loop;
787:
794: loop
795: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
796: exit when c_get_nonbox_footnote%NOTFOUND;
797:
798: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
799: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
800:
801: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
802: end loop;
795: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
796: exit when c_get_nonbox_footnote%NOTFOUND;
797:
798: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
799: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
800:
801: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
802: end loop;
803:
806:
807: /* Compare RL1 Amendment Box Data and RL1 YEPP Box Data for an
808: assignment_action */
809:
810: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Box Data ');
811:
812: if ln_box <> ln_amend_box then
813: lv_flag := 'Y';
814: elsif ln_box = ln_amend_box then
824: (ltr_yepp_arch_data(i).archive_value is not null and
825: ltr_amend_arch_data(i).archive_value is null)) then
826:
827: lv_flag := 'Y';
828: hr_utility.trace('Archive_UE_id with differnt value :'||
829: to_char(ltr_yepp_arch_data(i).archive_ue_id));
830: exit;
831: end if;
832:
839: /* Compare RL1 Employee Data and RL1 YEPP Employee Data for an
840: assignment_action */
841: If lv_flag <> 'Y' then
842:
843: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Employee Data ');
844: for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
845: loop
846: if (ltr_yepp_emp_data(i).archive_ue_id =
847: ltr_amend_emp_data(i).archive_ue_id) then
845: loop
846: if (ltr_yepp_emp_data(i).archive_ue_id =
847: ltr_amend_emp_data(i).archive_ue_id) then
848:
849: hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
850: ltr_yepp_emp_data(i).archive_value);
851: hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
852: ltr_amend_emp_data(i).archive_value);
853:
847: ltr_amend_emp_data(i).archive_ue_id) then
848:
849: hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
850: ltr_yepp_emp_data(i).archive_value);
851: hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
852: ltr_amend_emp_data(i).archive_value);
853:
854: if ((ltr_yepp_emp_data(i).archive_value <>
855: ltr_amend_emp_data(i).archive_value) or
858: (ltr_yepp_emp_data(i).archive_value is not null and
859: ltr_amend_emp_data(i).archive_value is null)) then
860:
861: lv_flag := 'Y';
862: hr_utility.trace('Archive_UE_id with different value :'||
863: to_char(ltr_yepp_arch_data(i).archive_ue_id));
864: exit;
865: end if;
866:
871:
872: /* Compare RL1 Amendment Footnotes and RL1 YEPP Footnotes for an
873: assignment_action */
874:
875: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Footnotes');
876:
877: if lv_flag <> 'Y' then
878:
879: if ln_yepp_footnote_count <> ln_amend_footnote_count then
892: (ltr_yepp_footnote(i).value is not null and
893: ltr_amend_footnote(i).value is null)) then
894:
895: lv_flag := 'Y';
896: hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
897: exit;
898: end if;
899: end if;
900: end loop;
906: flag to 'N' */
907:
908: if lv_flag <> 'Y' then
909: lv_flag := 'N';
910: hr_utility.trace('No value difference for an Employee Asg Action: '||
911: to_char(p_assignment_action_id));
912: end if;
913:
914: hr_utility.trace('lv_flag :'||lv_flag);
910: hr_utility.trace('No value difference for an Employee Asg Action: '||
911: to_char(p_assignment_action_id));
912: end if;
913:
914: hr_utility.trace('lv_flag :'||lv_flag);
915: return lv_flag;
916:
917: end compare_archive_data;
918:
1132:
1133: /* Get the report type, report qualifier, business group id and the
1134: gre for which the archiving has to be done */
1135:
1136: hr_utility.trace('getting report type ');
1137:
1138: select effective_date,
1139: report_type,
1140: business_group_id,
1146: l_pre_organization_id
1147: from pay_payroll_actions
1148: where payroll_action_id = pactid;
1149:
1150: hr_utility.trace('getting dates');
1151:
1152: get_dates(l_report_type,
1153: l_effective_date,
1154: l_period_end,
1156: l_quarter_end,
1157: l_year_start,
1158: l_year_end);
1159:
1160: hr_utility.trace('getting selection information');
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1157: l_year_start,
1158: l_year_end);
1159:
1160: hr_utility.trace('getting selection information');
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1158: l_year_end);
1159:
1160: hr_utility.trace('getting selection information');
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1166:
1159:
1160: hr_utility.trace('getting selection information');
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1166:
1167: get_selection_information
1160: hr_utility.trace('getting selection information');
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1166:
1167: get_selection_information
1168: (l_report_type,
1161: hr_utility.trace('report type '|| l_report_type);
1162: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1163: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1164: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1165: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1166:
1167: get_selection_information
1168: (l_report_type,
1169: l_quarter_start,
1177: l_group_by_medicare,
1178: l_tax_unit_context,
1179: l_jurisdiction_context);
1180:
1181: hr_utility.trace('Out of get selection information');
1182: open c_eoy_qbin;
1183:
1184: /* Loop for all rows returned for SQL statement. */
1185:
1182: open c_eoy_qbin;
1183:
1184: /* Loop for all rows returned for SQL statement. */
1185:
1186: hr_utility.trace('Entering loop');
1187:
1188: loop
1189:
1190: fetch c_eoy_qbin
1199: the rows are grouped then discard the row ie. grouping by PRE
1200: organization id requires a single row for each person / PRE
1201: combination. */
1202:
1203: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1204: hr_utility.trace('previous pre_organization_id is '||
1205: l_prev_pre_organization_id);
1206: hr_utility.trace('person_id is '||
1207: to_char(l_person_id));
1200: organization id requires a single row for each person / PRE
1201: combination. */
1202:
1203: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1204: hr_utility.trace('previous pre_organization_id is '||
1205: l_prev_pre_organization_id);
1206: hr_utility.trace('person_id is '||
1207: to_char(l_person_id));
1208: hr_utility.trace('previous person_id is '||
1202:
1203: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1204: hr_utility.trace('previous pre_organization_id is '||
1205: l_prev_pre_organization_id);
1206: hr_utility.trace('person_id is '||
1207: to_char(l_person_id));
1208: hr_utility.trace('previous person_id is '||
1209: to_char(l_prev_person_id));
1210:
1204: hr_utility.trace('previous pre_organization_id is '||
1205: l_prev_pre_organization_id);
1206: hr_utility.trace('person_id is '||
1207: to_char(l_person_id));
1208: hr_utility.trace('previous person_id is '||
1209: to_char(l_prev_person_id));
1210:
1211: if (l_person_id = l_prev_person_id and
1212: l_pre_organization_id = l_prev_pre_organization_id) then
1210:
1211: if (l_person_id = l_prev_person_id and
1212: l_pre_organization_id = l_prev_pre_organization_id) then
1213:
1214: hr_utility.trace('Not creating assignment action');
1215:
1216: else
1217: /* Check whether the person has 0 payment or not */
1218:
1284: end loop;
1285: close c_all_qbin_gres;
1286: /* end of checking whether the person has 0 payment */
1287:
1288: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1289: hr_utility.trace('person is '|| to_char(l_person_id));
1290: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1291:
1292:
1285: close c_all_qbin_gres;
1286: /* end of checking whether the person has 0 payment */
1287:
1288: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1289: hr_utility.trace('person is '|| to_char(l_person_id));
1290: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1291:
1292:
1293: /* Have a new unique row according to the way the rows are grouped.
1286: /* end of checking whether the person has 0 payment */
1287:
1288: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1289: hr_utility.trace('person is '|| to_char(l_person_id));
1290: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1291:
1292:
1293: /* Have a new unique row according to the way the rows are grouped.
1294: The inclusion of the person is dependent on having a non zero
1297: indicate their inclusion in the magnetic tape report. */
1298:
1299: /* Set up the context of tax unit id */
1300:
1301: hr_utility.trace('Setting context');
1302:
1303: /* Only create assignment actions if Gross Earnings are not 0 and are not
1304: made up of only nontaxable earnings or the No Gross Earnings balance is
1305: non zero */
1313: fetch c_get_asg_id into l_primary_asg;
1314:
1315: if c_get_asg_id%NOTFOUND then
1316: close c_get_asg_id;
1317: raise hr_utility.hr_error;
1318: else
1319: close c_get_asg_id;
1320: end if;
1321:
1327: from dual;
1328:
1329: /* Insert into pay_assignment_actions. */
1330:
1331: hr_utility.trace('creating assignment action');
1332:
1333: /* Passing tax unit id as null */
1334:
1335: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1339: so that the mag routine and the RL1 view will not have
1340: to do an additional checking against the assignment
1341: table */
1342:
1343: hr_utility.trace('updating assignment action' || to_char(lockingactid));
1344:
1345: update pay_assignment_actions aa
1346: set aa.serial_number = to_char(l_person_id)
1347: where aa.assignment_action_id = lockingactid;
1352: /*
1353: l_counter := 1;
1354: l_user_entity_name := 'CAEOY_RL1_QUEBEC_BN';
1355:
1356: hr_utility.trace('Archiving AAC level data for ' || to_char(lockingactid));
1357: ff_archive_api.create_archive_item(
1358: p_archive_item_id => l_archive_item_id
1359: ,p_user_entity_id => get_user_entity_id(l_user_entity_name)
1360: ,p_archive_value => l_pre_organization_id
1363: ,p_legislation_code => 'CA'
1364: ,p_object_version_number => l_object_version_number
1365: ,p_some_warning => l_some_warning
1366: );
1367: hr_utility.trace('Archived AAC level data');
1368: */
1369:
1370: /* I have to enter data in new archive table also with archive type as AAC, ie
1371: assignment_action_creation */
1378: l_prev_pre_organization_id := l_pre_organization_id;
1379:
1380: end loop;
1381:
1382: hr_utility.trace('Action creation done');
1383: close c_eoy_qbin;
1384:
1385: end eoy_action_creation;
1386:
1413: exception
1414: when no_data_found then
1415: return(0);
1416: when others then
1417: hr_utility.trace('Error while getting the jursd. value ' ||
1418: to_char(sqlcode));
1419:
1420: end eoy_get_jursd_level;
1421:
1527: /* payroll action level database items */
1528:
1529: BEGIN
1530:
1531: /*hr_utility.trace_on('Y','RL1'); */
1532:
1533: select to_char(effective_date,'YYYY'),business_group_id
1534: into l_taxation_year,l_business_group_id
1535: from pay_payroll_actions
1555: l_transmitter_org_id,
1556: l_transmitter_gre_ind;
1557:
1558: l_arch_gre_step := 40;
1559: hr_utility.trace('eoy_archive_gre_data 1');
1560:
1561: if employer_info%FOUND then
1562:
1563: close employer_info;
1560:
1561: if employer_info%FOUND then
1562:
1563: close employer_info;
1564: hr_utility.trace('got employer data ');
1565:
1566: l_employer_info_found := 'Y';
1567:
1568: begin
1650: l_accounting_contact_language:= 'TEST_DATA';
1651: l_proprietor_sin := 'TEST_DATA';
1652: l_arch_gre_step := 424;
1653:
1654: hr_utility.trace('eoy_archive_gre_data 2');
1655: close employer_info;
1656:
1657: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1658: hr_utility.set_message_token('ORGIND','GRE');
1653:
1654: hr_utility.trace('eoy_archive_gre_data 2');
1655: close employer_info;
1656:
1657: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1658: hr_utility.set_message_token('ORGIND','GRE');
1659: hr_utility.raise_error;
1660: end if;
1661:
1654: hr_utility.trace('eoy_archive_gre_data 2');
1655: close employer_info;
1656:
1657: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1658: hr_utility.set_message_token('ORGIND','GRE');
1659: hr_utility.raise_error;
1660: end if;
1661:
1662: /* archive Releve 1 data */
1655: close employer_info;
1656:
1657: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1658: hr_utility.set_message_token('ORGIND','GRE');
1659: hr_utility.raise_error;
1660: end if;
1661:
1662: /* archive Releve 1 data */
1663:
1850: */
1851:
1852: l_arch_gre_step := 52;
1853:
1854: /*hr_utility.trace_on('Y','RL1'); */
1855:
1856: hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
1857:
1858: ff_archive_api.create_archive_item(
1852: l_arch_gre_step := 52;
1853:
1854: /*hr_utility.trace_on('Y','RL1'); */
1855:
1856: hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
1857:
1858: ff_archive_api.create_archive_item(
1859: p_archive_item_id => l_archive_item_id
1860: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1870:
1871: EXCEPTION
1872: when others then
1873: g_archive_flag := 'N';
1874: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1875: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1876: if l_arch_gre_step = 40 then
1877: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1878: hr_utility.set_message_token('ORGIND','ORG');
1873: g_archive_flag := 'N';
1874: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1875: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1876: if l_arch_gre_step = 40 then
1877: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1878: hr_utility.set_message_token('ORGIND','ORG');
1879: end if;
1880: hr_utility.raise_error;
1881:
1874: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1875: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1876: if l_arch_gre_step = 40 then
1877: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1878: hr_utility.set_message_token('ORGIND','ORG');
1879: end if;
1880: hr_utility.raise_error;
1881:
1882: END eoy_archive_gre_data;
1876: if l_arch_gre_step = 40 then
1877: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1878: hr_utility.set_message_token('ORGIND','ORG');
1879: end if;
1880: hr_utility.raise_error;
1881:
1882: END eoy_archive_gre_data;
1883:
1884: /* Name : chk_gre_archive
1899: from ff_archive_items fai
1900: where fai.context1 = p_payroll_action_id);
1901: begin
1902:
1903: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1904:
1905: if g_archive_flag = 'Y' then
1906: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1907: return (TRUE);
1902:
1903: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1904:
1905: if g_archive_flag = 'Y' then
1906: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1907: return (TRUE);
1908: else
1909:
1910: hr_utility.trace('chk_gre_archive - opening cursor');
1906: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1907: return (TRUE);
1908: else
1909:
1910: hr_utility.trace('chk_gre_archive - opening cursor');
1911:
1912: open c_chk_payroll_action;
1913: fetch c_chk_payroll_action into l_flag;
1914: if c_chk_payroll_action%FOUND then
1911:
1912: open c_chk_payroll_action;
1913: fetch c_chk_payroll_action into l_flag;
1914: if c_chk_payroll_action%FOUND then
1915: hr_utility.trace('chk_gre_archive - found in cursor');
1916: g_archive_flag := 'Y';
1917: else
1918: hr_utility.trace('chk_gre_archive - not found in cursor');
1919: g_archive_flag := 'N';
1914: if c_chk_payroll_action%FOUND then
1915: hr_utility.trace('chk_gre_archive - found in cursor');
1916: g_archive_flag := 'Y';
1917: else
1918: hr_utility.trace('chk_gre_archive - not found in cursor');
1919: g_archive_flag := 'N';
1920: end if;
1921:
1922: hr_utility.trace('chk_gre_archive - closing cursor');
1918: hr_utility.trace('chk_gre_archive - not found in cursor');
1919: g_archive_flag := 'N';
1920: end if;
1921:
1922: hr_utility.trace('chk_gre_archive - closing cursor');
1923: close c_chk_payroll_action;
1924: if g_archive_flag = 'Y' then
1925: hr_utility.trace('chk_gre_archive - returning true');
1926: return (TRUE);
1921:
1922: hr_utility.trace('chk_gre_archive - closing cursor');
1923: close c_chk_payroll_action;
1924: if g_archive_flag = 'Y' then
1925: hr_utility.trace('chk_gre_archive - returning true');
1926: return (TRUE);
1927: else
1928: hr_utility.trace('chk_gre_archive - returning false');
1929: return(FALSE);
1924: if g_archive_flag = 'Y' then
1925: hr_utility.trace('chk_gre_archive - returning true');
1926: return (TRUE);
1927: else
1928: hr_utility.trace('chk_gre_archive - returning false');
1929: return(FALSE);
1930: end if;
1931: end if;
1932: end chk_gre_archive;
1958: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1959: end if;
1960: close c_get_min_chunk;
1961:
1962: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1963: l_step := 2;
1964: l_archive := chk_gre_archive(p_payroll_action_id);
1965:
1966: l_step := 3;
1963: l_step := 2;
1964: l_archive := chk_gre_archive(p_payroll_action_id);
1965:
1966: l_step := 3;
1967: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1968: exception
1969: when others then
1970: raise_application_error(-20001,'eoy_archinit at '
1971: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2389:
2390:
2391: BEGIN
2392:
2393: --hr_utility.trace_on(null,'RL1');
2394: hr_utility.set_location ('archive_data',1);
2395: hr_utility.trace('getting assignment');
2396:
2397: l_negative_balance_exists := 'N';
2390:
2391: BEGIN
2392:
2393: --hr_utility.trace_on(null,'RL1');
2394: hr_utility.set_location ('archive_data',1);
2395: hr_utility.trace('getting assignment');
2396:
2397: l_negative_balance_exists := 'N';
2398: lv_qpp_pensionable_earnings := 0;
2391: BEGIN
2392:
2393: --hr_utility.trace_on(null,'RL1');
2394: hr_utility.set_location ('archive_data',1);
2395: hr_utility.trace('getting assignment');
2396:
2397: l_negative_balance_exists := 'N';
2398: lv_qpp_pensionable_earnings := 0;
2399: l_step := 1;
2418: archived then archive the gre data i.e. the employer data */
2419:
2420: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2421:
2422: hr_utility.trace('eoy_archive_data archiving employer data');
2423: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2424:
2425: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2426: legislative_parameters),
2419:
2420: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2421:
2422: hr_utility.trace('eoy_archive_data archiving employer data');
2423: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2424:
2425: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
2426: legislative_parameters),
2427: business_group_id
2431:
2432: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2433: p_pre_organization_id=>l_pre_organization_id);
2434:
2435: hr_utility.trace('eoy_archive_data archived employer data');
2436:
2437: end if;
2438:
2439: hr_utility.set_location ('archive_data',2);
2435: hr_utility.trace('eoy_archive_data archived employer data');
2436:
2437: end if;
2438:
2439: hr_utility.set_location ('archive_data',2);
2440:
2441: hr_utility.trace('assignment '|| to_char(l_asgid));
2442: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2437: end if;
2438:
2439: hr_utility.set_location ('archive_data',2);
2440:
2441: hr_utility.trace('assignment '|| to_char(l_asgid));
2442: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2444: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445:
2438:
2439: hr_utility.set_location ('archive_data',2);
2440:
2441: hr_utility.trace('assignment '|| to_char(l_asgid));
2442: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2444: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445:
2446: /* Derive the beginning and end of the effective year */
2439: hr_utility.set_location ('archive_data',2);
2440:
2441: hr_utility.trace('assignment '|| to_char(l_asgid));
2442: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2444: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445:
2446: /* Derive the beginning and end of the effective year */
2447:
2440:
2441: hr_utility.trace('assignment '|| to_char(l_asgid));
2442: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2443: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2444: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445:
2446: /* Derive the beginning and end of the effective year */
2447:
2448: hr_utility.trace('getting begin and end dates');
2444: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2445:
2446: /* Derive the beginning and end of the effective year */
2447:
2448: hr_utility.trace('getting begin and end dates');
2449:
2450: l_step := 2;
2451:
2452: l_year_start := trunc(p_effective_date, 'Y');
2451:
2452: l_year_start := trunc(p_effective_date, 'Y');
2453: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2454:
2455: hr_utility.trace('year start '|| to_char(l_year_start));
2456: hr_utility.trace('year end '|| to_char(l_year_end));
2457:
2458: if to_number(to_char(l_year_end,'YYYY')) > 2005 then
2459: lv_footnote_element := 'RL1 Non Box Footnotes';
2452: l_year_start := trunc(p_effective_date, 'Y');
2453: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2454:
2455: hr_utility.trace('year start '|| to_char(l_year_start));
2456: hr_utility.trace('year end '|| to_char(l_year_end));
2457:
2458: if to_number(to_char(l_year_end,'YYYY')) > 2005 then
2459: lv_footnote_element := 'RL1 Non Box Footnotes';
2460: else
2462: end if;
2463:
2464: /* Initialise the PL/SQL table before populating it */
2465:
2466: hr_utility.trace('Initialising Pl/SQL table');
2467:
2468: l_step := 3;
2469:
2470: /* Get the context_id for 'Jurisdiction' from ff_contexts */
2488: l_step := 12;
2489:
2490: l_count := l_count + 1;
2491:
2492: hr_utility.trace('archiving CAEOY_RL1_PROVINCE_OF_EMPLOYMENT');
2493:
2494: ff_archive_api.create_archive_item(
2495: /*p_validate => 'TRUE' */
2496: p_archive_item_id => l_archive_item_id
2503: ,p_object_version_number => l_object_version_number
2504: ,p_some_warning => l_some_warning
2505: );
2506:
2507: hr_utility.trace('archived caeoy_rl1_employment_province');
2508:
2509: /* We can archive the balance level dbis also because for employee level
2510: balances jurisdiction is always a context. */
2511:
2508:
2509: /* We can archive the balance level dbis also because for employee level
2510: balances jurisdiction is always a context. */
2511:
2512: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2513:
2514: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2515: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2516:
2513:
2514: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2515: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2516:
2517: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2518:
2519: /* RL1 Slip number generation */
2520:
2521: begin
2534: ppa.legislative_parameters )
2535: and ppa.payroll_action_id = l_payroll_action_id
2536: and hoi.org_information4 = 'P01';
2537:
2538: hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2539:
2540: hr_utility.trace('3');
2541:
2542: if l_transmitter_name1 is not null then
2536: and hoi.org_information4 = 'P01';
2537:
2538: hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2539:
2540: hr_utility.trace('3');
2541:
2542: if l_transmitter_name1 is not null then
2543:
2544: select to_number(target.ORG_INFORMATION18)
2548: and target.org_information_context = 'Prov Reporting Est'
2549: and target.ORG_INFORMATION3 = 'Y';
2550:
2551: else
2552: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2553: hr_utility.set_message_token('ORGIND','PRE');
2554: hr_utility.raise_error;
2555: end if;
2556:
2549: and target.ORG_INFORMATION3 = 'Y';
2550:
2551: else
2552: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2553: hr_utility.set_message_token('ORGIND','PRE');
2554: hr_utility.raise_error;
2555: end if;
2556:
2557: hr_utility.trace('2');
2550:
2551: else
2552: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2553: hr_utility.set_message_token('ORGIND','PRE');
2554: hr_utility.raise_error;
2555: end if;
2556:
2557: hr_utility.trace('2');
2558:
2553: hr_utility.set_message_token('ORGIND','PRE');
2554: hr_utility.raise_error;
2555: end if;
2556:
2557: hr_utility.trace('2');
2558:
2559: select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2560: into l_rl1_curr_slip_number from dual;
2561:
2558:
2559: select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2560: into l_rl1_curr_slip_number from dual;
2561:
2562: hr_utility.trace('1');
2563:
2564: select mod(l_rl1_curr_slip_number,7)
2565: into l_rl1_slip_number_last_digit
2566: from dual;
2564: select mod(l_rl1_curr_slip_number,7)
2565: into l_rl1_slip_number_last_digit
2566: from dual;
2567:
2568: hr_utility.trace('l_rl1_slip_number_last_digit : '||
2569: l_rl1_slip_number_last_digit);
2570:
2571: l_rl1_slip_number := (l_rl1_curr_slip_number)||
2572: l_rl1_slip_number_last_digit;
2570:
2571: l_rl1_slip_number := (l_rl1_curr_slip_number)||
2572: l_rl1_slip_number_last_digit;
2573:
2574: hr_utility.trace('l_rl1_slip_number : ' || l_rl1_slip_number);
2575:
2576: hr_utility.trace('l_rl1_curr_slip_number : '||l_rl1_curr_slip_number);
2577: end;
2578:
2572: l_rl1_slip_number_last_digit;
2573:
2574: hr_utility.trace('l_rl1_slip_number : ' || l_rl1_slip_number);
2575:
2576: hr_utility.trace('l_rl1_curr_slip_number : '||l_rl1_curr_slip_number);
2577: end;
2578:
2579: l_count := 0;
2580:
2806: l_count := l_count + 1;
2807: l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXU_PER_JD_YTD';
2808: l_balance_type_tab(l_count) := 'RL1_BOXU';
2809:
2810: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2811:
2812: for i in 1 .. l_count
2813: loop
2814: hr_utility.trace('Initialising values');
2810: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2811:
2812: for i in 1 .. l_count
2813: loop
2814: hr_utility.trace('Initialising values');
2815: l_user_entity_value_tab(i) := 0;
2816: end loop;
2817:
2818: open c_all_gres(p_assactid);
2818: open c_all_gres(p_assactid);
2819:
2820: loop
2821:
2822: hr_utility.trace('Fetching all GREs');
2823: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
2824: exit when c_all_gres%NOTFOUND;
2825:
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2822: hr_utility.trace('Fetching all GREs');
2823: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
2824: exit when c_all_gres%NOTFOUND;
2825:
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828: hr_utility.trace('Person id is ' || lv_serial_number);
2829: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2823: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
2824: exit when c_all_gres%NOTFOUND;
2825:
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828: hr_utility.trace('Person id is ' || lv_serial_number);
2829: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2831:
2824: exit when c_all_gres%NOTFOUND;
2825:
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828: hr_utility.trace('Person id is ' || lv_serial_number);
2829: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2831:
2832: begin
2825:
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828: hr_utility.trace('Person id is ' || lv_serial_number);
2829: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2831:
2832: begin
2833: /* Removed select stmt to get max asgact_id and replaced it with
2826: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2827: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2828: hr_utility.trace('Person id is ' || lv_serial_number);
2829: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2830: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2831:
2832: begin
2833: /* Removed select stmt to get max asgact_id and replaced it with
2834: cursor c_get_max_asgactid_jd. 11510 Changes Bug#3356533.
2840: l_year_end);
2841: fetch c_get_max_asgactid_jd into l_aaid;
2842: close c_get_max_asgactid_jd;
2843:
2844: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2845: hr_utility.trace('l_count is ' || to_char(l_count));
2846:
2847: ln_no_gross_earnings := ln_no_gross_earnings +
2848: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2841: fetch c_get_max_asgactid_jd into l_aaid;
2842: close c_get_max_asgactid_jd;
2843:
2844: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2845: hr_utility.trace('l_count is ' || to_char(l_count));
2846:
2847: ln_no_gross_earnings := ln_no_gross_earnings +
2848: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2849: ('RL1 No Gross Earnings',
2866:
2867: if l_tax_unit_id <> l_prev_tax_unit_id or
2868: l_prev_tax_unit_id is null then
2869:
2870: hr_utility.trace('l_business_group_id is '||l_business_group_id);
2871:
2872: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2873: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2874: Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
2875:
2876: for i in 1 .. l_count
2877: loop
2878:
2879: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
2880: hr_utility.trace('i is ' || i);
2881:
2882: /* T4A earnings should not go to BOX A of RL1 */
2883:
2876: for i in 1 .. l_count
2877: loop
2878:
2879: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
2880: hr_utility.trace('i is ' || i);
2881:
2882: /* T4A earnings should not go to BOX A of RL1 */
2883:
2884: /* bug 5768390
2918: l_business_group_id,
2919: 'QC'
2920: ),0);
2921:
2922: hr_utility.trace('Fed Result = ' || fed_result);
2923: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2924: else
2925: fed_result := 0;
2926: non_taxable_earnings := 0;
2919: 'QC'
2920: ),0);
2921:
2922: hr_utility.trace('Fed Result = ' || fed_result);
2923: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2924: else
2925: fed_result := 0;
2926: non_taxable_earnings := 0;
2927: hr_utility.trace('Fed Result = ' || fed_result);
2923: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2924: else
2925: fed_result := 0;
2926: non_taxable_earnings := 0;
2927: hr_utility.trace('Fed Result = ' || fed_result);
2928: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2929: end if;
2930:
2931: ln_balance_value :=
2924: else
2925: fed_result := 0;
2926: non_taxable_earnings := 0;
2927: hr_utility.trace('Fed Result = ' || fed_result);
2928: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
2929: end if;
2930:
2931: ln_balance_value :=
2932: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2945: if l_balance_type_tab(i) = 'QPP EE Taxable' then
2946: lv_qpp_pensionable_earnings := lv_qpp_pensionable_earnings + ln_balance_value;
2947: end if;
2948:
2949: hr_utility.trace('Balance value is '|| ln_balance_value);
2950:
2951: if ln_balance_value <> 0 then
2952: l_has_been_paid := 'Y';
2953: if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
2968: end if;
2969: bug 5768390 */
2970:
2971:
2972: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975: hr_utility.trace('Assignemnt ID '|| l_asgid);
2976: hr_utility.trace('ln_boxo_exclude_from_boxa '||
2969: bug 5768390 */
2970:
2971:
2972: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975: hr_utility.trace('Assignemnt ID '|| l_asgid);
2976: hr_utility.trace('ln_boxo_exclude_from_boxa '||
2977: ln_boxo_exclude_from_boxa);
2970:
2971:
2972: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975: hr_utility.trace('Assignemnt ID '|| l_asgid);
2976: hr_utility.trace('ln_boxo_exclude_from_boxa '||
2977: ln_boxo_exclude_from_boxa);
2978:
2971:
2972: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975: hr_utility.trace('Assignemnt ID '|| l_asgid);
2976: hr_utility.trace('ln_boxo_exclude_from_boxa '||
2977: ln_boxo_exclude_from_boxa);
2978:
2979: end if;
2972: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
2973: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
2974: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
2975: hr_utility.trace('Assignemnt ID '|| l_asgid);
2976: hr_utility.trace('ln_boxo_exclude_from_boxa '||
2977: ln_boxo_exclude_from_boxa);
2978:
2979: end if;
2980: l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
2985: /* bug 5768390
2986: end if;
2987: bug 5768390 */
2988:
2989: hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
2990: l_prev_tax_unit_id := l_tax_unit_id ;
2991:
2992: end loop;
2993: end if;
2993: end if;
2994:
2995: exception
2996: when no_data_found then
2997: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2998: end;
2999: end loop;
3000: close c_all_gres;
3001:
2998: end;
2999: end loop;
3000: close c_all_gres;
3001:
3002: hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3003:
3004: if ((l_no_of_payroll_run > 0) and
3005: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3006:
3003:
3004: if ((l_no_of_payroll_run > 0) and
3005: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3006:
3007: -- hr_utility.trace_on('Y','RL1');
3008: for i in 1 .. l_count
3009: loop
3010:
3011: hr_utility.trace('in the create_archive_item loop');
3007: -- hr_utility.trace_on('Y','RL1');
3008: for i in 1 .. l_count
3009: loop
3010:
3011: hr_utility.trace('in the create_archive_item loop');
3012: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3013: hr_utility.trace('archive value is ');
3014:
3015: /* Archiving footnotes */
3008: for i in 1 .. l_count
3009: loop
3010:
3011: hr_utility.trace('in the create_archive_item loop');
3012: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3013: hr_utility.trace('archive value is ');
3014:
3015: /* Archiving footnotes */
3016:
3009: loop
3010:
3011: hr_utility.trace('in the create_archive_item loop');
3012: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3013: hr_utility.trace('archive value is ');
3014:
3015: /* Archiving footnotes */
3016:
3017: old_l_footnote_code := NULL;
3016:
3017: old_l_footnote_code := NULL;
3018: old_balance_type_tab := NULL;
3019:
3020: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3021: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3022:
3023: if l_user_entity_value_tab(i) <> 0 then
3024:
3017: old_l_footnote_code := NULL;
3018: old_balance_type_tab := NULL;
3019:
3020: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3021: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3022:
3023: if l_user_entity_value_tab(i) <> 0 then
3024:
3025: if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3062: fetch c_footnote_info into l_footnote_code,
3063: l_footnote_balance;
3064: exit when c_footnote_info%NOTFOUND;
3065:
3066: hr_utility.trace('l_footnote_amount_balance is '||
3067: l_footnote_balance);
3068: hr_utility.trace('l_footnote_code is '||
3069: l_footnote_code);
3070: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3064: exit when c_footnote_info%NOTFOUND;
3065:
3066: hr_utility.trace('l_footnote_amount_balance is '||
3067: l_footnote_balance);
3068: hr_utility.trace('l_footnote_code is '||
3069: l_footnote_code);
3070: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3071: l_footnote_amount_ue);
3072:
3066: hr_utility.trace('l_footnote_amount_balance is '||
3067: l_footnote_balance);
3068: hr_utility.trace('l_footnote_code is '||
3069: l_footnote_code);
3070: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3071: l_footnote_amount_ue);
3072:
3073: /* Must ensure that BOXR is only used with footnote code 14 */
3074: l_boxr_flag := 'Y';
3081:
3082: if ( l_footnote_code <> old_l_footnote_code or
3083: old_l_footnote_code is null )
3084: then
3085: hr_utility.trace('old_l_footnote_code is '||
3086: nvl(old_l_footnote_code,'NULL'));
3087: if old_l_footnote_code is not null then
3088:
3089: l_footnote_amount_ue := 'CAEOY_' ||old_balance_type_tab
3119: old_balance_type_tab := l_footnote_balance_type_tab ;
3120: l_footnote_amount_ue := 'CAEOY_' ||
3121: l_footnote_balance_type_tab||
3122: '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3123: hr_utility.trace('l_footnote_amount_ue is '||
3124: l_footnote_amount_ue);
3125: end if;
3126:
3127: l_footnote_amount_ue := 'CAEOY_' ||
3131:
3132: /* get the footnote_balance */
3133: open c_all_gres_for_footnote(p_assactid);
3134: loop
3135: hr_utility.trace('Fetching all GREs');
3136: fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3137: l_ft_reporting_type;
3138: exit when c_all_gres_for_footnote%NOTFOUND;
3139:
3136: fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3137: l_ft_reporting_type;
3138: exit when c_all_gres_for_footnote%NOTFOUND;
3139:
3140: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3141: hr_utility.trace('Asgid is ' || l_asgid);
3142: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3143: hr_utility.trace('Effective date is '|| p_effective_date);
3144: begin
3137: l_ft_reporting_type;
3138: exit when c_all_gres_for_footnote%NOTFOUND;
3139:
3140: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3141: hr_utility.trace('Asgid is ' || l_asgid);
3142: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3143: hr_utility.trace('Effective date is '|| p_effective_date);
3144: begin
3145: /* Removed select stmt to get max asgact_id and replaced
3138: exit when c_all_gres_for_footnote%NOTFOUND;
3139:
3140: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3141: hr_utility.trace('Asgid is ' || l_asgid);
3142: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3143: hr_utility.trace('Effective date is '|| p_effective_date);
3144: begin
3145: /* Removed select stmt to get max asgact_id and replaced
3146: it with cursor c_get_max_asgactid_jd, reusing the same
3139:
3140: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3141: hr_utility.trace('Asgid is ' || l_asgid);
3142: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3143: hr_utility.trace('Effective date is '|| p_effective_date);
3144: begin
3145: /* Removed select stmt to get max asgact_id and replaced
3146: it with cursor c_get_max_asgactid_jd, reusing the same
3147: cursor used above. 11510 Changes Bug#3356533. Changed
3153: l_year_end);
3154: fetch c_get_max_asgactid_jd into l_ft_aaid;
3155: close c_get_max_asgactid_jd;
3156:
3157: hr_utility.trace('l_aaid is ' || l_ft_aaid);
3158: hr_utility.trace('l_count is ' || l_count);
3159:
3160: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3161:
3154: fetch c_get_max_asgactid_jd into l_ft_aaid;
3155: close c_get_max_asgactid_jd;
3156:
3157: hr_utility.trace('l_aaid is ' || l_ft_aaid);
3158: hr_utility.trace('l_count is ' || l_count);
3159:
3160: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3161:
3162: select target1.business_group_id
3166:
3167: if ( l_ft_tax_unit_id <> l_prev_tax_unit_id or
3168: l_prev_tax_unit_id is null )
3169: then
3170: hr_utility.trace('l_business_group_id is ' ||
3171: l_business_group_id);
3172:
3173: pay_balance_pkg.set_context('TAX_UNIT_ID',
3174: l_ft_tax_unit_id);
3192:
3193: l_prev_tax_unit_id := l_ft_tax_unit_id ;
3194: exception
3195: when no_data_found then
3196: hr_utility.trace('This Tax unit id has no payroll run,'||
3197: ' so skip it');
3198: end;
3199: end loop;
3200: close c_all_gres_for_footnote;
3211:
3212: end loop; /* c_footnote_info loop */
3213: close c_footnote_info;
3214:
3215: hr_utility.trace('before archiving l_footnote_amount_ue is '||
3216: l_footnote_amount_ue);
3217:
3218: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3219: and l_footnote_amount <> 0 then
3245:
3246: /* End of footnote archiving */
3247:
3248: /* archive the box balances */
3249: hr_utility.trace('here1');
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3246: /* End of footnote archiving */
3247:
3248: /* archive the box balances */
3249: hr_utility.trace('here1');
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3247:
3248: /* archive the box balances */
3249: hr_utility.trace('here1');
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3248: /* archive the box balances */
3249: hr_utility.trace('here1');
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3256: ff_archive_api.create_archive_item(
3249: hr_utility.trace('here1');
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3256: ff_archive_api.create_archive_item(
3257: /* p_validate => 'TRUE' */
3250: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3256: ff_archive_api.create_archive_item(
3257: /* p_validate => 'TRUE' */
3258: p_archive_item_id => l_archive_item_id
3251: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
3252: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
3253: hr_utility.trace('p_assactid ='|| p_assactid);
3254: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
3255: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
3256: ff_archive_api.create_archive_item(
3257: /* p_validate => 'TRUE' */
3258: p_archive_item_id => l_archive_item_id
3259: ,p_user_entity_id =>
3266: ,p_context_name1 => 'JURISDICTION_CODE'
3267: ,p_context1 => 'QC'
3268: ,p_some_warning => l_some_warning
3269: );
3270: hr_utility.trace('after the call');
3271: if l_user_entity_value_tab(i) < 0 then
3272: l_negative_balance_exists := 'Y';
3273: end if;
3274:
3362: lv_cpp_pensionable_earnings := 0;
3363: lv_taxable_benefit_with_no_rem := 0;
3364:
3365: loop
3366: hr_utility.trace('Fetching all GREs for the person');
3367: fetch c_all_gres_for_person into l_tax_unit_id;
3368: exit when c_all_gres_for_person%NOTFOUND;
3369:
3370: begin
3417:
3418: end;
3419: end loop;
3420: close c_all_gres_for_person;
3421: hr_utility.trace('closed all GREs for the person');
3422:
3423: ff_archive_api.create_archive_item(
3424: /* p_validate => 'TRUE' */
3425: p_archive_item_id => l_archive_item_id
3432: ,p_object_version_number => l_object_version_number
3433: ,p_some_warning => l_some_warning);
3434:
3435: /* End of CPP archiving */
3436: --hr_utility.trace_off;
3437: end if;
3438:
3439: hr_utility.trace('Out of province loop ');
3440:
3435: /* End of CPP archiving */
3436: --hr_utility.trace_off;
3437: end if;
3438:
3439: hr_utility.trace('Out of province loop ');
3440:
3441: /* Archiving of Non-Box Footnotes */
3442: begin
3443: --hr_utility.trace_on('Y','NONBOX');
3439: hr_utility.trace('Out of province loop ');
3440:
3441: /* Archiving of Non-Box Footnotes */
3442: begin
3443: --hr_utility.trace_on('Y','NONBOX');
3444:
3445: /* Archive Nonbox footnote for Taxable Benefits that are processed on their
3446: own if the total pensionable earnings is less than the maximum bug# 3369317 */
3447:
3520: if cur_non_box_mesg%notfound then
3521: exit;
3522: end if;
3523:
3524: hr_utility.trace('l_messages - '||l_messages);
3525: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3526:
3527: /* If the same Non Box footnote is processed more than
3528: once during the year, then the sum of the associated
3521: exit;
3522: end if;
3523:
3524: hr_utility.trace('l_messages - '||l_messages);
3525: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3526:
3527: /* If the same Non Box footnote is processed more than
3528: once during the year, then the sum of the associated
3529: amounts is archived */
3530:
3531: if ((l_messages <> l_prev_messages) and
3532: (l_prev_messages is not null)) then
3533:
3534: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3535:
3536: if l_total_mesg_amt <> 0 then
3537:
3538: pay_action_information_api.create_action_information(
3587: else
3588: l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
3589: end if;
3590:
3591: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3592:
3593: l_prev_messages := l_messages;
3594: ln_prev_tax_unit_id := ln_tax_unit_id;
3595: ld_prev_eff_date := ld_eff_date;
3599: close cur_non_box_mesg;
3600:
3601: if (l_prev_messages is not null) then
3602:
3603: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3604: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3605:
3606: if l_total_mesg_amt <> 0 then
3607:
3600:
3601: if (l_prev_messages is not null) then
3602:
3603: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3604: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3605:
3606: if l_total_mesg_amt <> 0 then
3607:
3608: pay_action_information_api.create_action_information(
3654: end if;
3655:
3656: end if;
3657:
3658: --hr_utility.trace_off;
3659: end;
3660:
3661: ff_archive_api.create_archive_item(
3662: p_archive_item_id => l_archive_item_id
3779: l_termination_date := null;
3780:
3781: end;
3782:
3783: hr_utility.trace('Before counter of asgid '|| l_asgid);
3784:
3785: l_counter := l_counter + 1;
3786: l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
3787: l_user_entity_value_tab(l_counter):= l_person_id;
3789: l_counter := l_counter + 1;
3790: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
3791: l_user_entity_value_tab(l_counter):= l_first_name;
3792:
3793: hr_utility.trace('Before counter 2');
3794: l_counter := l_counter + 1;
3795: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
3796: l_user_entity_value_tab(l_counter):= l_last_name ;
3797:
3794: l_counter := l_counter + 1;
3795: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
3796: l_user_entity_value_tab(l_counter):= l_last_name ;
3797:
3798: hr_utility.trace('Before counter 3');
3799: l_counter := l_counter + 1;
3800: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
3801: l_user_entity_value_tab(l_counter):= l_middle_name ;
3802:
3799: l_counter := l_counter + 1;
3800: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
3801: l_user_entity_value_tab(l_counter):= l_middle_name ;
3802:
3803: hr_utility.trace('Before counter 3');
3804: l_counter := l_counter + 1;
3805: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
3806: l_user_entity_value_tab(l_counter):= l_national_identifier;
3807:
3804: l_counter := l_counter + 1;
3805: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
3806: l_user_entity_value_tab(l_counter):= l_national_identifier;
3807:
3808: hr_utility.trace('Before counter 3');
3809: l_counter := l_counter + 1;
3810: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
3811: l_user_entity_value_tab(l_counter):=
3812: fnd_date.date_to_canonical(l_date_of_birth);
3810: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
3811: l_user_entity_value_tab(l_counter):=
3812: fnd_date.date_to_canonical(l_date_of_birth);
3813:
3814: hr_utility.trace('Before counter 3');
3815: l_counter := l_counter + 1;
3816: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
3817: l_user_entity_value_tab(l_counter):=
3818: fnd_date.date_to_canonical(l_hire_date);
3816: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
3817: l_user_entity_value_tab(l_counter):=
3818: fnd_date.date_to_canonical(l_hire_date);
3819:
3820: hr_utility.trace('Before counter 3');
3821: l_counter := l_counter + 1;
3822: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
3823: l_user_entity_value_tab(l_counter):=
3824: fnd_date.date_to_canonical(l_termination_date);
3822: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
3823: l_user_entity_value_tab(l_counter):=
3824: fnd_date.date_to_canonical(l_termination_date);
3825:
3826: hr_utility.trace('Before counter 3');
3827: l_counter := l_counter + 1;
3828: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
3829: l_user_entity_value_tab(l_counter):= l_employee_number;
3830:
3830:
3831: for i in 1 .. l_counter
3832: loop
3833:
3834: hr_utility.trace('inside create loop '||l_user_entity_value_tab(i));
3835:
3836: ff_archive_api.create_archive_item(
3837: /* p_validate => 'TRUE' */
3838: p_archive_item_id => l_archive_item_id
3925: end if;
3926:
3927: Begin
3928:
3929: hr_utility.trace('Started Provincial YE Amendment PP Validation ');
3930: select to_char(effective_date,'YYYY'),
3931: report_type,
3932: to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID'
3933: ,legislative_parameters))
3936: ln_fapp_pre_org_id
3937: from pay_payroll_actions
3938: where payroll_action_id = l_payroll_action_id;
3939:
3940: hr_utility.trace('Prov Amend Pre-Process Pactid :'||
3941: to_char(l_payroll_action_id));
3942: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3943:
3944: /* Archive the Pre-Printed form number for the RL1 YEPP
3938: where payroll_action_id = l_payroll_action_id;
3939:
3940: hr_utility.trace('Prov Amend Pre-Process Pactid :'||
3941: to_char(l_payroll_action_id));
3942: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3943:
3944: /* Archive the Pre-Printed form number for the RL1 YEPP
3945: and Amendment Pre-Process if one exists*/
3946:
4002: open c_get_fapp_locked_action_id(p_assactid);
4003: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4004: close c_get_fapp_locked_action_id;
4005:
4006: hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
4007: hr_utility.trace('ln_fapp_locked_action_id :'||
4008: to_char(ln_fapp_locked_action_id));
4009: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4010: fetch c_get_fapp_lkd_actid_rtype
4003: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4004: close c_get_fapp_locked_action_id;
4005:
4006: hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
4007: hr_utility.trace('ln_fapp_locked_action_id :'||
4008: to_char(ln_fapp_locked_action_id));
4009: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4010: fetch c_get_fapp_lkd_actid_rtype
4011: into lv_fapp_locked_actid_reptype;
4009: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4010: fetch c_get_fapp_lkd_actid_rtype
4011: into lv_fapp_locked_actid_reptype;
4012: close c_get_fapp_lkd_actid_rtype;
4013: hr_utility.trace('lv_fapp_locked_actid_reptype :'||
4014: lv_fapp_locked_actid_reptype);
4015:
4016: open c_get_fapp_prov_emp(p_assactid);
4017: loop
4016: open c_get_fapp_prov_emp(p_assactid);
4017: loop
4018: fetch c_get_fapp_prov_emp into lv_fapp_prov;
4019: exit when c_get_fapp_prov_emp%NOTFOUND;
4020: hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
4021:
4022: lv_fapp_flag := compare_archive_data(p_assactid,
4023: ln_fapp_locked_action_id,
4024: lv_fapp_prov);
4024: lv_fapp_prov);
4025:
4026: if lv_fapp_flag = 'Y' then
4027:
4028: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4029: hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
4030:
4031: ff_archive_api.create_archive_item(
4032: p_archive_item_id => l_archive_item_id
4025:
4026: if lv_fapp_flag = 'Y' then
4027:
4028: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4029: hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
4030:
4031: ff_archive_api.create_archive_item(
4032: p_archive_item_id => l_archive_item_id
4033: ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_AMENDMENT_FLAG')
4050:
4051: end; -- report_type validation
4052:
4053: END IF; -- report type validation for FAPP
4054: hr_utility.trace('End of Provincial YE Amendment PP Validation');
4055:
4056: Exception when no_data_found then
4057: hr_utility.trace('Report type not found for given Payroll_action ');
4058: null;
4053: END IF; -- report type validation for FAPP
4054: hr_utility.trace('End of Provincial YE Amendment PP Validation');
4055:
4056: Exception when no_data_found then
4057: hr_utility.trace('Report type not found for given Payroll_action ');
4058: null;
4059: End;
4060: -- End of Provincial YE Amendment Pre-Process Validation
4061:
4132: order by asg.person_id';
4133:
4134: l_archive := chk_gre_archive(pactid);
4135: if g_archive_flag = 'N' then
4136: hr_utility.trace('eoy_range_cursor archiving employer data');
4137: /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
4138: eoy_archive_gre_data(pactid,
4139: l_pre_organization_id);
4140: hr_utility.trace('eoy_range_cursor archived employer data');
4136: hr_utility.trace('eoy_range_cursor archiving employer data');
4137: /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
4138: eoy_archive_gre_data(pactid,
4139: l_pre_organization_id);
4140: hr_utility.trace('eoy_range_cursor archived employer data');
4141: end if;
4142:
4143: end eoy_range_cursor;
4144: