DECLARE
V_SEQ VARCHAR2(20);
V_COUNT NUMBER;
V_NUM_SEQ NUMBER;
V_NRORG NUMBER;
BEGIN
   SELECT COUNT(1)
     INTO V_NUM_SEQ
     FROM
   (
   SELECT 1
     FROM FOTOPRASER
    WHERE IDFOTOPRASER = '1'
   UNION ALL
   SELECT 1
     FROM FOTOPRAFIL
    WHERE IDFOTOPRAFIL = '1'
   UNION ALL
   SELECT 1
     FROM FOTOPRAPAD
    WHERE IDFOTOPRAPAD = '1'
   );

   SELECT COUNT(1)
     INTO V_COUNT
     FROM FOTOPRATOBLOB;

   IF(V_COUNT = 0 AND V_NUM_SEQ > 0) THEN
      NOVO_CODIGO_NT('FOTOPRATOBLOB', V_SEQ, V_NUM_SEQ);

      INSERT INTO FOTOPRATOBLOB
      (NRFOTOPRATOBLOB, CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, FGFOTOPRATO, DTINCLUSAO, NRFOTO)
      SELECT TO_NUMBER(V_SEQ) + (ROWNUM -1), CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, TO_LOB(FGFOTOPRATO), SYSDATE, NRFOTO
        FROM
      (
      SELECT CDFILAUXPRAT, CDPRATO, 'P' AS IDRECEITA, NULL AS CDFILIAL, NULL AS CDSERVFILI, FGFOTOPRAPAD AS FGFOTOPRATO, NRFOTOPRAPAD AS NRFOTO
        FROM FOTOPRAPAD
       WHERE IDFOTOPRAPAD = '1' AND FGFOTOPRAPAD IS NOT NULL
       UNION ALL
      SELECT CDFILAUXPRAT, CDPRATO, 'F' AS IDRECEITA, CDFILIAL, NULL AS CDSERVFILI, FGFOTOPRAFIL AS FGFOTOPRATO, NRFOTOPRAFIL AS NRFOTO
        FROM FOTOPRAFIL
       WHERE IDFOTOPRAFIL = '1' AND FGFOTOPRAFIL IS NOT NULL
       UNION ALL
      SELECT CDFILAUXPRAT, CDPRATO, 'S' AS IDRECEITA, CDFILIAL, CDSERVFILI, FGFOTOPRASER AS FGFOTOPRATO, NRFOTOPRASER AS NRFOTO
        FROM FOTOPRASER
       WHERE IDFOTOPRASER = '1' AND FGFOTOPRASER IS NOT NULL
      );

   END IF;

   COMMIT;
END;