Useful queries in debugging Payroll issues

Few queries that are useful in debugging Payroll or Retropay issues in HRMS.

1) To get all the actions for an assignment
     SELECT   paa.assignment_id, ppa.payroll_id, ppa.business_group_id,
         hrl.meaning, ppa.action_status, ppa.effective_date, ppa.date_earned,
         ppa.payroll_action_id, paa.assignment_action_id, paa.action_status,
         paa.action_sequence, paa.source_action_id, paa.run_type_id
    FROM pay_assignment_actions paa, pay_payroll_actions ppa, hr_lookups hrl
   WHERE paa.payroll_action_id = ppa.payroll_action_id
     AND ppa.action_type = hrl.lookup_code
     AND hrl.lookup_type = 'ACTION_TYPE'
     AND paa.assignment_id = <<assignment_id>>
ORDER BY paa.action_sequence DESC;


2) To get all the Element Entries for an Assignment
    SELECT DISTINCT pet.element_name, pet.element_type_id, piv.NAME input_value,
                piv.input_value_id, pee.element_entry_id, pee.creator_type,
                pee.entry_type, pev.element_entry_value_id,
                pev.screen_entry_value, pev.effective_start_date,
                pev.effective_end_date, pee.creator_id, pee.source_id,
                pee.source_asg_action_id, pee.source_start_date,
                pee.source_end_date
           FROM pay_element_types_f pet,
                pay_input_values_f piv,
                pay_element_entries_f pee,
                pay_element_entry_values_f pev
          WHERE pee.assignment_id = <<assignment_id>>
            AND piv.element_type_id = pet.element_type_id
            AND pee.element_type_id = pet.element_type_id
            AND pee.element_entry_id = pev.element_entry_id
            AND pev.input_value_id = piv.input_value_id
       ORDER BY 1, 6, 5;


3) To get Run Results for an Assignment
     SELECT DISTINCT pet.element_name, piv.NAME, ppa.action_type,
                ppa.effective_date payroll_run_date, 
                paa.assignment_action_id,
                ppa.payroll_action_id, 
                prr.run_result_id, prv.result_value,
                prr.entry_type, prr.source_id, prr.source_type,
                prr.start_date, prr.end_date, prr.element_entry_id,
                prv.formula_result_flag
           FROM pay_element_types_f pet,
                pay_input_values_f piv,
                pay_run_results prr,
                pay_run_result_values prv,
                pay_assignment_actions paa,
                pay_payroll_actions ppa
          WHERE paa.assignment_id = <<assignment_id>>
            AND paa.payroll_action_id = ppa.payroll_action_id
            AND paa.assignment_action_id = prr.assignment_action_id
            AND prr.run_result_id = prv.run_result_id
            AND prv.input_value_id = piv.input_value_id
            AND pet.element_type_id = prr.element_type_id
            AND piv.element_type_id = pet.element_type_id
            AND ppa.action_type IN ('Q', 'R', 'V', 'B')
       ORDER BY 1, 2, 3, 4, 9, 10;