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
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') LOOP

        FOR C_FATURA IN  (SELECT NRFATURACONT, NRSEQCONTRATO, IDSITUACAO
                            FROM FCT_FATURACONT
                           WHERE 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_FATURA.NRSEQCONTRATO
                   AND NRORG = P_NRORG;

              DELETE FROM FCT_FATURACONT
                 WHERE NRFATURACONT = C_FATURA.NRFATURACONT
                   AND NRSEQCONTRATO = C_FATURA.NRSEQCONTRATO
                   AND NRORG = P_NRORG;

              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;