SET TERM ^ ;

ALTER TABLE TBCONCILIACAO_CARTAO
ADD DATA_ALTERACAO DATE^

ALTER TABLE TBCONCILIACAO_CARTAO
ADD VALOR_ORIGINAL NUMERIC(15,4)
DEFAULT 0^

ALTER TABLE TBCONCILIACAO_CARTAO
ADD TARIFA_ORIGINAL NUMERIC(15,4)
DEFAULT 0^

ALTER TABLE TBCONCILIACAO_CARTAO
ADD MOTIVO_ALTERACAO VARCHAR(200)
DEFAULT ''^

ALTER TABLE TBCONCILIACAO_CARTAO
ADD PROTOCOLO_CANCELAMENTO VARCHAR(200)
DEFAULT ''^

ALTER TABLE TBCONCILIACAO_CARTAO_PARCELAS
ADD VALOR_ORIGINAL NUMERIC(15,4)
DEFAULT 0^

ALTER TABLE TBCONCILIACAO_CARTAO_PARCELAS
ADD TARIFA_ORIGINAL NUMERIC(15,4)
DEFAULT 0^

ALTER TABLE TBCONCILIACAO_CARTAO_PARCELAS
ADD VALOR_LIQ_ORIGINAL NUMERIC(15,4)
DEFAULT 0^

UPDATE TBCONCILIACAO_CARTAO          SET VALOR_ORIGINAL     = VALOR         WHERE VALOR_ORIGINAL IS NULL^
UPDATE TBCONCILIACAO_CARTAO          SET TARIFA_ORIGINAL    = DESCONTO1     WHERE TARIFA_ORIGINAL IS NULL^

UPDATE TBCONCILIACAO_CARTAO_PARCELAS SET VALOR_ORIGINAL     = VALOR         WHERE VALOR_ORIGINAL IS NULL^
UPDATE TBCONCILIACAO_CARTAO_PARCELAS SET TARIFA_ORIGINAL    = DESCONTO1     WHERE TARIFA_ORIGINAL IS NULL^
UPDATE TBCONCILIACAO_CARTAO_PARCELAS SET VALOR_LIQ_ORIGINAL = VALOR_LIQUIDO WHERE VALOR_LIQ_ORIGINAL IS NULL^

CREATE OR ALTER TRIGGER TTBCONCILIACAO_CARTAO_B02I FOR TBCONCILIACAO_CARTAO
ACTIVE BEFORE INSERT POSITION 2
AS
BEGIN
  IF(TRIM(NEW.CODIGO_AUTORIZACAO) = '' OR TRIM(NEW.CODIGO_AUTORIZACAO) = '0' OR COALESCE(NEW.CODIGO_AUTORIZACAO,'') = '')THEN
  BEGIN
     NEW.CODIGO_AUTORIZACAO = NEW.NSU;
  END

  IF(NEW.VALOR_ORIGINAL IS NULL)THEN
  BEGIN
     NEW.VALOR_ORIGINAL = NEW.VALOR;
  END

  IF(NEW.TARIFA_ORIGINAL IS NULL)THEN
  BEGIN
     NEW.TARIFA_ORIGINAL = NEW.DESCONTO1;
  END
END^

CREATE OR ALTER TRIGGER TTBCONCIL_CARTAO_PARCELAS_B01I FOR TBCONCILIACAO_CARTAO_PARCELAS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
  BEGIN
    NEW.ID = GEN_ID(GTBCONCILI_CARTAO_PARCELA,1);
  END

  IF(NEW.VALOR_ORIGINAL IS NULL)THEN
  BEGIN
     NEW.VALOR_ORIGINAL = NEW.VALOR;
  END

  IF(NEW.TARIFA_ORIGINAL IS NULL)THEN
  BEGIN
     NEW.TARIFA_ORIGINAL = NEW.DESCONTO1;
  END

  IF(NEW.VALOR_LIQ_ORIGINAL IS NULL)THEN
  BEGIN
     NEW.VALOR_LIQ_ORIGINAL = NEW.VALOR_LIQUIDO;
  END
END^

