SET TERM ^ ;

ALTER TABLE TBCOMISSAO
ADD VALOR_ACRESCIMO NUMERIC(15,2)^

ALTER TABLE TBCOMISSAO
ADD VALOR_DESCONTO NUMERIC(15,2)^

ALTER TABLE TBCOMISSAO
ADD PERCENTUAL_ACRESCIMO NUMERIC(18,5)^

ALTER TABLE TBCOMISSAO
ADD PERCENTUAL_DESCONTO NUMERIC(18,5)^

CREATE OR ALTER TRIGGER TTBCOMISSAO_B01U FOR TBCOMISSAO
ACTIVE BEFORE UPDATE POSITION 3
AS
DECLARE VARIABLE HISTORICO        VARCHAR(1000);
DECLARE VARIABLE HISTORICO_STATUS VARCHAR(1000);
DECLARE VARIABLE HISTORICO_SALDO  VARCHAR(1000);
DECLARE VARIABLE STATUS_PROVISAO  VARCHAR(1);
BEGIN
     HISTORICO       = '';
     HISTORICO_SALDO = '';
     HISTORICO_STATUS= '';
     STATUS_PROVISAO = COALESCE((SELECT FIRST 1 PREV.PENDENTE FROM TBCAIXABANCO_PREVISAO PREV WHERE PREV.CONTROLE = NEW.PREVISAO_ID AND NEW.PREVISAO_ID > 0),0);


     IF((NEW.REPRESENTANTE_ID               <> OLD.REPRESENTANTE_ID
      OR OLD.DATA_VENCIMENTO                <> NEW.DATA_VENCIMENTO
      OR OLD.ORIGEM                         <> NEW.ORIGEM
      OR OLD.ESTABELECIMENTO_ID             <> NEW.ESTABELECIMENTO_ID
      OR OLD.VALOR                          <> NEW.VALOR
      OR OLD.DATA_EMISSAO                   <> NEW.DATA_EMISSAO
      OR OLD.CORRETOR_ID                    <> NEW.CORRETOR_ID
      OR OLD.VENDEDOR_ID                    <> NEW.VENDEDOR_ID
      OR OLD.STATUS                         <> NEW.STATUS
      OR COALESCE(OLD.VALOR_DESCONTO,0.00)  <> COALESCE(NEW.VALOR_DESCONTO,0.00)
      OR COALESCE(OLD.VALOR_ACRESCIMO,0.00) <> COALESCE(NEW.VALOR_ACRESCIMO,0.00))
      AND (OLD.SALDO = 0 AND STATUS_PROVISAO = '0'))THEN
     BEGIN
        EXCEPTION "..." 'COMISSAO '||FN_LPAD(OLD.ID,5,0)||', DT.EMISSAO:'||FN_DATE(OLD.DATA_EMISSAO,'DD/MM/YYYY')||', VALOR:'||OLD.VALOR||' JA UTILIZADA, ALTERACAO CANCELADA!';
     END

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

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

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

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

     IF(COALESCE(NEW.VALOR_ACRESCIMO,0.00) <> COALESCE(OLD.VALOR_ACRESCIMO,0.00))THEN
     BEGIN
          HISTORICO = HISTORICO||' VR.ACRES:'||COALESCE(OLD.VALOR_ACRESCIMO,0.00)||'->'||COALESCE(NEW.VALOR_ACRESCIMO,0.00);
     END

     IF(COALESCE(NEW.VALOR_DESCONTO,0.00) <> COALESCE(OLD.VALOR_DESCONTO,0.00))THEN
     BEGIN
          HISTORICO = HISTORICO||' VR.DESC:'||COALESCE(OLD.VALOR_DESCONTO,0.00)||'->'||COALESCE(NEW.VALOR_DESCONTO,0.00);
     END

     IF(NEW.STATUS <> OLD.STATUS)THEN
     BEGIN
       IF(NEW.STATUS = '0')THEN
       BEGIN
          HISTORICO_STATUS = HISTORICO_STATUS||' COMISSAO INATIVADA';
       END
       IF(NEW.STATUS = '1')THEN
       BEGIN
          HISTORICO_STATUS = HISTORICO_STATUS||' COMISSAO REATIVADA';
       END
     END

     IF(NEW.DATA_VENCIMENTO <> OLD.DATA_VENCIMENTO)THEN
     BEGIN
          HISTORICO = HISTORICO||' DT.VENC:'||FORMATDATE(OLD.DATA_VENCIMENTO)||'->'||FORMATDATE(NEW.DATA_VENCIMENTO);
     END

     IF(NEW.SALDO <> OLD.SALDO)THEN
     BEGIN
          IF(NEW.SALDO = 0)THEN
          BEGIN
             HISTORICO_SALDO = HISTORICO_SALDO||' COMISSAO BAIXADA.';
          END
          
          IF(NEW.SALDO = OLD.VALOR)THEN
          BEGIN
             HISTORICO_SALDO = HISTORICO_SALDO||' BAIXA DE COMISSAO DESFEITA.';
          END
     END

     IF(HISTORICO <> '') THEN
     BEGIN
          EXECUTE PROCEDURE SPI_HISTORICO('TBCOMISSAO', OLD.ID, HISTORICO);
     END

     IF(HISTORICO_SALDO <> '') THEN
     BEGIN
          EXECUTE PROCEDURE SPI_HISTORICO('TBCOMISSAO', OLD.ID, HISTORICO_SALDO);
     END             

     IF(HISTORICO_STATUS <> '') THEN
     BEGIN
          EXECUTE PROCEDURE SPI_HISTORICO('TBCOMISSAO', OLD.ID, HISTORICO_STATUS);
     END
