[Home] [Help]
365: open csr_defined_balance;
366: fetch csr_defined_balance into l_defined_balance_id;
367: if csr_defined_balance%notfound then
368: close csr_defined_balance;
369: raise hr_utility.hr_error;
370: else
371: close csr_defined_balance;
372: end if;
373:
488: /* An invalid report type has been passed so fail. */
489:
490: else
491:
492: raise hr_utility.hr_error;
493:
494: end if;
495:
496: end get_selection_information;
711:
712: /* Get the report type, report qualifier, business group id and the
713: gre for which the archiving has to be done */
714:
715: hr_utility.trace('getting report type ');
716:
717: select effective_date,
718: report_type,
719: -- Added for bug 10399514
734: where payroll_action_id = pactid;
735:
736: l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
737:
738: hr_utility.trace('getting dates');
739:
740: get_dates(l_report_type,
741: l_effective_date,
742: l_period_end,
744: l_quarter_end,
745: l_year_start,
746: l_year_end);
747:
748: hr_utility.trace('getting selection information');
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
745: l_year_start,
746: l_year_end);
747:
748: hr_utility.trace('getting selection information');
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
746: l_year_end);
747:
748: hr_utility.trace('getting selection information');
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
754:
747:
748: hr_utility.trace('getting selection information');
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
754:
755: get_selection_information
748: hr_utility.trace('getting selection information');
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
754:
755: get_selection_information
756: (l_report_type,
749: hr_utility.trace('report type '|| l_report_type);
750: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
751: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
752: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
753: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
754:
755: get_selection_information
756: (l_report_type,
757: l_quarter_start,
792:
793: -- open c_eoy_qbin;
794:
795: if l_person_on then
796: hr_utility.trace('opening c_eoy_gre_range CURSOR');
797: OPEN c_eoy_gre_range ;
798: else
799: hr_utility.trace('opening c_eoy_gre CURSOR');
800: OPEN c_eoy_gre;
795: if l_person_on then
796: hr_utility.trace('opening c_eoy_gre_range CURSOR');
797: OPEN c_eoy_gre_range ;
798: else
799: hr_utility.trace('opening c_eoy_gre CURSOR');
800: OPEN c_eoy_gre;
801: end if ;
802:
803: -- Code modification for bug 10399514 ends here
804: end if;
805:
806: /* Loop for all rows returned for SQL statement. */
807:
808: hr_utility.trace('Entering loop');
809:
810: loop
811:
812: if l_eoy_tax_unit_id <> 99999 then
810: loop
811:
812: if l_eoy_tax_unit_id <> 99999 then
813:
814: hr_utility.trace('Fetching person id');
815:
816: -- Code modification for bug 10399514 starts here
817: if l_person_on then
818: hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
814: hr_utility.trace('Fetching person id');
815:
816: -- Code modification for bug 10399514 starts here
817: if l_person_on then
818: hr_utility.trace('fetching from c_eoy_gre_range CURSOR');
819: fetch c_eoy_gre_range
820: into l_person_id,
821: l_assignment_id,
822: l_tax_unit_id,
822: l_tax_unit_id,
823: l_effective_end_date;
824: exit when c_eoy_gre_range%NOTFOUND;
825: else
826: hr_utility.trace('fetching from c_eoy_gre CURSOR');
827: fetch c_eoy_gre
828: into l_person_id,
829: l_assignment_id,
830: l_tax_unit_id,
848: /* If the new row is the same as the previous row according to the way
849: the rows are grouped then discard the row ie. grouping by GRE
850: requires a single row for each person / GRE combination. */
851:
852: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
853: hr_utility.trace('previous tax unit id is '||
854: to_char(l_prev_tax_unit_id));
855:
856: if ( l_person_id = l_prev_person_id and
849: the rows are grouped then discard the row ie. grouping by GRE
850: requires a single row for each person / GRE combination. */
851:
852: hr_utility.trace('tax unit id is '|| to_char(l_tax_unit_id));
853: hr_utility.trace('previous tax unit id is '||
854: to_char(l_prev_tax_unit_id));
855:
856: if ( l_person_id = l_prev_person_id and
857: l_tax_unit_id = l_prev_tax_unit_id) then
858: null;
859:
860: else
861:
862: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
863: hr_utility.trace('person is '|| to_char(l_person_id));
864: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
865:
866:
859:
860: else
861:
862: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
863: hr_utility.trace('person is '|| to_char(l_person_id));
864: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
865:
866:
867: /* Have a new unique row according to the way the rows are grouped.
860: else
861:
862: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
863: hr_utility.trace('person is '|| to_char(l_person_id));
864: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
865:
866:
867: /* Have a new unique row according to the way the rows are grouped.
868: ** The inclusion of the person is dependent on having a non zero
871: ** indicate their inclusion in the magnetic tape report. */
872:
873: /* Set up the context of tax unit id */
874:
875: hr_utility.trace('Setting context');
876:
877: if l_tax_unit_context then
878: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
879: end if;
919: open c_get_asg_id(l_person_id);
920: fetch c_get_asg_id into l_primary_asg;
921: if c_get_asg_id%NOTFOUND then
922: close c_get_asg_id;
923: raise hr_utility.hr_error;
924: else
925: close c_get_asg_id;
926: end if;
927:
944: from dual;
945:
946: /* Insert into pay_assignment_actions. */
947:
948: hr_utility.trace('creating assignment action');
949:
950: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
951: pactid,chunk,l_tax_unit_id);
952:
955: ** to do an additional checking against the assignment
956: ** table
957: */
958:
959: hr_utility.trace('updating assignment action');
960:
961: update pay_assignment_actions aa
962: set aa.serial_number = to_char(l_person_id)
963: where aa.assignment_action_id = lockingactid;
974:
975: if l_eoy_tax_unit_id <> 99999 then
976: -- Code modification for bug 10399514 starts here
977: if l_person_on then
978: hr_utility.trace('closing c_eoy_gre_range CURSOR');
979: close c_eoy_gre_range;
980: else
981: hr_utility.trace('closing c_eoy_gre CURSOR');
982: close c_eoy_gre;
977: if l_person_on then
978: hr_utility.trace('closing c_eoy_gre_range CURSOR');
979: close c_eoy_gre_range;
980: else
981: hr_utility.trace('closing c_eoy_gre CURSOR');
982: close c_eoy_gre;
983: end if ;
984: -- Code modification for bug 10399514 ends here
985: /* else
1004: l_user_entity_id number;
1005:
1006: begin
1007:
1008: hr_utility.trace('getting the user_entity_id for '
1009: || p_dbi_name);
1010: select fdi.user_entity_id
1011: into l_user_entity_id
1012: from ff_database_items fdi,
1018: return l_user_entity_id;
1019:
1020: exception
1021: when others then
1022: hr_utility.trace('Error while getting the user_entity_id for '
1023: || p_dbi_name ||'**');
1024: raise hr_utility.hr_error;
1025:
1026: end get_user_entity_id;
1020: exception
1021: when others then
1022: hr_utility.trace('Error while getting the user_entity_id for '
1023: || p_dbi_name ||'**');
1024: raise hr_utility.hr_error;
1025:
1026: end get_user_entity_id;
1027:
1028: /* Name : get_footnote_user_entity_id
1046: return l_user_entity_id;
1047:
1048: exception
1049: when others then
1050: hr_utility.trace('Error while getting the user_entity_id'
1051: || p_dbi_name ||'***');
1052: return 0;
1053:
1054: end get_footnote_user_entity_id;
1116: ln_yepp_footnote_count number;
1117: ln_amend_footnote_count number;
1118:
1119: begin
1120: -- hr_utility.trace_on('Y','TEST');
1121:
1122: /* Initialization Process */
1123:
1124: lv_flag := 'N';
1141:
1142: /* Populate T4A Amendment Footnotes */
1143: open c_get_nonbox_footnote(p_assignment_action_id);
1144:
1145: hr_utility.trace('Populating T4A Amendment Footnote ');
1146:
1147: ln_amend_footnote_count := 0;
1148: loop
1149: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
1148: loop
1149: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
1150: exit when c_get_nonbox_footnote%NOTFOUND;
1151:
1152: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
1153: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
1154:
1155: ln_amend_footnote_count := ln_amend_footnote_count + 1;
1156: end loop;
1149: fetch c_get_nonbox_footnote into ltr_amend_footnote(ln_amend_footnote_count);
1150: exit when c_get_nonbox_footnote%NOTFOUND;
1151:
1152: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
1153: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
1154:
1155: ln_amend_footnote_count := ln_amend_footnote_count + 1;
1156: end loop;
1157:
1164: loop
1165: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
1166: exit when c_get_nonbox_footnote%NOTFOUND;
1167:
1168: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
1169: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
1170:
1171: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
1172: end loop;
1165: fetch c_get_nonbox_footnote into ltr_yepp_footnote(ln_yepp_footnote_count);
1166: exit when c_get_nonbox_footnote%NOTFOUND;
1167:
1168: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
1169: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
1170:
1171: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
1172: end loop;
1173:
1176:
1177: /* Populate T4A Amendment Data for an assignment_action */
1178: open c_get_emp_t4a_data(p_assignment_action_id);
1179:
1180: hr_utility.trace('Populating T4A Amendment Data ');
1181: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1182:
1183: ln_amend_box_count := 0;
1184: loop
1177: /* Populate T4A Amendment Data for an assignment_action */
1178: open c_get_emp_t4a_data(p_assignment_action_id);
1179:
1180: hr_utility.trace('Populating T4A Amendment Data ');
1181: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
1182:
1183: ln_amend_box_count := 0;
1184: loop
1185: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1184: loop
1185: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1186: exit when c_get_emp_t4a_data%NOTFOUND;
1187:
1188: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1189: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1190: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1191: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1192:
1185: fetch c_get_emp_t4a_data into ltr_amend_arch_data(ln_amend_box_count);
1186: exit when c_get_emp_t4a_data%NOTFOUND;
1187:
1188: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1189: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1190: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1191: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1192:
1193: ln_amend_box_count := ln_amend_box_count + 1;
1186: exit when c_get_emp_t4a_data%NOTFOUND;
1187:
1188: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1189: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1190: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1191: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1192:
1193: ln_amend_box_count := ln_amend_box_count + 1;
1194: end loop;
1187:
1188: hr_utility.trace('I :'||to_char(ln_amend_box_count));
1189: hr_utility.trace('Archive_Context1: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_context1));
1190: hr_utility.trace('Archive_UE_id: '||to_char(ltr_amend_arch_data(ln_amend_box_count).archive_ue_id));
1191: hr_utility.trace('Archive_Value: '||ltr_amend_arch_data(ln_amend_box_count).archive_value);
1192:
1193: ln_amend_box_count := ln_amend_box_count + 1;
1194: end loop;
1195:
1198:
1199: /* Populate T4A YEPP Data for an assignment_action */
1200: open c_get_emp_t4a_data(p_locked_action_id);
1201:
1202: hr_utility.trace('Populating T4A YEPP Data ');
1203: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1204:
1205: ln_yepp_box_count := 0;
1206: loop
1199: /* Populate T4A YEPP Data for an assignment_action */
1200: open c_get_emp_t4a_data(p_locked_action_id);
1201:
1202: hr_utility.trace('Populating T4A YEPP Data ');
1203: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
1204:
1205: ln_yepp_box_count := 0;
1206: loop
1207: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1206: loop
1207: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1208: exit when c_get_emp_t4a_data%NOTFOUND;
1209:
1210: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1211: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1212: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1213: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1214:
1207: fetch c_get_emp_t4a_data into ltr_yepp_arch_data(ln_yepp_box_count);
1208: exit when c_get_emp_t4a_data%NOTFOUND;
1209:
1210: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1211: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1212: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1213: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1214:
1215: ln_yepp_box_count := ln_yepp_box_count + 1;
1208: exit when c_get_emp_t4a_data%NOTFOUND;
1209:
1210: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1211: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1212: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1213: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1214:
1215: ln_yepp_box_count := ln_yepp_box_count + 1;
1216: end loop;
1209:
1210: hr_utility.trace('I :'||to_char(ln_yepp_box_count));
1211: hr_utility.trace('Archive_Context1: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_context1));
1212: hr_utility.trace('Archive_UE_id: '||to_char(ltr_yepp_arch_data(ln_yepp_box_count).archive_ue_id));
1213: hr_utility.trace('Archive_Value: '||ltr_yepp_arch_data(ln_yepp_box_count).archive_value);
1214:
1215: ln_yepp_box_count := ln_yepp_box_count + 1;
1216: end loop;
1217:
1219:
1220: /* Compare T4A Amendment Data and T4A YEPP Data for an
1221: assignment_action */
1222:
1223: hr_utility.trace('Comparing T4A Amend and T4A YEPP Data');
1224:
1225: if ln_yepp_box_count <> ln_amend_box_count then
1226: lv_flag := 'Y';
1227: elsif ln_yepp_box_count = ln_amend_box_count then
1237: (ltr_yepp_arch_data(i).archive_value is not null and
1238: ltr_amend_arch_data(i).archive_value is null)) then
1239:
1240: lv_flag := 'Y';
1241: hr_utility.trace('Archive_UE_id with differnt value :'||to_char(ltr_yepp_arch_data(i).archive_ue_id));
1242: exit;
1243: end if;
1244: end if;
1245: end loop;
1247:
1248: /* Compare T4A Amendment Footnotes and T4A YEPP Footnotes for an
1249: assignment_action */
1250:
1251: hr_utility.trace('Comparing T4A Amend and T4A YEPP Footnotes');
1252:
1253: if lv_flag <> 'Y' then
1254: if ln_yepp_footnote_count <> ln_amend_footnote_count then
1255: lv_flag := 'Y';
1267: (ltr_yepp_footnote(i).value is not null and
1268: ltr_amend_footnote(i).value is null)) then
1269:
1270: lv_flag := 'Y';
1271: hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
1272: exit;
1273: end if;
1274: end if;
1275: end loop;
1280: flag to 'N' */
1281:
1282: if lv_flag <> 'Y' then
1283: lv_flag := 'N';
1284: hr_utility.trace('No value difference for an Employee Asg Action: '||
1285: to_char(p_assignment_action_id));
1286: end if;
1287:
1288: hr_utility.trace('lv_flag :'||lv_flag);
1284: hr_utility.trace('No value difference for an Employee Asg Action: '||
1285: to_char(p_assignment_action_id));
1286: end if;
1287:
1288: hr_utility.trace('lv_flag :'||lv_flag);
1289:
1290: return lv_flag;
1291:
1292: -- hr_utility.trace_off;
1288: hr_utility.trace('lv_flag :'||lv_flag);
1289:
1290: return lv_flag;
1291:
1292: -- hr_utility.trace_off;
1293:
1294: end compare_archive_data;
1295:
1296:
1480:
1481: /* Archive the Employer level data */
1482:
1483: begin
1484: hr_utility.trace('getting employer data ');
1485:
1486: open employer_info;
1487: fetch employer_info
1488: into l_name, l_business_group_id,
1517:
1518:
1519: if employer_info%FOUND then
1520: close employer_info;
1521: hr_utility.trace('got employer data ');
1522: else
1523: hr_utility.trace('cannot find employer data ');
1524: l_employer_ein := null;
1525: l_address_line_1 := null;
1519: if employer_info%FOUND then
1520: close employer_info;
1521: hr_utility.trace('got employer data ');
1522: else
1523: hr_utility.trace('cannot find employer data ');
1524: l_employer_ein := null;
1525: l_address_line_1 := null;
1526: l_address_line_2 := null;
1527: l_address_line_3 := null;
1542: l_technical_contact_phone := null;
1543: l_technical_contact_language := null;
1544:
1545: close employer_info;
1546: hr_utility.raise_error;
1547: end if;
1548:
1549: /* Added this part to fix bug#2598802 */
1550: if c_get_gre_acct_info%found then
1761:
1762: for i in 1..l_counter loop
1763:
1764: l_arch_gre_step := 42;
1765: hr_utility.trace('calling archive API ' || l_user_entity_name_tab(i));
1766: ff_archive_api.create_archive_item(
1767: -- p_validate => 'TRUE'
1768: p_archive_item_id => l_archive_item_id
1769: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
1773: ,p_legislation_code => 'CA'
1774: ,p_object_version_number => l_object_version_number
1775: ,p_some_warning => l_some_warning
1776: );
1777: hr_utility.trace('Ended calling archive API');
1778: l_arch_gre_step := 47;
1779: end loop;
1780:
1781: /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1779: end loop;
1780:
1781: /* Removed the unnecessary archiving of db items with dimension _GRE_YTD */
1782:
1783: --hr_utility.trace_off;
1784: g_archive_flag := 'Y';
1785: exception
1786: when others then
1787: g_archive_flag := 'N';
1785: exception
1786: when others then
1787: g_archive_flag := 'N';
1788: if l_transmitter_name is null then
1789: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1790: hr_utility.set_message_token('ORGIND','GRE');
1791: /* push message into pay_message_lines */
1792: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1793: pay_core_utils.push_token('ORGIND','GRE');
1786: when others then
1787: g_archive_flag := 'N';
1788: if l_transmitter_name is null then
1789: hr_utility.set_message(801,'PAY_74014_NO_TRANSMITTER_ORG');
1790: hr_utility.set_message_token('ORGIND','GRE');
1791: /* push message into pay_message_lines */
1792: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1793: pay_core_utils.push_token('ORGIND','GRE');
1794: hr_utility.raise_error;
1790: hr_utility.set_message_token('ORGIND','GRE');
1791: /* push message into pay_message_lines */
1792: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1793: pay_core_utils.push_token('ORGIND','GRE');
1794: hr_utility.raise_error;
1795: else
1796: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1797: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1792: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','P');
1793: pay_core_utils.push_token('ORGIND','GRE');
1794: hr_utility.raise_error;
1795: else
1796: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1797: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1799: hr_utility.raise_error;
1800: end if;
1794: hr_utility.raise_error;
1795: else
1796: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1797: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1799: hr_utility.raise_error;
1800: end if;
1801: raise hr_utility.hr_error;
1802:
1795: else
1796: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1797: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1799: hr_utility.raise_error;
1800: end if;
1801: raise hr_utility.hr_error;
1802:
1803: end eoy_archive_gre_data;
1797: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1798: hr_utility.set_message(801, 'PAY_34957_ARCPROC_MUST_EXIST');
1799: hr_utility.raise_error;
1800: end if;
1801: raise hr_utility.hr_error;
1802:
1803: end eoy_archive_gre_data;
1804:
1805: /* Name : chk_gre_archive
1821: where fai.context1 = p_payroll_action_id
1822: and archive_type = 'PA');
1823: begin
1824:
1825: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1826:
1827: if g_archive_flag = 'Y' then
1828: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1829: return (TRUE);
1824:
1825: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1826:
1827: if g_archive_flag = 'Y' then
1828: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1829: return (TRUE);
1830: else
1831:
1832: hr_utility.trace('chk_gre_archive - opening cursor');
1828: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1829: return (TRUE);
1830: else
1831:
1832: hr_utility.trace('chk_gre_archive - opening cursor');
1833:
1834: open c_chk_payroll_action;
1835: fetch c_chk_payroll_action into l_flag;
1836: if c_chk_payroll_action%FOUND then
1833:
1834: open c_chk_payroll_action;
1835: fetch c_chk_payroll_action into l_flag;
1836: if c_chk_payroll_action%FOUND then
1837: hr_utility.trace('chk_gre_archive - found in cursor');
1838: g_archive_flag := 'Y';
1839: else
1840: hr_utility.trace('chk_gre_archive - not found in cursor');
1841: g_archive_flag := 'N';
1836: if c_chk_payroll_action%FOUND then
1837: hr_utility.trace('chk_gre_archive - found in cursor');
1838: g_archive_flag := 'Y';
1839: else
1840: hr_utility.trace('chk_gre_archive - not found in cursor');
1841: g_archive_flag := 'N';
1842: end if;
1843:
1844: hr_utility.trace('chk_gre_archive - closing cursor');
1840: hr_utility.trace('chk_gre_archive - not found in cursor');
1841: g_archive_flag := 'N';
1842: end if;
1843:
1844: hr_utility.trace('chk_gre_archive - closing cursor');
1845: close c_chk_payroll_action;
1846: if g_archive_flag = 'Y' then
1847: hr_utility.trace('chk_gre_archive - returning true');
1848: return (TRUE);
1843:
1844: hr_utility.trace('chk_gre_archive - closing cursor');
1845: close c_chk_payroll_action;
1846: if g_archive_flag = 'Y' then
1847: hr_utility.trace('chk_gre_archive - returning true');
1848: return (TRUE);
1849: else
1850: hr_utility.trace('chk_gre_archive - returning false');
1851: return(FALSE);
1846: if g_archive_flag = 'Y' then
1847: hr_utility.trace('chk_gre_archive - returning true');
1848: return (TRUE);
1849: else
1850: hr_utility.trace('chk_gre_archive - returning false');
1851: return(FALSE);
1852: end if;
1853: end if;
1854: end chk_gre_archive;
1881: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1882: end if;
1883: close c_get_min_chunk;
1884:
1885: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1886: l_step := 2;
1887: l_archive := chk_gre_archive(p_payroll_action_id);
1888:
1889: l_step := 3;
1886: l_step := 2;
1887: l_archive := chk_gre_archive(p_payroll_action_id);
1888:
1889: l_step := 3;
1890: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1891: exception
1892: when others then
1893: raise_application_error(-20001,'eoy_archinit at '
1894: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
2137: pca.effective_end_date;
2138:
2139: begin
2140:
2141: -- hr_utility.trace_on('Y','ORACLEMM');
2142:
2143: l_count := 0;
2144: l_box38_footnote_code := '00';
2145: -- l_negative_balance_exists := 'N';
2144: l_box38_footnote_code := '00';
2145: -- l_negative_balance_exists := 'N';
2146: l_negative_balance_exists := p_negative_balance_exists;
2147:
2148: hr_utility.set_location ('archive_data',1);
2149: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2150:
2151:
2152: SELECT aa.assignment_id,
2145: -- l_negative_balance_exists := 'N';
2146: l_negative_balance_exists := p_negative_balance_exists;
2147:
2148: hr_utility.set_location ('archive_data',1);
2149: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
2150:
2151:
2152: SELECT aa.assignment_id,
2153: pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2173:
2174: l_year_start := trunc(p_effective_date, 'Y');
2175: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2176:
2177: hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
2178:
2179: /* YE-2001 change to avoid hr_ca_tax_units_v view */
2180: select business_group_id
2181: into l_business_group_id
2187: begin
2188: open c_get_latest_asg(lv_serial_number );
2189: fetch c_get_latest_asg into l_aaid;
2190: close c_get_latest_asg;
2191: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2192:
2193: exception
2194: when no_data_found then
2195: l_aaid := -9999;
2195: l_aaid := -9999;
2196: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2197: ||to_char(l_person_id));
2198: end;
2199: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2200: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2201: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2202:
2203: -- code changes for T4A Redesign bug 6456662 starts here. sneelapa.
2196: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
2197: ||to_char(l_person_id));
2198: end;
2199: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2200: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2201: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2202:
2203: -- code changes for T4A Redesign bug 6456662 starts here. sneelapa.
2204:
2197: ||to_char(l_person_id));
2198: end;
2199: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2200: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
2201: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
2202:
2203: -- code changes for T4A Redesign bug 6456662 starts here. sneelapa.
2204:
2205: -- IF condition added for bug 6456662, 9980854
2430: <> 0) ) then
2431:
2432: earning_exists := 1;
2433:
2434: hr_utility.trace('starting loop for balances');
2435:
2436: for i in 1 .. l_count
2437: loop
2438: result := 0;
2450:
2451: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2452: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2453:
2454: hr_utility.trace('i is ' ||to_char(i));
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2451: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
2452: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2453:
2454: hr_utility.trace('i is ' ||to_char(i));
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2452: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
2453:
2454: hr_utility.trace('i is ' ||to_char(i));
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2460:
2453:
2454: hr_utility.trace('i is ' ||to_char(i));
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2460:
2461: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2454: hr_utility.trace('i is ' ||to_char(i));
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2460:
2461: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2462: ( l_balance_type_tab(i),
2455: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
2456: hr_utility.trace('AAID is ' || to_char(l_aaid));
2457: hr_utility.trace('ASGID is ' || to_char(l_asgid));
2458: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
2459: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
2460:
2461: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
2462: ( l_balance_type_tab(i),
2463: 'YTD' ,
2469: l_business_group_id,
2470: NULL
2471: );
2472:
2473: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2474: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2475: hr_utility.trace('Result is ' || to_char(result));
2476:
2477: /* Added this condition to fix bug#2598777 */
2470: NULL
2471: );
2472:
2473: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2474: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2475: hr_utility.trace('Result is ' || to_char(result));
2476:
2477: /* Added this condition to fix bug#2598777 */
2478: if l_user_entity_name_tab(i) = 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD' then
2471: );
2472:
2473: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2474: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2475: hr_utility.trace('Result is ' || to_char(result));
2476:
2477: /* Added this condition to fix bug#2598777 */
2478: if l_user_entity_name_tab(i) = 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD' then
2479: result := round(result);
2555: if l_value is null then
2556: l_value := 0;
2557: end if;
2558:
2559: hr_utility.trace('after check null l_value:'||to_char(l_value));
2560:
2561:
2562: /* Condition to check the amounts and determine the registration
2563: number to archive Bug fix 2408456 */
2561:
2562: /* Condition to check the amounts and determine the registration
2563: number to archive Bug fix 2408456 */
2564: if old_l_value = 0 then
2565: hr_utility.trace('in reg1');
2566: old_l_value := l_value;
2567: old_l_registration_no := l_registration_no;
2568: elsif old_l_value1 = 0 then
2569: hr_utility.trace('in reg2');
2565: hr_utility.trace('in reg1');
2566: old_l_value := l_value;
2567: old_l_registration_no := l_registration_no;
2568: elsif old_l_value1 = 0 then
2569: hr_utility.trace('in reg2');
2570: old_l_value1 := l_value;
2571: old_l_registration_no1 := l_registration_no;
2572: elsif old_l_value2 = 0 then
2573: hr_utility.trace('in reg3');
2569: hr_utility.trace('in reg2');
2570: old_l_value1 := l_value;
2571: old_l_registration_no1 := l_registration_no;
2572: elsif old_l_value2 = 0 then
2573: hr_utility.trace('in reg3');
2574: old_l_value2 := l_value;
2575: old_l_registration_no2 := l_registration_no;
2576: else
2577: if l_value > nvl(old_l_value,0) then
2574: old_l_value2 := l_value;
2575: old_l_registration_no2 := l_registration_no;
2576: else
2577: if l_value > nvl(old_l_value,0) then
2578: hr_utility.trace('old_l1');
2579: old_l_value := l_value;
2580: old_l_registration_no := l_registration_no;
2581: elsif l_value > nvl(old_l_value1,0) then
2582: hr_utility.trace('old_2');
2578: hr_utility.trace('old_l1');
2579: old_l_value := l_value;
2580: old_l_registration_no := l_registration_no;
2581: elsif l_value > nvl(old_l_value1,0) then
2582: hr_utility.trace('old_2');
2583: old_l_value1 := l_value;
2584: old_l_registration_no1 := l_registration_no;
2585: elsif l_value > nvl(old_l_value2,0) then
2586: old_l_value2 := l_value;
2591:
2592: end loop;
2593: close c_reg_balance_feed_info;
2594: if old_l_value > old_l_value1 then
2595: hr_utility.trace('in reg4');
2596: if old_l_value> old_l_value2 then
2597: arch_l_registration_no := old_l_registration_no;
2598: arch_l_value := old_l_value;
2599: else
2632:
2633: if l_negative_balance_exists ='N' then
2634: -- l_negative_balance_exists condition added for bug 10420909
2635: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2636: hr_utility.trace('Start of Employer Level PP Reg no ');
2637:
2638: if old_l_registration_no is not null and old_l_value >0 then
2639: hr_utility.trace('in reg1 pay_action_information');
2640: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2635: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2636: hr_utility.trace('Start of Employer Level PP Reg no ');
2637:
2638: if old_l_registration_no is not null and old_l_value >0 then
2639: hr_utility.trace('in reg1 pay_action_information');
2640: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2641: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2642:
2643: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2636: hr_utility.trace('Start of Employer Level PP Reg no ');
2637:
2638: if old_l_registration_no is not null and old_l_value >0 then
2639: hr_utility.trace('in reg1 pay_action_information');
2640: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2641: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2642:
2643: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2644: l_payroll_action_id
2637:
2638: if old_l_registration_no is not null and old_l_value >0 then
2639: hr_utility.trace('in reg1 pay_action_information');
2640: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2641: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2642:
2643: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2644: l_payroll_action_id
2645: ,old_l_registration_no
2645: ,old_l_registration_no
2646: ,p_effective_date);
2647: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2648: if c_get_emplr_reg_no%FOUND then
2649: hr_utility.trace('in ln_emplr_regamt new = ' || to_char(ln_emplr_regamt));
2650:
2651: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2652:
2653: update pay_action_information
2660:
2661:
2662: else
2663:
2664: hr_utility.trace('in reg1 new insert pay_action_information');
2665: -- insert a new record into pay_action_information
2666:
2667: pay_action_information_api.create_action_information(
2668: p_action_information_id => l_action_information_id_1,
2709: close c_get_emplr_reg_no;
2710: end if;
2711:
2712: if old_l_registration_no1 is not null and old_l_value1 >0 then
2713: hr_utility.trace('in reg2 pay_action_information');
2714:
2715: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2716: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2717:
2711:
2712: if old_l_registration_no1 is not null and old_l_value1 >0 then
2713: hr_utility.trace('in reg2 pay_action_information');
2714:
2715: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2716: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2717:
2718: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2719: l_payroll_action_id
2712: if old_l_registration_no1 is not null and old_l_value1 >0 then
2713: hr_utility.trace('in reg2 pay_action_information');
2714:
2715: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
2716: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
2717:
2718: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2719: l_payroll_action_id
2720: , old_l_registration_no1
2721: ,p_effective_date);
2722: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
2723: if c_get_emplr_reg_no%FOUND then
2724:
2725: hr_utility.trace('in ln_emplr_regamt1 new = ' || to_char(ln_emplr_regamt1));
2726:
2727: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
2728:
2729: update pay_action_information
2734: and action_information_category = 'CAEOY PENSION PLAN INFO'
2735: AND ACTION_INFORMATION4 = old_l_registration_no1;
2736:
2737: else
2738: hr_utility.trace('in reg2 new insert pay_action_information');
2739:
2740: -- insert a new record into pay_action_information
2741:
2742: pay_action_information_api.create_action_information(
2784: close c_get_emplr_reg_no;
2785: end if;
2786:
2787: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2788: hr_utility.trace('in reg3 pay_action_information');
2789: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2790: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2791: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2792: l_payroll_action_id
2785: end if;
2786:
2787: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2788: hr_utility.trace('in reg3 pay_action_information');
2789: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2790: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2791: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2792: l_payroll_action_id
2793: , old_l_registration_no2
2786:
2787: if old_l_registration_no2 is not null and old_l_value2 > 0 then
2788: hr_utility.trace('in reg3 pay_action_information');
2789: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
2790: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
2791: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2792: l_payroll_action_id
2793: , old_l_registration_no2
2794: ,p_effective_date);
2794: ,p_effective_date);
2795: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
2796: if c_get_emplr_reg_no%FOUND then
2797:
2798: hr_utility.trace('in ln_emplr_regamt2 new = ' || to_char(ln_emplr_regamt2));
2799:
2800: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
2801:
2802: update pay_action_information
2806: and effective_date = p_effective_date
2807: and action_information_category = 'CAEOY PENSION PLAN INFO'
2808: AND ACTION_INFORMATION4 = old_l_registration_no2;
2809:
2810: hr_utility.trace('Updated pay_action_information');
2811:
2812: else
2813: hr_utility.trace('in reg3 new insert pay_action_information');
2814:
2809:
2810: hr_utility.trace('Updated pay_action_information');
2811:
2812: else
2813: hr_utility.trace('in reg3 new insert pay_action_information');
2814:
2815: -- insert a new record into pay_action_information
2816:
2817: pay_action_information_api.create_action_information(
2913: l_business_group_id,
2914: NULL );
2915:
2916:
2917: hr_utility.trace('l_value:'||to_char(l_value));
2918: if l_value is null then
2919: l_value := 0;
2920: end if;
2921:
2980:
2981:
2982: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2983:
2984: hr_utility.trace('Start of Employer Level PP Reg no ');
2985: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2986: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2987: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2988: l_payroll_action_id
2981:
2982: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2983:
2984: hr_utility.trace('Start of Employer Level PP Reg no ');
2985: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2986: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2987: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2988: l_payroll_action_id
2989: ,old_l_registration_no
2982: /* Bug fix#2696309, Employer level Pension Plan Register Number */
2983:
2984: hr_utility.trace('Start of Employer Level PP Reg no ');
2985: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
2986: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
2987: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
2988: l_payroll_action_id
2989: ,old_l_registration_no
2990: ,p_effective_date);
2989: ,old_l_registration_no
2990: ,p_effective_date);
2991: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
2992: if c_get_emplr_reg_no%FOUND then
2993: hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
2994: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
2995:
2996: update pay_action_information
2997: set action_information5 = to_char(ln_emplr_regamt)
3053:
3054:
3055: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3056:
3057: hr_utility.trace('Start of Employer Level PP Reg no ');
3058: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3059: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3060:
3061: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3054:
3055: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3056:
3057: hr_utility.trace('Start of Employer Level PP Reg no ');
3058: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3059: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3060:
3061: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3062: l_payroll_action_id
3055: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3056:
3057: hr_utility.trace('Start of Employer Level PP Reg no ');
3058: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
3059: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
3060:
3061: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3062: l_payroll_action_id
3063: ,old_l_registration_no1
3063: ,old_l_registration_no1
3064: ,p_effective_date);
3065: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
3066: if c_get_emplr_reg_no%FOUND then
3067: hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
3068: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
3069:
3070: update pay_action_information
3071: set action_information5 = to_char(ln_emplr_regamt1)
3129: if old_l_registration_no2 is not null and old_l_value2 > 0 then
3130:
3131: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3132:
3133: hr_utility.trace('Start of Employer Level PP Reg no ');
3134: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3135: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3136:
3137: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3130:
3131: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3132:
3133: hr_utility.trace('Start of Employer Level PP Reg no ');
3134: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3135: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3136:
3137: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3138: l_payroll_action_id
3131: /* Bug fix#2696309, Employer level Pension Plan Register Number */
3132:
3133: hr_utility.trace('Start of Employer Level PP Reg no ');
3134: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
3135: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
3136:
3137: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
3138: l_payroll_action_id
3139: ,old_l_registration_no2
3138: l_payroll_action_id
3139: ,old_l_registration_no2
3140: ,p_effective_date);
3141: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
3142: hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
3143: if c_get_emplr_reg_no%FOUND then
3144: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
3145:
3146: update pay_action_information
3211: from T4A_BOX34 */
3212: end;
3213: /* end registration number archiving */
3214: else
3215: hr_utility.trace('result is 0');
3216:
3217: end if;
3218:
3219: /* Archive the negative balance flag */
3229: ,p_context1 => l_tax_unit_id
3230: ,p_some_warning => l_some_warning
3231: );
3232:
3233: hr_utility.trace('end of eoy_archive_data_new_format');
3234: l_step := 37;
3235:
3236: end eoy_archive_data_new_format;
3237:
3551: pca.effective_end_date;
3552:
3553: begin
3554:
3555: -- hr_utility.trace_on('Y','ORACLEMM');
3556:
3557: l_count := 0;
3558: l_box38_footnote_code := '00';
3559: l_negative_balance_exists := 'N';
3557: l_count := 0;
3558: l_box38_footnote_code := '00';
3559: l_negative_balance_exists := 'N';
3560:
3561: hr_utility.set_location ('archive_data',1);
3562: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
3563:
3564:
3565: SELECT aa.assignment_id,
3558: l_box38_footnote_code := '00';
3559: l_negative_balance_exists := 'N';
3560:
3561: hr_utility.set_location ('archive_data',1);
3562: hr_utility.trace('getting assignment for asgactid'|| to_char(p_assactid));
3563:
3564:
3565: SELECT aa.assignment_id,
3566: pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
3591: where organization_id = l_tax_unit_id
3592: and org_information_context = 'Canada Employer Identification';
3593:
3594: l_step := 3;
3595: hr_utility.trace('eoy_archive_data archiving employer data');
3596: eoy_archive_gre_data(l_payroll_action_id,
3597: l_tax_unit_id,
3598: l_transmitter_gre_id);
3599:
3597: l_tax_unit_id,
3598: l_transmitter_gre_id);
3599:
3600: l_step := 4;
3601: hr_utility.trace('eoy_archive_data archived employer data');
3602: else
3603: g_archive_flag := 'Y';
3604: end if;
3605:
3612: else
3613: lv_footnote_element := 'T4A NonBox Footnotes';
3614: end if;
3615:
3616: hr_utility.trace('l_date_earned : '|| to_char(l_date_earned));
3617:
3618: /* YE-2001 change to avoid hr_ca_tax_units_v view */
3619: select business_group_id
3620: into l_business_group_id
3651: begin
3652: open c_get_latest_asg(lv_serial_number );
3653: fetch c_get_latest_asg into l_aaid;
3654: close c_get_latest_asg;
3655: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3656:
3657: exception
3658: when no_data_found then
3659: l_aaid := -9999;
3659: l_aaid := -9999;
3660: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
3661: ||to_char(l_person_id));
3662: end;
3663: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3664: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
3665: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
3666:
3667:
3660: raise_application_error(-20001,'Balance Assignment Action does not exist for : '
3661: ||to_char(l_person_id));
3662: end;
3663: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3664: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
3665: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
3666:
3667:
3668:
3661: ||to_char(l_person_id));
3662: end;
3663: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
3664: hr_utility.trace('l_tax_unit_id : ' || to_char(l_tax_unit_id));
3665: hr_utility.trace('l_asgid : ' || to_char(l_asgid));
3666:
3667:
3668:
3669: /* Assign values to the PL/SQL tables */
3722: where payroll_action_id = l_payroll_action_id;
3723: EXCEPTION
3724: WHEN OTHERS THEN
3725: lv_fapp_report_type :='T4A';
3726: hr_utility.trace('Report type not found for given Payroll_action ');
3727: END;
3728:
3729: hr_utility.trace('Fed Pre-Process Pactid :'||
3730: to_char(l_payroll_action_id));
3725: lv_fapp_report_type :='T4A';
3726: hr_utility.trace('Report type not found for given Payroll_action ');
3727: END;
3728:
3729: hr_utility.trace('Fed Pre-Process Pactid :'||
3730: to_char(l_payroll_action_id));
3731: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3732:
3733: if (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3727: END;
3728:
3729: hr_utility.trace('Fed Pre-Process Pactid :'||
3730: to_char(l_payroll_action_id));
3731: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3732:
3733: if (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3734:
3735: hr_utility.trace('Archiving BOX28 etc., for payroll_action_id and report_type:'||
3731: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3732:
3733: if (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3734:
3735: hr_utility.trace('Archiving BOX28 etc., for payroll_action_id and report_type:'||
3736: l_payroll_action_id||' and '||lv_fapp_report_type);
3737:
3738: l_count := l_count + 1;
3739: l_user_entity_name_tab(l_count) := 'CAEOY_T4A_BOX26_PER_GRE_YTD';
3792: <> 0) ) then
3793:
3794: earning_exists := 1;
3795:
3796: hr_utility.trace('starting loop for balances');
3797:
3798: for i in 1 .. l_count
3799: loop
3800: result := 0;
3812:
3813: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3814: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
3815:
3816: hr_utility.trace('i is ' ||to_char(i));
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3813: pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3814: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
3815:
3816: hr_utility.trace('i is ' ||to_char(i));
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3814: pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_aaid);
3815:
3816: hr_utility.trace('i is ' ||to_char(i));
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3822:
3815:
3816: hr_utility.trace('i is ' ||to_char(i));
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3822:
3823: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
3816: hr_utility.trace('i is ' ||to_char(i));
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3822:
3823: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
3824: ( l_balance_type_tab(i),
3817: hr_utility.trace('Balance type is ' ||l_balance_type_tab(i));
3818: hr_utility.trace('AAID is ' || to_char(l_aaid));
3819: hr_utility.trace('ASGID is ' || to_char(l_asgid));
3820: hr_utility.trace('Tax_unit_id is ' || to_char(l_tax_unit_id));
3821: hr_utility.trace('Business_group_id is ' || to_char(l_business_group_id));
3822:
3823: result := result + pay_ca_balance_pkg.call_ca_balance_get_value
3824: ( l_balance_type_tab(i),
3825: 'YTD' ,
3852: -- IF condition added by sneelapa, bug 10115617
3853: IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3854: if result <> 0 then
3855:
3856: /* hr_utility.trace_on('Y','T4AARCH'); */
3857: hr_utility.trace('Result is ' || to_char(result));
3858: /* Check the footnote amounts and codes and archive them */
3859: /* Check which elements has fed the balance and what are their
3860: footnotes and if the primary balance for that element is 0 or not */
3853: IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010) then
3854: if result <> 0 then
3855:
3856: /* hr_utility.trace_on('Y','T4AARCH'); */
3857: hr_utility.trace('Result is ' || to_char(result));
3858: /* Check the footnote amounts and codes and archive them */
3859: /* Check which elements has fed the balance and what are their
3860: footnotes and if the primary balance for that element is 0 or not */
3861:
3867: 'T4A_BOX28',
3868: 'T4A_BOX32',
3869: 'T4A_BOX40') then
3870: begin
3871: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3868: 'T4A_BOX32',
3869: 'T4A_BOX40') then
3870: begin
3871: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3869: 'T4A_BOX40') then
3870: begin
3871: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3870: begin
3871: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3871: hr_utility.trace('Footnote Archiving Start for Asg_act_id: '||to_char(p_assactid));
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3879:
3872: hr_utility.trace('balance_type - values before c_balance_feed_info'||l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3879:
3880: open c_balance_feed_info(l_balance_type_tab(i));
3873: hr_utility.trace('l_box38_footnote_code: '||l_box38_footnote_code);
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3879:
3880: open c_balance_feed_info(l_balance_type_tab(i));
3881:
3874: hr_utility.trace('l_footnote_code : '||l_footnote_code);
3875: hr_utility.trace('l_no_of_fn_codes :'||l_no_of_fn_codes);
3876: hr_utility.trace('l_footnote_amount :'||to_char(l_footnote_amount));
3877: hr_utility.trace('old_l_footnote_code :'||old_l_footnote_code);
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3879:
3880: open c_balance_feed_info(l_balance_type_tab(i));
3881:
3882: hr_utility.trace('balance_type '||l_balance_type_tab(i));
3878: hr_utility.trace('l_box38_count :'||l_box38_count);
3879:
3880: open c_balance_feed_info(l_balance_type_tab(i));
3881:
3882: hr_utility.trace('balance_type '||l_balance_type_tab(i));
3883:
3884: loop
3885:
3886: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
3882: hr_utility.trace('balance_type '||l_balance_type_tab(i));
3883:
3884: loop
3885:
3886: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
3887: hr_utility.trace('666 l_count '|| l_count);
3888: hr_utility.trace('666 p_assactid '|| p_assactid);
3889: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
3890: if l_balance_type_tab(i) = 'T4A_BOX24'
3883:
3884: loop
3885:
3886: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
3887: hr_utility.trace('666 l_count '|| l_count);
3888: hr_utility.trace('666 p_assactid '|| p_assactid);
3889: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
3890: if l_balance_type_tab(i) = 'T4A_BOX24'
3891: and l_footnote_code = '10(BOX24)' then
3884: loop
3885:
3886: hr_utility.trace('begin of loop c_balance_feed_info '|| l_count);
3887: hr_utility.trace('666 l_count '|| l_count);
3888: hr_utility.trace('666 p_assactid '|| p_assactid);
3889: fetch c_balance_feed_info into l_footnote_code, l_footnote_balance;
3890: if l_balance_type_tab(i) = 'T4A_BOX24'
3891: and l_footnote_code = '10(BOX24)' then
3892: l_footnote_code := '10A';
3894:
3895: exit when c_balance_feed_info%NOTFOUND;
3896: l_count := l_count + 1;
3897:
3898: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
3899: hr_utility.trace('l_footnotecode '||l_footnote_code);
3900: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
3901: if l_footnote_code <> old_l_footnote_code then /* footnote
3902: changed */
3895: exit when c_balance_feed_info%NOTFOUND;
3896: l_count := l_count + 1;
3897:
3898: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
3899: hr_utility.trace('l_footnotecode '||l_footnote_code);
3900: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
3901: if l_footnote_code <> old_l_footnote_code then /* footnote
3902: changed */
3903: if old_l_footnote_code is not null then /* not the first
3896: l_count := l_count + 1;
3897:
3898: hr_utility.trace('l_footnote_balance '||l_footnote_balance);
3899: hr_utility.trace('l_footnotecode '||l_footnote_code);
3900: hr_utility.trace('old_l_footnotecode '||old_l_footnote_code);
3901: if l_footnote_code <> old_l_footnote_code then /* footnote
3902: changed */
3903: if old_l_footnote_code is not null then /* not the first
3904: record */
3902: changed */
3903: if old_l_footnote_code is not null then /* not the first
3904: record */
3905:
3906: hr_utility.trace('archive ft_amount_ue'
3907: ||l_footnote_amount_ue);
3908: hr_utility.trace('archive ft_amount'
3909: ||to_char(l_footnote_amount));
3910: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3904: record */
3905:
3906: hr_utility.trace('archive ft_amount_ue'
3907: ||l_footnote_amount_ue);
3908: hr_utility.trace('archive ft_amount'
3909: ||to_char(l_footnote_amount));
3910: if get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3911: and l_footnote_amount <> 0 then
3912:
3953: l_tax_unit_id,
3954: l_business_group_id,
3955: NULL );
3956:
3957: hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
3958: hr_utility.trace('666 l_value '|| l_value);
3959: l_footnote_amount := l_footnote_amount + l_value ;
3960:
3961: /* to fix bug#2426517 added one more validation to if stmt */
3954: l_business_group_id,
3955: NULL );
3956:
3957: hr_utility.trace('666 l_footnote_balance '|| l_footnote_balance);
3958: hr_utility.trace('666 l_value '|| l_value);
3959: l_footnote_amount := l_footnote_amount + l_value ;
3960:
3961: /* to fix bug#2426517 added one more validation to if stmt */
3962: /* if (l_value <> 0 and
3965: l_no_of_fn_codes := l_no_of_fn_codes + 1;
3966: l_box38_count := l_box38_count + 1;
3967: l_single_footnote_code := l_footnote_code;
3968:
3969: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
3970: hr_utility.trace('chk l_box38_count '|| l_box38_count);
3971: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
3972: end if;
3973: */
3966: l_box38_count := l_box38_count + 1;
3967: l_single_footnote_code := l_footnote_code;
3968:
3969: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
3970: hr_utility.trace('chk l_box38_count '|| l_box38_count);
3971: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
3972: end if;
3973: */
3974: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
3967: l_single_footnote_code := l_footnote_code;
3968:
3969: hr_utility.trace('chk l_no_of_fn_codes '|| l_no_of_fn_codes);
3970: hr_utility.trace('chk l_box38_count '|| l_box38_count);
3971: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
3972: end if;
3973: */
3974: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
3975: end loop;
3970: hr_utility.trace('chk l_box38_count '|| l_box38_count);
3971: hr_utility.trace('chk l_single_footnote_code '|| l_single_footnote_code);
3972: end if;
3973: */
3974: hr_utility.trace('end of loop record over for balance: '|| l_balance_type_tab(i));
3975: end loop;
3976: close c_balance_feed_info;
3977:
3978: if l_footnote_code is not null and
3979: l_footnote_amount_ue is not null and
3980: l_footnote_amount <> 0 and
3981: get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3982: then
3983: hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
3984: hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
3985:
3986: ff_archive_api.create_archive_item(
3987: p_archive_item_id => l_archive_item_id
3980: l_footnote_amount <> 0 and
3981: get_footnote_user_entity_id(l_footnote_amount_ue) <> 0
3982: then
3983: hr_utility.trace('666archive footnote amount '|| l_footnote_amount);
3984: hr_utility.trace('666archive footnote amount ue'|| l_footnote_amount_ue);
3985:
3986: ff_archive_api.create_archive_item(
3987: p_archive_item_id => l_archive_item_id
3988: ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_amount_ue)
4005: end if;
4006:
4007: end if;
4008:
4009: hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
4010: if l_no_of_fn_codes > 1 then
4011: l_footnote_code := '13';
4012: hr_utility.trace('666archive footnote code '|| l_footnote_code);
4013: /* changed here as part of bugfix#2426517 */
4008:
4009: hr_utility.trace('666archive l_no_of_fn_codes '|| l_no_of_fn_codes);
4010: if l_no_of_fn_codes > 1 then
4011: l_footnote_code := '13';
4012: hr_utility.trace('666archive footnote code '|| l_footnote_code);
4013: /* changed here as part of bugfix#2426517 */
4014: elsif l_no_of_fn_codes = 1 then
4015: l_footnote_code := l_single_footnote_code;
4016: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
4012: hr_utility.trace('666archive footnote code '|| l_footnote_code);
4013: /* changed here as part of bugfix#2426517 */
4014: elsif l_no_of_fn_codes = 1 then
4015: l_footnote_code := l_single_footnote_code;
4016: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
4017: elsif l_no_of_fn_codes = 0 then
4018: l_footnote_code := '00';
4019: hr_utility.trace('666archive footnote code '|| l_footnote_code);
4020: end if;
4015: l_footnote_code := l_single_footnote_code;
4016: hr_utility.trace('666archive footnote code '|| l_single_footnote_code);
4017: elsif l_no_of_fn_codes = 0 then
4018: l_footnote_code := '00';
4019: hr_utility.trace('666archive footnote code '|| l_footnote_code);
4020: end if;
4021:
4022: l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
4023:
4020: end if;
4021:
4022: l_footnote_code_ue := 'CAEOY_' || l_balance_type_tab(i) || '_FOOTNOTE_CODE';
4023:
4024: hr_utility.trace('before archiving l_footnote_code_ue is '|| l_footnote_code_ue);
4025: /* Part of fix for bug#2426517, to avoid unnecessary archiving
4026: of footnote code added one more condiftion to if stmt before
4027: archiving the footnote code for the corresponding BOX balance */
4028:
4028:
4029: if l_footnote_code is not null and l_no_of_fn_codes > 0 and
4030: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
4031:
4032: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
4033: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
4034: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
4035: ff_archive_api.create_archive_item(
4036: -- p_validate => 'TRUE'
4029: if l_footnote_code is not null and l_no_of_fn_codes > 0 and
4030: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
4031:
4032: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
4033: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
4034: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
4035: ff_archive_api.create_archive_item(
4036: -- p_validate => 'TRUE'
4037: p_archive_item_id => l_archive_item_id
4030: get_footnote_user_entity_id(l_footnote_code_ue) <> 0 then
4031:
4032: hr_utility.trace('l_footnote_code_ue:'|| l_footnote_code_ue);
4033: hr_utility.trace('l_footnote_code:'|| l_footnote_code);
4034: hr_utility.trace('l_single_footnote_code:'|| l_single_footnote_code);
4035: ff_archive_api.create_archive_item(
4036: -- p_validate => 'TRUE'
4037: p_archive_item_id => l_archive_item_id
4038: ,p_user_entity_id => get_footnote_user_entity_id(l_footnote_code_ue)
4048: end if;
4049:
4050: /* assigning value to box38_footnote_code */
4051:
4052: hr_utility.trace('999 l_box38_count '|| l_box38_count);
4053: /* initialised l_box38_footnote_code before checking
4054: gross earnings to this assignment action fix#2426517 */
4055:
4056: if l_box38_count > 1 then
4054: gross earnings to this assignment action fix#2426517 */
4055:
4056: if l_box38_count > 1 then
4057: l_box38_footnote_code := '13';
4058: hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
4059: /* Added one more condition to archive correct footnote code
4060: value for box38 as part of bug fix#2426517 and assigned
4061: l_single_footnote_code to l_box38_footnote_code variable */
4062: elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
4060: value for box38 as part of bug fix#2426517 and assigned
4061: l_single_footnote_code to l_box38_footnote_code variable */
4062: elsif l_box38_count = 1 and l_no_of_fn_codes > 0 then
4063: l_box38_footnote_code := l_single_footnote_code;
4064: hr_utility.trace('666 l_box38_footnote_code '||l_box38_footnote_code);
4065: end if;
4066:
4067: end;
4068: end if;
4070: end if; -- (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010)
4071:
4072: /** End of Footnote archiving **/
4073:
4074: --hr_utility.trace_off;
4075:
4076: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4077: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
4078: hr_utility.trace('Result is ' || to_char(result));
4072: /** End of Footnote archiving **/
4073:
4074: --hr_utility.trace_off;
4075:
4076: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4077: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
4078: hr_utility.trace('Result is ' || to_char(result));
4079:
4080: /* Added this condition to fix bug#2598777 */
4073:
4074: --hr_utility.trace_off;
4075:
4076: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4077: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
4078: hr_utility.trace('Result is ' || to_char(result));
4079:
4080: /* Added this condition to fix bug#2598777 */
4081: if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
4074: --hr_utility.trace_off;
4075:
4076: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4077: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
4078: hr_utility.trace('Result is ' || to_char(result));
4079:
4080: /* Added this condition to fix bug#2598777 */
4081: if l_user_entity_name_tab(i) = 'CAEOY_T4A_BOX34_PER_GRE_YTD' then
4082: result := round(result);
4108:
4109: end loop; /* for archiving all T4A Balances */
4110:
4111: /** Start box38 footnote archiving **/
4112: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4113: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
4114: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
4115:
4116: /** box38 footnote archive has been moved after nonbox footnote archive **/
4109: end loop; /* for archiving all T4A Balances */
4110:
4111: /** Start box38 footnote archiving **/
4112: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4113: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
4114: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
4115:
4116: /** box38 footnote archive has been moved after nonbox footnote archive **/
4117:
4110:
4111: /** Start box38 footnote archiving **/
4112: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
4113: hr_utility.trace('Archiving CAEOY_T4A_FOOTNOTE_CODE ');
4114: hr_utility.trace('l_box38_footnote_code '||l_box38_footnote_code);
4115:
4116: /** box38 footnote archive has been moved after nonbox footnote archive **/
4117:
4118: -- IF condition added by sneelapa, bug 10099479
4140: fetch c_reg_balance_feed_info into l_registration_no,l_balance_name,
4141: l_element_type_id,l_ele_classification_id;
4142: exit when c_reg_balance_feed_info%NOTFOUND;
4143:
4144: hr_utility.trace('checking for T4A_BOX34');
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4141: l_element_type_id,l_ele_classification_id;
4142: exit when c_reg_balance_feed_info%NOTFOUND;
4143:
4144: hr_utility.trace('checking for T4A_BOX34');
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4142: exit when c_reg_balance_feed_info%NOTFOUND;
4143:
4144: hr_utility.trace('checking for T4A_BOX34');
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4143:
4144: hr_utility.trace('checking for T4A_BOX34');
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4144: hr_utility.trace('checking for T4A_BOX34');
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4152:
4145: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4152:
4153: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4146: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4152:
4153: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4154: ( l_balance_name,
4147: hr_utility.trace('l_registration_no:'||l_registration_no);
4148: hr_utility.trace('l_balance_name:'||l_balance_name);
4149: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4150: hr_utility.trace('before c_ele_processed cur l_check_flag:'||l_check_flag);
4151: hr_utility.trace('l_ele_classification_id:'||to_char(l_ele_classification_id));
4152:
4153: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4154: ( l_balance_name,
4155: 'YTD' ,
4160: l_tax_unit_id,
4161: l_business_group_id,
4162: NULL );
4163:
4164: hr_utility.trace('before check null l_value:'||to_char(l_value));
4165: if l_value is null then
4166: l_value := 0;
4167: end if;
4168:
4165: if l_value is null then
4166: l_value := 0;
4167: end if;
4168:
4169: hr_utility.trace('after check null l_value:'||to_char(l_value));
4170:
4171:
4172: /* Condition to check the amounts and determine the registration
4173: number to archive Bug fix 2408456 */
4171:
4172: /* Condition to check the amounts and determine the registration
4173: number to archive Bug fix 2408456 */
4174: if old_l_value = 0 then
4175: hr_utility.trace('in reg1');
4176: old_l_value := l_value;
4177: old_l_registration_no := l_registration_no;
4178: elsif old_l_value1 = 0 then
4179: hr_utility.trace('in reg2');
4175: hr_utility.trace('in reg1');
4176: old_l_value := l_value;
4177: old_l_registration_no := l_registration_no;
4178: elsif old_l_value1 = 0 then
4179: hr_utility.trace('in reg2');
4180: old_l_value1 := l_value;
4181: old_l_registration_no1 := l_registration_no;
4182: elsif old_l_value2 = 0 then
4183: hr_utility.trace('in reg3');
4179: hr_utility.trace('in reg2');
4180: old_l_value1 := l_value;
4181: old_l_registration_no1 := l_registration_no;
4182: elsif old_l_value2 = 0 then
4183: hr_utility.trace('in reg3');
4184: old_l_value2 := l_value;
4185: old_l_registration_no2 := l_registration_no;
4186: else
4187: if l_value > nvl(old_l_value,0) then
4184: old_l_value2 := l_value;
4185: old_l_registration_no2 := l_registration_no;
4186: else
4187: if l_value > nvl(old_l_value,0) then
4188: hr_utility.trace('old_l1');
4189: old_l_value := l_value;
4190: old_l_registration_no := l_registration_no;
4191: elsif l_value > nvl(old_l_value1,0) then
4192: hr_utility.trace('old_2');
4188: hr_utility.trace('old_l1');
4189: old_l_value := l_value;
4190: old_l_registration_no := l_registration_no;
4191: elsif l_value > nvl(old_l_value1,0) then
4192: hr_utility.trace('old_2');
4193: old_l_value1 := l_value;
4194: old_l_registration_no1 := l_registration_no;
4195: elsif l_value > nvl(old_l_value2,0) then
4196: old_l_value2 := l_value;
4201:
4202: end loop;
4203: close c_reg_balance_feed_info;
4204: if old_l_value > old_l_value1 then
4205: hr_utility.trace('in reg4');
4206: if old_l_value> old_l_value2 then
4207: arch_l_registration_no := old_l_registration_no;
4208: arch_l_value := old_l_value;
4209: else
4242:
4243: -- Added l_negative_balance_exists ='N' condition for bug 10420909
4244: if l_negative_balance_exists ='N' then
4245:
4246: hr_utility.trace('Start of Employer Level PP Reg no ');
4247:
4248: if old_l_registration_no is not null and old_l_value >0 then
4249: hr_utility.trace('in reg1 pay_action_information');
4250: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4245:
4246: hr_utility.trace('Start of Employer Level PP Reg no ');
4247:
4248: if old_l_registration_no is not null and old_l_value >0 then
4249: hr_utility.trace('in reg1 pay_action_information');
4250: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4251: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4252:
4253: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4246: hr_utility.trace('Start of Employer Level PP Reg no ');
4247:
4248: if old_l_registration_no is not null and old_l_value >0 then
4249: hr_utility.trace('in reg1 pay_action_information');
4250: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4251: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4252:
4253: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4254: l_payroll_action_id
4247:
4248: if old_l_registration_no is not null and old_l_value >0 then
4249: hr_utility.trace('in reg1 pay_action_information');
4250: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4251: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4252:
4253: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4254: l_payroll_action_id
4255: ,old_l_registration_no
4255: ,old_l_registration_no
4256: ,p_effective_date);
4257: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
4258: if c_get_emplr_reg_no%FOUND then
4259: hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
4260:
4261: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
4262:
4263: update pay_action_information
4270:
4271:
4272: else
4273:
4274: hr_utility.trace('in reg1 insert pay_action_information');
4275: -- insert a new record into pay_action_information
4276:
4277: pay_action_information_api.create_action_information(
4278: p_action_information_id => l_action_information_id_1,
4319: close c_get_emplr_reg_no;
4320: end if;
4321:
4322: if old_l_registration_no1 is not null and old_l_value1 >0 then
4323: hr_utility.trace('in reg2 pay_action_information');
4324:
4325: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4326: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4327:
4321:
4322: if old_l_registration_no1 is not null and old_l_value1 >0 then
4323: hr_utility.trace('in reg2 pay_action_information');
4324:
4325: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4326: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4327:
4328: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4329: l_payroll_action_id
4322: if old_l_registration_no1 is not null and old_l_value1 >0 then
4323: hr_utility.trace('in reg2 pay_action_information');
4324:
4325: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4326: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4327:
4328: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4329: l_payroll_action_id
4330: , old_l_registration_no1
4331: ,p_effective_date);
4332: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
4333: if c_get_emplr_reg_no%FOUND then
4334:
4335: hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
4336:
4337: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
4338:
4339: update pay_action_information
4344: and action_information_category = 'CAEOY PENSION PLAN INFO'
4345: AND ACTION_INFORMATION4 = old_l_registration_no1;
4346:
4347: else
4348: hr_utility.trace('in reg2 insert pay_action_information');
4349:
4350: -- insert a new record into pay_action_information
4351:
4352: pay_action_information_api.create_action_information(
4394: close c_get_emplr_reg_no;
4395: end if;
4396:
4397: if old_l_registration_no2 is not null and old_l_value2 > 0 then
4398: hr_utility.trace('in reg3 pay_action_information');
4399: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4400: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4401: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4402: l_payroll_action_id
4395: end if;
4396:
4397: if old_l_registration_no2 is not null and old_l_value2 > 0 then
4398: hr_utility.trace('in reg3 pay_action_information');
4399: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4400: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4401: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4402: l_payroll_action_id
4403: , old_l_registration_no2
4396:
4397: if old_l_registration_no2 is not null and old_l_value2 > 0 then
4398: hr_utility.trace('in reg3 pay_action_information');
4399: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4400: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4401: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4402: l_payroll_action_id
4403: , old_l_registration_no2
4404: ,p_effective_date);
4404: ,p_effective_date);
4405: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
4406: if c_get_emplr_reg_no%FOUND then
4407:
4408: hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
4409:
4410: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
4411:
4412: update pay_action_information
4416: and effective_date = p_effective_date
4417: and action_information_category = 'CAEOY PENSION PLAN INFO'
4418: AND ACTION_INFORMATION4 = old_l_registration_no2;
4419:
4420: hr_utility.trace('Updated pay_action_information');
4421:
4422: else
4423: hr_utility.trace('in reg3 insert pay_action_information');
4424:
4419:
4420: hr_utility.trace('Updated pay_action_information');
4421:
4422: else
4423: hr_utility.trace('in reg3 insert pay_action_information');
4424:
4425: -- insert a new record into pay_action_information
4426:
4427: pay_action_information_api.create_action_information(
4511: l_balance_name,l_element_type_id,
4512: l_ele_classification_id;
4513: exit when c_reg_balance_feed_info%NOTFOUND;
4514:
4515: hr_utility.trace('checking for T4A_BOX32 ');
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4512: l_ele_classification_id;
4513: exit when c_reg_balance_feed_info%NOTFOUND;
4514:
4515: hr_utility.trace('checking for T4A_BOX32 ');
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4520: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4513: exit when c_reg_balance_feed_info%NOTFOUND;
4514:
4515: hr_utility.trace('checking for T4A_BOX32 ');
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4520: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4521:
4514:
4515: hr_utility.trace('checking for T4A_BOX32 ');
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4520: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4521:
4522: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4515: hr_utility.trace('checking for T4A_BOX32 ');
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4520: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4521:
4522: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4523: ( l_balance_name,
4516: hr_utility.trace('p_assactid:'||to_char(p_assactid));
4517: hr_utility.trace('l_asgid:'||to_char(l_asgid));
4518: hr_utility.trace('l_registration_no:'||l_registration_no);
4519: hr_utility.trace('l_balance_name:'||l_balance_name);
4520: hr_utility.trace('l_element_type_id:'||to_char(l_element_type_id));
4521:
4522: l_value := pay_ca_balance_pkg.call_ca_balance_get_value
4523: ( l_balance_name,
4524: 'YTD' ,
4530: l_business_group_id,
4531: NULL );
4532:
4533:
4534: hr_utility.trace('l_value:'||to_char(l_value));
4535: if l_value is null then
4536: l_value := 0;
4537: end if;
4538:
4535: if l_value is null then
4536: l_value := 0;
4537: end if;
4538:
4539: hr_utility.trace('before checking the new validation ');
4540: hr_utility.trace('l_value :'||to_char(l_value));
4541: hr_utility.trace('l_registration_no:'||l_registration_no);
4542: hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4536: l_value := 0;
4537: end if;
4538:
4539: hr_utility.trace('before checking the new validation ');
4540: hr_utility.trace('l_value :'||to_char(l_value));
4541: hr_utility.trace('l_registration_no:'||l_registration_no);
4542: hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4544:
4537: end if;
4538:
4539: hr_utility.trace('before checking the new validation ');
4540: hr_utility.trace('l_value :'||to_char(l_value));
4541: hr_utility.trace('l_registration_no:'||l_registration_no);
4542: hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4544:
4545: /* Condition to check the amounts and determine the
4538:
4539: hr_utility.trace('before checking the new validation ');
4540: hr_utility.trace('l_value :'||to_char(l_value));
4541: hr_utility.trace('l_registration_no:'||l_registration_no);
4542: hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4544:
4545: /* Condition to check the amounts and determine the
4546: registration number to archive Bug fix 2408456 */
4539: hr_utility.trace('before checking the new validation ');
4540: hr_utility.trace('l_value :'||to_char(l_value));
4541: hr_utility.trace('l_registration_no:'||l_registration_no);
4542: hr_utility.trace('old_l_value :'||to_char(old_l_value));
4543: hr_utility.trace('old_l_registration_no:'||old_l_registration_no);
4544:
4545: /* Condition to check the amounts and determine the
4546: registration number to archive Bug fix 2408456 */
4547:
4602:
4603:
4604: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4605:
4606: hr_utility.trace('Start of Employer Level PP Reg no ');
4607: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4608: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4609: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4610: l_payroll_action_id
4603:
4604: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4605:
4606: hr_utility.trace('Start of Employer Level PP Reg no ');
4607: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4608: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4609: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4610: l_payroll_action_id
4611: ,old_l_registration_no
4604: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4605:
4606: hr_utility.trace('Start of Employer Level PP Reg no ');
4607: hr_utility.trace('in old_l_value = ' || to_char(old_l_value));
4608: hr_utility.trace('in old_l_reg = ' || old_l_registration_no);
4609: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4610: l_payroll_action_id
4611: ,old_l_registration_no
4612: ,p_effective_date);
4611: ,old_l_registration_no
4612: ,p_effective_date);
4613: fetch c_get_emplr_reg_no into lv_emplr_regno,ln_emplr_regamt;
4614: if c_get_emplr_reg_no%FOUND then
4615: hr_utility.trace('in ln_emplr_regamt = ' || to_char(ln_emplr_regamt));
4616: ln_emplr_regamt := ln_emplr_regamt + old_l_value;
4617:
4618: update pay_action_information
4619: set action_information5 = to_char(ln_emplr_regamt)
4675:
4676:
4677: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4678:
4679: hr_utility.trace('Start of Employer Level PP Reg no ');
4680: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4681: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4682:
4683: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4676:
4677: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4678:
4679: hr_utility.trace('Start of Employer Level PP Reg no ');
4680: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4681: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4682:
4683: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4684: l_payroll_action_id
4677: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4678:
4679: hr_utility.trace('Start of Employer Level PP Reg no ');
4680: hr_utility.trace('in old_l_value1 = ' || to_char(old_l_value1));
4681: hr_utility.trace('in old_l_reg1 = ' || old_l_registration_no1);
4682:
4683: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4684: l_payroll_action_id
4685: ,old_l_registration_no1
4685: ,old_l_registration_no1
4686: ,p_effective_date);
4687: fetch c_get_emplr_reg_no into lv_emplr_regno1,ln_emplr_regamt1;
4688: if c_get_emplr_reg_no%FOUND then
4689: hr_utility.trace('in ln_emplr_regamt1 = ' || to_char(ln_emplr_regamt1));
4690: ln_emplr_regamt1 := ln_emplr_regamt1 + old_l_value1;
4691:
4692: update pay_action_information
4693: set action_information5 = to_char(ln_emplr_regamt1)
4751: if old_l_registration_no2 is not null and old_l_value2 > 0 then
4752:
4753: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4754:
4755: hr_utility.trace('Start of Employer Level PP Reg no ');
4756: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4757: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4758:
4759: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4752:
4753: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4754:
4755: hr_utility.trace('Start of Employer Level PP Reg no ');
4756: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4757: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4758:
4759: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4760: l_payroll_action_id
4753: /* Bug fix#2696309, Employer level Pension Plan Register Number */
4754:
4755: hr_utility.trace('Start of Employer Level PP Reg no ');
4756: hr_utility.trace('in old_l_value2 = ' || to_char(old_l_value2));
4757: hr_utility.trace('in old_l_reg2 = ' || old_l_registration_no2);
4758:
4759: open c_get_emplr_reg_no(to_char(l_tax_unit_id),
4760: l_payroll_action_id
4761: ,old_l_registration_no2
4760: l_payroll_action_id
4761: ,old_l_registration_no2
4762: ,p_effective_date);
4763: fetch c_get_emplr_reg_no into lv_emplr_regno2,ln_emplr_regamt2;
4764: hr_utility.trace('in ln_emplr_regamt2 = ' || to_char(ln_emplr_regamt2));
4765: if c_get_emplr_reg_no%FOUND then
4766: ln_emplr_regamt2 := ln_emplr_regamt2 + old_l_value2;
4767:
4768: update pay_action_information
4836: /* end registration number archiving */
4837: end if; -- IF (lv_fapp_report_type = 'T4A' and to_number(to_char(l_year_end,'YYYY')) < 2010)
4838: -- IF condition added by sneelapa, bug 10099479
4839: else
4840: hr_utility.trace('result is 0');
4841:
4842: end if; /* end if for result <> 0 condition */
4843:
4844: /* Need to add the T4A Nonbox Footnote archiving code to fix bug#2175045 */
4858: if cur_non_box_mesg%notfound then
4859: exit;
4860: end if;
4861:
4862: hr_utility.trace('l_messages - '||l_messages);
4863: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4864:
4865: /* If the same Non Box footnote is processed more than
4866: once during the year, then the sum of the associated
4859: exit;
4860: end if;
4861:
4862: hr_utility.trace('l_messages - '||l_messages);
4863: hr_utility.trace('l_mesg_amt - '||to_char(l_mesg_amt));
4864:
4865: /* If the same Non Box footnote is processed more than
4866: once during the year, then the sum of the associated
4867: amounts is archived */
4868:
4869: if ((l_messages <> l_prev_messages) and
4870: (l_prev_messages is not null)) then
4871:
4872: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4873:
4874: if l_total_mesg_amt <> 0 then
4875:
4876: pay_action_information_api.create_action_information(
4933: else
4934: l_total_mesg_amt := l_total_mesg_amt + l_mesg_amt;
4935: end if;
4936:
4937: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4938:
4939: l_prev_messages := l_messages;
4940: ln_prev_tax_unit_id := ln_tax_unit_id;
4941: ld_prev_eff_date := ld_eff_date;
4945: close cur_non_box_mesg;
4946:
4947: if (l_prev_messages is not null) then
4948:
4949: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4950: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4951:
4952: if l_total_mesg_amt <> 0 then
4953:
4946:
4947: if (l_prev_messages is not null) then
4948:
4949: hr_utility.trace('l_prev_messages - '||l_prev_messages);
4950: hr_utility.trace('l_total_mesg_amt - '||to_char(l_total_mesg_amt));
4951:
4952: if l_total_mesg_amt <> 0 then
4953:
4954: pay_action_information_api.create_action_information(
5048: /* End of t4a box 38 archive */
5049:
5050: begin
5051: l_counter := 0;
5052: hr_utility.trace('selecting people');
5053:
5054: select PEOPLE.person_id,
5055: PEOPLE.first_name,
5056: PEOPLE.last_name,
5090: l_work_telephone := null;
5091: l_national_identifier := null;
5092: l_middle_names := null; /* changed variable l_pre_name_adjunct
5093: to l_middle_names */
5094: hr_utility.raise_error;
5095: end;
5096:
5097: begin
5098: select PHONE.phone_number
5109: when no_data_found then
5110: l_employee_phone_no := l_work_telephone;
5111: end;
5112:
5113: hr_utility.trace('selected people');
5114: /* Initialise l_count */
5115: l_count := 0;
5116:
5117: /* hr_utility.trace_on('Y','ORACLE'); */
5113: hr_utility.trace('selected people');
5114: /* Initialise l_count */
5115: l_count := 0;
5116:
5117: /* hr_utility.trace_on('Y','ORACLE'); */
5118:
5119: l_counter := l_counter + 1;
5120: l_user_entity_name_tab(l_counter) := 'CAEOY_PERSON_ID';
5121: l_user_entity_value_tab(l_counter) := l_person_id;
5169: end loop;
5170: end if;
5171:
5172: begin
5173: hr_utility.trace('selecting address');
5174:
5175: select addr.address_line1,
5176: addr.address_line2,
5177: addr.address_line3,
5208: l_telephone_number := null;
5209: l_country_code := null;
5210: end;
5211:
5212: hr_utility.trace('selected address');
5213:
5214: l_counter := 0;
5215: l_counter := l_counter + 1;
5216: l_user_entity_name_tab(l_counter) := 'CAEOY_EMPLOYEE_ADDRESS_LINE1';
5254:
5255: l_context_id := l_taxunit_context_id;
5256: l_context_val := l_tax_unit_id;
5257:
5258: hr_utility.trace('archiving address');
5259: ff_archive_api.create_archive_item(
5260: p_archive_item_id => l_archive_item_id
5261: ,p_user_entity_id => get_user_entity_id(l_user_entity_name_tab(i))
5262: ,p_archive_value => l_user_entity_value_tab(i)
5265: ,p_legislation_code => 'CA'
5266: ,p_object_version_number => l_object_version_number
5267: ,p_some_warning => l_some_warning
5268: );
5269: hr_utility.trace('archived address');
5270: end loop;
5271: end if;
5272: hr_utility.trace('end of eoy_archive_data');
5273: l_step := 37;
5268: );
5269: hr_utility.trace('archived address');
5270: end loop;
5271: end if;
5272: hr_utility.trace('end of eoy_archive_data');
5273: l_step := 37;
5274:
5275: -- Moidification for bug 6456662 starts here.
5276: /*
5284: or
5285: to_number(to_char(l_year_end,'YYYY')) >= 2010
5286: ) then
5287:
5288: hr_utility.trace('Calling new procedure for archiving Other Info Amts.');
5289:
5290: eoy_archive_data_new_format(p_assactid , p_effective_date,l_negative_balance_exists );
5291:
5292: ELSE
5314: -- Federal YE Amendment Pre-Process Validation (T4A Amendment Archiver code)
5315:
5316: Begin
5317:
5318: hr_utility.trace('Started Federal YE Amendment PP Validation ');
5319:
5320: -- This query is called above, before archiving T4A_BOX28 etc.,
5321:
5322: /*
5328: where payroll_action_id = l_payroll_action_id;
5329: */
5330:
5331:
5332: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
5333: to_char(l_payroll_action_id));
5334: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
5335:
5336: if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
5330:
5331:
5332: hr_utility.trace('Fed Amend Pre-Process Pactid :'||
5333: to_char(l_payroll_action_id));
5334: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
5335:
5336: if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
5337:
5338: hr_utility.trace('Archiving CAEOY_T4A_AMEND_PP for payroll_action_id and report_type:'||
5334: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
5335:
5336: if lv_fapp_report_type = 'CAEOY_T4A_AMEND_PP' then
5337:
5338: hr_utility.trace('Archiving CAEOY_T4A_AMEND_PP for payroll_action_id and report_type:'||
5339: l_payroll_action_id||' and '||lv_fapp_report_type);
5340:
5341: begin
5342:
5345: into ln_fapp_locked_action_id;
5346:
5347: close c_get_fapp_locked_action_id;
5348:
5349: hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
5350: hr_utility.trace('ln_fapp_locked_action_id :'||
5351: to_char(ln_fapp_locked_action_id));
5352:
5353: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5346:
5347: close c_get_fapp_locked_action_id;
5348:
5349: hr_utility.trace('T4A Amend PP Action ID : '||to_char(p_assactid));
5350: hr_utility.trace('ln_fapp_locked_action_id :'||
5351: to_char(ln_fapp_locked_action_id));
5352:
5353: open c_get_fapp_lkd_actid_rtype(ln_fapp_locked_action_id);
5354: fetch c_get_fapp_lkd_actid_rtype
5355: into lv_fapp_locked_actid_reptype;
5356:
5357: close c_get_fapp_lkd_actid_rtype;
5358:
5359: hr_utility.trace('lv_fapp_locked_actid_reptype :'||
5360: lv_fapp_locked_actid_reptype);
5361:
5362: lv_fapp_flag := compare_archive_data(p_assactid,
5363: ln_fapp_locked_action_id);
5363: ln_fapp_locked_action_id);
5364:
5365: if lv_fapp_flag = 'Y' then
5366:
5367: hr_utility.trace('Archiving T4A Amendment Flag is : ' || lv_fapp_flag);
5368:
5369: ff_archive_api.create_archive_item(
5370: p_archive_item_id => l_archive_item_id
5371: ,p_user_entity_id => get_user_entity_id('CAEOY_T4A_AMENDMENT_FLAG')
5383:
5384: end; -- report_type validation
5385:
5386: end if; -- report type validation for FAPP
5387: hr_utility.trace('End of Federal YE Amendment PP Validation');
5388:
5389: exception when no_data_found then
5390: hr_utility.trace('Report type not found for given Payroll_action ');
5391: null;
5386: end if; -- report type validation for FAPP
5387: hr_utility.trace('End of Federal YE Amendment PP Validation');
5388:
5389: exception when no_data_found then
5390: hr_utility.trace('Report type not found for given Payroll_action ');
5391: null;
5392: end;
5393:
5394: -- End of Federal YE Amendment Pre-Process Validation
5405: or
5406: to_number(to_char(l_year_end,'YYYY')) >= 2010
5407: ) then
5408:
5409: hr_utility.trace('Calling new procedure for archiving Other Info Amts.');
5410:
5411: eoy_archive_data_new_format(p_assactid , p_effective_date,l_negative_balance_exists );
5412:
5413: ELSE
5464: l_business_group
5465: from pay_payroll_actions
5466: where payroll_action_id = pactid;
5467:
5468: hr_utility.trace('legislative prameter is '|| l_legislative_parameters);
5469:
5470: l_eoy_tax_unit_id := pycadar_pkg.get_parameter('TRANSFER_GRE',l_legislative_parameters);
5471:
5472: select org_information11
5475: where organization_id = l_eoy_tax_unit_id
5476: and org_information_context = 'Canada Employer Identification'
5477: and org_information5 in ('T4A/RL1','T4A/RL2');
5478:
5479: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
5480: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
5481:
5482: if l_eoy_tax_unit_id <> -99999 then
5483:
5476: and org_information_context = 'Canada Employer Identification'
5477: and org_information5 in ('T4A/RL1','T4A/RL2');
5478:
5479: hr_utility.trace('Transfer GRE is '|| to_char(l_eoy_tax_unit_id));
5480: hr_utility.trace('Transmitter GRE is '|| to_char(l_transmitter_gre_id));
5481:
5482: if l_eoy_tax_unit_id <> -99999 then
5483:
5484: sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
5516:
5517: l_archive := chk_gre_archive(pactid);
5518:
5519: if g_archive_flag = 'N' then
5520: hr_utility.trace('eoy_range_cursor archiving employer data');
5521:
5522: -- now the archiver has provision for archiving payroll_action_level data .
5523: -- So make use of that
5524:
5521:
5522: -- now the archiver has provision for archiving payroll_action_level data .
5523: -- So make use of that
5524:
5525: hr_utility.trace('eoy_range_cursor archiving employer data');
5526:
5527: eoy_archive_gre_data(pactid,
5528: l_eoy_tax_unit_id,
5529: l_transmitter_gre_id);