/*==============*/
/* Versão: 1.16 */
/*==============================================================*/
/* Trigger: TG_UPDATE_NOTAFISCAL                                */
/*==============================================================*/
DROP TRIGGER TG_UPDATE_NOTAFISCAL;

COMMIT WORK;

/*==============================================================*/
/* Table: SERVICOS_NF                                           */
/*==============================================================*/
ALTER TABLE SERVICOS_NF ALTER COLUMN COD_ICMS       TO COD_ISS;
ALTER TABLE SERVICOS_NF ALTER COLUMN BASE_ICMS      TO BASE_ISS;
ALTER TABLE SERVICOS_NF ALTER COLUMN PERC_ICMS      TO PERC_ISS;
ALTER TABLE SERVICOS_NF ALTER COLUMN PERC_RETENCAO  TO PERC_INSS;
ALTER TABLE SERVICOS_NF ALTER COLUMN VLR_RETENCAO   TO VLR_INSS;
ALTER TABLE SERVICOS_NF ALTER COLUMN DESC_RETENCAO  TO DESC_INSS

COMMIT WORK;

alter table "SERVICOS_NF" drop "VLR_ICMS";
alter table "SERVICOS_NF" drop "ALIQ_ISS";

COMMIT WORK;

ALTER TABLE SERVICOS_NF ADD DESC_ISS CHAR(1);

COMMIT WORK;

/*==============================================================*/
/* Trigger: TG_UPDATE_NOTAFISCAL                                */
/*==============================================================*/
SET TERM ^;

CREATE TRIGGER TG_UPDATE_NOTAFISCAL FOR NOTA_FISCAL
BEFORE UPDATE POSITION 0
AS
    DECLARE VARIABLE varCOD_EMPRESA INTEGER;
    DECLARE VARIABLE varTIPO_CONTROL CHAR(1);
    DECLARE VARIABLE varNRO_NF INTEGER;
    DECLARE VARIABLE varVLR_TOTAL DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS DECIMAL(15,2);
    DECLARE VARIABLE varVLR_IPI DECIMAL(15,2);
    DECLARE VARIABLE varBASE_ICMS2 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS2 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_SERV DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ISS DECIMAL(15,2);
    DECLARE VARIABLE varDESC_ISS CHAR(1);
    DECLARE VARIABLE varVLR_INSS DECIMAL(15,2);
    DECLARE VARIABLE varDESC_INSS CHAR(1);
BEGIN
        /* SELEÇÃO DOS VALORES DOS ITENS DE PRODUTOS */
        SELECT I.NRO_NF, SUM(I.VLR_TOTAL), SUM(I.BASE_ICMS), SUM(I.VLR_ICMS), SUM(I.VLR_IPI)
        FROM ITENS_NF I
        WHERE I.COD_EMPRESA = NEW.COD_EMPRESA AND
              I.NRO_NF = NEW.NRO_NF AND
              I.TIPO_CONTROL = NEW.TIPO_CONTROL
        GROUP BY I.NRO_NF
        INTO :varNRO_NF, :varVLR_TOTAL, :varBASE_ICMS, :varVLR_ICMS, :varVLR_IPI;

        /* SELEÇÃO DOS VALORES DOS ITENS DE SERVICO */
        SELECT S.NRO_NF, SUM(S.VLR_TOTAL), SUM(S.VLR_ISS), SUM(S.VLR_INSS), MAX(S.DESC_ISS), MAX(DESC_INSS)
        FROM SERVICOS_NF S
        WHERE S.COD_EMPRESA = NEW.COD_EMPRESA AND
              S.NRO_NF = NEW.NRO_NF AND
              S.TIPO_CONTROL = NEW.TIPO_CONTROL
        GROUP BY S.NRO_NF
        INTO :varNRO_NF, :varVLR_SERV, :varVLR_ISS, :varVLR_INSS, varDESC_ISS, varDESC_INSS;

        NEW.VLR_TOTAL = NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC;

        IF (varVLR_TOTAL IS NULL) THEN
            varVLR_TOTAL = 0;
        IF (varBASE_ICMS IS NULL) THEN
            varBASE_ICMS = 0;
        IF (varVLR_ICMS IS NULL) THEN
            varVLR_ICMS = 0;
        IF (varVLR_IPI IS NULL) THEN
            varVLR_IPI = 0;

        IF (varVLR_TOTAL IS NOT NULL) THEN
        BEGIN
            UPDATE ITENS_NF SET
               ITENS_NF.VLR_RATEIO = (ITENS_NF.VLR_TOTAL / :varVLR_TOTAL) *
               (NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC)
            WHERE ITENS_NF.COD_EMPRESA = NEW.COD_EMPRESA AND
                  ITENS_NF.NRO_NF = NEW.NRO_NF AND
                  ITENS_NF.TIPO_CONTROL = NEW.TIPO_CONTROL;

            NEW.VLR_PROD  = :varVLR_TOTAL;
            NEW.VLR_SERV  = :varVLR_SERV;
            NEW.BASE_ICMS    = :varBASE_ICMS;
            NEW.VLR_ICMS     = :varVLR_ICMS;
            NEW.VLR_IPI      = :varVLR_IPI;
            NEW.VLR_TOTAL    = :varVLR_TOTAL + :varVLR_SERV + NEW.VLR_FRETE + NEW.VLR_SEGURO + NEW.VLR_OUTDESP + NEW.VLR_IPI + NEW.VLR_ACRES - NEW.VLR_DESC;

            IF (varDESC_ISS = 'S') THEN
               NEW.VLR_TOTAL = NEW.VLR_TOTAL - varVLR_ISS;
            IF (varDESC_INSS = 'S') THEN
               NEW.VLR_TOTAL = NEW.VLR_TOTAL - varVLR_INSS;
        END

        SELECT A.NRO_NF, SUM(A.BASE_ICMS), SUM(A.VLR_ICMS)
        FROM ALIQ_ICMSNF A
        WHERE A.COD_EMPRESA = NEW.COD_EMPRESA AND
              A.NRO_NF = NEW.NRO_NF AND
              A.TIPO_CONTROL = NEW.TIPO_CONTROL
        GROUP BY A.NRO_NF
        INTO :varNRO_NF, :varBASE_ICMS2, :varVLR_ICMS2;

        IF (:varBASE_ICMS2 IS NOT NULL) THEN
        BEGIN
              NEW.BASE_ICMS = NEW.BASE_ICMS + :varBASE_ICMS2;
              NEW.VLR_ICMS  = NEW.VLR_ICMS  + :varVLR_ICMS2;
        END
END^

SET TERM ;^

COMMIT WORK;