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;
/

Return to : Oracle Database, SQL, Application, Programming Tips