Include Balance Values in Reports

There are 2 way:
1) Assignment action mode
2) Date mode


select *
from pay_balance_types
where balance_name like 'D310%'


select *
from pay_balance_dimensions
where dimension_name = '_ASG_YTD'


select *
from PAY_DEFINED_BALANCES
where balance_type_id = 264
and balance_dimension_id = 114


---- Assignment action mode -----
declare
my_value number;
begin
my_value:= pay_balance_pkg.get_value
(
p_defined_balance_id => 553,
p_assignment_action_id => 38593,
);
dbms_output.put_line('value: ' my_value);
end;


---- Date mode ----
declare
my_value number;
begin
my_value:= pay_balance_pkg.get_value
(
p_defined_balance_id => 553,
p_assignment_id => 38593,
p_virtual_date => to_date('05072004','ddmmyyyy')
);
dbms_output.put_line('value: ' my_value);
end;

select ptp.period_name,
pay.payroll_name,
ptp.start_date,
ptp.end_date,
pac.assignment_id,
es.element_set_name,
et.element_name,
max(decode(dim.dimension_name,'_ASG_RUN', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) this_pay,
max(decode(dim.dimension_name,'_ASG_MTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) month_2_date,
max(decode(dim.dimension_name,'_ASG_YTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) fisc_year_2_date,
max(decode(dim.dimension_name,'_ASG_CAL_YTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) cal_year_2_date
from per_time_periods ptp,
pay_payrolls_f pay,
pay_payroll_actions ppa,
pay_assignment_actions pac,
pay_element_sets es,
pay_element_type_rules tr,
pay_element_types_x et,
per_assignments_f ass,
pay_balance_types bt,
pay_defined_balances db,
pay_balance_dimensions dim
where pay.payroll_name = NVL(:p_payroll_name ,pay.payroll_name)
and pay.payroll_id = ptp.payroll_id
and ptp.period_name = :p_period_name
and ptp.payroll_id = ppa.payroll_id
and ppa.action_type = 'R'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ppa.payroll_action_id = pac.payroll_action_id
and ass.assignment_id = pac.assignment_id
and ptp.end_date between ass.effective_start_date and ass.effective_end_date
and utsc_hr_rpt_utilities_pkg.org_under_node (ass.organization_id, NVL(:p_org,81)) = 'Y'
-- Element belong to the Element Set
and ( es.BUSINESS_GROUP_ID IS NULL OR es.BUSINESS_GROUP_ID = 81 )
and ( es.LEGISLATION_CODE IS NULL OR es.LEGISLATION_CODE = 'AU' )
and ELEMENT_SET_NAME in ('UTS Taxable Income','UTS Non Taxable Income')
and tr.element_set_id = es.element_set_id
and tr.element_type_id = et.element_type_id
and include_or_exclude = 'I'
and bt.balance_name = element_name
and db.balance_type_id = bt.balance_type_id
and db.balance_dimension_id = dim.balance_dimension_id
and dim.dimension_name in ('_ASG_RUN','_ASG_MTD','_ASG_YTD','_ASG_CAL_YTD')
group by ptp.period_name,pay.payroll_name, ptp.start_date, ptp.end_date, pac.assignment_id, es.element_set_name, et.element_name