create or replace 
FUNCTION           "TRANSLATE_PAYMENT_STATUS" 
 (payment_sid_in IN paymentcheckrequestfull_view.PaymentSid%TYPE)
RETURN paymentcheckrequestfull_view.PaymentStatus%TYPE
as

   CURSOR payments_cur  IS
      SELECT PaymentStatus
        FROM PaymentCheckRequestFull_View
       WHERE PaymentSID = payment_sid_in;

   payment_rec payments_cur%ROWTYPE;

   /*internal copy of current return payment status */
   ret_status paymentcheckrequestfull_view.PaymentStatus%TYPE;

BEGIN

   ret_status := 'START';

   FOR payment_rec IN payments_cur
   LOOP

   /*Return a status of PAID for mixed statuses of PAID and MANUALLYPAID */

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


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

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

   END LOOP;

   RETURN(ret_status);
END;