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_ITNEGOCIACAOCONTRATO.VRTOTITEM%TYPE;
    V_VRITEMCONT        FCT_ITNEGOCIACAOCONTRATO.VRTOTITEMCOMDESC%TYPE;
    V_VALIDAAPUR        NUMBER;
    V_VALIDAMEDIDACONT  NUMBER;
    V_QTDE_FILHOS_ITNEG NUMBER;
    V_VRPERCDESCNEGO    FCT_NEGOCIACAOCONTRATO.VRPERCDESCITEM%TYPE;
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

            SELECT COUNT(*)
              INTO V_QTDE_FILHOS_ITNEG
              FROM FCT_ITNEGOCIACAOCONTRATO
             WHERE NRITEMCONTRATOPAI = C_ITEM.NRITEMOCONTRATO
               AND NRORG             = P_NRORG;
            
            IF (V_QTDE_FILHOS_ITNEG > 0) THEN
            
                BEGIN
                    SELECT ITNEG.VRTOTITEM,
                           ITNEG.VRTOTITEMCOMDESC,
                           DECODE(NEG.VRTOTITEM, 0, 0, NVL(NEG.VRDESCITEM, 0) / NEG.VRTOTITEM * 100) AS VRPERCDESCNEGO
                      INTO V_VRTOTITEMCONT,
                           V_VRITEMCONT,
                           V_VRPERCDESCNEGO
                      FROM (
                        SELECT SUM(ITNEG.VRTOTITEM) AS VRTOTITEM,
                               SUM(ITNEG.VRTOTITEMCOMDESC) AS VRTOTITEMCOMDESC,
                               ITNEG.NRNEGOCIACAOCONTRATO,
                               ITNEG.NRORG
                           FROM FCT_ITNEGOCIACAOCONTRATO ITNEG
                          WHERE ITNEG.NRITEMCONTRATOPAI = C_ITEM.NRITEMOCONTRATO
                            AND ITNEG.NRORG             = P_NRORG
                          GROUP BY ITNEG.NRNEGOCIACAOCONTRATO,
                                   ITNEG.NRORG
                      ) ITNEG
                     INNER JOIN FCT_NEGOCIACAOCONTRATO NEG
                        ON ITNEG.NRNEGOCIACAOCONTRATO = NEG.NRNEGOCIACAOCONTRATO
                       AND ITNEG.NRORG                = NEG.NRORG;
                EXCEPTION WHEN NO_DATA_FOUND THEN
                    V_VRTOTITEMCONT := 0;
                    V_VRITEMCONT := 0;
                    V_VRPERCDESCNEGO := 0;
                END;
            
            ELSE
            
                BEGIN
                    SELECT ITNEG.VRTOTITEM,
                           ITNEG.VRTOTITEMCOMDESC,
                           DECODE(NEG.VRTOTITEM, 0, 0, NVL(NEG.VRDESCITEM, 0) / NEG.VRTOTITEM * 100) AS VRPERCDESCNEGO
                      INTO V_VRTOTITEMCONT,
                           V_VRITEMCONT,
                           V_VRPERCDESCNEGO
                      FROM FCT_ITNEGOCIACAOCONTRATO ITNEG
                     INNER JOIN FCT_NEGOCIACAOCONTRATO NEG
                        ON ITNEG.NRNEGOCIACAOCONTRATO = NEG.NRNEGOCIACAOCONTRATO
                       AND ITNEG.NRORG                = NEG.NRORG
                     WHERE ITNEG.NRITEMOCONTRATO = C_ITEM.NRITEMOCONTRATO
                       AND ITNEG.NRORG           = P_NRORG;
                EXCEPTION WHEN NO_DATA_FOUND THEN
                    V_VRTOTITEMCONT := 0;
                    V_VRITEMCONT := 0;
                    V_VRPERCDESCNEGO := 0;
                END;
            
            END IF; --V_QTDE_FILHOS_ITNEG > 0

            IF (V_VRPERCDESCNEGO > 0) THEN
                V_VRITEMCONT := V_VRTOTITEMCONT - (V_VRTOTITEMCONT * V_VRPERCDESCNEGO / 100);
            END IF;

            UPDATE FCT_ITMEDICAOFATURA
               SET IDSITUACAO    = '1',
                   VRTOTITEMCONT = V_VRTOTITEMCONT,
                   VRITEMCONT    = V_VRITEMCONT,
                   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
            SELECT COUNT(*)
              INTO V_VALIDAMEDIDACONT
              FROM FCT_RELACMEDIDACONTRATO
             WHERE INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(NRSEQCONTRATO) || '_') > 0
               AND NRORG = P_NRORG;
            
            UPDATE FCT_MEDVARMEDICAOFATURA
                   SET 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; --C_MEDVAR

          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;