[Home] [Help]
252: l_procedure varchar2(20) ;
253:
254: BEGIN
255: l_procedure := 'populate_actions_ids';
256: hr_utility.set_location(l_package||l_procedure, 10);
257:
258: -- delete the plsql tables that stores max run action ids and
259: -- locked run action ids by prepayment
260: master_actions_tab.delete;
259: -- locked run action ids by prepayment
260: master_actions_tab.delete;
261: run_actions_tab.delete;
262:
263: hr_utility.set_location(l_package||l_procedure, 20);
264:
265: -- Check if the Payroll is Multiple Assignments checked or not
266: -- If yes we have to display the Person level balance for YTD field
267: -- If no we will display the assignment level YTD values
291: FETCH c_get_max_action_id INTO master_actions_tab(j).aaid;
292: CLOSE c_get_max_action_id ;
293:
294: END IF; -- p_asg_multi_flag = 'Y'
295: hr_utility.set_location(l_package||l_procedure, 30);
296:
297: -- get all locked child actions
298: hr_utility.set_location(l_package||l_procedure, 40);
299:
294: END IF; -- p_asg_multi_flag = 'Y'
295: hr_utility.set_location(l_package||l_procedure, 30);
296:
297: -- get all locked child actions
298: hr_utility.set_location(l_package||l_procedure, 40);
299:
300: OPEN c_get_all_aaid_for_mast(p_master_action_id);
301: LOOP
302: FETCH c_get_all_aaid_for_mast
312: CLOSE c_get_all_aaid_for_mast;
313:
314: EXCEPTION
315: WHEN others THEN
316: hr_utility.set_location(l_package||l_procedure,50);
317: raise_application_error(-20101, 'Error in '|| l_package||l_procedure);
318: raise;
319:
320: END populate_actions_ids;
333: v_defbal_id number;
334: l_function varchar2(16);
335: BEGIN
336: l_function :='get_defined_bal';
337: hr_utility.set_location(l_package||l_function, 10);
338:
339: SELECT defined_balance_id
340: INTO v_defbal_id
341: FROM pay_defined_balances pdb
342: WHERE pdb.balance_type_id = p_bal_id
343: AND pdb.balance_dimension_id = p_dim_id
344: AND nvl(pdb.legislation_code,'US') = 'US';
345:
346: hr_utility.set_location(l_package||l_function, 20);
347:
348: RETURN v_defbal_id;
349:
350: EXCEPTION WHEN NO_DATA_FOUND THEN
347:
348: RETURN v_defbal_id;
349:
350: EXCEPTION WHEN NO_DATA_FOUND THEN
351: hr_utility.set_location(l_package||l_function, 30);
352: RETURN -1;
353:
354: END;
355:
626: END IF;
627: END get_position_name;
628:
629: BEGIN
630: --hr_utility.trace_on(null,'SOE');
631: l_procedure := 'populate_earn_bal';
632: hr_utility.set_location(l_package||l_procedure,10);
633:
634: IF g_run_dimension_id is null THEN
628:
629: BEGIN
630: --hr_utility.trace_on(null,'SOE');
631: l_procedure := 'populate_earn_bal';
632: hr_utility.set_location(l_package||l_procedure,10);
633:
634: IF g_run_dimension_id is null THEN
635: OPEN c_get_dimension_ids('_ASG_GRE_RUN');
636: FETCH c_get_dimension_ids into g_run_dimension_id;
639: OPEN c_get_dimension_ids('_ASG_GRE_YTD');
640: FETCH c_get_dimension_ids into g_ytd_dimension_id;
641: CLOSE c_get_dimension_ids;
642: END IF;
643: hr_utility.trace('Run Dimension : ' || g_run_dimension_id ||
644: 'YTD Dimension : ' || g_ytd_dimension_id);
645:
646: -- delete earnings table
647: p_earn_tab.delete;
645:
646: -- delete earnings table
647: p_earn_tab.delete;
648: earnings_elements_tab.delete;
649: hr_utility.set_location(l_package||l_procedure,20);
650:
651: IF p_action_type in ('P','U') THEN
652:
653: IF p_balance_status = 'Y' THEN
652:
653: IF p_balance_status = 'Y' THEN
654: IF run_actions_tab.count > 0 THEN
655:
656: hr_utility.set_location(l_package||l_procedure,30);
657: -- set the session variable as we are only getting the
658: -- RUN Balance
659: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
660: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
688: := null;
689: --
690: -- See if element already exists in plsql table
691: get_position_name(l_rep_name,l_found, l_pos);
692: hr_utility.set_location(l_package||l_procedure,40);
693: IF l_found = FALSE THEN
694: l_pos := p_earn_tab.COUNT + 1;
695: p_earn_tab(l_pos).rep_name := l_rep_name;
696: p_earn_tab(l_pos).hour_val := l_hours;
704: p_earn_tab(l_pos).ytd_val:=0;
705: END IF;
706: END LOOP;
707: CLOSE c_get_pre_earn_run_rb;
708: hr_utility.set_location(l_package||l_procedure,50);
709: END IF;
710: END LOOP;
711: hr_utility.set_location(l_package||l_procedure,60);
712: END IF;
707: CLOSE c_get_pre_earn_run_rb;
708: hr_utility.set_location(l_package||l_procedure,50);
709: END IF;
710: END LOOP;
711: hr_utility.set_location(l_package||l_procedure,60);
712: END IF;
713:
714: -- Get YTD values for master action
715: IF p_earn_tab.COUNT > 0 THEN
713:
714: -- Get YTD values for master action
715: IF p_earn_tab.COUNT > 0 THEN
716:
717: hr_utility.set_location(l_package||l_procedure,70);
718:
719: -- Get the YTD value for the maximum run action stored in the
720: -- master_actions_tab plsql table
721: IF master_actions_tab.count > 0 then
719: -- Get the YTD value for the maximum run action stored in the
720: -- master_actions_tab plsql table
721: IF master_actions_tab.count > 0 then
722:
723: hr_utility.set_location(l_package||l_procedure,90);
724:
725: pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
726: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
727: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
734: LOOP
735: FETCH c_get_pre_earn_ytd_rb INTO
736: l_ytd_val,l_rep_name,l_element_type_id;
737: EXIT WHEN c_get_pre_earn_ytd_rb%NOTFOUND;
738: hr_utility.set_location(l_package||l_procedure,91);
739:
740: -- Populate Earnings after check
741: IF earnings_elements_tab.count > 0 THEN
742: IF earnings_elements_tab.exists(l_element_type_id) THEN
739:
740: -- Populate Earnings after check
741: IF earnings_elements_tab.count > 0 THEN
742: IF earnings_elements_tab.exists(l_element_type_id) THEN
743: hr_utility.trace('Element already exists in PLSQL table');
744: ELSE
745: earnings_elements_tab(l_element_type_id).element_reporting_name
746: := l_rep_name ;
747: earnings_elements_tab(l_element_type_id).element_information10
752: := null;
753: earnings_elements_tab(l_element_type_id).classification_name
754: := null;
755: END IF;
756: hr_utility.set_location(l_package||l_procedure,92);
757: ELSE
758: earnings_elements_tab(l_element_type_id).element_reporting_name
759: := l_rep_name;
760: earnings_elements_tab(l_element_type_id).element_information10
765: := null;
766: earnings_elements_tab(l_element_type_id).classification_name
767: := null;
768: END IF;
769: hr_utility.set_location(l_package||l_procedure,93);
770:
771: -- get the position of the element in the plsql table
772: get_position_name(l_rep_name,l_found, l_pos);
773: IF l_found = TRUE THEN
781: p_earn_tab(l_pos).hour_val :=0;
782: p_earn_tab(l_pos).ytd_val :=l_ytd_val;
783: END IF;
784: END LOOP;
785: hr_utility.set_location(l_package||l_procedure,94);
786: CLOSE c_get_pre_earn_ytd_rb;
787: END IF;
788: END LOOP;
789:
786: CLOSE c_get_pre_earn_ytd_rb;
787: END IF;
788: END LOOP;
789:
790: hr_utility.set_location(l_package||l_procedure,100);
791: END IF; -- master_actions_tab.count > 0
792: END IF;
793: hr_utility.set_location(l_package||l_procedure,110);
794: end if;
789:
790: hr_utility.set_location(l_package||l_procedure,100);
791: END IF; -- master_actions_tab.count > 0
792: END IF;
793: hr_utility.set_location(l_package||l_procedure,110);
794: end if;
795:
796: IF p_balance_status <> 'Y' THEN
797:
794: end if;
795:
796: IF p_balance_status <> 'Y' THEN
797:
798: hr_utility.set_location(l_package||l_procedure,120);
799:
800: IF run_actions_tab.COUNT >0 THEN
801: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
802: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
812: OPEN c_get_pay_assignment_dtl(run_actions_tab(i).aaid);
813: FETCH c_get_pay_assignment_dtl INTO l_assignment_id, l_date_earned, l_date_paid;
814: CLOSE c_get_pay_assignment_dtl;
815:
816: hr_utility.set_location(l_package||l_procedure,210);
817: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
818: hr_utility.set_location(l_package||l_procedure,220);
819: -- 4966938
820: OPEN c_get_earn_elements(l_date_paid,run_actions_tab(i).aaid);
813: FETCH c_get_pay_assignment_dtl INTO l_assignment_id, l_date_earned, l_date_paid;
814: CLOSE c_get_pay_assignment_dtl;
815:
816: hr_utility.set_location(l_package||l_procedure,210);
817: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
818: hr_utility.set_location(l_package||l_procedure,220);
819: -- 4966938
820: OPEN c_get_earn_elements(l_date_paid,run_actions_tab(i).aaid);
821: LOOP
814: CLOSE c_get_pay_assignment_dtl;
815:
816: hr_utility.set_location(l_package||l_procedure,210);
817: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
818: hr_utility.set_location(l_package||l_procedure,220);
819: -- 4966938
820: OPEN c_get_earn_elements(l_date_paid,run_actions_tab(i).aaid);
821: LOOP
822: FETCH c_get_earn_elements
841: := l_business_group_id;
842: earnings_elements_tab(l_element_type_id).classification_name
843: := l_classification_name;
844:
845: hr_utility.set_location(l_package||l_procedure,221);
846: IF l_classification_name = 'Non-payroll Payments' THEN
847: l_rep_name := l_element_reporting_name;
848: l_hours := null;
849: l_run_val := pay_balance_pkg.get_value
850: (get_defined_bal(l_element_information10,
851: g_run_dimension_id),
852: run_actions_tab(i).aaid);
853: ELSE
854: hr_utility.set_location(l_package||l_procedure,222);
855: l_rep_name := l_element_reporting_name;
856: hr_utility.set_location(l_package||l_procedure ,223);
857:
858: IF l_element_information12 is not null THEN
852: run_actions_tab(i).aaid);
853: ELSE
854: hr_utility.set_location(l_package||l_procedure,222);
855: l_rep_name := l_element_reporting_name;
856: hr_utility.set_location(l_package||l_procedure ,223);
857:
858: IF l_element_information12 is not null THEN
859: l_hours := pay_balance_pkg.get_value
860: (get_defined_bal(
864: ELSE
865: l_hours := null;
866: END IF;
867:
868: hr_utility.set_location(l_package||l_procedure,224);
869: l_run_val := pay_balance_pkg.get_value
870: (get_defined_bal(
871: to_number(l_element_information10),
872: g_run_dimension_id),
870: (get_defined_bal(
871: to_number(l_element_information10),
872: g_run_dimension_id),
873: run_actions_tab(i).aaid);
874: hr_utility.set_location(l_package||l_procedure,225);
875: END IF;
876: hr_utility.trace('Hours Val : ' || l_hours);
877: hr_utility.trace('Run Val : ' || l_run_val);
878:
872: g_run_dimension_id),
873: run_actions_tab(i).aaid);
874: hr_utility.set_location(l_package||l_procedure,225);
875: END IF;
876: hr_utility.trace('Hours Val : ' || l_hours);
877: hr_utility.trace('Run Val : ' || l_run_val);
878:
879: get_position_name(l_rep_name,l_found, l_pos);
880: IF l_found = FALSE THEN
873: run_actions_tab(i).aaid);
874: hr_utility.set_location(l_package||l_procedure,225);
875: END IF;
876: hr_utility.trace('Hours Val : ' || l_hours);
877: hr_utility.trace('Run Val : ' || l_run_val);
878:
879: get_position_name(l_rep_name,l_found, l_pos);
880: IF l_found = FALSE THEN
881: l_pos := p_earn_tab.COUNT + 1;
896: END IF;
897: END LOOP;
898: END IF;
899:
900: hr_utility.set_location(l_package||l_procedure,226);
901: IF earnings_elements_tab.COUNT > 0 THEN
902:
903: IF master_actions_tab.COUNT>0 THEN
904: pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
909:
910: FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
911: IF master_actions_tab.exists(i) THEN
912:
913: hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
914: hr_utility.set_location(l_package||l_procedure,230);
915:
916: FOR j IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
917: IF earnings_elements_tab.exists(j) and
910: FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
911: IF master_actions_tab.exists(i) THEN
912:
913: hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
914: hr_utility.set_location(l_package||l_procedure,230);
915:
916: FOR j IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
917: IF earnings_elements_tab.exists(j) and
918: earnings_elements_tab(j).element_information10 is not null THEN
915:
916: FOR j IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
917: IF earnings_elements_tab.exists(j) and
918: earnings_elements_tab(j).element_information10 is not null THEN
919: hr_utility.set_location(l_package||l_procedure,240);
920:
921: l_rep_name := earnings_elements_tab(j).element_reporting_name;
922: l_hours := null;
923: l_ytd_val := pay_balance_pkg.get_value
924: (get_defined_bal(
925: to_number(earnings_elements_tab(j).element_information10),
926: g_ytd_dimension_id),
927: master_actions_tab(i).aaid);
928: hr_utility.set_location(l_package||l_procedure,254);
929:
930: get_position_name(l_rep_name,l_found, l_pos);
931: IF l_found = TRUE THEN
932: p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
940: END IF;
941: END LOOP;
942: END IF;
943: END LOOP;
944: hr_utility.set_location(l_package||l_procedure,150);
945: END IF;
946: END IF;
947: END IF;
948:
949: ELSE -- SOE for Run is viewed
950:
951: IF p_balance_status = 'Y' THEN
952:
953: hr_utility.set_location(l_package||l_procedure,160);
954: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
955: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
956: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
957: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
975: earnings_elements_tab(l_element_type_id).classification_name := null;
976:
977: get_position_name(l_rep_name,l_found, l_pos);
978:
979: hr_utility.set_location(l_package||l_procedure,40);
980: IF l_found = FALSE THEN
981: l_pos := p_earn_tab.COUNT + 1;
982: p_earn_tab(l_pos).rep_name := l_rep_name;
983: p_earn_tab(l_pos).hour_val := l_hours;
989: p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
990: END IF;
991: END LOOP;
992: CLOSE c_get_pay_rb_elements;
993: hr_utility.set_location(l_package||l_procedure,170);
994: END IF;
995:
996: IF p_balance_status <> 'Y' THEN
997: hr_utility.set_location(l_package||l_procedure,200);
993: hr_utility.set_location(l_package||l_procedure,170);
994: END IF;
995:
996: IF p_balance_status <> 'Y' THEN
997: hr_utility.set_location(l_package||l_procedure,200);
998:
999: OPEN c_get_pay_action_details(p_assignment_action_id);
1000: FETCH c_get_pay_action_details INTO l_assignment_id
1001: ,l_assignment_action_id
1002: ,l_date_earned
1003: ,l_tax_unit_id
1004: ,l_date_paid;
1005: CLOSE c_get_pay_action_details;
1006: hr_utility.set_location(l_package||l_procedure,210);
1007:
1008: OPEN c_get_earn_elements(l_date_paid,l_assignment_action_id); -- Saurabh
1009: LOOP
1010: FETCH c_get_earn_elements INTO l_element_type_id
1013: ,l_element_information12
1014: ,l_business_group_id
1015: ,l_classification_name
1016: ,l_processing_priority;
1017: hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1014: ,l_business_group_id
1015: ,l_classification_name
1016: ,l_processing_priority;
1017: hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1015: ,l_classification_name
1016: ,l_processing_priority;
1017: hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1016: ,l_processing_priority;
1017: hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1017: hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1025: hr_utility.set_location(l_package||l_procedure,220);
1018: hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1025: hr_utility.set_location(l_package||l_procedure,220);
1026: IF earnings_elements_tab.count > 0 THEN
1019: hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020: hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1025: hr_utility.set_location(l_package||l_procedure,220);
1026: IF earnings_elements_tab.count > 0 THEN
1027:
1021: hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022: hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023: hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1025: hr_utility.set_location(l_package||l_procedure,220);
1026: IF earnings_elements_tab.count > 0 THEN
1027:
1028: hr_utility.set_location(l_package||l_procedure,230);
1029: IF earnings_elements_tab.exists(l_element_type_id) THEN
1024: EXIT WHEN c_get_earn_elements%NOTFOUND;
1025: hr_utility.set_location(l_package||l_procedure,220);
1026: IF earnings_elements_tab.count > 0 THEN
1027:
1028: hr_utility.set_location(l_package||l_procedure,230);
1029: IF earnings_elements_tab.exists(l_element_type_id) THEN
1030: hr_utility.trace('The element already exists in PLSQL table');
1031: ELSE
1032: earnings_elements_tab(l_element_type_id).element_reporting_name
1026: IF earnings_elements_tab.count > 0 THEN
1027:
1028: hr_utility.set_location(l_package||l_procedure,230);
1029: IF earnings_elements_tab.exists(l_element_type_id) THEN
1030: hr_utility.trace('The element already exists in PLSQL table');
1031: ELSE
1032: earnings_elements_tab(l_element_type_id).element_reporting_name
1033: := l_element_reporting_name ;
1034: earnings_elements_tab(l_element_type_id).element_information10
1054: END IF;
1055: --bugno 4743188
1056: IF earnings_elements_tab(l_element_type_id).classification_name
1057: = 'Non-payroll Payments' THEN
1058: hr_utility.set_location(l_package||l_procedure,240);
1059:
1060: l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1061: l_hours := null;
1062: l_run_val := pay_balance_pkg.get_value
1068: (get_defined_bal(earnings_elements_tab(l_element_type_id).element_information10,
1069: g_ytd_dimension_id),
1070: p_assignment_action_id);
1071: ELSE
1072: hr_utility.set_location(l_package||l_procedure,250);
1073: l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1074: hr_utility.set_location(l_package||l_procedure ,251);
1075:
1076: IF earnings_elements_tab(l_element_type_id).element_information12 is not null THEN
1070: p_assignment_action_id);
1071: ELSE
1072: hr_utility.set_location(l_package||l_procedure,250);
1073: l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1074: hr_utility.set_location(l_package||l_procedure ,251);
1075:
1076: IF earnings_elements_tab(l_element_type_id).element_information12 is not null THEN
1077: hr_utility.trace('Info12 : ' ||
1078: earnings_elements_tab(l_element_type_id).element_information12 ||
1073: l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1074: hr_utility.set_location(l_package||l_procedure ,251);
1075:
1076: IF earnings_elements_tab(l_element_type_id).element_information12 is not null THEN
1077: hr_utility.trace('Info12 : ' ||
1078: earnings_elements_tab(l_element_type_id).element_information12 ||
1079: 'g_run_dimension_id : ' || g_run_dimension_id);
1080: l_hours := pay_balance_pkg.get_value
1081: (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information12),
1084: ELSE
1085: l_hours := null;
1086: END IF;
1087:
1088: hr_utility.set_location(l_package||l_procedure,252);
1089: hr_utility.trace('Info10 : ' ||
1090: earnings_elements_tab(l_element_type_id).element_information10 ||
1091: 'g_run_dimension_id : ' || g_run_dimension_id);
1092:
1085: l_hours := null;
1086: END IF;
1087:
1088: hr_utility.set_location(l_package||l_procedure,252);
1089: hr_utility.trace('Info10 : ' ||
1090: earnings_elements_tab(l_element_type_id).element_information10 ||
1091: 'g_run_dimension_id : ' || g_run_dimension_id);
1092:
1093: l_run_val := pay_balance_pkg.get_value
1094: (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information10),
1095: g_run_dimension_id),
1096: p_assignment_action_id);
1097:
1098: hr_utility.set_location(l_package||l_procedure,254);
1099: l_ytd_val := pay_balance_pkg.get_value
1100: (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information10),
1101: g_ytd_dimension_id),
1102: p_assignment_action_id);
1099: l_ytd_val := pay_balance_pkg.get_value
1100: (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information10),
1101: g_ytd_dimension_id),
1102: p_assignment_action_id);
1103: hr_utility.set_location(l_package||l_procedure,256);
1104: END IF;
1105:
1106: get_position_name(l_rep_name,l_found, l_pos);
1107:
1105:
1106: get_position_name(l_rep_name,l_found, l_pos);
1107:
1108: IF l_found = TRUE THEN
1109: hr_utility.set_location(l_package||l_procedure,260);
1110: p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
1111: p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
1112: p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
1113: ELSE
1110: p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
1111: p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
1112: p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
1113: ELSE
1114: hr_utility.set_location(l_package||l_procedure,261);
1115: l_pos := p_earn_tab.count + 1;
1116: p_earn_tab(l_pos).rep_name := l_rep_name;
1117: p_earn_tab(l_pos).ytd_val := l_ytd_val;
1118: p_earn_tab(l_pos).cur_val := l_run_val;
1122:
1123:
1124: END LOOP;
1125: CLOSE c_get_earn_elements;
1126: hr_utility.set_location(l_package||l_procedure,270);
1127: /* --comments start bug 4743188
1128: IF earnings_elements_tab.count > 0 THEN
1129: FOR i IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
1130: IF earnings_elements_tab.exists(i) and
1143: (get_defined_bal(earnings_elements_tab(i).element_information10,
1144: g_ytd_dimension_id),
1145: p_assignment_action_id);
1146: ELSE
1147: hr_utility.set_location(l_package||l_procedure,250);
1148: l_rep_name := earnings_elements_tab(i).element_reporting_name;
1149: hr_utility.set_location(l_package||l_procedure ,251);
1150:
1151: IF earnings_elements_tab(i).element_information12 is not null THEN
1145: p_assignment_action_id);
1146: ELSE
1147: hr_utility.set_location(l_package||l_procedure,250);
1148: l_rep_name := earnings_elements_tab(i).element_reporting_name;
1149: hr_utility.set_location(l_package||l_procedure ,251);
1150:
1151: IF earnings_elements_tab(i).element_information12 is not null THEN
1152: hr_utility.trace('Info12 : ' ||
1153: earnings_elements_tab(i).element_information12 ||
1148: l_rep_name := earnings_elements_tab(i).element_reporting_name;
1149: hr_utility.set_location(l_package||l_procedure ,251);
1150:
1151: IF earnings_elements_tab(i).element_information12 is not null THEN
1152: hr_utility.trace('Info12 : ' ||
1153: earnings_elements_tab(i).element_information12 ||
1154: 'g_run_dimension_id : ' || g_run_dimension_id);
1155: l_hours := pay_balance_pkg.get_value
1156: (get_defined_bal(to_number(earnings_elements_tab(i).element_information12),
1159: ELSE
1160: l_hours := null;
1161: END IF;
1162:
1163: hr_utility.set_location(l_package||l_procedure,252);
1164: hr_utility.trace('Info10 : ' ||
1165: earnings_elements_tab(i).element_information10 ||
1166: 'g_run_dimension_id : ' || g_run_dimension_id);
1167:
1160: l_hours := null;
1161: END IF;
1162:
1163: hr_utility.set_location(l_package||l_procedure,252);
1164: hr_utility.trace('Info10 : ' ||
1165: earnings_elements_tab(i).element_information10 ||
1166: 'g_run_dimension_id : ' || g_run_dimension_id);
1167:
1168: l_run_val := pay_balance_pkg.get_value
1169: (get_defined_bal(to_number(earnings_elements_tab(i).element_information10),
1170: g_run_dimension_id),
1171: p_assignment_action_id);
1172:
1173: hr_utility.set_location(l_package||l_procedure,254);
1174: l_ytd_val := pay_balance_pkg.get_value
1175: (get_defined_bal(to_number(earnings_elements_tab(i).element_information10),
1176: g_ytd_dimension_id),
1177: p_assignment_action_id);
1174: l_ytd_val := pay_balance_pkg.get_value
1175: (get_defined_bal(to_number(earnings_elements_tab(i).element_information10),
1176: g_ytd_dimension_id),
1177: p_assignment_action_id);
1178: hr_utility.set_location(l_package||l_procedure,256);
1179: END IF;
1180:
1181: get_position_name(l_rep_name,l_found, l_pos);
1182: hr_utility.set_location(l_package||l_procedure,260);
1178: hr_utility.set_location(l_package||l_procedure,256);
1179: END IF;
1180:
1181: get_position_name(l_rep_name,l_found, l_pos);
1182: hr_utility.set_location(l_package||l_procedure,260);
1183: IF l_found = TRUE THEN
1184: p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
1185: p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
1186: p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
1194: END IF;
1195: END LOOP;
1196: END IF; */-- comments end
1197: END IF;
1198: hr_utility.set_location(l_package||l_procedure,248);
1199: END IF;--run/prepayment check
1200:
1201: EXCEPTION
1202: WHEN others THEN
1199: END IF;--run/prepayment check
1200:
1201: EXCEPTION
1202: WHEN others THEN
1203: hr_utility.set_location(l_package||l_procedure,290);
1204: raise_application_error(-20101, 'Error in ' || l_package||l_procedure|| ' - ' || sqlerrm);
1205: raise;
1206: END populate_earn_bal;
1207:
1381: -- The code change is part of the eBRA Enhancement of SOE Form-----
1382: -- Check Balance Status
1383: l_procedure := 'populate_fed_balance';
1384:
1385: hr_utility.set_location(l_package||l_procedure,10);
1386: -- delete the federal and deduction plsql tables
1387: fed_tab.delete;
1388:
1389: hr_utility.set_location(l_package||l_procedure,20);
1385: hr_utility.set_location(l_package||l_procedure,10);
1386: -- delete the federal and deduction plsql tables
1387: fed_tab.delete;
1388:
1389: hr_utility.set_location(l_package||l_procedure,20);
1390: IF p_action_type in ('P','U') THEN
1391:
1392: hr_utility.set_location(l_package||l_procedure,30);
1393: IF p_balance_status = 'Y' THEN
1388:
1389: hr_utility.set_location(l_package||l_procedure,20);
1390: IF p_action_type in ('P','U') THEN
1391:
1392: hr_utility.set_location(l_package||l_procedure,30);
1393: IF p_balance_status = 'Y' THEN
1394:
1395: IF run_actions_tab.COUNT>0 THEN
1396: start_cnt := run_actions_tab.FIRST;
1401: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1402: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1403: pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1404:
1405: hr_utility.set_location(l_package||l_procedure,40);
1406: FOR i IN start_cnt..end_cnt LOOP
1407: IF run_actions_tab.exists(i) THEN
1408: OPEN c_get_pre_fed_run_rb(run_actions_tab(i).aaid);
1409: LOOP
1412: ,l_run_val
1413: ,l_tax_type;
1414: EXIT WHEN c_get_pre_fed_run_rb%NOTFOUND;
1415:
1416: hr_utility.set_location(l_package||l_procedure,50);
1417: get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1418:
1419: hr_utility.set_location(l_package||l_procedure,60);
1420: IF l_found = FALSE THEN
1415:
1416: hr_utility.set_location(l_package||l_procedure,50);
1417: get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1418:
1419: hr_utility.set_location(l_package||l_procedure,60);
1420: IF l_found = FALSE THEN
1421: j := fed_tab.COUNT + 1;
1422: fed_tab(j).rep_name := l_rep_name;
1423: fed_tab(j).tax_type := l_tax_type;
1426: ELSE
1427: fed_tab(l_pos).cur_val := fed_tab(l_pos).cur_val + l_run_val;
1428: fed_tab(l_pos).ytd_val := 0;
1429: END IF;
1430: hr_utility.set_location(l_package||l_procedure,70);
1431: END LOOP;
1432: CLOSE c_get_pre_fed_run_rb;
1433: END IF;
1434: hr_utility.set_location(l_package||l_procedure,80);
1430: hr_utility.set_location(l_package||l_procedure,70);
1431: END LOOP;
1432: CLOSE c_get_pre_fed_run_rb;
1433: END IF;
1434: hr_utility.set_location(l_package||l_procedure,80);
1435: END LOOP;
1436: END IF;
1437: hr_utility.set_location(l_package||l_procedure,90);
1438: IF fed_tab.COUNT > 0 THEN
1433: END IF;
1434: hr_utility.set_location(l_package||l_procedure,80);
1435: END LOOP;
1436: END IF;
1437: hr_utility.set_location(l_package||l_procedure,90);
1438: IF fed_tab.COUNT > 0 THEN
1439:
1440: IF master_actions_tab.COUNT>0 THEN
1441: start_cnt:=master_actions_tab.FIRST;
1446: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1447: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1448: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1449:
1450: hr_utility.set_location(l_package||l_procedure,100);
1451: FOR i IN start_cnt..end_cnt LOOP
1452: IF master_actions_tab.exists(i) THEN
1453: OPEN c_get_pre_fed_ytd_rb(master_actions_tab(i).aaid);
1454: LOOP
1453: OPEN c_get_pre_fed_ytd_rb(master_actions_tab(i).aaid);
1454: LOOP
1455: FETCH c_get_pre_fed_ytd_rb into l_ytd_val,l_rep_name,l_tax_type;
1456: EXIT WHEN c_get_pre_fed_ytd_rb%NOTFOUND;
1457: hr_utility.set_location(l_package||l_procedure,110);
1458: get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1459:
1460: IF l_found = TRUE THEN
1461: fed_tab(l_pos).ytd_val := fed_tab(l_pos).ytd_val + l_ytd_val;
1464: fed_tab(k).rep_name :=l_rep_name;
1465: fed_tab(k).cur_val :=0;
1466: fed_tab(k).ytd_val :=l_ytd_val;
1467: END IF;
1468: hr_utility.set_location(l_package||l_procedure,120);
1469: END LOOP;
1470: CLOSE c_get_pre_fed_ytd_rb;
1471: END IF;
1472:
1470: CLOSE c_get_pre_fed_ytd_rb;
1471: END IF;
1472:
1473: END LOOP;
1474: hr_utility.set_location(l_package||l_procedure,120);
1475: END IF;
1476: END IF;
1477:
1478: ELSE
1476: END IF;
1477:
1478: ELSE
1479:
1480: hr_utility.set_location(l_package||l_procedure,130);
1481: IF run_actions_tab.count>0 THEN
1482: start_cnt := run_actions_tab.FIRST;
1483: end_cnt := run_actions_tab.LAST;
1484:
1497: ,l_run_val
1498: ,l_tax_type;
1499: EXIT WHEN c_get_pre_fed_run_rr%NOTFOUND;
1500:
1501: hr_utility.set_location(l_package||l_procedure,140);
1502: get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1503:
1504: IF l_found = FALSE THEN
1505: j := fed_tab.COUNT + 1;
1510: ELSE
1511: fed_tab(l_pos).cur_val := fed_tab(l_pos).cur_val + l_run_val;
1512: fed_tab(l_pos).ytd_val := 0;
1513: END IF;
1514: hr_utility.set_location(l_package||l_procedure,150);
1515: END LOOP;
1516:
1517: CLOSE c_get_pre_fed_run_rr;
1518: END LOOP;
1517: CLOSE c_get_pre_fed_run_rr;
1518: END LOOP;
1519: END IF;
1520:
1521: hr_utility.set_location(l_package||l_procedure,160);
1522: IF fed_tab.COUNT > 0 THEN
1523: IF master_actions_tab.COUNT>0 THEN
1524:
1525: start_cnt:= master_actions_tab.FIRST;
1524:
1525: start_cnt:= master_actions_tab.FIRST;
1526: end_cnt:=master_actions_tab.LAST;
1527:
1528: hr_utility.set_location(l_package||l_procedure,170);
1529: pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
1530: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1531: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1532: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1539: LOOP
1540: FETCH c_get_pre_fed_ytd_rr into l_ytd_val,l_rep_name,l_tax_type;
1541: EXIT WHEN c_get_pre_fed_ytd_rr%NOTFOUND;
1542:
1543: hr_utility.set_location(l_package||l_procedure,180);
1544: get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1545:
1546: IF l_found = TRUE THEN
1547: fed_tab(l_pos).ytd_val := fed_tab(l_pos).ytd_val + l_ytd_val;
1552: fed_tab(k).ytd_val :=l_ytd_val;
1553: END IF;
1554:
1555: END LOOP;
1556: hr_utility.set_location(l_package||l_procedure,190);
1557: CLOSE c_get_pre_fed_ytd_rr;
1558: END IF;
1559: END LOOP;
1560: END IF;
1557: CLOSE c_get_pre_fed_ytd_rr;
1558: END IF;
1559: END LOOP;
1560: END IF;
1561: hr_utility.set_location(l_package||l_procedure,200);
1562: END IF;
1563: END IF;
1564:
1565: ELSE
1563: END IF;
1564:
1565: ELSE
1566:
1567: hr_utility.set_location(l_package||l_procedure,210);
1568: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1569: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1570: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1571: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1570: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1571: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1572: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1573:
1574: hr_utility.set_location(l_package||l_procedure,220);
1575: -------------Run Federal Taxes Start-------------------------------
1576: i := 0;
1577: IF p_balance_status = 'Y' THEN
1578: hr_utility.set_location(l_package||l_procedure,230);
1574: hr_utility.set_location(l_package||l_procedure,220);
1575: -------------Run Federal Taxes Start-------------------------------
1576: i := 0;
1577: IF p_balance_status = 'Y' THEN
1578: hr_utility.set_location(l_package||l_procedure,230);
1579: OPEN get_valid_taxes_fed_rb(p_assignment_action_id) ;
1580: LOOP
1581: FETCH get_valid_taxes_fed_rb
1582: INTO fed_tab(i).rep_name,
1586:
1587: EXIT WHEN get_valid_taxes_fed_rb%NOTFOUND;
1588: i := i+1;
1589: END LOOP;
1590: hr_utility.set_location(l_package||l_procedure,240);
1591: CLOSE get_valid_taxes_fed_rb;
1592:
1593: ELSE
1594: hr_utility.set_location(l_package||l_procedure,250);
1590: hr_utility.set_location(l_package||l_procedure,240);
1591: CLOSE get_valid_taxes_fed_rb;
1592:
1593: ELSE
1594: hr_utility.set_location(l_package||l_procedure,250);
1595: OPEN get_valid_taxes_fed_rr(p_assignment_action_id) ;
1596: LOOP
1597:
1598: FETCH get_valid_taxes_fed_rr
1603:
1604: EXIT WHEN get_valid_taxes_fed_rr%NOTFOUND;
1605: i := i +1 ;
1606: END LOOP;
1607: hr_utility.set_location(l_package||l_procedure,260);
1608: CLOSE get_valid_taxes_fed_rr;
1609: END IF;
1610: END IF;
1611:
1609: END IF;
1610: END IF;
1611:
1612: -- Populate the values in dedn plsql table for SOE Form
1613: hr_utility.set_location(l_package||l_procedure,270);
1614: IF fed_tab.count > 0 THEN
1615:
1616: start_cnt := fed_tab.FIRST;
1617: end_cnt := fed_tab.LAST;
1615:
1616: start_cnt := fed_tab.FIRST;
1617: end_cnt := fed_tab.LAST;
1618:
1619: hr_utility.set_location(l_package||l_procedure,280);
1620: FOR i IN start_cnt..end_cnt LOOP
1621: IF fed_tab.exists(i) THEN
1622: fed_tab(i).rep_name := REPLACE(fed_tab(i).rep_name, 'EE ', '');
1623:
1629: -- Bug 1786497
1630: p_eic_ytd_val := fed_tab(i).ytd_val;
1631:
1632: END IF;
1633: hr_utility.set_location(l_package||l_procedure,290);
1634: p_dedn_tab(i).rep_name := fed_tab(i).rep_name;
1635: p_dedn_tab(i).cur_val := fed_tab(i).cur_val;
1636: p_dedn_tab(i).ytd_val := fed_tab(i).ytd_val ;
1637: END IF;
1634: p_dedn_tab(i).rep_name := fed_tab(i).rep_name;
1635: p_dedn_tab(i).cur_val := fed_tab(i).cur_val;
1636: p_dedn_tab(i).ytd_val := fed_tab(i).ytd_val ;
1637: END IF;
1638: hr_utility.set_location(l_package||l_procedure,300);
1639: END LOOP;
1640: END IF;
1641:
1642: EXCEPTION
1640: END IF;
1641:
1642: EXCEPTION
1643: WHEN others THEN
1644: hr_utility.set_location(l_package||l_procedure,310);
1645: raise_application_error(-20101, 'Error in ' ||l_package||l_procedure || ' - ' || sqlerrm);
1646: END populate_fed_balance;
1647:
1648:
1836: -- Start the Code : Need to Consider the Secondary Assignments Also--
1837: -- The code change is part of the eBRA Enhancement of SOE Form-----
1838: -- Check Balance Status
1839: l_procedure := 'populate_state_balance';
1840: hr_utility.set_location(l_package||l_procedure,10);
1841: -- Delete the state table
1842: state_tab.delete;
1843:
1844: IF p_action_type = 'P' OR p_action_type = 'U' THEN
1843:
1844: IF p_action_type = 'P' OR p_action_type = 'U' THEN
1845:
1846: --------State----------------
1847: hr_utility.set_location(l_package||l_procedure,20);
1848: IF p_balance_status = 'Y' THEN
1849: IF run_actions_tab.COUNT>0 THEN
1850:
1851: start_cnt := run_actions_tab.FIRST;
1856: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1857: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1858: pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1859:
1860: hr_utility.set_location(l_package||l_procedure,30);
1861:
1862: FOR i IN start_cnt..end_cnt LOOP
1863: IF run_actions_tab.exists(i) THEN
1864: OPEN c_get_pre_state_run_rb(run_actions_tab(i).aaid);
1869: l_run_val,
1870: l_tax_type;
1871: EXIT WHEN c_get_pre_state_run_rb%NOTFOUND;
1872:
1873: hr_utility.set_location(l_package||l_procedure,40);
1874: get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1875:
1876: IF l_found = FALSE THEN
1877: j := state_tab.COUNT + 1;
1884: state_tab(l_pos).cur_val := state_tab(l_pos).cur_val + l_run_val;
1885: state_tab(l_pos).ytd_val := 0;
1886: END IF;
1887: END LOOP;
1888: hr_utility.set_location(l_package||l_procedure,50);
1889: CLOSE c_get_pre_state_run_rb;
1890: END IF;
1891: END LOOP;
1892: END IF;
1889: CLOSE c_get_pre_state_run_rb;
1890: END IF;
1891: END LOOP;
1892: END IF;
1893: hr_utility.set_location(l_package||l_procedure,60);
1894:
1895: IF state_tab.COUNT > 0 THEN
1896: IF master_actions_tab.COUNT>0 THEN
1897: start_cnt:=master_actions_tab.FIRST;
1902: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1903: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1904: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1905:
1906: hr_utility.set_location(l_package||l_procedure,70);
1907: FOR i IN start_cnt..end_cnt LOOP
1908: IF master_actions_tab.exists(i) THEN
1909: OPEN c_get_pre_state_ytd_rb(master_actions_tab(i).aaid);
1910: LOOP
1910: LOOP
1911: FETCH c_get_pre_state_ytd_rb into l_ytd_val,l_rep_name,l_tax_type,l_state_abbrev;
1912: EXIT WHEN c_get_pre_state_ytd_rb%NOTFOUND;
1913:
1914: hr_utility.set_location(l_package||l_procedure,80);
1915: get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1916:
1917: IF l_found = TRUE THEN
1918: state_tab(l_pos).ytd_val := state_tab(l_pos).ytd_val + l_ytd_val;
1926: END IF;
1927: END LOOP;
1928: CLOSE c_get_pre_state_ytd_rb;
1929: END IF;
1930: hr_utility.set_location(l_package||l_procedure,90);
1931: END LOOP;
1932: END IF;
1933: END IF;
1934:
1933: END IF;
1934:
1935:
1936: ELSE -- Status Not Valid
1937: hr_utility.set_location(l_package||l_procedure,100);
1938: IF run_actions_tab.COUNT>0 THEN
1939: start_cnt := run_actions_tab.FIRST;
1940: end_cnt := run_actions_tab.LAST;
1941:
1955: l_run_val,
1956: l_tax_type;
1957: EXIT WHEN c_get_pre_state_run_rr%NOTFOUND;
1958:
1959: hr_utility.set_location(l_package||l_procedure,110);
1960: get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1961:
1962: IF l_found = FALSE THEN
1963: j := state_tab.COUNT + 1;
1970: state_tab(l_pos).cur_val := state_tab(l_pos).cur_val + l_run_val;
1971: state_tab(l_pos).ytd_val := 0;
1972: END IF;
1973: END LOOP;
1974: hr_utility.set_location(l_package||l_procedure,120);
1975: CLOSE c_get_pre_state_run_rr;
1976: END IF;
1977: END LOOP;
1978: END IF;
1975: CLOSE c_get_pre_state_run_rr;
1976: END IF;
1977: END LOOP;
1978: END IF;
1979: hr_utility.set_location(l_package||l_procedure,130);
1980:
1981: IF state_tab.COUNT > 0 THEN
1982: hr_utility.set_location(l_package||l_procedure,140);
1983: IF master_actions_tab.COUNT>0 THEN
1978: END IF;
1979: hr_utility.set_location(l_package||l_procedure,130);
1980:
1981: IF state_tab.COUNT > 0 THEN
1982: hr_utility.set_location(l_package||l_procedure,140);
1983: IF master_actions_tab.COUNT>0 THEN
1984: start_cnt:=master_actions_tab.FIRST;
1985: end_cnt:=master_actions_tab.LAST;
1986:
2000: l_tax_type,
2001: l_state_abbrev;
2002: EXIT WHEN c_get_pre_state_ytd_rr%NOTFOUND;
2003:
2004: hr_utility.set_location(l_package||l_procedure,150);
2005: get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
2006:
2007: IF l_found = TRUE THEN
2008: state_tab(l_pos).ytd_val := state_tab(l_pos).ytd_val + l_ytd_val;
2021: END IF;
2022:
2023: END IF;
2024:
2025: hr_utility.set_location(l_package||l_procedure,150);
2026: -- END IF;
2027:
2028: END IF;
2029:
2028: END IF;
2029:
2030: ELSE -- SOE for Run Action
2031:
2032: hr_utility.set_location(l_package||l_procedure,160);
2033: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2034: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2035: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2036: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2039: -------------Start State Run--------------------------------------
2040: --State taxes
2041: --Use run Balances
2042: i := 0;
2043: hr_utility.set_location(l_package||l_procedure,170);
2044: IF p_balance_status = 'Y' THEN
2045: hr_utility.set_location(l_package||l_procedure,180);
2046: OPEN get_valid_taxes_state_rb(p_assignment_action_id);
2047: LOOP
2041: --Use run Balances
2042: i := 0;
2043: hr_utility.set_location(l_package||l_procedure,170);
2044: IF p_balance_status = 'Y' THEN
2045: hr_utility.set_location(l_package||l_procedure,180);
2046: OPEN get_valid_taxes_state_rb(p_assignment_action_id);
2047: LOOP
2048: FETCH get_valid_taxes_state_rb
2049: INTO state_tab(i).state_abbrev
2055: EXIT WHEN get_valid_taxes_state_rb%NOTFOUND;
2056:
2057: i := i + 1;
2058: END LOOP;
2059: hr_utility.set_location(l_package||l_procedure,190);
2060: CLOSE get_valid_taxes_state_rb;
2061:
2062: --Use Run result
2063: ELSE
2060: CLOSE get_valid_taxes_state_rb;
2061:
2062: --Use Run result
2063: ELSE
2064: hr_utility.set_location(l_package||l_procedure,200);
2065: OPEN get_valid_taxes_state_rr(p_assignment_action_id);
2066: LOOP
2067: FETCH get_valid_taxes_state_rr
2068: INTO state_tab(i).state_abbrev
2074: EXIT WHEN get_valid_taxes_state_rr%NOTFOUND;
2075: i := i + 1;
2076: END LOOP;
2077: CLOSE get_valid_taxes_state_rr;
2078: hr_utility.set_location(l_package||l_procedure,210);
2079: END IF;
2080: END IF;
2081: hr_utility.set_location(l_package||l_procedure,220);
2082:
2077: CLOSE get_valid_taxes_state_rr;
2078: hr_utility.set_location(l_package||l_procedure,210);
2079: END IF;
2080: END IF;
2081: hr_utility.set_location(l_package||l_procedure,220);
2082:
2083: -- Populate State Table of values for SOE form
2084: IF state_tab.count > 0 THEN
2085: start_cnt := state_tab.FIRST;
2086: end_cnt := state_tab.LAST;
2087:
2088: FOR i IN start_cnt..end_cnt LOOP
2089: IF state_tab.exists(i) THEN
2090: hr_utility.set_location(l_package||l_procedure,230);
2091: state_tab(i).rep_name := REPLACE(state_tab(i).rep_name, 'EE ','');
2092: state_tab(i).rep_name := state_tab(i).rep_name||' ('||state_tab(i).state_abbrev||')';
2093:
2094: IF state_tab(i).tax_type = 'STEIC' THEN
2106: p_dedn_tab(i).ytd_val := state_tab(i).ytd_val ;
2107: END IF;
2108: END LOOP;
2109: END IF;
2110: hr_utility.set_location(l_package||l_procedure,230);
2111:
2112: EXCEPTION
2113: WHEN others THEN
2114: hr_utility.set_location(l_package||l_procedure,240);
2110: hr_utility.set_location(l_package||l_procedure,230);
2111:
2112: EXCEPTION
2113: WHEN others THEN
2114: hr_utility.set_location(l_package||l_procedure,240);
2115: raise_application_error(-20101, 'Error in '|| l_package||l_procedure || ' - ' || sqlerrm);
2116:
2117: END populate_state_balance;
2118:
2316:
2317: BEGIN
2318: l_procedure := 'populate_local_balance';
2319:
2320: hr_utility.set_location(l_package||l_procedure,10);
2321: -- delete local tables
2322: local_tab.delete;
2323:
2324: hr_utility.set_location(l_package||l_procedure,20);
2320: hr_utility.set_location(l_package||l_procedure,10);
2321: -- delete local tables
2322: local_tab.delete;
2323:
2324: hr_utility.set_location(l_package||l_procedure,20);
2325: IF p_action_type = 'P' OR p_action_type = 'U' THEN
2326:
2327: hr_utility.set_location(l_package||l_procedure,30);
2328: IF p_balance_status = 'Y' THEN
2323:
2324: hr_utility.set_location(l_package||l_procedure,20);
2325: IF p_action_type = 'P' OR p_action_type = 'U' THEN
2326:
2327: hr_utility.set_location(l_package||l_procedure,30);
2328: IF p_balance_status = 'Y' THEN
2329:
2330: IF run_actions_tab.COUNT>0 THEN
2331: start_cnt := run_actions_tab.FIRST;
2348: ,l_rep_name
2349: ,l_run_val;
2350: EXIT WHEN c_get_pre_local_run_rb%NOTFOUND;
2351:
2352: hr_utility.set_location(l_package||l_procedure,40);
2353: get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2354:
2355: IF l_found = FALSE THEN
2356: j := local_tab.COUNT + 1;
2364: local_tab(l_pos).cur_val := local_tab(l_pos).cur_val + l_run_val;
2365: local_tab(l_pos).ytd_val := 0;
2366: END IF;
2367: END LOOP;
2368: hr_utility.set_location(l_package||l_procedure,50);
2369: CLOSE c_get_pre_local_run_rb;
2370: END IF;
2371: END LOOP;
2372: END IF;
2370: END IF;
2371: END LOOP;
2372: END IF;
2373:
2374: hr_utility.set_location(l_package||l_procedure,60);
2375: IF local_tab.COUNT > 0 THEN
2376: IF master_actions_tab.COUNT>0 THEN
2377: start_cnt:=master_actions_tab.FIRST;
2378: end_cnt:=master_actions_tab.LAST;
2382: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2383: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2384: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2385:
2386: hr_utility.set_location(l_package||l_procedure,60);
2387: FOR i IN start_cnt..end_cnt LOOP
2388: IF master_actions_tab.exists(i) THEN
2389: OPEN c_get_pre_local_ytd_rb(master_actions_tab(i).aaid);
2390: LOOP
2390: LOOP
2391: FETCH c_get_pre_local_ytd_rb into l_city_name,l_ytd_val,l_juris_code,l_tax_type,l_rep_name;
2392: EXIT WHEN c_get_pre_local_ytd_rb%NOTFOUND;
2393:
2394: hr_utility.set_location(l_package||l_procedure,70);
2395: get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2396:
2397: IF l_found = TRUE THEN
2398: local_tab(l_pos).ytd_val := local_tab(l_pos).ytd_val + l_ytd_val;
2405: local_tab(k).ytd_val :=l_ytd_val;
2406: local_tab(k).city_name := l_city_name;
2407: END IF;
2408: END LOOP;
2409: hr_utility.set_location(l_package||l_procedure,80);
2410: CLOSE c_get_pre_local_ytd_rb;
2411: END IF;
2412: END LOOP;
2413: END IF;
2414: END IF;
2415:
2416: ELSE -- Invalid Local Balances for eBRA
2417:
2418: hr_utility.set_location(l_package||l_procedure,90);
2419: IF run_actions_tab.COUNT>0 THEN
2420: start_cnt := run_actions_tab.FIRST;
2421: end_cnt := run_actions_tab.LAST;
2422:
2437: ,l_rep_name
2438: ,l_run_val;
2439: EXIT WHEN c_get_pre_local_run_rr%NOTFOUND;
2440:
2441: hr_utility.set_location(l_package||l_procedure,100);
2442: get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2443:
2444: IF l_found = FALSE THEN
2445: j := local_tab.COUNT + 1;
2458: END IF;
2459: END LOOP;
2460: END IF;
2461:
2462: hr_utility.set_location(l_package||l_procedure,110);
2463: IF local_tab.COUNT > 0 THEN
2464: IF master_actions_tab.COUNT>0 THEN
2465: start_cnt := master_actions_tab.FIRST;
2466: end_cnt := master_actions_tab.LAST;
2470: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2471: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2472: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2473:
2474: hr_utility.set_location(l_package||l_procedure,120);
2475: FOR i IN start_cnt..end_cnt LOOP
2476: IF master_actions_tab.exists(i) THEN
2477: OPEN c_get_pre_local_ytd_rr(master_actions_tab(i).aaid);
2478: LOOP
2478: LOOP
2479: FETCH c_get_pre_local_ytd_rr into l_city_name,l_ytd_val,l_juris_code,l_tax_type,l_rep_name;
2480: EXIT WHEN c_get_pre_local_ytd_rr%NOTFOUND;
2481:
2482: hr_utility.set_location(l_package||l_procedure,130);
2483: get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2484:
2485: IF l_found = TRUE THEN
2486: local_tab(l_pos).ytd_val := local_tab(l_pos).ytd_val + l_ytd_val;
2498: END IF;
2499: END LOOP;
2500: END IF;
2501:
2502: hr_utility.set_location(l_package||l_procedure,140);
2503: END IF;
2504: END IF;
2505:
2506: ELSE -- SOE for Run is viewed
2504: END IF;
2505:
2506: ELSE -- SOE for Run is viewed
2507:
2508: hr_utility.set_location(l_package||l_procedure,150);
2509: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2510: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2511: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2512: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2516:
2517: ------- Start Local Taxes-------------------
2518: IF p_balance_status ='Y' THEN
2519:
2520: hr_utility.set_location(l_package||l_procedure,160);
2521: OPEN get_valid_taxes_local_rb(p_assignment_action_id);
2522: LOOP
2523: FETCH get_valid_taxes_local_rb
2524: INTO local_tab(i).city_name,
2533:
2534: END LOOP;
2535: CLOSE get_valid_taxes_local_rb;
2536: ELSE
2537: hr_utility.set_location(l_package||l_procedure,170);
2538:
2539: OPEN get_valid_taxes_local_rr(p_assignment_action_id);
2540: LOOP
2541: FETCH get_valid_taxes_local_rr
2549: EXIT WHEN get_valid_taxes_local_rr%NOTFOUND;
2550: i := i + 1;
2551:
2552: END LOOP;
2553: hr_utility.set_location(l_package||l_procedure,180);
2554: CLOSE get_valid_taxes_local_rr;
2555: END IF;
2556: hr_utility.set_location(l_package||l_procedure,190);
2557: END IF;
2552: END LOOP;
2553: hr_utility.set_location(l_package||l_procedure,180);
2554: CLOSE get_valid_taxes_local_rr;
2555: END IF;
2556: hr_utility.set_location(l_package||l_procedure,190);
2557: END IF;
2558:
2559: hr_utility.set_location(l_package||l_procedure,200);
2560: -- Populate local deduction table for SOE
2555: END IF;
2556: hr_utility.set_location(l_package||l_procedure,190);
2557: END IF;
2558:
2559: hr_utility.set_location(l_package||l_procedure,200);
2560: -- Populate local deduction table for SOE
2561: l_county_code := '000';
2562: l_county_state_code :='00';
2563:
2561: l_county_code := '000';
2562: l_county_state_code :='00';
2563:
2564: IF local_tab.count > 0 THEN
2565: hr_utility.set_location(l_package||l_procedure,210);
2566: start_cnt := local_tab.FIRST;
2567: end_cnt := local_tab.LAST;
2568:
2569: FOR i IN start_cnt..end_cnt LOOP
2569: FOR i IN start_cnt..end_cnt LOOP
2570: IF local_tab.exists(i) THEN
2571: IF local_tab(i).tax_type = 'CITY' OR local_tab(i).tax_type = 'HT' THEN
2572:
2573: hr_utility.set_location(l_package||l_procedure,220);
2574: local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ', '');
2575: local_tab(i).rep_name := local_tab(i).rep_name||' ('||local_tab(i).city_name||')';
2576:
2577: ELSIF local_tab(i).tax_type = 'COUNTY' THEN
2574: local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ', '');
2575: local_tab(i).rep_name := local_tab(i).rep_name||' ('||local_tab(i).city_name||')';
2576:
2577: ELSIF local_tab(i).tax_type = 'COUNTY' THEN
2578: hr_utility.set_location(l_package||l_procedure,230);
2579: select county_name into l_county_name
2580: from pay_us_counties
2581: where county_code = substr(local_tab(i).juris_code,4,3)
2582: and state_code = substr(local_tab(i).juris_code,1,2);
2585: local_tab(i).rep_name := local_tab(i).rep_name||' ('||l_county_name||')';
2586:
2587: -- Bug 3250653
2588: ELSIF local_tab(i).tax_type = 'SCHOOL' THEN
2589: hr_utility.set_location(l_package||l_procedure,240);
2590: select distinct school_dst_name into l_school_name
2591: from pay_us_school_dsts --Bug 3412605
2592: where school_dst_code = substr(local_tab(i).juris_code,4,5)
2593: and state_code = substr(local_tab(i).juris_code,1,2);
2597:
2598: local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ','');
2599: local_tab(i).rep_name := local_tab(i).rep_name||' ('||l_school_name||'-'||l_school_code||')';
2600: END IF;
2601: hr_utility.set_location(l_package||l_procedure,240);
2602:
2603: hr_utility.set_location(l_package||l_procedure,250);
2604: p_dedn_tab(i).rep_name := local_tab(i).rep_name;
2605: p_dedn_tab(i).cur_val := local_tab(i).cur_val;
2599: local_tab(i).rep_name := local_tab(i).rep_name||' ('||l_school_name||'-'||l_school_code||')';
2600: END IF;
2601: hr_utility.set_location(l_package||l_procedure,240);
2602:
2603: hr_utility.set_location(l_package||l_procedure,250);
2604: p_dedn_tab(i).rep_name := local_tab(i).rep_name;
2605: p_dedn_tab(i).cur_val := local_tab(i).cur_val;
2606: p_dedn_tab(i).ytd_val := local_tab(i).ytd_val ;
2607: END IF;
2606: p_dedn_tab(i).ytd_val := local_tab(i).ytd_val ;
2607: END IF;
2608: END LOOP;
2609: END IF; -- Prepayment or Quick Pay Prepayment
2610: hr_utility.set_location(l_package||l_procedure,260);
2611:
2612: EXCEPTION
2613: WHEN others THEN
2614: hr_utility.set_location(l_package||l_procedure,270);
2610: hr_utility.set_location(l_package||l_procedure,260);
2611:
2612: EXCEPTION
2613: WHEN others THEN
2614: hr_utility.set_location(l_package||l_procedure,270);
2615: raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
2616:
2617: END populate_local_balance;
2618:
2788: END; /* get_position */
2789:
2790: BEGIN
2791: l_procedure := 'populate_dedn_balance';
2792: --hr_utility.trace_on(null,'SOE');
2793: hr_utility.set_location(l_package||l_procedure,10);
2794: deduction_elements_tab.delete;
2795: p_dedn_tab.delete;
2796:
2789:
2790: BEGIN
2791: l_procedure := 'populate_dedn_balance';
2792: --hr_utility.trace_on(null,'SOE');
2793: hr_utility.set_location(l_package||l_procedure,10);
2794: deduction_elements_tab.delete;
2795: p_dedn_tab.delete;
2796:
2797: -- SOE for Prepayment/Quick pay prepayment is viewed
2795: p_dedn_tab.delete;
2796:
2797: -- SOE for Prepayment/Quick pay prepayment is viewed
2798: IF p_action_type in ('P', 'U') THEN
2799: hr_utility.set_location(l_package||l_procedure,20);
2800:
2801: IF p_pre_balance_status = 'Y' and p_aft_balance_status = 'Y' THEN
2802:
2803: IF run_actions_tab.COUNT >0 THEN
2806: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2807: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2808: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2809:
2810: hr_utility.set_location(l_package||l_procedure,30);
2811: FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
2812: IF run_actions_tab.exists(i) THEN
2813: OPEN c_get_dedn_run_rb(run_actions_tab(i).aaid);
2814: LOOP
2822: := l_rep_name ;
2823: deduction_elements_tab(l_element_type_id).element_information10
2824: := null;
2825:
2826: hr_utility.set_location(l_package||l_procedure,40);
2827: get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
2828:
2829: IF l_found = FALSE THEN
2830: l_pos := p_dedn_tab.COUNT + 1;
2840: END IF;
2841: END LOOP;
2842: END IF;
2843:
2844: hr_utility.set_location(l_package||l_procedure,50);
2845: IF p_dedn_tab.COUNT > 0 THEN
2846: hr_utility.set_location(l_package||l_procedure,60);
2847: IF master_Actions_tab.COUNT>0 THEN
2848:
2842: END IF;
2843:
2844: hr_utility.set_location(l_package||l_procedure,50);
2845: IF p_dedn_tab.COUNT > 0 THEN
2846: hr_utility.set_location(l_package||l_procedure,60);
2847: IF master_Actions_tab.COUNT>0 THEN
2848:
2849: pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
2850: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2864: -- Populate Deductions after check
2865: IF deduction_elements_tab.count > 0 THEN
2866:
2867: IF deduction_elements_tab.exists(l_element_type_id) THEN
2868: hr_utility.trace('The element already exists in PLSQL table');
2869: ELSE
2870: deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name ;
2871: deduction_elements_tab(l_element_type_id).element_information10 := null;
2872: END IF;
2873: ELSE
2874: deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name;
2875: deduction_elements_tab(l_element_type_id).element_information10 := null;
2876: END IF;
2877: hr_utility.set_location(l_package||l_procedure,70);
2878: get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
2879:
2880: IF l_found = TRUE THEN
2881: p_dedn_tab(l_pos).ytd_val := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
2895: END IF;
2896:
2897: IF p_pre_balance_status <> 'Y' or p_aft_balance_status <> 'Y' THEN
2898:
2899: hr_utility.set_location(l_package||l_procedure,80);
2900:
2901: IF run_actions_tab.COUNT>0 THEN
2902: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2903: pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
2907:
2908: FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
2909: IF run_actions_tab.exists(i) THEN
2910:
2911: hr_utility.set_location(l_package||l_procedure,90);
2912: OPEN c_get_pay_action_details(run_actions_tab(i).aaid);
2913: -- 4966938
2914: FETCH c_get_pay_action_details INTO l_assignment_id
2915: ,l_assignment_action_id
2916: ,l_date_earned
2917: ,l_tax_unit_id
2918: ,l_date_paid;
2919: CLOSE c_get_pay_action_details;
2920: hr_utility.set_location(l_package||l_procedure,210);
2921:
2922: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
2923:
2924: hr_utility.set_location(l_package||l_procedure,220);
2918: ,l_date_paid;
2919: CLOSE c_get_pay_action_details;
2920: hr_utility.set_location(l_package||l_procedure,210);
2921:
2922: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
2923:
2924: hr_utility.set_location(l_package||l_procedure,220);
2925: -- 4966938
2926: OPEN c_get_dedn_elements(l_date_paid,l_assignment_action_id);
2920: hr_utility.set_location(l_package||l_procedure,210);
2921:
2922: hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
2923:
2924: hr_utility.set_location(l_package||l_procedure,220);
2925: -- 4966938
2926: OPEN c_get_dedn_elements(l_date_paid,l_assignment_action_id);
2927: LOOP
2928: FETCH c_get_dedn_elements INTO l_element_type_id
2936: FOR i in deduction_elements_tab.first ..
2937: deduction_elements_tab.last LOOP
2938: IF deduction_elements_tab.exists(l_element_type_id) THEN
2939: l_found1 := 1;
2940: hr_utility.trace('Element already fetched from Run Bal');
2941: EXIT;
2942: ELSE
2943: l_found1 := 0;
2944: END IF;
2951: deduction_elements_tab(l_element_type_id).element_information10
2952: := l_element_information10;
2953:
2954: l_rep_name := l_element_reporting_name;
2955: hr_utility.set_location(l_package||l_procedure ,221);
2956:
2957: l_run_val := pay_balance_pkg.get_value
2958: (get_defined_bal(to_number(l_element_information10),
2959: g_run_dimension_id),
2957: l_run_val := pay_balance_pkg.get_value
2958: (get_defined_bal(to_number(l_element_information10),
2959: g_run_dimension_id),
2960: run_actions_tab(i).aaid);
2961: hr_utility.set_location(l_package||l_procedure,222);
2962: hr_utility.trace('Run Val : ' || l_run_val);
2963:
2964: get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
2965: IF l_found = FALSE THEN
2958: (get_defined_bal(to_number(l_element_information10),
2959: g_run_dimension_id),
2960: run_actions_tab(i).aaid);
2961: hr_utility.set_location(l_package||l_procedure,222);
2962: hr_utility.trace('Run Val : ' || l_run_val);
2963:
2964: get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
2965: IF l_found = FALSE THEN
2966: l_pos := p_dedn_tab.COUNT + 1;
2977: END IF;
2978: END LOOP;
2979: END IF;
2980:
2981: hr_utility.set_location(l_package||l_procedure,223);
2982: IF deduction_elements_tab.COUNT > 0 THEN
2983:
2984: IF master_actions_tab.COUNT>0 THEN
2985:
2990: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2991:
2992: FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
2993: IF master_actions_tab.exists(i) THEN
2994: hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
2995: hr_utility.set_location(l_package||l_procedure,230);
2996: FOR j IN deduction_elements_tab.first ..
2997: deduction_elements_tab.last LOOP
2998: IF deduction_elements_tab.exists(j) and
2991:
2992: FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
2993: IF master_actions_tab.exists(i) THEN
2994: hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
2995: hr_utility.set_location(l_package||l_procedure,230);
2996: FOR j IN deduction_elements_tab.first ..
2997: deduction_elements_tab.last LOOP
2998: IF deduction_elements_tab.exists(j) and
2999: deduction_elements_tab(j).element_information10 is not null THEN
2996: FOR j IN deduction_elements_tab.first ..
2997: deduction_elements_tab.last LOOP
2998: IF deduction_elements_tab.exists(j) and
2999: deduction_elements_tab(j).element_information10 is not null THEN
3000: hr_utility.set_location(l_package||l_procedure,240);
3001: l_rep_name := deduction_elements_tab(j).element_reporting_name;
3002: l_ytd_val := pay_balance_pkg.get_value
3003: (get_defined_bal(to_number(deduction_elements_tab(j).element_information10),
3004: g_ytd_dimension_id),
3002: l_ytd_val := pay_balance_pkg.get_value
3003: (get_defined_bal(to_number(deduction_elements_tab(j).element_information10),
3004: g_ytd_dimension_id),
3005: master_actions_tab(i).aaid);
3006: hr_utility.set_location(l_package||l_procedure,254);
3007: get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
3008: IF l_found = TRUE THEN
3009: p_dedn_tab(l_pos).ytd_val
3010: := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
3017: END IF;
3018: END LOOP;
3019: END IF;
3020: END LOOP;
3021: hr_utility.set_location(l_package||l_procedure,150);
3022: END IF;
3023: END IF;
3024: END IF;
3025: hr_utility.set_location(l_package||l_procedure,140);
3021: hr_utility.set_location(l_package||l_procedure,150);
3022: END IF;
3023: END IF;
3024: END IF;
3025: hr_utility.set_location(l_package||l_procedure,140);
3026:
3027: ELSE -- SOE for run actions is viewed
3028:
3029: IF p_pre_balance_status = 'Y' and p_aft_balance_status = 'Y' THEN
3026:
3027: ELSE -- SOE for run actions is viewed
3028:
3029: IF p_pre_balance_status = 'Y' and p_aft_balance_status = 'Y' THEN
3030: hr_utility.set_location(l_package||l_procedure,150);
3031: pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
3032: pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
3033: pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
3034: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
3034: pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
3035: pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
3036: deduction_elements_tab.delete;
3037: p_dedn_tab.delete;
3038: hr_utility.set_location(l_package||l_procedure,160);
3039: OPEN c_get_dedn_elements_rb(p_assignment_action_id);
3040: LOOP
3041: FETCH c_get_dedn_elements_rb INTO l_ytd_val,
3042: l_rep_name,
3047: deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name ;
3048: deduction_elements_tab(l_element_type_id).element_information10 := null;
3049:
3050: get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
3051: hr_utility.set_location(l_package||l_procedure,40);
3052: IF l_found = FALSE THEN
3053: l_pos := p_dedn_tab.COUNT + 1;
3054: p_dedn_tab(l_pos).rep_name := l_rep_name;
3055: p_dedn_tab(l_pos).cur_val := l_run_val;
3061: END LOOP;
3062: CLOSE c_get_dedn_elements_rb;
3063: END IF;
3064:
3065: hr_utility.set_location(l_package||l_procedure,170);
3066:
3067: IF p_pre_balance_status <> 'Y' or p_aft_balance_status <> 'Y' THEN
3068:
3069: OPEN c_get_pay_action_details(p_assignment_action_id);
3084: EXIT WHEN c_get_dedn_elements%NOTFOUND;
3085:
3086: IF deduction_elements_tab.count > 0 THEN
3087: IF deduction_elements_tab.exists(l_element_type_id) THEN
3088: hr_utility.trace('The element already exists in PLSQL table');
3089: ELSE
3090: deduction_elements_tab(l_element_type_id).element_reporting_name
3091: := l_element_reporting_name ;
3092: deduction_elements_tab(l_element_type_id).element_information10
3162: END IF;
3163:
3164: EXCEPTION
3165: WHEN others THEN
3166: hr_utility.set_location(l_package||l_procedure,180);
3167: raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
3168:
3169: END populate_dedn_balance;
3170:
3185: l_temp number;
3186: l_procedure varchar2(21) ;
3187: BEGIN
3188: l_procedure := 'get_max_actions_table';
3189: hr_utility.set_location(l_package||l_procedure,10);
3190: IF master_actions_tab.count >0 THEN
3191: cnt_start := master_actions_tab.first;
3192: cnt_end := master_actions_tab.last;
3193:
3190: IF master_actions_tab.count >0 THEN
3191: cnt_start := master_actions_tab.first;
3192: cnt_end := master_actions_tab.last;
3193:
3194: hr_utility.set_location(l_package||l_procedure,20);
3195:
3196: -- Sort the table in Ascending Order
3197: FOR i in cnt_start..(cnt_end-1) LOOP
3198: IF master_actions_tab.exists(i) THEN
3206: END IF;
3207: END LOOP;
3208: END IF;
3209: END LOOP;
3210: hr_utility.set_location(l_package||l_procedure,30);
3211:
3212: -- Assign the sorted max actions table to the table to be used
3213: -- by SOE Form
3214:
3218: p_max_actions_tab(i).aaid := master_actions_tab(i).aaid;
3219: END IF;
3220: END LOOP;
3221: END IF;
3222: hr_utility.set_location(l_package||l_procedure,40);
3223:
3224: EXCEPTION
3225: WHEN others THEN
3226: hr_utility.set_location(l_package||l_procedure,50);
3222: hr_utility.set_location(l_package||l_procedure,40);
3223:
3224: EXCEPTION
3225: WHEN others THEN
3226: hr_utility.set_location(l_package||l_procedure,50);
3227: raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
3228: END;
3229: END pay_us_soe_balances_pkg;