SET TERM ^ ;

ALTER TABLE TBREP
ADD PORTARIA_671 SMALLINT
DEFAULT 0 ^

COMMENT ON COLUMN TBREP.PORTARIA_671 IS
'INDICA SE OS REGISTROS DE PONTO IMPORTADOS DEVEM SEGUIR A NORMA DA PORTARIA 671'^

CREATE OR ALTER TRIGGER TTBREP_B01U FOR TBREP
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF ( OLD.CODIGO <> NEW.CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||'CÃ³DIGO: '|| OLD.CODIGO||'->'||NEW.CODIGO;
    END
    
    IF ( OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
        HISTORICO = HISTORICO||', DESC. '|| OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END
    
    IF ( OLD.CONSULTA <> NEW.CONSULTA) THEN
    BEGIN
        HISTORICO = HISTORICO||', CONSULTA: '|| OLD.CONSULTA||'->'||NEW.CONSULTA;
    END

    IF ( OLD.STATUS <> NEW.STATUS) THEN
    BEGIN
        HISTORICO = HISTORICO||', STATUS: '|| OLD.STATUS||'->'||NEW.STATUS;
    END
    
    IF ( OLD.SEQ <> NEW.SEQ) THEN
    BEGIN
        HISTORICO = HISTORICO||', SEQ. '|| OLD.SEQ||'->'||NEW.SEQ;
    END 
    
    IF ( COALESCE(OLD.PORTARIA_671,0) <> COALESCE(NEW.PORTARIA_671,0)) THEN
    BEGIN
        HISTORICO = HISTORICO||', PORT.671 '|| COALESCE(OLD.PORTARIA_671,0)||'->'||COALESCE(NEW.PORTARIA_671,0);
    END
    
    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBREP',NEW.ID, HISTORICO);
    END
END^

ALTER TABLE TBPONTO_MARCACAO ADD DOCUMENTO_CPF VARCHAR(11)^

CREATE OR ALTER TRIGGER TTBPONTO_MARCACAO_B0I FOR TBPONTO_MARCACAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.CONTROLE1 IS NULL) THEN
  BEGIN
    NEW.CONTROLE1 = GEN_ID(GTBPONTO_MARCACAO,1);
  END

  IF(NEW.DOCUMENTO_CPF IS NULL) THEN
  BEGIN
     NEW.DOCUMENTO_CPF = COALESCE((SELECT FIRST 1 COLAB.DOCUMENTO_CPF FROM TBCOLABORADOR COLAB WHERE COLAB.DOCUMENTO_PIS = NEW.DOCUMENTO_PIS AND COLAB.CODIGO = NEW.COLABORADOR_CODIGO),'');
  END
END^

SET TERM ; ^