UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO)
	VALUES (34060, 'FOLP', 'FOLP', '1', '', 'GERAÇÃO DA FOLHA DE PAGAMENTO')
	MATCHING (CONTROLE);

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


CREATE GENERATOR GTBFOLHAPAGAMENTO;

CREATE TABLE TBFOLHAPAGAMENTO (
    ID                  INTEGER NOT NULL,
    ESTABELECIMENTO_ID  INTEGER,
    DATA_FOLHA          DATE,
    FOLPAG_CONTABIL_ID  INTEGER,
    OBSERVACAO          VARCHAR(200)
);




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

ALTER TABLE TBFOLHAPAGAMENTO ADD CONSTRAINT PK_TBFOLHAPAGAMENTO PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBFOLHAPAGAMENTO_01 ON TBFOLHAPAGAMENTO (ESTABELECIMENTO_ID);
CREATE INDEX ITBFOLHAPAGAMENTO_02 ON TBFOLHAPAGAMENTO (DATA_FOLHA);
CREATE INDEX ITBFOLHAPAGAMENTO_03 ON TBFOLHAPAGAMENTO (FOLPAG_CONTABIL_ID);

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


CREATE GENERATOR GTBFOLHAPAGAMENTO_COLABORADOR;

CREATE TABLE TBFOLHAPAGAMENTO_COLABORADOR (
    ID                        INTEGER NOT NULL,
    TBFOLHAPAGAMENTO_ID       INTEGER,
    COLABORADOR_ID            INTEGER,
    CARGO_ID                  INTEGER,
    SALARIO_BASE              NUMERIC(15,2),
    BASE_INSS                 NUMERIC(15,2),
    BASE_FGTS                 NUMERIC(15,2),
    VALOR_FGTS                NUMERIC(15,2),
    FGTS_CONTRIBUICAO_SOCIAL  NUMERIC(15,2),
    BASE_IRRF                 NUMERIC(15,2),
    VALOR_LIQUIDO             NUMERIC(15,2),
    INC_PADRAO                CHAR(1) DEFAULT '0'
);




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

ALTER TABLE TBFOLHAPAGAMENTO_COLABORADOR ADD CONSTRAINT PK_TBFOLHAPAGAMENTO_COLABORADOR PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBFOLHAPAGAMENTO_COLABORAD_01 ON TBFOLHAPAGAMENTO_COLABORADOR (TBFOLHAPAGAMENTO_ID);
CREATE INDEX ITBFOLHAPAGAMENTO_COLABORAD_02 ON TBFOLHAPAGAMENTO_COLABORADOR (COLABORADOR_ID);
CREATE INDEX ITBFOLHAPAGAMENTO_COLABORAD_03 ON TBFOLHAPAGAMENTO_COLABORADOR (CARGO_ID);

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


CREATE GENERATOR GTBFOLHAPAGAMENTO_VERBAS;

CREATE TABLE TBFOLHAPAGAMENTO_VERBAS (
    ID                               INTEGER NOT NULL,
    TBFOLHAPAGAMENTO_COLABORADOR_ID  INTEGER,
    VERBA_ID                         INTEGER,
    NATUREZA                         CHAR(1),
    VALOR                            NUMERIC(15,2),
    INC_PADRAO                       CHAR(1) DEFAULT '0'
);




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

ALTER TABLE TBFOLHAPAGAMENTO_VERBAS ADD CONSTRAINT PK_TBFOLHAPAGAMENTO_VERBAS PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBFOLHAPAGAMENTO_VERBAS_01 ON TBFOLHAPAGAMENTO_VERBAS (TBFOLHAPAGAMENTO_COLABORADOR_ID);
CREATE INDEX ITBFOLHAPAGAMENTO_VERBAS_02 ON TBFOLHAPAGAMENTO_VERBAS (VERBA_ID);

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

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



SET TERM ^ ;



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



/* Trigger: TTBFOLHAPAGAMENTO_B01D */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_B01D FOR TBFOLHAPAGAMENTO
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('TBFOLHAPAGAMENTO', OLD.ID, HISTORICO);
    END
END
^


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


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

