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;
    V_NRNEGOCIACAOCONTRATO FCT_NEGOCIACAOCONTRATO.NRNEGOCIACAOCONTRATO%TYPE;
    V_VRPERCDESCPERIODO    FCT_DESCPERIODONEGOCONT.VRPERCDESCITEM%TYPE;
    V_VRDESCPERIODO        FCT_DESCPERIODONEGOCONT.VRDESCITEM%TYPE;
    V_IDTPDESCONTOPERIODO  FCT_DESCPERIODONEGOCONT.IDTPDESCONTO%TYPE;
    V_QTMEDSERVICO         INTEGER;
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.NRNEGOCIACAOCONTRATO,
                           ITNEG.VRTOTITEM,
                           ITNEG.VRTOTITEMCOMDESC,
                           DECODE(NEG.VRTOTITEM, 0, 0, NVL(NEG.VRDESCITEM, 0) / NEG.VRTOTITEM * 100) AS VRPERCDESCNEGO
                      INTO V_NRNEGOCIACAOCONTRATO,
                           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_NRNEGOCIACAOCONTRATO := NULL;
                    V_VRTOTITEMCONT := 0;
                    V_VRITEMCONT := 0;
                    V_VRPERCDESCNEGO := 0;
                END;

            ELSE

                BEGIN
                    SELECT ITNEG.NRNEGOCIACAOCONTRATO,
                           ITNEG.VRTOTITEM,
                           ITNEG.VRTOTITEMCOMDESC,
                           DECODE(NEG.VRTOTITEM, 0, 0, NVL(NEG.VRDESCITEM, 0) / NEG.VRTOTITEM * 100) AS VRPERCDESCNEGO
                      INTO V_NRNEGOCIACAOCONTRATO,
                           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_NRNEGOCIACAOCONTRATO := NULL;
                    V_VRTOTITEMCONT := 0;
                    V_VRITEMCONT := 0;
                    V_VRPERCDESCNEGO := 0;
                END;

            END IF; --V_QTDE_FILHOS_ITNEG > 0

            SELECT COUNT(*)
              INTO V_QTMEDSERVICO
              FROM FCT_RELACMEDIDACONTRATO
             WHERE NRSEQCONTRATO  = C_MEDICAO.NRSEQCONTRATO
               AND NRORG          = P_NRORG
               AND CDSERVORCA     = C_ITEM.CDSERVORCA
               AND IDELEMENTORAIZ IS NULL;
            
            IF (V_QTMEDSERVICO > 0) THEN
                V_VRTOTITEMCONT := 0;
                V_VRITEMCONT := 0;
            ELSIF (V_VRPERCDESCNEGO > 0) THEN
                V_VRITEMCONT := V_VRTOTITEMCONT - (V_VRTOTITEMCONT * V_VRPERCDESCNEGO / 100);
            END IF;
            
            IF (V_NRNEGOCIACAOCONTRATO IS NOT NULL) THEN

                BEGIN
                    SELECT VRPERCDESCITEM, VRDESCITEM, IDTPDESCONTO
                      INTO V_VRPERCDESCPERIODO, V_VRDESCPERIODO, V_IDTPDESCONTOPERIODO
                      FROM FCT_DESCPERIODONEGOCONT
                     WHERE NRNEGOCIACAOCONTRATO         = V_NRNEGOCIACAOCONTRATO
                       AND NRORG                        = P_NRORG
                       AND TRUNC(C_MEDICAO.DTINICOMPET) < TRUNC(DTFINALVALIDADEDESC)
                       AND NRORDEMAPLICADESC            = (SELECT MIN(NRORDEMAPLICADESC)
                                                             FROM FCT_DESCPERIODONEGOCONT
                                                            WHERE NRNEGOCIACAOCONTRATO         = V_NRNEGOCIACAOCONTRATO
                                                              AND NRORG                        = P_NRORG
                                                              AND TRUNC(C_MEDICAO.DTINICOMPET) < TRUNC(DTFINALVALIDADEDESC));
                EXCEPTION WHEN NO_DATA_FOUND THEN
                    V_VRPERCDESCPERIODO   := NULL;
                    V_VRDESCPERIODO       := NULL;
                    V_IDTPDESCONTOPERIODO := NULL;
                END;

                IF (V_VRPERCDESCPERIODO IS NOT NULL AND V_VRPERCDESCPERIODO > 0) THEN
                    IF (V_IDTPDESCONTOPERIODO = 'V') THEN
                        V_VRITEMCONT := V_VRITEMCONT - V_VRDESCPERIODO;
                    ELSE
                        V_VRITEMCONT := V_VRITEMCONT - (V_VRITEMCONT * V_VRPERCDESCPERIODO / 100);
                    END IF;
                END IF;

            END IF; --V_NRNEGOCIACAOCONTRATO IS NOT NULL

            UPDATE FCT_ITMEDICAOFATURA
               SET IDSITUACAO         = '1',
                   VRTOTITEMCONT      = V_VRTOTITEMCONT,
                   VRITEMCONT         = V_VRITEMCONT,
                   VRNEGCONTR         = NULL,
                   NRPARCELASNEGCONTR = NULL,
                   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',
                   VRITEMCONT         = NULL,
                   VRITEMCONTCOMDESC  = NULL,
                   VRNEGCONTR         = NULL,
                   NRPARCELASNEGCONTR = NULL,
                   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,
                 VRNEGCONTR         = NULL,
                 NRPARCELASNEGCONTR = NULL
           WHERE NRMEDICAOFATURA = C_MEDICAO.NRMEDICAOFATURA
             AND NRSEQCONTRATO   = C_MEDICAO.NRSEQCONTRATO
             AND NRORG           = P_NRORG;

        END IF;
    END LOOP;
END;
/