Oracle Payroll Costing Detail Query

select

    paf.ASSIGNMENT_NUMBER,

    ppf.FULL_NAME ,

    pet.element_name,

    pca.CONCATENATED_SEGMENTS,

    decode(pc.DEBIT_OR_CREDIT,’D',pc.COSTED_VALUE) Debit,

    decode(pc.DEBIT_OR_CREDIT,’C',pc.COSTED_VALUE) Credit

from

    per_people_f ppf,

    per_assignments_f paf,

    pay_assignment_actions pav,

    pay_payroll_actions ppa,

    pay_costs pc,

    PAY_COST_ALLOCATION_KEYFLEX pca,

    pay_element_types_f pet,

    pay_run_results prr,

    pay_run_result_values prrv

where ppf.PERSON_ID=paf.PERSON_ID

    and paf.ASSIGNMENT_ID=pav.ASSIGNMENT_ID

    and paf.PRIMARY_FLAG=’Y’

    and ppf.EMPLOYEE_NUMBER=:p_emp_no

    and pav.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID

    and trunc(ppa.EFFECTIVE_DATE) between :p_start_date and :p_end_date

    and pav.ASSIGNMENT_ACTION_ID=pc.ASSIGNMENT_ACTION_ID

    and pc.COST_ALLOCATION_KEYFLEX_ID=pca.COST_ALLOCATION_KEYFLEX_ID

    and pet.ELEMENT_TYPE_ID=prr.ELEMENT_TYPE_ID

    and prr.RUN_RESULT_ID=prrv.RUN_RESULT_ID

    and pc.RUN_RESULT_ID=prrv.RUN_RESULT_ID

    and pc.INPUT_VALUE_ID=prrv.INPUT_VALUE_ID

    and :p_end_date between pet.EFFECTIVE_START_DATE and pet.EFFECTIVE_END_DATE

    and ppf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_people_f where person_id=ppf.PERSON_ID)

    and paf.EFFECTIVE_END_DATE = (select max(effective_end_date) from per_assignments_f where assignment_id=paf.ASSIGNMENT_ID)