ALTER TABLE TBFOLPAG_VERBA_PADRAO
ALTER COLUMN FORMULA TYPE VARCHAR(5000);

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

ALTER TABLE TBFOLPAG_VERBA
    ADD DESCONTO_IRRF INTEGER DEFAULT 0;

COMMENT ON COLUMN TBFOLPAG_VERBA.DESCONTO_IRRF IS
'Se natureza C (Credora / Debita do Colaborador), possibilita utilizar no valor total do desconto do IRRF

0 - NAO;
1 - SIM;';


SET TERM ^ ;

CREATE OR ALTER trigger ttbfolpag_verba_b01u for tbfolpag_verba
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
DECLARE VARIABLE CODIGO INTEGER;
BEGIN
    HISTORICO = '';
    IF(OLD.CONTACONTABIL <> NEW.CONTACONTABIL) THEN
    BEGIN
        HISTORICO = HISTORICO||' C.Contabil: '||OLD.CONTACONTABIL||'->'||NEW.CONTACONTABIL;
    END

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

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

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

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

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

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

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

    if(OLD.CODIGO <> NEW.CODIGO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Codigo: '||OLD.CODIGO||'->'||NEW.CODIGO;
        CODIGO = COALESCE((SELECT FIRST 1 A.CODIGO FROM TBFOLPAG_VERBA A WHERE A.CODIGO = NEW.CODIGO AND A.ID <> NEW.ID),0);
    END
    IF(CODIGO > 0)THEN
        BEGIN
           EXCEPTION "..." 'VERBA CODIGO: '||CODIGO||' JÁ CADASTRADA! ';
        END
    
    IF(HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBFOLPAG_VERBA',NEW.ID,HISTORICO);
    END
END
^

SET TERM ; ^

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

CREATE GENERATOR GTBFOLPAG_IRRF;

CREATE TABLE TBFOLPAG_IRRF (
    ID             INTEGER NOT NULL,
    VALOR_INICIAL  NUMERIC(15,2),
    VALOR_FINAL    NUMERIC(15,2),
    PERCENTUAL     NUMERIC(15,2),
    TETO           INTEGER DEFAULT 0,
    DATA_INICIAL   DATE,
    DATA_FINAL     DATE,
    VALOR_DEDUCAO  NUMERIC(15,2)
);




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

ALTER TABLE TBFOLPAG_IRRF ADD CONSTRAINT PK_TBFOLPAG_IRRF PRIMARY KEY (ID);


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



SET TERM ^ ;



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



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


/* Trigger: TTBFOLPAG_IRRF_B01I */
CREATE OR ALTER TRIGGER TTBFOLPAG_IRRF_B01I FOR TBFOLPAG_IRRF
ACTIVE BEFORE INSERT POSITION 0
as
begin
    if (new.id is null) then
        new.id = gen_id(gtbfolpag_irrf, 1);
end
^


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

    HISTORICO = '';

    IF (OLD.VALOR_INICIAL IS DISTINCT FROM NEW.VALOR_INICIAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' VL.INICIAL:'|| 'R$ '||FN_FORMAT_NUMBER2(OLD.VALOR_INICIAL, 2) ||'->'|| 'R$ '||FN_FORMAT_NUMBER2(NEW.VALOR_INICIAL, 2);
    END

    IF (OLD.VALOR_FINAL IS DISTINCT FROM NEW.VALOR_FINAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' VL.FINAL:'|| 'R$ '||FN_FORMAT_NUMBER2(OLD.VALOR_FINAL, 2) ||'->'|| 'R$ '||FN_FORMAT_NUMBER2(NEW.VALOR_FINAL, 2);
    END

    IF (OLD.PERCENTUAL IS DISTINCT FROM NEW.PERCENTUAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' PERCENT:'||OLD.PERCENTUAL||'->'||NEW.PERCENTUAL;
    END

    IF (OLD.TETO IS DISTINCT FROM NEW.TETO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TETO:'||OLD.TETO||'->'||NEW.TETO;
    END

    IF (OLD.DATA_INICIAL IS DISTINCT FROM NEW.DATA_INICIAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.INICIAL:'|| FORMATDATE(OLD.DATA_INICIAL) ||'->'|| FORMATDATE(NEW.DATA_INICIAL);
    END

    IF (OLD.DATA_FINAL IS DISTINCT FROM NEW.DATA_FINAL) THEN
    BEGIN
        HISTORICO = HISTORICO||' DT.FINAL:'|| FORMATDATE(OLD.DATA_FINAL) ||'->'|| FORMATDATE(NEW.DATA_FINAL);
    END

    IF (OLD.VALOR_DEDUCAO IS DISTINCT FROM NEW.VALOR_DEDUCAO) THEN
    BEGIN
        HISTORICO = HISTORICO||' VL.DEDUCAO:'|| 'R$ '||FN_FORMAT_NUMBER2(OLD.VALOR_DEDUCAO, 2) ||'->'|| 'R$ '||FN_FORMAT_NUMBER2(NEW.VALOR_DEDUCAO, 2);
    END

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


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

SET TERM ; ^



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

COMMENT ON COLUMN TBFOLPAG_IRRF.VALOR_INICIAL IS 
'Margem inicial do valor para calculo do IRRF';

COMMENT ON COLUMN TBFOLPAG_IRRF.VALOR_FINAL IS 
'Margem final do valor para calculo do IRRF';

COMMENT ON COLUMN TBFOLPAG_IRRF.PERCENTUAL IS 
'Percentual de desconto';

COMMENT ON COLUMN TBFOLPAG_IRRF.DATA_INICIAL IS 
'Data inicial da validade do desconto';

COMMENT ON COLUMN TBFOLPAG_IRRF.DATA_FINAL IS 
'Data final da validade do desconto';

COMMENT ON COLUMN TBFOLPAG_IRRF.VALOR_DEDUCAO IS 
'Valor de desconto da deducao da tabela do IRRF';

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

UPDATE OR INSERT INTO TBFOLPAG_IRRF (ID, VALOR_INICIAL, VALOR_FINAL, PERCENTUAL, TETO, DATA_INICIAL, DATA_FINAL, VALOR_DEDUCAO)
    VALUES (1, 1903.99, 2826.65, 7.5, 0, '2000-01-01', '2999-12-31', 142.8)
    MATCHING (ID);

UPDATE OR INSERT INTO TBFOLPAG_IRRF (ID, VALOR_INICIAL, VALOR_FINAL, PERCENTUAL, TETO, DATA_INICIAL, DATA_FINAL, VALOR_DEDUCAO)
    VALUES (2, 2826.66, 3751.05, 15, 0, '2000-01-01', '2999-12-31', 354.8)
    MATCHING (ID);

UPDATE OR INSERT INTO TBFOLPAG_IRRF (ID, VALOR_INICIAL, VALOR_FINAL, PERCENTUAL, TETO, DATA_INICIAL, DATA_FINAL, VALOR_DEDUCAO)
    VALUES (3, 3751.06, 4664.68, 22.5, 0, '2000-01-01', '2999-12-31', 636.13)
    MATCHING (ID);

UPDATE OR INSERT INTO TBFOLPAG_IRRF (ID, VALOR_INICIAL, VALOR_FINAL, PERCENTUAL, TETO, DATA_INICIAL, DATA_FINAL, VALOR_DEDUCAO)
    VALUES (4, 4664.69, 999999.99, 27.5, 1, '2000-01-01', '2999-12-31', 869.36)
    MATCHING (ID);

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

ALTER TABLE TBCOLABORADOR_DEPENDENTE
    ADD DESCONTO_IRRF INTEGER DEFAULT 0;

COMMENT ON COLUMN TBCOLABORADOR_DEPENDENTE.DESCONTO_IRRF IS
'0 - Nao desconta do IRRF;
1 - Desconta do IRRF como dependente valido;';

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

UPDATE TBCOLABORADOR_DEPENDENTE
	SET DESCONTO_IRRF = 0
	WHERE DESCONTO_IRRF IS NULL;

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

SET TERM ^ ;

CREATE OR ALTER trigger ttbcolaborador_dependente_b01u for tbcolaborador_dependente
active before update position 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

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

    IF (OLD.DATA_NASCIMENTO <> NEW.DATA_NASCIMENTO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Dt.Nasc: '||OLD.DATA_NASCIMENTO||' -> '||NEW.DATA_NASCIMENTO;
    END
    
    IF (OLD.SEXO <> NEW.SEXO) THEN
    BEGIN
        HISTORICO = HISTORICO||' Sexo: '||OLD.SEXO||' -> '||NEW.SEXO;
    END

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

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

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

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'Depend. ID '||LPAD(OLD.CONTROLE,6,'0')||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCOLABORADOR', NEW.COLABORADOR_CODIGO, HISTORICO);
    END
END
^

SET TERM ; ^

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

INSERT INTO TBPARAMETRO (TABELA, CODIGO, DESCRICAO, VALOR, OBSERVACAO, GRUPO)
    VALUES ('SISTEMA', 'DESCONTO_DEPENDENTE_IRRF', 'VALOR DE DESCONTO POR DEPENDENTES NO IRRF', '189.59', 'VALOR SERA UTILIZADO PARA O DESCONTO DOS DEPENDENTES NO CALCULO DA BASE PARA O IRRF (IMPOSTO DE RENDE) NA FOLHA DE PAGAMENTO', 'WEB34060');
	
/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

ALTER TABLE TBFOLHAPAGAMENTO_VERBAS
    ADD VALOR_REFERENCIA INTEGER;

ALTER TABLE TBFOLHAPAGAMENTO_VERBAS
    ADD TIPO_REFERENCIA INTEGER;

COMMENT ON COLUMN TBFOLHAPAGAMENTO_VERBAS.VALOR_REFERENCIA IS
'Valor de Referencia para os descontos (Dias/Horas/Percentuais)';

COMMENT ON COLUMN TBFOLHAPAGAMENTO_VERBAS.TIPO_REFERENCIA IS
'1 - PERCENTUAL;
2 - DIAS/HORAS;';

