The following function return balance by passing Balance Name, Assignment ID, Date of Calculation and Dimension suffix like YTD, QTD etc.

CREATE OR REPLACE FUNCTION GET_ADHOC_BALANCES_FNC
 ( p_balance_name varchar2,
          p_assign_id number,
   p_virtual_date  date,
   p_dimension_suffix varchar2
  ) RETURN NUMBER IS
 temp_value number;
 v_defbal_id pay_defined_balances.defined_balance_id%TYPE;
 v_Assignment_action_id number;
 v_org_id number;
 v_jr_code varchar2(30);
 v_region1 per_addresses.region_1%TYPE;
 v_region2 per_addresses.region_2%TYPE;
 v_postal_code per_addresses.postal_code%TYPE;
 v_town_or_city per_addresses.town_or_city%TYPE;
BEGIN
 SELECT MAX(assignment_action_id)
 INTO v_assignment_action_id
 FROM pay_assignment_actions paa,
      pay_payroll_actions ppa
 WHERE assignment_id = p_assign_id
 AND   ppa.payroll_action_id = paa.payroll_action_id;
 BEGIN
  SELECT distinct tax_unit_id
  INTO v_org_id
  FROM pay_assignment_actions
  WHERE assignment_action_id = v_assignment_action_id;
 EXCEPTION
  WHEN no_data_found THEN
   v_org_id := 1;
 END;
 BEGIN
  SELECT DISTINCT pdb.defined_balance_id
   INTO    v_defbal_id
      FROM   pay_defined_balances pdb,
                 pay_balance_types pbt,
                 pay_balance_dimensions dim
      WHERE  pdb.balance_type_id      = pbt.balance_type_id
      AND    pdb.balance_dimension_id = dim.balance_dimension_id
      AND    upper(pbt.balance_name)  = upper(p_balance_name)
      AND    dim.database_item_suffix = p_dimension_suffix;
 EXCEPTION
  WHEN no_data_found THEN
   v_defbal_id := NULL;
 END;
 BEGIN
  SELECT  per_addresses.region_1,
   per_addresses.postal_code,
   per_addresses.town_or_city,
   per_addresses.region_2
  INTO v_region1,
   v_postal_code,
   v_town_or_city,
   v_region2
  FROM  per_people_f,
   per_assignments_f,
   per_addresses
  WHERE  per_assignments_f.assignment_id=p_assign_id
   AND   ((per_addresses.person_id=per_people_f.person_id)
   AND     (per_people_f.person_id=per_assignments_f.person_id))
  AND  per_addresses.primary_flag = 'Y';
 EXCEPTION
  WHEN no_data_found THEN
   v_region1 := NULL;
   v_region2 := NULL;
   v_postal_code := NULL;
   v_town_or_city := NULL;
  WHEN too_many_rows THEN
   null;
 END;
 pay_balance_pkg.set_context ('TAX_UNIT_ID',  v_org_id);
 v_jr_code:=hr_us_ff_udfs.addr_val(v_region2,v_region1,v_town_or_city,v_postal_code);
 pay_balance_pkg.set_context ('JURISDICTION_CODE',v_jr_code);
     temp_value:=pay_balance_pkg.get_value(
                    v_defbal_id,
                    p_assign_id,
                    p_virtual_date);
 return nvl(temp_value,0.00);
EXCEPTION
 WHEN no_data_found THEN
  temp_value := 0.00;
  return temp_value;
END;
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips