[Home] [Help]
232: FND_MESSAGE.SET_TOKEN('STEP',p_step, FALSE);
233: --FND_MESSAGE.RAISE_ERROR;
234: -- BHOMAN - todo have to raise some error
235: -- RAISE_APPLICATION_ERROR(-20001, 'local_error called');
236: pay_us_balance_view_pkg.debug_err('local error exception raised from proc: ' ||
237: p_procedure);
238: raise bh_workaround_local_error;
239: --
240: END local_error;
440: begin
441: -- Look to see if the group level balance is in our cache.
442: --
443: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
444: pay_us_balance_view_pkg.debug_msg( '===========================================');
445: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
446: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
447: --
448: l_count := 0;
441: -- Look to see if the group level balance is in our cache.
442: --
443: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
444: pay_us_balance_view_pkg.debug_msg( '===========================================');
445: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
446: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
447: --
448: l_count := 0;
449: l_found := FALSE;
442: --
443: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
444: pay_us_balance_view_pkg.debug_msg( '===========================================');
445: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
446: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
447: --
448: l_count := 0;
449: l_found := FALSE;
450: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
447: --
448: l_count := 0;
449: l_found := FALSE;
450: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
451: pay_us_balance_view_pkg.debug_msg(' checking internal dim tables, count: '
452: || l_count);
453: if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
454: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 20);
455: --
465: else
466: p_asg_jd_required := FALSE;
467: end if;
468: l_found := TRUE;
469: pay_us_balance_view_pkg.debug_msg(' FOUND asg level');
470: --
471: end if;
472: l_count := l_count + 1;
473: end loop;
517: l_business_group_id number;
518: --
519: begin
520: --
521: l_business_group_id := pay_us_balance_view_pkg.get_context('BUSINESS_GROUP_ID');
522: --
523: begin
524: SELECT creator_id
525: INTO l_defined_balance_id
532: exception
533: when others then
534: -- BHOMAN - added this exception to fix issues with testing
535: -- we must revisit this!!
536: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
537: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
538: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
539: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
540: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
533: when others then
534: -- BHOMAN - added this exception to fix issues with testing
535: -- we must revisit this!!
536: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
537: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
538: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
539: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
540: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
541: local_error( 'get_defined_balance', '1' );
534: -- BHOMAN - added this exception to fix issues with testing
535: -- we must revisit this!!
536: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
537: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
538: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
539: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
540: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
541: local_error( 'get_defined_balance', '1' );
542: end;
535: -- we must revisit this!!
536: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
537: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
538: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
539: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
540: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
541: local_error( 'get_defined_balance', '1' );
542: end;
543: return l_defined_balance_id;
711: l_virtual_date date;
712: l_balance_type_id number;
713: begin
714: --
715: pay_us_balance_view_pkg.debug_msg( '===========================================');
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
712: l_balance_type_id number;
713: begin
714: --
715: pay_us_balance_view_pkg.debug_msg( '===========================================');
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
713: begin
714: --
715: pay_us_balance_view_pkg.debug_msg( '===========================================');
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
714: --
715: pay_us_balance_view_pkg.debug_msg( '===========================================');
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
722: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
715: pay_us_balance_view_pkg.debug_msg( '===========================================');
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
722: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
723: --
716: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
722: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
723: --
724: l_balance_value := 0;
717: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
722: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
723: --
724: l_balance_value := 0;
725: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 10);
718: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
719: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
720: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
721: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
722: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
723: --
724: l_balance_value := 0;
725: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 10);
726: --
735: l_grp_lat_exist);
736: --
737: --
738: if l_grp_lat_exist then
739: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
740: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
741: || l_asg_data_suffix);
742: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
743: || l_asg_data_cursor);
736: --
737: --
738: if l_grp_lat_exist then
739: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
740: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
741: || l_asg_data_suffix);
742: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
743: || l_asg_data_cursor);
744: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
738: if l_grp_lat_exist then
739: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
740: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
741: || l_asg_data_suffix);
742: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
743: || l_asg_data_cursor);
744: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
745: || to_char(l_asg_vdate_cursor));
746: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
740: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
741: || l_asg_data_suffix);
742: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
743: || l_asg_data_cursor);
744: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
745: || to_char(l_asg_vdate_cursor));
746: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
747: || l_asg_balance_time);
748: if l_asg_jd_required = TRUE then
742: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
743: || l_asg_data_cursor);
744: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
745: || to_char(l_asg_vdate_cursor));
746: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
747: || l_asg_balance_time);
748: if l_asg_jd_required = TRUE then
749: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
750: else
745: || to_char(l_asg_vdate_cursor));
746: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
747: || l_asg_balance_time);
748: if l_asg_jd_required = TRUE then
749: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
750: else
751: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: FALSE');
752: end if;
753: --
747: || l_asg_balance_time);
748: if l_asg_jd_required = TRUE then
749: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
750: else
751: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: FALSE');
752: end if;
753: --
754: -- Are there latest balances available.
755: --
755: --
756: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 20);
757: l_defined_balance_id := get_defined_balance(p_balance_name,
758: l_asg_data_suffix);
759: pay_us_balance_view_pkg.debug_msg(' got defbalid: '
760: || l_defined_balance_id
761: || ' from balname: '
762: || p_balance_name
763: || ' and asg data suffix: '
761: || ' from balname: '
762: || p_balance_name
763: || ' and asg data suffix: '
764: || l_asg_data_suffix);
765: pay_us_balance_view_pkg.debug_msg(' looking for latest bals ');
766: --
767: begin
768: select ''
769: into l_dummy
785: --
786: exception
787: when no_data_found then
788: l_lat_balances := FALSE;
789: pay_us_balance_view_pkg.debug_msg(' latest balances found');
790: end;
791: --
792: if (l_lat_balances = TRUE) then
793: --
793: --
794: -- OK, we can sum the values of the assignment balances to get the
795: -- group balance.
796: --
797: pay_us_balance_view_pkg.debug_msg(' summing latest balance values');
798: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 30);
799: begin
800: --
801: -- Does the cursor require the jurisdiction code. Hence balance
805: select balance_type_id
806: into l_balance_type_id
807: from pay_defined_balances
808: where defined_balance_id = l_defined_balance_id;
809: pay_us_balance_view_pkg.debug_msg(' since jd req, retrieved bal type id of: '
810: || l_balance_type_id);
811: --
812: end if;
813:
827: -- Loop through all the contributing assignments, go get there
828: -- balance value and add onto the running total.
829: --
830: loop
831: pay_us_balance_view_pkg.debug_msg(' loop thru latest balances cursor');
832: if l_asg_data_cursor = ASG_CURSOR0 then
833: fetch asg_cur0 into l_asg_id;
834: exit when asg_cur0%notfound;
835: elsif l_asg_data_cursor = ASG_CURSOR1 then
843: --hr_utility.set_location( 'bh_taxbal.get_grp_asg_value', 40);
844: l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
845: l_asg_balance_time,
846: l_asg_vdate_cursor);
847: pay_us_balance_view_pkg.debug_msg(' got l_virtual_date: '
848: || fnd_date.date_to_canonical(p_virtual_date));
849: --
850: -- Dont cache on this get_value call because assignment_id
851: -- is changing.
851: -- is changing.
852: --
853: l_balance_value := l_balance_value +
854: -- BHOMAN - fixed param order ....
855: pay_us_balance_view_pkg.get_value(l_asg_id,
856: l_defined_balance_id,
857: l_virtual_date, 1);
858: pay_us_balance_view_pkg.debug_msg(' running sum l_balance_value: '
859: || l_balance_value);
854: -- BHOMAN - fixed param order ....
855: pay_us_balance_view_pkg.get_value(l_asg_id,
856: l_defined_balance_id,
857: l_virtual_date, 1);
858: pay_us_balance_view_pkg.debug_msg(' running sum l_balance_value: '
859: || l_balance_value);
860: end loop;
861:
862: --
881: else
882: --
883: -- No latest balances available. Run the route.
884: --
885: pay_us_balance_view_pkg.debug_msg(' no latest bals, running route');
886: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
887: l_defined_balance_id := get_defined_balance(p_balance_name,
888: p_database_suffix);
889: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
885: pay_us_balance_view_pkg.debug_msg(' no latest bals, running route');
886: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
887: l_defined_balance_id := get_defined_balance(p_balance_name,
888: p_database_suffix);
889: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
890: l_balance_value := pay_us_balance_view_pkg.get_value( p_assignment_id,
891: l_defined_balance_id,
892: p_virtual_date
893: );
886: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
887: l_defined_balance_id := get_defined_balance(p_balance_name,
888: p_database_suffix);
889: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
890: l_balance_value := pay_us_balance_view_pkg.get_value( p_assignment_id,
891: l_defined_balance_id,
892: p_virtual_date
893: );
894: end if;
895: else
896: --
897: -- Can not sum the assignment level balances, thus run group
898: -- level route.
899: pay_us_balance_view_pkg.debug_msg(' Can not sum the assignment level balances, running route');
900: --
901: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
902: l_defined_balance_id := get_defined_balance(p_balance_name,
903: p_database_suffix);
900: --
901: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
902: l_defined_balance_id := get_defined_balance(p_balance_name,
903: p_database_suffix);
904: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
905: l_balance_value := pay_us_balance_view_pkg.get_value
906: (
907: p_assignment_id,
908: l_defined_balance_id,
901: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
902: l_defined_balance_id := get_defined_balance(p_balance_name,
903: p_database_suffix);
904: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
905: l_balance_value := pay_us_balance_view_pkg.get_value
906: (
907: p_assignment_id,
908: l_defined_balance_id,
909: p_virtual_date
910: );
911: end if;
912: --
913: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 70);
914: pay_us_balance_view_pkg.debug_msg(' *** get_grp_asg_value returning l_balance_value: '
915: || l_balance_value);
916: return l_balance_value;
917: --
918: end;
931: l_balance_value number;
932: begin
933: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
934: --
935: pay_us_balance_view_pkg.debug_msg( '===========================================');
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
932: begin
933: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
934: --
935: pay_us_balance_view_pkg.debug_msg( '===========================================');
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
933: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
934: --
935: pay_us_balance_view_pkg.debug_msg( '===========================================');
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
941: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
934: --
935: pay_us_balance_view_pkg.debug_msg( '===========================================');
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
941: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
942: --
935: pay_us_balance_view_pkg.debug_msg( '===========================================');
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
941: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
942: --
943: l_balance_value := 0;
936: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
941: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
942: --
943: l_balance_value := 0;
944: --
937: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
938: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
939: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
940: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
941: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
942: --
943: l_balance_value := 0;
944: --
945: l_defined_balance_id := get_defined_balance(p_balance_name,
943: l_balance_value := 0;
944: --
945: l_defined_balance_id := get_defined_balance(p_balance_name,
946: p_database_suffix);
947: l_balance_value := pay_us_balance_view_pkg.get_value (
948: p_assignment_action_id,
949: l_defined_balance_id
950: );
951: --
966: p_assignment_action_id number default null)
967: return number is
968: begin
969: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
970: pay_us_balance_view_pkg.debug_msg( '===========================================');
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
967: return number is
968: begin
969: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
970: pay_us_balance_view_pkg.debug_msg( '===========================================');
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
968: begin
969: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
970: pay_us_balance_view_pkg.debug_msg( '===========================================');
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
969: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
970: pay_us_balance_view_pkg.debug_msg( '===========================================');
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
970: pay_us_balance_view_pkg.debug_msg( '===========================================');
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
978: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
971: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
978: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
979:
972: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
978: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
979:
980: if p_assignment_action_id is null then
973: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
978: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
979:
980: if p_assignment_action_id is null then
981: --hr_utility.set_location('bh_taxbal.get_grp_value', 20);
974: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
975: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
976: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
977: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
978: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
979:
980: if p_assignment_action_id is null then
981: --hr_utility.set_location('bh_taxbal.get_grp_value', 20);
982: return get_grp_asg_value(p_assignment_id,
1081: l_session VARCHAR2(15);
1082: --
1083: BEGIN
1084: --
1085: pay_us_balance_view_pkg.debug_msg( '===========================================');
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1082: --
1083: BEGIN
1084: --
1085: pay_us_balance_view_pkg.debug_msg( '===========================================');
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1083: BEGIN
1084: --
1085: pay_us_balance_view_pkg.debug_msg( '===========================================');
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1084: --
1085: pay_us_balance_view_pkg.debug_msg( '===========================================');
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1085: pay_us_balance_view_pkg.debug_msg( '===========================================');
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1086: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1094: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
1087: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1094: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
1095:
1088: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1094: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
1095:
1096: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1089: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1094: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
1095:
1096: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1097:
1090: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1091: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1092: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1093: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
1094: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
1095:
1096: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1097:
1098: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 10);
1097:
1098: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 10);
1099: IF p_assignment_action_id IS NOT NULL THEN
1100: -- If group level balance, call the group level balance code.
1101: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not NULL: '
1102: || p_assignment_action_id);
1103: if p_asg_type = 'GRE' then
1104: pay_us_balance_view_pkg.debug_msg(', p_asg_type GRE, calling get_grp_value');
1105: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 20);
1100: -- If group level balance, call the group level balance code.
1101: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not NULL: '
1102: || p_assignment_action_id);
1103: if p_asg_type = 'GRE' then
1104: pay_us_balance_view_pkg.debug_msg(', p_asg_type GRE, calling get_grp_value');
1105: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 20);
1106: return get_grp_value(p_assignment_id,
1107: p_virtual_date,
1108: p_balance_name,
1111: p_jurisdiction_code,
1112: p_assignment_action_id
1113: );
1114: else
1115: pay_us_balance_view_pkg.debug_msg(' p_asg_type *not* GRE, calling get_defined_balance and get_value');
1116: l_defined_balance_id := get_defined_balance(p_balance_name,
1117: p_dimension_suffix);
1118: IF p_dimension_suffix not like '%PAY%' THEN
1119: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 30);
1125: -- return p_assignment_action_id;
1126: -- end if;
1127: --
1128: --
1129: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1130: -- p_assignment_action_id
1131: -- );
1132: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1133: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1128: --
1129: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1130: -- p_assignment_action_id
1131: -- );
1132: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1133: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1134: l_defined_balance_id
1135: );
1136: ELSE /* If payments dimension then must execute DB item 395029 */
1129: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1130: -- p_assignment_action_id
1131: -- );
1132: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1133: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1134: l_defined_balance_id
1135: );
1136: ELSE /* If payments dimension then must execute DB item 395029 */
1137: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1134: l_defined_balance_id
1135: );
1136: ELSE /* If payments dimension then must execute DB item 395029 */
1137: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1138: pay_us_balance_view_pkg.debug_msg(' dimension *is* like PAY, calling get_value with *NO* caching');
1139: return pay_us_balance_view_pkg.get_value (
1140: p_assignment_action_id,
1141: l_defined_balance_id,
1142: 0,
1135: );
1136: ELSE /* If payments dimension then must execute DB item 395029 */
1137: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1138: pay_us_balance_view_pkg.debug_msg(' dimension *is* like PAY, calling get_value with *NO* caching');
1139: return pay_us_balance_view_pkg.get_value (
1140: p_assignment_action_id,
1141: l_defined_balance_id,
1142: 0,
1143: 1 );
1145: end if;
1146: --
1147: ELSE
1148: -- If group level balance, call the group level balance code.
1149: pay_us_balance_view_pkg.debug_msg(' assignment_action_id *is* NULL: ');
1150: if p_asg_type = 'GRE' then
1151: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE, calling get_grp_value');
1152: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 50);
1153: return get_grp_value(p_assignment_id,
1147: ELSE
1148: -- If group level balance, call the group level balance code.
1149: pay_us_balance_view_pkg.debug_msg(' assignment_action_id *is* NULL: ');
1150: if p_asg_type = 'GRE' then
1151: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE, calling get_grp_value');
1152: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 50);
1153: return get_grp_value(p_assignment_id,
1154: p_virtual_date,
1155: p_balance_name,
1158: p_jurisdiction_code,
1159: null
1160: );
1161: else
1162: pay_us_balance_view_pkg.debug_msg(' p_asg_type is *not* GRE, calling get_defined_balance and get_value');
1163: l_defined_balance_id := get_defined_balance(p_balance_name,
1164: p_dimension_suffix);
1165: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 60);
1166: return pay_us_balance_view_pkg.get_value (
1162: pay_us_balance_view_pkg.debug_msg(' p_asg_type is *not* GRE, calling get_defined_balance and get_value');
1163: l_defined_balance_id := get_defined_balance(p_balance_name,
1164: p_dimension_suffix);
1165: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 60);
1166: return pay_us_balance_view_pkg.get_value (
1167: p_assignment_id,
1168: l_defined_balance_id,
1169: p_virtual_date
1170: );
1191: p_jurisdiction_code varchar2 )
1192: RETURN number IS
1193: l_balance number;
1194: BEGIN
1195: pay_us_balance_view_pkg.debug_msg( '===========================================');
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1192: RETURN number IS
1193: l_balance number;
1194: BEGIN
1195: pay_us_balance_view_pkg.debug_msg( '===========================================');
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1193: l_balance number;
1194: BEGIN
1195: pay_us_balance_view_pkg.debug_msg( '===========================================');
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1194: BEGIN
1195: pay_us_balance_view_pkg.debug_msg( '===========================================');
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1195: pay_us_balance_view_pkg.debug_msg( '===========================================');
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1196: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1197: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1198: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1206: --
1199: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1206: --
1207: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1200: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1206: --
1207: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1208: IF p_jurisdiction_code IS NOT NULL THEN
1201: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1202: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1206: --
1207: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1208: IF p_jurisdiction_code IS NOT NULL THEN
1209: -- BHOMAN - review this twisted logic with LWTHOMPS:
1203: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1204: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1205: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1206: --
1207: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1208: IF p_jurisdiction_code IS NOT NULL THEN
1209: -- BHOMAN - review this twisted logic with LWTHOMPS:
1210: -- should I use 'SCHOOL%', or '%SCHOOL%', or shoud I just
1211: -- pass a tax type
1210: -- should I use 'SCHOOL%', or '%SCHOOL%', or shoud I just
1211: -- pass a tax type
1212: IF (p_balance_name like ('SCHOOL%') and
1213: length(p_jurisdiction_code) > 11) THEN
1214: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',
1215: substr(p_jurisdiction_code,1,2)||
1216: '-'||substr(p_jurisdiction_code,13,5));
1217: ELSE
1218: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1214: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',
1215: substr(p_jurisdiction_code,1,2)||
1216: '-'||substr(p_jurisdiction_code,13,5));
1217: ELSE
1218: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1219: END IF;
1220: END IF;
1221: --
1222: pay_us_balance_view_pkg.set_context('BUSINESS_GROUP_ID',
1218: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1219: END IF;
1220: END IF;
1221: --
1222: pay_us_balance_view_pkg.set_context('BUSINESS_GROUP_ID',
1223: NVL(p_business_group_id,-1) );
1224:
1225: BEGIN
1226: l_balance := call_balance_user_exit (
1234: p_jurisdiction_code);
1235: EXCEPTION
1236: when others then
1237: --
1238: pay_us_balance_view_pkg.debug_err(
1239: 'us_named_balance: call_balance_user_exit raised exception');
1240: pay_us_balance_view_pkg.debug_err(
1241: ' RETURNING NULL.');
1242: return NULL;
1236: when others then
1237: --
1238: pay_us_balance_view_pkg.debug_err(
1239: 'us_named_balance: call_balance_user_exit raised exception');
1240: pay_us_balance_view_pkg.debug_err(
1241: ' RETURNING NULL.');
1242: return NULL;
1243: END run_asg_vdate_cursor;
1244: --
1241: ' RETURNING NULL.');
1242: return NULL;
1243: END run_asg_vdate_cursor;
1244: --
1245: pay_us_balance_view_pkg.debug_err(
1246: 'us_named_balance: return balance: ' || l_balance);
1247: return l_balance;
1248: --
1249: END;
1566: --
1567: -- check time part of dimension, and calc_all_timetype_flag
1568: --
1569: ----------------
1570: if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1571: p_dimension_suffix like '%CURRENT%' THEN
1572: return NULL;
1573: end if;
1574:
1571: p_dimension_suffix like '%CURRENT%' THEN
1572: return NULL;
1573: end if;
1574:
1575: if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1576: p_dimension_suffix like '%RUN%' THEN
1577: return NULL;
1578: end if;
1579:
1576: p_dimension_suffix like '%RUN%' THEN
1577: return NULL;
1578: end if;
1579:
1580: if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1581: p_dimension_suffix like '%PYDATE%' THEN
1582: return NULL;
1583: end if;
1584:
1581: p_dimension_suffix like '%PYDATE%' THEN
1582: return NULL;
1583: end if;
1584:
1585: if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1586: p_dimension_suffix like '%PTD%' THEN
1587: return NULL;
1588: end if;
1589:
1586: p_dimension_suffix like '%PTD%' THEN
1587: return NULL;
1588: end if;
1589:
1590: if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1591: p_dimension_suffix like '%MONTH%' THEN
1592: return NULL;
1593: end if;
1594:
1591: p_dimension_suffix like '%MONTH%' THEN
1592: return NULL;
1593: end if;
1594:
1595: if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1596: p_dimension_suffix like '%QTD%' THEN
1597: return NULL;
1598: end if;
1599:
1596: p_dimension_suffix like '%QTD%' THEN
1597: return NULL;
1598: end if;
1599:
1600: if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1601: p_dimension_suffix like '%YTD%' THEN
1602: return NULL;
1603: end if;
1604:
1604:
1605: ---------------
1606: if p_dimension_suffix like ('%MONTH%') OR
1607: p_dimension_suffix like ('%PYDATE%') then
1608: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1609: if l_calc_all = 0 then
1610: pay_us_balance_view_pkg.debug_msg(
1611: 'us_named_balance_vm: dimension '
1612: || p_dimension_suffix
1606: if p_dimension_suffix like ('%MONTH%') OR
1607: p_dimension_suffix like ('%PYDATE%') then
1608: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1609: if l_calc_all = 0 then
1610: pay_us_balance_view_pkg.debug_msg(
1611: 'us_named_balance_vm: dimension '
1612: || p_dimension_suffix
1613: || ' disabled, returning NULL');
1614: return NULL;
1670: end if; -- dimension suffix
1671: --
1672: -- get asg_type/view_mode
1673: --
1674: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1675: pay_us_balance_view_pkg.debug_msg(
1676: 'us_named_balance_vm called, view_mode: ' || l_asg_type);
1677: --
1678: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1671: --
1672: -- get asg_type/view_mode
1673: --
1674: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1675: pay_us_balance_view_pkg.debug_msg(
1676: 'us_named_balance_vm called, view_mode: ' || l_asg_type);
1677: --
1678: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1679: -- TODO - verify non-null p_gre_id and p_virtual_date??
1678: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1679: -- TODO - verify non-null p_gre_id and p_virtual_date??
1680: --
1681: if l_asg_type = 'GRE' then
1682: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1683: fnd_date.date_to_canonical(p_virtual_date));
1684: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1685: then
1686: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1680: --
1681: if l_asg_type = 'GRE' then
1682: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1683: fnd_date.date_to_canonical(p_virtual_date));
1684: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1685: then
1686: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1687: fnd_date.date_to_canonical(p_virtual_date));
1688: end if;
1682: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1683: fnd_date.date_to_canonical(p_virtual_date));
1684: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1685: then
1686: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1687: fnd_date.date_to_canonical(p_virtual_date));
1688: end if;
1689: pay_us_balance_view_pkg.debug_msg(
1690: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1685: then
1686: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1687: fnd_date.date_to_canonical(p_virtual_date));
1688: end if;
1689: pay_us_balance_view_pkg.debug_msg(
1690: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1691: || fnd_date.date_to_canonical(p_virtual_date));
1692: end if;
1693:
1824: --
1825: -- check time_type, and CalcAllTimeTypes flag
1826: --
1827: --------------------
1828: if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1829: p_time_type like '%CURRENT%' THEN
1830: return NULL;
1831: end if;
1832:
1829: p_time_type like '%CURRENT%' THEN
1830: return NULL;
1831: end if;
1832:
1833: if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1834: p_time_type like '%RUN%' THEN
1835: return NULL;
1836: end if;
1837:
1834: p_time_type like '%RUN%' THEN
1835: return NULL;
1836: end if;
1837:
1838: if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1839: p_time_type like '%PYDATE%' THEN
1840: return NULL;
1841: end if;
1842:
1839: p_time_type like '%PYDATE%' THEN
1840: return NULL;
1841: end if;
1842:
1843: if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1844: p_time_type like '%PTD%' THEN
1845: return NULL;
1846: end if;
1847:
1844: p_time_type like '%PTD%' THEN
1845: return NULL;
1846: end if;
1847:
1848: if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1849: p_time_type like '%MONTH%' THEN
1850: return NULL;
1851: end if;
1852:
1849: p_time_type like '%MONTH%' THEN
1850: return NULL;
1851: end if;
1852:
1853: if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1854: p_time_type like '%QTD%' THEN
1855: return NULL;
1856: end if;
1857:
1854: p_time_type like '%QTD%' THEN
1855: return NULL;
1856: end if;
1857:
1858: if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1859: p_time_type like '%YTD%' THEN
1860: return NULL;
1861: end if;
1862:
1861: end if;
1862:
1863: ---------------------
1864: if p_time_type in ('MONTH', 'PYDATE') then
1865: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1866: if l_calc_all = 0 then
1867: pay_us_balance_view_pkg.debug_msg(
1868: 'us_tax_balance_vm, timetype '
1869: || p_time_type
1863: ---------------------
1864: if p_time_type in ('MONTH', 'PYDATE') then
1865: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1866: if l_calc_all = 0 then
1867: pay_us_balance_view_pkg.debug_msg(
1868: 'us_tax_balance_vm, timetype '
1869: || p_time_type
1870: || ' disabled, returning NULL');
1871: return NULL;
1927: end if;
1928: --
1929: -- get asg_type
1930: --
1931: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1932:
1933: pay_us_balance_view_pkg.debug_msg(
1934: 'us_tax_balance_vm called, view_mode: ' || l_asg_type);
1935: --
1929: -- get asg_type
1930: --
1931: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1932:
1933: pay_us_balance_view_pkg.debug_msg(
1934: 'us_tax_balance_vm called, view_mode: ' || l_asg_type);
1935: --
1936: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1937: -- TODO - verify non-null p_gre_id and p_virtual_date??
1936: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1937: -- TODO - verify non-null p_gre_id and p_virtual_date??
1938: --
1939: if l_asg_type = 'GRE' then
1940: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1941: fnd_date.date_to_canonical(p_virtual_date));
1942: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1943: then
1944: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1938: --
1939: if l_asg_type = 'GRE' then
1940: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1941: fnd_date.date_to_canonical(p_virtual_date));
1942: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1943: then
1944: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1945: fnd_date.date_to_canonical(p_virtual_date));
1946: end if;
1940: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1941: fnd_date.date_to_canonical(p_virtual_date));
1942: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1943: then
1944: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1945: fnd_date.date_to_canonical(p_virtual_date));
1946: end if;
1947: pay_us_balance_view_pkg.debug_msg(
1948: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1943: then
1944: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1945: fnd_date.date_to_canonical(p_virtual_date));
1946: end if;
1947: pay_us_balance_view_pkg.debug_msg(
1948: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1949: || fnd_date.date_to_canonical(p_virtual_date));
1950: end if;
1951: --
2046: BEGIN
2047: --
2048: -- Check that inputs based on lookups are valid
2049: --
2050: pay_us_balance_view_pkg.debug_msg( '===========================================');
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2047: --
2048: -- Check that inputs based on lookups are valid
2049: --
2050: pay_us_balance_view_pkg.debug_msg( '===========================================');
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2048: -- Check that inputs based on lookups are valid
2049: --
2050: pay_us_balance_view_pkg.debug_msg( '===========================================');
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2049: --
2050: pay_us_balance_view_pkg.debug_msg( '===========================================');
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2050: pay_us_balance_view_pkg.debug_msg( '===========================================');
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2051: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2052: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2053: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2054: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2062: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
2055: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2062: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
2063: --
2056: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2062: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
2063: --
2064: /*Changes for Bug#6696348*/
2057: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2062: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
2063: --
2064: /*Changes for Bug#6696348*/
2065: --
2058: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
2059: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
2060: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
2061: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
2062: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
2063: --
2064: /*Changes for Bug#6696348*/
2065: --
2066: --Check if Direct Balances needs to be used for Federal Balances
2110: WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
2111: AND lookup_code = p_tax_balance_category;
2112: --
2113: IF l_valid = 0 THEN
2114: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax bal category:' ||
2115: p_tax_balance_category);
2116: local_error('us_tax_balance',1);
2117: END IF;
2118: --
2123: AND lookup_code = p_tax_type;
2124: --
2125: IF l_valid = 0 THEN
2126: IF instr(l_tax_type,'PSD') = 0 THEN -- #11926304 Added If to support PSD tax
2127: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax type: '
2128: || p_tax_type);
2129: local_error('us_tax_balance',2);
2130: END IF; -- #11926304
2131: END IF;
2135: FROM dual
2136: WHERE p_asg_type in ('ASG','PER','GRE');
2137: --
2138: IF l_valid = 0 THEN
2139: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid asg_type: '
2140: || p_asg_type);
2141: local_error('us_tax_balance',3);
2142: END IF;
2143: --
2146: FROM dual
2147: WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE', 'ASG_PAYMENTS');
2148: --
2149: IF l_valid = 0 THEN
2150: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid time_type: '
2151: || p_time_type);
2152: local_error('us_tax_balance',4);
2153: END IF;
2154: --
2156: -- them up front
2157: --
2158: --hr_utility.set_location('pay_tax_bals_pkg',30);
2159: --
2160: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
2161: IF p_jd_context IS NOT NULL THEN
2162: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
2163: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
2164: '-'||substr(p_jd_context,13,5));
2159: --
2160: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
2161: IF p_jd_context IS NOT NULL THEN
2162: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
2163: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
2164: '-'||substr(p_jd_context,13,5));
2165: ELSE
2166: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jd_context);
2167: END IF;
2162: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
2163: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
2164: '-'||substr(p_jd_context,13,5));
2165: ELSE
2166: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jd_context);
2167: END IF;
2168: END IF;
2169: --
2170: --hr_utility.set_location('pay_tax_bals_pkg',40);
2223: --
2224: -- set the virtual date to termination date, or return 0 if terminated
2225: -- before the time period.
2226: --
2227: pay_us_balance_view_pkg.debug_msg('Assignment was terminated on : ' || l_max_date);
2228: pay_us_balance_view_pkg.debug_msg('Time period in question begins on : ' ||
2229: l_bal_start_date);
2230: --
2231: IF l_max_date < l_bal_start_date THEN
2224: -- set the virtual date to termination date, or return 0 if terminated
2225: -- before the time period.
2226: --
2227: pay_us_balance_view_pkg.debug_msg('Assignment was terminated on : ' || l_max_date);
2228: pay_us_balance_view_pkg.debug_msg('Time period in question begins on : ' ||
2229: l_bal_start_date);
2230: --
2231: IF l_max_date < l_bal_start_date THEN
2232: return 0;
2233: ELSE
2234: l_virtual_date := l_max_date;
2235: END IF;
2236: --
2237: pay_us_balance_view_pkg.debug_msg('Using new virtual date : ' || l_virtual_date);
2238: --
2239: END IF;
2240: END IF;
2241: --
2263: l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
2264: l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
2265: IF (l_tax_balance_category = 'TAXABLE' or
2266: l_tax_balance_category = 'EXCESS') THEN
2267: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2268: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2269: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2270: local_error('us_tax_balance',5);
2271: END IF;
2264: l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
2265: IF (l_tax_balance_category = 'TAXABLE' or
2266: l_tax_balance_category = 'EXCESS') THEN
2267: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2268: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2269: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2270: local_error('us_tax_balance',5);
2271: END IF;
2272: --
2265: IF (l_tax_balance_category = 'TAXABLE' or
2266: l_tax_balance_category = 'EXCESS') THEN
2267: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2268: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2269: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2270: local_error('us_tax_balance',5);
2271: END IF;
2272: --
2273: -- return 0 for currently unsupported EIC balances.
2301: if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
2302: l_tax_type = 'COUNTY' or l_tax_type = 'EIC' or l_tax_type = 'SCHOOL' or l_tax_type = 'HT' or l_tax_type = 'WC' or l_tax_type = 'WC2'
2303: or l_tax_type = 'STEIC' or instr(l_tax_type,'PSD') <> 0 ) THEN --#11926304
2304: if l_ee_or_er = 'ER' THEN
2305: pay_us_balance_view_pkg.debug_err('us_tax_balance: ER not valid for tax type: '
2306: || l_tax_type);
2307: local_error('us_tax_balance',6);
2308: else
2309: l_ee_or_er := NULL;
2309: l_ee_or_er := NULL;
2310: end if;
2311: elsif (l_tax_type = 'FUTA') THEN
2312: if l_ee_or_er = 'EE' THEN
2313: pay_us_balance_view_pkg.debug_err('us_tax_balance: EE not valid for tax type: '
2314: || l_tax_type);
2315: local_error('us_tax_balance',7);
2316: else
2317: l_ee_or_er := NULL;
2318: end if;
2319: elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
2320: l_tax_type = 'SUI') THEN
2321: if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
2322: pay_us_balance_view_pkg.debug_err('Error: EE or ER required for tax type: '
2323: || l_tax_type);
2324: local_error('us_tax_balance',8);
2325: end if;
2326: elsif (l_tax_type = 'SUI1') OR (l_tax_type = 'SDI1')THEN
2324: local_error('us_tax_balance',8);
2325: end if;
2326: elsif (l_tax_type = 'SUI1') OR (l_tax_type = 'SDI1')THEN
2327: if (l_ee_or_er <> 'EE' ) THEN
2328: pay_us_balance_view_pkg.debug_err('Error: EE required for tax type: '
2329: || l_tax_type);
2330: local_error('us_tax_balance',9);
2331: end if;
2332: end if;
2360: --
2361: -- Set up dimension strings
2362: --
2363: IF p_asg_type <> 'GRE' THEN
2364: pay_us_balance_view_pkg.debug_msg(' p_asg_type is not GRE');
2365: l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
2366: l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
2367: ELSE
2368: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE');
2364: pay_us_balance_view_pkg.debug_msg(' p_asg_type is not GRE');
2365: l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
2366: l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
2367: ELSE
2368: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE');
2369: --
2370: l_dimension_string := 'GRE_'||p_time_type;
2371: l_jd_dimension_string := 'GRE_JD_'||p_time_type;
2372: --
2379: --
2380: -- bug # gaz
2381: --
2382: IF (p_payroll_action_id is not null) THEN
2383: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is not NULL, getting assignment_action_id from cursor');
2384: begin
2385: OPEN get_pay_action_id;
2386: FETCH get_pay_action_id INTO l_assignment_action_id;
2387: CLOSE get_pay_action_id;
2386: FETCH get_pay_action_id INTO l_assignment_action_id;
2387: CLOSE get_pay_action_id;
2388: end;
2389: else
2390: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is NULL, cannot use it to get assignment_action_id');
2391: if (p_assignment_action_id is null) then
2392: pay_us_balance_view_pkg.debug_msg(' assignment_action_id is NULL, getting dummy for date mode');
2393: --
2394: -- Get a dummy assignment id to call the balance user exit in date mode.
2388: end;
2389: else
2390: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is NULL, cannot use it to get assignment_action_id');
2391: if (p_assignment_action_id is null) then
2392: pay_us_balance_view_pkg.debug_msg(' assignment_action_id is NULL, getting dummy for date mode');
2393: --
2394: -- Get a dummy assignment id to call the balance user exit in date mode.
2395: --
2396: declare
2398: l_count number;
2399: l_found boolean;
2400: check_asg number;
2401: begin
2402: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2403: fnd_date.date_to_canonical(l_virtual_date));
2404: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2405: then
2406: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2400: check_asg number;
2401: begin
2402: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2403: fnd_date.date_to_canonical(l_virtual_date));
2404: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2405: then
2406: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2407: fnd_date.date_to_canonical(p_virtual_date));
2408: end if;
2402: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2403: fnd_date.date_to_canonical(l_virtual_date));
2404: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2405: then
2406: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2407: fnd_date.date_to_canonical(p_virtual_date));
2408: end if;
2409:
2410: select business_group_id
2418: l_count := 0;
2419: l_found := FALSE;
2420: while ((l_count < g_nxt_free_asgid) AND (l_found = FALSE)) loop
2421: if (l_bg_id = g_asgid_tbl_bgid(l_count)) then
2422: pay_us_balance_view_pkg.debug_msg(' found candidate assignment_action_id in cache');
2423: --
2424: -- OK, now check that the assignment is valid as of the
2425: -- virtual date.
2426: --
2432: and p_virtual_date between paf.effective_start_date
2433: and paf.effective_end_date;
2434: --
2435: l_assignment_id := g_asgid_tbl_id(l_count);
2436: pay_us_balance_view_pkg.debug_msg(' candidate assignment_action_id '
2437: || l_assignment_id
2438: || ' is valid as of vdate');
2439: l_found := TRUE;
2440: --
2448: if (l_found = FALSE) then
2449: --
2450: -- OK, need to get an assignment from the database.
2451: --
2452: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not found in cache, going to DB');
2453: begin /* Modified the query for performance (Bug 3343982)*/
2454:
2455: select min(paa.assignment_id)
2456: into l_assignment_id
2473: g_nxt_free_asgid := g_nxt_free_asgid + 1;
2474: --
2475: exception when no_data_found then
2476: begin
2477: pay_us_balance_view_pkg.debug_err('us_tax_balance: Failed find asg id');
2478: local_error('us_tax_balance',1);
2479: --
2480: end;
2481: end;
3014: -- Some Reports Require Reporting of W2 Wages instead of
3015: -- subject. Properly this should be done with an additional
3016: -- balance type
3017: IF l_tax_balance_category = 'SUBJECT' and
3018: NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') <> 'W2' THEN
3019: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE');
3020: l_return_value := us_tax_balance('SUBJ_WHABLE',
3021: l_tax_type,
3022: p_ee_or_er,
3015: -- subject. Properly this should be done with an additional
3016: -- balance type
3017: IF l_tax_balance_category = 'SUBJECT' and
3018: NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') <> 'W2' THEN
3019: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE');
3020: l_return_value := us_tax_balance('SUBJ_WHABLE',
3021: l_tax_type,
3022: p_ee_or_er,
3023: p_time_type,
3040:
3041: --
3042: -- Note: Below is equivalent to reduced subject withholdable.
3043: --
3044: ELSIF l_tax_balance_category = 'SUBJECT' and NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
3045:
3046: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE FOR W2');
3047: l_return_value := us_tax_balance('SUBJ_WHABLE',
3048: l_tax_type,
3042: -- Note: Below is equivalent to reduced subject withholdable.
3043: --
3044: ELSIF l_tax_balance_category = 'SUBJECT' and NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
3045:
3046: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE FOR W2');
3047: l_return_value := us_tax_balance('SUBJ_WHABLE',
3048: l_tax_type,
3049: p_ee_or_er,
3050: p_time_type,
3244: l_virtual_date);
3245: END IF;
3246: END IF;
3247: --
3248: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE Returning : ' || l_return_value);
3249: --
3250: return l_return_value;
3251: --
3252: END us_tax_balance;