[Home] [Help]
201: FND_MESSAGE.SET_TOKEN('STEP',p_step, FALSE);
202: --FND_MESSAGE.RAISE_ERROR;
203: -- BHOMAN - todo have to raise some error
204: -- RAISE_APPLICATION_ERROR(-20001, 'local_error called');
205: pay_us_balance_view_pkg.debug_err('local error exception raised from proc: ' ||
206: p_procedure);
207: raise bh_workaround_local_error;
208: --
209: END local_error;
409: begin
410: -- Look to see if the group level balance is in our cache.
411: --
412: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
413: pay_us_balance_view_pkg.debug_msg( '===========================================');
414: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
415: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
416: --
417: l_count := 0;
410: -- Look to see if the group level balance is in our cache.
411: --
412: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
413: pay_us_balance_view_pkg.debug_msg( '===========================================');
414: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
415: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
416: --
417: l_count := 0;
418: l_found := FALSE;
411: --
412: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 10);
413: pay_us_balance_view_pkg.debug_msg( '===========================================');
414: pay_us_balance_view_pkg.debug_msg('get_asg_for_grp_lvl entry:');
415: pay_us_balance_view_pkg.debug_msg(' p_grp_dvl_dimension: ' || p_grp_dvl_dimension);
416: --
417: l_count := 0;
418: l_found := FALSE;
419: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
416: --
417: l_count := 0;
418: l_found := FALSE;
419: while ((l_count < g_nxt_free_dim) AND (l_found = FALSE)) loop
420: pay_us_balance_view_pkg.debug_msg(' checking internal dim tables, count: '
421: || l_count);
422: if (p_grp_dvl_dimension = g_dim_tbl_grp(l_count)) then
423: --hr_utility.set_location('bh_taxbal.get_asg_for_grp_lvl', 20);
424: --
434: else
435: p_asg_jd_required := FALSE;
436: end if;
437: l_found := TRUE;
438: pay_us_balance_view_pkg.debug_msg(' FOUND asg level');
439: --
440: end if;
441: l_count := l_count + 1;
442: end loop;
486: l_business_group_id number;
487: --
488: begin
489: --
490: l_business_group_id := pay_us_balance_view_pkg.get_context('BUSINESS_GROUP_ID');
491: --
492: begin
493: SELECT creator_id
494: INTO l_defined_balance_id
501: exception
502: when others then
503: -- BHOMAN - added this exception to fix issues with testing
504: -- we must revisit this!!
505: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
506: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
507: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
508: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
509: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
502: when others then
503: -- BHOMAN - added this exception to fix issues with testing
504: -- we must revisit this!!
505: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
506: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
507: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
508: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
509: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
510: local_error( 'get_defined_balance', '1' );
503: -- BHOMAN - added this exception to fix issues with testing
504: -- we must revisit this!!
505: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
506: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
507: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
508: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
509: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
510: local_error( 'get_defined_balance', '1' );
511: end;
504: -- we must revisit this!!
505: pay_us_balance_view_pkg.debug_err('get_defined_balance failed: ');
506: pay_us_balance_view_pkg.debug_err(' bal_name: ' || p_balance_name);
507: pay_us_balance_view_pkg.debug_err(' p_dimension_suffix: '|| p_dimension_suffix);
508: pay_us_balance_view_pkg.debug_err(' user_entity_name like: ' ||
509: translate(p_balance_name ||'_' ||p_dimension_suffix, ' ', '_'));
510: local_error( 'get_defined_balance', '1' );
511: end;
512: return l_defined_balance_id;
680: l_virtual_date date;
681: l_balance_type_id number;
682: begin
683: --
684: pay_us_balance_view_pkg.debug_msg( '===========================================');
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
681: l_balance_type_id number;
682: begin
683: --
684: pay_us_balance_view_pkg.debug_msg( '===========================================');
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
682: begin
683: --
684: pay_us_balance_view_pkg.debug_msg( '===========================================');
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
683: --
684: pay_us_balance_view_pkg.debug_msg( '===========================================');
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
684: pay_us_balance_view_pkg.debug_msg( '===========================================');
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
692: --
685: pay_us_balance_view_pkg.debug_msg('get_grp_asg_value entry:');
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
692: --
693: l_balance_value := 0;
686: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
692: --
693: l_balance_value := 0;
694: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 10);
687: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
688: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
689: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
690: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
691: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
692: --
693: l_balance_value := 0;
694: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 10);
695: --
704: l_grp_lat_exist);
705: --
706: --
707: if l_grp_lat_exist then
708: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
709: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
710: || l_asg_data_suffix);
711: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712: || l_asg_data_cursor);
705: --
706: --
707: if l_grp_lat_exist then
708: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
709: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
710: || l_asg_data_suffix);
711: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712: || l_asg_data_cursor);
713: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
707: if l_grp_lat_exist then
708: pay_us_balance_view_pkg.debug_msg(' l_grp_lat_exist TRUE');
709: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
710: || l_asg_data_suffix);
711: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712: || l_asg_data_cursor);
713: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
714: || to_char(l_asg_vdate_cursor));
715: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
709: pay_us_balance_view_pkg.debug_msg(' l_asg_data_suffix: '
710: || l_asg_data_suffix);
711: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712: || l_asg_data_cursor);
713: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
714: || to_char(l_asg_vdate_cursor));
715: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
716: || l_asg_balance_time);
717: if l_asg_jd_required = TRUE then
711: pay_us_balance_view_pkg.debug_msg(' l_asg_data_cursor: '
712: || l_asg_data_cursor);
713: pay_us_balance_view_pkg.debug_msg(' l_asg_vdate_cursor: '
714: || to_char(l_asg_vdate_cursor));
715: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
716: || l_asg_balance_time);
717: if l_asg_jd_required = TRUE then
718: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
719: else
714: || to_char(l_asg_vdate_cursor));
715: pay_us_balance_view_pkg.debug_msg(' l_asg_balance_time: '
716: || l_asg_balance_time);
717: if l_asg_jd_required = TRUE then
718: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
719: else
720: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: FALSE');
721: end if;
722: --
716: || l_asg_balance_time);
717: if l_asg_jd_required = TRUE then
718: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: TRUE');
719: else
720: pay_us_balance_view_pkg.debug_msg(' l_asg_jd_required: FALSE');
721: end if;
722: --
723: -- Are there latest balances available.
724: --
724: --
725: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 20);
726: l_defined_balance_id := get_defined_balance(p_balance_name,
727: l_asg_data_suffix);
728: pay_us_balance_view_pkg.debug_msg(' got defbalid: '
729: || l_defined_balance_id
730: || ' from balname: '
731: || p_balance_name
732: || ' and asg data suffix: '
730: || ' from balname: '
731: || p_balance_name
732: || ' and asg data suffix: '
733: || l_asg_data_suffix);
734: pay_us_balance_view_pkg.debug_msg(' looking for latest bals ');
735: --
736: begin
737: select ''
738: into l_dummy
754: --
755: exception
756: when no_data_found then
757: l_lat_balances := FALSE;
758: pay_us_balance_view_pkg.debug_msg(' latest balances found');
759: end;
760: --
761: if (l_lat_balances = TRUE) then
762: --
762: --
763: -- OK, we can sum the values of the assignment balances to get the
764: -- group balance.
765: --
766: pay_us_balance_view_pkg.debug_msg(' summing latest balance values');
767: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 30);
768: begin
769: --
770: -- Does the cursor require the jurisdiction code. Hence balance
774: select balance_type_id
775: into l_balance_type_id
776: from pay_defined_balances
777: where defined_balance_id = l_defined_balance_id;
778: pay_us_balance_view_pkg.debug_msg(' since jd req, retrieved bal type id of: '
779: || l_balance_type_id);
780: --
781: end if;
782:
796: -- Loop through all the contributing assignments, go get there
797: -- balance value and add onto the running total.
798: --
799: loop
800: pay_us_balance_view_pkg.debug_msg(' loop thru latest balances cursor');
801: if l_asg_data_cursor = ASG_CURSOR0 then
802: fetch asg_cur0 into l_asg_id;
803: exit when asg_cur0%notfound;
804: elsif l_asg_data_cursor = ASG_CURSOR1 then
812: --hr_utility.set_location( 'bh_taxbal.get_grp_asg_value', 40);
813: l_virtual_date := get_virtual_date(l_asg_id, p_virtual_date,
814: l_asg_balance_time,
815: l_asg_vdate_cursor);
816: pay_us_balance_view_pkg.debug_msg(' got l_virtual_date: '
817: || fnd_date.date_to_canonical(p_virtual_date));
818: --
819: -- Dont cache on this get_value call because assignment_id
820: -- is changing.
820: -- is changing.
821: --
822: l_balance_value := l_balance_value +
823: -- BHOMAN - fixed param order ....
824: pay_us_balance_view_pkg.get_value(l_asg_id,
825: l_defined_balance_id,
826: l_virtual_date, 1);
827: pay_us_balance_view_pkg.debug_msg(' running sum l_balance_value: '
828: || l_balance_value);
823: -- BHOMAN - fixed param order ....
824: pay_us_balance_view_pkg.get_value(l_asg_id,
825: l_defined_balance_id,
826: l_virtual_date, 1);
827: pay_us_balance_view_pkg.debug_msg(' running sum l_balance_value: '
828: || l_balance_value);
829: end loop;
830:
831: --
850: else
851: --
852: -- No latest balances available. Run the route.
853: --
854: pay_us_balance_view_pkg.debug_msg(' no latest bals, running route');
855: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
856: l_defined_balance_id := get_defined_balance(p_balance_name,
857: p_database_suffix);
858: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
854: pay_us_balance_view_pkg.debug_msg(' no latest bals, running route');
855: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
856: l_defined_balance_id := get_defined_balance(p_balance_name,
857: p_database_suffix);
858: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
859: l_balance_value := pay_us_balance_view_pkg.get_value( p_assignment_id,
860: l_defined_balance_id,
861: p_virtual_date
862: );
855: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 50);
856: l_defined_balance_id := get_defined_balance(p_balance_name,
857: p_database_suffix);
858: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
859: l_balance_value := pay_us_balance_view_pkg.get_value( p_assignment_id,
860: l_defined_balance_id,
861: p_virtual_date
862: );
863: end if;
864: else
865: --
866: -- Can not sum the assignment level balances, thus run group
867: -- level route.
868: pay_us_balance_view_pkg.debug_msg(' Can not sum the assignment level balances, running route');
869: --
870: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
871: l_defined_balance_id := get_defined_balance(p_balance_name,
872: p_database_suffix);
869: --
870: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
871: l_defined_balance_id := get_defined_balance(p_balance_name,
872: p_database_suffix);
873: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
874: l_balance_value := pay_us_balance_view_pkg.get_value
875: (
876: p_assignment_id,
877: l_defined_balance_id,
870: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 60);
871: l_defined_balance_id := get_defined_balance(p_balance_name,
872: p_database_suffix);
873: pay_us_balance_view_pkg.debug_msg(' def bal id: ' || l_defined_balance_id);
874: l_balance_value := pay_us_balance_view_pkg.get_value
875: (
876: p_assignment_id,
877: l_defined_balance_id,
878: p_virtual_date
879: );
880: end if;
881: --
882: --hr_utility.set_location('bh_taxbal.get_grp_asg_value', 70);
883: pay_us_balance_view_pkg.debug_msg(' *** get_grp_asg_value returning l_balance_value: '
884: || l_balance_value);
885: return l_balance_value;
886: --
887: end;
900: l_balance_value number;
901: begin
902: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
903: --
904: pay_us_balance_view_pkg.debug_msg( '===========================================');
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
901: begin
902: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
903: --
904: pay_us_balance_view_pkg.debug_msg( '===========================================');
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
902: --hr_utility.set_location('bh_taxbal.get_grp_act_value', 10);
903: --
904: pay_us_balance_view_pkg.debug_msg( '===========================================');
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
903: --
904: pay_us_balance_view_pkg.debug_msg( '===========================================');
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
911: --
904: pay_us_balance_view_pkg.debug_msg( '===========================================');
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
911: --
912: l_balance_value := 0;
905: pay_us_balance_view_pkg.debug_msg('get_grp_act_value entry:');
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
911: --
912: l_balance_value := 0;
913: --
906: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
907: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
908: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
909: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
910: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
911: --
912: l_balance_value := 0;
913: --
914: l_defined_balance_id := get_defined_balance(p_balance_name,
912: l_balance_value := 0;
913: --
914: l_defined_balance_id := get_defined_balance(p_balance_name,
915: p_database_suffix);
916: l_balance_value := pay_us_balance_view_pkg.get_value (
917: p_assignment_action_id,
918: l_defined_balance_id
919: );
920: --
935: p_assignment_action_id number default null)
936: return number is
937: begin
938: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
939: pay_us_balance_view_pkg.debug_msg( '===========================================');
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
936: return number is
937: begin
938: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
939: pay_us_balance_view_pkg.debug_msg( '===========================================');
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
937: begin
938: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
939: pay_us_balance_view_pkg.debug_msg( '===========================================');
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
938: --hr_utility.set_location('bh_taxbal.get_grp_value', 10);
939: pay_us_balance_view_pkg.debug_msg( '===========================================');
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
939: pay_us_balance_view_pkg.debug_msg( '===========================================');
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
940: pay_us_balance_view_pkg.debug_msg('get_grp_value entry:');
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
948:
941: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
948:
949: if p_assignment_action_id is null then
942: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
948:
949: if p_assignment_action_id is null then
950: --hr_utility.set_location('bh_taxbal.get_grp_value', 20);
943: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
944: pay_us_balance_view_pkg.debug_msg(' p_database_suffix: ' || p_database_suffix);
945: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
946: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
947: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
948:
949: if p_assignment_action_id is null then
950: --hr_utility.set_location('bh_taxbal.get_grp_value', 20);
951: return get_grp_asg_value(p_assignment_id,
985: l_session VARCHAR2(15);
986: --
987: BEGIN
988: --
989: pay_us_balance_view_pkg.debug_msg( '===========================================');
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
986: --
987: BEGIN
988: --
989: pay_us_balance_view_pkg.debug_msg( '===========================================');
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
987: BEGIN
988: --
989: pay_us_balance_view_pkg.debug_msg( '===========================================');
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
988: --
989: pay_us_balance_view_pkg.debug_msg( '===========================================');
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
989: pay_us_balance_view_pkg.debug_msg( '===========================================');
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
990: pay_us_balance_view_pkg.debug_msg('call_balance_user_exit entry:');
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
991: pay_us_balance_view_pkg.debug_msg(' p_balance_name: ' || p_balance_name);
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
999:
992: pay_us_balance_view_pkg.debug_msg(' p_dimension_suffix: ' || p_dimension_suffix);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
999:
1000: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
993: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
999:
1000: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1001:
994: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
995: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
996: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
997: pay_us_balance_view_pkg.debug_msg(' p_gre_id: ' || p_gre_id);
998: pay_us_balance_view_pkg.debug_msg(' p_jurisdiction_code: ' || p_jurisdiction_code);
999:
1000: --hr_utility.set_location('bh_taxbal.balance_name'||p_balance_name, 9);
1001:
1002: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 10);
1001:
1002: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 10);
1003: IF p_assignment_action_id IS NOT NULL THEN
1004: -- If group level balance, call the group level balance code.
1005: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not NULL: '
1006: || p_assignment_action_id);
1007: if p_asg_type = 'GRE' then
1008: pay_us_balance_view_pkg.debug_msg(', p_asg_type GRE, calling get_grp_value');
1009: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 20);
1004: -- If group level balance, call the group level balance code.
1005: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not NULL: '
1006: || p_assignment_action_id);
1007: if p_asg_type = 'GRE' then
1008: pay_us_balance_view_pkg.debug_msg(', p_asg_type GRE, calling get_grp_value');
1009: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 20);
1010: return get_grp_value(p_assignment_id,
1011: p_virtual_date,
1012: p_balance_name,
1015: p_jurisdiction_code,
1016: p_assignment_action_id
1017: );
1018: else
1019: pay_us_balance_view_pkg.debug_msg(' p_asg_type *not* GRE, calling get_defined_balance and get_value');
1020: l_defined_balance_id := get_defined_balance(p_balance_name,
1021: p_dimension_suffix);
1022: IF p_dimension_suffix not like '%PAY%' THEN
1023: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 30);
1029: -- return p_assignment_action_id;
1030: -- end if;
1031: --
1032: --
1033: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1034: -- p_assignment_action_id
1035: -- );
1036: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1037: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1032: --
1033: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1034: -- p_assignment_action_id
1035: -- );
1036: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1037: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1038: l_defined_balance_id
1039: );
1040: ELSE /* If payments dimension then must execute DB item 395029 */
1033: -- return pay_us_balance_view_pkg.get_value (l_defined_balance_id,
1034: -- p_assignment_action_id
1035: -- );
1036: pay_us_balance_view_pkg.debug_msg(' dimension not like PAY, calling get_value in default mode');
1037: return pay_us_balance_view_pkg.get_value (p_assignment_action_id,
1038: l_defined_balance_id
1039: );
1040: ELSE /* If payments dimension then must execute DB item 395029 */
1041: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1038: l_defined_balance_id
1039: );
1040: ELSE /* If payments dimension then must execute DB item 395029 */
1041: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1042: pay_us_balance_view_pkg.debug_msg(' dimension *is* like PAY, calling get_value with *NO* caching');
1043: return pay_us_balance_view_pkg.get_value (
1044: p_assignment_action_id,
1045: l_defined_balance_id,
1046: 0,
1039: );
1040: ELSE /* If payments dimension then must execute DB item 395029 */
1041: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 40);
1042: pay_us_balance_view_pkg.debug_msg(' dimension *is* like PAY, calling get_value with *NO* caching');
1043: return pay_us_balance_view_pkg.get_value (
1044: p_assignment_action_id,
1045: l_defined_balance_id,
1046: 0,
1047: 1 );
1049: end if;
1050: --
1051: ELSE
1052: -- If group level balance, call the group level balance code.
1053: pay_us_balance_view_pkg.debug_msg(' assignment_action_id *is* NULL: ');
1054: if p_asg_type = 'GRE' then
1055: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE, calling get_grp_value');
1056: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 50);
1057: return get_grp_value(p_assignment_id,
1051: ELSE
1052: -- If group level balance, call the group level balance code.
1053: pay_us_balance_view_pkg.debug_msg(' assignment_action_id *is* NULL: ');
1054: if p_asg_type = 'GRE' then
1055: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE, calling get_grp_value');
1056: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 50);
1057: return get_grp_value(p_assignment_id,
1058: p_virtual_date,
1059: p_balance_name,
1062: p_jurisdiction_code,
1063: null
1064: );
1065: else
1066: pay_us_balance_view_pkg.debug_msg(' p_asg_type is *not* GRE, calling get_defined_balance and get_value');
1067: l_defined_balance_id := get_defined_balance(p_balance_name,
1068: p_dimension_suffix);
1069: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 60);
1070: return pay_us_balance_view_pkg.get_value (
1066: pay_us_balance_view_pkg.debug_msg(' p_asg_type is *not* GRE, calling get_defined_balance and get_value');
1067: l_defined_balance_id := get_defined_balance(p_balance_name,
1068: p_dimension_suffix);
1069: --hr_utility.set_location('bh_taxbal.call_balance_user_exit', 60);
1070: return pay_us_balance_view_pkg.get_value (
1071: p_assignment_id,
1072: l_defined_balance_id,
1073: p_virtual_date
1074: );
1095: p_jurisdiction_code varchar2 )
1096: RETURN number IS
1097: l_balance number;
1098: BEGIN
1099: pay_us_balance_view_pkg.debug_msg( '===========================================');
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1096: RETURN number IS
1097: l_balance number;
1098: BEGIN
1099: pay_us_balance_view_pkg.debug_msg( '===========================================');
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1097: l_balance number;
1098: BEGIN
1099: pay_us_balance_view_pkg.debug_msg( '===========================================');
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1098: BEGIN
1099: pay_us_balance_view_pkg.debug_msg( '===========================================');
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1099: pay_us_balance_view_pkg.debug_msg( '===========================================');
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1100: pay_us_balance_view_pkg.debug_msg('Enter US_NAMED_BALANCE:');
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1101: pay_us_balance_view_pkg.debug_msg(' balance_name: ' || p_balance_name);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1102: pay_us_balance_view_pkg.debug_msg(' dimension_suffix: ' || p_dimension_suffix);
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110: --
1103: pay_us_balance_view_pkg.debug_msg(' assignment_action_id: ' || p_assignment_action_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110: --
1111: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1104: pay_us_balance_view_pkg.debug_msg(' assignment_id: ' || p_assignment_id);
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110: --
1111: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1112: IF p_jurisdiction_code IS NOT NULL THEN
1105: pay_us_balance_view_pkg.debug_msg(' virtual_date: ' || p_virtual_date);
1106: pay_us_balance_view_pkg.debug_msg(' asg_type: ' || p_asg_type);
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110: --
1111: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1112: IF p_jurisdiction_code IS NOT NULL THEN
1113: -- BHOMAN - review this twisted logic with LWTHOMPS:
1107: pay_us_balance_view_pkg.debug_msg(' gre_id: ' || p_gre_id);
1108: pay_us_balance_view_pkg.debug_msg(' business_group_id: ' || p_business_group_id);
1109: pay_us_balance_view_pkg.debug_msg(' jurisdiction_code: ' || p_jurisdiction_code);
1110: --
1111: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
1112: IF p_jurisdiction_code IS NOT NULL THEN
1113: -- BHOMAN - review this twisted logic with LWTHOMPS:
1114: -- should I use 'SCHOOL%', or '%SCHOOL%', or shoud I just
1115: -- pass a tax type
1114: -- should I use 'SCHOOL%', or '%SCHOOL%', or shoud I just
1115: -- pass a tax type
1116: IF (p_balance_name like ('SCHOOL%') and
1117: length(p_jurisdiction_code) > 11) THEN
1118: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',
1119: substr(p_jurisdiction_code,1,2)||
1120: '-'||substr(p_jurisdiction_code,13,5));
1121: ELSE
1122: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1118: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',
1119: substr(p_jurisdiction_code,1,2)||
1120: '-'||substr(p_jurisdiction_code,13,5));
1121: ELSE
1122: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1123: END IF;
1124: END IF;
1125: --
1126: pay_us_balance_view_pkg.set_context('BUSINESS_GROUP_ID',
1122: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jurisdiction_code);
1123: END IF;
1124: END IF;
1125: --
1126: pay_us_balance_view_pkg.set_context('BUSINESS_GROUP_ID',
1127: NVL(p_business_group_id,-1) );
1128:
1129: BEGIN
1130: l_balance := call_balance_user_exit (
1138: p_jurisdiction_code);
1139: EXCEPTION
1140: when others then
1141: --
1142: pay_us_balance_view_pkg.debug_err(
1143: 'us_named_balance: call_balance_user_exit raised exception');
1144: pay_us_balance_view_pkg.debug_err(
1145: ' RETURNING NULL.');
1146: return NULL;
1140: when others then
1141: --
1142: pay_us_balance_view_pkg.debug_err(
1143: 'us_named_balance: call_balance_user_exit raised exception');
1144: pay_us_balance_view_pkg.debug_err(
1145: ' RETURNING NULL.');
1146: return NULL;
1147: END run_asg_vdate_cursor;
1148: --
1145: ' RETURNING NULL.');
1146: return NULL;
1147: END run_asg_vdate_cursor;
1148: --
1149: pay_us_balance_view_pkg.debug_err(
1150: 'us_named_balance: return balance: ' || l_balance);
1151: return l_balance;
1152: --
1153: END;
1455: --
1456: -- check time part of dimension, and calc_all_timetype_flag
1457: --
1458: ----------------
1459: if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1460: p_dimension_suffix like '%CURRENT%' THEN
1461: return NULL;
1462: end if;
1463:
1460: p_dimension_suffix like '%CURRENT%' THEN
1461: return NULL;
1462: end if;
1463:
1464: if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1465: p_dimension_suffix like '%RUN%' THEN
1466: return NULL;
1467: end if;
1468:
1465: p_dimension_suffix like '%RUN%' THEN
1466: return NULL;
1467: end if;
1468:
1469: if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1470: p_dimension_suffix like '%PYDATE%' THEN
1471: return NULL;
1472: end if;
1473:
1470: p_dimension_suffix like '%PYDATE%' THEN
1471: return NULL;
1472: end if;
1473:
1474: if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1475: p_dimension_suffix like '%PTD%' THEN
1476: return NULL;
1477: end if;
1478:
1475: p_dimension_suffix like '%PTD%' THEN
1476: return NULL;
1477: end if;
1478:
1479: if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1480: p_dimension_suffix like '%MONTH%' THEN
1481: return NULL;
1482: end if;
1483:
1480: p_dimension_suffix like '%MONTH%' THEN
1481: return NULL;
1482: end if;
1483:
1484: if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1485: p_dimension_suffix like '%QTD%' THEN
1486: return NULL;
1487: end if;
1488:
1485: p_dimension_suffix like '%QTD%' THEN
1486: return NULL;
1487: end if;
1488:
1489: if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1490: p_dimension_suffix like '%YTD%' THEN
1491: return NULL;
1492: end if;
1493:
1493:
1494: ---------------
1495: if p_dimension_suffix like ('%MONTH%') OR
1496: p_dimension_suffix like ('%PYDATE%') then
1497: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1498: if l_calc_all = 0 then
1499: pay_us_balance_view_pkg.debug_msg(
1500: 'us_named_balance_vm: dimension '
1501: || p_dimension_suffix
1495: if p_dimension_suffix like ('%MONTH%') OR
1496: p_dimension_suffix like ('%PYDATE%') then
1497: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1498: if l_calc_all = 0 then
1499: pay_us_balance_view_pkg.debug_msg(
1500: 'us_named_balance_vm: dimension '
1501: || p_dimension_suffix
1502: || ' disabled, returning NULL');
1503: return NULL;
1559: end if; -- dimension suffix
1560: --
1561: -- get asg_type/view_mode
1562: --
1563: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1564: pay_us_balance_view_pkg.debug_msg(
1565: 'us_named_balance_vm called, view_mode: ' || l_asg_type);
1566: --
1567: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1560: --
1561: -- get asg_type/view_mode
1562: --
1563: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1564: pay_us_balance_view_pkg.debug_msg(
1565: 'us_named_balance_vm called, view_mode: ' || l_asg_type);
1566: --
1567: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1568: -- TODO - verify non-null p_gre_id and p_virtual_date??
1567: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1568: -- TODO - verify non-null p_gre_id and p_virtual_date??
1569: --
1570: if l_asg_type = 'GRE' then
1571: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1572: fnd_date.date_to_canonical(p_virtual_date));
1573: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1574: then
1575: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1569: --
1570: if l_asg_type = 'GRE' then
1571: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1572: fnd_date.date_to_canonical(p_virtual_date));
1573: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1574: then
1575: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1576: fnd_date.date_to_canonical(p_virtual_date));
1577: end if;
1571: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1572: fnd_date.date_to_canonical(p_virtual_date));
1573: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1574: then
1575: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1576: fnd_date.date_to_canonical(p_virtual_date));
1577: end if;
1578: pay_us_balance_view_pkg.debug_msg(
1579: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1574: then
1575: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1576: fnd_date.date_to_canonical(p_virtual_date));
1577: end if;
1578: pay_us_balance_view_pkg.debug_msg(
1579: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1580: || fnd_date.date_to_canonical(p_virtual_date));
1581: end if;
1582: --
1643: --
1644: -- check time_type, and CalcAllTimeTypes flag
1645: --
1646: --------------------
1647: if pay_us_balance_view_pkg.get_session_var('CURRENT') = 'FALSE' and
1648: p_time_type like '%CURRENT%' THEN
1649: return NULL;
1650: end if;
1651:
1648: p_time_type like '%CURRENT%' THEN
1649: return NULL;
1650: end if;
1651:
1652: if pay_us_balance_view_pkg.get_session_var('RUN') = 'FALSE' and
1653: p_time_type like '%RUN%' THEN
1654: return NULL;
1655: end if;
1656:
1653: p_time_type like '%RUN%' THEN
1654: return NULL;
1655: end if;
1656:
1657: if pay_us_balance_view_pkg.get_session_var('PYDATE') = 'FALSE' and
1658: p_time_type like '%PYDATE%' THEN
1659: return NULL;
1660: end if;
1661:
1658: p_time_type like '%PYDATE%' THEN
1659: return NULL;
1660: end if;
1661:
1662: if pay_us_balance_view_pkg.get_session_var('PTD') = 'FALSE' and
1663: p_time_type like '%PTD%' THEN
1664: return NULL;
1665: end if;
1666:
1663: p_time_type like '%PTD%' THEN
1664: return NULL;
1665: end if;
1666:
1667: if pay_us_balance_view_pkg.get_session_var('MONTH') = 'FALSE' and
1668: p_time_type like '%MONTH%' THEN
1669: return NULL;
1670: end if;
1671:
1668: p_time_type like '%MONTH%' THEN
1669: return NULL;
1670: end if;
1671:
1672: if pay_us_balance_view_pkg.get_session_var('QTD') = 'FALSE' and
1673: p_time_type like '%QTD%' THEN
1674: return NULL;
1675: end if;
1676:
1673: p_time_type like '%QTD%' THEN
1674: return NULL;
1675: end if;
1676:
1677: if pay_us_balance_view_pkg.get_session_var('YTD') = 'FALSE' and
1678: p_time_type like '%YTD%' THEN
1679: return NULL;
1680: end if;
1681:
1680: end if;
1681:
1682: ---------------------
1683: if p_time_type in ('MONTH', 'PYDATE') then
1684: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1685: if l_calc_all = 0 then
1686: pay_us_balance_view_pkg.debug_msg(
1687: 'us_tax_balance_vm, timetype '
1688: || p_time_type
1682: ---------------------
1683: if p_time_type in ('MONTH', 'PYDATE') then
1684: l_calc_all := pay_us_balance_view_pkg.get_calc_all_timetypes_flag;
1685: if l_calc_all = 0 then
1686: pay_us_balance_view_pkg.debug_msg(
1687: 'us_tax_balance_vm, timetype '
1688: || p_time_type
1689: || ' disabled, returning NULL');
1690: return NULL;
1746: end if;
1747: --
1748: -- get asg_type
1749: --
1750: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1751:
1752: pay_us_balance_view_pkg.debug_msg(
1753: 'us_tax_balance_vm called, view_mode: ' || l_asg_type);
1754: --
1748: -- get asg_type
1749: --
1750: l_asg_type := pay_us_balance_view_pkg.get_view_mode;
1751:
1752: pay_us_balance_view_pkg.debug_msg(
1753: 'us_tax_balance_vm called, view_mode: ' || l_asg_type);
1754: --
1755: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1756: -- TODO - verify non-null p_gre_id and p_virtual_date??
1755: -- if GRE mode, set DATE_EARNED context from p_virutal_date
1756: -- TODO - verify non-null p_gre_id and p_virtual_date??
1757: --
1758: if l_asg_type = 'GRE' then
1759: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1760: fnd_date.date_to_canonical(p_virtual_date));
1761: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1762: then
1763: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1757: --
1758: if l_asg_type = 'GRE' then
1759: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1760: fnd_date.date_to_canonical(p_virtual_date));
1761: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1762: then
1763: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1764: fnd_date.date_to_canonical(p_virtual_date));
1765: end if;
1759: pay_us_balance_view_pkg.set_context('DATE_EARNED',
1760: fnd_date.date_to_canonical(p_virtual_date));
1761: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
1762: then
1763: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1764: fnd_date.date_to_canonical(p_virtual_date));
1765: end if;
1766: pay_us_balance_view_pkg.debug_msg(
1767: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1762: then
1763: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
1764: fnd_date.date_to_canonical(p_virtual_date));
1765: end if;
1766: pay_us_balance_view_pkg.debug_msg(
1767: 'us_named_balance_vm GRE mode, set DATE_EARNED context to '
1768: || fnd_date.date_to_canonical(p_virtual_date));
1769: end if;
1770: --
1852: BEGIN
1853: --
1854: -- Check that inputs based on lookups are valid
1855: --
1856: pay_us_balance_view_pkg.debug_msg( '===========================================');
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1853: --
1854: -- Check that inputs based on lookups are valid
1855: --
1856: pay_us_balance_view_pkg.debug_msg( '===========================================');
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1854: -- Check that inputs based on lookups are valid
1855: --
1856: pay_us_balance_view_pkg.debug_msg( '===========================================');
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1855: --
1856: pay_us_balance_view_pkg.debug_msg( '===========================================');
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1856: pay_us_balance_view_pkg.debug_msg( '===========================================');
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1857: pay_us_balance_view_pkg.debug_msg('Enter US_TAX_BALANCE:');
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1858: pay_us_balance_view_pkg.debug_msg(' p_tax_balance_category: ' || p_tax_balance_category);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1859: pay_us_balance_view_pkg.debug_msg(' p_tax_type: ' || p_tax_type);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1860: pay_us_balance_view_pkg.debug_msg(' p_ee_or_er: ' || p_ee_or_er);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1861: pay_us_balance_view_pkg.debug_msg(' p_time_type: ' || p_time_type);
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1869: --
1862: pay_us_balance_view_pkg.debug_msg(' p_asg_type: ' || p_asg_type);
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1869: --
1870: --
1863: pay_us_balance_view_pkg.debug_msg(' p_gre_id_context: ' || p_gre_id_context);
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1869: --
1870: --
1871: SELECT count(0)
1864: pay_us_balance_view_pkg.debug_msg(' p_jd_context: ' || p_jd_context);
1865: pay_us_balance_view_pkg.debug_msg(' p_assignment_action_id: ' || p_assignment_action_id);
1866: pay_us_balance_view_pkg.debug_msg(' p_assignment_id: ' || p_assignment_id);
1867: pay_us_balance_view_pkg.debug_msg(' p_virtual_date: ' || p_virtual_date);
1868: pay_us_balance_view_pkg.debug_msg(' p_payroll_action_id: ' || p_payroll_action_id);
1869: --
1870: --
1871: SELECT count(0)
1872: INTO l_valid
1874: WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
1875: AND lookup_code = p_tax_balance_category;
1876: --
1877: IF l_valid = 0 THEN
1878: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax bal category:' ||
1879: p_tax_balance_category);
1880: local_error('us_tax_balance',1);
1881: END IF;
1882: --
1886: WHERE lookup_type = 'US_TAX_TYPE'
1887: AND lookup_code = p_tax_type;
1888: --
1889: IF l_valid = 0 THEN
1890: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax type: '
1891: || p_tax_type);
1892: local_error('us_tax_balance',2);
1893: END IF;
1894: --
1897: FROM dual
1898: WHERE p_asg_type in ('ASG','PER','GRE');
1899: --
1900: IF l_valid = 0 THEN
1901: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid asg_type: '
1902: || p_asg_type);
1903: local_error('us_tax_balance',3);
1904: END IF;
1905: --
1908: FROM dual
1909: WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE', 'ASG_PAYMENTS');
1910: --
1911: IF l_valid = 0 THEN
1912: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid time_type: '
1913: || p_time_type);
1914: local_error('us_tax_balance',4);
1915: END IF;
1916: --
1918: -- them up front
1919: --
1920: --hr_utility.set_location('pay_tax_bals_pkg',30);
1921: --
1922: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
1923: IF p_jd_context IS NOT NULL THEN
1924: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1925: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1926: '-'||substr(p_jd_context,13,5));
1921: --
1922: pay_us_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id_context);
1923: IF p_jd_context IS NOT NULL THEN
1924: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1925: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1926: '-'||substr(p_jd_context,13,5));
1927: ELSE
1928: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jd_context);
1929: END IF;
1924: IF (p_tax_type = 'SCHOOL' and length(p_jd_context) > 11) THEN
1925: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',substr(p_jd_context,1,2)||
1926: '-'||substr(p_jd_context,13,5));
1927: ELSE
1928: pay_us_balance_view_pkg.set_context('JURISDICTION_CODE',p_jd_context);
1929: END IF;
1930: END IF;
1931: --
1932: --hr_utility.set_location('pay_tax_bals_pkg',40);
1985: --
1986: -- set the virtual date to termination date, or return 0 if terminated
1987: -- before the time period.
1988: --
1989: pay_us_balance_view_pkg.debug_msg('Assignment was terminated on : ' || l_max_date);
1990: pay_us_balance_view_pkg.debug_msg('Time period in question begins on : ' ||
1991: l_bal_start_date);
1992: --
1993: IF l_max_date < l_bal_start_date THEN
1986: -- set the virtual date to termination date, or return 0 if terminated
1987: -- before the time period.
1988: --
1989: pay_us_balance_view_pkg.debug_msg('Assignment was terminated on : ' || l_max_date);
1990: pay_us_balance_view_pkg.debug_msg('Time period in question begins on : ' ||
1991: l_bal_start_date);
1992: --
1993: IF l_max_date < l_bal_start_date THEN
1994: return 0;
1995: ELSE
1996: l_virtual_date := l_max_date;
1997: END IF;
1998: --
1999: pay_us_balance_view_pkg.debug_msg('Using new virtual date : ' || l_virtual_date);
2000: --
2001: END IF;
2002: END IF;
2003: --
2025: l_tax_type = 'CITY' or l_tax_type = 'EIC' or l_tax_type = 'HT' or
2026: l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
2027: IF (l_tax_balance_category = 'TAXABLE' or
2028: l_tax_balance_category = 'EXCESS') THEN
2029: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2030: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2031: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2032: local_error('us_tax_balance',5);
2033: END IF;
2026: l_tax_type = 'SCHOOL' or l_tax_type = 'STEIC' ) THEN -- income tax
2027: IF (l_tax_balance_category = 'TAXABLE' or
2028: l_tax_balance_category = 'EXCESS') THEN
2029: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2030: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2031: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2032: local_error('us_tax_balance',5);
2033: END IF;
2034: --
2027: IF (l_tax_balance_category = 'TAXABLE' or
2028: l_tax_balance_category = 'EXCESS') THEN
2029: pay_us_balance_view_pkg.debug_err('us_tax_balance: Invalid tax cat for tax type');
2030: pay_us_balance_view_pkg.debug_err(' cat: ' || l_tax_balance_category);
2031: pay_us_balance_view_pkg.debug_err(' type: ' || l_tax_type);
2032: local_error('us_tax_balance',5);
2033: END IF;
2034: --
2035: -- return 0 for currently unsupported EIC balances.
2063: if (l_tax_type = 'FIT' or l_tax_type = 'SIT' or l_tax_type = 'CITY' or
2064: 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'
2065: or l_tax_type = 'STEIC' ) THEN
2066: if l_ee_or_er = 'ER' THEN
2067: pay_us_balance_view_pkg.debug_err('us_tax_balance: ER not valid for tax type: '
2068: || l_tax_type);
2069: local_error('us_tax_balance',6);
2070: else
2071: l_ee_or_er := NULL;
2071: l_ee_or_er := NULL;
2072: end if;
2073: elsif (l_tax_type = 'FUTA') THEN
2074: if l_ee_or_er = 'EE' THEN
2075: pay_us_balance_view_pkg.debug_err('us_tax_balance: EE not valid for tax type: '
2076: || l_tax_type);
2077: local_error('us_tax_balance',7);
2078: else
2079: l_ee_or_er := NULL;
2080: end if;
2081: elsif (l_tax_type = 'SS' or l_tax_type = 'MEDICARE' or l_tax_type = 'SDI' or
2082: l_tax_type = 'SUI') THEN
2083: if (l_ee_or_er <> 'EE' and l_ee_or_er <> 'ER') THEN
2084: pay_us_balance_view_pkg.debug_err('Error: EE or ER required for tax type: '
2085: || l_tax_type);
2086: local_error('us_tax_balance',8);
2087: end if;
2088: end if;
2099: --
2100: -- Set up dimension strings
2101: --
2102: IF p_asg_type <> 'GRE' THEN
2103: pay_us_balance_view_pkg.debug_msg(' p_asg_type is not GRE');
2104: l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
2105: l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
2106: ELSE
2107: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE');
2103: pay_us_balance_view_pkg.debug_msg(' p_asg_type is not GRE');
2104: l_dimension_string := p_asg_type||'_GRE_'||p_time_type;
2105: l_jd_dimension_string := p_asg_type||'_JD_GRE_'||p_time_type;
2106: ELSE
2107: pay_us_balance_view_pkg.debug_msg(' p_asg_type is GRE');
2108: --
2109: l_dimension_string := 'GRE_'||p_time_type;
2110: l_jd_dimension_string := 'GRE_JD_'||p_time_type;
2111: --
2118: --
2119: -- bug # gaz
2120: --
2121: IF (p_payroll_action_id is not null) THEN
2122: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is not NULL, getting assignment_action_id from cursor');
2123: begin
2124: OPEN get_pay_action_id;
2125: FETCH get_pay_action_id INTO l_assignment_action_id;
2126: CLOSE get_pay_action_id;
2125: FETCH get_pay_action_id INTO l_assignment_action_id;
2126: CLOSE get_pay_action_id;
2127: end;
2128: else
2129: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is NULL, cannot use it to get assignment_action_id');
2130: if (p_assignment_action_id is null) then
2131: pay_us_balance_view_pkg.debug_msg(' assignment_action_id is NULL, getting dummy for date mode');
2132: --
2133: -- Get a dummy assignment id to call the balance user exit in date mode.
2127: end;
2128: else
2129: pay_us_balance_view_pkg.debug_msg(' payroll_action_id is NULL, cannot use it to get assignment_action_id');
2130: if (p_assignment_action_id is null) then
2131: pay_us_balance_view_pkg.debug_msg(' assignment_action_id is NULL, getting dummy for date mode');
2132: --
2133: -- Get a dummy assignment id to call the balance user exit in date mode.
2134: --
2135: declare
2137: l_count number;
2138: l_found boolean;
2139: check_asg number;
2140: begin
2141: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2142: fnd_date.date_to_canonical(l_virtual_date));
2143: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2144: then
2145: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2139: check_asg number;
2140: begin
2141: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2142: fnd_date.date_to_canonical(l_virtual_date));
2143: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2144: then
2145: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2146: fnd_date.date_to_canonical(p_virtual_date));
2147: end if;
2141: pay_us_balance_view_pkg.set_context('DATE_EARNED',
2142: fnd_date.date_to_canonical(l_virtual_date));
2143: if (pay_us_balance_view_pkg.get_context('BALANCE_DATE') is NULL)
2144: then
2145: pay_us_balance_view_pkg.set_context('BALANCE_DATE',
2146: fnd_date.date_to_canonical(p_virtual_date));
2147: end if;
2148:
2149: select business_group_id
2157: l_count := 0;
2158: l_found := FALSE;
2159: while ((l_count < g_nxt_free_asgid) AND (l_found = FALSE)) loop
2160: if (l_bg_id = g_asgid_tbl_bgid(l_count)) then
2161: pay_us_balance_view_pkg.debug_msg(' found candidate assignment_action_id in cache');
2162: --
2163: -- OK, now check that the assignment is valid as of the
2164: -- virtual date.
2165: --
2171: and p_virtual_date between paf.effective_start_date
2172: and paf.effective_end_date;
2173: --
2174: l_assignment_id := g_asgid_tbl_id(l_count);
2175: pay_us_balance_view_pkg.debug_msg(' candidate assignment_action_id '
2176: || l_assignment_id
2177: || ' is valid as of vdate');
2178: l_found := TRUE;
2179: --
2187: if (l_found = FALSE) then
2188: --
2189: -- OK, need to get an assignment from the database.
2190: --
2191: pay_us_balance_view_pkg.debug_msg(' assignment_action_id not found in cache, going to DB');
2192: begin /* Modified the query for performance (Bug 3343982)*/
2193:
2194: select min(paa.assignment_id)
2195: into l_assignment_id
2212: g_nxt_free_asgid := g_nxt_free_asgid + 1;
2213: --
2214: exception when no_data_found then
2215: begin
2216: pay_us_balance_view_pkg.debug_err('us_tax_balance: Failed find asg id');
2217: local_error('us_tax_balance',1);
2218: --
2219: end;
2220: end;
2603: -- Some Reports Require Reporting of W2 Wages instead of
2604: -- subject. Properly this should be done with an additional
2605: -- balance type
2606: IF l_tax_balance_category = 'SUBJECT' and
2607: NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') <> 'W2' THEN
2608: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE');
2609: l_return_value := us_tax_balance('SUBJ_WHABLE',
2610: l_tax_type,
2611: p_ee_or_er,
2604: -- subject. Properly this should be done with an additional
2605: -- balance type
2606: IF l_tax_balance_category = 'SUBJECT' and
2607: NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') <> 'W2' THEN
2608: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE');
2609: l_return_value := us_tax_balance('SUBJ_WHABLE',
2610: l_tax_type,
2611: p_ee_or_er,
2612: p_time_type,
2629:
2630: --
2631: -- Note: Below is equivalent to reduced subject withholdable.
2632: --
2633: ELSIF l_tax_balance_category = 'SUBJECT' and NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
2634:
2635: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE FOR W2');
2636: l_return_value := us_tax_balance('SUBJ_WHABLE',
2637: l_tax_type,
2631: -- Note: Below is equivalent to reduced subject withholdable.
2632: --
2633: ELSIF l_tax_balance_category = 'SUBJECT' and NVL(pay_us_balance_view_pkg.get_session_var('REPORT_TYPE'),'NOT_DEFINED') = 'W2' THEN
2634:
2635: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE summing SUBJ_WHABLE and SUBJ_NWHABLE FOR W2');
2636: l_return_value := us_tax_balance('SUBJ_WHABLE',
2637: l_tax_type,
2638: p_ee_or_er,
2639: p_time_type,
2833: l_virtual_date);
2834: END IF;
2835: END IF;
2836: --
2837: pay_us_balance_view_pkg.debug_msg('US_TAX_BALANCE Returning : ' || l_return_value);
2838: --
2839: return l_return_value;
2840: --
2841: END us_tax_balance;