[Home] [Help]
222:
223: if csr_bal_type_id%notfound then
224: close csr_bal_type_id;
225: /* need a pop-message */
226: hr_utility.trace('Balance name :'||p_balance_name||'doesnot exist');
227: raise hr_utility.hr_error;
228: else
229: close csr_bal_type_id;
230: end if;
223: if csr_bal_type_id%notfound then
224: close csr_bal_type_id;
225: /* need a pop-message */
226: hr_utility.trace('Balance name :'||p_balance_name||'doesnot exist');
227: raise hr_utility.hr_error;
228: else
229: close csr_bal_type_id;
230: end if;
231:
234: fetch csr_def_bal_id into l_defined_balance_id;
235: if csr_def_bal_id%notfound then
236: close csr_def_bal_id;
237: /* need a pop-message */
238: hr_utility.trace('Balance Dimension :'||p_balance_dimension||'doesnot exist');
239: raise hr_utility.hr_error;
240: else
241: close csr_def_bal_id;
242: end if;
235: if csr_def_bal_id%notfound then
236: close csr_def_bal_id;
237: /* need a pop-message */
238: hr_utility.trace('Balance Dimension :'||p_balance_dimension||'doesnot exist');
239: raise hr_utility.hr_error;
240: else
241: close csr_def_bal_id;
242: end if;
243:
348: if p_report_type = 'RL2' then
349:
350: /* Default settings for Year End Preprocess. */
351:
352: hr_utility.trace('in getting selection information ');
353: p_period_start := p_year_start;
354: p_period_end := p_year_end;
355: p_defined_balance_id := 0;
356: p_group_by_gre := FALSE;
362:
363: /* An invalid report type has been passed so fail. */
364:
365: else
366: hr_utility.trace('in error of getting selection information ');
367:
368: raise hr_utility.hr_error;
369:
370: end if;
364:
365: else
366: hr_utility.trace('in error of getting selection information ');
367:
368: raise hr_utility.hr_error;
369:
370: end if;
371:
372: end get_selection_information;
393: and lookup_code = p_footnote_code))
394: );
395: begin
396:
397: hr_utility.trace('chk_rl2_footnote - checking footnote exists');
398: hr_utility.trace('c_chk_footnote - opening cursor');
399:
400: open c_chk_footnote;
401: fetch c_chk_footnote into l_flag;
394: );
395: begin
396:
397: hr_utility.trace('chk_rl2_footnote - checking footnote exists');
398: hr_utility.trace('c_chk_footnote - opening cursor');
399:
400: open c_chk_footnote;
401: fetch c_chk_footnote into l_flag;
402: if c_chk_footnote%FOUND then
399:
400: open c_chk_footnote;
401: fetch c_chk_footnote into l_flag;
402: if c_chk_footnote%FOUND then
403: hr_utility.trace('c_chk_footnote - found in cursor');
404: l_flag := 'Y';
405: else
406: hr_utility.trace('c_chk_footnote - not found in cursor');
407: l_flag := 'N';
402: if c_chk_footnote%FOUND then
403: hr_utility.trace('c_chk_footnote - found in cursor');
404: l_flag := 'Y';
405: else
406: hr_utility.trace('c_chk_footnote - not found in cursor');
407: l_flag := 'N';
408: end if;
409:
410: hr_utility.trace('c_chk_footnote - closing cursor');
406: hr_utility.trace('c_chk_footnote - not found in cursor');
407: l_flag := 'N';
408: end if;
409:
410: hr_utility.trace('c_chk_footnote - closing cursor');
411: close c_chk_footnote;
412:
413: if l_flag = 'Y' then
414: hr_utility.trace('chk_rl2_footnote - returning true');
410: hr_utility.trace('c_chk_footnote - closing cursor');
411: close c_chk_footnote;
412:
413: if l_flag = 'Y' then
414: hr_utility.trace('chk_rl2_footnote - returning true');
415: return (TRUE);
416: else
417: hr_utility.trace('chk_rl2_footnote - returning false');
418: return(FALSE);
413: if l_flag = 'Y' then
414: hr_utility.trace('chk_rl2_footnote - returning true');
415: return (TRUE);
416: else
417: hr_utility.trace('chk_rl2_footnote - returning false');
418: return(FALSE);
419: end if;
420:
421: end chk_rl2_footnote;
435: BEGIN
436:
437: If p_data = 'EMPLOYEE_DATA' then
438:
439: hr_utility.trace('deleting plsql table'|| p_data);
440:
441: if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count > 0 then
442: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.delete;
443: end if;
453: End if;
454:
455: If p_data = 'PRE_DATA' then
456:
457: hr_utility.trace('deleting plsql table'|| p_data);
458:
459: if pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.count > 0 then
460: pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.delete;
461: end if;
493: BEGIN
494:
495: if p_tab_rec_data.count > 0 then
496: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
497: hr_utility.trace('Defining category '||
498: p_tab_rec_data(i).action_info_category);
499: hr_utility.trace('action_context_id = '|| p_action_context_id);
500: hr_utility.trace('jurisdiction_code '||
501: p_tab_rec_data(i).jurisdiction_code);
495: if p_tab_rec_data.count > 0 then
496: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
497: hr_utility.trace('Defining category '||
498: p_tab_rec_data(i).action_info_category);
499: hr_utility.trace('action_context_id = '|| p_action_context_id);
500: hr_utility.trace('jurisdiction_code '||
501: p_tab_rec_data(i).jurisdiction_code);
502: hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503:
496: for i in p_tab_rec_data.first .. p_tab_rec_data.last loop
497: hr_utility.trace('Defining category '||
498: p_tab_rec_data(i).action_info_category);
499: hr_utility.trace('action_context_id = '|| p_action_context_id);
500: hr_utility.trace('jurisdiction_code '||
501: p_tab_rec_data(i).jurisdiction_code);
502: hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503:
504: hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
498: p_tab_rec_data(i).action_info_category);
499: hr_utility.trace('action_context_id = '|| p_action_context_id);
500: hr_utility.trace('jurisdiction_code '||
501: p_tab_rec_data(i).jurisdiction_code);
502: hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503:
504: hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
505:
506: hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
500: hr_utility.trace('jurisdiction_code '||
501: p_tab_rec_data(i).jurisdiction_code);
502: hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503:
504: hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
505:
506: hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
507:
508: hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
502: hr_utility.trace('act_info1 is'|| p_tab_rec_data(i).act_info1);
503:
504: hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
505:
506: hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
507:
508: hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
509:
510: hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
504: hr_utility.trace('act_info2 is'|| p_tab_rec_data(i).act_info2);
505:
506: hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
507:
508: hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
509:
510: hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
511:
512: hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
506: hr_utility.trace('act_info3 is'|| p_tab_rec_data(i).act_info3);
507:
508: hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
509:
510: hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
511:
512: hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
513:
514: hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
508: hr_utility.trace('act_info4 is'|| p_tab_rec_data(i).act_info4);
509:
510: hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
511:
512: hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
513:
514: hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
515:
516: pay_action_information_api.create_action_information(
510: hr_utility.trace('act_info5 is'|| p_tab_rec_data(i).act_info5);
511:
512: hr_utility.trace('act_info6 is'|| p_tab_rec_data(i).act_info6);
513:
514: hr_utility.trace('act_info30 is'|| p_tab_rec_data(i).act_info30);
515:
516: pay_action_information_api.create_action_information(
517: p_action_information_id => l_action_information_id_1,
518: p_object_version_number => l_object_version_number_1,
703: /* Populate RL2 Amendment Employee Data for an assignment_action */
704:
705: open c_get_employee_data(p_assignment_action_id);
706:
707: hr_utility.trace('Populating RL2 Amendment Employee Data ');
708: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
709:
710: fetch c_get_employee_data into ltr_amend_arch_data(0);
711: close c_get_employee_data;
704:
705: open c_get_employee_data(p_assignment_action_id);
706:
707: hr_utility.trace('Populating RL2 Amendment Employee Data ');
708: hr_utility.trace('P_assignment_action_id :'||to_char(p_assignment_action_id));
709:
710: fetch c_get_employee_data into ltr_amend_arch_data(0);
711: close c_get_employee_data;
712:
709:
710: fetch c_get_employee_data into ltr_amend_arch_data(0);
711: close c_get_employee_data;
712:
713: hr_utility.trace('ltr_amend_pre_printed_slipno:'||to_char(l_pre_printed_slip_no));
714:
715: /* Populate RL2 YEPP Employee Data for an assignment_action */
716:
717: open c_get_employee_data(p_locked_action_id);
715: /* Populate RL2 YEPP Employee Data for an assignment_action */
716:
717: open c_get_employee_data(p_locked_action_id);
718:
719: hr_utility.trace('Populating RL2 YEPP Employee Data ');
720: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
721:
722: fetch c_get_employee_data into ltr_yepp_arch_data(0);
723: close c_get_employee_data;
716:
717: open c_get_employee_data(p_locked_action_id);
718:
719: hr_utility.trace('Populating RL2 YEPP Employee Data ');
720: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
721:
722: fetch c_get_employee_data into ltr_yepp_arch_data(0);
723: close c_get_employee_data;
724:
723: close c_get_employee_data;
724:
725: open c_get_employee_data2(p_locked_action_id); -- For Bug 8921055
726:
727: hr_utility.trace('Populating RL2 YEPP Employee Data2 ');
728: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
729:
730: fetch c_get_employee_data2 into ltr_yepp_pre_printed_slipno;
731: hr_utility.trace('ltr_yepp_pre_printed_slipno :'||to_char(ltr_yepp_pre_printed_slipno));
724:
725: open c_get_employee_data2(p_locked_action_id); -- For Bug 8921055
726:
727: hr_utility.trace('Populating RL2 YEPP Employee Data2 ');
728: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
729:
730: fetch c_get_employee_data2 into ltr_yepp_pre_printed_slipno;
731: hr_utility.trace('ltr_yepp_pre_printed_slipno :'||to_char(ltr_yepp_pre_printed_slipno));
732: close c_get_employee_data2;
727: hr_utility.trace('Populating RL2 YEPP Employee Data2 ');
728: hr_utility.trace('P_locked_action_id :'||to_char(p_locked_action_id));
729:
730: fetch c_get_employee_data2 into ltr_yepp_pre_printed_slipno;
731: hr_utility.trace('ltr_yepp_pre_printed_slipno :'||to_char(ltr_yepp_pre_printed_slipno));
732: close c_get_employee_data2;
733:
734: /* Populate RL2 Amendment Footnotes */
735: open c_get_footnotes(p_assignment_action_id);
733:
734: /* Populate RL2 Amendment Footnotes */
735: open c_get_footnotes(p_assignment_action_id);
736:
737: hr_utility.trace('Populating RL2 Amendment Footnote ');
738:
739: ln_amend_footnote_count := 0;
740: loop
741: fetch c_get_footnotes into ltr_amend_footnote(ln_amend_footnote_count);
740: loop
741: fetch c_get_footnotes into ltr_amend_footnote(ln_amend_footnote_count);
742: exit when c_get_footnotes%NOTFOUND;
743:
744: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
745: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
746:
747: ln_amend_footnote_count := ln_amend_footnote_count + 1;
748: end loop;
741: fetch c_get_footnotes into ltr_amend_footnote(ln_amend_footnote_count);
742: exit when c_get_footnotes%NOTFOUND;
743:
744: hr_utility.trace('Amend Message: '||ltr_amend_footnote(ln_amend_footnote_count).message);
745: hr_utility.trace('Amend Value: '||ltr_amend_footnote(ln_amend_footnote_count).value);
746:
747: ln_amend_footnote_count := ln_amend_footnote_count + 1;
748: end loop;
749:
756: loop
757: fetch c_get_footnotes into ltr_yepp_footnote(ln_yepp_footnote_count);
758: exit when c_get_footnotes%NOTFOUND;
759:
760: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
761: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
762:
763: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
764: end loop;
757: fetch c_get_footnotes into ltr_yepp_footnote(ln_yepp_footnote_count);
758: exit when c_get_footnotes%NOTFOUND;
759:
760: hr_utility.trace('YEPP Message: '||ltr_yepp_footnote(ln_yepp_footnote_count).message);
761: hr_utility.trace('YEPP Value: '||ltr_yepp_footnote(ln_yepp_footnote_count).value);
762:
763: ln_yepp_footnote_count := ln_yepp_footnote_count + 1;
764: end loop;
765:
764: end loop;
765:
766: close c_get_footnotes;
767:
768: hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Data ');
769:
770: if (ltr_yepp_arch_data.count = ltr_amend_arch_data.count) then
771:
772: if (ltr_yepp_arch_data.count <> 0) then
801: (ltr_yepp_arch_data(0).act_info29 <> ltr_amend_arch_data(0).act_info29))or
802: (ltr_yepp_pre_printed_slipno <> l_pre_printed_slip_no) then -- For Bug 8921055
803:
804: lv_flag := 'Y';
805: hr_utility.trace('lv_flag has been set to Y for Employee Data');
806: end if;
807:
808: end if;
809:
808: end if;
809:
810: else
811: lv_flag := 'Y';
812: hr_utility.trace('lv_flag has been set to Y for Employee Data');
813: end if;
814:
815:
816: /* Compare RL2 Amendment Footnotes and RL2 YEPP Footnotes for an
815:
816: /* Compare RL2 Amendment Footnotes and RL2 YEPP Footnotes for an
817: assignment_action */
818:
819: hr_utility.trace('Comparing RL2 Amend and RL2 YEPP Footnotes');
820:
821: if lv_flag <> 'Y' then
822:
823: if ln_yepp_footnote_count <> ln_amend_footnote_count then
838: (ltr_yepp_footnote(i).value is not null and
839: ltr_amend_footnote(i).value is null)) then
840:
841: lv_flag := 'Y';
842: hr_utility.trace('Footnote with diff value :'||ltr_yepp_footnote(i).message);
843: exit;
844: end if;
845: end if;
846: end loop;
853:
854: if lv_flag <> 'Y' then
855:
856: lv_flag := 'N';
857: hr_utility.trace('No value difference for Asg Action: '|| to_char(p_assignment_action_id));
858:
859: end if;
860:
861: hr_utility.trace('lv_flag :'||lv_flag);
857: hr_utility.trace('No value difference for Asg Action: '|| to_char(p_assignment_action_id));
858:
859: end if;
860:
861: hr_utility.trace('lv_flag :'||lv_flag);
862:
863: return lv_flag;
864:
865: end compare_archive_data;
1128:
1129: /* Get the report type, report qualifier, business group id and the
1130: gre for which the archiving has to be done */
1131:
1132: hr_utility.trace('getting report type ');
1133:
1134: select effective_date,
1135: report_type,
1136: -- Added for bug 10399514
1150: l_pre_org_id
1151: from pay_payroll_actions
1152: where payroll_action_id = pactid;
1153:
1154: hr_utility.trace('getting dates');
1155:
1156: get_dates(l_report_type,
1157: l_effective_date,
1158: l_period_end,
1160: l_quarter_end,
1161: l_year_start,
1162: l_year_end);
1163:
1164: hr_utility.trace('getting selection information');
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1161: l_year_start,
1162: l_year_end);
1163:
1164: hr_utility.trace('getting selection information');
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1162: l_year_end);
1163:
1164: hr_utility.trace('getting selection information');
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1163:
1164: hr_utility.trace('getting selection information');
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1164: hr_utility.trace('getting selection information');
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1172:
1165: hr_utility.trace('report type '|| l_report_type);
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1172:
1173: get_selection_information
1166: hr_utility.trace('report qualifier '|| l_state);
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1172:
1173: get_selection_information
1174: (l_report_type,
1167: hr_utility.trace('report category '|| l_report_cat);
1168: hr_utility.trace('quarter start '|| to_char(l_quarter_start,'dd-mm-yyyy'));
1169: hr_utility.trace('quarter end '|| to_char(l_quarter_end,'dd-mm-yyyy'));
1170: hr_utility.trace('year start '|| to_char(l_year_start,'dd-mm-yyyy'));
1171: hr_utility.trace('year end '|| to_char(l_year_end,'dd-mm-yyyy'));
1172:
1173: get_selection_information
1174: (l_report_type,
1175: l_quarter_start,
1183: l_group_by_medicare,
1184: l_tax_unit_context,
1185: l_jurisdiction_context);
1186:
1187: hr_utility.trace('Out of get selection information');
1188:
1189: -- Code modification for bug 10399514 starts here
1190: /* Initializing variable */
1191: l_person_on := FALSE ;
1209:
1210: -- open c_eoy_qbin;
1211:
1212: if l_person_on then
1213: hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1214: OPEN c_eoy_qbin_range ;
1215: else
1216: hr_utility.trace('opening c_eoy_qbin CURSOR');
1217: OPEN c_eoy_qbin;
1212: if l_person_on then
1213: hr_utility.trace('opening c_eoy_qbin_range CURSOR');
1214: OPEN c_eoy_qbin_range ;
1215: else
1216: hr_utility.trace('opening c_eoy_qbin CURSOR');
1217: OPEN c_eoy_qbin;
1218: end if ;
1219:
1220: -- Code modification for bug 10399514 ends here
1220: -- Code modification for bug 10399514 ends here
1221:
1222: /* Loop for all rows returned for SQL statement. */
1223:
1224: hr_utility.trace('Entering loop');
1225:
1226: loop
1227:
1228: -- Code modification for bug 10399514 starts here
1226: loop
1227:
1228: -- Code modification for bug 10399514 starts here
1229: if l_person_on then
1230: hr_utility.trace('fetching from c_eoy_qbin_range CURSOR');
1231: fetch c_eoy_qbin_range
1232: into l_person_id,
1233: l_assignment_id,
1234: l_effective_end_date;
1233: l_assignment_id,
1234: l_effective_end_date;
1235: exit when c_eoy_qbin_range%NOTFOUND;
1236: else
1237: hr_utility.trace('fetching from c_eoy_qbin CURSOR');
1238: fetch c_eoy_qbin into l_person_id,
1239: l_assignment_id,
1240: l_effective_end_date;
1241:
1247: /* If the new row is the same as the previous row according to the way
1248: the rows are grouped then discard the row ie. grouping by Prov Reporting
1249: Est requires a single row for each person / PRE combination. */
1250:
1251: hr_utility.trace('Prov Reporting Est is '
1252: || l_pre_org_id);
1253: hr_utility.trace('previous Prov Reporting Est is '||
1254: l_prev_pre_org_id);
1255: hr_utility.trace('person_id is '||
1249: Est requires a single row for each person / PRE combination. */
1250:
1251: hr_utility.trace('Prov Reporting Est is '
1252: || l_pre_org_id);
1253: hr_utility.trace('previous Prov Reporting Est is '||
1254: l_prev_pre_org_id);
1255: hr_utility.trace('person_id is '||
1256: to_char(l_person_id));
1257: hr_utility.trace('previous person_id is '||
1251: hr_utility.trace('Prov Reporting Est is '
1252: || l_pre_org_id);
1253: hr_utility.trace('previous Prov Reporting Est is '||
1254: l_prev_pre_org_id);
1255: hr_utility.trace('person_id is '||
1256: to_char(l_person_id));
1257: hr_utility.trace('previous person_id is '||
1258: to_char(l_prev_person_id));
1259:
1253: hr_utility.trace('previous Prov Reporting Est is '||
1254: l_prev_pre_org_id);
1255: hr_utility.trace('person_id is '||
1256: to_char(l_person_id));
1257: hr_utility.trace('previous person_id is '||
1258: to_char(l_prev_person_id));
1259:
1260: if (l_person_id = l_prev_person_id and
1261: l_pre_org_id = l_prev_pre_org_id) then
1259:
1260: if (l_person_id = l_prev_person_id and
1261: l_pre_org_id = l_prev_pre_org_id) then
1262:
1263: hr_utility.trace('Not creating assignment action');
1264:
1265: else
1266: /* Check whether the person has 0 payment or not */
1267:
1324: end loop;
1325: close c_all_qbin_gres;
1326: /* end of checking whether the person has 0 payment */
1327:
1328: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1329: hr_utility.trace('person is '|| to_char(l_person_id));
1330: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1331:
1332:
1325: close c_all_qbin_gres;
1326: /* end of checking whether the person has 0 payment */
1327:
1328: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1329: hr_utility.trace('person is '|| to_char(l_person_id));
1330: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1331:
1332:
1333: /* Have a new unique row according to the way the rows are grouped.
1326: /* end of checking whether the person has 0 payment */
1327:
1328: hr_utility.trace('prev person is '|| to_char(l_prev_person_id));
1329: hr_utility.trace('person is '|| to_char(l_person_id));
1330: hr_utility.trace('assignment is '|| to_char(l_assignment_id));
1331:
1332:
1333: /* Have a new unique row according to the way the rows are grouped.
1334: The inclusion of the person is dependent on having a non zero
1337: indicate their inclusion in the magnetic tape report. */
1338:
1339: /* Set up the context of tax unit id */
1340:
1341: hr_utility.trace('Setting context');
1342:
1343: if ((l_value <> 0) or (ln_no_gross_earnings <> 0)) then
1344: /* Get the primary assignment */
1345: open c_get_asg_id(l_person_id);
1347:
1348: if c_get_asg_id%NOTFOUND then
1349: close c_get_asg_id;
1350: pay_core_utils.push_message(800,'HR_74004_ASSIGNMENT_ABSENT','A');
1351: raise hr_utility.hr_error;
1352: else
1353: close c_get_asg_id;
1354: end if;
1355:
1361: from dual;
1362:
1363: /* Insert into pay_assignment_actions. */
1364:
1365: hr_utility.trace('creating assignment_action');
1366:
1367: /* Passing tax unit id as null */
1368:
1369: hr_nonrun_asact.insact(lockingactid,l_primary_asg,
1374: to do an additional checking against the assignment
1375: table
1376: */
1377:
1378: hr_utility.trace('updating assignment_action' || to_char(lockingactid));
1379:
1380: update pay_assignment_actions aa
1381: set aa.serial_number = to_char(l_person_id)
1382: where aa.assignment_action_id = lockingactid;
1391: l_prev_pre_org_id := l_pre_org_id;
1392:
1393: end loop;
1394:
1395: hr_utility.trace('Action creation done');
1396:
1397: -- Code modification for bug 10399514 starts here
1398: if l_person_on then
1399: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1395: hr_utility.trace('Action creation done');
1396:
1397: -- Code modification for bug 10399514 starts here
1398: if l_person_on then
1399: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1400: close c_eoy_qbin_range;
1401: else
1402: hr_utility.trace('closing c_eoy_qbin CURSOR');
1403: close c_eoy_qbin;
1398: if l_person_on then
1399: hr_utility.trace('closing c_eoy_qbin_range CURSOR');
1400: close c_eoy_qbin_range;
1401: else
1402: hr_utility.trace('closing c_eoy_qbin CURSOR');
1403: close c_eoy_qbin;
1404: end if ;
1405: -- Code modification for bug 10399514 ends here
1406:
1522: /* payroll action level database items */
1523:
1524: BEGIN
1525:
1526: /* hr_utility.trace_on('Y','RL2'); */
1527:
1528: initialization_process('PRE_DATA');
1529:
1530: select to_char(effective_date,'YYYY'),business_group_id,effective_date
1552: l_transmitter_org_id,
1553: l_transmitter_gre_ind;
1554:
1555: l_arch_gre_step := 40;
1556: hr_utility.trace('eoy_archive_gre_data 1');
1557:
1558: if employer_info%FOUND then
1559:
1560: close employer_info;
1557:
1558: if employer_info%FOUND then
1559:
1560: close employer_info;
1561: hr_utility.trace('got employer data ');
1562:
1563: l_employer_info_found := 'Y';
1564:
1565: begin
1640: l_accounting_contact_language:= 'TEST_DATA';
1641: l_proprietor_sin := 'TEST_DATA';
1642: l_arch_gre_step := 424;
1643:
1644: hr_utility.trace('eoy_archive_gre_data 2');
1645: close employer_info;
1646:
1647: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1648: pay_core_utils.push_token('orgind','Prov Reporting Est: '
1646:
1647: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1648: pay_core_utils.push_token('orgind','Prov Reporting Est: '
1649: ||p_pre_org_id);
1650: hr_utility.raise_error;
1651: end if; /* end if for employer_info%FOUND */
1652:
1653:
1654: /* archive Releve 2 data */
1788: /* Inserting rows into pay_action_information table
1789: Transmitter PRE Information */
1790:
1791: if ltr_ppa_arch_data.count >0 then
1792: hr_utility.trace('Archiving PRE Data');
1793: archive_data_records(
1794: p_action_context_id => p_payroll_action_id
1795: ,p_action_context_type=> 'PA'
1796: ,p_assignment_id => null
1802: /* Inserting rows into pay_action_information table
1803: Employer Information (Could be just a PRE or Transmitter PRE) */
1804:
1805: if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count >0 then
1806: hr_utility.trace('Archiving Employer Data');
1807: archive_data_records(
1808: p_action_context_id => p_payroll_action_id
1809: ,p_action_context_type=> 'PA'
1810: ,p_assignment_id => null
1815:
1816: EXCEPTION
1817: when others then
1818: g_archive_flag := 'N';
1819: hr_utility.trace('Error in eoy_archive_gre_data at step :' ||
1820: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
1821: if l_arch_gre_step = 40 then
1822: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1823: pay_core_utils.push_token('orgind','Prov Reporting Est: '
1822: pay_core_utils.push_message(801,'PAY_74014_NO_TRANSMITTER_ORG','A');
1823: pay_core_utils.push_token('orgind','Prov Reporting Est: '
1824: ||p_pre_org_id);
1825: end if;
1826: hr_utility.raise_error;
1827:
1828: END eoy_archive_gre_data;
1829:
1830: /* Name : chk_gre_archive
1845: and action_context_id = p_payroll_action_id;
1846:
1847: begin
1848:
1849: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1850:
1851: if g_archive_flag = 'Y' then
1852: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1853: return (TRUE);
1848:
1849: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
1850:
1851: if g_archive_flag = 'Y' then
1852: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1853: return (TRUE);
1854: else
1855:
1856: hr_utility.trace('chk_gre_archive - opening cursor');
1852: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
1853: return (TRUE);
1854: else
1855:
1856: hr_utility.trace('chk_gre_archive - opening cursor');
1857:
1858: open c_chk_payroll_action;
1859: fetch c_chk_payroll_action into l_flag;
1860: if c_chk_payroll_action%FOUND then
1857:
1858: open c_chk_payroll_action;
1859: fetch c_chk_payroll_action into l_flag;
1860: if c_chk_payroll_action%FOUND then
1861: hr_utility.trace('chk_gre_archive - found in cursor');
1862: g_archive_flag := 'Y';
1863: else
1864: hr_utility.trace('chk_gre_archive - not found in cursor');
1865: g_archive_flag := 'N';
1860: if c_chk_payroll_action%FOUND then
1861: hr_utility.trace('chk_gre_archive - found in cursor');
1862: g_archive_flag := 'Y';
1863: else
1864: hr_utility.trace('chk_gre_archive - not found in cursor');
1865: g_archive_flag := 'N';
1866: end if;
1867:
1868: hr_utility.trace('chk_gre_archive - closing cursor');
1864: hr_utility.trace('chk_gre_archive - not found in cursor');
1865: g_archive_flag := 'N';
1866: end if;
1867:
1868: hr_utility.trace('chk_gre_archive - closing cursor');
1869: close c_chk_payroll_action;
1870: if g_archive_flag = 'Y' then
1871: hr_utility.trace('chk_gre_archive - returning true');
1872: return (TRUE);
1867:
1868: hr_utility.trace('chk_gre_archive - closing cursor');
1869: close c_chk_payroll_action;
1870: if g_archive_flag = 'Y' then
1871: hr_utility.trace('chk_gre_archive - returning true');
1872: return (TRUE);
1873: else
1874: hr_utility.trace('chk_gre_archive - returning false');
1875: return(FALSE);
1870: if g_archive_flag = 'Y' then
1871: hr_utility.trace('chk_gre_archive - returning true');
1872: return (TRUE);
1873: else
1874: hr_utility.trace('chk_gre_archive - returning false');
1875: return(FALSE);
1876: end if;
1877: end if;
1878: end chk_gre_archive;
1904: raise_application_error(-20001,'eoy_archinit: Assignment actions not created!!!');
1905: end if;
1906: close c_get_min_chunk;
1907:
1908: hr_utility.set_location ('eoy_archinit min chunk is ' || to_char(g_min_chunk),12);
1909: l_step := 2;
1910: l_archive := chk_gre_archive(p_payroll_action_id);
1911:
1912: l_step := 3;
1909: l_step := 2;
1910: l_archive := chk_gre_archive(p_payroll_action_id);
1911:
1912: l_step := 3;
1913: hr_utility.trace ('eoy_archinit g_archive_flag is ' || g_archive_flag);
1914: exception
1915: when others then
1916: raise_application_error(-20001,'eoy_archinit at '
1917: ||to_char(l_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
1928: l_check_number number;
1929: BEGIN
1930:
1931: l_check_number := mod(p_curr_seq,7);
1932: hr_utility.trace('l_check_number ='|| l_check_number);
1933: l_seq_number := (p_curr_seq * 10) + l_check_number;
1934: hr_utility.trace('l_seq_number ='|| l_seq_number);
1935: return l_seq_number;
1936: END;
1930:
1931: l_check_number := mod(p_curr_seq,7);
1932: hr_utility.trace('l_check_number ='|| l_check_number);
1933: l_seq_number := (p_curr_seq * 10) + l_check_number;
1934: hr_utility.trace('l_seq_number ='|| l_seq_number);
1935: return l_seq_number;
1936: END;
1937:
1938: /* Function to generate sequence number for RL2 PDFs - Bug 6768167*/
1968: l_act_info_id number;
1969:
1970: begin
1971:
1972: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 10');
1973:
1974: if (called_from = 'XMLPROC') then
1975: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 20');
1976: open c_get_arch_seq_num(p_aaid);
1971:
1972: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 10');
1973:
1974: if (called_from = 'XMLPROC') then
1975: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 20');
1976: open c_get_arch_seq_num(p_aaid);
1977: fetch c_get_arch_seq_num into l_final_seq_num,l_act_info_id,l_obj_ver;
1978: close c_get_arch_seq_num;
1979:
1988: fetch c_get_seq_num_range into l_start_seq_num,l_end_seq_num;
1989: close c_get_seq_num_range;
1990:
1991: if (l_start_seq_num is not null) then
1992: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 30');
1993:
1994: select PAY_CA_RL2_PDF_SEQ_COUNT_S.nextval into l_seq_offset
1995: from dual;
1996: l_final_seq_num := getnext_seq_num(l_start_seq_num + l_seq_offset); --Bug 8500723
1998: l_final_seq_num := null;
1999: end if;
2000:
2001: if (called_from ='XMLPROC') then
2002: hr_utility.trace('In pay_ca_eoy_rl2_archive.gen_rl2_pdf_seq 40');
2003:
2004: pay_action_information_api.update_action_information(p_action_information_id=>l_act_info_id,
2005: p_object_version_number=>l_obj_ver,
2006: p_action_information3=>l_final_seq_num);
2027: l_rl2_slip_number varchar2(20);
2028: l_rl2_last_slip_number number;
2029:
2030: begin
2031: hr_utility.trace('p_transmitter_id : ' || to_char(p_transmitter_id));
2032:
2033: begin
2034: select to_number(target.ORG_INFORMATION1),to_number(target.ORG_INFORMATION2)
2035: into l_rl2_starting_slip_num,l_rl2_ending_slip_num
2045: null;
2046: end;
2047:
2048: if l_rl2_starting_slip_num is NULL then
2049: hr_utility.trace('Warning: Starting Slip number is NULL');
2050: end if;
2051:
2052: if l_rl2_ending_slip_num is NULL then
2053: hr_utility.trace('Warning: Ending Slip number is NULL');
2049: hr_utility.trace('Warning: Starting Slip number is NULL');
2050: end if;
2051:
2052: if l_rl2_ending_slip_num is NULL then
2053: hr_utility.trace('Warning: Ending Slip number is NULL');
2054: end if;
2055: hr_utility.trace('Starting Slip number: '||l_rl2_starting_slip_num);
2056: hr_utility.trace('Ending Slip number: '||l_rl2_ending_slip_num);
2057:
2051:
2052: if l_rl2_ending_slip_num is NULL then
2053: hr_utility.trace('Warning: Ending Slip number is NULL');
2054: end if;
2055: hr_utility.trace('Starting Slip number: '||l_rl2_starting_slip_num);
2056: hr_utility.trace('Ending Slip number: '||l_rl2_ending_slip_num);
2057:
2058: select l_rl2_starting_slip_num + pay_ca_eoy_rl2_s.nextval - 1
2059: into l_rl2_curr_slip_number from dual;
2052: if l_rl2_ending_slip_num is NULL then
2053: hr_utility.trace('Warning: Ending Slip number is NULL');
2054: end if;
2055: hr_utility.trace('Starting Slip number: '||l_rl2_starting_slip_num);
2056: hr_utility.trace('Ending Slip number: '||l_rl2_ending_slip_num);
2057:
2058: select l_rl2_starting_slip_num + pay_ca_eoy_rl2_s.nextval - 1
2059: into l_rl2_curr_slip_number from dual;
2060: hr_utility.trace('l_rl2_curr_slip_number: '||l_rl2_curr_slip_number);
2056: hr_utility.trace('Ending Slip number: '||l_rl2_ending_slip_num);
2057:
2058: select l_rl2_starting_slip_num + pay_ca_eoy_rl2_s.nextval - 1
2059: into l_rl2_curr_slip_number from dual;
2060: hr_utility.trace('l_rl2_curr_slip_number: '||l_rl2_curr_slip_number);
2061:
2062: if l_rl2_ending_slip_num is not NULL and l_rl2_curr_slip_number is not NULL
2063: and l_rl2_curr_slip_number > l_rl2_ending_slip_num then
2064: hr_utility.trace('Warning: Generated Slip number '||l_rl2_curr_slip_number
2060: hr_utility.trace('l_rl2_curr_slip_number: '||l_rl2_curr_slip_number);
2061:
2062: if l_rl2_ending_slip_num is not NULL and l_rl2_curr_slip_number is not NULL
2063: and l_rl2_curr_slip_number > l_rl2_ending_slip_num then
2064: hr_utility.trace('Warning: Generated Slip number '||l_rl2_curr_slip_number
2065: ||' is greater than Ending slip number '||l_rl2_ending_slip_num
2066: ||' for assignment id '||to_char(p_asgid));
2067: end if;
2068: -- Ended changes for bug 11694701
2068: -- Ended changes for bug 11694701
2069:
2070: l_rl2_slip_number_last_digit := mod(l_rl2_curr_slip_number,7);
2071:
2072: hr_utility.trace('l_rl2_curr_slip_number : '||l_rl2_curr_slip_number);
2073: hr_utility.trace('l_rl2_slip_number_last_digit : '||
2074: l_rl2_slip_number_last_digit);
2075:
2076: /* Commented and modified as below by sneelapa, bug 11654691
2069:
2070: l_rl2_slip_number_last_digit := mod(l_rl2_curr_slip_number,7);
2071:
2072: hr_utility.trace('l_rl2_curr_slip_number : '||l_rl2_curr_slip_number);
2073: hr_utility.trace('l_rl2_slip_number_last_digit : '||
2074: l_rl2_slip_number_last_digit);
2075:
2076: /* Commented and modified as below by sneelapa, bug 11654691
2077:
2081:
2082: l_rl2_slip_number :=
2083: lpad ((l_rl2_curr_slip_number)||l_rl2_slip_number_last_digit, 9, 0);
2084:
2085: hr_utility.trace('l_rl2_slip_number : ' || l_rl2_slip_number);
2086: return l_rl2_slip_number;
2087:
2088: exception
2089: when no_data_found then
2086: return l_rl2_slip_number;
2087:
2088: exception
2089: when no_data_found then
2090: hr_utility.trace('Problem in generation of RL2 Slip Number');
2091: hr_utility.trace('l_transmitter_id :'||to_char(p_transmitter_id));
2092: /* need a pop-message if rl2 slip number not generated */
2093: l_rl2_slip_number := 0;
2094:
2087:
2088: exception
2089: when no_data_found then
2090: hr_utility.trace('Problem in generation of RL2 Slip Number');
2091: hr_utility.trace('l_transmitter_id :'||to_char(p_transmitter_id));
2092: /* need a pop-message if rl2 slip number not generated */
2093: l_rl2_slip_number := 0;
2094:
2095: end gen_rl2_slip_no;
2177: order by paa.action_sequence desc;
2178:
2179: BEGIN
2180:
2181: --hr_utility.trace_on(null,'RL2');
2182: hr_utility.set_location ('archive_data',1);
2183: hr_utility.trace('getting assignment');
2184: l_negative_balance_exists := p_negative_balance_exists;
2185: p_temp_fi := 0; -- 14701748
2178:
2179: BEGIN
2180:
2181: --hr_utility.trace_on(null,'RL2');
2182: hr_utility.set_location ('archive_data',1);
2183: hr_utility.trace('getting assignment');
2184: l_negative_balance_exists := p_negative_balance_exists;
2185: p_temp_fi := 0; -- 14701748
2186:
2179: BEGIN
2180:
2181: --hr_utility.trace_on(null,'RL2');
2182: hr_utility.set_location ('archive_data',1);
2183: hr_utility.trace('getting assignment');
2184: l_negative_balance_exists := p_negative_balance_exists;
2185: p_temp_fi := 0; -- 14701748
2186:
2187: l_step := 1;
2214: where payroll_action_id = l_payroll_action_id;
2215:
2216: exception when no_data_found then
2217: /* need a pop-message */
2218: hr_utility.trace('assignment_action_id doesnot exist to archive emp_info'
2219: ||to_char(p_assactid));
2220: end;
2221:
2222: /* If the chunk of the assignment is same as the minimun chunk
2224: archived then archive the gre data i.e. the employer data */
2225:
2226: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2227:
2228: hr_utility.trace('eoy_archive_data archiving employer data');
2229: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2230: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2231:
2232: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2225:
2226: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2227:
2228: hr_utility.trace('eoy_archive_data archiving employer data');
2229: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2230: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2231:
2232: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2233: p_pre_org_id=>l_pre_org_id);
2226: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2227:
2228: hr_utility.trace('eoy_archive_data archiving employer data');
2229: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2230: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2231:
2232: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2233: p_pre_org_id=>l_pre_org_id);
2234:
2231:
2232: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2233: p_pre_org_id=>l_pre_org_id);
2234:
2235: hr_utility.trace('eoy_archive_data archived employer data');
2236:
2237: end if;
2238:
2239: hr_utility.set_location ('eoy_archive_further_info',2);
2235: hr_utility.trace('eoy_archive_data archived employer data');
2236:
2237: end if;
2238:
2239: hr_utility.set_location ('eoy_archive_further_info',2);
2240:
2241: hr_utility.trace('assignment '|| to_char(l_asgid));
2242: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2237: end if;
2238:
2239: hr_utility.set_location ('eoy_archive_further_info',2);
2240:
2241: hr_utility.trace('assignment '|| to_char(l_asgid));
2242: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2244: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245:
2238:
2239: hr_utility.set_location ('eoy_archive_further_info',2);
2240:
2241: hr_utility.trace('assignment '|| to_char(l_asgid));
2242: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2244: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245:
2246: /* Derive the beginning and end of the effective year */
2239: hr_utility.set_location ('eoy_archive_further_info',2);
2240:
2241: hr_utility.trace('assignment '|| to_char(l_asgid));
2242: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2244: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245:
2246: /* Derive the beginning and end of the effective year */
2247:
2240:
2241: hr_utility.trace('assignment '|| to_char(l_asgid));
2242: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2243: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2244: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245:
2246: /* Derive the beginning and end of the effective year */
2247:
2248: hr_utility.trace('getting begin and end dates');
2244: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2245:
2246: /* Derive the beginning and end of the effective year */
2247:
2248: hr_utility.trace('getting begin and end dates');
2249:
2250: l_step := 2;
2251:
2252: l_year_start := trunc(p_effective_date, 'Y');
2251:
2252: l_year_start := trunc(p_effective_date, 'Y');
2253: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2254:
2255: hr_utility.trace('year start '|| to_char(l_year_start));
2256: hr_utility.trace('year end '|| to_char(l_year_end));
2257:
2258:
2259: l_step := 3;
2252: l_year_start := trunc(p_effective_date, 'Y');
2253: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2254:
2255: hr_utility.trace('year start '|| to_char(l_year_start));
2256: hr_utility.trace('year end '|| to_char(l_year_end));
2257:
2258:
2259: l_step := 3;
2260:
2267:
2268: /* We can archive the balance level dbis also because for employee level
2269: balances jurisdiction is always a context. */
2270:
2271: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2272:
2273: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2274: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2275:
2272:
2273: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2274: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2275:
2276: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2277:
2278: /* Initialise the PL/SQL table before populating it */
2279: hr_utility.trace('Initialising Pl/SQL table');
2280:
2275:
2276: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2277:
2278: /* Initialise the PL/SQL table before populating it */
2279: hr_utility.trace('Initialising Pl/SQL table');
2280:
2281: /* Any new further info codes introduced should be added here and also
2282: in the procedure eoy_archive_data to check whether line of records
2283: need to archived or not -- bug 16045054*/
2313:
2314: l_count := l_count + 1;
2315: l_balance_type_tab(l_count) := 'RL2_FURTHER_INFO_AMOUNT_C3';
2316:
2317: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
2318:
2319: if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.count > 0 then
2320: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.delete;
2321: end if;
2342: else
2343: result := 0;
2344: open c_all_gres(p_assactid);
2345: loop
2346: hr_utility.trace('Fetching all GREs');
2347: fetch c_all_gres into l_tax_unit_id;
2348: exit when c_all_gres%NOTFOUND;
2349:
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2346: hr_utility.trace('Fetching all GREs');
2347: fetch c_all_gres into l_tax_unit_id;
2348: exit when c_all_gres%NOTFOUND;
2349:
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351: hr_utility.trace('Person_id is ' || lv_serial_number);
2352: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2347: fetch c_all_gres into l_tax_unit_id;
2348: exit when c_all_gres%NOTFOUND;
2349:
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351: hr_utility.trace('Person_id is ' || lv_serial_number);
2352: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2355:
2348: exit when c_all_gres%NOTFOUND;
2349:
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351: hr_utility.trace('Person_id is ' || lv_serial_number);
2352: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2355:
2356: begin
2349:
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351: hr_utility.trace('Person_id is ' || lv_serial_number);
2352: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2355:
2356: begin
2357: /* Getting Payroll Run Level Max Assignment Action Id for
2350: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
2351: hr_utility.trace('Person_id is ' || lv_serial_number);
2352: hr_utility.trace('Asgid is ' || to_char(l_asgid));
2353: hr_utility.trace('Reporting_type is ' || l_reporting_type);
2354: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
2355:
2356: begin
2357: /* Getting Payroll Run Level Max Assignment Action Id for
2358: the given tax_unit_id in the reporting year. Fix for bug#3638928 */
2363: l_year_end);
2364: fetch c_get_max_asg_act_id into l_aaid;
2365: close c_get_max_asg_act_id;
2366:
2367: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2368: hr_utility.trace('l_count is ' || to_char(l_count));
2369:
2370: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2371: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2364: fetch c_get_max_asg_act_id into l_aaid;
2365: close c_get_max_asg_act_id;
2366:
2367: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
2368: hr_utility.trace('l_count is ' || to_char(l_count));
2369:
2370: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2371: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2372: result := result + nvl(pay_ca_balance_pkg.call_ca_balance_get_value
2379: l_tax_unit_id,
2380: l_business_group_id,
2381: 'QC'
2382: ),0) ;
2383: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2384: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2385: hr_utility.trace('Result is ' || to_char(result));
2386: exception when no_data_found then
2387: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2380: l_business_group_id,
2381: 'QC'
2382: ),0) ;
2383: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2384: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2385: hr_utility.trace('Result is ' || to_char(result));
2386: exception when no_data_found then
2387: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2388: end;
2381: 'QC'
2382: ),0) ;
2383: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2384: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2385: hr_utility.trace('Result is ' || to_char(result));
2386: exception when no_data_found then
2387: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2388: end;
2389: end loop;
2383: hr_utility.trace('for Asg_Act_id :'||to_char(p_assactid));
2384: hr_utility.trace('l_user_entity_name_tab(i) is ' || l_user_entity_name_tab(i));
2385: hr_utility.trace('Result is ' || to_char(result));
2386: exception when no_data_found then
2387: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
2388: end;
2389: end loop;
2390: close c_all_gres;
2391: ln_further_index := ln_further_index+1;
2421: ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data);
2422: ln_further_index := null;
2423: end if;
2424: p_negative_balance_exists := l_negative_balance_exists;
2425: hr_utility.trace('End of Provincial YE Amendment PP Validation');
2426:
2427: Exception when no_data_found then
2428: hr_utility.trace('Report type not found for given Payroll_action ');
2429: null;
2424: p_negative_balance_exists := l_negative_balance_exists;
2425: hr_utility.trace('End of Provincial YE Amendment PP Validation');
2426:
2427: Exception when no_data_found then
2428: hr_utility.trace('Report type not found for given Payroll_action ');
2429: null;
2430: -- End of Provincial YE Amendment Pre-Process Validation
2431: end eoy_archive_further_info;
2432:
2779: and cp_eff_date between start_date and end_date;
2780:
2781: BEGIN
2782:
2783: -- hr_utility.trace_on(null,'RL2');
2784: hr_utility.set_location ('archive_data',1);
2785: hr_utility.trace('getting assignment');
2786: l_negative_balance_exists := 'N';
2787: l_has_been_paid := 'N';
2780:
2781: BEGIN
2782:
2783: -- hr_utility.trace_on(null,'RL2');
2784: hr_utility.set_location ('archive_data',1);
2785: hr_utility.trace('getting assignment');
2786: l_negative_balance_exists := 'N';
2787: l_has_been_paid := 'N';
2788:
2781: BEGIN
2782:
2783: -- hr_utility.trace_on(null,'RL2');
2784: hr_utility.set_location ('archive_data',1);
2785: hr_utility.trace('getting assignment');
2786: l_negative_balance_exists := 'N';
2787: l_has_been_paid := 'N';
2788:
2789: lv_BoxL_Excess_amt := '0';
2821: where payroll_action_id = l_payroll_action_id;
2822:
2823: exception when no_data_found then
2824: /* need a pop-message */
2825: hr_utility.trace('assignment_action_id doesnot exist to archive emp_info'
2826: ||to_char(p_assactid));
2827: end;
2828:
2829: /* If the chunk of the assignment is same as the minimun chunk
2831: archived then archive the gre data i.e. the employer data */
2832:
2833: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2834:
2835: hr_utility.trace('eoy_archive_data archiving employer data');
2836: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2837: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2838:
2839: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2832:
2833: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2834:
2835: hr_utility.trace('eoy_archive_data archiving employer data');
2836: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2837: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2838:
2839: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2840: p_pre_org_id=>l_pre_org_id);
2833: if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2834:
2835: hr_utility.trace('eoy_archive_data archiving employer data');
2836: hr_utility.trace('l_payroll_action_id '|| to_char(l_payroll_action_id));
2837: hr_utility.trace('l_pre_org_id '|| l_pre_org_id);
2838:
2839: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2840: p_pre_org_id=>l_pre_org_id);
2841:
2838:
2839: eoy_archive_gre_data(p_payroll_action_id =>l_payroll_action_id,
2840: p_pre_org_id=>l_pre_org_id);
2841:
2842: hr_utility.trace('eoy_archive_data archived employer data');
2843:
2844: end if;
2845:
2846: hr_utility.set_location ('archive_data',2);
2842: hr_utility.trace('eoy_archive_data archived employer data');
2843:
2844: end if;
2845:
2846: hr_utility.set_location ('archive_data',2);
2847:
2848: hr_utility.trace('assignment '|| to_char(l_asgid));
2849: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2844: end if;
2845:
2846: hr_utility.set_location ('archive_data',2);
2847:
2848: hr_utility.trace('assignment '|| to_char(l_asgid));
2849: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2851: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852:
2845:
2846: hr_utility.set_location ('archive_data',2);
2847:
2848: hr_utility.trace('assignment '|| to_char(l_asgid));
2849: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2851: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852:
2853: /* Derive the beginning and end of the effective year */
2846: hr_utility.set_location ('archive_data',2);
2847:
2848: hr_utility.trace('assignment '|| to_char(l_asgid));
2849: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2851: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852:
2853: /* Derive the beginning and end of the effective year */
2854:
2847:
2848: hr_utility.trace('assignment '|| to_char(l_asgid));
2849: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2850: hr_utility.trace('tax_unit_id '|| to_char(l_tax_unit_id));
2851: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852:
2853: /* Derive the beginning and end of the effective year */
2854:
2855: hr_utility.trace('getting begin and end dates');
2851: hr_utility.trace('business_group_id '|| to_char(l_business_group_id));
2852:
2853: /* Derive the beginning and end of the effective year */
2854:
2855: hr_utility.trace('getting begin and end dates');
2856:
2857: l_step := 2;
2858:
2859: l_year_start := trunc(p_effective_date, 'Y');
2858:
2859: l_year_start := trunc(p_effective_date, 'Y');
2860: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2861:
2862: hr_utility.trace('year start '|| to_char(l_year_start));
2863: hr_utility.trace('year end '|| to_char(l_year_end));
2864:
2865:
2866: l_step := 3;
2859: l_year_start := trunc(p_effective_date, 'Y');
2860: l_year_end := add_months(trunc(p_effective_date, 'Y'),12) - 1;
2861:
2862: hr_utility.trace('year start '|| to_char(l_year_start));
2863: hr_utility.trace('year end '|| to_char(l_year_end));
2864:
2865:
2866: l_step := 3;
2867:
2887:
2888: /* We can archive the balance level dbis also because for employee level
2889: balances jurisdiction is always a context. */
2890:
2891: hr_utility.trace('max assignment_action_id : ' || to_char(l_aaid));
2892:
2893: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2894: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2895:
2892:
2893: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
2894: pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction);
2895:
2896: hr_utility.trace('Archiving the balance dbi ' || l_jurisdiction);
2897:
2898: /* 14701748 */
2899: begin
2900:
2907: and hoi.organization_id = hou.organization_id
2908: and hoi.org_information_context = 'Prov Reporting Est'
2909: and hoi.organization_id = to_number(l_pre_org_id)
2910: and hoi.org_information4 = 'P02';
2911: hr_utility.trace('l_transmitter_id : ' || to_char(l_transmitter_id));
2912:
2913: begin
2914: select hoi.org_information1,hoi.org_information2
2915: into l_pre_source_of_income,l_pre_description
2918: and hoi.org_information_context = 'Prov Reporting Est2';
2919:
2920: exception
2921: when no_data_found then
2922: hr_utility.trace('No RL2 Source of Income at PRE level');
2923: hr_utility.trace('l_transmitter_id :'||to_char(l_transmitter_id));
2924: l_pre_source_of_income := NULL;
2925: l_pre_description := NULL;
2926: end;
2919:
2920: exception
2921: when no_data_found then
2922: hr_utility.trace('No RL2 Source of Income at PRE level');
2923: hr_utility.trace('l_transmitter_id :'||to_char(l_transmitter_id));
2924: l_pre_source_of_income := NULL;
2925: l_pre_description := NULL;
2926: end;
2927: exception
2925: l_pre_description := NULL;
2926: end;
2927: exception
2928: when no_data_found then
2929: hr_utility.trace('Could not get the transmitter ID for the PRE');
2930: end;
2931:
2932:
2933: /* Initialise the PL/SQL table before populating it */
2930: end;
2931:
2932:
2933: /* Initialise the PL/SQL table before populating it */
2934: hr_utility.trace('Initialising Pl/SQL table');
2935:
2936: l_count := 0;
2937:
2938: l_count := l_count + 1;
3050: l_fi_balance_type_tab(l_count_fi) := 'RL2_FURTHER_INFO_AMOUNT_C3';
3051:
3052: /*Bug 16045054 ends here*/
3053:
3054: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3055:
3056: for i in 1 .. l_count
3057: loop
3058: hr_utility.trace('Initialising values');
3054: hr_utility.trace('Assignment action id is ' || to_char(p_assactid));
3055:
3056: for i in 1 .. l_count
3057: loop
3058: hr_utility.trace('Initialising values');
3059: l_user_entity_value_tab(i) := 0;
3060: end loop;
3061:
3062: open c_all_gres(p_assactid);
3062: open c_all_gres(p_assactid);
3063:
3064: loop
3065:
3066: hr_utility.trace('Fetching all GREs');
3067: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3068: exit when c_all_gres%NOTFOUND;
3069:
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3066: hr_utility.trace('Fetching all GREs');
3067: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3068: exit when c_all_gres%NOTFOUND;
3069:
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071: hr_utility.trace('Person_id is ' || lv_serial_number);
3072: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3067: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3068: exit when c_all_gres%NOTFOUND;
3069:
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071: hr_utility.trace('Person_id is ' || lv_serial_number);
3072: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3075:
3068: exit when c_all_gres%NOTFOUND;
3069:
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071: hr_utility.trace('Person_id is ' || lv_serial_number);
3072: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3075:
3076: begin
3069:
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071: hr_utility.trace('Person_id is ' || lv_serial_number);
3072: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3075:
3076: begin
3077: /* Getting Payroll Run Level Max Assignment Action Id for
3070: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3071: hr_utility.trace('Person_id is ' || lv_serial_number);
3072: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3073: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3074: hr_utility.trace('Effective date is ' || to_char(p_effective_date));
3075:
3076: begin
3077: /* Getting Payroll Run Level Max Assignment Action Id for
3078: the given tax_unit_id in the reporting year. Fix for bug#3638928 */
3083: l_year_end);
3084: fetch c_get_max_asg_act_id into l_aaid;
3085: close c_get_max_asg_act_id;
3086:
3087: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3088: hr_utility.trace('l_count is ' || to_char(l_count));
3089:
3090: ln_no_gross_earnings := ln_no_gross_earnings +
3091: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3084: fetch c_get_max_asg_act_id into l_aaid;
3085: close c_get_max_asg_act_id;
3086:
3087: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3088: hr_utility.trace('l_count is ' || to_char(l_count));
3089:
3090: ln_no_gross_earnings := ln_no_gross_earnings +
3091: nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3092: ('RL2 No Gross Earnings',
3104:
3105: if l_tax_unit_id <> l_prev_tax_unit_id or
3106: l_prev_tax_unit_id is null then
3107:
3108: hr_utility.trace('l_business_group_id is '||to_char(l_business_group_id));
3109:
3110: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3111: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3112: Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3113:
3114: for i in 1 .. l_count
3115: loop
3116:
3117: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3118: hr_utility.trace('i is ' || i);
3119:
3120: -- T4A earnings should not go to BOX A of RL2
3121:
3114: for i in 1 .. l_count
3115: loop
3116:
3117: hr_utility.trace('l_balance_type is ' || l_balance_type_tab(i));
3118: hr_utility.trace('i is ' || i);
3119:
3120: -- T4A earnings should not go to BOX A of RL2
3121:
3122: if l_reporting_type = 'T4A/RL2' and
3140: l_business_group_id,
3141: 'QC'
3142: ),0);
3143:
3144: hr_utility.trace('Fed Result = ' || fed_result);
3145: else
3146: fed_result := 0;
3147: hr_utility.trace('Fed Result = ' || fed_result);
3148: end if;/*end if for l_balance_type_tab(i)='Gross Earnings' */
3143:
3144: hr_utility.trace('Fed Result = ' || fed_result);
3145: else
3146: fed_result := 0;
3147: hr_utility.trace('Fed Result = ' || fed_result);
3148: end if;/*end if for l_balance_type_tab(i)='Gross Earnings' */
3149:
3150: /* Based on defined_balance_id get the balance value
3151: for each assignment action */
3167: end if;
3168:
3169: end if; -- end if for 'T4A/RL2' validation
3170:
3171: hr_utility.trace('Balance Type is '||l_balance_type_tab(i));
3172: hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
3173: l_prev_tax_unit_id := l_tax_unit_id ;
3174:
3175: end loop; -- end loop for all balances plsql table
3168:
3169: end if; -- end if for 'T4A/RL2' validation
3170:
3171: hr_utility.trace('Balance Type is '||l_balance_type_tab(i));
3172: hr_utility.trace('archive value is '||l_user_entity_value_tab(i));
3173: l_prev_tax_unit_id := l_tax_unit_id ;
3174:
3175: end loop; -- end loop for all balances plsql table
3176: end if; --end if for l_tax_unit_id <> l_prev_tax_unit_id validation
3176: end if; --end if for l_tax_unit_id <> l_prev_tax_unit_id validation
3177:
3178: exception
3179: when no_data_found then
3180: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3181: /* need a pop-message asgid has no payroll run in tax-unit-id */
3182: end;
3183: end loop;
3184: close c_all_gres;
3192: loop
3193: l_fi_result := 0;
3194: open c_all_gres(p_assactid);
3195: loop
3196: hr_utility.trace('Fetching all GREs');
3197: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3198: exit when c_all_gres%NOTFOUND;
3199:
3200: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3196: hr_utility.trace('Fetching all GREs');
3197: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3198: exit when c_all_gres%NOTFOUND;
3199:
3200: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3201: hr_utility.trace('Person_id is ' || lv_serial_number);
3202: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3203:
3204: begin
3197: fetch c_all_gres into l_tax_unit_id,l_reporting_type;
3198: exit when c_all_gres%NOTFOUND;
3199:
3200: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3201: hr_utility.trace('Person_id is ' || lv_serial_number);
3202: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3203:
3204: begin
3205: /* Getting Payroll Run Level Max Assignment Action Id for
3198: exit when c_all_gres%NOTFOUND;
3199:
3200: hr_utility.trace('Tax unit id is ' || to_char(l_tax_unit_id));
3201: hr_utility.trace('Person_id is ' || lv_serial_number);
3202: hr_utility.trace('Asgid is ' || to_char(l_asgid));
3203:
3204: begin
3205: /* Getting Payroll Run Level Max Assignment Action Id for
3206: the given tax_unit_id in the reporting year. Fix for bug#3638928 */
3211: l_year_end);
3212: fetch c_get_max_asg_act_id into l_aaid;
3213: close c_get_max_asg_act_id;
3214:
3215: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3216: hr_utility.trace('l_count_fi is ' || to_char(l_count_fi));
3217: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3218: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3219: Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3212: fetch c_get_max_asg_act_id into l_aaid;
3213: close c_get_max_asg_act_id;
3214:
3215: hr_utility.trace('l_aaid is ' || to_char(l_aaid));
3216: hr_utility.trace('l_count_fi is ' || to_char(l_count_fi));
3217: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
3218: pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID',l_aaid);
3219: Pay_balance_pkg.set_context('JURISDICTION_CODE', 'QC');
3220: l_fi_result := l_fi_result + nvl(pay_ca_balance_pkg.call_ca_balance_get_value
3227: l_tax_unit_id,
3228: l_business_group_id,
3229: 'QC'
3230: ),0) ;
3231: hr_utility.trace('Result is ' || to_char(l_fi_result));
3232: exception when no_data_found then
3233: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3234: end;
3235: end loop;
3229: 'QC'
3230: ),0) ;
3231: hr_utility.trace('Result is ' || to_char(l_fi_result));
3232: exception when no_data_found then
3233: hr_utility.trace('This Tax unit id has no payroll run, so skip it');
3234: end;
3235: end loop;
3236: close c_all_gres;
3237: if l_fi_result is not null and l_fi_result <> 0 then
3238: l_has_been_paid := 'Y';
3239: end if;
3240: end loop;
3241: /*Bug 16045054 ends here sbachu*/
3242: hr_utility.trace('l_no_of_payroll_run is ' || l_no_of_payroll_run);
3243:
3244: ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count;
3245: ln_footnote_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count;
3246:
3243:
3244: ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count;
3245: ln_footnote_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count;
3246:
3247: hr_utility.trace('ln_index :'||to_char(ln_index));
3248: hr_utility.trace('ln_footnote_index :'||to_char(ln_footnote_index));
3249:
3250: if ((l_no_of_payroll_run > 0) and
3251: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3244: ln_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count;
3245: ln_footnote_index := pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.count;
3246:
3247: hr_utility.trace('ln_index :'||to_char(ln_index));
3248: hr_utility.trace('ln_footnote_index :'||to_char(ln_footnote_index));
3249:
3250: if ((l_no_of_payroll_run > 0) and
3251: ((l_has_been_paid = 'Y') or (ln_no_gross_earnings <> 0)) ) then
3252:
3255: := gen_rl2_slip_no(l_transmitter_id,l_asgid);*/
3256: for i in 1 .. l_count
3257: loop
3258:
3259: hr_utility.trace('in the employee info archive loop');
3260: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3261: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3262: /*
3263: lv_BoxL_excess_amt := '0';
3256: for i in 1 .. l_count
3257: loop
3258:
3259: hr_utility.trace('in the employee info archive loop');
3260: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3261: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3262: /*
3263: lv_BoxL_excess_amt := '0';
3264: lv_BoxO_excess_amt := '0';
3257: loop
3258:
3259: hr_utility.trace('in the employee info archive loop');
3260: hr_utility.trace('Balance name is '|| l_balance_type_tab(i));
3261: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3262: /*
3263: lv_BoxL_excess_amt := '0';
3264: lv_BoxO_excess_amt := '0';
3265: */
3267: if l_balance_type_tab(i) =
3268: 'Life Annuity Payments registered plan' then
3269: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info3
3270: := l_user_entity_value_tab(i);
3271: hr_utility.trace('ln_index :'||to_char(ln_index));
3272:
3273: elsif l_balance_type_tab(i) =
3274: 'Life Annuity Payments Unregistered plan' then
3275:
3285:
3286: elsif l_balance_type_tab(i) = 'Other Payments' then
3287: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3288: := l_user_entity_value_tab(i);
3289: hr_utility.trace('Box C :'||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
3290:
3291: elsif l_balance_type_tab(i) =
3292: 'Refund of RRSP Premiums paid to surviving spouse' then
3293: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info6
3371:
3372: end if;
3373:
3374:
3375: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3376:
3377: if to_number(nvl(l_user_entity_value_tab(i),'0')) < 0 then
3378:
3379: hr_utility.trace('Negative balance exists');
3375: hr_utility.trace('value tab is '|| l_user_entity_value_tab(i));
3376:
3377: if to_number(nvl(l_user_entity_value_tab(i),'0')) < 0 then
3378:
3379: hr_utility.trace('Negative balance exists');
3380: l_negative_balance_exists := 'Y';
3381: end if;
3382:
3383: if l_user_entity_value_tab(i) <> 0 then
3392:
3393: l_footnote_amount
3394: := to_number(nvl(l_user_entity_value_tab(i),0));
3395: ln_footnote_index := ln_footnote_index;
3396: hr_utility.trace(' Box A ln_footnote_index :'
3397: ||to_char(ln_footnote_index));
3398:
3399: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3400: (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3409: (ln_footnote_index).act_info6 := 'RL2';
3410:
3411: if l_footnote_amount < 0 then
3412:
3413: hr_utility.trace('Negative balance exists');
3414: l_negative_balance_exists := 'Y';
3415: end if;
3416:
3417: end if; /* end if for chk_rl2_footnote */
3424: ('Benefits from RRSP RRIF DPSP and Annuities',
3425: 'Other Payments') then
3426: begin
3427:
3428: hr_utility.trace('RL2 Footnote archive start ');
3429: lv_footnote_bal := l_footnote_balance_type_tab;
3430: l_footnote_code := NULL;
3431: old_l_footnote_code := NULL;
3432: l_footnote_amount := 0;
3431: old_l_footnote_code := NULL;
3432: l_footnote_amount := 0;
3433:
3434: open c_footnote_info(lv_footnote_bal);
3435: hr_utility.trace('lv_footnote_bal is '||lv_footnote_bal);
3436:
3437: loop
3438: fetch c_footnote_info into l_footnote_code,
3439: l_footnote_balance;
3438: fetch c_footnote_info into l_footnote_code,
3439: l_footnote_balance;
3440: exit when c_footnote_info%NOTFOUND;
3441:
3442: hr_utility.trace('l_footnote_amount_balance is '||
3443: l_footnote_balance);
3444: hr_utility.trace('l_footnote_code is '||
3445: l_footnote_code);
3446:
3440: exit when c_footnote_info%NOTFOUND;
3441:
3442: hr_utility.trace('l_footnote_amount_balance is '||
3443: l_footnote_balance);
3444: hr_utility.trace('l_footnote_code is '||
3445: l_footnote_code);
3446:
3447: if ( l_footnote_code <> old_l_footnote_code or
3448: old_l_footnote_code is null )
3448: old_l_footnote_code is null )
3449: then
3450: if old_l_footnote_code is not null then
3451:
3452: hr_utility.trace('old_l_footnote_code is '||
3453: old_l_footnote_code);
3454: hr_utility.trace('l_footnote_amount is '||
3455: to_char(l_footnote_amount));
3456:
3450: if old_l_footnote_code is not null then
3451:
3452: hr_utility.trace('old_l_footnote_code is '||
3453: old_l_footnote_code);
3454: hr_utility.trace('l_footnote_amount is '||
3455: to_char(l_footnote_amount));
3456:
3457: if chk_rl2_footnote(old_l_footnote_code) and
3458: l_footnote_amount <> 0 then
3456:
3457: if chk_rl2_footnote(old_l_footnote_code) and
3458: l_footnote_amount <> 0 then
3459:
3460: hr_utility.trace('RL2 footnote archiving ');
3461: ln_footnote_index := ln_footnote_index + 1;
3462: hr_utility.trace('old_l_ftcode ln_footnote_index :'
3463: ||to_char(ln_footnote_index));
3464:
3458: l_footnote_amount <> 0 then
3459:
3460: hr_utility.trace('RL2 footnote archiving ');
3461: ln_footnote_index := ln_footnote_index + 1;
3462: hr_utility.trace('old_l_ftcode ln_footnote_index :'
3463: ||to_char(ln_footnote_index));
3464:
3465: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3466: (ln_footnote_index).action_info_category
3481: (ln_footnote_index).act_info6 := 'RL2';
3482:
3483: if l_footnote_amount < 0 then
3484:
3485: hr_utility.trace('Negative balance exists');
3486: l_negative_balance_exists := 'Y';
3487: end if;
3488:
3489: end if;/* end if for chk_rl2_footnote */
3501: -- get the footnote_balance
3502:
3503: open c_all_gres_for_footnote(p_assactid);
3504: loop
3505: hr_utility.trace('Fetching all GREs for footnotes');
3506: fetch c_all_gres_for_footnote into l_tax_unit_id,
3507: l_reporting_type;
3508: exit when c_all_gres_for_footnote%NOTFOUND;
3509:
3506: fetch c_all_gres_for_footnote into l_tax_unit_id,
3507: l_reporting_type;
3508: exit when c_all_gres_for_footnote%NOTFOUND;
3509:
3510: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
3511: hr_utility.trace('Asgid is ' || l_asgid);
3512: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3513: hr_utility.trace('Effective date is '|| p_effective_date);
3514:
3507: l_reporting_type;
3508: exit when c_all_gres_for_footnote%NOTFOUND;
3509:
3510: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
3511: hr_utility.trace('Asgid is ' || l_asgid);
3512: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3513: hr_utility.trace('Effective date is '|| p_effective_date);
3514:
3515: begin
3508: exit when c_all_gres_for_footnote%NOTFOUND;
3509:
3510: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
3511: hr_utility.trace('Asgid is ' || l_asgid);
3512: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3513: hr_utility.trace('Effective date is '|| p_effective_date);
3514:
3515: begin
3516: open c_get_max_asg_act_id(to_number(lv_serial_number),
3509:
3510: hr_utility.trace('Tax unit id is ' || l_tax_unit_id);
3511: hr_utility.trace('Asgid is ' || l_asgid);
3512: hr_utility.trace('Reporting_type is ' || l_reporting_type);
3513: hr_utility.trace('Effective date is '|| p_effective_date);
3514:
3515: begin
3516: open c_get_max_asg_act_id(to_number(lv_serial_number),
3517: l_tax_unit_id,
3519: l_year_end);
3520: fetch c_get_max_asg_act_id into l_aaid;
3521: close c_get_max_asg_act_id;
3522:
3523: hr_utility.trace('l_aaid is ' || l_aaid);
3524: hr_utility.trace('l_count is ' || l_count);
3525:
3526: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3527:
3520: fetch c_get_max_asg_act_id into l_aaid;
3521: close c_get_max_asg_act_id;
3522:
3523: hr_utility.trace('l_aaid is ' || l_aaid);
3524: hr_utility.trace('l_count is ' || l_count);
3525:
3526: l_no_of_payroll_run := l_no_of_payroll_run + 1;
3527:
3528: if ( l_tax_unit_id <> l_prev_tax_unit_id or
3546: l_business_group_id,
3547: 'QC'
3548: ),0) ;
3549:
3550: hr_utility.trace('l_footnote_amount is '
3551: || to_char(l_footnote_amount));
3552: end if;
3553:
3554: l_prev_tax_unit_id := l_tax_unit_id ;
3554: l_prev_tax_unit_id := l_tax_unit_id ;
3555: exception
3556: when no_data_found then
3557: /* need a pop-message asgid has not payrollrun in tx*/
3558: hr_utility.trace('This Tax unit id has no payroll run'
3559: ||' so skip it');
3560: end;
3561: end loop;
3562: close c_all_gres_for_footnote;
3564: -- end of getting balance
3565:
3566: if l_footnote_amount <> 0 then
3567: l_no_of_fn_codes := l_no_of_fn_codes + 1;
3568: hr_utility.trace('l_no_of_fn_codes is '
3569: || l_no_of_fn_codes);
3570: end if;
3571:
3572: end loop; -- c_footnote_info loop
3575: -- Archiving the last footnote code and amount
3576: if chk_rl2_footnote(l_footnote_code) and
3577: l_footnote_amount <> 0 then
3578:
3579: hr_utility.trace('p_assactid is ' ||to_char(p_assactid));
3580: hr_utility.trace('before ftnote archive l_footnote_code is '
3581: || l_footnote_code);
3582: hr_utility.trace('l_footnote_amount is '
3583: || to_char(l_footnote_amount));
3576: if chk_rl2_footnote(l_footnote_code) and
3577: l_footnote_amount <> 0 then
3578:
3579: hr_utility.trace('p_assactid is ' ||to_char(p_assactid));
3580: hr_utility.trace('before ftnote archive l_footnote_code is '
3581: || l_footnote_code);
3582: hr_utility.trace('l_footnote_amount is '
3583: || to_char(l_footnote_amount));
3584:
3578:
3579: hr_utility.trace('p_assactid is ' ||to_char(p_assactid));
3580: hr_utility.trace('before ftnote archive l_footnote_code is '
3581: || l_footnote_code);
3582: hr_utility.trace('l_footnote_amount is '
3583: || to_char(l_footnote_amount));
3584:
3585: hr_utility.trace('RL2 footnote archiving ');
3586: ln_footnote_index := ln_footnote_index + 1;
3581: || l_footnote_code);
3582: hr_utility.trace('l_footnote_amount is '
3583: || to_char(l_footnote_amount));
3584:
3585: hr_utility.trace('RL2 footnote archiving ');
3586: ln_footnote_index := ln_footnote_index + 1;
3587: hr_utility.trace('after close c_footnote_info ln_footnote_index :'
3588: ||to_char(ln_footnote_index));
3589:
3583: || to_char(l_footnote_amount));
3584:
3585: hr_utility.trace('RL2 footnote archiving ');
3586: ln_footnote_index := ln_footnote_index + 1;
3587: hr_utility.trace('after close c_footnote_info ln_footnote_index :'
3588: ||to_char(ln_footnote_index));
3589:
3590: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3591: (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3599: (ln_footnote_index).act_info6 := 'RL2';
3600:
3601: if l_footnote_amount < 0 then
3602:
3603: hr_utility.trace('Negative balance exists');
3604: l_negative_balance_exists := 'Y';
3605: end if;
3606:
3607: end if;/* end if for chk_rl2_footnote */
3606:
3607: end if;/* end if for chk_rl2_footnote */
3608:
3609: end;
3610: hr_utility.trace('RL2 Footnote archive end ');
3611: end if; /* end if for l_footnote_balance_type_tab in validation */
3612: -- End of footnote archiving
3613:
3614: end if; /* end if for l_user_entity_value_tab(i) <>0 */
3615:
3616: end loop; /* end loop for plsql table balances */
3617:
3618: /* Bug#3358604 Adding Box L,O excess Amount to Box C */
3619: hr_utility.trace('lv_BoxL_excess_amt : '||lv_BoxL_excess_amt);
3620: hr_utility.trace('lv_BoxO_excess_amt : '||lv_BoxO_excess_amt);
3621:
3622: if ((lv_BoxL_Excess_amt > 0) or (lv_BoxO_Excess_amt > 0)) then
3623: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3616: end loop; /* end loop for plsql table balances */
3617:
3618: /* Bug#3358604 Adding Box L,O excess Amount to Box C */
3619: hr_utility.trace('lv_BoxL_excess_amt : '||lv_BoxL_excess_amt);
3620: hr_utility.trace('lv_BoxO_excess_amt : '||lv_BoxO_excess_amt);
3621:
3622: if ((lv_BoxL_Excess_amt > 0) or (lv_BoxO_Excess_amt > 0)) then
3623: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3624: := to_char(NVL(to_number(pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5),0)
3622: if ((lv_BoxL_Excess_amt > 0) or (lv_BoxO_Excess_amt > 0)) then
3623: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5
3624: := to_char(NVL(to_number(pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5),0)
3625: + to_number(lv_BoxL_excess_amt) + to_number(lv_BoxO_excess_amt));
3626: hr_utility.trace('Box C : '||pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info5);
3627: end if;
3628:
3629: /* Commented out for Bug 15997380
3630: Set the Negative Balance Flag for Archiving
3630: Set the Negative Balance Flag for Archiving
3631: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info30
3632: := l_negative_balance_exists; */
3633:
3634: hr_utility.trace('after loop act_info4 is: '
3635: || pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info4);
3636:
3637: end if; /* end if for ((l_no_of_payroll_run >0) and (l_has_been_paid='Y'))*/
3638:
3728: l_per_eit_description;
3729:
3730: -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
3731: ln_footnote_index := ln_footnote_index + 1;
3732: hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
3733: ||to_char(ln_footnote_index));
3734:
3735: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3736: (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3751: l_pre_description;
3752:
3753: -- Added Source of Income 'Other' to be archived as footnote Bug#3531136
3754: ln_footnote_index := ln_footnote_index + 1;
3755: hr_utility.trace('Archiving Source of Income Other as Footnote ln_footnote_index :'
3756: ||to_char(ln_footnote_index));
3757:
3758: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data
3759: (ln_footnote_index).action_info_category := 'CA FOOTNOTES';
3769: l_rl2_source_of_income := l_pre_source_of_income;
3770: end if;
3771: end if;
3772:
3773: hr_utility.trace('Before counter of asgid '|| l_asgid);
3774:
3775: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).action_info_category
3776: := 'CAEOY RL2 EMPLOYEE INFO';
3777:
3784: -- RL2 Box N SIN of Spouse
3785: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info16
3786: := l_beneficiary_sin;
3787:
3788: hr_utility.trace('Employee Info ln_index: '||to_char(ln_index));
3789: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info18
3790: := l_first_name;
3791:
3792: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data(ln_index).act_info20
3929: ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data);
3930: ln_footnote_index := null;
3931: end if;
3932:
3933: --hr_utility.trace_on('Y','SAM');
3934: hr_utility.trace('Started Provincial YE Amendment');
3935:
3936: select to_char(effective_date,'YYYY'),
3937: report_type,
3930: ln_footnote_index := null;
3931: end if;
3932:
3933: --hr_utility.trace_on('Y','SAM');
3934: hr_utility.trace('Started Provincial YE Amendment');
3935:
3936: select to_char(effective_date,'YYYY'),
3937: report_type,
3938: to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
3942: ln_fapp_pre_org_id
3943: from pay_payroll_actions
3944: where payroll_action_id = l_payroll_action_id;
3945:
3946: hr_utility.trace('lv_fapp_report_type :'||lv_fapp_report_type);
3947:
3948:
3949: /* Archive the Pre-Printed form number for the RL2
3950: Amendment Pre-Process if one exists*/
3994: open c_get_fapp_locked_action_id(p_assactid);
3995: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
3996: close c_get_fapp_locked_action_id;
3997:
3998: hr_utility.trace('RL2 Amend Action ID : '||to_char(p_assactid));
3999: hr_utility.trace('ln_fapp_locked_action_id :'|| to_char(ln_fapp_locked_action_id));
4000:
4001: -- For Bug 8921055
4002: lv_fapp_flag := compare_archive_data(p_assactid,
3995: fetch c_get_fapp_locked_action_id into ln_fapp_locked_action_id;
3996: close c_get_fapp_locked_action_id;
3997:
3998: hr_utility.trace('RL2 Amend Action ID : '||to_char(p_assactid));
3999: hr_utility.trace('ln_fapp_locked_action_id :'|| to_char(ln_fapp_locked_action_id));
4000:
4001: -- For Bug 8921055
4002: lv_fapp_flag := compare_archive_data(p_assactid,
4003: ln_fapp_locked_action_id,l_pre_printed_slip_no);
4003: ln_fapp_locked_action_id,l_pre_printed_slip_no);
4004:
4005: end if; -- report type validation for FAPP
4006:
4007: hr_utility.trace('Archiving RL2 Amendment Flag : ' || lv_fapp_flag);
4008: pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2(ln_index).act_info2
4009: := lv_fapp_flag;
4010:
4011: l_rl2_seq_number := gen_rl2_pdf_seq(p_assactid,
4017: loop
4018: l_rl2_seq_number := l_rl2_seq_number || '|' || gen_rl2_pdf_seq(p_assactid,
4019: to_char(p_effective_date,'YYYY'),
4020: 'ARCHIVER');
4021: hr_utility.trace('l_rl2_seq_number -- '||l_rl2_seq_number);
4022: end loop;
4023: else
4024: for l_i in 1..trunc(ln_no_fi/ln_no_fi_per_slip)
4025: loop
4025: loop
4026: l_rl2_seq_number := l_rl2_seq_number || '|' || gen_rl2_pdf_seq(p_assactid,
4027: to_char(p_effective_date,'YYYY'),
4028: 'ARCHIVER');
4029: hr_utility.trace('l_rl2_seq_number -- '||l_rl2_seq_number);
4030: end loop;
4031: end if;
4032: end if;
4033:
4043: ,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2);
4044: ln_index := null;
4045: end if;
4046:
4047: hr_utility.trace('End of Provincial YE Amendment PP Validation');
4048:
4049: end eoy_archive_data;
4050:
4051:
4077: l_business_group
4078: from pay_payroll_actions
4079: where payroll_action_id = pactid;
4080:
4081: hr_utility.trace('in range cursor step 1');
4082:
4083: sqlstr := 'select distinct asg.person_id
4084: from pay_all_payrolls_f ppy,
4085: pay_payroll_actions ppa,
4121: order by asg.person_id';
4122:
4123: l_archive := chk_gre_archive(pactid);
4124: if g_archive_flag = 'N' then
4125: hr_utility.trace('eoy_range_cursor archiving employer data');
4126: /* Now the archiver has provision for archiving
4127: payroll_action_level data . So make use of that */
4128: eoy_archive_gre_data(pactid,
4129: l_pre_org_id);
4126: /* Now the archiver has provision for archiving
4127: payroll_action_level data . So make use of that */
4128: eoy_archive_gre_data(pactid,
4129: l_pre_org_id);
4130: hr_utility.trace('eoy_range_cursor archived employer data');
4131: end if;
4132:
4133: end eoy_range_cursor;
4134: end pay_ca_eoy_rl2_archive;