CREATE OR REPLACE PROCEDURE FCT_CANCELAR_MEDICAO (
    P_NRMEDICAOFATURA  IN VARCHAR2,
    P_NRSEQCONTRATO    IN VARCHAR2,
    P_NRORG            IN NUMBER,
    P_CDOPERADOR       IN VARCHAR2
)
AS

    V_NRSEQORCAMENTO  FCT_ORCAMENTO.NRSEQORCAMENTO%TYPE;
    V_VRTOTITEMCONT   FCT_ITEMCONTRATO.VRTOTITEMCONT%TYPE;
    V_VALIDAAPUR      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 IN ('2', '3')) LOOP
      BEGIN
          SELECT COUNT(*)
            INTO V_VALIDAAPUR
            FROM FCT_FATURACONT
           WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
             AND NRSEQCONTRATO = C_MEDICAO.NRSEQCONTRATO
             AND NRORG = P_NRORG;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_VALIDAAPUR := 0;
      END;

      IF (V_VALIDAAPUR = 0) THEN

          BEGIN
              SELECT NRSEQORCAMENTO
                INTO V_NRSEQORCAMENTO
                FROM FCT_REFEORCACONT
               WHERE NRSEQCONTRATO = C_MEDICAO.NRSEQCONTRATO
                 AND NRORG = P_NRORG;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
                V_NRSEQORCAMENTO := NULL;
          END;


          FOR C_ITEM IN (SELECT NRITMEDICAOFATURA, NRMEDICAOFATURA, NRSEQCONTRATO, NRITEMOCONTRATO, CDSERVORCA,
                                CDPRODFATURA,IDSITUACAO, VRTOTITEMCONT, IDATIVO, NRORG
                           FROM FCT_ITMEDICAOFATURA
                          WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
                            AND NRSEQCONTRATO = C_MEDICAO.NRSEQCONTRATO
                            AND NRORG = P_NRORG) LOOP

            BEGIN
                SELECT VRTOTITEMCONT
                  INTO V_VRTOTITEMCONT
                  FROM FCT_ITEMCONTRATO
                 WHERE NRSEQCONTRATO = C_ITEM.NRSEQCONTRATO
                   AND NRITEMOCONTRATO = C_ITEM.NRITEMOCONTRATO
                   AND NRORG = P_NRORG;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    V_VRTOTITEMCONT := 0;
            END;

            UPDATE FCT_ITMEDICAOFATURA
               SET IDSITUACAO = '1',
                   VRTOTITEMCONT = V_VRTOTITEMCONT,
                   CDOPERULTATU = P_CDOPERADOR,
                   NRORGULTATU = P_NRORG,
                   DTULTATU = SYSDATE
             WHERE NRITMEDICAOFATURA = C_ITEM.NRITMEDICAOFATURA
               AND NRMEDICAOFATURA = C_ITEM.NRMEDICAOFATURA
               AND NRORG = P_NRORG;

          END LOOP;

          FOR C_MEDVAR IN ( SELECT NRMEDVARMEDICAOFATURA, NRMEDICAOFATURA, IDELEMENTORAIZ, CDSERVORCA,
                                    CDTPMEDIDAVARIAVEL, QTMEDIDAVARIAVEL, IDMULTIPVARIACAO, IDATIVO, NRORG
                               FROM FCT_MEDVARMEDICAOFATURA
                              WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
                                AND NRORG = P_NRORG
          ) LOOP
            --Buscar os itens na proposta pra saber a medida variavel e sua quantidade
            FOR C_MEDIDAS IN (
                SELECT REL.IDELEMENTORAIZ,
                       REL.CDTPMEDIDAVARIAVEL,
                       REL.QTMEDIDAVARIAVEL,
                       REL.QTFRANQUIA,
                       VAR.IDMULTIPVARIACAO
                  FROM FCT_RELACMEDIDAPROPOSTA REL
                  LEFT JOIN FCT_VARPRECBASESERVORCA VAR
                    ON REL.NRSEQORCAMENTO     = VAR.NRSEQORCAMENTO
                   AND REL.CDTPMEDIDAVARIAVEL = VAR.CDTPMEDIDAVARIAVEL
                   AND REL.NRORG              = VAR.NRORG
                 WHERE REL.NRSEQORCAMENTO = V_NRSEQORCAMENTO
                   AND REL.NRORG          = P_NRORG
                   AND VAR.CDSERVORCA     = C_MEDVAR.CDSERVORCA
                   AND REL.IDELEMENTORAIZ = C_MEDVAR.IDELEMENTORAIZ
                   AND REL.QTMEDIDAVARIAVEL BETWEEN VAR.QTMEDIDAINIVARIACAO AND VAR.QTMEDIDAFIMVARIACAO
                 ORDER BY REL.IDELEMENTORAIZ
            ) LOOP

                UPDATE FCT_MEDVARMEDICAOFATURA
                   SET QTMEDIDAVARIAVEL = C_MEDIDAS.QTMEDIDAVARIAVEL,
                       QTMEDIDAMEDICAO  = C_MEDIDAS.QTMEDIDAVARIAVEL - C_MEDIDAS.QTFRANQUIA,
                       IDCALCULADO      = 'N',
                       DTULTATU         = SYSDATE,
                       CDOPERULTATU     = P_CDOPERADOR,
                       NRORGULTATU      = P_NRORG
                 WHERE NRMEDVARMEDICAOFATURA = C_MEDVAR.NRMEDVARMEDICAOFATURA
                   AND NRMEDICAOFATURA       = C_MEDVAR.NRMEDICAOFATURA
                   AND IDELEMENTORAIZ        = C_MEDVAR.IDELEMENTORAIZ
                   AND NRORG                 = P_NRORG;

            END LOOP;
          END LOOP;

          UPDATE FCT_MEDICAOFATURA
             SET IDSITUACAO = '1',
                 VRMEDICAO = 0
           WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
             AND NRSEQCONTRATO = C_MEDICAO.NRSEQCONTRATO
             AND NRORG = P_NRORG;

        END IF;
    END LOOP;
END;