Oracle Balance Queries

 

— Balance_value_based_on balance_name

select sum (nvl (pbv.value, 0))
from pay_balance_values_v pbv
where pbv.balance_name = ‘Basic Salary’
and pbv.database_item_suffix = ‘_ASG_PTD’
and pbv.assignment_id = <your_assignment_id>
and pbv.effective_date between to_date(’01/02/2015′,’dd/mm/yyyy’)
and to_date(’31/03/2015′,’dd/mm/yyyy’);

 

— Benefit_Balance_Name_query

SELECT bpbfbal.val
INTO l_retval
FROM ben_bnfts_bal_f bfbal,
ben_per_bnfts_bal_f bpbfbal,
per_all_assignments_f paaf
WHERE bfbal.name = p_name
AND bfbal.bnfts_bal_id = bpbfbal.bnfts_bal_id
AND bpbfbal.person_id = paaf.person_id
AND paaf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN bfbal.effective_start_date AND NVL(bfbal.effective_end_date,to_date(’31-DEC-4712′))
AND p_date_earned BETWEEN bpbfbal.effective_start_date AND NVL(bpbfbal.effective_end_date,to_date(’31-DEC-4712′))
AND p_date_earned BETWEEN paaf.effective_start_date AND NVL(paaf.effective_end_date,to_date(’31-DEC-4712′));

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s