/*==============*/
/* Versão: 1.15 */
/*==============================================================*/
/* View: ORCTOS                                                 */
/*==============================================================*/
ALTER TABLE ORCTOS ADD COD_OBRA SMALLINT;

COMMIT WORK;

ALTER TABLE ORCTOS DROP CONSTRAINT FK_ORCTOS_PESSOAS_FJ;

COMMIT WORK;

alter table ORCTOS
   add constraint FK_ORCTOS_OBRAS_PESSOAS foreign key (COD_PESSOA,COD_OBRA)
      references OBRAS_PESSOAS(COD_PESSOA,COD_OBRA);

COMMIT WORK;

/*==============================================================*/
/* Table: COMODOS_DIVISOES                                      */
/*==============================================================*/
create table COMODOS_DIVISOES (
COD_COMODO           SMALLINT                       not null,
DESCRICAO            VARCHAR(35),
SEQ_EXIBICAO         SMALLINT,
constraint PK_COMODOS_DIVISOES primary key (COD_COMODO)
);

/*==============================================================*/
/* Table: ITENS_ORCTOS                                          */
/*==============================================================*/
alter table ITENS_ORCTOS add COD_COMODO  SMALLINT;

alter table ITENS_ORCTOS
   add constraint FK_ITENS_ORCTOS_COMODOS foreign key (COD_COMODO)
      references COMODOS_DIVISOES (COD_COMODO);

COMMIT WORK;

/*==============================================================*/
/* Table: SERVICOS_NF                                           */
/*==============================================================*/
ALTER TABLE SERVICOS_NF ADD DESC_RETENCAO CHAR(1);

COMMIT WORK;

UPDATE SERVICOS_NF SET PERC_RETENCAO = 0, VLR_RETENCAO = 0, DESC_RETENCAO = 'N'
WHERE PERC_RETENCAO IS NULL OR
      VLR_RETENCAO IS NULL OR
      PERC_RETENCAO = 0 OR
      VLR_RETENCAO = 0;

COMMIT WORK;

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

ALTER TRIGGER TG_UPDATE_NOTAFISCAL
ACTIVE 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 varBASE_ICMS3 DECIMAL(15,2);
    DECLARE VARIABLE varVLR_ICMS3 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_RETENCAO DECIMAL(15,2);
    DECLARE VARIABLE varDESC_RETENCAO 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.BASE_ICMS), SUM(S.VLR_ICMS), SUM(S.VLR_RETENCAO), MAX(DESC_RETENCAO)
        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, :varBASE_ICMS3, :varVLR_ICMS3, varVLR_RETENCAO, varDESC_RETENCAO;

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

        IF (varVLR_SERV IS NULL) THEN
            varVLR_SERV = 0;
        IF (varBASE_ICMS3 IS NULL) THEN
            varBASE_ICMS3 = 0;
        IF (varVLR_ICMS3 IS NULL) THEN
            varVLR_ICMS3 = 0;

        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 + :varBASE_ICMS3;
            NEW.VLR_ICMS     = :varVLR_ICMS + :varVLR_ICMS3;
            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_RETENCAO = 'S') THEN
               NEW.VLR_TOTAL = NEW.VLR_TOTAL - varVLR_RETENCAO;
        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;