DECLARE VARIABLE OLD_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE NEW_ESTABELECIMENTO VARCHAR(500);
DECLARE VARIABLE OLD_FOLPAG_CONTABIL VARCHAR(500);  
DECLARE VARIABLE NEW_FOLPAG_CONTABIL VARCHAR(500);
BEGIN
    
    HISTORICO = '';

    OLD_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.NOMEFANTASIA FROM TBESTABELECIMENTO A WHERE A.CODIGO = OLD.ESTABELECIMENTO_ID), '');
    NEW_ESTABELECIMENTO = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 4, '0')||' - '||A.NOMEFANTASIA FROM TBESTABELECIMENTO A WHERE A.CODIGO = NEW.ESTABELECIMENTO_ID), '');
    OLD_FOLPAG_CONTABIL = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 6, '0')||' - '||B.DESCRICAO_ABREVIADA FROM TBFOLPAG_VERBA B WHERE B.ID = OLD.FOLPAG_CONTABIL_ID), '');
    NEW_FOLPAG_CONTABIL = COALESCE((SELECT FIRST 1 FN_LPAD(B.ID, 6, '0')||' - '||B.DESCRICAO_ABREVIADA FROM TBFOLPAG_VERBA B WHERE B.ID = NEW.FOLPAG_CONTABIL_ID), '');

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

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

    IF (OLD.FOLPAG_CONTABIL_ID <> NEW.FOLPAG_CONTABIL_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' FOLHA PAG:'||OLD_FOLPAG_CONTABIL||'->'||NEW_FOLPAG_CONTABIL;
    END

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

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


/* Trigger: TTBFOLHAPAGAMENTO_B02D */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_B02D FOR TBFOLHAPAGAMENTO
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBFOLHAPAGAMENTO_COLABORADOR A WHERE A.TBFOLHAPAGAMENTO_ID = OLD.ID; /* Exclui os registros filhos da folha - colaboradores */
END
^


/* Trigger: TTBFOLHAPAGAMENTO_B02I */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_B02I FOR TBFOLHAPAGAMENTO
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('TBFOLHAPAGAMENTO', NEW.ID, HISTORICO);
    END
END
^


/* Trigger: TTBFOLHAPAGAMENTO_B02U */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_B02U FOR TBFOLHAPAGAMENTO
ACTIVE BEFORE UPDATE POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
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('TBFOLHAPAGAMENTO', OLD.ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^

SET TERM ; ^



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

COMMENT ON COLUMN TBFOLHAPAGAMENTO.FOLPAG_CONTABIL_ID IS 
'ID da Folha pela Tabela TBFOLPAG_CONTABIL';


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



SET TERM ^ ;



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



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

DECLARE VARIABLE OLD_COLABORADOR VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.INC_PADRAO = 0) THEN
    BEGIN

        OLD_COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 5, '0')||' - '||A.PESSOAL_NOME FROM TBCOLABORADOR A WHERE A.CODIGO = OLD.COLABORADOR_ID), '');

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

    END

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


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


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

DECLARE VARIABLE OLD_COLABORADOR VARCHAR(500);
DECLARE VARIABLE NEW_COLABORADOR VARCHAR(500);
DECLARE VARIABLE OLD_CARGO VARCHAR(500);
DECLARE VARIABLE NEW_CARGO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.INC_PADRAO = 0) THEN
    BEGIN

        OLD_COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 5, '0')||' - '||A.PESSOAL_NOME FROM TBCOLABORADOR A WHERE A.CODIGO = OLD.COLABORADOR_ID), '');
        NEW_COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 5, '0')||' - '||A.PESSOAL_NOME FROM TBCOLABORADOR A WHERE A.CODIGO = NEW.COLABORADOR_ID), '');
        OLD_CARGO = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 4, '0')||' - '||B.DESCRICAO FROM TBCARGO B WHERE B.CODIGO = OLD.CARGO_ID), '');
        NEW_CARGO = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 4, '0')||' - '||B.DESCRICAO FROM TBCARGO B WHERE B.CODIGO = NEW.CARGO_ID), '');

        IF (OLD.COLABORADOR_ID <> NEW.COLABORADOR_ID) THEN
        BEGIN
            HISTORICO = HISTORICO||' COLABORADOR:'||OLD_COLABORADOR||'->'||NEW_COLABORADOR;
        END

        IF (OLD.CARGO_ID <> NEW.CARGO_ID) THEN
        BEGIN
            HISTORICO = HISTORICO||' CARGO:'||OLD_CARGO||'->'||NEW_CARGO;
        END

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

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

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

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

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

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

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

    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'COLABORADOR:'||NEW_COLABORADOR||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLHAPAGAMENTO', OLD.TBFOLHAPAGAMENTO_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBFOLHAPAGAMENTO_COLAB_B02D */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_COLAB_B02D FOR TBFOLHAPAGAMENTO_COLABORADOR
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBFOLHAPAGAMENTO_VERBAS A WHERE A.TBFOLHAPAGAMENTO_COLABORADOR_ID = OLD.ID; /* Exclui os registros filhos do colaborador da folha - verbas */
END
^


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

DECLARE VARIABLE NEW_COLABORADOR VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.INC_PADRAO = 0) THEN
    BEGIN

        NEW_COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.CODIGO, 5, '0')||' - '||A.PESSOAL_NOME FROM TBCOLABORADOR A WHERE A.CODIGO = NEW.COLABORADOR_ID), '');

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

    END

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

SET TERM ; ^



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

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.SALARIO_BASE IS 
'Salário Base registrado na TBCOLABORADOR_SALARIO';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.BASE_INSS IS 
'Salário que será utilizado para o Cálculo do INSS';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.BASE_FGTS IS 
'Salário que será utilizado para o Cálculo do FGTS';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.VALOR_FGTS IS 
'Valor do FGTS';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.VALOR_LIQUIDO IS 
'Valor líquido do colaborador';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_COLABORADOR.INC_PADRAO IS 
'0 - Incluído Manualmente;
1 - Incluído via geração;';

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



