Function to Get Batch Name for Corresponding Element Entry Created

FUNCTION xxx_get_batch_details_f (p_assignment_id           NUMBER,

                                  p_element_type_id         NUMBER,

                                  p_effective_start_date    DATE,

                                  p_effective_end_date      DATE)

   RETURN VARCHAR2

AS

   l_batch_name   VARCHAR2 (500) := NULL;

BEGIN

   SELECT pbh.batch_name

     INTO l_batch_name

     FROM pay_batch_headers pbh,

          pay_batch_lines pbl,

          pay_element_types_f petf

    WHERE     pbh.batch_id = pbl.batch_id

          AND pbl.batch_line_status = 'T'

          AND petf.element_type_id = pbl.element_type_id

          AND pbl.assignment_id = p_assignment_id

          AND pbl.effective_date BETWEEN p_effective_start_date

                                     AND p_effective_end_date

          AND pbl.element_type_id = p_element_type_id;

   RETURN l_batch_name;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END;