SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBCFOP */
CREATE OR ALTER TRIGGER TTBCFOP FOR TBCFOP
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 999
AS
begin

  update tbreplica_tabela set Status = '0', Status_Qlik = '0'
  Where tabela = 'TBCFOP'
    and (Status = '1' or Status_Qlik = '1') ;

end
^


/* Trigger: TTBCFOP_B02D */
CREATE OR ALTER TRIGGER TTBCFOP_B02D FOR TBCFOP
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.CODIGO > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro Excluido: '||OLD.CODIGO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(OLD.CODIGO, '.', '') AS INTEGER), HISTORICO);
    END
END
^


/* Trigger: TTBCFOP_B02I */
CREATE OR ALTER TRIGGER TTBCFOP_B02I FOR TBCFOP
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( NEW.CODIGO > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'Registro incluido';
    END

    IF ((SELECT A.CODIGO FROM TBCFOP A WHERE A.CODIGO = NEW.CODIGO) > 0) then
    BEGIN
        EXCEPTION "..." 'Registro ja existente com os dados informados! CFOP: '||NEW.CODIGO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(NEW.CODIGO, '.', '') AS INTEGER), HISTORICO);
    END
END
^


/* Trigger: TTBCFOP_B02U */
CREATE OR ALTER TRIGGER TTBCFOP_B02U FOR TBCFOP
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Descricao na CFOP: '||OLD.CODIGO||', Alterada: '|| OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END

    IF (OLD.DESCRICAO_COMPLETA <> NEW.DESCRICAO_COMPLETA) THEN
    BEGIN
        HISTORICO = HISTORICO||' Desc. Completa na CFOP: '||OLD.CODIGO||', Alterada: '|| OLD.DESCRICAO_COMPLETA||'->'||NEW.DESCRICAO_COMPLETA;
    END

    IF (OLD.TIPO <> NEW.TIPO) THEN
    BEGIN
        if (NEW.TIPO = 'E') then
           HISTORICO = HISTORICO||' Tipo na CFOP: '||OLD.CODIGO||', Tipo Entrada.';
        if (NEW.TIPO = 'S') then
           HISTORICO = HISTORICO||' Tipo na CFOP: '||OLD.CODIGO||', Tipo Saida.';
    END

    IF (OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
       if (NEW.STATUS = '0') then
           HISTORICO = HISTORICO||' Status na CFOP: '||OLD.CODIGO||', Status Inativado.';
       if (NEW.STATUS = '1') then
           HISTORICO = HISTORICO||' Status na CFOP: '||OLD.CODIGO||', Status Ativado.';
    END

    IF (OLD.BASELEGAL_CODIGO <> NEW.BASELEGAL_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Base Legal na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.BASELEGAL_CODIGO||'->'||NEW.BASELEGAL_CODIGO;
    END

    IF (OLD.CONTA_RECEBER_OPERACAO <> NEW.CONTA_RECEBER_OPERACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Operacao Padrao na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CONTA_RECEBER_OPERACAO||'->'||NEW.CONTA_RECEBER_OPERACAO;
    END

    IF (OLD.CONTA_RECEBER_MODALIDADE <> NEW.CONTA_RECEBER_MODALIDADE) THEN
    BEGIN
        HISTORICO = HISTORICO||' Modalidade na Padrao CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CONTA_RECEBER_MODALIDADE||'->'||NEW.CONTA_RECEBER_MODALIDADE;
    END

    IF (OLD.CONTA_RECEBER_BANCO <> NEW.CONTA_RECEBER_BANCO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Banco Padrao na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CONTA_RECEBER_BANCO||'->'||NEW.CONTA_RECEBER_BANCO;
    END

    IF (OLD.CONTA_RECEBER_COBRANCA <> NEW.CONTA_RECEBER_COBRANCA) THEN
    BEGIN
        HISTORICO = HISTORICO||' Cobranca Padrao CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CONTA_RECEBER_COBRANCA||'->'||NEW.CONTA_RECEBER_COBRANCA;
    END

    IF (OLD.CONTA_RECEBER_POSICAO <> NEW.CONTA_RECEBER_POSICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Posicao Padrao CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CONTA_RECEBER_POSICAO||'->'||NEW.CONTA_RECEBER_POSICAO;
    END

    IF (OLD.VALOR_CONTABIL <> NEW.VALOR_CONTABIL) THEN
    BEGIN
        HISTORICO = HISTORICO||' Gerar Valor Contabil CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.VALOR_CONTABIL||'->'||NEW.VALOR_CONTABIL;
    END

    IF (OLD.DEVOLUCAO_RETORNO <> NEW.DEVOLUCAO_RETORNO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Devolucao e/ou Retorno CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.DEVOLUCAO_RETORNO||'->'||NEW.DEVOLUCAO_RETORNO;
    END

    IF (OLD.IBPTAX <> NEW.IBPTAX) THEN
    BEGIN
        HISTORICO = HISTORICO||' IBIPTAX na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.IBPTAX||'->'||NEW.IBPTAX;
    END

    IF (OLD.SPED_PISCOF <> NEW.SPED_PISCOF) THEN
    BEGIN
        HISTORICO = HISTORICO||' Ger. de Receita/Crédito(PIS/COFINS) na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.SPED_PISCOF||'->'||NEW.SPED_PISCOF;
    END

    IF (OLD.REDUCAO_BASE_PISCOF <> NEW.REDUCAO_BASE_PISCOF) THEN
    BEGIN
        HISTORICO = HISTORICO||' Reduzir Vr. Icms na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.REDUCAO_BASE_PISCOF||'->'||NEW.REDUCAO_BASE_PISCOF;
    END

    IF (OLD.CFOP_INVERSO <> NEW.CFOP_INVERSO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Cfop inverso na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.CFOP_INVERSO||'->'||NEW.CFOP_INVERSO;
    END

    IF (OLD.OPERACAO_FISCAL <> NEW.OPERACAO_FISCAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' Operacao Fiscal na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.OPERACAO_FISCAL||'->'||NEW.OPERACAO_FISCAL;
    END

    IF (OLD.PDV <> NEW.PDV) THEN
    BEGIN
        HISTORICO = HISTORICO||' Habilitar PDV na CFOP: '||OLD.CODIGO||', Alterado: '|| OLD.PDV||'->'||NEW.PDV;
    END

    IF (OLD.FDI <> NEW.FDI) THEN
    BEGIN
        HISTORICO = HISTORICO||' FDI:'||OLD.FDI||'->'||NEW.FDI;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(NEW.CODIGO, '.', '') AS INTEGER), HISTORICO);
    END
END
^


/* Trigger: TTBCFOP_BIUD998 */
CREATE OR ALTER TRIGGER TTBCFOP_BIUD998 FOR TBCFOP
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 998
AS
BEGIN

    IF(INSERTING) THEN
    BEGIN
        EXECUTE PROCEDURE SPI_INTEGRACAO('TBCFOP', CAST(FN_REPLACE(NEW.CODIGO, '.', '') AS INTEGER), 2);
    END ELSE
    BEGIN
        EXECUTE PROCEDURE SPI_INTEGRACAO('TBCFOP', CAST(FN_REPLACE(OLD.CODIGO, '.', '') AS INTEGER), 2);
    END

END
^

SET TERM ; ^


/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBCFOP_INVERSO_REGRA_B01D */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01D FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = OLD.CFOP_CODIGO), '');
    OLD_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = OLD.CFOP_INVERSO_CODIGO), '');
    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    OLD_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = OLD.OPERACAO_FISCAL), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO: '||OLD_CFOP_INVERSO||' EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(OLD.CFOP_CODIGO, '.', '') AS INTEGER), HISTORICO);
    END