SET TERM ^ ;



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



/* Trigger: TTBFOLHAPAGAMENTO_VERBAS_B01D */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_VERBAS_B01D FOR TBFOLHAPAGAMENTO_VERBAS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE TBFOLHAPAGAMENTO_ID INTEGER;
DECLARE VARIABLE COLABORADOR varchar(500);

DECLARE VARIABLE OLD_VERBA VARCHAR(500);
BEGIN

    HISTORICO = '';
    TBFOLHAPAGAMENTO_ID = COALESCE((SELECT FIRST 1 A.ID FROM TBFOLHAPAGAMENTO_COLABORADOR A WHERE A.ID = OLD.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);
    COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 5, '0')||' - '||B.PESSOAL_NOME FROM TBFOLHAPAGAMENTO_COLABORADOR A INNER JOIN TBCOLABORADOR B ON B.CODIGO = A.COLABORADOR_ID WHERE A.ID = OLD.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);

    IF (OLD.INC_PADRAO = 0) THEN
    BEGIN

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

        IF (OLD.ID > 0) THEN
        BEGIN
            HISTORICO = HISTORICO||'VERBA:'||OLD_VERBA||' EXCLUIDA NO COLABORADOR:'||COLABORADOR;
        END

    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLHAPAGAMENTO', TBFOLHAPAGAMENTO_ID, HISTORICO);
    END
END
^


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


/* Trigger: TTBFOLHAPAGAMENTO_VERBAS_B01U */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_VERBAS_B01U FOR TBFOLHAPAGAMENTO_VERBAS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE TBFOLHAPAGAMENTO_ID INTEGER;
DECLARE VARIABLE COLABORADOR varchar(500);

DECLARE VARIABLE OLD_VERBA VARCHAR(500);
DECLARE VARIABLE NEW_VERBA VARCHAR(500);
BEGIN

    HISTORICO = '';
    TBFOLHAPAGAMENTO_ID = COALESCE((SELECT FIRST 1 A.ID FROM TBFOLHAPAGAMENTO_COLABORADOR A WHERE A.ID = OLD.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);
    COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 5, '0')||' - '||B.PESSOAL_NOME FROM TBFOLHAPAGAMENTO_COLABORADOR A INNER JOIN TBCOLABORADOR B ON B.CODIGO = A.COLABORADOR_ID WHERE A.ID = OLD.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);

    IF (OLD.INC_PADRAO = 0) THEN
    BEGIN

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

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

        IF (OLD.NATUREZA <> NEW.NATUREZA) THEN
        BEGIN
            HISTORICO = HISTORICO||' NATUREZA:'||OLD.NATUREZA||'->'||NEW.NATUREZA;
        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

    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'VERBA:'||NEW_VERBA||' DO COLABORADOR:'||COLABORADOR||' ALTERADA:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLHAPAGAMENTO', TBFOLHAPAGAMENTO_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBFOLHAPAGAMENTO_VERBAS_B02I */
CREATE OR ALTER TRIGGER TTBFOLHAPAGAMENTO_VERBAS_B02I FOR TBFOLHAPAGAMENTO_VERBAS
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE TBFOLHAPAGAMENTO_ID INTEGER;
DECLARE VARIABLE COLABORADOR varchar(500);

DECLARE VARIABLE NEW_VERBA VARCHAR(500);
BEGIN

    HISTORICO = '';
    TBFOLHAPAGAMENTO_ID = COALESCE((SELECT FIRST 1 A.ID FROM TBFOLHAPAGAMENTO_COLABORADOR A WHERE A.ID = NEW.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);
    COLABORADOR = COALESCE((SELECT FIRST 1 FN_LPAD(B.CODIGO, 5, '0')||' - '||B.PESSOAL_NOME FROM TBFOLHAPAGAMENTO_COLABORADOR A INNER JOIN TBCOLABORADOR B ON B.CODIGO = A.COLABORADOR_ID WHERE A.ID = NEW.TBFOLHAPAGAMENTO_COLABORADOR_ID), 0);

    IF (NEW.INC_PADRAO = 0) THEN
    BEGIN

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

        IF (NEW.ID > 0) THEN
        BEGIN
            HISTORICO = HISTORICO||'VERBA:'||NEW_VERBA||' INCLUIDA NO COLABORADOR:'||COLABORADOR;
        END

    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLHAPAGAMENTO', TBFOLHAPAGAMENTO_ID, HISTORICO);
    END
END
^

SET TERM ; ^



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

COMMENT ON COLUMN TBFOLHAPAGAMENTO_VERBAS.NATUREZA IS 
'D - Devedora;
C - Credora;';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_VERBAS.INC_PADRAO IS 
'0 - Incluído Manualmente;
1 - Incluído via geração;';