[Home] [Help]
424: open csr_defined_balance;
425: fetch csr_defined_balance into l_defined_balance_id;
426: if csr_defined_balance%notfound then
427: close csr_defined_balance;
428: hr_utility.raise_error;
429: else
430: close csr_defined_balance;
431: end if;
432:
533: /* An invalid report type has been passed so fail. */
534:
535: else
536:
537: hr_utility.raise_error;
538:
539: end if;
540:
541: end get_selection_information;
845:
846: /* Get the report type, report qualifier, business group id and the
847: gre for which the archiving has to be done */
848:
849: hr_utility.trace('getting report type ');
850:
851: select effective_date,
852: report_type,
853: -- Added for bug 10399514
868: where payroll_action_id = pactid;
869:
870: l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
871:
872: hr_utility.trace('getting dates');
873:
874: get_dates(l_report_type,
875: l_effective_date,
876: l_period_end,
878: l_quarter_end,
879: l_year_start,
880: l_year_end);
881:
882: hr_utility.trace('getting selection information');
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
879: l_year_start,
880: l_year_end);
881:
882: hr_utility.trace('getting selection information');
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
880: l_year_end);
881:
882: hr_utility.trace('getting selection information');
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
888:
881:
882: hr_utility.trace('getting selection information');
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
888:
889: get_selection_information
882: hr_utility.trace('getting selection information');
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
888:
889: get_selection_information
890: (l_report_type,
883: hr_utility.trace('report type '|| l_report_type);
884: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
885: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
886: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
887: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
888:
889: get_selection_information
890: (l_report_type,
891: l_quarter_start,
932:
933: -- open c_eoy_qbin;
934:
935: if l_person_on then
936: hr_utility.trace('opening c_eoy_gre_range CURSOR');
937: OPEN c_eoy_gre_range ;
938: else
939: hr_utility.trace('opening c_eoy_gre CURSOR');
940: OPEN c_eoy_gre;
935: if l_person_on then
936: hr_utility.trace('opening c_eoy_gre_range CURSOR');
937: OPEN c_eoy_gre_range ;
938: else
939: hr_utility.trace('opening c_eoy_gre CURSOR');
940: OPEN c_eoy_gre;
941: end if ;
942:
943: -- Code modification for bug 10399514 ends here
943: -- Code modification for bug 10399514 ends here
944:
945: /* Loop for all rows returned for SQL statement. */
946:
947: hr_utility.trace('Entering loop');
948:
949: loop
950:
951: if l_eoy_tax_unit_id <> 99999 then
949: loop
950:
951: if l_eoy_tax_unit_id <> 99999 then
952:
953: hr_utility.trace('Fetching person id');
954:
955: -- Code modification for bug 10399514 starts here
956: if l_person_on then
957: hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
953: hr_utility.trace('Fetching person id');
954:
955: -- Code modification for bug 10399514 starts here
956: if l_person_on then
957: hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
958: fetch c_eoy_gre_range
959: into l_person_id;
960: exit when c_eoy_gre_range%NOTFOUND;
961: else
958: fetch c_eoy_gre_range
959: into l_person_id;
960: exit when c_eoy_gre_range%NOTFOUND;
961: else
962: hr_utility.trace('fetching from c_eoy_gre CURSOR');
963: fetch c_eoy_gre
964: into l_person_id;
965: exit when c_eoy_gre%NOTFOUND;
966: end if ;
984: /* If the new row is the same as the previous row according to the way
985: the rows are grouped then discard the row ie. grouping by GRE
986: requires a single row for each person / GRE combination. */
987:
988: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
989: hr_utility.trace('previous tax unit id is '||
990: to_char(l_prev_tax_unit_id));
991:
992: if ( l_person_id = l_prev_person_id and
985: the rows are grouped then discard the row ie. grouping by GRE
986: requires a single row for each person / GRE combination. */
987:
988: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
989: hr_utility.trace('previous tax unit id is '||
990: to_char(l_prev_tax_unit_id));
991:
992: if ( l_person_id = l_prev_person_id and
993: l_tax_unit_id = l_prev_tax_unit_id) then
991:
992: if ( l_person_id = l_prev_person_id and
993: l_tax_unit_id = l_prev_tax_unit_id) then
994:
995: hr_utility.trace('Not creating Asg_action, duplicate');
996: null;
997:
998: else
999:
996: null;
997:
998: else
999:
1000: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1001: hr_utility.trace('person is '|| to_char(l_person_id));
1002: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1003:
1004:
997:
998: else
999:
1000: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1001: hr_utility.trace('person is '|| to_char(l_person_id));
1002: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1003:
1004:
1005: /* Have a new unique row according to the way the rows are grouped.
998: else
999:
1000: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1001: hr_utility.trace('person is '|| to_char(l_person_id));
1002: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1003:
1004:
1005: /* Have a new unique row according to the way the rows are grouped.
1006: The inclusion of the person is dependent on having a non zero
1012: open c_get_asg_id(l_person_id);
1013: fetch c_get_asg_id into l_primary_asg;
1014: if c_get_asg_id%NOTFOUND then
1015: close c_get_asg_id;
1016: hr_utility.trace('Primary Asg Not found');
1017: hr_utility.raise_error;
1018: else
1019: close c_get_asg_id;
1020: end if;
1013: fetch c_get_asg_id into l_primary_asg;
1014: if c_get_asg_id%NOTFOUND then
1015: close c_get_asg_id;
1016: hr_utility.trace('Primary Asg Not found');
1017: hr_utility.raise_error;
1018: else
1019: close c_get_asg_id;
1020: end if;
1021:
1029: begin
1030: open c_get_latest_asg(l_person_id );
1031: fetch c_get_latest_asg into ln_max_aa_id;
1032: close c_get_latest_asg;
1033: hr_utility.trace('Action creation Max assignment_action_id : ' ||
1034: to_char(ln_max_aa_id));
1035:
1036: exception
1037: when no_data_found then
1040: 'Balance Assignment Action does not exist for : '
1041: ||to_char(l_person_id));
1042: end;
1043:
1044: hr_utility.trace('Setting context');
1045: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1046: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',ln_max_aa_id);
1047:
1048: ln_non_taxable_earnings :=
1049: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1050: ('T4 Non Taxable Earnings',
1051: 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER',
1052: l_tax_unit_id,l_bus_group_id,NULL),0);
1053: hr_utility.trace('T4 Non Taxable Earnings :'||
1054: to_char(ln_non_taxable_earnings));
1055:
1056: ln_gross_earnings :=
1057: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1058: ('Gross Earnings',
1059: 'YTD',ln_max_aa_id,l_primary_asg,NULL,'PER' ,
1060:
1061: l_tax_unit_id, l_bus_group_id, NULL),0);
1062: hr_utility.trace('Gross Earnings :'||
1063: to_char(ln_gross_earnings));
1064:
1065: ln_no_gross_earnings :=
1066: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1066: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
1067: ('T4 No Gross Earnings',
1068: 'YTD',ln_max_aa_id, l_primary_asg,NULL,'PER' ,
1069: l_tax_unit_id, l_bus_group_id, NULL),0);
1070: hr_utility.trace('T4 No Gross Earnings :'||
1071: to_char(ln_no_gross_earnings));
1072:
1073: /* End of adding code for bug#3267520, below If condition
1074: is also part of this bug fix */
1084: from dual;
1085:
1086: /* Insert into pay_assignment_actions. */
1087:
1088: hr_utility.trace('creating assignment action');
1089:
1090: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1091: pactid,chunk,l_tax_unit_id);
1092:
1093: /* Update the serial number column with the person id
1094: so that we can use in the Magnetic Media process
1095: to do an additional check against the assignment table */
1096:
1097: hr_utility.trace('updating assignment action');
1098:
1099: update pay_assignment_actions aa
1100: set aa.serial_number = to_char(l_person_id)
1101: where aa.assignment_action_id = lockingactid;
1099: update pay_assignment_actions aa
1100: set aa.serial_number = to_char(l_person_id)
1101: where aa.assignment_action_id = lockingactid;
1102:
1103: hr_utility.trace('Created Assignment action'||
1104: to_char(lockingactid));
1105:
1106: End if; --Checking Gross Earnings, No Gross Earnings, NonTaxable Earn
1107:
1117: if l_eoy_tax_unit_id <> 99999 then
1118:
1119: -- Code modification for bug 10399514 starts here
1120: if l_person_on then
1121: hr_utility.trace('closing c_eoy_gre_range CURSOR');
1122: close c_eoy_gre_range;
1123: else
1124: hr_utility.trace('closing c_eoy_gre CURSOR');
1125: close c_eoy_gre;
1120: if l_person_on then
1121: hr_utility.trace('closing c_eoy_gre_range CURSOR');
1122: close c_eoy_gre_range;
1123: else
1124: hr_utility.trace('closing c_eoy_gre CURSOR');
1125: close c_eoy_gre;
1126: end if ;
1127: -- Code modification for bug 10399514 ends here
1128: /*
1160: return l_user_entity_id;
1161:
1162: exception
1163: when others then
1164: hr_utility.trace('Error while getting the user_entity_id for '
1165: || p_dbi_name);
1166: hr_utility.raise_error;
1167:
1168: end get_user_entity_id;
1162: exception
1163: when others then
1164: hr_utility.trace('Error while getting the user_entity_id for '
1165: || p_dbi_name);
1166: hr_utility.raise_error;
1167:
1168: end get_user_entity_id;
1169:
1170:
1233: ln_amend_box_count number;
1234:
1235:
1236: begin
1237: -- hr_utility.trace_on('Y','TEST');
1238: /* Initialization Process */
1239: lv_flag := 'N';
1240: if ltr_amend_arch_data.count > 0 then
1241: ltr_amend_arch_data.delete;
1299:
1300:
1301: /* Populate T4 Amendment Box Data for an assignment_action */
1302: open c_get_emp_t4box_data(p_assignment_action_id,p_jurisdiction);
1303: hr_utility.trace('Populating T4 Amendment Box Data ');
1304: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1305: ln_amend_box_count := 0;
1306: loop
1307: fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1300:
1301: /* Populate T4 Amendment Box Data for an assignment_action */
1302: open c_get_emp_t4box_data(p_assignment_action_id,p_jurisdiction);
1303: hr_utility.trace('Populating T4 Amendment Box Data ');
1304: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1305: ln_amend_box_count := 0;
1306: loop
1307: fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1308: exit when c_get_emp_t4box_data%NOTFOUND;
1306: loop
1307: fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1308: exit when c_get_emp_t4box_data%NOTFOUND;
1309:
1310: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1311: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1312: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1313: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1314: ln_amend_box_count := ln_amend_box_count + 1;
1307: fetch c_get_emp_t4box_data into ltr_amend_arch_data(ln_amend_box_count);
1308: exit when c_get_emp_t4box_data%NOTFOUND;
1309:
1310: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1311: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1312: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1313: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1314: ln_amend_box_count := ln_amend_box_count + 1;
1315: end loop;
1308: exit when c_get_emp_t4box_data%NOTFOUND;
1309:
1310: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1311: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1312: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1313: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1314: ln_amend_box_count := ln_amend_box_count + 1;
1315: end loop;
1316:
1309:
1310: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1311: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1312: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1313: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1314: ln_amend_box_count := ln_amend_box_count + 1;
1315: end loop;
1316:
1317: close c_get_emp_t4box_data;
1316:
1317: close c_get_emp_t4box_data;
1318:
1319: /* Populate T4 Amendment Employee Data for an assignment_action */
1320: hr_utility.trace('Populating Amendment Employee Data ');
1321: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1322: for i in 0 .. j
1323: loop
1324: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1317: close c_get_emp_t4box_data;
1318:
1319: /* Populate T4 Amendment Employee Data for an assignment_action */
1320: hr_utility.trace('Populating Amendment Employee Data ');
1321: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1322: for i in 0 .. j
1323: loop
1324: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1325: fetch c_get_employee_data into ltr_amend_emp_data(i);
1323: loop
1324: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1325: fetch c_get_employee_data into ltr_amend_emp_data(i);
1326:
1327: hr_utility.trace('I :'||to_char(i));
1328: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1329: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1330: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1331:
1324: open c_get_employee_data(p_assignment_action_id,ltr_emp_ue_id(i));
1325: fetch c_get_employee_data into ltr_amend_emp_data(i);
1326:
1327: hr_utility.trace('I :'||to_char(i));
1328: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1329: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1330: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1331:
1332: close c_get_employee_data;
1325: fetch c_get_employee_data into ltr_amend_emp_data(i);
1326:
1327: hr_utility.trace('I :'||to_char(i));
1328: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1329: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1330: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1331:
1332: close c_get_employee_data;
1333: end loop;
1326:
1327: hr_utility.trace('I :'||to_char(i));
1328: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_emp_data(i).archive_context1));
1329: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_emp_data(i).archive_ue_id));
1330: hr_utility.trace('Archive_Value: '||ltr_amend_emp_data(i).archive_value);
1331:
1332: close c_get_employee_data;
1333: end loop;
1334:
1334:
1335:
1336: /* Populate T4 YEPP Box Data for an assignment_action */
1337: open c_get_emp_t4box_data(p_locked_action_id,p_jurisdiction);
1338: hr_utility.trace('Populating T4 YEPP Box Data ');
1339: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1340: ln_yepp_box_count := 0;
1341: loop
1342: fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1335:
1336: /* Populate T4 YEPP Box Data for an assignment_action */
1337: open c_get_emp_t4box_data(p_locked_action_id,p_jurisdiction);
1338: hr_utility.trace('Populating T4 YEPP Box Data ');
1339: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1340: ln_yepp_box_count := 0;
1341: loop
1342: fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1343: exit when c_get_emp_t4box_data%NOTFOUND;
1341: loop
1342: fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1343: exit when c_get_emp_t4box_data%NOTFOUND;
1344:
1345: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1346: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1347: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1348: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1349: ln_yepp_box_count := ln_yepp_box_count + 1;
1342: fetch c_get_emp_t4box_data into ltr_yepp_arch_data(ln_yepp_box_count);
1343: exit when c_get_emp_t4box_data%NOTFOUND;
1344:
1345: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1346: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1347: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1348: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1349: ln_yepp_box_count := ln_yepp_box_count + 1;
1350: end loop;
1343: exit when c_get_emp_t4box_data%NOTFOUND;
1344:
1345: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1346: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1347: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1348: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1349: ln_yepp_box_count := ln_yepp_box_count + 1;
1350: end loop;
1351:
1344:
1345: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1346: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1347: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1348: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1349: ln_yepp_box_count := ln_yepp_box_count + 1;
1350: end loop;
1351:
1352: close c_get_emp_t4box_data;
1351:
1352: close c_get_emp_t4box_data;
1353:
1354: /* Populate T4 YEPP Employee Data for an assignment_action */
1355: hr_utility.trace('Populating YEPP Employee Data ');
1356: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
1357: for i in 0 .. j
1358: loop
1359: open c_get_employee_data(P_locked_action_id,ltr_emp_ue_id(i));
1352: close c_get_emp_t4box_data;
1353:
1354: /* Populate T4 YEPP Employee Data for an assignment_action */
1355: hr_utility.trace('Populating YEPP Employee Data ');
1356: hr_utility.trace('P_locked_action_id :'||to_char(P_locked_action_id));
1357: for i in 0 .. j
1358: loop
1359: open c_get_employee_data(P_locked_action_id,ltr_emp_ue_id(i));
1360: fetch c_get_employee_data into ltr_yepp_emp_data(i);
1359: open c_get_employee_data(P_locked_action_id,ltr_emp_ue_id(i));
1360: fetch c_get_employee_data into ltr_yepp_emp_data(i);
1361: exit when c_get_employee_data%NOTFOUND;
1362:
1363: hr_utility.trace('I :'||to_char(i));
1364: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1365: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1366: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1367:
1360: fetch c_get_employee_data into ltr_yepp_emp_data(i);
1361: exit when c_get_employee_data%NOTFOUND;
1362:
1363: hr_utility.trace('I :'||to_char(i));
1364: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1365: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1366: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1367:
1368: close c_get_employee_data;
1361: exit when c_get_employee_data%NOTFOUND;
1362:
1363: hr_utility.trace('I :'||to_char(i));
1364: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1365: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1366: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1367:
1368: close c_get_employee_data;
1369: end loop;
1362:
1363: hr_utility.trace('I :'||to_char(i));
1364: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_emp_data(i).archive_context1));
1365: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_emp_data(i).archive_ue_id));
1366: hr_utility.trace('Archive_Value: '||ltr_yepp_emp_data(i).archive_value);
1367:
1368: close c_get_employee_data;
1369: end loop;
1370:
1370:
1371: /* Compare T4 Amendment Box Data and T4 YEPP Box Data for an
1372: assignment_action */
1373:
1374: hr_utility.trace('Comparing T4 Amend and T4 YEPP Box Data ');
1375:
1376: if ln_yepp_box_count <> ln_amend_box_count then
1377: lv_flag := 'Y';
1378: elsif ln_yepp_box_count = ln_amend_box_count then
1388: (ltr_yepp_arch_data(i).archive_value is not null and
1389: ltr_amend_arch_data(i).archive_value is null)) then
1390:
1391: lv_flag := 'Y';
1392: hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1393: exit;
1394: end if;
1395: end if;
1396: end loop;
1399: /* Compare T4 Employee Data and T4 YEPP Employee Data for an
1400: assignment_action */
1401: If lv_flag <> 'Y' then
1402:
1403: hr_utility.trace('Comparing T4 Amend and T4 YEPP Employee Data ');
1404: for i in ltr_yepp_emp_data.first..ltr_yepp_emp_data.last
1405: loop
1406: if (ltr_yepp_emp_data(i).archive_ue_id =
1407: ltr_amend_emp_data(i).archive_ue_id) then
1413: (ltr_yepp_emp_data(i).archive_value is not null and
1414: ltr_amend_emp_data(i).archive_value is null)) then
1415:
1416: lv_flag := 'Y';
1417: hr_utility.trace('Archive_UE_id with different value :'||
1418: to_char(ltr_yepp_arch_data(i).archive_ue_id));
1419: exit;
1420: end if;
1421: end if;
1427: flag to 'N' */
1428:
1429: if lv_flag <> 'Y' then
1430: lv_flag := 'N';
1431: hr_utility.trace('No value difference for an Employee Asg Action: '||
1432: to_char(p_assignment_action_id));
1433: end if;
1434:
1435: hr_utility.trace('lv_flag :'||lv_flag);
1431: hr_utility.trace('No value difference for an Employee Asg Action: '||
1432: to_char(p_assignment_action_id));
1433: end if;
1434:
1435: hr_utility.trace('lv_flag :'||lv_flag);
1436: return lv_flag;
1437: -- hr_utility.trace_off;
1438: end compare_archive_data;
1439:
1433: end if;
1434:
1435: hr_utility.trace('lv_flag :'||lv_flag);
1436: return lv_flag;
1437: -- hr_utility.trace_off;
1438: end compare_archive_data;
1439:
1440:
1441:
1622: l_arch_gre_step := 30;
1623:
1624: /* Archive the Employer level data */
1625:
1626: --hr_utility.trace_on('Y','CAEOY');
1627: hr_utility.trace('getting employer data ');
1628:
1629: open cur_bg(p_tax_unit_id);
1630: fetch
1623:
1624: /* Archive the Employer level data */
1625:
1626: --hr_utility.trace_on('Y','CAEOY');
1627: hr_utility.trace('getting employer data ');
1628:
1629: open cur_bg(p_tax_unit_id);
1630: fetch
1631: cur_bg
1648: l_ei_rate;
1649:
1650: if employer_info%NOTFOUND then
1651:
1652: hr_utility.trace('cannot find employer data ');
1653: employer_info_found := 'N';
1654:
1655: l_employer_ein := null;
1656: l_address_line_1 := null;
1666:
1667: else
1668:
1669: close employer_info;
1670: hr_utility.trace('Employer data found !!!! ');
1671: employer_info_found := 'Y';
1672:
1673: end if;
1674:
1684: l_federal_youth_hire_ind*/
1685:
1686: if cur_employer_info%NOTFOUND then
1687:
1688: hr_utility.trace('cannot find employer data 2 ');
1689: employer_info_found := 'N';
1690:
1691: l_proprietor_sin_1 := null;
1692: l_proprietor_sin_2 := null;
1702:
1703: else
1704:
1705: close cur_employer_info;
1706: hr_utility.trace('Employer data found 2 !!!! ');
1707: employer_info_found := 'Y';
1708:
1709: end if;
1710:
1740:
1741: if cur_transmitter_info%NOTFOUND then
1742:
1743: close cur_transmitter_info;
1744: hr_utility.trace('Transmitter information not found');
1745:
1746: l_transmitter_name := null;
1747: l_transmitter_addr_line_1 := null;
1748: l_transmitter_addr_line_2 := null;
1761: l_technical_contact_language := null;
1762:
1763: employer_info_found := 'N';
1764:
1765: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1766: hr_utility.set_message_token('ORGIND','GRE');
1767: /* push message into pay_message_lines */
1768: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1769: pay_core_utils.push_token('ORGIND','GRE');
1762:
1763: employer_info_found := 'N';
1764:
1765: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1766: hr_utility.set_message_token('ORGIND','GRE');
1767: /* push message into pay_message_lines */
1768: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1769: pay_core_utils.push_token('ORGIND','GRE');
1770: hr_utility.raise_error;
1766: hr_utility.set_message_token('ORGIND','GRE');
1767: /* push message into pay_message_lines */
1768: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1769: pay_core_utils.push_token('ORGIND','GRE');
1770: hr_utility.raise_error;
1771:
1772: else
1773:
1774: close cur_transmitter_info;
1978:
1979: for i in 1..l_counter loop
1980:
1981: l_arch_gre_step := 42;
1982: hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1983: ff_archive_api.create_archive_item(
1984: p_archive_item_id => l_archive_item_id
1985: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1986: ,p_archive_value => l_user_entity_value_tab(i)
1989: ,p_legislation_code => 'CA'
1990: ,p_object_version_number => l_object_version_number
1991: ,p_some_warning => l_some_warning
1992: );
1993: hr_utility.trace('Ended calling archive API');
1994: l_arch_gre_step := 47;
1995:
1996: end loop;
1997:
1998: g_archive_flag := 'Y';
1999: exception
2000: when others then
2001: g_archive_flag := 'N';
2002: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2003: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2004: if l_arch_gre_step = 30 and l_transmitter_name is null then
2005: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2006: hr_utility.set_message_token('ORGIND','GRE');
2001: g_archive_flag := 'N';
2002: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2003: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2004: if l_arch_gre_step = 30 and l_transmitter_name is null then
2005: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2006: hr_utility.set_message_token('ORGIND','GRE');
2007: end if;
2008:
2009: hr_utility.raise_error;
2002: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
2003: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2004: if l_arch_gre_step = 30 and l_transmitter_name is null then
2005: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2006: hr_utility.set_message_token('ORGIND','GRE');
2007: end if;
2008:
2009: hr_utility.raise_error;
2010:
2005: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
2006: hr_utility.set_message_token('ORGIND','GRE');
2007: end if;
2008:
2009: hr_utility.raise_error;
2010:
2011: end eoy_archive_gre_data;
2012:
2013: /* Name : chk_gre_archive
2029: where fai.context1 = p_payroll_action_id
2030: and archive_type = 'PA');
2031: begin
2032:
2033: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2034:
2035: if g_archive_flag = 'Y' then
2036: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2037: return (TRUE);
2032:
2033: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2034:
2035: if g_archive_flag = 'Y' then
2036: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2037: return (TRUE);
2038: else
2039:
2040: hr_utility.trace('chk_gre_archive - opening cursor');
2036: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2037: return (TRUE);
2038: else
2039:
2040: hr_utility.trace('chk_gre_archive - opening cursor');
2041:
2042: open c_chk_payroll_action;
2043: fetch c_chk_payroll_action into l_flag;
2044: if c_chk_payroll_action%FOUND then
2041:
2042: open c_chk_payroll_action;
2043: fetch c_chk_payroll_action into l_flag;
2044: if c_chk_payroll_action%FOUND then
2045: hr_utility.trace('chk_gre_archive - found in cursor');
2046: g_archive_flag := 'Y';
2047: else
2048: hr_utility.trace('chk_gre_archive - not found in cursor');
2049: g_archive_flag := 'N';
2044: if c_chk_payroll_action%FOUND then
2045: hr_utility.trace('chk_gre_archive - found in cursor');
2046: g_archive_flag := 'Y';
2047: else
2048: hr_utility.trace('chk_gre_archive - not found in cursor');
2049: g_archive_flag := 'N';
2050: end if;
2051:
2052: hr_utility.trace('chk_gre_archive - closing cursor');
2048: hr_utility.trace('chk_gre_archive - not found in cursor');
2049: g_archive_flag := 'N';
2050: end if;
2051:
2052: hr_utility.trace('chk_gre_archive - closing cursor');
2053: close c_chk_payroll_action;
2054: if g_archive_flag = 'Y' then
2055: hr_utility.trace('chk_gre_archive - returning true');
2056: return (TRUE);
2051:
2052: hr_utility.trace('chk_gre_archive - closing cursor');
2053: close c_chk_payroll_action;
2054: if g_archive_flag = 'Y' then
2055: hr_utility.trace('chk_gre_archive - returning true');
2056: return (TRUE);
2057: else
2058: hr_utility.trace('chk_gre_archive - returning false');
2059: return(FALSE);
2054: if g_archive_flag = 'Y' then
2055: hr_utility.trace('chk_gre_archive - returning true');
2056: return (TRUE);
2057: else
2058: hr_utility.trace('chk_gre_archive - returning false');
2059: return(FALSE);
2060: end if;
2061: end if;
2062: end chk_gre_archive;
2088: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
2089: end if;
2090: close c_get_min_chunk;
2091:
2092: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
2093: l_step := 2;
2094: l_archive := chk_gre_archive(p_payroll_action_id);
2095:
2096: l_step := 3;
2093: l_step := 2;
2094: l_archive := chk_gre_archive(p_payroll_action_id);
2095:
2096: l_step := 3;
2097: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
2098: exception
2099: when others then
2100: raise_application_error(-20001,'eoy_archinit at '
2101: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2560: and cp_eff_date between start_date and end_date;
2561:
2562: begin
2563:
2564: -- hr_utility.trace_on(1,'ORACLE');
2565:
2566: l_negative_balance_exists := 'N';
2567: box_52_exists := 'N';
2568: -- box_20_exists added for bug 7611439
2567: box_52_exists := 'N';
2568: -- box_20_exists added for bug 7611439
2569: box_20_exists := 'N';
2570:
2571: hr_utility.trace('box_52_exists 1 '||box_52_exists);
2572: hr_utility.trace('box_20_exists 1 '||box_20_exists);
2573: hr_utility.trace('p_effective_date '||to_char(p_effective_date));
2574:
2575: lv_under18_flag := 'N';
2568: -- box_20_exists added for bug 7611439
2569: box_20_exists := 'N';
2570:
2571: hr_utility.trace('box_52_exists 1 '||box_52_exists);
2572: hr_utility.trace('box_20_exists 1 '||box_20_exists);
2573: hr_utility.trace('p_effective_date '||to_char(p_effective_date));
2574:
2575: lv_under18_flag := 'N';
2576: lv_over70_flag := 'N';
2569: box_20_exists := 'N';
2570:
2571: hr_utility.trace('box_52_exists 1 '||box_52_exists);
2572: hr_utility.trace('box_20_exists 1 '||box_20_exists);
2573: hr_utility.trace('p_effective_date '||to_char(p_effective_date));
2574:
2575: lv_under18_flag := 'N';
2576: lv_over70_flag := 'N';
2577: ln_cpp_ee_taxable_pjgy := 0;
2582: lv_ppip_archive_exempt_flag := Null;
2583: lv_qpp_exempt_flag := Null;
2584: lv_fapp_flag := 'N';
2585: l_count := 0;
2586: hr_utility.trace('p_assactida value '||p_assactid);
2587: hr_utility.trace('getting assignment');
2588:
2589:
2590: SELECT
2583: lv_qpp_exempt_flag := Null;
2584: lv_fapp_flag := 'N';
2585: l_count := 0;
2586: hr_utility.trace('p_assactida value '||p_assactid);
2587: hr_utility.trace('getting assignment');
2588:
2589:
2590: SELECT
2591: aa.assignment_id,
2633: where organization_id = l_tax_unit_id
2634: and org_information_context = 'Canada Employer Identification';
2635:
2636: l_step := 3;
2637: hr_utility.trace('eoy_archive_data archiving employer data');
2638: eoy_archive_gre_data(l_payroll_action_id,
2639: l_tax_unit_id,
2640: l_transmitter_gre_id);
2641:
2639: l_tax_unit_id,
2640: l_transmitter_gre_id);
2641:
2642: l_step := 4;
2643: hr_utility.trace('eoy_archive_data archived employer data');
2644: end if;
2645:
2646: /* Now, set up the jurisdiction context for the db items that
2647: need the jurisdiction as a context.Here we are archiving all the
2683:
2684: FETCH c_get_test_province
2685: INTO l_jurisdiction;
2686:
2687: hr_utility.trace('In jurisdiction loop ' || l_jurisdiction);
2688: EXIT WHEN c_get_test_province%NOTFOUND;
2689:
2690: /*
2691: SELECT
2721: open c_get_latest_asg(lv_serial_number,l_jurisdiction);
2722: fetch c_get_latest_asg into l_aaid,lv_actual_date;
2723: close c_get_latest_asg;
2724:
2725: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2726:
2727: exception
2728: when no_data_found then
2729: l_aaid := -9999;
2733:
2734: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2735: pay_ca_balance_view_pkg.set_context( 'JURISDICTION_CODE', l_jurisdiction);
2736:
2737: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2738:
2739: /* Assign values to the PL/SQL tables */
2740:
2741: l_step := 16;
2883: l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT43';
2884:
2885: /* Modification for bug 9135405 starts here. */
2886:
2887: hr_utility.trace('l_year_end is '|| to_char(l_year_end,'yyyy'));
2888:
2889: IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
2890: hr_utility.trace('inside if condition l_year_end '|| to_char(l_year_end,'yyyy'));
2891:
2886:
2887: hr_utility.trace('l_year_end is '|| to_char(l_year_end,'yyyy'));
2888:
2889: IF ( to_number(to_char(l_year_end,'YYYY')) >= 2010) then
2890: hr_utility.trace('inside if condition l_year_end '|| to_char(l_year_end,'yyyy'));
2891:
2892: l_count := l_count + 1;
2893: l_user_entity_name_tab(l_count) := 'CAEOY_T4_OTHER_INFO_AMOUNT66_PER_JD_GRE_YTD';
2894: l_balance_type_tab(l_count) := 'T4_OTHER_INFO_AMOUNT66';
3029: ('Taxable Benefit without Remuneration',
3030: 'YTD' , l_aaid, l_asgid , NULL, 'PER' ,
3031: l_tax_unit_id, l_business_group_id, l_jurisdiction),0);
3032:
3033: hr_utility.trace('13087530 ln_taxben_without_remuneration '||ln_taxben_without_remuneration);
3034: END IF;
3035:
3036: /* Modification for bug 13087530 ends here. */
3037:
3038: if (((ln_gross_earnings <> 0) and
3039: (ln_non_taxable_earnings <> ln_gross_earnings)) or
3040: (ln_no_gross_earnings <> 0)) then
3041:
3042: hr_utility.trace('Jurisdiction is ** ' || l_jurisdiction);
3043: earning_exists := 1;
3044: ff_archive_api.create_archive_item(
3045: p_archive_item_id => l_archive_item_id
3046: ,p_user_entity_id => get_user_entity_id('CAEOY_PROVINCE_OF_EMPLOYMENT')
3096: l_ei_ee_withheld_pjgy := result;
3097: elsif
3098: l_user_entity_name_tab(i) = 'CAEOY_T4_BOX52_PER_JD_GRE_YTD' then
3099: result := round(result);
3100: hr_utility.trace('box_52 Result = ' || to_char(result));
3101: if result > 0 then
3102: box_52_exists := 'Y';
3103: hr_utility.trace('box_52_exists');
3104: hr_utility.trace('box_52_exists 2 '||box_52_exists);
3099: result := round(result);
3100: hr_utility.trace('box_52 Result = ' || to_char(result));
3101: if result > 0 then
3102: box_52_exists := 'Y';
3103: hr_utility.trace('box_52_exists');
3104: hr_utility.trace('box_52_exists 2 '||box_52_exists);
3105: end if;
3106: -- Modification for bug 7611439 starts here.
3107: elsif
3100: hr_utility.trace('box_52 Result = ' || to_char(result));
3101: if result > 0 then
3102: box_52_exists := 'Y';
3103: hr_utility.trace('box_52_exists');
3104: hr_utility.trace('box_52_exists 2 '||box_52_exists);
3105: end if;
3106: -- Modification for bug 7611439 starts here.
3107: elsif
3108: l_user_entity_name_tab(i) = 'CAEOY_T4_BOX20_PER_JD_GRE_YTD' then
3105: end if;
3106: -- Modification for bug 7611439 starts here.
3107: elsif
3108: l_user_entity_name_tab(i) = 'CAEOY_T4_BOX20_PER_JD_GRE_YTD' then
3109: hr_utility.trace('box_20 Result = ' || to_char(result));
3110: if result > 0 then
3111: box_20_exists := 'Y';
3112: hr_utility.trace('box_20_exists');
3113: hr_utility.trace('box_20_exists 2 '||box_20_exists);
3108: l_user_entity_name_tab(i) = 'CAEOY_T4_BOX20_PER_JD_GRE_YTD' then
3109: hr_utility.trace('box_20 Result = ' || to_char(result));
3110: if result > 0 then
3111: box_20_exists := 'Y';
3112: hr_utility.trace('box_20_exists');
3113: hr_utility.trace('box_20_exists 2 '||box_20_exists);
3114:
3115: end if;
3116: -- Modification for bug 7611439 ends here.
3109: hr_utility.trace('box_20 Result = ' || to_char(result));
3110: if result > 0 then
3111: box_20_exists := 'Y';
3112: hr_utility.trace('box_20_exists');
3113: hr_utility.trace('box_20_exists 2 '||box_20_exists);
3114:
3115: end if;
3116: -- Modification for bug 7611439 ends here.
3117: /* bug#3422384 Box26, Box24 */
3135:
3136: if l_jurisdiction = 'QC' and
3137: l_balance_type_tab(i) = 'Gross Earnings' then
3138:
3139: hr_utility.trace('Calculating QC only taxable benefit');
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3136: if l_jurisdiction = 'QC' and
3137: l_balance_type_tab(i) = 'Gross Earnings' then
3138:
3139: hr_utility.trace('Calculating QC only taxable benefit');
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144: hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3137: l_balance_type_tab(i) = 'Gross Earnings' then
3138:
3139: hr_utility.trace('Calculating QC only taxable benefit');
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144: hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3145:
3138:
3139: hr_utility.trace('Calculating QC only taxable benefit');
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144: hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3145:
3146: qc_result := pay_ca_balance_pkg.call_ca_balance_get_value
3139: hr_utility.trace('Calculating QC only taxable benefit');
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144: hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3145:
3146: qc_result := pay_ca_balance_pkg.call_ca_balance_get_value
3147: ( 'Taxable Benefits for Quebec',
3140: hr_utility.trace('l_aaid ' || to_char(l_aaid));
3141: hr_utility.trace('l_asgid ' || to_char(l_asgid));
3142: hr_utility.trace('l_tax_unit_id ' || to_char(l_tax_unit_id));
3143: hr_utility.trace('l_business_group_id '||to_char(l_business_group_id));
3144: hr_utility.trace('l_jurisdiction ' || l_jurisdiction);
3145:
3146: qc_result := pay_ca_balance_pkg.call_ca_balance_get_value
3147: ( 'Taxable Benefits for Quebec',
3148: 'YTD' ,
3154: l_business_group_id,
3155: l_jurisdiction
3156: ) ;
3157:
3158: hr_utility.trace('qc_result is' || to_char(qc_result));
3159:
3160: if qc_result is null then
3161: qc_result := 0;
3162: end if;
3394: + NVL(other_info_amount87,0))
3395: + lv_code87_excess_amt;
3396: -- code ends here for bug 10244185
3397:
3398: hr_utility.trace(' Gross Earnings = ' || to_char(result));
3399:
3400: /* Added for Bug 4028693 */
3401: open c_get_employment_code(to_char(l_tax_unit_id),
3402: to_number(lv_serial_number));
3445:
3446: if l_user_entity_name_tab(i) = 'CAEOY_CPP_EE_TAXABLE_PER_JD_GRE_YTD'
3447: and l_jurisdiction <> 'QC'
3448: then
3449: hr_utility.trace('13087530 inside Non QC IF condition ');
3450:
3451: if ln_taxben_without_remuneration > 0 then
3452:
3453: hr_utility.trace('13087530 result before '||result);
3449: hr_utility.trace('13087530 inside Non QC IF condition ');
3450:
3451: if ln_taxben_without_remuneration > 0 then
3452:
3453: hr_utility.trace('13087530 result before '||result);
3454: result := result + ln_taxben_without_remuneration;
3455: hr_utility.trace('13087530 result after '||result);
3456:
3457: end if;
3451: if ln_taxben_without_remuneration > 0 then
3452:
3453: hr_utility.trace('13087530 result before '||result);
3454: result := result + ln_taxben_without_remuneration;
3455: hr_utility.trace('13087530 result after '||result);
3456:
3457: end if;
3458:
3459: end if;
3460:
3461: if l_user_entity_name_tab(i) = 'CAEOY_QPP_EE_TAXABLE_PER_JD_GRE_YTD'
3462: and l_jurisdiction = 'QC'
3463: then
3464: hr_utility.trace('13087530 inside QC IF condition ');
3465:
3466: if ln_taxben_without_remuneration > 0 then
3467:
3468: hr_utility.trace('13087530 result before '||result);
3464: hr_utility.trace('13087530 inside QC IF condition ');
3465:
3466: if ln_taxben_without_remuneration > 0 then
3467:
3468: hr_utility.trace('13087530 result before '||result);
3469: result := result + ln_taxben_without_remuneration;
3470: hr_utility.trace('13087530 result after '||result);
3471:
3472: end if;
3466: if ln_taxben_without_remuneration > 0 then
3467:
3468: hr_utility.trace('13087530 result before '||result);
3469: result := result + ln_taxben_without_remuneration;
3470: hr_utility.trace('13087530 result after '||result);
3471:
3472: end if;
3473: end if;
3474:
3482: result := 0;
3483:
3484: -- box_20_exists added for bug 7611439
3485: box_20_exists := 'N';
3486: hr_utility.trace('box_20_exists 3 '||box_20_exists);
3487: end if;
3488:
3489: ff_archive_api.create_archive_item(
3490: p_archive_item_id => l_archive_item_id
3511: end if;
3512:
3513: end loop;
3514:
3515: hr_utility.trace(' Archiver Asg Act Id = ' || to_char(p_assactid));
3516: hr_utility.trace(' Negative Balance Exists Flag = ' || l_negative_balance_exists);
3517:
3518: /* Archiving the Negative Balance Exists Flag Bug#3289072 */
3519: if l_negative_balance_exists = 'Y' then
3512:
3513: end loop;
3514:
3515: hr_utility.trace(' Archiver Asg Act Id = ' || to_char(p_assactid));
3516: hr_utility.trace(' Negative Balance Exists Flag = ' || l_negative_balance_exists);
3517:
3518: /* Archiving the Negative Balance Exists Flag Bug#3289072 */
3519: if l_negative_balance_exists = 'Y' then
3520: l_user_entity_id :=
3596:
3597: l_negative_balance_exists := 'N';
3598:
3599: /* Archiving Exempt flags separately */
3600: hr_utility.trace('I am archiving CPP-QPP exempt flags for ' ||
3601: to_char(l_asgid));
3602: hr_utility.trace('effective date is ' || to_char(p_effective_date));
3603:
3604: /* Bug#3422384, checking whether the employee age is under 18 or over 70 */
3598:
3599: /* Archiving Exempt flags separately */
3600: hr_utility.trace('I am archiving CPP-QPP exempt flags for ' ||
3601: to_char(l_asgid));
3602: hr_utility.trace('effective date is ' || to_char(p_effective_date));
3603:
3604: /* Bug#3422384, checking whether the employee age is under 18 or over 70 */
3605: open c_get_date_of_birth(to_number(lv_serial_number),p_effective_date);
3606: fetch c_get_date_of_birth into ld_date_of_birth;
3606: fetch c_get_date_of_birth into ld_date_of_birth;
3607: close c_get_date_of_birth;
3608:
3609: if ld_date_of_birth is NULL then
3610: hr_utility.trace('Employee date of birth is NULL satisfied ');
3611: lv_over70_flag := 'N';
3612: lv_under18_flag := 'N';
3613: else
3614: hr_utility.trace('Employee date of birth found ');
3610: hr_utility.trace('Employee date of birth is NULL satisfied ');
3611: lv_over70_flag := 'N';
3612: lv_under18_flag := 'N';
3613: else
3614: hr_utility.trace('Employee date of birth found ');
3615:
3616: /* Backing out the changes made in 115.60 */
3617: /* if (( add_months(trunc(ld_date_of_birth,'MONTH'),(18*12)+1)) <= lv_actual_date ) then
3618: if ( lv_actual_date >= ( add_months(trunc(ld_date_of_birth,'MONTH'),(70*12)+1))) then */
3630:
3631: -- Bug 13501503
3632: select distinct(person_id) into l_each_person_id
3633: from per_all_assignments_f where assignment_id = l_asgid;
3634: hr_utility.trace('l_each_person_id ' || l_each_person_id);
3635:
3636: open c_diff_assignments(l_each_person_id);
3637: fetch c_diff_assignments into l_each_asgid;
3638:
3636: open c_diff_assignments(l_each_person_id);
3637: fetch c_diff_assignments into l_each_asgid;
3638:
3639: loop
3640: hr_utility.trace('start of c_diff_assignments CURSOR');
3641:
3642: if c_diff_assignments%rowcount = 0 then
3643: hr_utility.trace('No rows fetched from c_diff_assignments');
3644: end if;
3639: loop
3640: hr_utility.trace('start of c_diff_assignments CURSOR');
3641:
3642: if c_diff_assignments%rowcount = 0 then
3643: hr_utility.trace('No rows fetched from c_diff_assignments');
3644: end if;
3645:
3646: exit when c_diff_assignments%NOTFOUND;
3647: BEGIN
3644: end if;
3645:
3646: exit when c_diff_assignments%NOTFOUND;
3647: BEGIN
3648: hr_utility.trace('l_each_asgid ' || l_each_asgid);
3649: BEGIN
3650:
3651: /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
3652: SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
3768: ,p_context2 => l_tax_unit_id
3769: ,p_some_warning => l_some_warning
3770: );
3771:
3772: hr_utility.trace('I am archiving EI exempt flag');
3773:
3774: /* Added extra validation to fix bug#3422384. For EI
3775: 1. If ei_exempt_flag in tax_information form is 'Y' and
3776: and Box18,Box24 = 0 then ei_exempt_flag= 'X'
3782: lv_ei_archive_exempt_flag := 'X';
3783:
3784: END IF;
3785:
3786: hr_utility.trace('assignment id ' || to_char(l_each_asgid) || '**');
3787: hr_utility.trace('cpp exempt flag is ' || lv_cpp_archive_exempt_flag || '**');
3788: hr_utility.trace('ei exempt flag is ' || lv_ei_archive_exempt_flag || '**');
3789:
3790: ff_archive_api.create_archive_item(
3783:
3784: END IF;
3785:
3786: hr_utility.trace('assignment id ' || to_char(l_each_asgid) || '**');
3787: hr_utility.trace('cpp exempt flag is ' || lv_cpp_archive_exempt_flag || '**');
3788: hr_utility.trace('ei exempt flag is ' || lv_ei_archive_exempt_flag || '**');
3789:
3790: ff_archive_api.create_archive_item(
3791: p_archive_item_id => l_archive_item_id
3784: END IF;
3785:
3786: hr_utility.trace('assignment id ' || to_char(l_each_asgid) || '**');
3787: hr_utility.trace('cpp exempt flag is ' || lv_cpp_archive_exempt_flag || '**');
3788: hr_utility.trace('ei exempt flag is ' || lv_ei_archive_exempt_flag || '**');
3789:
3790: ff_archive_api.create_archive_item(
3791: p_archive_item_id => l_archive_item_id
3792: ,p_user_entity_id => get_user_entity_id('CAEOY_EI_EXEMPT')
3803: );
3804:
3805: exception
3806: when others then
3807: hr_utility.trace('Exception raised');
3808: null;
3809: end;
3810: hr_utility.trace('I have archived exempt flags for assignment id '||l_each_asgid);
3811: hr_utility.trace('Reached end');
3806: when others then
3807: hr_utility.trace('Exception raised');
3808: null;
3809: end;
3810: hr_utility.trace('I have archived exempt flags for assignment id '||l_each_asgid);
3811: hr_utility.trace('Reached end');
3812: fetch c_diff_assignments into l_each_asgid;
3813: end loop;
3814: close c_diff_assignments;
3807: hr_utility.trace('Exception raised');
3808: null;
3809: end;
3810: hr_utility.trace('I have archived exempt flags for assignment id '||l_each_asgid);
3811: hr_utility.trace('Reached end');
3812: fetch c_diff_assignments into l_each_asgid;
3813: end loop;
3814: close c_diff_assignments;
3815:
3839: ,p_context2 => l_tax_unit_id
3840: ,p_some_warning => l_some_warning
3841: );
3842:
3843: hr_utility.trace('Archived Employment code single prov');
3844:
3845: /* to avoid archiving of Box29 value for other prov
3846: if employment code specified for particular
3847: province */
3861: ,p_context2 => l_tax_unit_id
3862: ,p_some_warning => l_some_warning
3863: );
3864:
3865: hr_utility.trace('Archived employment code all prov');
3866:
3867: end if;
3868:
3869: end loop; -- c_get_employment_code
3881: old_l_registration_no := NULL;
3882: old_l_value := 0;
3883: l_value := 0;
3884:
3885: --hr_utility.trace_on('Y','ORACLE');
3886:
3887: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888: hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889: hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3883: l_value := 0;
3884:
3885: --hr_utility.trace_on('Y','ORACLE');
3886:
3887: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888: hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889: hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3890: hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3891:
3884:
3885: --hr_utility.trace_on('Y','ORACLE');
3886:
3887: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888: hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889: hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3890: hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3891:
3892: begin
3885: --hr_utility.trace_on('Y','ORACLE');
3886:
3887: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888: hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889: hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3890: hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3891:
3892: begin
3893:
3886:
3887: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3888: hr_utility.trace('l_asgid is ' || to_char(l_asgid));
3889: hr_utility.trace('l_tax_unit_id is ' || to_char(l_tax_unit_id));
3890: hr_utility.trace('l_business group_id is ' || to_char(l_business_group_id));
3891:
3892: begin
3893:
3894:
3900:
3901: -- l_balance_name1 := 'T4_BOX52'; -- commented by sneelapa for bug 7611439
3902:
3903:
3904: hr_utility.trace('BOX name is ' || l_balance_name1);
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3901: -- l_balance_name1 := 'T4_BOX52'; -- commented by sneelapa for bug 7611439
3902:
3903:
3904: hr_utility.trace('BOX name is ' || l_balance_name1);
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3902:
3903:
3904: hr_utility.trace('BOX name is ' || l_balance_name1);
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3903:
3904: hr_utility.trace('BOX name is ' || l_balance_name1);
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911:
3904: hr_utility.trace('BOX name is ' || l_balance_name1);
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911:
3912: -- adding this logic to resolving bug 13797428
3905: hr_utility.trace('l_asgid ' || l_asgid);
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911:
3912: -- adding this logic to resolving bug 13797428
3913: select distinct(person_id) into l_each_person_id
3906: hr_utility.trace('l_business_group_id ' || l_business_group_id);
3907: hr_utility.trace('p_effective_date ' || to_char(p_effective_date));
3908: hr_utility.trace('box_52_exists ' || box_52_exists);
3909: hr_utility.trace('box_20_exists ' || box_20_exists);
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911:
3912: -- adding this logic to resolving bug 13797428
3913: select distinct(person_id) into l_each_person_id
3914: from per_all_assignments_f where assignment_id = l_asgid;
3911:
3912: -- adding this logic to resolving bug 13797428
3913: select distinct(person_id) into l_each_person_id
3914: from per_all_assignments_f where assignment_id = l_asgid;
3915: hr_utility.trace('l_each_person_id ' || l_each_person_id);
3916:
3917: open c_diff_assignments(l_each_person_id);
3918: fetch c_diff_assignments into l_each_asgid;
3919:
3917: open c_diff_assignments(l_each_person_id);
3918: fetch c_diff_assignments into l_each_asgid;
3919:
3920: loop
3921: hr_utility.trace('start of c_diff_assignments CURSOR');
3922:
3923: if c_diff_assignments%rowcount = 0 then
3924: hr_utility.trace('No rows fetched from c_diff_assignments');
3925: end if;
3920: loop
3921: hr_utility.trace('start of c_diff_assignments CURSOR');
3922:
3923: if c_diff_assignments%rowcount = 0 then
3924: hr_utility.trace('No rows fetched from c_diff_assignments');
3925: end if;
3926:
3927: exit when c_diff_assignments%NOTFOUND;
3928: begin
3925: end if;
3926:
3927: exit when c_diff_assignments%NOTFOUND;
3928: begin
3929: hr_utility.trace('l_each_asgid ' || l_each_asgid);
3930:
3931: open c_balance_feed_info(l_balance_name1,l_each_asgid);
3932: fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3933:
3930:
3931: open c_balance_feed_info(l_balance_name1,l_each_asgid);
3932: fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3933:
3934: hr_utility.trace('CURSOR count ' || c_balance_feed_info%rowcount);
3935:
3936: if c_balance_feed_info%rowcount = 0 and nvl(l_status_indian,'N') = 'N' then
3937: l_balance_name2 := 'T4_BOX20';
3938: if c_balance_feed_info%isopen then
3939: close c_balance_feed_info;
3940: end if;
3941: end if;
3942:
3943: hr_utility.trace('BOX name is ' || l_balance_name2);
3944: if not c_balance_feed_info%isopen then
3945: open c_balance_feed_info(l_balance_name2,l_each_asgid);
3946: fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3947: hr_utility.trace('CURSOR count BOX20 is ' || c_balance_feed_info%rowcount);
3943: hr_utility.trace('BOX name is ' || l_balance_name2);
3944: if not c_balance_feed_info%isopen then
3945: open c_balance_feed_info(l_balance_name2,l_each_asgid);
3946: fetch c_balance_feed_info into l_registration_no, l_balance_name, l_screen_entry_value;
3947: hr_utility.trace('CURSOR count BOX20 is ' || c_balance_feed_info%rowcount);
3948: end if;
3949:
3950: loop
3951: hr_utility.trace('start of c_balance_feed_info CURSOR');
3947: hr_utility.trace('CURSOR count BOX20 is ' || c_balance_feed_info%rowcount);
3948: end if;
3949:
3950: loop
3951: hr_utility.trace('start of c_balance_feed_info CURSOR');
3952: exit when c_balance_feed_info%NOTFOUND;
3953:
3954: -- exception handling added by sneelapa for bug 6399498
3955: -- screen_entry_value will be NON NUMERIC data for certain Element Entry Values
3973:
3974: if l_value is null then
3975: l_value := 0;
3976: end if;
3977: hr_utility.trace('l_value is ' || to_char(l_value));
3978: hr_utility.trace('old_l_value is ' || old_l_value);
3979: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3980: hr_utility.trace('l_registration_no is ' || l_registration_no);
3981:
3974: if l_value is null then
3975: l_value := 0;
3976: end if;
3977: hr_utility.trace('l_value is ' || to_char(l_value));
3978: hr_utility.trace('old_l_value is ' || old_l_value);
3979: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3980: hr_utility.trace('l_registration_no is ' || l_registration_no);
3981:
3982: if l_registration_no <> 'NOT FOUND' then
3975: l_value := 0;
3976: end if;
3977: hr_utility.trace('l_value is ' || to_char(l_value));
3978: hr_utility.trace('old_l_value is ' || old_l_value);
3979: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3980: hr_utility.trace('l_registration_no is ' || l_registration_no);
3981:
3982: if l_registration_no <> 'NOT FOUND' then
3983: if old_l_registration_no is null and l_value <> 0 then
3976: end if;
3977: hr_utility.trace('l_value is ' || to_char(l_value));
3978: hr_utility.trace('old_l_value is ' || old_l_value);
3979: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
3980: hr_utility.trace('l_registration_no is ' || l_registration_no);
3981:
3982: if l_registration_no <> 'NOT FOUND' then
3983: if old_l_registration_no is null and l_value <> 0 then
3984: old_l_registration_no := l_registration_no;
3986:
3987: -- modified for bug 6399498
3988: if old_l_value >= l_value then -- revert back for bug 11886376
3989: -- if old_l_value > l_value then
3990: hr_utility.trace('if old_l_value >= l_value');
3991: if old_l_registration_no is not null then
3992: l_registration_no := old_l_registration_no;
3993: else old_l_registration_no := l_registration_no;
3994: end if;
3994: end if;
3995: -- modified for bug 6399498
3996: elsif old_l_value < l_value then -- revert back for bug 11886376
3997: -- elsif old_l_value <= l_value then
3998: hr_utility.trace('elsif old_l_value < l_value');
3999: old_l_value := l_value;
4000: old_l_registration_no := l_registration_no;
4001: end if;
4002: end if;
4012:
4013: close c_balance_feed_info;
4014: exception
4015: when others then
4016: hr_utility.trace('Exception raised');
4017: null;
4018: end;
4019: hr_utility.trace('Reached end');
4020: fetch c_diff_assignments into l_each_asgid;
4015: when others then
4016: hr_utility.trace('Exception raised');
4017: null;
4018: end;
4019: hr_utility.trace('Reached end');
4020: fetch c_diff_assignments into l_each_asgid;
4021: end loop;
4022: close c_diff_assignments;
4023:
4020: fetch c_diff_assignments into l_each_asgid;
4021: end loop;
4022: close c_diff_assignments;
4023:
4024: hr_utility.trace('loop ended');
4025: hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026: hr_utility.trace('l_registration no is ' || l_registration_no);
4027: hr_utility.trace('l_value is ' || to_char(l_value));
4028: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4021: end loop;
4022: close c_diff_assignments;
4023:
4024: hr_utility.trace('loop ended');
4025: hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026: hr_utility.trace('l_registration no is ' || l_registration_no);
4027: hr_utility.trace('l_value is ' || to_char(l_value));
4028: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4029:
4022: close c_diff_assignments;
4023:
4024: hr_utility.trace('loop ended');
4025: hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026: hr_utility.trace('l_registration no is ' || l_registration_no);
4027: hr_utility.trace('l_value is ' || to_char(l_value));
4028: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4029:
4030: -- modified for bug 6399498, for QA reported issue in this bug.
4023:
4024: hr_utility.trace('loop ended');
4025: hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026: hr_utility.trace('l_registration no is ' || l_registration_no);
4027: hr_utility.trace('l_value is ' || to_char(l_value));
4028: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4029:
4030: -- modified for bug 6399498, for QA reported issue in this bug.
4031: -- if l_registration_no is not null and old_l_value <> 0 then
4024: hr_utility.trace('loop ended');
4025: hr_utility.trace('old_l_value is ' || to_char(old_l_value));
4026: hr_utility.trace('l_registration no is ' || l_registration_no);
4027: hr_utility.trace('l_value is ' || to_char(l_value));
4028: hr_utility.trace('old_l_registration_no is ' || old_l_registration_no);
4029:
4030: -- modified for bug 6399498, for QA reported issue in this bug.
4031: -- if l_registration_no is not null and old_l_value <> 0 then
4032:
4032:
4033:
4034: -- ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') added for bug 7611439
4035:
4036: hr_utility.trace('box_52_exists before if condition '||box_52_exists);
4037: hr_utility.trace('box_20_exists before if condition '||box_20_exists);
4038: hr_utility.trace('box_52_exists before Archiving old_l_registration_no '||box_52_exists);
4039:
4040: if old_l_registration_no is not null and
4033:
4034: -- ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') added for bug 7611439
4035:
4036: hr_utility.trace('box_52_exists before if condition '||box_52_exists);
4037: hr_utility.trace('box_20_exists before if condition '||box_20_exists);
4038: hr_utility.trace('box_52_exists before Archiving old_l_registration_no '||box_52_exists);
4039:
4040: if old_l_registration_no is not null and
4041: ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') then
4034: -- ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') added for bug 7611439
4035:
4036: hr_utility.trace('box_52_exists before if condition '||box_52_exists);
4037: hr_utility.trace('box_20_exists before if condition '||box_20_exists);
4038: hr_utility.trace('box_52_exists before Archiving old_l_registration_no '||box_52_exists);
4039:
4040: if old_l_registration_no is not null and
4041: ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') then
4042:
4039:
4040: if old_l_registration_no is not null and
4041: ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') then
4042:
4043: hr_utility.trace('l_registration no is ' || 'archiving');
4044: -- hr_utility.trace_off;
4045:
4046: ff_archive_api.create_archive_item(
4047: p_archive_item_id => l_archive_item_id
4040: if old_l_registration_no is not null and
4041: ( nvl(box_52_exists, 'N') = 'Y' or nvl(box_20_exists, 'N') = 'Y') then
4042:
4043: hr_utility.trace('l_registration no is ' || 'archiving');
4044: -- hr_utility.trace_off;
4045:
4046: ff_archive_api.create_archive_item(
4047: p_archive_item_id => l_archive_item_id
4048: ,p_user_entity_id =>
4071: end loop;
4072:
4073: begin
4074: l_counter := 0;
4075: hr_utility.trace('selecting people');
4076:
4077: open get_person_info(l_asgid);
4078:
4079: fetch get_person_info
4114:
4115: for l_out_cnt in
4116: l_outputs.first..l_outputs.last
4117: loop
4118: hr_utility.trace('inside loop for SIN validation');
4119: if l_outputs(l_out_cnt).name='RETURN_VALUE' then
4120: l_return_value := l_outputs(l_out_cnt).value;
4121: end if;
4122: if l_outputs(l_out_cnt).name='INVALID_MESG' then
4133: l_invalid_sin := 'A';
4134: end if;
4135:
4136: l_person_arch_step := 2;
4137: hr_utility.trace('selected people');
4138: /* Initialise l_count */
4139: l_count := 0;
4140:
4141: --hr_utility.trace_on('Y','ORACLE');
4137: hr_utility.trace('selected people');
4138: /* Initialise l_count */
4139: l_count := 0;
4140:
4141: --hr_utility.trace_on('Y','ORACLE');
4142:
4143: l_counter := l_counter + 1;
4144: l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
4145: l_user_entity_value_tab(l_counter) := l_person_id;
4201: l_employee_number := null;
4202: l_national_identifier := null;
4203: l_middle_names := null;
4204: l_employee_phone_no := null;
4205: hr_utility.raise_error;
4206: when others then
4207: hr_utility.trace('Error in archiving person '||
4208: to_char(l_person_id) || 'at step :' ||
4209: to_char(l_person_arch_step) ||
4203: l_middle_names := null;
4204: l_employee_phone_no := null;
4205: hr_utility.raise_error;
4206: when others then
4207: hr_utility.trace('Error in archiving person '||
4208: to_char(l_person_id) || 'at step :' ||
4209: to_char(l_person_arch_step) ||
4210: 'sqlcode : ' || to_char(sqlcode));
4211: end;
4219: l_province_code := addr.province;
4220: l_postal_code := replace(addr.postal_code,' ');
4221: l_country_code := addr.addr_line_5;
4222:
4223: hr_utility.trace('selected address');
4224:
4225: l_counter := 0;
4226: l_counter := l_counter + 1;
4227: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
4265:
4266: l_context_id := l_taxunit_context_id;
4267: l_context_val := l_tax_unit_id;
4268:
4269: hr_utility.trace('archiving address');
4270: ff_archive_api.create_archive_item(
4271: p_archive_item_id => l_archive_item_id
4272: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
4273: ,p_archive_value => l_user_entity_value_tab(i)
4276: ,p_legislation_code => 'CA'
4277: ,p_object_version_number => l_object_version_number
4278: ,p_some_warning => l_some_warning
4279: );
4280: hr_utility.trace('archived address');
4281: end loop;
4282: end if;
4283: hr_utility.trace('end of eoy_archive_data');
4284: l_step := 37;
4279: );
4280: hr_utility.trace('archived address');
4281: end loop;
4282: end if;
4283: hr_utility.trace('end of eoy_archive_data');
4284: l_step := 37;
4285:
4286:
4287: -- Federal YE Amendment Pre-Process Validation (T4 Amendmendment Archiver code)
4287: -- Federal YE Amendment Pre-Process Validation (T4 Amendmendment Archiver code)
4288:
4289: Begin
4290:
4291: hr_utility.trace('Started Federal YE Amendment PP Validation ');
4292: select effective_date,report_type
4293: into ld_fapp_effective_date,lv_fapp_report_type
4294: from pay_payroll_actions
4295: where payroll_action_id = l_payroll_action_id;
4293: into ld_fapp_effective_date,lv_fapp_report_type
4294: from pay_payroll_actions
4295: where payroll_action_id = l_payroll_action_id;
4296:
4297: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
4298: to_char(l_payroll_action_id));
4299: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4300:
4301: IF lv_fapp_report_type = 'CAEOY_T4_AMEND_PP' then
4295: where payroll_action_id = l_payroll_action_id;
4296:
4297: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
4298: to_char(l_payroll_action_id));
4299: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
4300:
4301: IF lv_fapp_report_type = 'CAEOY_T4_AMEND_PP' then
4302: begin
4303:
4304: open c_get_fapp_locked_action_id(p_assactid);
4305: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4306: close c_get_fapp_locked_action_id;
4307:
4308: hr_utility.trace('T4 Amend PP Action ID : '||to_char(p_assactid));
4309: hr_utility.trace('ln_fapp_locked_action_id :'||
4310: to_char(ln_fapp_locked_action_id));
4311: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4312: fetch c_get_fapp_lkd_actid_rtype
4305: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
4306: close c_get_fapp_locked_action_id;
4307:
4308: hr_utility.trace('T4 Amend PP Action ID : '||to_char(p_assactid));
4309: hr_utility.trace('ln_fapp_locked_action_id :'||
4310: to_char(ln_fapp_locked_action_id));
4311: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4312: fetch c_get_fapp_lkd_actid_rtype
4313: into lv_fapp_locked_actid_reptype;
4311: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
4312: fetch c_get_fapp_lkd_actid_rtype
4313: into lv_fapp_locked_actid_reptype;
4314: close c_get_fapp_lkd_actid_rtype;
4315: hr_utility.trace('lv_fapp_locked_actid_reptype :'||
4316: lv_fapp_locked_actid_reptype);
4317:
4318: open c_get_fapp_prov_emp(p_assactid);
4319: loop
4318: open c_get_fapp_prov_emp(p_assactid);
4319: loop
4320: fetch c_get_fapp_prov_emp into lv_fapp_prov;
4321: exit when c_get_fapp_prov_emp%NOTFOUND;
4322: hr_utility.trace('lv_fapp_prov : '||lv_fapp_prov);
4323: lv_fapp_flag := compare_archive_data(p_assactid,
4324: ln_fapp_locked_action_id,
4325: lv_fapp_prov);
4326:
4325: lv_fapp_prov);
4326:
4327: if lv_fapp_flag = 'Y' then
4328:
4329: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4330: hr_utility.trace('Archiving T4 Amendment Flag is : ' || lv_fapp_flag);
4331:
4332: ff_archive_api.create_archive_item(
4333: p_archive_item_id => l_archive_item_id
4326:
4327: if lv_fapp_flag = 'Y' then
4328:
4329: hr_utility.trace('Jurisdiction is : ' || lv_fapp_prov);
4330: hr_utility.trace('Archiving T4 Amendment Flag is : ' || lv_fapp_flag);
4331:
4332: ff_archive_api.create_archive_item(
4333: p_archive_item_id => l_archive_item_id
4334: ,p_user_entity_id => get_user_entity_id('CAEOY_T4_AMENDMENT_FLAG'
4352:
4353: end; -- report_type validation
4354:
4355: END IF; -- report type validation for FAPP
4356: hr_utility.trace('End of Federal YE Amendment PP Validation');
4357:
4358: Exception when no_data_found then
4359: hr_utility.trace('Report type not found for given Payroll_action ');
4360: null;
4355: END IF; -- report type validation for FAPP
4356: hr_utility.trace('End of Federal YE Amendment PP Validation');
4357:
4358: Exception when no_data_found then
4359: hr_utility.trace('Report type not found for given Payroll_action ');
4360: null;
4361: End;
4362: -- End of Federal YE Amendment Pre-Process Validation
4363:
4383:
4384:
4385: begin
4386:
4387: --hr_utility.trace_on('Y','ORACLE');
4388:
4389: select legislative_parameters,
4390: trunc(effective_date,'Y'),
4391: effective_date,
4396: l_business_group
4397: from pay_payroll_actions
4398: where payroll_action_id = pactid;
4399:
4400: hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
4401: l_eoy_tax_unit_id := get_parameter('TRANSFER_GRE',l_legislative_parameters);
4402:
4403: select org_information11
4404: into l_transmitter_gre_id
4405: from hr_organization_information
4406: where organization_id = l_eoy_tax_unit_id
4407: and org_information_context = 'Canada Employer Identification';
4408:
4409: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
4410: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
4411:
4412: if l_eoy_tax_unit_id <> -99999 then
4413:
4406: where organization_id = l_eoy_tax_unit_id
4407: and org_information_context = 'Canada Employer Identification';
4408:
4409: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
4410: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
4411:
4412: if l_eoy_tax_unit_id <> -99999 then
4413:
4414: sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
4447: l_archive := chk_gre_archive(pactid);
4448:
4449: if g_archive_flag = 'N' then
4450:
4451: hr_utility.trace('eoy_range_cursor archiving employer data');
4452:
4453: /* now the archiver has provision for archiving payroll_action_level
4454: data. So make use of that */
4455:
4452:
4453: /* now the archiver has provision for archiving payroll_action_level
4454: data. So make use of that */
4455:
4456: hr_utility.trace('eoy_range_cursor archiving employer data');
4457:
4458: eoy_archive_gre_data(pactid,
4459: l_eoy_tax_unit_id,
4460: l_transmitter_gre_id);