Since 2006 by Malik Sikandar Hayat - Oracle ACE Pro
https://erpstuff.com/
select
a.assignment_number,bb.VALUE,bb.effective_date
from
(
SELECT b.assignment_id, max(b.effective_date) l_effective_date
FROM pay_balance_values_v b
where 1=1
and b.balance_name = :b.balance_name
and b.effective_date >= to_date('1/1/2008','dd/mm/yyyy')
group by b.assignment_id
)BL,
per_all_assignments_f a,
pay_balance_values_v bb
where 1=1
and a.assignment_id=BL.assignment_id
and bb.balance_name = :bb.balance_name
and bb.assignment_id=a.assignment_id
and BL.l_effective_date = bb.effective_date
and sysdate between a.effective_start_date and a.effective_end_date
SELECT
NVL(bal.value,0)
FROM pay_balance_values_v bal
where
bal.business_group_id=81
and bal.balance_name like 'End Of Service Accrual Balance' --Here Type Balance Name
and bal.dimension_name = 'Assignment Calendar Year To Date' -- Type Of Balance YTD
--and bal.effective_date = to_date('30-SEP-08') --Effictive Date For Balance
and SUBSTR(to_char(bal.effective_date,'DD-MON-YYYY'),4,8)='MAR-2008' --Effictive Date For Balance
and bal.assignment_id = :ass_id