SQL & PL/SQL

Hi Everyone,


I have one payroll query that shows the employee loan balances like ( car loan, housing loan and salary loan) , It takes too much of time to execute and i have one more problem in it, that i needed a parameters selection criteria like loan types ( carloan, housing loan or sal loan), I created the parameters using it's input_value_id for example ( carloan=512, housingloan-517 and salaryloan=497), but the problem there is one more input_value_id for housing loan i.e., 520 which shows the remarks, when i choose 517 it shows only cr,dr,bal but the remarks will also come
together with housing loan,i want a parameters value like that if select 517 or 520 ith shows housing with all values.. Just look at the query and you will know the problem. so, please anyone have an idea about this.

Here is my query



SELECT ppf.employee_number, ppf.full_name,
MAX (DECODE (pev.input_value_id, 512, pev.screen_entry_value)
)
- MAX (d152.VALUE) car_loan_balance,
MAX (DECODE (pev.input_value_id, 512, pev.screen_entry_value)
) car_loan_dr,

/* sum(decode(PEV.INPUT_VALUE_ID,512,PEV.SCREEN_ENTRY_VALUE) ) -
(SELECT VALUE FROM pay_balance_values_v WHERE business_group_id = 0
AND assignment_action_id = paa.assignment_action_id AND defined_balance_id = 152) CAR_LOAN_Balance ,
*/
MAX (DECODE (pev.input_value_id, 517, pev.screen_entry_value)
)
- MAX (d154.VALUE) housing_loan_balance,
MAX (DECODE (pev.input_value_id, 517, pev.screen_entry_value)
) housing_loan_dr,
MAX (DECODE (pev.input_value_id, 520, pev.screen_entry_value)
) remarks,

/* sum(decode (PEV.INPUT_VALUE_ID,517,PEV.SCREEN_ENTRY_VALUE)) - (SELECT VALUE FROM pay_balance_values_v WHERE business_group_id = 0
AND assignment_action_id = paa.assignment_action_id AND defined_balance_id = 154) HOUSING_LOAN_BALANCE,

*/
MAX (DECODE (pev.input_value_id, 497, pev.screen_entry_value)
)
- MAX (d150.VALUE) loan_new_balance,
MAX (DECODE (pev.input_value_id, 497, pev.screen_entry_value)
) loan_recovery_dr
FROM apps.pay_element_entries_f peef,
apps.pay_element_entry_values_f pev,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_assignment_actions paa,
pay_all_payrolls_f ppf1,
pay_payroll_actions ppa1,
(SELECT pbv.VALUE, pbv.assignment_action_id
FROM pay_balance_values_v pbv, pay_assignment_actions pas
WHERE pbv.assignment_action_id = pas.assignment_action_id
AND pbv.effective_date = TO_DATE (:p_tdate, 'DD-MON-YYYY')
AND pbv.defined_balance_id = 154) d154,
(SELECT pbv.VALUE, pbv.assignment_action_id
FROM pay_balance_values_v pbv, pay_assignment_actions pas
WHERE pbv.assignment_action_id = pas.assignment_action_id
AND pbv.effective_date = TO_DATE (:p_tdate, 'DD-MON-YYYY')
AND pbv.defined_balance_id = 150) d150,
(SELECT pbv.VALUE, pbv.assignment_action_id
FROM pay_balance_values_v pbv, pay_assignment_actions pas
WHERE pbv.assignment_action_id = pas.assignment_action_id
AND pbv.effective_date = TO_DATE (:p_tdate, 'DD-MON-YYYY')
AND pbv.defined_balance_id = 152) d152
WHERE pev.element_entry_id = peef.element_entry_id
AND paa.assignment_action_id = d152.assignment_action_id(+)
AND paa.assignment_action_id = d150.assignment_action_id(+)
AND paa.assignment_action_id = d154.assignment_action_id(+)
AND paf.assignment_id = paa.assignment_id
AND ppa1.effective_date = TO_DATE (:p_tdate, 'DD-MON-YYYY')
AND paf.assignment_id = peef.assignment_id
AND ppf.person_id = paf.person_id
AND ppf1.payroll_id = paf.payroll_id
AND ppa1.payroll_action_id = paa.payroll_action_id
AND ppf1.payroll_id = :p_payroll
AND TO_DATE (:p_tdate, 'DD-MON-YYYY') BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND TO_DATE (:p_tdate, 'DD-MON-YYYY') BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TO_DATE (:p_tdate, 'DD-MON-YYYY') BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND TO_DATE (:p_tdate, 'DD-MON-YYYY') BETWEEN pev.effective_start_date
AND pev.effective_end_date
AND ppa1.effective_date = TO_DATE (:p_tdate, 'DD-MON-YYYY')
AND pev.input_value_id IN (512, 517, 497, 520)
AND ppf.employee_number BETWEEN NVL (:p_femployee, ppf.employee_number)
AND NVL (:p_temployee, ppf.employee_number)
-- and ppf.employee_number in ('R10196','R10229','R10253')
--and ppf.employee_number in ('R20001')
GROUP BY ppf.employee_number, ppf.full_name
ORDER BY ppf.employee_number





Thank You


Regards

Mohsin