create or replace PROCEDURE FCT_CANCELAR_APURA_MEDICAO (
    P_NRMEDICAOFATURA  IN VARCHAR2,
    P_NRSEQCONTRATO    IN VARCHAR2,
    P_NRORG            IN NUMBER,
    P_CDOPERADOR       IN VARCHAR2
)
AS
   V_NRFATURACONT FCT_FATURACONT.NRFATURACONT%TYPE;
   V_IDPARCAGRUP  FCT_RELACAGRUPCONTFAT.IDPARCAGRUP%TYPE;
   V_QTAGRUP NUMBER;
BEGIN
     FOR C_MEDICAO IN (SELECT NRMEDICAOFATURA, NRSEQCONTRATO, NRGRUPOFATURA, CDFILIALFATURA, NRPARCFATURA,
                             IDSITUACAO, DTINICOMPET, DTFINCOMPET, DTLIMITEAPU, DTVENCIMENTO,
                             NRDIASVENCIMENTO, QTDIASFATUVENC, VRMEDICAO, IDATIVO, NRORG
                        FROM FCT_MEDICAOFATURA
                       WHERE ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(FCT_MEDICAOFATURA.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                         AND ((INSTR(P_NRMEDICAOFATURA, '_' || TO_CHAR(FCT_MEDICAOFATURA.NRMEDICAOFATURA) || '_') > 0) OR (P_NRMEDICAOFATURA IS NULL))
                         AND NRORG = P_NRORG
                         AND IDSITUACAO = '4'
                         ORDER BY NRMEDICAOFATURA) LOOP
        
        BEGIN 
          SELECT NRFATURACONT, IDPARCAGRUP
          INTO V_NRFATURACONT, V_IDPARCAGRUP
          FROM FCT_RELACAGRUPCONTFAT 
          WHERE NRSEQCONTRATO   = C_MEDICAO.NRSEQCONTRATO
            AND NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
            AND NRORG           = C_MEDICAO.NRORG;
        EXCEPTION WHEN NO_DATA_FOUND THEN
          V_NRFATURACONT := NULL;
        END;

        FOR C_FATURA IN (SELECT NRFATURACONT, NRSEQCONTRATO, IDSITUACAO, VRVENCIMENTO
                         FROM FCT_FATURACONT
                         WHERE NRFATURACONT = V_NRFATURACONT 
                              OR (V_NRFATURACONT IS NULL 
                            AND NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
                            AND NRSEQCONTRATO   = C_MEDICAO.NRSEQCONTRATO
                            AND NRORG           = P_NRORG)) LOOP

           IF (C_FATURA.IDSITUACAO = 1) THEN
              DELETE FROM FCT_ITFATURACONT
                 WHERE NRFATURACONT    = C_FATURA.NRFATURACONT
                   AND NRSEQCONTRATO   = C_MEDICAO.NRSEQCONTRATO
                   AND NRORG           = P_NRORG
                   AND NRITEMOCONTRATO IN (SELECT NRITEMOCONTRATO
                                            FROM FCT_ITMEDICAOFATURA
                                            WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA);

              DELETE FROM FCT_RELACAGRUPCONTFAT
                 WHERE NRFATURACONT    = C_FATURA.NRFATURACONT
                   AND NRSEQCONTRATO   = C_MEDICAO.NRSEQCONTRATO
                   AND NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
                   AND NRORG           = P_NRORG;
                   
              SELECT COUNT(*)
                INTO V_QTAGRUP
                FROM FCT_RELACAGRUPCONTFAT
               WHERE NRFATURACONT    = C_FATURA.NRFATURACONT
                 AND NRORG           = P_NRORG;

              IF(V_IDPARCAGRUP = 'N' OR V_QTAGRUP = 0) THEN
                
                DELETE FROM FCT_FATURACONT
                 WHERE NRFATURACONT = C_FATURA.NRFATURACONT
                   AND NRORG        = P_NRORG;
                
              ELSE 
                
                UPDATE FCT_FATURACONT 
                SET VRVENCIMENTO = VRVENCIMENTO - C_MEDICAO.VRMEDICAO
                WHERE NRFATURACONT  = C_FATURA.NRFATURACONT
                  AND NRORG         = P_NRORG; 

              END IF;

              UPDATE FCT_MEDICAOFATURA
                 SET IDSITUACAO = '3',
                     NRORGULTATU = P_NRORG,
                     DTULTATU = SYSDATE,
                     CDOPERULTATU = P_CDOPERADOR
               WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
                 AND NRORG = P_NRORG;
           END IF;
        END LOOP;
    END LOOP;
END;