[Home] [Help]
287: open csr_defined_balance;
288: fetch csr_defined_balance into l_defined_balance_id;
289: if csr_defined_balance%notfound then
290: close csr_defined_balance;
291: raise hr_utility.hr_error;
292: else
293: close csr_defined_balance;
294: end if;
295:
410: /* An invalid report type has been passed so fail. */
411:
412: else
413:
414: raise hr_utility.hr_error;
415:
416: end if;
417:
418: end get_selection_information;
594:
595: /* Get the report type, report qualifier, business group id and the
596: gre for which the archiving has to be done */
597:
598: hr_utility.trace('getting report type ');
599:
600: select effective_date,
601: report_type,
602: business_group_id,
609: where payroll_action_id = pactid;
610:
611: l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
612:
613: hr_utility.trace('getting dates');
614:
615: get_dates(l_report_type,
616: l_effective_date,
617: l_period_end,
619: l_quarter_end,
620: l_year_start,
621: l_year_end);
622:
623: hr_utility.trace('getting selection information');
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
620: l_year_start,
621: l_year_end);
622:
623: hr_utility.trace('getting selection information');
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
621: l_year_end);
622:
623: hr_utility.trace('getting selection information');
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
629:
622:
623: hr_utility.trace('getting selection information');
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
629:
630: get_selection_information
623: hr_utility.trace('getting selection information');
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
629:
630: get_selection_information
631: (l_report_type,
624: hr_utility.trace('report type '|| l_report_type);
625: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
626: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
627: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
628: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
629:
630: get_selection_information
631: (l_report_type,
632: l_quarter_start,
648: end if;
649:
650: /* Loop for all rows returned for SQL statement. */
651:
652: hr_utility.trace('Entering loop');
653:
654: loop
655:
656: if l_eoy_tax_unit_id <> 99999 then
654: loop
655:
656: if l_eoy_tax_unit_id <> 99999 then
657:
658: hr_utility.trace('Fetching person id');
659:
660: fetch c_eoy_gre into l_person_id,
661: l_assignment_id,
662: l_tax_unit_id,
679: /* If the new row is the same as the previous row according to the way
680: the rows are grouped then discard the row ie. grouping by GRE
681: requires a single row for each person / GRE combination. */
682:
683: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
684: hr_utility.trace('previous tax unit id is '||
685: to_char(l_prev_tax_unit_id));
686:
687: if ( l_person_id = l_prev_person_id and
680: the rows are grouped then discard the row ie. grouping by GRE
681: requires a single row for each person / GRE combination. */
682:
683: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
684: hr_utility.trace('previous tax unit id is '||
685: to_char(l_prev_tax_unit_id));
686:
687: if ( l_person_id = l_prev_person_id and
688: l_tax_unit_id = l_prev_tax_unit_id) then
689: null;
690:
691: else
692:
693: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
694: hr_utility.trace('person is '|| to_char(l_person_id));
695: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
696:
697:
690:
691: else
692:
693: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
694: hr_utility.trace('person is '|| to_char(l_person_id));
695: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
696:
697:
698: /* Have a new unique row according to the way the rows are grouped.
691: else
692:
693: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
694: hr_utility.trace('person is '|| to_char(l_person_id));
695: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
696:
697:
698: /* Have a new unique row according to the way the rows are grouped.
699: ** The inclusion of the person is dependent on having a non zero
702: ** indicate their inclusion in the magnetic tape report. */
703:
704: /* Set up the context of tax unit id */
705:
706: hr_utility.trace('Setting context');
707:
708: if l_tax_unit_context then
709: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
710: end if;
750: open c_get_asg_id(l_person_id);
751: fetch c_get_asg_id into l_primary_asg;
752: if c_get_asg_id%NOTFOUND then
753: close c_get_asg_id;
754: raise hr_utility.hr_error;
755: else
756: close c_get_asg_id;
757: end if;
758:
775: from dual;
776:
777: /* Insert into pay_assignment_actions. */
778:
779: hr_utility.trace('creating assignment action');
780:
781: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
782: pactid,chunk,l_tax_unit_id);
783:
786: ** to do an additional checking against the assignment
787: ** table
788: */
789:
790: hr_utility.trace('updating assignment action');
791:
792: update pay_assignment_actions aa
793: set aa.serial_number = to_char(l_person_id)
794: where aa.assignment_action_id = lockingactid;
827: l_user_entity_id number;
828:
829: begin
830:
831: hr_utility.trace('getting the user_entity_id for '
832: || p_dbi_name);
833: select fdi.user_entity_id
834: into l_user_entity_id
835: from ff_database_items fdi,
841: return l_user_entity_id;
842:
843: exception
844: when others then
845: hr_utility.trace('Error while getting the user_entity_id for '
846: || p_dbi_name ||'**');
847: raise hr_utility.hr_error;
848:
849: end get_user_entity_id;
843: exception
844: when others then
845: hr_utility.trace('Error while getting the user_entity_id for '
846: || p_dbi_name ||'**');
847: raise hr_utility.hr_error;
848:
849: end get_user_entity_id;
850:
851: /* Name : get_footnote_user_entity_id
869: return l_user_entity_id;
870:
871: exception
872: when others then
873: hr_utility.trace('Error while getting the user_entity_id'
874: || p_dbi_name ||'***');
875: return 0;
876:
877: end get_footnote_user_entity_id;
939: ln_yepp_footnote_count number;
940: ln_amend_footnote_count number;
941:
942: begin
943: -- hr_utility.trace_on('Y','TEST');
944:
945: /* Initialization Process */
946:
947: lv_flag := 'N';
964:
965: /* Populate T4A Amendment Footnotes */
966: open c_get_nonbox_footnote(p_assignment_action_id);
967:
968: hr_utility.trace('Populating T4A Amendment Footnote ');
969:
970: ln_amend_footnote_count := 0;
971: loop
972: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
971: loop
972: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
973: exit when c_get_nonbox_footnote%NOTFOUND;
974:
975: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
976: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
977:
978: ln_amend_footnote_count := ln_amend_footnote_count + 1;
979: end loop;
972: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
973: exit when c_get_nonbox_footnote%NOTFOUND;
974:
975: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
976: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
977:
978: ln_amend_footnote_count := ln_amend_footnote_count + 1;
979: end loop;
980:
987: loop
988: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
989: exit when c_get_nonbox_footnote%NOTFOUND;
990:
991: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
992: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
993:
994: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
995: end loop;
988: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
989: exit when c_get_nonbox_footnote%NOTFOUND;
990:
991: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
992: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
993:
994: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
995: end loop;
996:
999:
1000: /* Populate T4A Amendment Data for an assignment_action */
1001: open c_get_emp_t4a_data(p_assignment_action_id);
1002:
1003: hr_utility.trace('Populating T4A Amendment Data ');
1004: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1005:
1006: ln_amend_box_count := 0;
1007: loop
1000: /* Populate T4A Amendment Data for an assignment_action */
1001: open c_get_emp_t4a_data(p_assignment_action_id);
1002:
1003: hr_utility.trace('Populating T4A Amendment Data ');
1004: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1005:
1006: ln_amend_box_count := 0;
1007: loop
1008: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1007: loop
1008: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1009: exit when c_get_emp_t4a_data%NOTFOUND;
1010:
1011: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1012: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1013: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1014: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1015:
1008: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1009: exit when c_get_emp_t4a_data%NOTFOUND;
1010:
1011: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1012: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1013: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1014: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1015:
1016: ln_amend_box_count := ln_amend_box_count + 1;
1009: exit when c_get_emp_t4a_data%NOTFOUND;
1010:
1011: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1012: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1013: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1014: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1015:
1016: ln_amend_box_count := ln_amend_box_count + 1;
1017: end loop;
1010:
1011: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1012: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1013: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1014: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1015:
1016: ln_amend_box_count := ln_amend_box_count + 1;
1017: end loop;
1018:
1021:
1022: /* Populate T4A YEPP Data for an assignment_action */
1023: open c_get_emp_t4a_data(p_locked_action_id);
1024:
1025: hr_utility.trace('Populating T4A YEPP Data ');
1026: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1027:
1028: ln_yepp_box_count := 0;
1029: loop
1022: /* Populate T4A YEPP Data for an assignment_action */
1023: open c_get_emp_t4a_data(p_locked_action_id);
1024:
1025: hr_utility.trace('Populating T4A YEPP Data ');
1026: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1027:
1028: ln_yepp_box_count := 0;
1029: loop
1030: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1029: loop
1030: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1031: exit when c_get_emp_t4a_data%NOTFOUND;
1032:
1033: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1034: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1035: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1036: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1037:
1030: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1031: exit when c_get_emp_t4a_data%NOTFOUND;
1032:
1033: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1034: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1035: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1036: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1037:
1038: ln_yepp_box_count := ln_yepp_box_count + 1;
1031: exit when c_get_emp_t4a_data%NOTFOUND;
1032:
1033: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1034: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1035: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1036: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1037:
1038: ln_yepp_box_count := ln_yepp_box_count + 1;
1039: end loop;
1032:
1033: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1034: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1035: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1036: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1037:
1038: ln_yepp_box_count := ln_yepp_box_count + 1;
1039: end loop;
1040:
1042:
1043: /* Compare T4A Amendment Data and T4A YEPP Data for an
1044: assignment_action */
1045:
1046: hr_utility.trace('Comparing T4A Amend and T4A YEPP Data');
1047:
1048: if ln_yepp_box_count <> ln_amend_box_count then
1049: lv_flag := 'Y';
1050: elsif ln_yepp_box_count = ln_amend_box_count then
1060: (ltr_yepp_arch_data(i).archive_value is not null and
1061: ltr_amend_arch_data(i).archive_value is null)) then
1062:
1063: lv_flag := 'Y';
1064: hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1065: exit;
1066: end if;
1067: end if;
1068: end loop;
1070:
1071: /* Compare T4A Amendment Footnotes and T4A YEPP Footnotes for an
1072: assignment_action */
1073:
1074: hr_utility.trace('Comparing T4A Amend and T4A YEPP Footnotes');
1075:
1076: if lv_flag <> 'Y' then
1077: if ln_yepp_footnote_count <> ln_amend_footnote_count then
1078: lv_flag := 'Y';
1090: (ltr_yepp_footnote(i).value is not null and
1091: ltr_amend_footnote(i).value is null)) then
1092:
1093: lv_flag := 'Y';
1094: hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
1095: exit;
1096: end if;
1097: end if;
1098: end loop;
1103: flag to 'N' */
1104:
1105: if lv_flag <> 'Y' then
1106: lv_flag := 'N';
1107: hr_utility.trace('No value difference for an Employee Asg Action: '||
1108: to_char(p_assignment_action_id));
1109: end if;
1110:
1111: hr_utility.trace('lv_flag :'||lv_flag);
1107: hr_utility.trace('No value difference for an Employee Asg Action: '||
1108: to_char(p_assignment_action_id));
1109: end if;
1110:
1111: hr_utility.trace('lv_flag :'||lv_flag);
1112:
1113: return lv_flag;
1114:
1115: -- hr_utility.trace_off;
1111: hr_utility.trace('lv_flag :'||lv_flag);
1112:
1113: return lv_flag;
1114:
1115: -- hr_utility.trace_off;
1116:
1117: end compare_archive_data;
1118:
1119:
1294:
1295: /* Archive the Employer level data */
1296:
1297: begin
1298: hr_utility.trace('getting employer data ');
1299:
1300: open employer_info;
1301: fetch employer_info
1302: into l_name, l_business_group_id,
1329:
1330:
1331: if employer_info%FOUND then
1332: close employer_info;
1333: hr_utility.trace('got employer data ');
1334: else
1335: hr_utility.trace('cannot find employer data ');
1336: l_employer_ein := null;
1337: l_address_line_1 := null;
1331: if employer_info%FOUND then
1332: close employer_info;
1333: hr_utility.trace('got employer data ');
1334: else
1335: hr_utility.trace('cannot find employer data ');
1336: l_employer_ein := null;
1337: l_address_line_1 := null;
1338: l_address_line_2 := null;
1339: l_address_line_3 := null;
1354: l_technical_contact_phone := null;
1355: l_technical_contact_language := null;
1356:
1357: close employer_info;
1358: hr_utility.raise_error;
1359: end if;
1360:
1361: /* Added this part to fix bug#2598802 */
1362: if c_get_gre_acct_info%found then
1562:
1563: for i in 1..l_counter loop
1564:
1565: l_arch_gre_step := 42;
1566: hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1567: ff_archive_api.create_archive_item(
1568: -- p_validate => 'TRUE'
1569: p_archive_item_id => l_archive_item_id
1570: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1574: ,p_legislation_code => 'CA'
1575: ,p_object_version_number => l_object_version_number
1576: ,p_some_warning => l_some_warning
1577: );
1578: hr_utility.trace('Ended calling archive API');
1579: l_arch_gre_step := 47;
1580: end loop;
1581:
1582: /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1580: end loop;
1581:
1582: /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1583:
1584: --hr_utility.trace_off;
1585: g_archive_flag := 'Y';
1586: exception
1587: when others then
1588: g_archive_flag := 'N';
1586: exception
1587: when others then
1588: g_archive_flag := 'N';
1589: if l_transmitter_name is null then
1590: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1591: hr_utility.set_message_token('ORGIND','GRE');
1592: /* push message into pay_message_lines */
1593: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1594: pay_core_utils.push_token('ORGIND','GRE');
1587: when others then
1588: g_archive_flag := 'N';
1589: if l_transmitter_name is null then
1590: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1591: hr_utility.set_message_token('ORGIND','GRE');
1592: /* push message into pay_message_lines */
1593: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1594: pay_core_utils.push_token('ORGIND','GRE');
1595: hr_utility.raise_error;
1591: hr_utility.set_message_token('ORGIND','GRE');
1592: /* push message into pay_message_lines */
1593: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1594: pay_core_utils.push_token('ORGIND','GRE');
1595: hr_utility.raise_error;
1596: else
1597: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1598: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1593: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1594: pay_core_utils.push_token('ORGIND','GRE');
1595: hr_utility.raise_error;
1596: else
1597: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1598: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1600: hr_utility.raise_error;
1601: end if;
1595: hr_utility.raise_error;
1596: else
1597: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1598: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1600: hr_utility.raise_error;
1601: end if;
1602: raise hr_utility.hr_error;
1603:
1596: else
1597: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1598: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1600: hr_utility.raise_error;
1601: end if;
1602: raise hr_utility.hr_error;
1603:
1604: end eoy_archive_gre_data;
1598: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1599: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1600: hr_utility.raise_error;
1601: end if;
1602: raise hr_utility.hr_error;
1603:
1604: end eoy_archive_gre_data;
1605:
1606: /* Name : chk_gre_archive
1622: where fai.context1 = p_payroll_action_id
1623: and archive_type = 'PA');
1624: begin
1625:
1626: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1627:
1628: if g_archive_flag = 'Y' then
1629: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1630: return (TRUE);
1625:
1626: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1627:
1628: if g_archive_flag = 'Y' then
1629: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1630: return (TRUE);
1631: else
1632:
1633: hr_utility.trace('chk_gre_archive - opening cursor');
1629: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1630: return (TRUE);
1631: else
1632:
1633: hr_utility.trace('chk_gre_archive - opening cursor');
1634:
1635: open c_chk_payroll_action;
1636: fetch c_chk_payroll_action into l_flag;
1637: if c_chk_payroll_action%FOUND then
1634:
1635: open c_chk_payroll_action;
1636: fetch c_chk_payroll_action into l_flag;
1637: if c_chk_payroll_action%FOUND then
1638: hr_utility.trace('chk_gre_archive - found in cursor');
1639: g_archive_flag := 'Y';
1640: else
1641: hr_utility.trace('chk_gre_archive - not found in cursor');
1642: g_archive_flag := 'N';
1637: if c_chk_payroll_action%FOUND then
1638: hr_utility.trace('chk_gre_archive - found in cursor');
1639: g_archive_flag := 'Y';
1640: else
1641: hr_utility.trace('chk_gre_archive - not found in cursor');
1642: g_archive_flag := 'N';
1643: end if;
1644:
1645: hr_utility.trace('chk_gre_archive - closing cursor');
1641: hr_utility.trace('chk_gre_archive - not found in cursor');
1642: g_archive_flag := 'N';
1643: end if;
1644:
1645: hr_utility.trace('chk_gre_archive - closing cursor');
1646: close c_chk_payroll_action;
1647: if g_archive_flag = 'Y' then
1648: hr_utility.trace('chk_gre_archive - returning true');
1649: return (TRUE);
1644:
1645: hr_utility.trace('chk_gre_archive - closing cursor');
1646: close c_chk_payroll_action;
1647: if g_archive_flag = 'Y' then
1648: hr_utility.trace('chk_gre_archive - returning true');
1649: return (TRUE);
1650: else
1651: hr_utility.trace('chk_gre_archive - returning false');
1652: return(FALSE);
1647: if g_archive_flag = 'Y' then
1648: hr_utility.trace('chk_gre_archive - returning true');
1649: return (TRUE);
1650: else
1651: hr_utility.trace('chk_gre_archive - returning false');
1652: return(FALSE);
1653: end if;
1654: end if;
1655: end chk_gre_archive;
1682: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1683: end if;
1684: close c_get_min_chunk;
1685:
1686: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1687: l_step := 2;
1688: l_archive := chk_gre_archive(p_payroll_action_id);
1689:
1690: l_step := 3;
1687: l_step := 2;
1688: l_archive := chk_gre_archive(p_payroll_action_id);
1689:
1690: l_step := 3;
1691: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1692: exception
1693: when others then
1694: raise_application_error(-20001,'eoy_archinit at '
1695: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2006: pca.effective_end_date;
2007:
2008: begin
2009:
2010: -- hr_utility.trace_on('Y','ORACLEMM');
2011:
2012: l_count := 0;
2013: l_box38_footnote_code := '00';
2014: l_negative_balance_exists := 'N';
2012: l_count := 0;
2013: l_box38_footnote_code := '00';
2014: l_negative_balance_exists := 'N';
2015:
2016: hr_utility.set_location ('archive_data',1);
2017: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2018:
2019:
2020: SELECT aa.assignment_id,
2013: l_box38_footnote_code := '00';
2014: l_negative_balance_exists := 'N';
2015:
2016: hr_utility.set_location ('archive_data',1);
2017: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2018:
2019:
2020: SELECT aa.assignment_id,
2021: pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2046: where organization_id = l_tax_unit_id
2047: and org_information_context = 'Canada Employer Identification';
2048:
2049: l_step := 3;
2050: hr_utility.trace('eoy_archive_data archiving employer data');
2051: eoy_archive_gre_data(l_payroll_action_id,
2052: l_tax_unit_id,
2053: l_transmitter_gre_id);
2054:
2052: l_tax_unit_id,
2053: l_transmitter_gre_id);
2054:
2055: l_step := 4;
2056: hr_utility.trace('eoy_archive_data archived employer data');
2057: else
2058: g_archive_flag := 'Y';
2059: end if;
2060:
2067: else
2068: lv_footnote_element := 'T4A NonBox Footnotes';
2069: end if;
2070:
2071: hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
2072:
2073: /* YE-2001 change to avoid hr_ca_tax_units_v view */
2074: select business_group_id
2075: into l_business_group_id
2106: begin
2107: open c_get_latest_asg(lv_serial_number );
2108: fetch c_get_latest_asg into l_aaid;
2109: close c_get_latest_asg;
2110: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2111:
2112: exception
2113: when no_data_found then
2114: l_aaid := -9999;
2114: l_aaid := -9999;
2115: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2116: ||to_char(l_person_id));
2117: end;
2118: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2119: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2120: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2121:
2122:
2115: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2116: ||to_char(l_person_id));
2117: end;
2118: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2119: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2120: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2121:
2122:
2123:
2116: ||to_char(l_person_id));
2117: end;
2118: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2119: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2120: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2121:
2122:
2123:
2124: /* Assign values to the PL/SQL tables */
2212: <> 0) ) then
2213:
2214: earning_exists := 1;
2215:
2216: hr_utility.trace('starting loop for balances');
2217:
2218: for i in 1 .. l_count
2219: loop
2220: result := 0;
2232:
2233: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2234: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2235:
2236: hr_utility.trace('i is ' ||to_char(i));
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2233: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2234: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2235:
2236: hr_utility.trace('i is ' ||to_char(i));
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2234: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2235:
2236: hr_utility.trace('i is ' ||to_char(i));
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2242:
2235:
2236: hr_utility.trace('i is ' ||to_char(i));
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2242:
2243: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2236: hr_utility.trace('i is ' ||to_char(i));
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2242:
2243: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2244: ( l_balance_type_tab(i),
2237: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2238: hr_utility.trace('AAID is ' || to_char(l_aaid));
2239: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2240: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2241: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2242:
2243: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2244: ( l_balance_type_tab(i),
2245: 'YTD' ,
2270: l_single_footnote_code := NULL;
2271:
2272: if result <> 0 then
2273:
2274: /* hr_utility.trace_on('Y','T4AARCH'); */
2275: hr_utility.trace('Result is ' || to_char(result));
2276: /* Check the footnote amounts and codes and archive them */
2277: /* Check which elements has fed the balance and what are their
2278: footnotes and if the primary balance for that element is 0 or not */
2271:
2272: if result <> 0 then
2273:
2274: /* hr_utility.trace_on('Y','T4AARCH'); */
2275: hr_utility.trace('Result is ' || to_char(result));
2276: /* Check the footnote amounts and codes and archive them */
2277: /* Check which elements has fed the balance and what are their
2278: footnotes and if the primary balance for that element is 0 or not */
2279:
2285: 'T4A_BOX28',
2286: 'T4A_BOX32',
2287: 'T4A_BOX40') then
2288: begin
2289: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2286: 'T4A_BOX32',
2287: 'T4A_BOX40') then
2288: begin
2289: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2287: 'T4A_BOX40') then
2288: begin
2289: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2288: begin
2289: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2289: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2297:
2290: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2297:
2298: open c_balance_feed_info(l_balance_type_tab(i));
2291: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2297:
2298: open c_balance_feed_info(l_balance_type_tab(i));
2299:
2292: hr_utility.trace('l_footnote_code : '||l_footnote_code);
2293: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
2294: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
2295: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2297:
2298: open c_balance_feed_info(l_balance_type_tab(i));
2299:
2300: hr_utility.trace('balance_type '||l_balance_type_tab(i));
2296: hr_utility.trace('l_box38_count :'||l_box38_count);
2297:
2298: open c_balance_feed_info(l_balance_type_tab(i));
2299:
2300: hr_utility.trace('balance_type '||l_balance_type_tab(i));
2301:
2302: loop
2303:
2304: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
2300: hr_utility.trace('balance_type '||l_balance_type_tab(i));
2301:
2302: loop
2303:
2304: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
2305: hr_utility.trace('666 l_count '|| l_count);
2306: hr_utility.trace('666 p_assactid '|| p_assactid);
2307: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
2308: if l_balance_type_tab(i) = 'T4A_BOX24'
2301:
2302: loop
2303:
2304: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
2305: hr_utility.trace('666 l_count '|| l_count);
2306: hr_utility.trace('666 p_assactid '|| p_assactid);
2307: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
2308: if l_balance_type_tab(i) = 'T4A_BOX24'
2309: and l_footnote_code = '10(BOX24)' then
2302: loop
2303:
2304: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
2305: hr_utility.trace('666 l_count '|| l_count);
2306: hr_utility.trace('666 p_assactid '|| p_assactid);
2307: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
2308: if l_balance_type_tab(i) = 'T4A_BOX24'
2309: and l_footnote_code = '10(BOX24)' then
2310: l_footnote_code := '10A';
2311: end if;
2312: exit when c_balance_feed_info%NOTFOUND;
2313: l_count := l_count + 1;
2314:
2315: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
2316: hr_utility.trace('l_footnotecode '||l_footnote_code);
2317: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
2318: if l_footnote_code <> old_l_footnote_code then /* footnote
2319: changed */
2312: exit when c_balance_feed_info%NOTFOUND;
2313: l_count := l_count + 1;
2314:
2315: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
2316: hr_utility.trace('l_footnotecode '||l_footnote_code);
2317: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
2318: if l_footnote_code <> old_l_footnote_code then /* footnote
2319: changed */
2320: if old_l_footnote_code is not null then /* not the first
2313: l_count := l_count + 1;
2314:
2315: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
2316: hr_utility.trace('l_footnotecode '||l_footnote_code);
2317: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
2318: if l_footnote_code <> old_l_footnote_code then /* footnote
2319: changed */
2320: if old_l_footnote_code is not null then /* not the first
2321: record */
2319: changed */
2320: if old_l_footnote_code is not null then /* not the first
2321: record */
2322:
2323: hr_utility.trace('archive ft_amount_ue'
2324: ||l_footnote_amount_ue);
2325: hr_utility.trace('archive ft_amount'
2326: ||to_char(l_footnote_amount));
2327: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2321: record */
2322:
2323: hr_utility.trace('archive ft_amount_ue'
2324: ||l_footnote_amount_ue);
2325: hr_utility.trace('archive ft_amount'
2326: ||to_char(l_footnote_amount));
2327: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2328: and l_footnote_amount <> 0 then
2329:
2370: l_tax_unit_id,
2371: l_business_group_id,
2372: NULL );
2373:
2374: hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
2375: hr_utility.trace('666 l_value '|| l_value);
2376: l_footnote_amount := l_footnote_amount + l_value ;
2377:
2378: /* to fix bug#2426517 added one more validation to if stmt */
2371: l_business_group_id,
2372: NULL );
2373:
2374: hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
2375: hr_utility.trace('666 l_value '|| l_value);
2376: l_footnote_amount := l_footnote_amount + l_value ;
2377:
2378: /* to fix bug#2426517 added one more validation to if stmt */
2379: /* if (l_value <> 0 and
2382: l_no_of_fn_codes := l_no_of_fn_codes + 1;
2383: l_box38_count := l_box38_count + 1;
2384: l_single_footnote_code := l_footnote_code;
2385:
2386: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
2387: hr_utility.trace('chk l_box38_count '|| l_box38_count);
2388: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
2389: end if;
2390: */
2383: l_box38_count := l_box38_count + 1;
2384: l_single_footnote_code := l_footnote_code;
2385:
2386: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
2387: hr_utility.trace('chk l_box38_count '|| l_box38_count);
2388: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
2389: end if;
2390: */
2391: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
2384: l_single_footnote_code := l_footnote_code;
2385:
2386: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
2387: hr_utility.trace('chk l_box38_count '|| l_box38_count);
2388: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
2389: end if;
2390: */
2391: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
2392: end loop;
2387: hr_utility.trace('chk l_box38_count '|| l_box38_count);
2388: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
2389: end if;
2390: */
2391: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
2392: end loop;
2393: close c_balance_feed_info;
2394:
2395: if l_footnote_code is not null and
2396: l_footnote_amount_ue is not null and
2397: l_footnote_amount <> 0 and
2398: get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2399: then
2400: hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
2401: hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
2402:
2403: ff_archive_api.create_archive_item(
2404: p_archive_item_id => l_archive_item_id
2397: l_footnote_amount <> 0 and
2398: get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
2399: then
2400: hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
2401: hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
2402:
2403: ff_archive_api.create_archive_item(
2404: p_archive_item_id => l_archive_item_id
2405: ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
2422: end if;
2423:
2424: end if;
2425:
2426: hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
2427: if l_no_of_fn_codes > 1 then
2428: l_footnote_code := '13';
2429: hr_utility.trace('666archive footnote code '|| l_footnote_code);
2430: /* changed here as part of bugfix#2426517 */
2425:
2426: hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
2427: if l_no_of_fn_codes > 1 then
2428: l_footnote_code := '13';
2429: hr_utility.trace('666archive footnote code '|| l_footnote_code);
2430: /* changed here as part of bugfix#2426517 */
2431: elsif l_no_of_fn_codes = 1 then
2432: l_footnote_code := l_single_footnote_code;
2433: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
2429: hr_utility.trace('666archive footnote code '|| l_footnote_code);
2430: /* changed here as part of bugfix#2426517 */
2431: elsif l_no_of_fn_codes = 1 then
2432: l_footnote_code := l_single_footnote_code;
2433: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
2434: elsif l_no_of_fn_codes = 0 then
2435: l_footnote_code := '00';
2436: hr_utility.trace('666archive footnote code '|| l_footnote_code);
2437: end if;
2432: l_footnote_code := l_single_footnote_code;
2433: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
2434: elsif l_no_of_fn_codes = 0 then
2435: l_footnote_code := '00';
2436: hr_utility.trace('666archive footnote code '|| l_footnote_code);
2437: end if;
2438:
2439: l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
2440:
2437: end if;
2438:
2439: l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
2440:
2441: hr_utility.trace('before archiving l_footnote_code_ue is '|| l_footnote_code_ue);
2442: /* Part of fix for bug#2426517, to avoid unnecessary archiving
2443: of footnote code added one more condiftion to if stmt before
2444: archiving the footnote code for the corresponding BOX balance */
2445:
2445:
2446: if l_footnote_code is not null and l_no_of_fn_codes > 0 and
2447: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
2448:
2449: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
2450: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
2451: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
2452: ff_archive_api.create_archive_item(
2453: -- p_validate => 'TRUE'
2446: if l_footnote_code is not null and l_no_of_fn_codes > 0 and
2447: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
2448:
2449: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
2450: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
2451: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
2452: ff_archive_api.create_archive_item(
2453: -- p_validate => 'TRUE'
2454: p_archive_item_id => l_archive_item_id
2447: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
2448:
2449: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
2450: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
2451: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
2452: ff_archive_api.create_archive_item(
2453: -- p_validate => 'TRUE'
2454: p_archive_item_id => l_archive_item_id
2455: ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_code_ue)
2465: end if;
2466:
2467: /* assigning value to box38_footnote_code */
2468:
2469: hr_utility.trace('999 l_box38_count '|| l_box38_count);
2470: /* initialised l_box38_footnote_code before checking
2471: gross earnings to this assignment action fix#2426517 */
2472:
2473: if l_box38_count > 1 then
2471: gross earnings to this assignment action fix#2426517 */
2472:
2473: if l_box38_count > 1 then
2474: l_box38_footnote_code := '13';
2475: hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
2476: /* Added one more condition to archive correct footnote code
2477: value for box38 as part of bug fix#2426517 and assigned
2478: l_single_footnote_code to l_box38_footnote_code variable */
2479: elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
2477: value for box38 as part of bug fix#2426517 and assigned
2478: l_single_footnote_code to l_box38_footnote_code variable */
2479: elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
2480: l_box38_footnote_code := l_single_footnote_code;
2481: hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
2482: end if;
2483:
2484: end;
2485: end if;
2485: end if;
2486: end if;
2487: /** End of Footnote archiving **/
2488:
2489: --hr_utility.trace_off;
2490:
2491: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2492: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2493: hr_utility.trace('Result is ' || to_char(result));
2487: /** End of Footnote archiving **/
2488:
2489: --hr_utility.trace_off;
2490:
2491: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2492: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2493: hr_utility.trace('Result is ' || to_char(result));
2494:
2495: /* Added this condition to fix bug#2598777 */
2488:
2489: --hr_utility.trace_off;
2490:
2491: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2492: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2493: hr_utility.trace('Result is ' || to_char(result));
2494:
2495: /* Added this condition to fix bug#2598777 */
2496: if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
2489: --hr_utility.trace_off;
2490:
2491: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2492: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2493: hr_utility.trace('Result is ' || to_char(result));
2494:
2495: /* Added this condition to fix bug#2598777 */
2496: if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
2497: result := round(result);
2523:
2524: end loop; /* for archiving all T4A Balances */
2525:
2526: /** Start box38 footnote archiving **/
2527: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2528: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
2529: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
2530:
2531: /** box38 footnote archive has been moved after nonbox footnote archive **/
2524: end loop; /* for archiving all T4A Balances */
2525:
2526: /** Start box38 footnote archiving **/
2527: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2528: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
2529: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
2530:
2531: /** box38 footnote archive has been moved after nonbox footnote archive **/
2532:
2525:
2526: /** Start box38 footnote archiving **/
2527: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2528: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
2529: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
2530:
2531: /** box38 footnote archive has been moved after nonbox footnote archive **/
2532:
2533: /* start registration number archiving */
2552: fetch c_reg_balance_feed_info into l_registration_no,l_balance_name,
2553: l_element_type_id,l_ele_classification_id;
2554: exit when c_reg_balance_feed_info%NOTFOUND;
2555:
2556: hr_utility.trace('checking for T4A_BOX34');
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2553: l_element_type_id,l_ele_classification_id;
2554: exit when c_reg_balance_feed_info%NOTFOUND;
2555:
2556: hr_utility.trace('checking for T4A_BOX34');
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2554: exit when c_reg_balance_feed_info%NOTFOUND;
2555:
2556: hr_utility.trace('checking for T4A_BOX34');
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2555:
2556: hr_utility.trace('checking for T4A_BOX34');
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2556: hr_utility.trace('checking for T4A_BOX34');
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2564:
2557: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2564:
2565: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2558: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2564:
2565: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2566: ( l_balance_name,
2559: hr_utility.trace('l_registration_no:'||l_registration_no);
2560: hr_utility.trace('l_balance_name:'||l_balance_name);
2561: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2562: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
2563: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
2564:
2565: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2566: ( l_balance_name,
2567: 'YTD' ,
2572: l_tax_unit_id,
2573: l_business_group_id,
2574: NULL );
2575:
2576: hr_utility.trace('before check null l_value:'||to_char(l_value));
2577: if l_value is null then
2578: l_value := 0;
2579: end if;
2580:
2577: if l_value is null then
2578: l_value := 0;
2579: end if;
2580:
2581: hr_utility.trace('after check null l_value:'||to_char(l_value));
2582:
2583:
2584: /* Condition to check the amounts and determine the registration
2585: number to archive Bug fix 2408456 */
2583:
2584: /* Condition to check the amounts and determine the registration
2585: number to archive Bug fix 2408456 */
2586: if old_l_value = 0 then
2587: hr_utility.trace('in reg1');
2588: old_l_value := l_value;
2589: old_l_registration_no := l_registration_no;
2590: elsif old_l_value1 = 0 then
2591: hr_utility.trace('in reg2');
2587: hr_utility.trace('in reg1');
2588: old_l_value := l_value;
2589: old_l_registration_no := l_registration_no;
2590: elsif old_l_value1 = 0 then
2591: hr_utility.trace('in reg2');
2592: old_l_value1 := l_value;
2593: old_l_registration_no1 := l_registration_no;
2594: elsif old_l_value2 = 0 then
2595: hr_utility.trace('in reg3');
2591: hr_utility.trace('in reg2');
2592: old_l_value1 := l_value;
2593: old_l_registration_no1 := l_registration_no;
2594: elsif old_l_value2 = 0 then
2595: hr_utility.trace('in reg3');
2596: old_l_value2 := l_value;
2597: old_l_registration_no2 := l_registration_no;
2598: else
2599: if l_value > nvl(old_l_value,0) then
2596: old_l_value2 := l_value;
2597: old_l_registration_no2 := l_registration_no;
2598: else
2599: if l_value > nvl(old_l_value,0) then
2600: hr_utility.trace('old_l1');
2601: old_l_value := l_value;
2602: old_l_registration_no := l_registration_no;
2603: elsif l_value > nvl(old_l_value1,0) then
2604: hr_utility.trace('old_2');
2600: hr_utility.trace('old_l1');
2601: old_l_value := l_value;
2602: old_l_registration_no := l_registration_no;
2603: elsif l_value > nvl(old_l_value1,0) then
2604: hr_utility.trace('old_2');
2605: old_l_value1 := l_value;
2606: old_l_registration_no1 := l_registration_no;
2607: elsif l_value > nvl(old_l_value2,0) then
2608: old_l_value2 := l_value;
2613:
2614: end loop;
2615: close c_reg_balance_feed_info;
2616: if old_l_value > old_l_value1 then
2617: hr_utility.trace('in reg4');
2618: if old_l_value> old_l_value2 then
2619: arch_l_registration_no := old_l_registration_no;
2620: arch_l_value := old_l_value;
2621: else
2648: );
2649: end if;
2650:
2651: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2652: hr_utility.trace('Start of Employer Level PP Reg no ');
2653:
2654: if old_l_registration_no is not null and old_l_value >0 then
2655: hr_utility.trace('in reg1 pay_action_information');
2656: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2651: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2652: hr_utility.trace('Start of Employer Level PP Reg no ');
2653:
2654: if old_l_registration_no is not null and old_l_value >0 then
2655: hr_utility.trace('in reg1 pay_action_information');
2656: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2657: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2658:
2659: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2652: hr_utility.trace('Start of Employer Level PP Reg no ');
2653:
2654: if old_l_registration_no is not null and old_l_value >0 then
2655: hr_utility.trace('in reg1 pay_action_information');
2656: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2657: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2658:
2659: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2660: l_payroll_action_id
2653:
2654: if old_l_registration_no is not null and old_l_value >0 then
2655: hr_utility.trace('in reg1 pay_action_information');
2656: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2657: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2658:
2659: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2660: l_payroll_action_id
2661: ,old_l_registration_no
2661: ,old_l_registration_no
2662: ,p_effective_date);
2663: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2664: if c_get_emplr_reg_no%FOUND then
2665: hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
2666:
2667: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2668:
2669: update pay_action_information
2676:
2677:
2678: else
2679:
2680: hr_utility.trace('in reg1 insert pay_action_information');
2681: -- insert a new record into pay_action_information
2682:
2683: pay_action_information_api.create_action_information(
2684: p_action_information_id => l_action_information_id_1,
2725: close c_get_emplr_reg_no;
2726: end if;
2727:
2728: if old_l_registration_no1 is not null and old_l_value1 >0 then
2729: hr_utility.trace('in reg2 pay_action_information');
2730:
2731: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2732: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2733:
2727:
2728: if old_l_registration_no1 is not null and old_l_value1 >0 then
2729: hr_utility.trace('in reg2 pay_action_information');
2730:
2731: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2732: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2733:
2734: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2735: l_payroll_action_id
2728: if old_l_registration_no1 is not null and old_l_value1 >0 then
2729: hr_utility.trace('in reg2 pay_action_information');
2730:
2731: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2732: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2733:
2734: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2735: l_payroll_action_id
2736: , old_l_registration_no1
2737: ,p_effective_date);
2738: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
2739: if c_get_emplr_reg_no%FOUND then
2740:
2741: hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
2742:
2743: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
2744:
2745: update pay_action_information
2750: and action_information_category = 'CAEOY PENSION PLAN INFO'
2751: AND ACTION_INFORMATION4 = old_l_registration_no1;
2752:
2753: else
2754: hr_utility.trace('in reg2 insert pay_action_information');
2755:
2756: -- insert a new record into pay_action_information
2757:
2758: pay_action_information_api.create_action_information(
2800: close c_get_emplr_reg_no;
2801: end if;
2802:
2803: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2804: hr_utility.trace('in reg3 pay_action_information');
2805: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2806: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2807: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2808: l_payroll_action_id
2801: end if;
2802:
2803: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2804: hr_utility.trace('in reg3 pay_action_information');
2805: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2806: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2807: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2808: l_payroll_action_id
2809: , old_l_registration_no2
2802:
2803: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2804: hr_utility.trace('in reg3 pay_action_information');
2805: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2806: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2807: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2808: l_payroll_action_id
2809: , old_l_registration_no2
2810: ,p_effective_date);
2810: ,p_effective_date);
2811: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
2812: if c_get_emplr_reg_no%FOUND then
2813:
2814: hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
2815:
2816: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
2817:
2818: update pay_action_information
2822: and effective_date = p_effective_date
2823: and action_information_category = 'CAEOY PENSION PLAN INFO'
2824: AND ACTION_INFORMATION4 = old_l_registration_no2;
2825:
2826: hr_utility.trace('Updated pay_action_information');
2827:
2828: else
2829: hr_utility.trace('in reg3 insert pay_action_information');
2830:
2825:
2826: hr_utility.trace('Updated pay_action_information');
2827:
2828: else
2829: hr_utility.trace('in reg3 insert pay_action_information');
2830:
2831: -- insert a new record into pay_action_information
2832:
2833: pay_action_information_api.create_action_information(
2914: l_balance_name,l_element_type_id,
2915: l_ele_classification_id;
2916: exit when c_reg_balance_feed_info%NOTFOUND;
2917:
2918: hr_utility.trace('checking for T4A_BOX32 ');
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2915: l_ele_classification_id;
2916: exit when c_reg_balance_feed_info%NOTFOUND;
2917:
2918: hr_utility.trace('checking for T4A_BOX32 ');
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2923: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2916: exit when c_reg_balance_feed_info%NOTFOUND;
2917:
2918: hr_utility.trace('checking for T4A_BOX32 ');
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2923: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2924:
2917:
2918: hr_utility.trace('checking for T4A_BOX32 ');
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2923: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2924:
2925: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2918: hr_utility.trace('checking for T4A_BOX32 ');
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2923: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2924:
2925: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2926: ( l_balance_name,
2919: hr_utility.trace('p_assactid:'||to_char(p_assactid));
2920: hr_utility.trace('l_asgid:'||to_char(l_asgid));
2921: hr_utility.trace('l_registration_no:'||l_registration_no);
2922: hr_utility.trace('l_balance_name:'||l_balance_name);
2923: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
2924:
2925: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
2926: ( l_balance_name,
2927: 'YTD' ,
2933: l_business_group_id,
2934: NULL );
2935:
2936:
2937: hr_utility.trace('l_value:'||to_char(l_value));
2938: if l_value is null then
2939: l_value := 0;
2940: end if;
2941:
2938: if l_value is null then
2939: l_value := 0;
2940: end if;
2941:
2942: hr_utility.trace('before checking the new validation ');
2943: hr_utility.trace('l_value :'||to_char(l_value));
2944: hr_utility.trace('l_registration_no:'||l_registration_no);
2945: hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2939: l_value := 0;
2940: end if;
2941:
2942: hr_utility.trace('before checking the new validation ');
2943: hr_utility.trace('l_value :'||to_char(l_value));
2944: hr_utility.trace('l_registration_no:'||l_registration_no);
2945: hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2947:
2940: end if;
2941:
2942: hr_utility.trace('before checking the new validation ');
2943: hr_utility.trace('l_value :'||to_char(l_value));
2944: hr_utility.trace('l_registration_no:'||l_registration_no);
2945: hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2947:
2948: /* Condition to check the amounts and determine the
2941:
2942: hr_utility.trace('before checking the new validation ');
2943: hr_utility.trace('l_value :'||to_char(l_value));
2944: hr_utility.trace('l_registration_no:'||l_registration_no);
2945: hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2947:
2948: /* Condition to check the amounts and determine the
2949: registration number to archive Bug fix 2408456 */
2942: hr_utility.trace('before checking the new validation ');
2943: hr_utility.trace('l_value :'||to_char(l_value));
2944: hr_utility.trace('l_registration_no:'||l_registration_no);
2945: hr_utility.trace('old_l_value :'||to_char(old_l_value));
2946: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
2947:
2948: /* Condition to check the amounts and determine the
2949: registration number to archive Bug fix 2408456 */
2950:
3000:
3001:
3002: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3003:
3004: hr_utility.trace('Start of Employer Level PP Reg no ');
3005: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
3006: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
3007: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3008: l_payroll_action_id
3001:
3002: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3003:
3004: hr_utility.trace('Start of Employer Level PP Reg no ');
3005: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
3006: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
3007: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3008: l_payroll_action_id
3009: ,old_l_registration_no
3002: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3003:
3004: hr_utility.trace('Start of Employer Level PP Reg no ');
3005: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
3006: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
3007: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3008: l_payroll_action_id
3009: ,old_l_registration_no
3010: ,p_effective_date);
3009: ,old_l_registration_no
3010: ,p_effective_date);
3011: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
3012: if c_get_emplr_reg_no%FOUND then
3013: hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
3014: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
3015:
3016: update pay_action_information
3017: set action_information5 = to_char(ln_emplr_regamt)
3073:
3074:
3075: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3076:
3077: hr_utility.trace('Start of Employer Level PP Reg no ');
3078: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3079: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3080:
3081: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3074:
3075: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3076:
3077: hr_utility.trace('Start of Employer Level PP Reg no ');
3078: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3079: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3080:
3081: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3082: l_payroll_action_id
3075: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3076:
3077: hr_utility.trace('Start of Employer Level PP Reg no ');
3078: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3079: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3080:
3081: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3082: l_payroll_action_id
3083: ,old_l_registration_no1
3083: ,old_l_registration_no1
3084: ,p_effective_date);
3085: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
3086: if c_get_emplr_reg_no%FOUND then
3087: hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
3088: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
3089:
3090: update pay_action_information
3091: set action_information5 = to_char(ln_emplr_regamt1)
3149: if old_l_registration_no2 is not null and old_l_value2 > 0 then
3150:
3151: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3152:
3153: hr_utility.trace('Start of Employer Level PP Reg no ');
3154: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3155: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3156:
3157: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3150:
3151: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3152:
3153: hr_utility.trace('Start of Employer Level PP Reg no ');
3154: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3155: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3156:
3157: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3158: l_payroll_action_id
3151: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3152:
3153: hr_utility.trace('Start of Employer Level PP Reg no ');
3154: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3155: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3156:
3157: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3158: l_payroll_action_id
3159: ,old_l_registration_no2
3158: l_payroll_action_id
3159: ,old_l_registration_no2
3160: ,p_effective_date);
3161: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
3162: hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
3163: if c_get_emplr_reg_no%FOUND then
3164: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
3165:
3166: update pay_action_information
3230: from T4A_BOX34 */
3231: end;
3232: /* end registration number archiving */
3233: else
3234: hr_utility.trace('result is 0');
3235:
3236: end if; /* end if for result <> 0 condition */
3237:
3238: /* Need to add the T4A Nonbox Footnote archiving code to fix bug#2175045 */
3252: if cur_non_box_mesg%notfound then
3253: exit;
3254: end if;
3255:
3256: hr_utility.trace('l_messages - '||l_messages);
3257: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3258:
3259: /* If the same Non Box footnote is processed more than
3260: once during the year, then the sum of the associated
3253: exit;
3254: end if;
3255:
3256: hr_utility.trace('l_messages - '||l_messages);
3257: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
3258:
3259: /* If the same Non Box footnote is processed more than
3260: once during the year, then the sum of the associated
3261: amounts is archived */
3262:
3263: if ((l_messages <> l_prev_messages) and
3264: (l_prev_messages is not null)) then
3265:
3266: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3267:
3268: if l_total_mesg_amt <> 0 then
3269:
3270: pay_action_information_api.create_action_information(
3327: else
3328: l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
3329: end if;
3330:
3331: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3332:
3333: l_prev_messages := l_messages;
3334: ln_prev_tax_unit_id := ln_tax_unit_id;
3335: ld_prev_eff_date := ld_eff_date;
3339: close cur_non_box_mesg;
3340:
3341: if (l_prev_messages is not null) then
3342:
3343: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3344: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3345:
3346: if l_total_mesg_amt <> 0 then
3347:
3340:
3341: if (l_prev_messages is not null) then
3342:
3343: hr_utility.trace('l_prev_messages - '||l_prev_messages);
3344: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
3345:
3346: if l_total_mesg_amt <> 0 then
3347:
3348: pay_action_information_api.create_action_information(
3435: /* End of t4a box 38 archive */
3436:
3437: begin
3438: l_counter := 0;
3439: hr_utility.trace('selecting people');
3440:
3441: select PEOPLE.person_id,
3442: PEOPLE.first_name,
3443: PEOPLE.last_name,
3477: l_work_telephone := null;
3478: l_national_identifier := null;
3479: l_middle_names := null; /* changed variable l_pre_name_adjunct
3480: to l_middle_names */
3481: hr_utility.raise_error;
3482: end;
3483:
3484: begin
3485: select PHONE.phone_number
3496: when no_data_found then
3497: l_employee_phone_no := l_work_telephone;
3498: end;
3499:
3500: hr_utility.trace('selected people');
3501: /* Initialise l_count */
3502: l_count := 0;
3503:
3504: /* hr_utility.trace_on('Y','ORACLE'); */
3500: hr_utility.trace('selected people');
3501: /* Initialise l_count */
3502: l_count := 0;
3503:
3504: /* hr_utility.trace_on('Y','ORACLE'); */
3505:
3506: l_counter := l_counter + 1;
3507: l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
3508: l_user_entity_value_tab(l_counter) := l_person_id;
3556: end loop;
3557: end if;
3558:
3559: begin
3560: hr_utility.trace('selecting address');
3561:
3562: select addr.address_line1,
3563: addr.address_line2,
3564: addr.address_line3,
3595: l_telephone_number := null;
3596: l_country_code := null;
3597: end;
3598:
3599: hr_utility.trace('selected address');
3600:
3601: l_counter := 0;
3602: l_counter := l_counter + 1;
3603: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
3641:
3642: l_context_id := l_taxunit_context_id;
3643: l_context_val := l_tax_unit_id;
3644:
3645: hr_utility.trace('archiving address');
3646: ff_archive_api.create_archive_item(
3647: p_archive_item_id => l_archive_item_id
3648: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
3649: ,p_archive_value => l_user_entity_value_tab(i)
3652: ,p_legislation_code => 'CA'
3653: ,p_object_version_number => l_object_version_number
3654: ,p_some_warning => l_some_warning
3655: );
3656: hr_utility.trace('archived address');
3657: end loop;
3658: end if;
3659: hr_utility.trace('end of eoy_archive_data');
3660: l_step := 37;
3655: );
3656: hr_utility.trace('archived address');
3657: end loop;
3658: end if;
3659: hr_utility.trace('end of eoy_archive_data');
3660: l_step := 37;
3661:
3662: -- Federal YE Amendment Pre-Process Validation (T4A Amendment Archiver code)
3663:
3662: -- Federal YE Amendment Pre-Process Validation (T4A Amendment Archiver code)
3663:
3664: Begin
3665:
3666: hr_utility.trace('Started Federal YE Amendment PP Validation ');
3667:
3668: select effective_date,
3669: report_type
3670: into ld_fapp_effective_date,
3671: lv_fapp_report_type
3672: from pay_payroll_actions
3673: where payroll_action_id = l_payroll_action_id;
3674:
3675: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
3676: to_char(l_payroll_action_id));
3677: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3678:
3679: if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
3673: where payroll_action_id = l_payroll_action_id;
3674:
3675: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
3676: to_char(l_payroll_action_id));
3677: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3678:
3679: if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
3680:
3681: begin
3685: into ln_fapp_locked_action_id;
3686:
3687: close c_get_fapp_locked_action_id;
3688:
3689: hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
3690: hr_utility.trace('ln_fapp_locked_action_id :'||
3691: to_char(ln_fapp_locked_action_id));
3692:
3693: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
3686:
3687: close c_get_fapp_locked_action_id;
3688:
3689: hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
3690: hr_utility.trace('ln_fapp_locked_action_id :'||
3691: to_char(ln_fapp_locked_action_id));
3692:
3693: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
3694: fetch c_get_fapp_lkd_actid_rtype
3695: into lv_fapp_locked_actid_reptype;
3696:
3697: close c_get_fapp_lkd_actid_rtype;
3698:
3699: hr_utility.trace('lv_fapp_locked_actid_reptype :'||
3700: lv_fapp_locked_actid_reptype);
3701:
3702: lv_fapp_flag := compare_archive_data(p_assactid,
3703: ln_fapp_locked_action_id);
3703: ln_fapp_locked_action_id);
3704:
3705: if lv_fapp_flag = 'Y' then
3706:
3707: hr_utility.trace('Archiving T4A Amendment Flag is : ' || lv_fapp_flag);
3708:
3709: ff_archive_api.create_archive_item(
3710: p_archive_item_id => l_archive_item_id
3711: ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_AMENDMENT_FLAG')
3723:
3724: end; -- report_type validation
3725:
3726: end if; -- report type validation for FAPP
3727: hr_utility.trace('End of Federal YE Amendment PP Validation');
3728:
3729: exception when no_data_found then
3730: hr_utility.trace('Report type not found for given Payroll_action ');
3731: null;
3726: end if; -- report type validation for FAPP
3727: hr_utility.trace('End of Federal YE Amendment PP Validation');
3728:
3729: exception when no_data_found then
3730: hr_utility.trace('Report type not found for given Payroll_action ');
3731: null;
3732: end;
3733:
3734: -- End of Federal YE Amendment Pre-Process Validation
3765: l_business_group
3766: from pay_payroll_actions
3767: where payroll_action_id = pactid;
3768:
3769: hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
3770:
3771: l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
3772:
3773: select org_information11
3776: where organization_id = l_eoy_tax_unit_id
3777: and org_information_context = 'Canada Employer Identification'
3778: and org_information5 in ('T4A/RL1','T4A/RL2');
3779:
3780: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
3781: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
3782:
3783: if l_eoy_tax_unit_id <> -99999 then
3784:
3777: and org_information_context = 'Canada Employer Identification'
3778: and org_information5 in ('T4A/RL1','T4A/RL2');
3779:
3780: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
3781: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
3782:
3783: if l_eoy_tax_unit_id <> -99999 then
3784:
3785: sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
3817:
3818: l_archive := chk_gre_archive(pactid);
3819:
3820: if g_archive_flag = 'N' then
3821: hr_utility.trace('eoy_range_cursor archiving employer data');
3822:
3823: -- now the archiver has provision for archiving payroll_action_level data .
3824: -- So make use of that
3825:
3822:
3823: -- now the archiver has provision for archiving payroll_action_level data .
3824: -- So make use of that
3825:
3826: hr_utility.trace('eoy_range_cursor archiving employer data');
3827:
3828: eoy_archive_gre_data(pactid,
3829: l_eoy_tax_unit_id,
3830: l_transmitter_gre_id);