ALTER TABLE ENVIOEVENTOREINF ADD DTATUCDESTAB DATE NULL
/

UPDATE ENVIOEVENTOREINF
   SET DSOBSERVACAO = CDESTABREINF  ||':'||  DSOBSERVACAO
WHERE CDESTABREINF IS NOT NULL AND DTATUCDESTAB IS NULL
/

DECLARE
   V_ESTAB VARCHAR2(50);
BEGIN
   FOR C_LOOP IN (SELECT E.NRENVIO, CDESTABREINF, 
                   NVL((SELECT NMEVENTO FROM ENVIOEVENTOREINF EX, EVENTOREINF DEL WHERE EV.NMEVENTO = 'R-9000' AND E.NRENVIOEXCLUSAO = EX.NRENVIO AND EX.NREVENTO = DEL.NREVENTO),EV.NMEVENTO) AS NMEVENTO
                  FROM ENVIOEVENTOREINF E, EVENTOREINF EV
                  WHERE CDESTABREINF IS NOT NULL
                  AND E.NREVENTO = EV.NREVENTO
                  AND SUBSTR(CDESTABREINF,1,1) IN ('J','F','I','O')
                  AND E.DTATUCDESTAB IS NULL) LOOP
              IF C_LOOP.NMEVENTO IN ('R-2055','R-2010','R-4010','R-4020') THEN
                 SELECT NRINSJURFORN 
                 INTO V_ESTAB
                 FROM FORNECEDOR
                 WHERE CDFORNECED = C_LOOP.CDESTABREINF;
              ELSE
                 SELECT NRINSJURCLIE 
                 INTO V_ESTAB
                 FROM CLIENTE
                 WHERE CDCLIENTE = C_LOOP.CDESTABREINF;
              END IF;

          UPDATE ENVIOEVENTOREINF
             SET CDESTABREINF = V_ESTAB,
                 DTATUCDESTAB = SYSDATE
            WHERE NRENVIO = C_LOOP.NRENVIO
             AND V_ESTAB IS NOT NULL;
     END LOOP;
END;
/

COMMIT
/
