Query to Get Batch Element Entry Details

SELECT per.employee_number,

       per.full_name,

       pbh.batch_name,

       petf.element_name,

       hr_general.decode_lookup ('BATCH_STATUS', pbl.batch_line_status)

          batch_status,

       TO_CHAR (pbl.effective_date, 'DD-MON-RRRR') effective_date

  FROM pay_batch_headers pbh,

       pay_batch_lines pbl,

       pay_element_types_f petf,

       per_all_assignments_f paaf,

       per_all_people_f per

 WHERE     pbh.batch_id = pbl.batch_id

       --       AND pbl.batch_line_status = 'T'

       AND petf.element_type_id = pbl.element_type_id

       AND pbl.effective_date BETWEEN NVL (:p_effective_start_date,

                                           hr_general.start_of_time)

                                  AND NVL (:p_effective_end_date,

                                           hr_general.end_of_time)

       AND pbl.batch_id = NVL (:p_batch_id, pbl.batch_id)

       AND petf.business_group_id = :p_business_group_id

       AND paaf.assignment_id = pbl.assignment_id

       AND paaf.person_id = per.person_id

       AND pbl.effective_date BETWEEN paaf.effective_start_date

                                  AND paaf.effective_end_date

       AND pbl.effective_date BETWEEN per.effective_start_date

                                  AND per.effective_end_date