CREATE OR REPLACE PROCEDURE CONVERTE_FOTOPRATO_BLOB
AS
BEGIN
   FOR CUR_FOTOS IN
   (
      SELECT NRFOTOPRATOBLOB, CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, NRFOTO
        FROM FOTOPRATOBLOB
       WHERE FGFOTOPRATO IS NULL
   ) LOOP
      IF CUR_FOTOS.IDRECEITA = 'P' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT FGFOTOPRAPAD FROM FOTOPRAPAD
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND NRFOTOPRAPAD = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
      IF CUR_FOTOS.IDRECEITA = 'F' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT FGFOTOPRAFIL FROM FOTOPRAFIL
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND CDFILIAL = CUR_FOTOS.CDFILIAL
                                  AND NRFOTOPRAFIL = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
      IF CUR_FOTOS.IDRECEITA = 'S' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT FGFOTOPRASER FROM FOTOPRASER
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND CDFILIAL = CUR_FOTOS.CDFILIAL
                                  AND CDSERVFILI = CUR_FOTOS.CDSERVFILI
                                  AND NRFOTOPRASER = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
   END LOOP;
END;
/

DECLARE
  V_JOB NUMBER;
BEGIN
  FOR C_JOBS IN (SELECT JOB
                   FROM ALL_JOBS
                  WHERE WHAT LIKE '%CONVERTE_FOTOPRATO_BLOB%') LOOP
    DBMS_JOB.REMOVE(C_JOBS.JOB);
  END LOOP;

  DBMS_JOB.SUBMIT(V_JOB, WHAT => 'BEGIN CONVERTE_FOTOPRATO_BLOB; END;', 
  NEXT_DATE => SYSDATE + 30/86400, 
  INTERVAL => 'SYSDATE + 30/86400');
  COMMIT;
END; 
/


-- VERSAO --
BEGIN ATUVEROBJ('CONVERTE_FOTOPRATO_BLOB','6.39.007'); END;
/