END
^


/* Trigger: TTBCFOP_INVERSO_REGRA_B01I */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01I FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE INSERT POSITION 0
AS     
DECLARE VARIABLE CFOP_INVERSO_CODIGO VARCHAR(10);
DECLARE VARIABLE CFOP_INVERSO_ESTAB VARCHAR(500);
BEGIN

    :CFOP_INVERSO_CODIGO = COALESCE((SELECT FIRST 1 A.CFOP_INVERSO_CODIGO FROM TBCFOP_INVERSO_REGRA A WHERE A.CFOP_CODIGO = NEW.CFOP_CODIGO AND COALESCE(A.ESTABELECIMENTO_ID, 0) = NEW.ESTABELECIMENTO_ID), '');
    :CFOP_INVERSO_ESTAB = COALESCE((SELECT FIRST 1 B.RAZAOSOCIAL FROM TBCFOP_INVERSO_REGRA A, TBESTABELECIMENTO B WHERE A.ESTABELECIMENTO_ID = B.CODIGO AND A.CFOP_CODIGO = NEW.CFOP_CODIGO AND COALESCE(A.ESTABELECIMENTO_ID, 0) = NEW.ESTABELECIMENTO_ID), 'TODOS OS ESTABELECIMENTOS');

    IF (COALESCE(:CFOP_INVERSO_CODIGO, '') <> '') THEN
    BEGIN
        EXCEPTION "..." 'O CFOP '||:CFOP_INVERSO_CODIGO||' JA ESTA CONFIGURADO COMO CFOP INVERSO PARA '||:CFOP_INVERSO_ESTAB||'. OPERACAO CANCELADA!';
    END

    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBCFOP_INVERSO_REGRA, 1);
