CREATE OR REPLACE TRIGGER USR_ALTERACAO_MOV_ESTOQUE
AFTER UPDATE OR INSERT OR DELETE ON ITLANCTOEST FOR EACH ROW
DECLARE
   V_NOVOCODIGO    INTEGER;
   V_MAIORCOD      VARCHAR2(38);
   V_MAIORCOD2     VARCHAR2(38);
BEGIN
   IF INSERTING OR UPDATING THEN
      UPDATE LANCTOESTOQ
         SET USR_IDEXPORTA = 'S',
             USR_DTEXPORTA = NULL
       WHERE CDFILIAL = :NEW.CDFILIAL
         AND NRLANCESTQ = :NEW.NRLANCESTQ
         AND IDTPLANCTO IN ('1','3','4');
   END IF;
   IF DELETING THEN
      SELECT NVL(MAX(WG_IDMVEST),0)
        INTO V_MAIORCOD
        FROM USR_ZWG, PRODUTO P
       WHERE CDFILIAL = :OLD.CDFILIAL
         AND NRLANCESTQ = :OLD.NRLANCESTQ
         AND WG_CC = :OLD.CDFILIMOVI
         AND :OLD.CDPRODUTO = P.CDPRODUTO
         AND WG_COD = SUBSTR(P.CDARVPROD,1,15)
         AND WG_DOC = SUBSTR(:OLD.NRLANCESTQ,2,9)
         AND WG_TPEDI = '1';

      SELECT NVL(MAX(WG_IDMVEST),0)
        INTO V_MAIORCOD2
        FROM USR_ZWG, PRODUTO P
       WHERE CDFILIAL = :OLD.CDFILIAL
         AND NRLANCESTQ = :OLD.NRLANCESTQ
         AND WG_CC = :OLD.CDFILIMOVI
         AND :OLD.CDPRODUTO = P.CDPRODUTO
         AND WG_COD = SUBSTR(P.CDARVPROD,1,15)
         AND WG_DOC = SUBSTR(:OLD.NRLANCESTQ,2,9)
         AND WG_TPEDI = '2';

      IF V_MAIORCOD > V_MAIORCOD2 THEN
         FOR C_USR_ZWG IN (
            SELECT CDFILIAL, NRLANCESTQ, WG_EMPRESA, WG_FILIAL, WG_TM, WG_COD, WG_LOCAL, WG_QUANT,
                   WG_EMISSAO, WG_CC, WG_CUSTO, WG_DOC, WG_USUARIO, WG_CONTA, WG_ITEMCTA, WG_CLVL,
                   WG_LOTECTL, WG_NUMLOTE, WG_DTVALID
              FROM USR_ZWG
             WHERE CDFILIAL = :OLD.CDFILIAL
               AND NRLANCESTQ = :OLD.NRLANCESTQ
               AND WG_IDMVEST = V_MAIORCOD) LOOP
            /* Retorna o sequencial para a tabela */
            BEGIN
               SELECT WG_IDMVEST.NEXTVAL
                 INTO V_NOVOCODIGO
                 FROM DUAL;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NOVOCODIGO := 1;
            END;
            INSERT INTO USR_ZWG
               (WG_IDMVEST, CDFILIAL, NRLANCESTQ, WG_EMPRESA, WG_FILIAL, WG_TM, WG_COD, WG_LOCAL, WG_QUANT,
                WG_EMISSAO, WG_CC, WG_CUSTO, WG_DOC, WG_USUARIO, WG_CONTA, WG_ITEMCTA, WG_CLVL,
                WG_LOTECTL, WG_NUMLOTE, WG_DTVALID, WG_TPEDI, WG_DTGRV, WG_HRGRV)
            VALUES
               (V_NOVOCODIGO, C_USR_ZWG.CDFILIAL, C_USR_ZWG.NRLANCESTQ, C_USR_ZWG.WG_EMPRESA, C_USR_ZWG.WG_FILIAL, C_USR_ZWG.WG_TM, C_USR_ZWG.WG_COD, C_USR_ZWG.WG_LOCAL, C_USR_ZWG.WG_QUANT,
                C_USR_ZWG.WG_EMISSAO, C_USR_ZWG.WG_CC, C_USR_ZWG.WG_CUSTO, C_USR_ZWG.WG_DOC, C_USR_ZWG.WG_USUARIO, C_USR_ZWG.WG_CONTA, C_USR_ZWG.WG_ITEMCTA, C_USR_ZWG.WG_CLVL,
                C_USR_ZWG.WG_LOTECTL, C_USR_ZWG.WG_NUMLOTE, C_USR_ZWG.WG_DTVALID, '2', TO_DATE(SYSDATE), TO_CHAR(SYSDATE,'HH24MI'));
         END LOOP;
      END IF;
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_ALTERACAO_MOV_ESTOQUE','5.09.000'); END;
/


