UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO) 
	VALUES (23430, 'RH', 'RH', '1', '', 'REGISTRO DE VERBAS FIXAS')
	MATCHING (CONTROLE);

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

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBFOLPAG_VERBA_COLABORADOR;

CREATE TABLE TBFOLPAG_VERBA_PADRAO (
    ID          INTEGER NOT NULL,
    VERBA_ID    INTEGER,
    STATUS      CHAR(1),
    DATA_REG    DATE DEFAULT 'NOW',
    TIPO        CHAR(1),
    VALOR       NUMERIC(15,4),
    FORMULA     VARCHAR(200),
    OBSERVACAO  VARCHAR(200)
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBFOLPAG_VERBA_PADRAO ADD CONSTRAINT PK_TBFOLPAG_VERBA_PADRAO PRIMARY KEY (ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



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



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

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO EXCLUIDO';
    END

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


/* Trigger: TTBFOLPAG_VERBA_PADRAO_B01I */
CREATE OR ALTER TRIGGER TTBFOLPAG_VERBA_PADRAO_B01I FOR TBFOLPAG_VERBA_PADRAO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBFOLPAG_VERBA_COLABORADOR, 1);
END
^


/* Trigger: TTBFOLPAG_VERBA_PADRAO_B01U */
CREATE OR ALTER TRIGGER TTBFOLPAG_VERBA_PADRAO_B01U FOR TBFOLPAG_VERBA_PADRAO
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE OLD_VERBA VARCHAR(500);
DECLARE VARIABLE NEW_VERBA VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    OLD_VERBA = COALESCE((SELECT FIRST 1 FN_LPAD(V.ID, 6, '0')||' - '||V.DESCRICAO_ABREVIADA FROM TBFOLPAG_VERBA V WHERE V.ID = OLD.VERBA_ID), '');
    NEW_VERBA = COALESCE((SELECT FIRST 1 FN_LPAD(V.ID, 6, '0')||' - '||V.DESCRICAO_ABREVIADA FROM TBFOLPAG_VERBA V WHERE V.ID = NEW.VERBA_ID), '');

    IF (OLD.VERBA_ID <> NEW.VERBA_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' VERBA:'||OLD_VERBA||'->'||NEW_VERBA;
    END

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

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

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

    IF (OLD.VALOR <> NEW.VALOR) THEN
    BEGIN
        HISTORICO = HISTORICO||' VALOR:R$'||FN_FORMAT_NUMBER2(OLD.VALOR, 2)||'->R$'||FN_FORMAT_NUMBER2(NEW.VALOR, 2);
    END

    IF (OLD.FORMULA <> NEW.FORMULA) THEN
    BEGIN
        HISTORICO = HISTORICO||' FORMULA ALTERADA!';
    END

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' OBSERVACAO ALTERADA!';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLPAG_VERBA_PADRAO', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBFOLPAG_VERBA_PADRAO_B02I */
CREATE OR ALTER TRIGGER TTBFOLPAG_VERBA_PADRAO_B02I FOR TBFOLPAG_VERBA_PADRAO
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'REGISTRO INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLPAG_VERBA_PADRAO', NEW.ID, HISTORICO);
    END
END
^


/* Trigger: TTBFOLPAG_VERBA_PADRAO_B02U */
CREATE OR ALTER TRIGGER TTBFOLPAG_VERBA_PADRAO_B02U FOR TBFOLPAG_VERBA_PADRAO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN
    
    HISTORICO = '';

    IF (OLD.FORMULA <> NEW.FORMULA) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSE(OLD.FORMULA, 200)||'->'||FN_ELIPSE(NEW.FORMULA, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' FORMULA ALTERADA: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLPAG_VERBA_PADRAO', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBFOLPAG_VERBA_PADRAO_B03U */
CREATE OR ALTER TRIGGER TTBFOLPAG_VERBA_PADRAO_B03U FOR TBFOLPAG_VERBA_PADRAO
ACTIVE BEFORE UPDATE POSITION 2
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN
    
    HISTORICO = '';

    IF (OLD.OBSERVACAO <> NEW.OBSERVACAO) THEN
    BEGIN
        HISTORICO = HISTORICO||FN_ELIPSE(OLD.OBSERVACAO, 200)||'->'||FN_ELIPSE(NEW.OBSERVACAO, 200);
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = ' OBSERVACAO ALTERADA: '||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLPAG_VERBA_PADRAO', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBFOLPAG_VERBA_PADRAO.STATUS IS 
'0 - INATIVO;
1 - ATIVO;';

COMMENT ON COLUMN TBFOLPAG_VERBA_PADRAO.TIPO IS 
'0 - VALOR FIXO (R$);
1 - FORMULA;';

COMMENT ON COLUMN TBFOLPAG_VERBA_PADRAO.VALOR IS 
'Valor Fixo para uso no cálculo da geração da folha';

COMMENT ON COLUMN TBFOLPAG_VERBA_PADRAO.FORMULA IS 
'Formula para uso no cálculo da geração da folha';