create or replace PROCEDURE FCT_CANC_APURCAO_FATURAMENTO
         (P_NRORG IN NUMBER,
          P_NRSEQCONTRATO IN VARCHAR2,
          P_CDOPERINCLUSAO IN FCT_APURACAO.CDOPERINCLUSAO%TYPE,
          P_DATAINI IN DATE,
          P_DATAFIN IN DATE,
          P_NRFATURACONT IN VARCHAR2,
          P_NRRATEIOITEMVENC IN VARCHAR2 DEFAULT NULL)
AS
  V_NRAPURACAO VARCHAR2(4000);
  V_SITUVENC VARCHAR2(1);
  V_VRFATURADO FLOAT;
  V_RATVENCPREV NUMBER;
  V_NRFATURACONT FCT_FATURACONT.NRFATURACONT%TYPE;
BEGIN
    V_VRFATURADO := 0;
 /*Quando for realziada a rotina de Rateio, deve ser considerado no loop de apuração C_APUR
    SELECT FCT_RATEIOITVENCIMENTO.NRRATEIOITEMVENC AS NRVENC,
           FCT_RATEIOITVENCIMENTO.VRRATEADO AS VRFATURADO,
           FCT_APURACAO.NRAPURACAO,
           FCT_APURACAO.CDFILIAL,
           FCT_APURACAO.NRLANCTONF,
           NOTAFISCAL.VRNOTAFISC,
           'RAT' AS CHAVE
      FROM FCT_RATEIOITVENCIMENTO, FCT_APURACAO, NOTAFISCAL
      JOIN FCT_APURACAO
        ON FCT_APURACAO.NRAPURACAO = FCT_APURACAO.NRAPURACAO
       AND FCT_APURACAO.NRSEQCONTRATO = FCT_APURACAO.NRSEQCONTRATO
       AND FCT_APURACAO.NRORG = FCT_APURACAO.NRORG
      JOIN NOTAFISCAL
        ON NOTAFISCAL.NRLANCTONF = FCT_APURACAO.NRLANCTONF
       AND NOTAFISCAL.CDFILIAL = FCT_APURACAO.CDFILIAL
       AND NOTAFISCAL.NRORG = FCT_APURACAO.NRORG
     WHERE (FCT_RATEIOITVENCIMENTO.NRORG = P_NRORG)
       AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(FCT_RATEIOITVENCIMENTO.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
       AND ((INSTR(P_NRFATURACONT, '_' || TO_CHAR(FCT_FATURACONT.NRFATURACONT) || '_') > 0) OR (P_NRFATURACONT IS NULL))
       AND ((INSTR(P_NRRATEIOITEMVENC, '_' || TO_CHAR(FCT_RATEIOITVENCIMENTO.NRRATEIOITEMVENC) || '_') > 0))
       AND (NOTAFISCAL.IDSTATUS = 'P')
    */
    FOR C_APUR IN (SELECT DISTINCT FCT_FATURACONT.NRFATURACONT AS NRVENC,
                            FCT_FATURACONT.VRFATURADO,
                            APURFATURA.NRAPURACAO,
                            FCT_APURACAO.CDFILIAL,
                            FCT_APURACAO.NRLANCTONF,
                            NOTAFISCAL.VRNOTAFISC,
                            'APU' AS CHAVE
                    FROM FCT_FATURACONT
                    INNER JOIN FCT_RELACAGRUPCONTFAT AGRUPFAT
                      ON FCT_FATURACONT.NRFATURACONT = AGRUPFAT.NRFATURACONT
                      AND FCT_FATURACONT.NRORG       = AGRUPFAT.NRORG
                    INNER JOIN FCT_RELACAPURACAOFATURA APURFATURA
                      ON FCT_FATURACONT.NRFATURACONT = APURFATURA.NRFATURACONT
                      AND FCT_FATURACONT.NRORG       = APURFATURA.NRORG
                    INNER JOIN FCT_APURACAO
                        ON APURFATURA.NRAPURACAO = FCT_APURACAO.NRAPURACAO
                        AND APURFATURA.NRORG     = FCT_APURACAO.NRORG
                    JOIN NOTAFISCAL
                      ON NOTAFISCAL.NRLANCTONF = FCT_APURACAO.NRLANCTONF
                     AND NOTAFISCAL.CDFILIAL = FCT_APURACAO.CDFILIAL
                     AND NOTAFISCAL.NRORG = FCT_APURACAO.NRORG
                   WHERE (FCT_FATURACONT.NRORG = P_NRORG)
                     AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(AGRUPFAT.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
                     AND ((INSTR(P_NRFATURACONT, '_' || TO_CHAR(FCT_FATURACONT.NRFATURACONT) || '_') > 0) OR (P_NRFATURACONT IS NULL))
                     AND (P_NRRATEIOITEMVENC IS NULL)
                     AND ((TRUNC(TO_DATE(FCT_FATURACONT.DTINICOMPET)) >= TRUNC(TO_DATE(P_DATAINI))) OR (P_DATAINI IS NULL))
                     AND ((TRUNC(TO_DATE(FCT_FATURACONT.DTFINCOMPET)) <= TRUNC(TO_DATE(P_DATAFIN))) OR (P_DATAFIN IS NULL))
                     AND (NOTAFISCAL.IDSTATUS = 'P')) LOOP

        V_NRFATURACONT := C_APUR.NRVENC;

        IF C_APUR.CHAVE = 'APU' THEN

            SELECT VENC.VRFATURADO
              INTO V_VRFATURADO
              FROM FCT_FATURACONT VENC
             WHERE VENC.NRFATURACONT = C_APUR.NRVENC
               AND VENC.NRORG = P_NRORG;

            UPDATE FCT_FATURACONT
               SET VRFATURADO     = VRFATURADO - C_APUR.VRNOTAFISC,
                   VRFALTAFATURAR = VRFALTAFATURAR + C_APUR.VRNOTAFISC,
                   IDSITUACAO     = '1',
                   IDFORMAFATURA  = NULL
             WHERE NRORG        = P_NRORG
               AND NRFATURACONT = C_APUR.NRVENC;

         /* -- Rateio será realizado posteriormente --
            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_FATURACONT
               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;
                                               */
        END IF;
        V_NRAPURACAO := V_NRAPURACAO||'_'||C_APUR.NRAPURACAO;
   END LOOP;
   V_NRAPURACAO := V_NRAPURACAO||'_';
   FOR C_DELAPU IN (SELECT FCT_APURACAO.NRLANCTONF, FCT_APURACAO.CDFILIAL, FCT_APURACAO.NRAPURACAO
                     FROM FCT_APURACAO
                     INNER JOIN FCT_RELACAPURACAOFATURA APURFAT
                        ON FCT_APURACAO.NRAPURACAO = APURFAT.NRAPURACAO
                        AND FCT_APURACAO.NRORG     = APURFAT.NRORG
                     WHERE ((INSTR(V_NRAPURACAO, '_' || TO_CHAR(APURFAT.NRAPURACAO) || '_') > 0) OR (V_NRAPURACAO IS NULL))
                  )LOOP

         DELETE FROM FCT_RELACAPURACAOFATURA
         WHERE NRFATURACONT = V_NRFATURACONT
          AND NRORG = P_NRORG;

         DELETE FROM FCT_APURACAO
          WHERE NRORG      = P_NRORG
            AND NRAPURACAO = C_DELAPU.NRAPURACAO;

         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;