SET TERM ^ ;

CREATE OR ALTER trigger ttboc_item_b0iu_01 for tboc_item
active before insert or update position 0
AS
DECLARE VARIABLE UM VARCHAR(3);
DECLARE VARIABLE DESCR VARCHAR(100);
DECLARE VARIABLE FRAC CHAR(1);
DECLARE VARIABLE LOC1 INTEGER;
DECLARE VARIABLE LOC2 INTEGER;


BEGIN


For
    Select First 1
        coalesce(U.SIGLA,'') as SIGLA,
        P.DESCRICAO,
        U.PODE_FRACIONAR,
        P.LOCALIZACAO_CODIGO,
        (SELECT FIRST 1 F.LOCALIZACAO_CODIGO FROM TBFAMILIA F WHERE F.CODIGO = P.FAMILIA_CODIGO)
    From TbProduto P
    left join TbUnidadeMedida U on P.Unidademedida_Sigla = U.Sigla
    Where P.Codigo = New.Produto_Codigo
    Into
        :UM,
        :DESCR,
        :FRAC,
        :LOC1,
        :LOC2
Do
Begin

  if(UM = '')then
  begin
     exception "..." 'Unidades de medida do produto nao foi encontrada. Produto:' || New.Produto_Codigo;
  end

  if (New.Controle > 0) then
      Begin
      /* Trigger text */

      if ((NEW.VALOR * NEW.QUANTIDADE) + NEW.ACRESCIMO - NEW.DESCONTO < 0) THEN
          Exception "..." 'VALOR TOTAL DO ITEM INVALIDO, NAO PODE SER NEGATIVO! '||
          NEW.PRODUTO_CODIGO||'-'||:DESCR||
          ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';


      if (:FRAC = 'N') then
          BEGIN
          if (NEW.QUANTIDADE > TRUNC(NEW.QUANTIDADE)) THEN
              Exception "..." 'PRODUTO NAO PERMITE QUANTIDADE FRACIONADA: '||
              NEW.PRODUTO_CODIGO||'-'||:DESCR||' QUANT.'||NEW.QUANTIDADE||' '||:UM||
              ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
          END
    
       if (Coalesce(New.Localizacao_Codigo,0) = 0) then
           Begin
           if (:Loc1 > 0) then
               Begin
               New.Localizacao_Codigo = :Loc1;
               End else
           if (:Loc2 > 0) then
               Begin
               New.Localizacao_Codigo = :Loc2;
               End else
               Begin
               Exception "..." 'LOCALIZACAO PADRAO DE ESTOQUE DO PRODUTO NAO LOCALIZADA '||
               NEW.PRODUTO_CODIGO||'-'||:DESCR||
               ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
               End
           End


    if (Inserting) then
        Begin
            UPDATE OR INSERT INTO TbOc_Item_Saldo (
            Oc,Oc_Item_Controle,Quantidade,        
            T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,
            T11,T12,T13,T14,T15,T16,T17,T18,T19,T20)
            VALUES (NEW.OC,NEW.CONTROLE,NEW.QUANTIDADE,
            NEW.T01,NEW.T02,NEW.T03,NEW.T04,NEW.T05,NEW.T06,NEW.T07,NEW.T08,NEW.T09,NEW.T10,
            NEW.T11,NEW.T12,NEW.T13,NEW.T14,NEW.T15,NEW.T16,NEW.T17,NEW.T18,NEW.T19,NEW.T20)
            MATCHING (Oc,Oc_Item_Controle);
        End
    
    if (Updating) then
        Begin
        --EXCEPTION "..."NEW.QUANTIDADE||' - '||OLD.QUANTIDADE;   6 -> 3

        if (NEW.QUANTIDADE <> OLD.QUANTIDADE or
            NEW.T01 <> OLD.T01 or NEW.T02 <> OLD.T02 or
            NEW.T03 <> OLD.T03 or NEW.T04 <> OLD.T04 or
            NEW.T05 <> OLD.T05 or NEW.T06 <> OLD.T06 or
            NEW.T07 <> OLD.T07 or NEW.T08 <> OLD.T08 or
            NEW.T09 <> OLD.T09 or NEW.T10 <> OLD.T10 or
            NEW.T11 <> OLD.T11 or NEW.T12 <> OLD.T12 or
            NEW.T13 <> OLD.T13 or NEW.T14 <> OLD.T14 or
            NEW.T15 <> OLD.T15 or NEW.T16 <> OLD.T16 or
            NEW.T17 <> OLD.T17 or NEW.T18 <> OLD.T18 or
            NEW.T19 <> OLD.T19 or NEW.T20 <> OLD.T20) then
            Begin
                --UPDATE OR INSERT INTO TbOc_Item_Saldo T (
                --Oc,Oc_Item_Controle,Quantidade,
                --T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,
                --T11,T12,T13,T14,T15,T16,T17,T18,T19,T20)
                --VALUES (NEW.OC,NEW.CONTROLE, NEW.QUANTIDADE ,
                --NEW.T01,NEW.T02,NEW.T03,NEW.T04,NEW.T05,NEW.T06,NEW.T07,NEW.T08,NEW.T09,NEW.T10,
                --NEW.T11,NEW.T12,NEW.T13,NEW.T14,NEW.T15,NEW.T16,NEW.T17,NEW.T18,NEW.T19,NEW.T20)
                --MATCHING (Oc,Oc_Item_Controle);
                UPDATE TBOC_ITEM_SALDO T
                SET OC = NEW.OC,
                    QUANTIDADE  =  T.QUANTIDADE - (OLD.QUANTIDADE - NEW.QUANTIDADE),
                    T01         =  T.T01 - (OLD.T01 - NEW.T01),
                    T02         =  T.T02 - (OLD.T02 - NEW.T02),
                    T03         =  T.T03 - (OLD.T03 - NEW.T03),
                    T04         =  T.T04 - (OLD.T04 - NEW.T04),
                    T05         =  T.T05 - (OLD.T05 - NEW.T05),
                    T06         =  T.T06 - (OLD.T06 - NEW.T06),
                    T07         =  T.T07 - (OLD.T07 - NEW.T07),
                    T08         =  T.T08 - (OLD.T08 - NEW.T08),
                    T09         =  T.T09 - (OLD.T09 - NEW.T09),
                    T10         =  T.T10 - (OLD.T10 - NEW.T10),
                    T11         =  T.T11 - (OLD.T11 - NEW.T11),
                    T12         =  T.T12 - (OLD.T12 - NEW.T12),
                    T13         =  T.T13 - (OLD.T13 - NEW.T13),
                    T14         =  T.T14 - (OLD.T14 - NEW.T14),
                    T15         =  T.T15 - (OLD.T15 - NEW.T15),
                    T16         =  T.T16 - (OLD.T16 - NEW.T16),
                    T17         =  T.T17 - (OLD.T17 - NEW.T17),
                    T18         =  T.T18 - (OLD.T18 - NEW.T18),
                    T19         =  T.T19 - (OLD.T19 - NEW.T19),
                    T20         =  T.T20 - (OLD.T20 - NEW.T20)
                WHERE (OC_ITEM_CONTROLE = NEW.CONTROLE);

            End
        End

    End

End

END
^

SET TERM ; ^