END^

CREATE OR ALTER TRIGGER TBCOMISSAO_B02U FOR TBCOMISSAO
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
/*TRIGGER PARA CALCULO DE ACRESCIMOS/DESCONTOS EM COMISSOES*/
  IF(COALESCE(NEW.VALOR_DESCONTO,0.00)  <> COALESCE(OLD.VALOR_DESCONTO,0.00)
  OR COALESCE(NEW.VALOR_ACRESCIMO,0.00) <> COALESCE(OLD.VALOR_ACRESCIMO,0.00))THEN
  BEGIN

     IF(NEW.VALOR_ACRESCIMO > 0)THEN
     BEGIN
        NEW.SALDO                = OLD.VALOR;
        NEW.PERCENTUAL_ACRESCIMO = CAST(((CAST(NEW.VALOR_ACRESCIMO AS DOUBLE PRECISION)*100.00)/CAST(OLD.VALOR AS DOUBLE PRECISION)) AS NUMERIC(18,5));
        NEW.SALDO                = CAST(NEW.SALDO AS DOUBLE PRECISION) + CAST(NEW.VALOR_ACRESCIMO AS DOUBLE PRECISION);
     END ELSE
     BEGIN
        NEW.SALDO                = OLD.SALDO - COALESCE(OLD.VALOR_ACRESCIMO,0.00);
        NEW.PERCENTUAL_ACRESCIMO = 0.00000;
     END

     IF(NEW.VALOR_DESCONTO > 0)THEN
     BEGIN
        NEW.SALDO                = OLD.VALOR;
        NEW.PERCENTUAL_DESCONTO  = CAST(((CAST(NEW.VALOR_DESCONTO AS DOUBLE PRECISION)*100.00)/CAST(OLD.VALOR AS DOUBLE PRECISION)) AS NUMERIC(18,5));
        NEW.SALDO                = CAST(NEW.SALDO AS DOUBLE PRECISION) - CAST(NEW.VALOR_DESCONTO AS DOUBLE PRECISION);
     END ELSE
     BEGIN
        NEW.SALDO                = OLD.SALDO + COALESCE(OLD.VALOR_DESCONTO,0.00);
        NEW.PERCENTUAL_DESCONTO  = 0.00000;
     END
  END

  IF(NEW.VALOR_DESCONTO > (COALESCE(NEW.VALOR_ACRESCIMO,0.00)+NEW.VALOR)) THEN
  BEGIN
     EXCEPTION "..." 'VALOR DESCONTADO (R$ '||FN_FORMAT_NUMBER2(NEW.VALOR_DESCONTO,2)||') ESTA ACIMA DO VALOR DA COMISSAO (R$ '||FN_FORMAT_NUMBER2((COALESCE(NEW.VALOR_ACRESCIMO,0.00)+NEW.VALOR),2)||'). <BR> OPERACAO CANCELADA!';
  END
END^

^ SET TERM ; ^