Fórum Exemplos funcionais de SQLs sob FB 2.1.1 #60785

UPDATE NFVENDA SET
CODCFOPCST = (SELECT CFOPCST.CODIGO FROM CFOPCST WHERE NFVENDA.CODCFOP=CFOPCST.CODCFOP AND NFVENDA.CODSITUACAOTRIBUTARIA=CFOPCST.CODSITUACAOTRIBUTARIA);

CREATE OR ALTER PROCEDURE CRIARCFOPCST 
AS
DECLARE VARIABLE AUXCODSITUACAOTRIBUTARIA INTEGER;
DECLARE VARIABLE AUXCODCFOP INTEGER;
BEGIN
  FOR SELECT CODCFOP, CODSITUACAOTRIBUTARIA FROM NFVENDA GROUP BY CODCFOP, CODSITUACAOTRIBUTARIA
  INTO AUXCODCFOP, AUXCODSITUACAOTRIBUTARIA DO
  BEGIN
    INSERT INTO CFOPCST (CODIGO, CODCFOP, CODSITUACAOTRIBUTARIA) VALUES (NEXT VALUE FOR G_CFOPCST, :AUXCODCFOP, :AUXCODSITUACAOTRIBUTARIA);
  END
  SUSPEND;
END;

GRANT SELECT ON NFVENDA TO PROCEDURE CRIARCFOPCST;

GRANT INSERT ON CFOPCST TO PROCEDURE CRIARCFOPCST;

GRANT EXECUTE ON PROCEDURE CRIARCFOPCST TO SYSDBA;



EXECUTE PROCEDURE CRIARCFOPCST;

UPDATE CFOPCST SET
CFOPCST = (SELECT CFOP.CODIGOINTERNO FROM CFOP WHERE CFOPCST.CODCFOP=CFOP.CODIGO) || ´ - ´ || (SELECT SITUACAOTRIBUTARIA.SITUACAOTRIBUTARIA FROM SITUACAOTRIBUTARIA WHERE CFOPCST.CODSITUACAOTRIBUTARIA=SITUACAOTRIBUTARIA.CODIGO);

DROP PROCEDURE CRIARCFOPCST;