Friday, August 30, 2013

Query to Get Costing Details

SELECT per.employee_number,
       per.full_name,
       petf.element_name,
       DECODE (pc.debit_or_credit,  'D', 'Debit',  'C', 'Credit',  NULL)
          debit_or_credit,
       pc.costed_value amount,
       pcak.segment4 account,
       ffvt.description account_description,
       ppa.effective_date
  FROM pay_costs pc,
       pay_assignment_actions pac,
       pay_payroll_actions ppa,
       per_all_assignments_f paaf,
       per_all_people_f per,
       pay_run_results prr,
       pay_element_types_f petf,
       pay_cost_allocation_keyflex pcak,
       fnd_flex_value_sets ffvs,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt
 WHERE     pac.assignment_action_id = pc.assignment_action_id
       AND paaf.assignment_id = pac.assignment_id
       AND pac.payroll_action_id = ppa.payroll_action_id
       AND paaf.person_id = per.person_id
       AND paaf.assignment_type = 'E'
       AND paaf.assignment_status_type_id = 1
       AND paaf.primary_flag = 'Y'
       AND ppa.effective_date BETWEEN per.effective_start_date
                                  AND per.effective_end_date
       AND ppa.effective_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
       AND ppa.effective_date BETWEEN :l_date_from AND :l_date_to
       AND pc.run_result_id = prr.run_result_id
       AND petf.element_type_id = prr.element_type_id
       AND per.employee_number = nvl(:l_employee_number,per.employee_number)
       AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
       AND flex_value_set_name = 'XXX_FIN_ACC_VS'
       AND ffv.flex_value_set_id = ffvs.flex_value_set_id
       AND ffvt.flex_value_id = ffv.flex_value_id
       AND ffvs.flex_value_set_id = ffv.flex_value_set_id
       AND ffvt.language = 'US'
       AND ffv.enabled_flag = 'Y'
       AND pcak.segment4 = ffv.flex_value

1 comment:

  1. Is there a version 10 example? I get table does not exist.

    ReplyDelete