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 whereassignment_id=paf.ASSIGNMENT_ID);