END
^


/* Trigger: TTBCFOP_INVERSO_REGRA_B01U */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B01U FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE OLD_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_OPERACAO_FISCAL VARCHAR(500);
DECLARE VARIABLE NEW_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = OLD.CFOP_CODIGO), '');
    NEW_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = NEW.CFOP_CODIGO), '');
    OLD_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = OLD.CFOP_INVERSO_CODIGO), '');
    NEW_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = NEW.CFOP_INVERSO_CODIGO), '');
    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    OLD_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = OLD.OPERACAO_FISCAL), '');
    NEW_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = NEW.OPERACAO_FISCAL), '');

    IF (OLD.CFOP_CODIGO IS DISTINCT FROM NEW.CFOP_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP:'||OLD_CFOP_CODIGO||'->'||NEW_CFOP_CODIGO;
    END

    IF (OLD.ESTABELECIMENTO_ID IS DISTINCT FROM NEW.ESTABELECIMENTO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' ESTABELECIMENTO:'||OLD_ESTABELECIMENTO||'->'||NEW_ESTABELECIMENTO;
    END

    IF (OLD.CFOP_INVERSO_CODIGO IS DISTINCT FROM NEW.CFOP_INVERSO_CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO:'||OLD_CFOP_INVERSO||'->'||NEW_CFOP_INVERSO;
    END

    IF (OLD.OPERACAO_FISCAL IS DISTINCT FROM NEW.OPERACAO_FISCAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' OP.FISCAL:'||OLD_OPERACAO_FISCAL||'->'||NEW_OPERACAO_FISCAL;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'CFOP INVERSO: '||NEW_CFOP_INVERSO||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(OLD.CFOP_CODIGO, '.', '') AS INTEGER), FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBCFOP_INVERSO_REGRA_B02I */
CREATE OR ALTER TRIGGER TTBCFOP_INVERSO_REGRA_B02I FOR TBCFOP_INVERSO_REGRA
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_CODIGO VARCHAR(500);
DECLARE VARIABLE NEW_CFOP_INVERSO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_OPERACAO_FISCAL VARCHAR(500);
BEGIN

    HISTORICO = '';

    NEW_CFOP_CODIGO     = COALESCE((SELECT FIRST 1 A.CODIGO||' - '||A.DESCRICAO FROM TBCFOP A WHERE A.CODIGO = NEW.CFOP_CODIGO), '');
    NEW_CFOP_INVERSO    = COALESCE((SELECT FIRST 1 B.CODIGO||' - '||B.DESCRICAO FROM TBCFOP B WHERE B.CODIGO = NEW.CFOP_INVERSO_CODIGO), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(C.CODIGO, 4, '0')||' - '||C.RAZAOSOCIAL FROM TBESTABELECIMENTO C WHERE C.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    NEW_OPERACAO_FISCAL = COALESCE((SELECT FIRST 1 D.CODIGO||' - '||D.DESCRICAO FROM TBOPERACAO D WHERE D.CODIGO = NEW.OPERACAO_FISCAL), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||' CFOP INVERSO: '||NEW_CFOP_INVERSO||' INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCFOP', CAST(FN_REPLACE(NEW.CFOP_CODIGO, '.', '') AS INTEGER), HISTORICO);
    END
END
^

SET TERM ; ^