DECLARE V_SEQ NUMBER;
BEGIN
   FOR C_DADO IN (SELECT CDEMPRESA, CDINSCMUNI, IDDECLARACAO, CDPRODSERV, DSPRODSERV, IDEXIGISSQN, IDTPDEDUCAO, 
                         DSDEDUCAO, IDENTRSAIDOP, NRORG, IDSERVOBRCCIVIL, CDCNO, CDCLASERVMOBEMP
                    FROM PARAMMUNISERV
                   WHERE IDDECLARACAO = 'RE'
                     AND IDENTRSAIDOP = 'T') LOOP
      
      V_SEQ := 0;
      BEGIN
         SELECT NRSEQPARAMUNISERV
           INTO V_SEQ
           FROM PARAMMUNISERV
          WHERE CDEMPRESA    = C_DADO.CDEMPRESA
            AND CDINSCMUNI   = C_DADO.CDINSCMUNI
            AND IDDECLARACAO = 'RE'
            AND CDPRODSERV   = C_DADO.CDPRODSERV
            AND IDENTRSAIDOP = 'E';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SEQ := 0;
      END;
      
      IF V_SEQ = 0 THEN 
         NOVO_CODIGO_NT('NRSEQPARAMUNISERV',V_SEQ);
         INSERT INTO PARAMMUNISERV
           (NRSEQPARAMUNISERV, CDEMPRESA, CDINSCMUNI, IDDECLARACAO, CDPRODSERV, DSPRODSERV, IDEXIGISSQN, 
            IDTPDEDUCAO, DSDEDUCAO, IDENTRSAIDOP, NRORG, IDSERVOBRCCIVIL, CDCNO, CDCLASERVMOBEMP)
         VALUES
           (V_SEQ, C_DADO.CDEMPRESA, C_DADO.CDINSCMUNI, C_DADO.IDDECLARACAO, C_DADO.CDPRODSERV, C_DADO.DSPRODSERV, C_DADO.IDEXIGISSQN, 
            C_DADO.IDTPDEDUCAO, C_DADO.DSDEDUCAO, 'E', C_DADO.NRORG, C_DADO.IDSERVOBRCCIVIL, C_DADO.CDCNO, C_DADO.CDCLASERVMOBEMP);
      END IF;
      
      V_SEQ := 0;
      BEGIN
         SELECT NRSEQPARAMUNISERV
           INTO V_SEQ
           FROM PARAMMUNISERV
          WHERE CDEMPRESA    = C_DADO.CDEMPRESA
            AND CDINSCMUNI   = C_DADO.CDINSCMUNI
            AND IDDECLARACAO = 'RE'
            AND CDPRODSERV   = C_DADO.CDPRODSERV
            AND IDENTRSAIDOP = 'S';
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_SEQ := 0;
      END;
      
      IF V_SEQ = 0 THEN 
         NOVO_CODIGO_NT('NRSEQPARAMUNISERV',V_SEQ);
         INSERT INTO PARAMMUNISERV
           (NRSEQPARAMUNISERV, CDEMPRESA, CDINSCMUNI, IDDECLARACAO, CDPRODSERV, DSPRODSERV, IDEXIGISSQN, 
            IDTPDEDUCAO, DSDEDUCAO, IDENTRSAIDOP, NRORG, IDSERVOBRCCIVIL, CDCNO, CDCLASERVMOBEMP)
         VALUES
           (V_SEQ, C_DADO.CDEMPRESA, C_DADO.CDINSCMUNI, C_DADO.IDDECLARACAO, C_DADO.CDPRODSERV, C_DADO.DSPRODSERV, C_DADO.IDEXIGISSQN, 
            C_DADO.IDTPDEDUCAO, C_DADO.DSDEDUCAO, 'S', C_DADO.NRORG, C_DADO.IDSERVOBRCCIVIL, C_DADO.CDCNO, C_DADO.CDCLASERVMOBEMP);
      END IF;      
   END LOOP;
   
   DELETE FROM PARAMMUNISERV
    WHERE IDDECLARACAO = 'RE'
      AND IDENTRSAIDOP = 'T';
  
   COMMIT;  
END;
/

ALTER TABLE PARAMMUNISERV DROP CONSTRAINT CKC_IDENTRSAIDOP_PARAMMUNISERV
/

ALTER TABLE PARAMMUNISERV ADD CONSTRAINT CKC_IDENTRSAIDOP_PARAMMUNISERV CHECK (IDENTRSAIDOP IN ('E','S'))
/