create or replace
PROCEDURE IMPORTA_INSCRMUNI
IS
   V_CDEMPRESA    INTEGER;
   V_CDPAIS       INTEGER;
   V_SGESTADO     INTEGER;
   V_CDMUNICIPIO  VARCHAR2(4);
   V_INSCRMUNICIP INTEGER;
   V_MUNICIPIO    INTEGER;

BEGIN
   FOR C_INSCMUNI IN
      (SELECT U.CDEMPRESA, U.CDINSCMUNI, U.DSENDEIEST, U.CDPAIS, U.SGESTADO,
              U.CDMUNICIPIO, U.NRCEPIMUN, U.IDINTERFACE, U.TPINTERFACE
         FROM USR_SAP_INSCMUNI U
        WHERE U.DTINTERFACEDEST IS NULL)
   LOOP
      SELECT COUNT(*)
        INTO V_CDEMPRESA
        FROM EMPRESA
       WHERE CDEMPRESA = C_INSCMUNI.CDEMPRESA;
      IF V_CDEMPRESA > 0 THEN
         SELECT COUNT(*)
           INTO V_INSCRMUNICIP
           FROM INSCRMUNICIP
          WHERE CDEMPRESA  = C_INSCMUNI.CDEMPRESA
            AND CDINSCMUNI = C_INSCMUNI.CDINSCMUNI;
         IF V_INSCRMUNICIP = 0 THEN
            SELECT COUNT(*)
              INTO V_CDPAIS
              FROM PAIS
             WHERE CDPAIS = C_INSCMUNI.CDPAIS;
            IF V_CDPAIS > 0 THEN
               SELECT COUNT(*)
                 INTO V_SGESTADO
                 FROM ESTADO
                WHERE CDPAIS = C_INSCMUNI.CDPAIS
                  AND SGESTADO = C_INSCMUNI.SGESTADO;
               IF V_SGESTADO > 0 THEN
                  BEGIN
                     SELECT COUNT(*) AS CONTADOR, CDMUNICIPIO
                       INTO V_MUNICIPIO, V_CDMUNICIPIO
                       FROM MUNICIPIO
                      WHERE CDPAIS = C_INSCMUNI.CDPAIS
                        AND SGESTADO = C_INSCMUNI.SGESTADO
                        AND CDMUNICIBGE = C_INSCMUNI.CDMUNICIPIO
                      GROUP BY CDMUNICIPIO;
                     IF V_MUNICIPIO > 0 THEN
                        INSERT INTO INSCRMUNICIP(CDEMPRESA, CDINSCMUNI, DSENDEIMUN, CDPAIS, SGESTADO, CDMUNICIPIO, NRCEPIMUN)
                        VALUES (C_INSCMUNI.CDEMPRESA, C_INSCMUNI.CDINSCMUNI, C_INSCMUNI.DSENDEIEST, C_INSCMUNI.CDPAIS,
                                C_INSCMUNI.SGESTADO, V_CDMUNICIPIO, C_INSCMUNI.NRCEPIMUN);
                        INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
                        VALUES(C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'Inscri'||CHR(231)||''||CHR(227)||'o Municipal '|| C_INSCMUNI.CDINSCMUNI ||' cadastrada com sucesso.');
                        
                        UPDATE USR_SAP_INSCMUNI SET DTINTERFACEDEST = SYSDATE, STATUS = 0
                         WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
                     END IF;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_CDMUNICIPIO := '';
                        INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
                        VALUES (C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'C'||CHR(243)||'digo do munic'||CHR(237)||'pio IBGE '|| C_INSCMUNI.CDMUNICIPIO||' n'||CHR(227)||'o cadastrado no sistema Teknisa.');

                        UPDATE USR_SAP_INSCMUNI SET STATUS = 2
                         WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
                  END;               
               ELSE
                  INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
                  VALUES (C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'Estado '|| C_INSCMUNI.SGESTADO ||' n'||CHR(227)||'o cadastrado no sistema Teknisa.');
                  
                  UPDATE USR_SAP_INSCMUNI SET STATUS = 2
                   WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
               END IF;
            ELSE
               INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
               VALUES (C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'Pa'||CHR(237)||'s '|| C_INSCMUNI.CDPAIS ||' n'||CHR(227)||'o cadastrado no sistema Teknisa.');

               UPDATE USR_SAP_INSCMUNI SET STATUS = 2
                WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
            END IF;
         ELSE
            INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
            VALUES (C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'Inscri'||CHR(231)||''||CHR(227)||'o Estadual '|| C_INSCMUNI.CDINSCMUNI ||' j'||CHR(225)||' est'||CHR(225)||' cadastrada no sistema Teknisa.');

            UPDATE USR_SAP_INSCMUNI SET STATUS = 2
             WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
         END IF;
	  ELSE
         INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
         VALUES (C_INSCMUNI.IDINTERFACE, 'I', 14, SYSDATE, 1, 'Empresa '|| C_INSCMUNI.CDEMPRESA ||' n'||CHR(227)||'o cadastrada no sistema Teknisa.');

         UPDATE USR_SAP_INSCMUNI SET STATUS = 2
          WHERE IDINTERFACE = C_INSCMUNI.IDINTERFACE;
      END IF;
   END LOOP;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('IMPORTA_INSCRMUNI','5.42.XXX'); END;
/