CREATE OR REPLACE FUNCTION SIT_VERSAOPROPCOM
(
   P_NRVERSAOPROPCOM IN NUMBER
) RETURN VARCHAR2
AS
   V_SITUACAO VARCHAR2(30);
   V_IDVERSAOPROPCOM VARCHAR2(30);
   V_QTDE NUMBER;
   V_QT_SERV_CALC NUMBER;
BEGIN
   SELECT MAX(IDVERSAOPROPCOM)
     INTO V_IDVERSAOPROPCOM
     FROM VERSAOPROPCOM
    WHERE NRVERSAOPROPCOM = P_NRVERSAOPROPCOM;

   IF V_IDVERSAOPROPCOM = 'CONCLUIDA' THEN
      V_SITUACAO := 'CONCLUIDA';
   ELSIF V_IDVERSAOPROPCOM = 'APROVADA' THEN
      V_SITUACAO := 'APROVADA';
      -- Se todos os servicos da versao estao efetivados,a versao esta efetivada
      SELECT COUNT(1)
        INTO V_QTDE
        FROM SERVICOPROPCOM
       WHERE NRVERSAOPROPCOM = P_NRVERSAOPROPCOM
         AND IDSERVICOPROPCOM <> 'EFETIVADO';
      IF V_QTDE = 0 THEN
         V_SITUACAO := 'EFETIVADA';
      END IF;
   ELSE
      V_SITUACAO := 'PENDENTE';

      FOR C_SERVICOS IN
      (
         SELECT S.IDSERVICOPROPCOM, D.NRCARDAPIOTRAB
           FROM SERVICOPROPCOM S
                INNER JOIN DETALHESERVPROPCOM D ON D.NRDETALHESERVPROPCOM = S.NRDETALHESERVPROPCOM
          WHERE S.NRVERSAOPROPCOM = P_NRVERSAOPROPCOM
          ORDER BY S.IDSERVICOPROPCOM
      ) LOOP
         -- A presenca de um cardapio de trabalho sinaliza o calculo do servico
         IF V_SITUACAO = 'PENDENTE' AND C_SERVICOS.NRCARDAPIOTRAB IS NOT NULL THEN
            V_SITUACAO := 'PARCIAL';
         END IF;
         -- Se existir algum servico aguardando planejamento, a versao esta aguardando planejamento
         IF V_SITUACAO NOT IN ('AGUARDANDOPLANEJAMENTO', 'AGUARDANDOGN') AND
            C_SERVICOS.IDSERVICOPROPCOM = 'AGUARDANDOPLANEJAMENTO' THEN
            V_SITUACAO := 'AGUARDANDOPLANEJAMENTO';
         END IF;
         -- Se a situacao for aguardando GN, ela tem prioridade sobre as demais
         IF V_SITUACAO <> 'AGUARDANDOGN' AND C_SERVICOS.IDSERVICOPROPCOM = 'AGUARDANDOGN' THEN
            V_SITUACAO := 'AGUARDANDOGN';
         END IF;      
      END LOOP;

      -- Se todos os servicos estao calculados, a versao esta calculada (caso ainda nao seja )
      IF V_SITUACAO = 'PARCIAL' THEN
         SELECT COUNT(1), SUM(CASE WHEN NRCARDAPIOTRAB IS NULL THEN 0 ELSE 1 END)
           INTO V_QTDE, V_QT_SERV_CALC
           FROM SERVICOPROPCOM S
                INNER JOIN DETALHESERVPROPCOM D ON D.NRDETALHESERVPROPCOM = S.NRDETALHESERVPROPCOM
          WHERE S. NRVERSAOPROPCOM = P_NRVERSAOPROPCOM;

         IF V_QTDE = V_QT_SERV_CALC THEN
            V_SITUACAO := 'CALCULADA';
         END IF;
      END IF;
   END IF;

   RETURN V_SITUACAO;
END;
/