select paf.ASSIGNMENT_NUMBER,
ppf.FULL_NAME,
pet.element_name,
pca.CONCATENATED_SEGMENTS,
’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)