CREATE OR ALTER TRIGGER TTBCONCILIACAO_CARTAO_B01U FOR TBCONCILIACAO_CARTAO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

  IF(NEW.CODIGO_AUTORIZACAO = '0' OR NEW.CODIGO_AUTORIZACAO IS NULL) THEN
  BEGIN
     NEW.CODIGO_AUTORIZACAO = NEW.NSU;
  END

  IF(NEW.CONCILIACAO = 1 AND NEW.CONCILIACAO <> OLD.CONCILIACAO)THEN
  BEGIN
     UPDATE TBCONCILIACAO_CARTAO_PARCELAS B
       SET B.CONCILIACAO = 1
     WHERE B.NSU         = NEW.NSU
     AND B.ADQUIRENTE_ID = NEW.ADQUIRENTE_ID
     AND B.MERCHANT_ID   = NEW.MERCHANT_ID
     AND B.DATA_VENDA    = NEW.DATA_VENDA;
  END

  IF(NEW.CONCILIACAO = 0 AND NEW.CONCILIACAO <> OLD.CONCILIACAO)THEN
  BEGIN
     UPDATE TBCONCILIACAO_CARTAO_PARCELAS B
       SET B.CONCILIACAO = 0
     WHERE B.NSU         = NEW.NSU
     AND B.ADQUIRENTE_ID = NEW.ADQUIRENTE_ID
     AND B.MERCHANT_ID   = NEW.MERCHANT_ID
     AND B.DATA_VENDA    = NEW.DATA_VENDA;
  END

  IF(COALESCE(NEW.PROTOCOLO_CANCELAMENTO,'') <> COALESCE(OLD.PROTOCOLO_CANCELAMENTO,''))THEN
  BEGIN
     :HISTORICO = :HISTORICO||' PROTOCOLO: '||COALESCE(OLD.PROTOCOLO_CANCELAMENTO,'')||'->'||COALESCE(NEW.PROTOCOLO_CANCELAMENTO,'');
  END

  IF(COALESCE(NEW.MOTIVO_ALTERACAO,'') <> COALESCE(OLD.MOTIVO_ALTERACAO,''))THEN
  BEGIN
     :HISTORICO = :HISTORICO||', MOTIVO: '||COALESCE(OLD.MOTIVO_ALTERACAO,'')||'->'||COALESCE(NEW.MOTIVO_ALTERACAO,'');
  END

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

  IF(COALESCE(NEW.DESCONTO1,0.0000) <> COALESCE(OLD.DESCONTO1,0.0000))THEN
  BEGIN
     :HISTORICO = :HISTORICO||', TARIFA: '||COALESCE(OLD.DESCONTO1,0.0000)||'->'||COALESCE(NEW.DESCONTO1,0.0000);
  END

  IF(:HISTORICO <> '')THEN
  BEGIN
     HISTORICO = ' VENDA '||FN_LPAD(NEW.ID, 4,0)||',DT:'||FN_DATE(NEW.DATA_VENDA,'DD/MM/YYYY')||' ALTERADA:'||:HISTORICO;
     EXECUTE PROCEDURE SPI_HISTORICO('TBCONCILIACAO_CARTAO',NEW.ID,FN_ELIPSES(:HISTORICO,500));
  END

END^

CREATE OR ALTER TRIGGER TTBCONCIL_CARTAO_PARCELAS_B01U FOR TBCONCILIACAO_CARTAO_PARCELAS
ACTIVE BEFORE UPDATE POSITION 3
AS
DECLARE VARIABLE ID_VENDA INTEGER;
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

  HISTORICO = '';

  ID_VENDA = COALESCE((SELECT FIRST 1 XT.ID FROM TBCONCILIACAO_CARTAO XT
                                WHERE XT.NSU = NEW.NSU AND XT.ADQUIRENTE_ID = NEW.ADQUIRENTE_ID
                                AND XT.DATA_VENDA    = NEW.DATA_VENDA AND XT.MERCHANT_ID   = NEW.MERCHANT_ID),0);

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

  IF(COALESCE(NEW.DESCONTO1,0.0000) <> COALESCE(OLD.DESCONTO1,0.0000))THEN
  BEGIN
     :HISTORICO = :HISTORICO||', TARIFA: '||COALESCE(OLD.DESCONTO1,0.0000)||'->'||COALESCE(NEW.DESCONTO1,0.0000);
  END 

  IF(COALESCE(NEW.VALOR_LIQUIDO,0.0000) <> COALESCE(OLD.VALOR_LIQUIDO,0.0000))THEN
  BEGIN
     :HISTORICO = :HISTORICO||', VALOR LIQ: '||COALESCE(OLD.VALOR_LIQUIDO,0.0000)||'->'||COALESCE(NEW.VALOR_LIQUIDO,0.0000);
  END

  --EXCEPTION "..." 'ID DA VENDA'||ID_VENDA;

  IF(:HISTORICO <> '' AND ID_VENDA > 0)THEN
  BEGIN
     HISTORICO = ' PARC.'||FN_LPAD(NEW.NUMERO_PARCELA, 3,0)||', VALOR:'||COALESCE(OLD.VALOR,0.0000)||' ALTERADA:'||:HISTORICO;
     --EXCEPTION "..." 'HISTORICO: '||HISTORICO;
     EXECUTE PROCEDURE SPI_HISTORICO('TBCONCILIACAO_CARTAO',:ID_VENDA,FN_ELIPSES(:HISTORICO,500));
  END
END^

SET TERM ; ^