[Home] [Help]
390: open csr_defined_balance;
391: fetch csr_defined_balance into l_defined_balance_id;
392: if csr_defined_balance%notfound then
393: close csr_defined_balance;
394: raise hr_utility.hr_error;
395: else
396: close csr_defined_balance;
397: end if;
398:
503: if p_report_type = 'RL1' then
504:
505: /* Default settings for Year End Preprocess. */
506:
507: hr_utility.trace('in getting selection information ');
508: p_period_start := p_year_start;
509: p_period_end := p_year_end;
510: /* p_defined_balance_id := bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'); */
511: p_defined_balance_id := 0;
518:
519: /* An invalid report type has been passed so fail. */
520:
521: else
522: hr_utility.trace('in error of getting selection information ');
523:
524: raise hr_utility.hr_error;
525:
526: end if;
520:
521: else
522: hr_utility.trace('in error of getting selection information ');
523:
524: raise hr_utility.hr_error;
525:
526: end if;
527:
528: end get_selection_information;
554: open csr_jurisdiction_code;
555: fetch csr_jurisdiction_code into l_jurisdiction_code;
556: if csr_jurisdiction_code%notfound then
557: close csr_jurisdiction_code;
558: raise hr_utility.hr_error;
559: else
560: close csr_jurisdiction_code;
561: end if;
562:
586: return l_user_entity_id;
587:
588: exception
589: when others then
590: hr_utility.trace('Error while getting the user_entity_id'
591: || p_dbi_name);
592: raise hr_utility.hr_error;
593:
594: end get_user_entity_id;
588: exception
589: when others then
590: hr_utility.trace('Error while getting the user_entity_id'
591: || p_dbi_name);
592: raise hr_utility.hr_error;
593:
594: end get_user_entity_id;
595:
596:
618: return l_user_entity_id;
619:
620: exception
621: when others then
622: hr_utility.trace('skipping the record because no dbi of name:'
623: || p_dbi_name);
624: return 0;
625: end;
626: end if;
787: ltr_emp_ue_id(j) := get_user_entity_id('CAEOY_CPP_EE_WITHHELD_PER_YTD');
788:
789: /* Populate RL1 Amendment Box Data for an assignment_action */
790: open c_get_emp_rl1box_data(p_assignment_action_id);
791: hr_utility.trace('Populating RL1 Amendment Box Data ');
792: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
793: ln_amend_box := 0;
794: loop
795: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
788:
789: /* Populate RL1 Amendment Box Data for an assignment_action */
790: open c_get_emp_rl1box_data(p_assignment_action_id);
791: hr_utility.trace('Populating RL1 Amendment Box Data ');
792: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
793: ln_amend_box := 0;
794: loop
795: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
796: exit when c_get_emp_rl1box_data%NOTFOUND;
794: loop
795: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
796: exit when c_get_emp_rl1box_data%NOTFOUND;
797:
798: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
799: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
800: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
801: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
802: ln_amend_box := ln_amend_box + 1;
795: fetch c_get_emp_rl1box_data into ltr_amend_arch_data(ln_amend_box);
796: exit when c_get_emp_rl1box_data%NOTFOUND;
797:
798: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
799: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
800: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
801: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
802: ln_amend_box := ln_amend_box + 1;
803: end loop;
796: exit when c_get_emp_rl1box_data%NOTFOUND;
797:
798: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
799: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
800: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
801: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
802: ln_amend_box := ln_amend_box + 1;
803: end loop;
804:
797:
798: hr_utility.trace('ln_amend_box :'||to_char(ln_amend_box));
799: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_context1));
800: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box).archive_ue_id));
801: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box).archive_value);
802: ln_amend_box := ln_amend_box + 1;
803: end loop;
804:
805: close c_get_emp_rl1box_data;
804:
805: close c_get_emp_rl1box_data;
806:
807: /* Populate RL1 Amendment Employee Data for an assignment_action */
808: hr_utility.trace('Populating Amendment Employee Data ');
809: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
810: for i in 0 .. j
811: loop
812: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
805: close c_get_emp_rl1box_data;
806:
807: /* Populate RL1 Amendment Employee Data for an assignment_action */
808: hr_utility.trace('Populating Amendment Employee Data ');
809: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
810: for i in 0 .. j
811: loop
812: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
813: fetch c_get_employee_data into ltr_amend_emp_data(i);
811: loop
812: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
813: fetch c_get_employee_data into ltr_amend_emp_data(i);
814:
815: hr_utility.trace('I :'||to_char(i));
816: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
817: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
818: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
819:
812: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
813: fetch c_get_employee_data into ltr_amend_emp_data(i);
814:
815: hr_utility.trace('I :'||to_char(i));
816: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
817: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
818: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
819:
820: close c_get_employee_data;
813: fetch c_get_employee_data into ltr_amend_emp_data(i);
814:
815: hr_utility.trace('I :'||to_char(i));
816: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
817: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
818: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
819:
820: close c_get_employee_data;
821: end loop;
814:
815: hr_utility.trace('I :'||to_char(i));
816: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
817: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
818: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
819:
820: close c_get_employee_data;
821: end loop;
822:
822:
823:
824: /* Populate RL1 YEPP Box Data for an assignment_action */
825: open c_get_emp_rl1box_data(p_locked_action_id);
826: hr_utility.trace('Populating RL1 YEPP Box Data ');
827: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
828: ln_box := 0;
829: loop
830: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
823:
824: /* Populate RL1 YEPP Box Data for an assignment_action */
825: open c_get_emp_rl1box_data(p_locked_action_id);
826: hr_utility.trace('Populating RL1 YEPP Box Data ');
827: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
828: ln_box := 0;
829: loop
830: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
831: exit when c_get_emp_rl1box_data%NOTFOUND;
829: loop
830: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
831: exit when c_get_emp_rl1box_data%NOTFOUND;
832:
833: hr_utility.trace('ln_box :'||to_char(ln_box));
834: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
835: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
836: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
837: ln_box := ln_box + 1;
830: fetch c_get_emp_rl1box_data into ltr_yepp_arch_data(ln_box);
831: exit when c_get_emp_rl1box_data%NOTFOUND;
832:
833: hr_utility.trace('ln_box :'||to_char(ln_box));
834: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
835: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
836: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
837: ln_box := ln_box + 1;
838: end loop;
831: exit when c_get_emp_rl1box_data%NOTFOUND;
832:
833: hr_utility.trace('ln_box :'||to_char(ln_box));
834: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
835: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
836: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
837: ln_box := ln_box + 1;
838: end loop;
839:
832:
833: hr_utility.trace('ln_box :'||to_char(ln_box));
834: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_box).archive_context1));
835: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_box).archive_ue_id));
836: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_box).archive_value);
837: ln_box := ln_box + 1;
838: end loop;
839:
840: close c_get_emp_rl1box_data;
839:
840: close c_get_emp_rl1box_data;
841:
842: /* Populate RL1 YEPP Employee Data for an assignment_action */
843: hr_utility.trace('Populating YEPP Employee Data ');
844: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
845:
846: for i in 0 .. j
847: loop
840: close c_get_emp_rl1box_data;
841:
842: /* Populate RL1 YEPP Employee Data for an assignment_action */
843: hr_utility.trace('Populating YEPP Employee Data ');
844: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
845:
846: for i in 0 .. j
847: loop
848: open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
848: open c_get_employee_data(p_locked_action_id, ltr_emp_ue_id(i));
849: fetch c_get_employee_data into ltr_yepp_emp_data(i);
850: exit when c_get_employee_data%NOTFOUND;
851:
852: hr_utility.trace('I :'||to_char(i));
853: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
854: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
855: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
856:
849: fetch c_get_employee_data into ltr_yepp_emp_data(i);
850: exit when c_get_employee_data%NOTFOUND;
851:
852: hr_utility.trace('I :'||to_char(i));
853: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
854: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
855: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
856:
857: close c_get_employee_data;
850: exit when c_get_employee_data%NOTFOUND;
851:
852: hr_utility.trace('I :'||to_char(i));
853: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
854: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
855: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
856:
857: close c_get_employee_data;
858: end loop;
851:
852: hr_utility.trace('I :'||to_char(i));
853: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
854: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
855: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
856:
857: close c_get_employee_data;
858: end loop;
859:
859:
860: /* Populate RL1 Amendment Footnotes */
861: open c_get_nonbox_footnote(p_assignment_action_id);
862:
863: hr_utility.trace('Populating RL1 Amendment Footnote ');
864:
865: ln_amend_footnote_count := 0;
866: loop
867: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
866: loop
867: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
868: exit when c_get_nonbox_footnote%NOTFOUND;
869:
870: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
871: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
872:
873: ln_amend_footnote_count := ln_amend_footnote_count + 1;
874: end loop;
867: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
868: exit when c_get_nonbox_footnote%NOTFOUND;
869:
870: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
871: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
872:
873: ln_amend_footnote_count := ln_amend_footnote_count + 1;
874: end loop;
875:
882: loop
883: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
884: exit when c_get_nonbox_footnote%NOTFOUND;
885:
886: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
887: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
888:
889: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
890: end loop;
883: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
884: exit when c_get_nonbox_footnote%NOTFOUND;
885:
886: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
887: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
888:
889: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
890: end loop;
891:
894:
895: /* Compare RL1 Amendment Box Data and RL1 YEPP Box Data for an
896: assignment_action */
897:
898: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Box Data ');
899:
900: if ln_box <> ln_amend_box then
901: lv_flag := 'Y';
902: elsif ln_box = ln_amend_box then
912: (ltr_yepp_arch_data(i).archive_value is not null and
913: ltr_amend_arch_data(i).archive_value is null)) then
914:
915: lv_flag := 'Y';
916: hr_utility.trace('Archive_UE_id with differnt value :'||
917: to_char(ltr_yepp_arch_data(i).archive_ue_id));
918: exit;
919: end if;
920:
927: /* Compare RL1 Employee Data and RL1 YEPP Employee Data for an
928: assignment_action */
929: If lv_flag <> 'Y' then
930:
931: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Employee Data ');
932: for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
933: loop
934: if (ltr_yepp_emp_data(i).archive_ue_id =
935: ltr_amend_emp_data(i).archive_ue_id) then
933: loop
934: if (ltr_yepp_emp_data(i).archive_ue_id =
935: ltr_amend_emp_data(i).archive_ue_id) then
936:
937: hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
938: ltr_yepp_emp_data(i).archive_value);
939: hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
940: ltr_amend_emp_data(i).archive_value);
941:
935: ltr_amend_emp_data(i).archive_ue_id) then
936:
937: hr_utility.trace('ltr_yepp_emp_data(i).archive_value : '||
938: ltr_yepp_emp_data(i).archive_value);
939: hr_utility.trace('ltr_amend_emp_data(i).archive_value: '||
940: ltr_amend_emp_data(i).archive_value);
941:
942: if ((ltr_yepp_emp_data(i).archive_value <>
943: ltr_amend_emp_data(i).archive_value) or
946: (ltr_yepp_emp_data(i).archive_value is not null and
947: ltr_amend_emp_data(i).archive_value is null)) then
948:
949: lv_flag := 'Y';
950: hr_utility.trace('Archive_UE_id with different value :'||
951: to_char(ltr_yepp_arch_data(i).archive_ue_id));
952: exit;
953: end if;
954:
959:
960: /* Compare RL1 Amendment Footnotes and RL1 YEPP Footnotes for an
961: assignment_action */
962:
963: hr_utility.trace('Comparing RL1 Amend and RL1 YEPP Footnotes');
964:
965: if lv_flag <> 'Y' then
966:
967: if ln_yepp_footnote_count <> ln_amend_footnote_count then
980: (ltr_yepp_footnote(i).value is not null and
981: ltr_amend_footnote(i).value is null)) then
982:
983: lv_flag := 'Y';
984: hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
985: exit;
986: end if;
987: end if;
988: end loop;
994: flag to 'N' */
995:
996: if lv_flag <> 'Y' then
997: lv_flag := 'N';
998: hr_utility.trace('No value difference for an Employee Asg Action: '||
999: to_char(p_assignment_action_id));
1000: end if;
1001:
1002: hr_utility.trace('lv_flag :'||lv_flag);
998: hr_utility.trace('No value difference for an Employee Asg Action: '||
999: to_char(p_assignment_action_id));
1000: end if;
1001:
1002: hr_utility.trace('lv_flag :'||lv_flag);
1003: return lv_flag;
1004:
1005: end compare_archive_data;
1006:
1268:
1269: /* Get the report type, report qualifier, business group id and the
1270: gre for which the archiving has to be done */
1271:
1272: hr_utility.trace('getting report type ');
1273:
1274: select effective_date,
1275: report_type,
1276: -- Added for bug 10399514
1290: l_pre_organization_id
1291: from pay_payroll_actions
1292: where payroll_action_id = pactid;
1293:
1294: hr_utility.trace('getting dates');
1295:
1296: get_dates(l_report_type,
1297: l_effective_date,
1298: l_period_end,
1300: l_quarter_end,
1301: l_year_start,
1302: l_year_end);
1303:
1304: hr_utility.trace('getting selection information');
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1301: l_year_start,
1302: l_year_end);
1303:
1304: hr_utility.trace('getting selection information');
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1302: l_year_end);
1303:
1304: hr_utility.trace('getting selection information');
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1303:
1304: hr_utility.trace('getting selection information');
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1304: hr_utility.trace('getting selection information');
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1312:
1305: hr_utility.trace('report type '|| l_report_type);
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1312:
1313: get_selection_information
1306: hr_utility.trace('report qualifier '|| l_state);
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1312:
1313: get_selection_information
1314: (l_report_type,
1307: hr_utility.trace('report category '|| l_report_cat);
1308: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1309: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1310: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1311: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1312:
1313: get_selection_information
1314: (l_report_type,
1315: l_quarter_start,
1323: l_group_by_medicare,
1324: l_tax_unit_context,
1325: l_jurisdiction_context);
1326:
1327: hr_utility.trace('Out of get selection information');
1328:
1329: -- Code modification for bug 10399514 starts here
1330: /* Initializing variable */
1331: l_person_on := FALSE ;
1349:
1350: -- open c_eoy_qbin;
1351:
1352: if l_person_on then
1353: hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1354: OPEN c_eoy_qbin_range ;
1355: else
1356: hr_utility.trace('opening c_eoy_qbin CURSOR');
1357: OPEN c_eoy_qbin;
1352: if l_person_on then
1353: hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1354: OPEN c_eoy_qbin_range ;
1355: else
1356: hr_utility.trace('opening c_eoy_qbin CURSOR');
1357: OPEN c_eoy_qbin;
1358: end if ;
1359:
1360: -- Code modification for bug 10399514 ends here
1360: -- Code modification for bug 10399514 ends here
1361:
1362: /* Loop for all rows returned for SQL statement. */
1363:
1364: hr_utility.trace('Entering loop');
1365:
1366: loop
1367:
1368: -- Code modification for bug 10399514 starts here
1366: loop
1367:
1368: -- Code modification for bug 10399514 starts here
1369: if l_person_on then
1370: hr_utility.trace('fetching from c_eoy_qbin_range CURSOR');
1371: fetch c_eoy_qbin_range
1372: into l_person_id,
1373: l_assignment_id,
1374: l_effective_end_date;
1373: l_assignment_id,
1374: l_effective_end_date;
1375: exit when c_eoy_qbin_range%NOTFOUND;
1376: else
1377: hr_utility.trace('fetching from c_eoy_qbin CURSOR');
1378: fetch c_eoy_qbin
1379: into l_person_id,
1380: l_assignment_id,
1381: l_effective_end_date;
1387: the rows are grouped then discard the row ie. grouping by PRE
1388: organization id requires a single row for each person / PRE
1389: combination. */
1390:
1391: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1392: hr_utility.trace('previous pre_organization_id is '||
1393: l_prev_pre_organization_id);
1394: hr_utility.trace('person_id is '||
1395: to_char(l_person_id));
1388: organization id requires a single row for each person / PRE
1389: combination. */
1390:
1391: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1392: hr_utility.trace('previous pre_organization_id is '||
1393: l_prev_pre_organization_id);
1394: hr_utility.trace('person_id is '||
1395: to_char(l_person_id));
1396: hr_utility.trace('previous person_id is '||
1390:
1391: hr_utility.trace('Prov Reporting Est organization id '|| l_pre_organization_id);
1392: hr_utility.trace('previous pre_organization_id is '||
1393: l_prev_pre_organization_id);
1394: hr_utility.trace('person_id is '||
1395: to_char(l_person_id));
1396: hr_utility.trace('previous person_id is '||
1397: to_char(l_prev_person_id));
1398:
1392: hr_utility.trace('previous pre_organization_id is '||
1393: l_prev_pre_organization_id);
1394: hr_utility.trace('person_id is '||
1395: to_char(l_person_id));
1396: hr_utility.trace('previous person_id is '||
1397: to_char(l_prev_person_id));
1398:
1399: if (l_person_id = l_prev_person_id and
1400: l_pre_organization_id = l_prev_pre_organization_id) then
1398:
1399: if (l_person_id = l_prev_person_id and
1400: l_pre_organization_id = l_prev_pre_organization_id) then
1401:
1402: hr_utility.trace('Not creating assignment action');
1403:
1404: else
1405: /* Check whether the person has 0 payment or not */
1406:
1472: end loop;
1473: close c_all_qbin_gres;
1474: /* end of checking whether the person has 0 payment */
1475:
1476: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1477: hr_utility.trace('person is '|| to_char(l_person_id));
1478: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1479:
1480:
1473: close c_all_qbin_gres;
1474: /* end of checking whether the person has 0 payment */
1475:
1476: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1477: hr_utility.trace('person is '|| to_char(l_person_id));
1478: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1479:
1480:
1481: /* Have a new unique row according to the way the rows are grouped.
1474: /* end of checking whether the person has 0 payment */
1475:
1476: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1477: hr_utility.trace('person is '|| to_char(l_person_id));
1478: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1479:
1480:
1481: /* Have a new unique row according to the way the rows are grouped.
1482: The inclusion of the person is dependent on having a non zero
1485: indicate their inclusion in the magnetic tape report. */
1486:
1487: /* Set up the context of tax unit id */
1488:
1489: hr_utility.trace('Setting context');
1490:
1491: /* Only create assignment actions if Gross Earnings are not 0 and are not
1492: made up of only nontaxable earnings or the No Gross Earnings balance is
1493: non zero */
1501: fetch c_get_asg_id into l_primary_asg;
1502:
1503: if c_get_asg_id%NOTFOUND then
1504: close c_get_asg_id;
1505: raise hr_utility.hr_error;
1506: else
1507: close c_get_asg_id;
1508: end if;
1509:
1515: from dual;
1516:
1517: /* Insert into pay_assignment_actions. */
1518:
1519: hr_utility.trace('creating assignment action');
1520:
1521: /* Passing tax unit id as null */
1522:
1523: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1527: so that the mag routine and the RL1 view will not have
1528: to do an additional checking against the assignment
1529: table */
1530:
1531: hr_utility.trace('updating assignment action' || to_char(lockingactid));
1532:
1533: update pay_assignment_actions aa
1534: set aa.serial_number = to_char(l_person_id)
1535: where aa.assignment_action_id = lockingactid;
1540: /*
1541: l_counter := 1;
1542: l_user_entity_name := 'CAEOY_RL1_QUEBEC_BN';
1543:
1544: hr_utility.trace('Archiving AAC level data for ' || to_char(lockingactid));
1545: ff_archive_api.create_archive_item(
1546: p_archive_item_id => l_archive_item_id
1547: ,p_user_entity_id => get_user_entity_id(l_user_entity_name)
1548: ,p_archive_value => l_pre_organization_id
1551: ,p_legislation_code => 'CA'
1552: ,p_object_version_number => l_object_version_number
1553: ,p_some_warning => l_some_warning
1554: );
1555: hr_utility.trace('Archived AAC level data');
1556: */
1557:
1558: /* I have to enter data in new archive table also with archive type as AAC, ie
1559: assignment_action_creation */
1566: l_prev_pre_organization_id := l_pre_organization_id;
1567:
1568: end loop;
1569:
1570: hr_utility.trace('Action creation done');
1571:
1572: -- Code modification for bug 10399514 starts here
1573: if l_person_on then
1574: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1570: hr_utility.trace('Action creation done');
1571:
1572: -- Code modification for bug 10399514 starts here
1573: if l_person_on then
1574: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1575: close c_eoy_qbin_range;
1576: else
1577: hr_utility.trace('closing c_eoy_qbin CURSOR');
1578: close c_eoy_qbin;
1573: if l_person_on then
1574: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1575: close c_eoy_qbin_range;
1576: else
1577: hr_utility.trace('closing c_eoy_qbin CURSOR');
1578: close c_eoy_qbin;
1579: end if ;
1580: -- Code modification for bug 10399514 ends here
1581:
1610: exception
1611: when no_data_found then
1612: return(0);
1613: when others then
1614: hr_utility.trace('Error while getting the jursd. value ' ||
1615: to_char(sqlcode));
1616:
1617: end eoy_get_jursd_level;
1618:
1724: /* payroll action level database items */
1725:
1726: BEGIN
1727:
1728: /*hr_utility.trace_on('Y','RL1'); */
1729:
1730: select to_char(effective_date,'YYYY'),business_group_id
1731: into l_taxation_year,l_business_group_id
1732: from pay_payroll_actions
1752: l_transmitter_org_id,
1753: l_transmitter_gre_ind;
1754:
1755: l_arch_gre_step := 40;
1756: hr_utility.trace('eoy_archive_gre_data 1');
1757:
1758: if employer_info%FOUND then
1759:
1760: close employer_info;
1757:
1758: if employer_info%FOUND then
1759:
1760: close employer_info;
1761: hr_utility.trace('got employer data ');
1762:
1763: l_employer_info_found := 'Y';
1764:
1765: begin
1847: l_accounting_contact_language:= 'TEST_DATA';
1848: l_proprietor_sin := 'TEST_DATA';
1849: l_arch_gre_step := 424;
1850:
1851: hr_utility.trace('eoy_archive_gre_data 2');
1852: close employer_info;
1853:
1854: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1855: hr_utility.set_message_token('ORGIND','GRE');
1850:
1851: hr_utility.trace('eoy_archive_gre_data 2');
1852: close employer_info;
1853:
1854: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1855: hr_utility.set_message_token('ORGIND','GRE');
1856: hr_utility.raise_error;
1857: end if;
1858:
1851: hr_utility.trace('eoy_archive_gre_data 2');
1852: close employer_info;
1853:
1854: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1855: hr_utility.set_message_token('ORGIND','GRE');
1856: hr_utility.raise_error;
1857: end if;
1858:
1859: /* archive Releve 1 data */
1852: close employer_info;
1853:
1854: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1855: hr_utility.set_message_token('ORGIND','GRE');
1856: hr_utility.raise_error;
1857: end if;
1858:
1859: /* archive Releve 1 data */
1860:
2047: */
2048:
2049: l_arch_gre_step := 52;
2050:
2051: /*hr_utility.trace_on('Y','RL1'); */
2052:
2053: hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
2054:
2055: ff_archive_api.create_archive_item(
2049: l_arch_gre_step := 52;
2050:
2051: /*hr_utility.trace_on('Y','RL1'); */
2052:
2053: hr_utility.trace('user_entity id is : ' || l_user_entity_name_tab(i));
2054:
2055: ff_archive_api.create_archive_item(
2056: p_archive_item_id => l_archive_item_id
2057: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
2067:
2068: EXCEPTION
2069: when others then
2070: g_archive_flag := 'N';
2071: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2072: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2073: if l_arch_gre_step = 40 then
2074: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2075: hr_utility.set_message_token('ORGIND','ORG');
2070: g_archive_flag := 'N';
2071: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2072: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2073: if l_arch_gre_step = 40 then
2074: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2075: hr_utility.set_message_token('ORGIND','ORG');
2076: end if;
2077: hr_utility.raise_error;
2078:
2071: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2072: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2073: if l_arch_gre_step = 40 then
2074: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2075: hr_utility.set_message_token('ORGIND','ORG');
2076: end if;
2077: hr_utility.raise_error;
2078:
2079: END eoy_archive_gre_data;
2073: if l_arch_gre_step = 40 then
2074: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2075: hr_utility.set_message_token('ORGIND','ORG');
2076: end if;
2077: hr_utility.raise_error;
2078:
2079: END eoy_archive_gre_data;
2080:
2081: /* Name : chk_gre_archive
2096: from ff_archive_items fai
2097: where fai.context1 = p_payroll_action_id);
2098: begin
2099:
2100: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2101:
2102: if g_archive_flag = 'Y' then
2103: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2104: return (TRUE);
2099:
2100: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2101:
2102: if g_archive_flag = 'Y' then
2103: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2104: return (TRUE);
2105: else
2106:
2107: hr_utility.trace('chk_gre_archive - opening cursor');
2103: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2104: return (TRUE);
2105: else
2106:
2107: hr_utility.trace('chk_gre_archive - opening cursor');
2108:
2109: open c_chk_payroll_action;
2110: fetch c_chk_payroll_action into l_flag;
2111: if c_chk_payroll_action%FOUND then
2108:
2109: open c_chk_payroll_action;
2110: fetch c_chk_payroll_action into l_flag;
2111: if c_chk_payroll_action%FOUND then
2112: hr_utility.trace('chk_gre_archive - found in cursor');
2113: g_archive_flag := 'Y';
2114: else
2115: hr_utility.trace('chk_gre_archive - not found in cursor');
2116: g_archive_flag := 'N';
2111: if c_chk_payroll_action%FOUND then
2112: hr_utility.trace('chk_gre_archive - found in cursor');
2113: g_archive_flag := 'Y';
2114: else
2115: hr_utility.trace('chk_gre_archive - not found in cursor');
2116: g_archive_flag := 'N';
2117: end if;
2118:
2119: hr_utility.trace('chk_gre_archive - closing cursor');
2115: hr_utility.trace('chk_gre_archive - not found in cursor');
2116: g_archive_flag := 'N';
2117: end if;
2118:
2119: hr_utility.trace('chk_gre_archive - closing cursor');
2120: close c_chk_payroll_action;
2121: if g_archive_flag = 'Y' then
2122: hr_utility.trace('chk_gre_archive - returning true');
2123: return (TRUE);
2118:
2119: hr_utility.trace('chk_gre_archive - closing cursor');
2120: close c_chk_payroll_action;
2121: if g_archive_flag = 'Y' then
2122: hr_utility.trace('chk_gre_archive - returning true');
2123: return (TRUE);
2124: else
2125: hr_utility.trace('chk_gre_archive - returning false');
2126: return(FALSE);
2121: if g_archive_flag = 'Y' then
2122: hr_utility.trace('chk_gre_archive - returning true');
2123: return (TRUE);
2124: else
2125: hr_utility.trace('chk_gre_archive - returning false');
2126: return(FALSE);
2127: end if;
2128: end if;
2129: end chk_gre_archive;
2155: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
2156: end if;
2157: close c_get_min_chunk;
2158:
2159: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
2160: l_step := 2;
2161: l_archive := chk_gre_archive(p_payroll_action_id);
2162:
2163: l_step := 3;
2160: l_step := 2;
2161: l_archive := chk_gre_archive(p_payroll_action_id);
2162:
2163: l_step := 3;
2164: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
2165: exception
2166: when others then
2167: raise_application_error(-20001,'eoy_archinit at '
2168: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2178: l_check_number number;
2179: BEGIN
2180:
2181: l_check_number := mod(p_curr_seq,7);
2182: hr_utility.trace('l_check_number ='|| l_check_number);
2183: l_seq_number := (p_curr_seq * 10) + l_check_number;
2184: hr_utility.trace('l_seq_number ='|| l_seq_number);
2185: return l_seq_number;
2186: END;
2180:
2181: l_check_number := mod(p_curr_seq,7);
2182: hr_utility.trace('l_check_number ='|| l_check_number);
2183: l_seq_number := (p_curr_seq * 10) + l_check_number;
2184: hr_utility.trace('l_seq_number ='|| l_seq_number);
2185: return l_seq_number;
2186: END;
2187:
2188:
2236: l_warning boolean;
2237: l_archive_item_id number;
2238:
2239: begin
2240: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 10');
2241:
2242: if (called_from = 'XMLPROC') then
2243: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 20');
2244:
2239: begin
2240: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 10');
2241:
2242: if (called_from = 'XMLPROC') then
2243: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 20');
2244:
2245: open c_get_arch_seq_num(p_aaid, p_jurisdiction);
2246: fetch c_get_arch_seq_num into l_final_seq_num,l_archive_item_id,l_obj_ver;
2247: close c_get_arch_seq_num;
2258: close c_get_seq_num_range;
2259:
2260: if (l_start_seq_num is not null) then
2261:
2262: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 30');
2263: hr_utility.trace('l_start_seq_num = '||l_start_seq_num);
2264: hr_utility.trace('l_end_seq_num = '||l_end_seq_num);
2265:
2266: select PAY_CA_RL1_PDF_SEQ_COUNT_S.nextval into l_seq_offset
2259:
2260: if (l_start_seq_num is not null) then
2261:
2262: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 30');
2263: hr_utility.trace('l_start_seq_num = '||l_start_seq_num);
2264: hr_utility.trace('l_end_seq_num = '||l_end_seq_num);
2265:
2266: select PAY_CA_RL1_PDF_SEQ_COUNT_S.nextval into l_seq_offset
2267: from dual;
2260: if (l_start_seq_num is not null) then
2261:
2262: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 30');
2263: hr_utility.trace('l_start_seq_num = '||l_start_seq_num);
2264: hr_utility.trace('l_end_seq_num = '||l_end_seq_num);
2265:
2266: select PAY_CA_RL1_PDF_SEQ_COUNT_S.nextval into l_seq_offset
2267: from dual;
2268: l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset);
2266: select PAY_CA_RL1_PDF_SEQ_COUNT_S.nextval into l_seq_offset
2267: from dual;
2268: l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset);
2269:
2270: hr_utility.trace('l_seq_offset = '||l_seq_offset);
2271: hr_utility.trace('l_final_seq_num = '||l_final_seq_num);
2272:
2273: elsif (called_from ='ARCHIVER') then
2274: l_final_seq_num := null;
2267: from dual;
2268: l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset);
2269:
2270: hr_utility.trace('l_seq_offset = '||l_seq_offset);
2271: hr_utility.trace('l_final_seq_num = '||l_final_seq_num);
2272:
2273: elsif (called_from ='ARCHIVER') then
2274: l_final_seq_num := null;
2275:
2276: end if;
2277:
2278: if (called_from ='XMLPROC') then
2279: if(l_archive_item_id is null) then --If DBI is not archived when PDF is run
2280: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 40');
2281:
2282: ff_archive_api.create_archive_item(
2283: p_archive_item_id => l_archive_item_id
2284: ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_PDF_SEQ_NUMBER')
2291: ,p_context1 => 'QC'
2292: ,p_some_warning => l_warning
2293: );
2294: else -- If DBI is archived but with null value then update it with new value
2295: hr_utility.trace('In pay_ca_eoy_rl1_archive.gen_rl1_pdf_seq 50');
2296:
2297: /* Commented for bug 8732218
2298: ff_archive_api.update_archive_item( p_archive_item_id => l_archive_item_id
2299: ,p_effective_date => fnd_date.string_to_date('31/12/'||p_reporting_year,'DD/MM/YYYY')
2344: ppa.legislative_parameters )
2345: and ppa.payroll_action_id = p_payroll_action_id
2346: and hoi.org_information4 = 'P01';
2347:
2348: hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2349:
2350: hr_utility.trace('3');
2351: if l_transmitter_name1 is not null then
2352:
2346: and hoi.org_information4 = 'P01';
2347:
2348: hr_utility.trace('l_transmitter ' || l_transmitter_name1);
2349:
2350: hr_utility.trace('3');
2351: if l_transmitter_name1 is not null then
2352:
2353: -- Added for bug 11694701
2354: /* select to_number(target.ORG_INFORMATION18)
2371: when no_data_found then
2372: null;
2373: end;
2374: if l_rl1_starting_slip_num is NULL then
2375: hr_utility.trace('Warning: Starting Slip number is NULL');
2376: end if;
2377:
2378: if l_rl1_ending_slip_num is NULL then
2379: hr_utility.trace('Warning: Ending Slip number is NULL');
2375: hr_utility.trace('Warning: Starting Slip number is NULL');
2376: end if;
2377:
2378: if l_rl1_ending_slip_num is NULL then
2379: hr_utility.trace('Warning: Ending Slip number is NULL');
2380: end if;
2381:
2382: hr_utility.trace('Starting Slip number: '||l_rl1_starting_slip_num);
2383: hr_utility.trace('Ending Slip number: '||l_rl1_ending_slip_num);
2378: if l_rl1_ending_slip_num is NULL then
2379: hr_utility.trace('Warning: Ending Slip number is NULL');
2380: end if;
2381:
2382: hr_utility.trace('Starting Slip number: '||l_rl1_starting_slip_num);
2383: hr_utility.trace('Ending Slip number: '||l_rl1_ending_slip_num);
2384: -- Ended changes for bug 11694701
2385:
2386: else
2379: hr_utility.trace('Warning: Ending Slip number is NULL');
2380: end if;
2381:
2382: hr_utility.trace('Starting Slip number: '||l_rl1_starting_slip_num);
2383: hr_utility.trace('Ending Slip number: '||l_rl1_ending_slip_num);
2384: -- Ended changes for bug 11694701
2385:
2386: else
2387: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2383: hr_utility.trace('Ending Slip number: '||l_rl1_ending_slip_num);
2384: -- Ended changes for bug 11694701
2385:
2386: else
2387: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2388: hr_utility.set_message_token('ORGIND','PRE');
2389: hr_utility.raise_error;
2390: end if;
2391:
2384: -- Ended changes for bug 11694701
2385:
2386: else
2387: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2388: hr_utility.set_message_token('ORGIND','PRE');
2389: hr_utility.raise_error;
2390: end if;
2391:
2392: hr_utility.trace('2');
2385:
2386: else
2387: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2388: hr_utility.set_message_token('ORGIND','PRE');
2389: hr_utility.raise_error;
2390: end if;
2391:
2392: hr_utility.trace('2');
2393:
2388: hr_utility.set_message_token('ORGIND','PRE');
2389: hr_utility.raise_error;
2390: end if;
2391:
2392: hr_utility.trace('2');
2393:
2394: -- Added for bug 11694701
2395: /*select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2396: into l_rl1_curr_slip_number from dual;*/
2395: /*select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
2396: into l_rl1_curr_slip_number from dual;*/
2397: select l_rl1_starting_slip_num + pay_ca_eoy_rl1_s.nextval - 1
2398: into l_rl1_curr_slip_number from dual;
2399: hr_utility.trace('l_rl1_curr_slip_number: '||l_rl1_curr_slip_number);
2400:
2401: if l_rl1_ending_slip_num is not NULL and l_rl1_curr_slip_number is not NULL
2402: and l_rl1_curr_slip_number > l_rl1_ending_slip_num then
2403: hr_utility.trace('Warning: Generated Slip number '||l_rl1_curr_slip_number
2399: hr_utility.trace('l_rl1_curr_slip_number: '||l_rl1_curr_slip_number);
2400:
2401: if l_rl1_ending_slip_num is not NULL and l_rl1_curr_slip_number is not NULL
2402: and l_rl1_curr_slip_number > l_rl1_ending_slip_num then
2403: hr_utility.trace('Warning: Generated Slip number '||l_rl1_curr_slip_number
2404: ||' is greater than Ending slip number '||l_rl1_ending_slip_num);
2405: end if;
2406: -- Ended changes for bug 11694701
2407:
2404: ||' is greater than Ending slip number '||l_rl1_ending_slip_num);
2405: end if;
2406: -- Ended changes for bug 11694701
2407:
2408: hr_utility.trace('1');
2409:
2410: select mod(l_rl1_curr_slip_number,7)
2411: into l_rl1_slip_number_last_digit
2412: from dual;
2410: select mod(l_rl1_curr_slip_number,7)
2411: into l_rl1_slip_number_last_digit
2412: from dual;
2413:
2414: hr_utility.trace('l_rl1_slip_number_last_digit : '|| l_rl1_slip_number_last_digit);
2415:
2416: /* Commented and modified as below by sneelapa, bug 11654691
2417:
2418: l_rl1_slip_number := (l_rl1_curr_slip_number)||
2420: */
2421:
2422: l_rl1_slip_number :=lpad ((l_rl1_curr_slip_number)||l_rl1_slip_number_last_digit, 9, 0);
2423:
2424: hr_utility.trace('l_rl1_slip_number : ' || l_rl1_slip_number);
2425: return l_rl1_slip_number;
2426:
2427: end gen_rl1_slip_no;
2428:
2505: order by organization_id;
2506:
2507: BEGIN
2508:
2509: --hr_utility.trace_on(null,'RL1');
2510: hr_utility.set_location ('archive_data',1);
2511: hr_utility.trace('getting assignment');
2512:
2513: l_negative_balance_exists := p_negative_balance_exists;
2506:
2507: BEGIN
2508:
2509: --hr_utility.trace_on(null,'RL1');
2510: hr_utility.set_location ('archive_data',1);
2511: hr_utility.trace('getting assignment');
2512:
2513: l_negative_balance_exists := p_negative_balance_exists;
2514:
2507: BEGIN
2508:
2509: --hr_utility.trace_on(null,'RL1');
2510: hr_utility.set_location ('archive_data',1);
2511: hr_utility.trace('getting assignment');
2512:
2513: l_negative_balance_exists := p_negative_balance_exists;
2514:
2515: SELECT aa.assignment_id,
2532: archived then archive the gre data i.e. the employer data */
2533:
2534: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2535:
2536: hr_utility.trace('eoy_archive_further_info archiving employer data');
2537: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2538:
2539: select business_group_id
2540: into l_business_group_id
2533:
2534: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2535:
2536: hr_utility.trace('eoy_archive_further_info archiving employer data');
2537: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2538:
2539: select business_group_id
2540: into l_business_group_id
2541: from pay_payroll_actions
2542: where payroll_action_id = l_payroll_action_id;
2543:
2544: end if;
2545:
2546: hr_utility.set_location ('eoy_archive_further_info',2);
2547:
2548: hr_utility.trace('assignment '|| to_char(l_asgid));
2549: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2544: end if;
2545:
2546: hr_utility.set_location ('eoy_archive_further_info',2);
2547:
2548: hr_utility.trace('assignment '|| to_char(l_asgid));
2549: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2551: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552:
2545:
2546: hr_utility.set_location ('eoy_archive_further_info',2);
2547:
2548: hr_utility.trace('assignment '|| to_char(l_asgid));
2549: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2551: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552:
2553: /* Derive the beginning and end of the effective year */
2546: hr_utility.set_location ('eoy_archive_further_info',2);
2547:
2548: hr_utility.trace('assignment '|| to_char(l_asgid));
2549: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2551: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552:
2553: /* Derive the beginning and end of the effective year */
2554:
2547:
2548: hr_utility.trace('assignment '|| to_char(l_asgid));
2549: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2550: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2551: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552:
2553: /* Derive the beginning and end of the effective year */
2554:
2555: hr_utility.trace('getting begin and end dates');
2551: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2552:
2553: /* Derive the beginning and end of the effective year */
2554:
2555: hr_utility.trace('getting begin and end dates');
2556:
2557: l_year_start := trunc(p_effective_date, 'Y');
2558: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2559:
2556:
2557: l_year_start := trunc(p_effective_date, 'Y');
2558: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2559:
2560: hr_utility.trace('year start '|| to_char(l_year_start));
2561: hr_utility.trace('year end '|| to_char(l_year_end));
2562:
2563: /* Initialise the PL/SQL table before populating it */
2564:
2557: l_year_start := trunc(p_effective_date, 'Y');
2558: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2559:
2560: hr_utility.trace('year start '|| to_char(l_year_start));
2561: hr_utility.trace('year end '|| to_char(l_year_end));
2562:
2563: /* Initialise the PL/SQL table before populating it */
2564:
2565: hr_utility.trace('Initialising Pl/SQL table');
2561: hr_utility.trace('year end '|| to_char(l_year_end));
2562:
2563: /* Initialise the PL/SQL table before populating it */
2564:
2565: hr_utility.trace('Initialising Pl/SQL table');
2566: l_count := 0;
2567:
2568: l_count := l_count + 1;
2569: l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_A1_AMT_PER_JD_YTD';
2694: l_user_entity_name_tab(l_count) := 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD';
2695: l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_235';
2696:
2697:
2698: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2699: hr_utility.trace('l_asgid '||l_asgid);
2700: hr_utility.trace('l_business_group_id '||l_business_group_id);
2701: l_jurisdiction := 'QC';
2702: p_temp_nz_fi := 0; /* Bug 13564765 sbachu*/
2695: l_balance_type_tab(l_count) := 'RL1_FURTHER_INFO_AMOUNT_235';
2696:
2697:
2698: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2699: hr_utility.trace('l_asgid '||l_asgid);
2700: hr_utility.trace('l_business_group_id '||l_business_group_id);
2701: l_jurisdiction := 'QC';
2702: p_temp_nz_fi := 0; /* Bug 13564765 sbachu*/
2703: for i in 1 .. l_count
2696:
2697:
2698: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2699: hr_utility.trace('l_asgid '||l_asgid);
2700: hr_utility.trace('l_business_group_id '||l_business_group_id);
2701: l_jurisdiction := 'QC';
2702: p_temp_nz_fi := 0; /* Bug 13564765 sbachu*/
2703: for i in 1 .. l_count
2704: loop
2705: /* Get the context_id for 'Jurisdiction' from ff_contexts */
2706: result := 0;
2707: open c_all_gres_for_further_info(p_assactid);
2708: loop
2709: hr_utility.trace('Fetching all GREs');
2710: fetch c_all_gres_for_further_info into l_tax_unit_id;
2711: exit when c_all_gres_for_further_info%NOTFOUND;
2712:
2713: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2709: hr_utility.trace('Fetching all GREs');
2710: fetch c_all_gres_for_further_info into l_tax_unit_id;
2711: exit when c_all_gres_for_further_info%NOTFOUND;
2712:
2713: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2714: hr_utility.trace('Asgid is ' || l_asgid);
2715: hr_utility.trace('Effective date is '|| p_effective_date);
2716: begin
2717:
2710: fetch c_all_gres_for_further_info into l_tax_unit_id;
2711: exit when c_all_gres_for_further_info%NOTFOUND;
2712:
2713: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2714: hr_utility.trace('Asgid is ' || l_asgid);
2715: hr_utility.trace('Effective date is '|| p_effective_date);
2716: begin
2717:
2718: open c_get_max_asgactid_jd(to_number(lv_serial_number),
2711: exit when c_all_gres_for_further_info%NOTFOUND;
2712:
2713: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
2714: hr_utility.trace('Asgid is ' || l_asgid);
2715: hr_utility.trace('Effective date is '|| p_effective_date);
2716: begin
2717:
2718: open c_get_max_asgactid_jd(to_number(lv_serial_number),
2719: l_tax_unit_id,
2739: l_tax_unit_id,
2740: l_business_group_id,
2741: 'QC'
2742: ),0) ;
2743: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2744: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2745: hr_utility.trace('Result is ' || to_char(result));
2746: exception when no_data_found then
2747: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2740: l_business_group_id,
2741: 'QC'
2742: ),0) ;
2743: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2744: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2745: hr_utility.trace('Result is ' || to_char(result));
2746: exception when no_data_found then
2747: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2748: end;
2741: 'QC'
2742: ),0) ;
2743: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2744: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2745: hr_utility.trace('Result is ' || to_char(result));
2746: exception when no_data_found then
2747: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2748: end;
2749: end loop; -- end of c_all_gres_for_further_info
2743: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2744: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2745: hr_utility.trace('Result is ' || to_char(result));
2746: exception when no_data_found then
2747: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2748: end;
2749: end loop; -- end of c_all_gres_for_further_info
2750: close c_all_gres_for_further_info;
2751: ff_archive_api.create_archive_item(
2771: end if;
2772: end loop;
2773:
2774: p_negative_balance_exists := l_negative_balance_exists;
2775: hr_utility.trace('p_temp_nz_fi = '||p_temp_nz_fi);
2776: hr_utility.trace('End of Provincial YE Amendment PP Validation');
2777:
2778: Exception when no_data_found then
2779: hr_utility.trace('Report type not found for given Payroll_action ');
2772: end loop;
2773:
2774: p_negative_balance_exists := l_negative_balance_exists;
2775: hr_utility.trace('p_temp_nz_fi = '||p_temp_nz_fi);
2776: hr_utility.trace('End of Provincial YE Amendment PP Validation');
2777:
2778: Exception when no_data_found then
2779: hr_utility.trace('Report type not found for given Payroll_action ');
2780: null;
2775: hr_utility.trace('p_temp_nz_fi = '||p_temp_nz_fi);
2776: hr_utility.trace('End of Provincial YE Amendment PP Validation');
2777:
2778: Exception when no_data_found then
2779: hr_utility.trace('Report type not found for given Payroll_action ');
2780: null;
2781: -- End of Provincial YE Amendment Pre-Process Validation
2782: end eoy_archive_further_info;
2783:
3233: order by paa.action_sequence desc;
3234:
3235: BEGIN
3236:
3237: --hr_utility.trace_on(null,'RL1');
3238: hr_utility.set_location ('archive_data',1);
3239: hr_utility.trace('getting assignment');
3240:
3241: l_negative_balance_exists := 'N';
3234:
3235: BEGIN
3236:
3237: --hr_utility.trace_on(null,'RL1');
3238: hr_utility.set_location ('archive_data',1);
3239: hr_utility.trace('getting assignment');
3240:
3241: l_negative_balance_exists := 'N';
3242: lv_qpp_pensionable_earnings := 0;
3235: BEGIN
3236:
3237: --hr_utility.trace_on(null,'RL1');
3238: hr_utility.set_location ('archive_data',1);
3239: hr_utility.trace('getting assignment');
3240:
3241: l_negative_balance_exists := 'N';
3242: lv_qpp_pensionable_earnings := 0;
3243: l_step := 1;
3262: archived then archive the gre data i.e. the employer data */
3263:
3264: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3265:
3266: hr_utility.trace('eoy_archive_data archiving employer data');
3267: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
3268:
3269: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3270: legislative_parameters),
3263:
3264: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
3265:
3266: hr_utility.trace('eoy_archive_data archiving employer data');
3267: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
3268:
3269: select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
3270: legislative_parameters),
3271: business_group_id
3275:
3276: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
3277: p_pre_organization_id=>l_pre_organization_id);
3278:
3279: hr_utility.trace('eoy_archive_data archived employer data');
3280:
3281: end if;
3282:
3283: hr_utility.set_location ('archive_data',2);
3279: hr_utility.trace('eoy_archive_data archived employer data');
3280:
3281: end if;
3282:
3283: hr_utility.set_location ('archive_data',2);
3284:
3285: hr_utility.trace('assignment '|| to_char(l_asgid));
3286: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3281: end if;
3282:
3283: hr_utility.set_location ('archive_data',2);
3284:
3285: hr_utility.trace('assignment '|| to_char(l_asgid));
3286: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3288: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289:
3282:
3283: hr_utility.set_location ('archive_data',2);
3284:
3285: hr_utility.trace('assignment '|| to_char(l_asgid));
3286: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3288: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289:
3290: /* Derive the beginning and end of the effective year */
3283: hr_utility.set_location ('archive_data',2);
3284:
3285: hr_utility.trace('assignment '|| to_char(l_asgid));
3286: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3288: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289:
3290: /* Derive the beginning and end of the effective year */
3291:
3284:
3285: hr_utility.trace('assignment '|| to_char(l_asgid));
3286: hr_utility.trace('date_earned '|| to_char(l_date_earned));
3287: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
3288: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289:
3290: /* Derive the beginning and end of the effective year */
3291:
3292: hr_utility.trace('getting begin and end dates');
3288: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
3289:
3290: /* Derive the beginning and end of the effective year */
3291:
3292: hr_utility.trace('getting begin and end dates');
3293:
3294: l_step := 2;
3295:
3296: l_year_start := trunc(p_effective_date, 'Y');
3295:
3296: l_year_start := trunc(p_effective_date, 'Y');
3297: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3298:
3299: hr_utility.trace('year start '|| to_char(l_year_start));
3300: hr_utility.trace('year end '|| to_char(l_year_end));
3301:
3302: if to_number(to_char(l_year_end,'YYYY')) > 2005 then
3303: lv_footnote_element := 'RL1 Non Box Footnotes';
3296: l_year_start := trunc(p_effective_date, 'Y');
3297: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
3298:
3299: hr_utility.trace('year start '|| to_char(l_year_start));
3300: hr_utility.trace('year end '|| to_char(l_year_end));
3301:
3302: if to_number(to_char(l_year_end,'YYYY')) > 2005 then
3303: lv_footnote_element := 'RL1 Non Box Footnotes';
3304: else
3306: end if;
3307:
3308: /* Initialise the PL/SQL table before populating it */
3309:
3310: hr_utility.trace('Initialising Pl/SQL table');
3311:
3312: l_step := 3;
3313:
3314: /* Get the context_id for 'Jurisdiction' from ff_contexts */
3332: l_step := 12;
3333:
3334: l_count := l_count + 1;
3335:
3336: hr_utility.trace('archiving CAEOY_RL1_PROVINCE_OF_EMPLOYMENT');
3337:
3338: ff_archive_api.create_archive_item(
3339: /*p_validate => 'TRUE' */
3340: p_archive_item_id => l_archive_item_id
3347: ,p_object_version_number => l_object_version_number
3348: ,p_some_warning => l_some_warning
3349: );
3350:
3351: hr_utility.trace('archived caeoy_rl1_employment_province');
3352:
3353: /* We can archive the balance level dbis also because for employee level
3354: balances jurisdiction is always a context. */
3355:
3352:
3353: /* We can archive the balance level dbis also because for employee level
3354: balances jurisdiction is always a context. */
3355:
3356: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3357:
3358: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3359: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
3360:
3357:
3358: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3359: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
3360:
3361: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
3362: /* RL1 Slip number generation part has been moved
3363: as a seperate function for bug 13564765*/
3364:
3365: l_count := 0;
3621: l_count := l_count + 1;
3622: l_user_entity_name_tab(l_count) := 'CAEOY_RL1_BOXU_PER_JD_YTD';
3623: l_balance_type_tab(l_count) := 'RL1_BOXU';
3624:
3625: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3626:
3627: for i in 1 .. l_count
3628: loop
3629: hr_utility.trace('Initialising values');
3625: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3626:
3627: for i in 1 .. l_count
3628: loop
3629: hr_utility.trace('Initialising values');
3630: l_user_entity_value_tab(i) := 0;
3631: end loop;
3632:
3633: open c_all_gres(p_assactid);
3633: open c_all_gres(p_assactid);
3634:
3635: loop
3636:
3637: hr_utility.trace('Fetching all GREs');
3638: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3639: exit when c_all_gres%NOTFOUND;
3640:
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3637: hr_utility.trace('Fetching all GREs');
3638: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3639: exit when c_all_gres%NOTFOUND;
3640:
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643: hr_utility.trace('Person id is ' || lv_serial_number);
3644: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3638: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3639: exit when c_all_gres%NOTFOUND;
3640:
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643: hr_utility.trace('Person id is ' || lv_serial_number);
3644: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3646:
3639: exit when c_all_gres%NOTFOUND;
3640:
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643: hr_utility.trace('Person id is ' || lv_serial_number);
3644: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3646:
3647: begin
3640:
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643: hr_utility.trace('Person id is ' || lv_serial_number);
3644: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3646:
3647: begin
3648: /* Removed select stmt to get max asgact_id and replaced it with
3641: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3642: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3643: hr_utility.trace('Person id is ' || lv_serial_number);
3644: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3645: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3646:
3647: begin
3648: /* Removed select stmt to get max asgact_id and replaced it with
3649: cursor c_get_max_asgactid_jd. 11510 Changes Bug#3356533.
3655: l_year_end);
3656: fetch c_get_max_asgactid_jd into l_aaid;
3657: close c_get_max_asgactid_jd;
3658:
3659: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3660: hr_utility.trace('l_count is ' || to_char(l_count));
3661:
3662: ln_no_gross_earnings := ln_no_gross_earnings +
3663: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3656: fetch c_get_max_asgactid_jd into l_aaid;
3657: close c_get_max_asgactid_jd;
3658:
3659: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3660: hr_utility.trace('l_count is ' || to_char(l_count));
3661:
3662: ln_no_gross_earnings := ln_no_gross_earnings +
3663: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3664: ('RL1 No Gross Earnings',
3681:
3682: if l_tax_unit_id <> l_prev_tax_unit_id or
3683: l_prev_tax_unit_id is null then
3684:
3685: hr_utility.trace('l_business_group_id is '||l_business_group_id);
3686:
3687: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3688: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3689: Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3690:
3691: for i in 1 .. l_count
3692: loop
3693:
3694: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3695: hr_utility.trace('i is ' || i);
3696:
3697: /* T4A earnings should not go to BOX A of RL1 */
3698:
3691: for i in 1 .. l_count
3692: loop
3693:
3694: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3695: hr_utility.trace('i is ' || i);
3696:
3697: /* T4A earnings should not go to BOX A of RL1 */
3698:
3699: /* bug 5768390
3733: l_business_group_id,
3734: 'QC'
3735: ),0);
3736:
3737: hr_utility.trace('Fed Result = ' || fed_result);
3738: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3739: else
3740: fed_result := 0;
3741: non_taxable_earnings := 0;
3734: 'QC'
3735: ),0);
3736:
3737: hr_utility.trace('Fed Result = ' || fed_result);
3738: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3739: else
3740: fed_result := 0;
3741: non_taxable_earnings := 0;
3742: hr_utility.trace('Fed Result = ' || fed_result);
3738: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3739: else
3740: fed_result := 0;
3741: non_taxable_earnings := 0;
3742: hr_utility.trace('Fed Result = ' || fed_result);
3743: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3744: end if;
3745:
3746: ln_balance_value :=
3739: else
3740: fed_result := 0;
3741: non_taxable_earnings := 0;
3742: hr_utility.trace('Fed Result = ' || fed_result);
3743: hr_utility.trace('Non Taxable Earnings = ' || non_taxable_earnings);
3744: end if;
3745:
3746: ln_balance_value :=
3747: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3760: if l_balance_type_tab(i) = 'QPP EE Taxable' then
3761: lv_qpp_pensionable_earnings := lv_qpp_pensionable_earnings + ln_balance_value;
3762: end if;
3763:
3764: hr_utility.trace('Balance value is '|| ln_balance_value);
3765:
3766: if ln_balance_value <> 0 then
3767: l_has_been_paid := 'Y';
3768: if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3783: end if;
3784: bug 5768390 */
3785:
3786:
3787: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790: hr_utility.trace('Assignemnt ID '|| l_asgid);
3791: hr_utility.trace('ln_boxo_exclude_from_boxa '||
3784: bug 5768390 */
3785:
3786:
3787: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790: hr_utility.trace('Assignemnt ID '|| l_asgid);
3791: hr_utility.trace('ln_boxo_exclude_from_boxa '||
3792: ln_boxo_exclude_from_boxa);
3785:
3786:
3787: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790: hr_utility.trace('Assignemnt ID '|| l_asgid);
3791: hr_utility.trace('ln_boxo_exclude_from_boxa '||
3792: ln_boxo_exclude_from_boxa);
3793:
3786:
3787: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790: hr_utility.trace('Assignemnt ID '|| l_asgid);
3791: hr_utility.trace('ln_boxo_exclude_from_boxa '||
3792: ln_boxo_exclude_from_boxa);
3793:
3794: end if;
3787: hr_utility.trace('REPORT_TYPE '||l_reporting_type);
3788: hr_utility.trace('TAX_UNIT_ID '||l_tax_unit_id);
3789: hr_utility.trace('ASSIGNMENT_ACTION_ID '||l_aaid);
3790: hr_utility.trace('Assignemnt ID '|| l_asgid);
3791: hr_utility.trace('ln_boxo_exclude_from_boxa '||
3792: ln_boxo_exclude_from_boxa);
3793:
3794: end if;
3795: l_user_entity_value_tab(i) := l_user_entity_value_tab(i) +
3800: /* bug 5768390
3801: end if;
3802: bug 5768390 */
3803:
3804: hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
3805: l_prev_tax_unit_id := l_tax_unit_id ;
3806:
3807: end loop;
3808: end if;
3808: end if;
3809:
3810: exception
3811: when no_data_found then
3812: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3813: end;
3814: end loop;
3815: close c_all_gres;
3816:
3813: end;
3814: end loop;
3815: close c_all_gres;
3816:
3817: hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3818:
3819: if ((l_no_of_payroll_run > 0) and
3820: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3821: if to_number(to_char(l_year_end,'YYYY')) >= 2011 then
3820: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3821: if to_number(to_char(l_year_end,'YYYY')) >= 2011 then
3822: eoy_archive_further_info(p_assactid,p_effective_date,l_negative_balance_exists,ln_no_nz_fi); /* bug 13564765*/
3823: end if;
3824: hr_utility.trace('ln_no_nz_fi4 = '||ln_no_nz_fi);
3825: -- hr_utility.trace_on('Y','RL1');
3826: for i in 1 .. l_count
3827: loop
3828:
3821: if to_number(to_char(l_year_end,'YYYY')) >= 2011 then
3822: eoy_archive_further_info(p_assactid,p_effective_date,l_negative_balance_exists,ln_no_nz_fi); /* bug 13564765*/
3823: end if;
3824: hr_utility.trace('ln_no_nz_fi4 = '||ln_no_nz_fi);
3825: -- hr_utility.trace_on('Y','RL1');
3826: for i in 1 .. l_count
3827: loop
3828:
3829: hr_utility.trace('in the create_archive_item loop');
3825: -- hr_utility.trace_on('Y','RL1');
3826: for i in 1 .. l_count
3827: loop
3828:
3829: hr_utility.trace('in the create_archive_item loop');
3830: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3831: hr_utility.trace('archive value is ');
3832:
3833: /* Archiving footnotes */
3826: for i in 1 .. l_count
3827: loop
3828:
3829: hr_utility.trace('in the create_archive_item loop');
3830: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3831: hr_utility.trace('archive value is ');
3832:
3833: /* Archiving footnotes */
3834:
3827: loop
3828:
3829: hr_utility.trace('in the create_archive_item loop');
3830: hr_utility.trace('archive item is ' || l_user_entity_name_tab(i));
3831: hr_utility.trace('archive value is ');
3832:
3833: /* Archiving footnotes */
3834:
3835: old_l_footnote_code := NULL;
3834:
3835: old_l_footnote_code := NULL;
3836: old_balance_type_tab := NULL;
3837:
3838: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3839: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3840:
3841: if l_user_entity_value_tab(i) <> 0 then
3842:
3835: old_l_footnote_code := NULL;
3836: old_balance_type_tab := NULL;
3837:
3838: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3839: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3840:
3841: if l_user_entity_value_tab(i) <> 0 then
3842:
3843: if l_balance_type_tab(i) = 'PROV STATUS INDIAN Subject' then
3880: fetch c_footnote_info into l_footnote_code,
3881: l_footnote_balance;
3882: exit when c_footnote_info%NOTFOUND;
3883:
3884: hr_utility.trace('l_footnote_amount_balance is '||
3885: l_footnote_balance);
3886: hr_utility.trace('l_footnote_code is '||
3887: l_footnote_code);
3888: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3882: exit when c_footnote_info%NOTFOUND;
3883:
3884: hr_utility.trace('l_footnote_amount_balance is '||
3885: l_footnote_balance);
3886: hr_utility.trace('l_footnote_code is '||
3887: l_footnote_code);
3888: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3889: l_footnote_amount_ue);
3890:
3884: hr_utility.trace('l_footnote_amount_balance is '||
3885: l_footnote_balance);
3886: hr_utility.trace('l_footnote_code is '||
3887: l_footnote_code);
3888: hr_utility.trace('after fetch if l_footnote_amount_ue is '||
3889: l_footnote_amount_ue);
3890:
3891: /* Must ensure that BOXR is only used with footnote code 14 */
3892: l_boxr_flag := 'Y';
3899:
3900: if ( l_footnote_code <> old_l_footnote_code or
3901: old_l_footnote_code is null )
3902: then
3903: hr_utility.trace('old_l_footnote_code is '||
3904: nvl(old_l_footnote_code,'NULL'));
3905: if old_l_footnote_code is not null then
3906:
3907: l_footnote_amount_ue := 'CAEOY_' ||old_balance_type_tab
3937: old_balance_type_tab := l_footnote_balance_type_tab ;
3938: l_footnote_amount_ue := 'CAEOY_' ||
3939: l_footnote_balance_type_tab||
3940: '_'||l_footnote_code||'_AMT_PER_JD_YTD';
3941: hr_utility.trace('l_footnote_amount_ue is '||
3942: l_footnote_amount_ue);
3943: end if;
3944:
3945: l_footnote_amount_ue := 'CAEOY_' ||
3949:
3950: /* get the footnote_balance */
3951: open c_all_gres_for_footnote(p_assactid);
3952: loop
3953: hr_utility.trace('Fetching all GREs');
3954: fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3955: l_ft_reporting_type;
3956: exit when c_all_gres_for_footnote%NOTFOUND;
3957:
3954: fetch c_all_gres_for_footnote into l_ft_tax_unit_id,
3955: l_ft_reporting_type;
3956: exit when c_all_gres_for_footnote%NOTFOUND;
3957:
3958: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3959: hr_utility.trace('Asgid is ' || l_asgid);
3960: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3961: hr_utility.trace('Effective date is '|| p_effective_date);
3962: begin
3955: l_ft_reporting_type;
3956: exit when c_all_gres_for_footnote%NOTFOUND;
3957:
3958: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3959: hr_utility.trace('Asgid is ' || l_asgid);
3960: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3961: hr_utility.trace('Effective date is '|| p_effective_date);
3962: begin
3963: /* Removed select stmt to get max asgact_id and replaced
3956: exit when c_all_gres_for_footnote%NOTFOUND;
3957:
3958: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3959: hr_utility.trace('Asgid is ' || l_asgid);
3960: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3961: hr_utility.trace('Effective date is '|| p_effective_date);
3962: begin
3963: /* Removed select stmt to get max asgact_id and replaced
3964: it with cursor c_get_max_asgactid_jd, reusing the same
3957:
3958: hr_utility.trace('Tax unit id is ' || l_ft_tax_unit_id);
3959: hr_utility.trace('Asgid is ' || l_asgid);
3960: hr_utility.trace('Reporting_type is ' || l_ft_reporting_type);
3961: hr_utility.trace('Effective date is '|| p_effective_date);
3962: begin
3963: /* Removed select stmt to get max asgact_id and replaced
3964: it with cursor c_get_max_asgactid_jd, reusing the same
3965: cursor used above. 11510 Changes Bug#3356533. Changed
3971: l_year_end);
3972: fetch c_get_max_asgactid_jd into l_ft_aaid;
3973: close c_get_max_asgactid_jd;
3974:
3975: hr_utility.trace('l_aaid is ' || l_ft_aaid);
3976: hr_utility.trace('l_count is ' || l_count);
3977:
3978: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3979:
3972: fetch c_get_max_asgactid_jd into l_ft_aaid;
3973: close c_get_max_asgactid_jd;
3974:
3975: hr_utility.trace('l_aaid is ' || l_ft_aaid);
3976: hr_utility.trace('l_count is ' || l_count);
3977:
3978: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3979:
3980: select target1.business_group_id
3984:
3985: if ( l_ft_tax_unit_id <> l_prev_tax_unit_id or
3986: l_prev_tax_unit_id is null )
3987: then
3988: hr_utility.trace('l_business_group_id is ' ||
3989: l_business_group_id);
3990:
3991: pay_balance_pkg.set_context('TAX_UNIT_ID',
3992: l_ft_tax_unit_id);
4010:
4011: l_prev_tax_unit_id := l_ft_tax_unit_id ;
4012: exception
4013: when no_data_found then
4014: hr_utility.trace('This Tax unit id has no payroll run,'||
4015: ' so skip it');
4016: end;
4017: end loop;
4018: close c_all_gres_for_footnote;
4029:
4030: end loop; /* c_footnote_info loop */
4031: close c_footnote_info;
4032:
4033: hr_utility.trace('before archiving l_footnote_amount_ue is '||
4034: l_footnote_amount_ue);
4035:
4036: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
4037: and l_footnote_amount <> 0 then
4063:
4064: /* End of footnote archiving */
4065:
4066: /* archive the box balances */
4067: hr_utility.trace('here1');
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4064: /* End of footnote archiving */
4065:
4066: /* archive the box balances */
4067: hr_utility.trace('here1');
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4065:
4066: /* archive the box balances */
4067: hr_utility.trace('here1');
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4066: /* archive the box balances */
4067: hr_utility.trace('here1');
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4074: ff_archive_api.create_archive_item(
4067: hr_utility.trace('here1');
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4074: ff_archive_api.create_archive_item(
4075: /* p_validate => 'TRUE' */
4068: hr_utility.trace('l_archive_item_id ='|| l_archive_item_id);
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4074: ff_archive_api.create_archive_item(
4075: /* p_validate => 'TRUE' */
4076: p_archive_item_id => l_archive_item_id
4069: hr_utility.trace('l_user_entity_name_tab(i) ='|| l_user_entity_name_tab(i));
4070: hr_utility.trace('l_user_entity_value_tab(i) ='|| l_user_entity_value_tab(i));
4071: hr_utility.trace('p_assactid ='|| p_assactid);
4072: hr_utility.trace('l_object_version_number ='|| l_object_version_number);
4073: -- hr_utility.trace('l_some_warning ='|| l_some_warning);
4074: ff_archive_api.create_archive_item(
4075: /* p_validate => 'TRUE' */
4076: p_archive_item_id => l_archive_item_id
4077: ,p_user_entity_id =>
4084: ,p_context_name1 => 'JURISDICTION_CODE'
4085: ,p_context1 => 'QC'
4086: ,p_some_warning => l_some_warning
4087: );
4088: hr_utility.trace('after the call');
4089: if l_user_entity_value_tab(i) < 0 then
4090: l_negative_balance_exists := 'Y';
4091: end if;
4092: /*Bug 15886329 sbachu*/
4126: if l_count_for_boxo_code > 1 then
4127: l_user_entity_code_tab_boxo := 'RZ' ;
4128: ln_no_nz_fi := ln_no_nz_fi + l_count_for_boxo_code ; /*Bug 13564765 sbachu*/
4129: end if;
4130: hr_utility.trace('ln_no_nz_fi3 = '||ln_no_nz_fi);
4131: if l_user_entity_value_tab_boxo < 0 then
4132: l_negative_balance_exists := 'Y';
4133: end if;
4134:
4177: lv_taxable_benefit_with_no_rem := 0;
4178: lv_tax_ben_no_rem_all_prov := 0;/* Bug 15914635*/
4179:
4180: loop
4181: hr_utility.trace('Fetching all GREs for the person');
4182: fetch c_all_gres_for_person into l_tax_unit_id;
4183: exit when c_all_gres_for_person%NOTFOUND;
4184:
4185: begin
4245:
4246: end;
4247: end loop;
4248: close c_all_gres_for_person;
4249: hr_utility.trace('closed all GREs for the person');
4250:
4251: ff_archive_api.create_archive_item(
4252: /* p_validate => 'TRUE' */
4253: p_archive_item_id => l_archive_item_id
4263: /*Bug 13564765 sbachu*/
4264: if result is not null and result <> 0 then
4265: ln_no_nz_fi := ln_no_nz_fi +1;
4266: end if;
4267: hr_utility.trace('ln_no_nz_fi2 = '||ln_no_nz_fi);
4268: /* bug 14030417, CPP value is negative then emp should report in
4269: error report */
4270: if result < 0 then
4271: l_negative_balance_exists := 'Y';
4319: /*Bug 13564765 starts here sbachu*/
4320: if lv_cpp_pensionable_earnings is not null and lv_cpp_pensionable_earnings <> 0 then
4321: ln_no_nz_fi := ln_no_nz_fi +1;
4322: end if;
4323: hr_utility.trace('ln_no_nz_fi5 = '||ln_no_nz_fi);
4324: lv_max_pensionable_earnings := 0;
4325: select fnd_number.canonical_to_number(information_value)
4326: into lv_max_pensionable_earnings
4327: from pay_ca_legislation_info
4332: lv_qpp_pensionable_earnings)) and
4333: (lv_taxable_benefit_with_no_rem <> 0)) then
4334: ln_no_nz_fi := ln_no_nz_fi +1;
4335: end if;
4336: hr_utility.trace('ln_no_nz_fi1 = '||ln_no_nz_fi);
4337:
4338: /* RL1 Slip number generation */
4339: l_rl1_slip_number := gen_rl1_slip_no(l_payroll_action_id);
4340: l_rl1_seq_number := gen_rl1_pdf_seq(p_assactid,
4349: l_rl1_seq_number := l_rl1_seq_number || '|' || gen_rl1_pdf_seq(p_assactid,
4350: to_char(l_year_end,'YYYY'),
4351: 'QC',
4352: 'ARCHIVER');
4353: hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4354: hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4355: end loop;
4356: else
4357: for l_i in 1..trunc(ln_no_nz_fi/ln_no_fi_per_slip)
4350: to_char(l_year_end,'YYYY'),
4351: 'QC',
4352: 'ARCHIVER');
4353: hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4354: hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4355: end loop;
4356: else
4357: for l_i in 1..trunc(ln_no_nz_fi/ln_no_fi_per_slip)
4358: loop
4360: l_rl1_seq_number := l_rl1_seq_number || '|' || gen_rl1_pdf_seq(p_assactid,
4361: to_char(l_year_end,'YYYY'),
4362: 'QC',
4363: 'ARCHIVER');
4364: hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4365: hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4366: end loop;
4367: end if;
4368: end if;
4361: to_char(l_year_end,'YYYY'),
4362: 'QC',
4363: 'ARCHIVER');
4364: hr_utility.trace('l_rl1_slip_number = '||l_rl1_slip_number);
4365: hr_utility.trace('l_rl1_seq_number = '||l_rl1_seq_number);
4366: end loop;
4367: end if;
4368: end if;
4369:
4396: ,p_context1 => 'QC'
4397: ,p_some_warning => l_some_warning
4398: );
4399: /*Bug 13564765 ends here sbachu*/
4400: --hr_utility.trace_off;
4401: end if;
4402:
4403: hr_utility.trace('Out of province loop ');
4404:
4399: /*Bug 13564765 ends here sbachu*/
4400: --hr_utility.trace_off;
4401: end if;
4402:
4403: hr_utility.trace('Out of province loop ');
4404:
4405: /* Archiving of Non-Box Footnotes */
4406: begin
4407: --hr_utility.trace_on('Y','NONBOX');
4403: hr_utility.trace('Out of province loop ');
4404:
4405: /* Archiving of Non-Box Footnotes */
4406: begin
4407: --hr_utility.trace_on('Y','NONBOX');
4408:
4409: /* Archive Nonbox footnote for Taxable Benefits that are processed on their
4410: own if the total pensionable earnings is less than the maximum bug# 3369317 */
4411:
4513: end if;
4514:
4515: l_total_mesg_amt := 0;
4516: l_mesg_amt := 0;
4517: hr_utility.trace('l_year_start - '||l_year_start);
4518: open cur_non_box_mesg(p_assactid, p_effective_date,l_year_start);
4519: loop
4520: fetch cur_non_box_mesg into l_messages,
4521: l_mesg_amt,
4525: if cur_non_box_mesg%notfound then
4526: exit;
4527: end if;
4528:
4529: hr_utility.trace('l_messages - '||l_messages);
4530: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4531: hr_utility.trace('effective date - '||p_effective_date);
4532:
4533: /* If the same Non Box footnote is processed more than
4526: exit;
4527: end if;
4528:
4529: hr_utility.trace('l_messages - '||l_messages);
4530: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4531: hr_utility.trace('effective date - '||p_effective_date);
4532:
4533: /* If the same Non Box footnote is processed more than
4534: once during the year, then the sum of the associated
4527: end if;
4528:
4529: hr_utility.trace('l_messages - '||l_messages);
4530: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4531: hr_utility.trace('effective date - '||p_effective_date);
4532:
4533: /* If the same Non Box footnote is processed more than
4534: once during the year, then the sum of the associated
4535: amounts is archived */
4540: if (c_non_box_lookup%found) then
4541: if ((l_messages <> l_prev_messages) and
4542: (l_prev_messages is not null)) then
4543:
4544: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4545:
4546: if l_total_mesg_amt <> 0 then
4547:
4548: pay_action_information_api.create_action_information(
4602: /* end if; --c_non_box_lookup%found
4603:
4604: close c_non_box_lookup; */
4605:
4606: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4607:
4608: l_prev_messages := l_messages;
4609: ln_prev_tax_unit_id := ln_tax_unit_id;
4610: ld_prev_eff_date := ld_eff_date;
4618: close cur_non_box_mesg;
4619:
4620: if (l_prev_messages is not null) then
4621:
4622: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4623: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4624:
4625: if l_total_mesg_amt <> 0 then
4626:
4619:
4620: if (l_prev_messages is not null) then
4621:
4622: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4623: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4624:
4625: if l_total_mesg_amt <> 0 then
4626:
4627: pay_action_information_api.create_action_information(
4673: end if;
4674:
4675: end if;
4676:
4677: --hr_utility.trace_off;
4678: end;
4679:
4680: ff_archive_api.create_archive_item(
4681: p_archive_item_id => l_archive_item_id
4798: l_termination_date := null;
4799:
4800: end;
4801:
4802: hr_utility.trace('Before counter of asgid '|| l_asgid);
4803:
4804: l_counter := l_counter + 1;
4805: l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
4806: l_user_entity_value_tab(l_counter):= l_person_id;
4808: l_counter := l_counter + 1;
4809: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_FIRST_NAME';
4810: l_user_entity_value_tab(l_counter):= l_first_name;
4811:
4812: hr_utility.trace('Before counter 2');
4813: l_counter := l_counter + 1;
4814: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
4815: l_user_entity_value_tab(l_counter):= l_last_name ;
4816:
4813: l_counter := l_counter + 1;
4814: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_LAST_NAME';
4815: l_user_entity_value_tab(l_counter):= l_last_name ;
4816:
4817: hr_utility.trace('Before counter 3');
4818: l_counter := l_counter + 1;
4819: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
4820: l_user_entity_value_tab(l_counter):= l_middle_name ;
4821:
4818: l_counter := l_counter + 1;
4819: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_INITIAL';
4820: l_user_entity_value_tab(l_counter):= l_middle_name ;
4821:
4822: hr_utility.trace('Before counter 3');
4823: l_counter := l_counter + 1;
4824: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
4825: l_user_entity_value_tab(l_counter):= l_national_identifier;
4826:
4823: l_counter := l_counter + 1;
4824: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_SIN';
4825: l_user_entity_value_tab(l_counter):= l_national_identifier;
4826:
4827: hr_utility.trace('Before counter 3');
4828: l_counter := l_counter + 1;
4829: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
4830: l_user_entity_value_tab(l_counter):=
4831: fnd_date.date_to_canonical(l_date_of_birth);
4829: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_DATE_OF_BIRTH';
4830: l_user_entity_value_tab(l_counter):=
4831: fnd_date.date_to_canonical(l_date_of_birth);
4832:
4833: hr_utility.trace('Before counter 3');
4834: l_counter := l_counter + 1;
4835: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
4836: l_user_entity_value_tab(l_counter):=
4837: fnd_date.date_to_canonical(l_hire_date);
4835: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_HIRE_DATE';
4836: l_user_entity_value_tab(l_counter):=
4837: fnd_date.date_to_canonical(l_hire_date);
4838:
4839: hr_utility.trace('Before counter 3');
4840: l_counter := l_counter + 1;
4841: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
4842: l_user_entity_value_tab(l_counter):=
4843: fnd_date.date_to_canonical(l_termination_date);
4841: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_TERMINATION_DATE';
4842: l_user_entity_value_tab(l_counter):=
4843: fnd_date.date_to_canonical(l_termination_date);
4844:
4845: hr_utility.trace('Before counter 3');
4846: l_counter := l_counter + 1;
4847: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_NUMBER';
4848: l_user_entity_value_tab(l_counter):= l_employee_number;
4849:
4849:
4850: for i in 1 .. l_counter
4851: loop
4852:
4853: hr_utility.trace('inside create loop '||l_user_entity_value_tab(i));
4854:
4855: ff_archive_api.create_archive_item(
4856: /* p_validate => 'TRUE' */
4857: p_archive_item_id => l_archive_item_id
4944: end if;
4945:
4946: Begin
4947:
4948: hr_utility.trace('Started Provincial YE Amendment PP Validation ');
4949: select to_char(effective_date,'YYYY'),
4950: report_type,
4951: to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID'
4952: ,legislative_parameters))
4955: ln_fapp_pre_org_id
4956: from pay_payroll_actions
4957: where payroll_action_id = l_payroll_action_id;
4958:
4959: hr_utility.trace('Prov Amend Pre-Process Pactid :'||
4960: to_char(l_payroll_action_id));
4961: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4962:
4963: /* Archive the Pre-Printed form number for the RL1 YEPP
4957: where payroll_action_id = l_payroll_action_id;
4958:
4959: hr_utility.trace('Prov Amend Pre-Process Pactid :'||
4960: to_char(l_payroll_action_id));
4961: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4962:
4963: /* Archive the Pre-Printed form number for the RL1 YEPP
4964: and Amendment Pre-Process if one exists*/
4965:
5027: open c_get_fapp_locked_action_id(p_assactid);
5028: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
5029: close c_get_fapp_locked_action_id;
5030:
5031: hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
5032: hr_utility.trace('ln_fapp_locked_action_id :'||
5033: to_char(ln_fapp_locked_action_id));
5034: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5035: fetch c_get_fapp_lkd_actid_rtype
5028: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
5029: close c_get_fapp_locked_action_id;
5030:
5031: hr_utility.trace('RL1 Amend PP Action ID : '||to_char(p_assactid));
5032: hr_utility.trace('ln_fapp_locked_action_id :'||
5033: to_char(ln_fapp_locked_action_id));
5034: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5035: fetch c_get_fapp_lkd_actid_rtype
5036: into lv_fapp_locked_actid_reptype;
5034: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5035: fetch c_get_fapp_lkd_actid_rtype
5036: into lv_fapp_locked_actid_reptype;
5037: close c_get_fapp_lkd_actid_rtype;
5038: hr_utility.trace('lv_fapp_locked_actid_reptype :'||
5039: lv_fapp_locked_actid_reptype);
5040:
5041: open c_get_fapp_prov_emp(p_assactid);
5042: loop
5041: open c_get_fapp_prov_emp(p_assactid);
5042: loop
5043: fetch c_get_fapp_prov_emp into lv_fapp_prov;
5044: exit when c_get_fapp_prov_emp%NOTFOUND;
5045: hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
5046:
5047: lv_fapp_flag := compare_archive_data(p_assactid,
5048: ln_fapp_locked_action_id,
5049: lv_fapp_prov);
5049: lv_fapp_prov);
5050:
5051: if lv_fapp_flag = 'Y' then
5052:
5053: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
5054: hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
5055:
5056: ff_archive_api.create_archive_item(
5057: p_archive_item_id => l_archive_item_id
5050:
5051: if lv_fapp_flag = 'Y' then
5052:
5053: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
5054: hr_utility.trace('Archiving RL1 Amendment Flag : ' || lv_fapp_flag);
5055:
5056: ff_archive_api.create_archive_item(
5057: p_archive_item_id => l_archive_item_id
5058: ,p_user_entity_id => get_user_entity_id('CAEOY_RL1_AMENDMENT_FLAG')
5075:
5076: end; -- report_type validation
5077:
5078: END IF; -- report type validation for FAPP
5079: hr_utility.trace('End of Provincial YE Amendment PP Validation');
5080:
5081: Exception when no_data_found then
5082: hr_utility.trace('Report type not found for given Payroll_action ');
5083: null;
5078: END IF; -- report type validation for FAPP
5079: hr_utility.trace('End of Provincial YE Amendment PP Validation');
5080:
5081: Exception when no_data_found then
5082: hr_utility.trace('Report type not found for given Payroll_action ');
5083: null;
5084: End;
5085: -- End of Provincial YE Amendment Pre-Process Validation
5086:
5157: order by asg.person_id';
5158:
5159: l_archive := chk_gre_archive(pactid);
5160: if g_archive_flag = 'N' then
5161: hr_utility.trace('eoy_range_cursor archiving employer data');
5162: /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
5163: eoy_archive_gre_data(pactid,
5164: l_pre_organization_id);
5165: hr_utility.trace('eoy_range_cursor archived employer data');
5161: hr_utility.trace('eoy_range_cursor archiving employer data');
5162: /* Now the archiver has provision for archiving payroll_action_level data . So make use of that */
5163: eoy_archive_gre_data(pactid,
5164: l_pre_organization_id);
5165: hr_utility.trace('eoy_range_cursor archived employer data');
5166: end if;
5167:
5168: end eoy_range_cursor;
5169: