create or replace PROCEDURE FCT_CANC_FATURA_CONTRATO
         (P_NRORG IN NUMBER,
          P_NRSEQCONTRATO IN VARCHAR2,
          P_CDOPERINCLUSAO IN FCT_APURACAO.CDOPERINCLUSAO%TYPE,
          P_DATAINI IN DATE,
          P_DATAFIN IN DATE,
          P_NRVENCPREVCONT IN VARCHAR2,
          P_NRRATEIOITEMVENC IN VARCHAR2 DEFAULT NULL)
AS
  V_NRAPURACAO VARCHAR2(4000);
  V_SITUVENC VARCHAR2(1);
  V_VRFATURADO FLOAT;
  V_RATVENCPREV NUMBER;

BEGIN
    V_VRFATURADO := 0;

    FOR C_APUR IN (SELECT AP.NRAPURACAO,
                          AP.CDFILIAL,
                          AP.NRLANCTONF,
                          NF.VRNOTAFISC,
                          VP.VRFATURADO,
                          VP.NRVENCPREVCONT AS VENCMEDAPU,
                          'APU' AS CHAVE
                    FROM FCT_VENCPREVCONT VP, FCT_APURACAO AP, NOTAFISCAL NF
                   WHERE (VP.NRORG = P_NRORG)
                     AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(VP.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                     AND ((INSTR(P_NRVENCPREVCONT, '_' || TO_CHAR(VP.NRVENCPREVCONT) || '_') > 0) OR (P_NRVENCPREVCONT IS NULL))
                     AND (P_NRRATEIOITEMVENC IS NULL)
                     AND ((VP.DTINICOMPET >= P_DATAINI) OR (P_DATAINI IS NULL))
                     AND ((VP.DTFINCOMPET <= P_DATAFIN) OR (P_DATAFIN IS NULL))
                     AND (AP.NRORG = VP.NRORG)
                     AND (AP.NRSEQCONTRATO = VP.NRSEQCONTRATO)
                     AND (AP.NRAPURACAO = VP.NRAPURACAO)
                     AND (AP.NRLANCTONF IS NOT NULL)
                     AND (NF.NRLANCTONF = AP.NRLANCTONF)
                     AND (NF.CDFILIAL = AP.CDFILIAL)
                     AND (NF.IDSTATUS = 'P')
                  UNION ALL
                  SELECT AP.NRAPURACAO,
                         AP.CDFILIAL,
                         AP.NRLANCTONF,
                         NF.VRNOTAFISC,
                         RAT.VRRATEADO AS VRFATURADO,
                         RAT.NRRATEIOITEMVENC AS VENCMEDAPU,
                         'RAT' AS CHAVE
                    FROM FCT_RATEIOITVENCIMENTO RAT, FCT_APURACAO AP, NOTAFISCAL NF
                   WHERE (RAT.NRORG = P_NRORG)
                     AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(RAT.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                     AND ((INSTR(P_NRVENCPREVCONT, '_' || TO_CHAR(RAT.NRVENCPREVCONT) || '_') > 0) OR (P_NRVENCPREVCONT IS NULL))
                     AND ((INSTR(P_NRRATEIOITEMVENC, '_' || TO_CHAR(RAT.NRRATEIOITEMVENC) || '_') > 0))
                     AND (AP.NRORG = RAT.NRORG)
                     AND (AP.NRSEQCONTRATO = RAT.NRSEQCONTRATO)
                     AND (AP.NRAPURACAO = RAT.NRAPURACAO)
                     AND (AP.NRLANCTONF IS NOT NULL)
                     AND (NF.NRLANCTONF = AP.NRLANCTONF)
                     AND (NF.CDFILIAL = AP.CDFILIAL)
                     AND (NF.IDSTATUS = 'P')   
                  UNION ALL
                  SELECT AP.NRAPURACAO,
                         AP.CDFILIAL,
                         AP.NRLANCTONF,
                         NF.VRNOTAFISC,
                         NF.VRNOTAFISC AS VRFATURADO,
                         MD.NRMEDICAOAPU AS VENCMEDAPU,
                         'MED' AS CHAVE
                    FROM FCT_MEDICAOAPU MD, FCT_APURACAO AP, NOTAFISCAL NF
                   WHERE (MD.NRORG = P_NRORG)
                     AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(MD.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                     AND ((INSTR(P_NRVENCPREVCONT, '_' || TO_CHAR(MD.NRMEDICAOAPU) || '_') > 0) OR (P_NRVENCPREVCONT IS NULL))
                     AND ((MD.DTINICOMPET >= P_DATAINI) OR (P_DATAINI IS NULL))
                     AND ((MD.DTFINCOMPET <= P_DATAFIN) OR (P_DATAFIN IS NULL))
                     AND (MD.IDTPMEDIAPU ='E')
                     AND (AP.NRORG = MD.NRORG)
                     AND (AP.NRSEQCONTRATO = MD.NRSEQCONTRATO)
                     AND (AP.NRAPURACAO = MD.NRAPURACAO)
                     AND (AP.NRLANCTONF IS NOT NULL)
                     AND (NF.NRLANCTONF = AP.NRLANCTONF)
                     AND (NF.CDFILIAL = AP.CDFILIAL)
                     AND (NF.IDSTATUS = 'P')) LOOP

        IF C_APUR.CHAVE = 'APU' THEN
           
            SELECT VENC.VRFATURADO
              INTO V_VRFATURADO
              FROM FCT_VENCPREVCONT VENC
             WHERE VENC.NRVENCPREVCONT = C_APUR.VENCMEDAPU
               AND VENC.NRORG = P_NRORG;
           
            IF (V_VRFATURADO = C_APUR.VRNOTAFISC) THEN
              V_SITUVENC := 'C';
            ELSE 
              V_SITUVENC := 'I';
            END IF;

            UPDATE FCT_VENCPREVCONT
               SET NRAPURACAO = NULL,
                   VRFATURADO = VRFATURADO - C_APUR.VRNOTAFISC,
                   VRFALTAFATURAR = VRFALTAFATURAR + C_APUR.VRNOTAFISC,
                   IDSITUVENCPREV = V_SITUVENC
             WHERE NRORG = P_NRORG
               AND NRAPURACAO = C_APUR.NRAPURACAO;

            UPDATE FCT_RATEIOITVENCIMENTO
               SET NRAPURACAO = NULL,
                   IDSITUVENCRATEIO = 'C' 
             WHERE NRORG = P_NRORG
               AND NRAPURACAO = C_APUR.NRAPURACAO;

        ELSIF C_APUR.CHAVE = 'RAT' THEN
            
            V_RATVENCPREV := SUBSTR(P_NRVENCPREVCONT, 2, INSTR(P_NRVENCPREVCONT, '_', 2) - 2);
            
            V_VRFATURADO := V_VRFATURADO + C_APUR.VRFATURADO;
            
            IF  (V_VRFATURADO = C_APUR.VRNOTAFISC) THEN
              V_SITUVENC := 'P';
            ELSE 
              V_SITUVENC := 'I';
            END IF;

            UPDATE FCT_VENCPREVCONT 
               SET VRFATURADO =  VRFATURADO - C_APUR.VRFATURADO,
                   VRFALTAFATURAR = VRFALTAFATURAR + C_APUR.VRFATURADO,
                   IDSITUVENCPREV = V_SITUVENC
             WHERE NRVENCPREVCONT = V_RATVENCPREV
               AND NRORG = P_NRORG;

            UPDATE FCT_RATEIOITVENCIMENTO
               SET NRAPURACAO = NULL,
                   IDSITUVENCRATEIO = 'C' 
             WHERE NRORG = P_NRORG
               AND NRAPURACAO = C_APUR.NRAPURACAO; 

        ELSE
            UPDATE FCT_MEDICAOAPU
                SET NRAPURACAO = NULL
              WHERE NRORG = P_NRORG
                --AND NRMEDICAOAPU = C_APUR.VENCMEDAPU
                  AND NRAPURACAO = C_APUR.NRAPURACAO;
        END IF;

        V_NRAPURACAO := V_NRAPURACAO||'_'||C_APUR.NRAPURACAO;

   END LOOP;
   V_NRAPURACAO := V_NRAPURACAO||'_';

   FOR C_DELAPU IN (SELECT NRLANCTONF, CDFILIAL, NRAPURACAO
                      FROM FCT_APURACAO
                     WHERE ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(FCT_APURACAO.NRSEQCONTRATO) || '_') > 0))
                       AND ((INSTR(V_NRAPURACAO, '_' || TO_CHAR(FCT_APURACAO.NRAPURACAO) || '_') > 0))

                  )LOOP


             DELETE FROM FCT_APURACAO
              WHERE NRORG = P_NRORG
                AND NRLANCTONF = C_DELAPU.NRLANCTONF
                AND CDFILIAL = C_DELAPU.CDFILIAL;

             DELETE FROM RATEIONF
              WHERE NRORG = P_NRORG
                AND NRLANCTONF = C_DELAPU.NRLANCTONF
                AND CDFILIAL = C_DELAPU.CDFILIAL;

        DELETA_NF(C_DELAPU.CDFILIAL,C_DELAPU.NRLANCTONF);

   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('FCT_CANC_FATURA_CONTRATO','6.19.xxx'); END;
/