Friday, November 29, 2013

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)

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect