CREATE OR REPLACE FUNCTION lms_app.translate_payment_status(
	payment_sid_in character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    STABLE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE

   payments_cur CURSOR FOR
      SELECT PaymentStatus
        FROM lms_app.PaymentCheckRequestFull_View
       WHERE PaymentSID = payment_sid_in;

   --payment_rec payments_cur%ROWTYPE;

   /*internal copy of current return payment status */
   
   payment_rec lms_app.PaymentCheckRequestFull_View.PaymentStatus%TYPE; ---Change

   ret_status lms_app.paymentcheckrequestfull_view.PaymentStatus%TYPE;

BEGIN

   ret_status := 'START';

   --FOR payment_rec IN payments_cur  ------Change
   OPEN payments_cur;   ------Change
   LOOP
   FETCH payments_cur INTO payment_rec;
   EXIT WHEN NOT FOUND;
   
   /*Return a status of PAID for mixed statuses of PAID and MANUALLYPAID */

      IF (ret_status = 'START')
       THEN
         ret_status := payment_rec;
      END IF;

     IF ((payment_rec = 'PAID') OR (payment_rec = 'MANUALLYPAID'))
     THEN
         IF (payment_rec = 'PAID') AND ((ret_status = 'MANUALLYPAID') OR (ret_status = 'PAID'))
         THEN
             ret_status := 'PAID';
         ELSIF (payment_rec = 'MANUALLYPAID') AND ((ret_status = 'MANUALLYPAID') OR (ret_status = 'PAID'))
         THEN
             ret_status := 'PAID';
         ELSE
             ret_status := 'MIXED';
         END IF;
     END IF;

     IF (payment_rec <> 'PAID' AND payment_rec <> 'MANUALLYPAID')
     THEN
         IF (ret_status = payment_rec)
         THEN
              ret_status := payment_rec;
         ELSE
              ret_status := 'MIXED';
         END IF;
     END IF;

   END LOOP;
   
    CLOSE payments_cur;

   RETURN(ret_status);
EXCEPTION
   When Others Then
	  RAISE INFO 'Error % , Cod: %', SQLERRM, sqlstate;
         
      Return 'Fail';
END;
$BODY$;