ALTER TABLE WEBSERVICE ADD WSQRCODE VARCHAR2(250) NULL
/

DECLARE
   V_CDPAIS VARCHAR2(04);
   V_WSQRCODE VARCHAR2(255);
BEGIN
   BEGIN
      SELECT CDPAIS
        INTO  V_CDPAIS
        FROM PAIS
       WHERE UPPER(NMPAIS) LIKE 'BRASIL';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDPAIS := NULL; 
   END;

   IF V_CDPAIS IS NULL THEN
      BEGIN
         SELECT CDPAIS
           INTO  V_CDPAIS
           FROM PAIS
          WHERE UPPER(NMPAIS) LIKE 'BRAZIL';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDPAIS := NULL;
      END;
   END IF;

   IF V_CDPAIS IS NOT NULL THEN
      FOR C_WEBSERVICE IN (SELECT CDPAIS, SGESTADO, IDSERVICO, IDAMBIENTE
                             FROM WEBSERVICE
                            WHERE IDSERVICO IN ('MDF', 'CTE')
                              AND WSQRCODE IS NULL
                              AND CDPAIS = V_CDPAIS
                              AND SGESTADO IN ('AC', 'AL', 'AM', 'AP', 'BA', 'CE', 'DF', 'ES', 'GO', 
                                               'MA', 'MG', 'MS', 'MT', 'PA', 'PB', 'PE', 'PI', 'PR', 
                                               'RJ', 'RN', 'RO', 'RR', 'RS', 'SC', 'SE', 'SP', 'TO'))
      LOOP
         V_WSQRCODE := NULL;
         
         IF C_WEBSERVICE.IDSERVICO = 'CTE' THEN
            IF C_WEBSERVICE.IDAMBIENTE = '1' THEN
               IF C_WEBSERVICE.SGESTADO = 'MG' THEN
                  V_WSQRCODE := 'https://cte.fazenda.mg.gov.br/portalcte/sistema/qrcode.xhtml';
               ELSIF C_WEBSERVICE.SGESTADO = 'MS' THEN
                  V_WSQRCODE := 'http://www.dfe.ms.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO = 'MT' THEN
                  V_WSQRCODE := 'https://www.sefaz.mt.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO = 'PR' THEN
                  V_WSQRCODE := 'http://www.fazenda.pr.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO IN ('AP', 'PE', 'RR', 'SP') THEN
                  V_WSQRCODE := 'https://nfe.fazenda.sp.gov.br/CTeConsulta/qrCode';
               ELSIF C_WEBSERVICE.SGESTADO IN ('AC', 'AL', 'AM', 'BA', 'CE', 'DF', 'ES', 'GO', 'MA', 'PA', 'PB', 'PI', 'RJ', 'RN', 'RO', 'RS', 'SC', 'SE', 'TO') THEN
                  V_WSQRCODE := 'https://dfe-portal.svrs.rs.gov.br/cte/qrCode';
               END IF;
            ELSIF C_WEBSERVICE.IDAMBIENTE = '2' THEN
               IF C_WEBSERVICE.SGESTADO = 'MG' THEN
                  V_WSQRCODE := 'https://hcte.fazenda.mg.gov.br/portalcte/sistema/qrcode.xhtml';
               ELSIF C_WEBSERVICE.SGESTADO = 'MS' THEN
                  V_WSQRCODE := 'http://www.dfe.ms.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO = 'MT' THEN
                  V_WSQRCODE := 'https://homologacao.sefaz.mt.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO = 'PR' THEN
                  V_WSQRCODE := 'http://www.fazenda.pr.gov.br/cte/qrcode';
               ELSIF C_WEBSERVICE.SGESTADO IN ('AP', 'PE', 'RR', 'SP') THEN
                  V_WSQRCODE := 'https://homologacao.nfe.fazenda.sp.gov.br/CTeConsulta/qrCode';
               ELSIF C_WEBSERVICE.SGESTADO IN ('AC', 'AL', 'AM', 'BA', 'CE', 'DF', 'ES', 'GO', 'MA', 'PA', 'PB', 'PI', 'RJ', 'RN', 'RO', 'RS', 'SC', 'SE', 'TO') THEN
                  V_WSQRCODE := 'https://dfe-portal.svrs.rs.gov.br/cte/qrCode';
               END IF;
            END IF;
         ELSIF C_WEBSERVICE.IDSERVICO = 'MDF' THEN
            V_WSQRCODE := 'https://dfe-portal.svrs.rs.gov.br/mdfe/qrCode';
         END IF;

         UPDATE WEBSERVICE
            SET WSQRCODE = V_WSQRCODE 
          WHERE CDPAIS = V_CDPAIS
            AND SGESTADO = C_WEBSERVICE.SGESTADO
            AND IDSERVICO = C_WEBSERVICE.IDSERVICO
            AND IDAMBIENTE = C_WEBSERVICE.IDAMBIENTE;
      
         COMMIT;
      END LOOP;
   END IF;
END;