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,
  P_NRITFATURACONT IN VARCHAR2 DEFAULT NULL)

AS
  V_NRAPURACAO       VARCHAR2(4000);
  V_SITUVENC         VARCHAR2(1);
  V_VRFATURADO       FLOAT;
  V_NRFATURACONT     FCT_FATURACONT.NRFATURACONT%TYPE;
  V_VRAPURACAO       FLOAT;
  V_VRNOTAFISC       FLOAT;
  V_VRTOTESTRATEIO   FLOAT;

BEGIN
    V_VRFATURADO := 0;
    FOR C_APUR IN (
      SELECT FATURAMENTO.NRVENC,    FATURAMENTO.VRFATURADO, FCT_APURACAO.NRAPURACAO,
             FCT_APURACAO.CDFILIAL, FCT_APURACAO.NRLANCTONF, NOTAFISCAL.VRNOTAFISC,
             FATURAMENTO.CHAVE
      FROM (
         SELECT DISTINCT FCT_FATURACONT.NRFATURACONT AS NRVENC,
                 FCT_FATURACONT.VRFATURADO,
                 FCT_FATURACONT.NRORG,
                 FCT_FATURACONT.NRFATURACONT,
                 NULL AS NRAPURACAO,
                 AGRUPFAT.NRSEQCONTRATO,
                 'APU' AS CHAVE
         FROM FCT_FATURACONT
         INNER JOIN FCT_RELACAGRUPCONTFAT AGRUPFAT
           ON FCT_FATURACONT.NRFATURACONT = AGRUPFAT.NRFATURACONT
           AND FCT_FATURACONT.NRORG       = AGRUPFAT.NRORG
         WHERE (P_NRRATEIOITEMVENC IS NULL AND P_NRITFATURACONT 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))
         UNION ALL
         SELECT RAT.NRRATEIOITEMFAT AS NRVENC,
             RAT.VRRATEADO AS VRFATURADO,
             RAT.NRORG,
             RAT.NRFATURACONT,
             RAT.NRAPURACAO,
             RAT.NRSEQCONTRATO,
             'RAT' AS CHAVE
         FROM FCT_RATEIOITFATURACONT RAT
         WHERE (P_NRITFATURACONT IS NULL)
           AND RAT.NRAPURACAO IN(SELECT NRAPURACAO
                                  FROM FCT_RATEIOITFATURACONT
                                  WHERE ((INSTR(P_NRRATEIOITEMVENC, '_' || TO_CHAR(NRRATEIOITEMFAT) || '_') > 0)))
         UNION ALL
         SELECT ITEM.NRITFATURACONT AS NRVENC,
            ITEM.VRFATURADO,
            ITEM.NRORG,
            ITEM.NRFATURACONT,
            ITEM.NRAPURACAO,
            ITEM.NRSEQCONTRATO,
            'ITEM' AS CHAVE
         FROM FCT_ITFATURACONT ITEM
         WHERE (P_NRRATEIOITEMVENC IS NULL)
           AND ITEM.NRAPURACAO IN(SELECT NRAPURACAO
                                  FROM FCT_ITFATURACONT
                                  WHERE (INSTR(P_NRITFATURACONT, '_' || TO_CHAR(NRITFATURACONT) || '_') > 0))
      ) FATURAMENTO
      INNER JOIN FCT_RELACAPURACAOFATURA APURFATURA
         ON FATURAMENTO.NRFATURACONT = APURFATURA.NRFATURACONT
         AND FATURAMENTO.NRORG       = APURFATURA.NRORG
         AND (FATURAMENTO.NRAPURACAO = APURFATURA.NRAPURACAO
              OR (FATURAMENTO.CHAVE = 'APU' AND FATURAMENTO.NRAPURACAO IS NULL))
      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 FATURAMENTO.NRORG = P_NRORG
        AND ((INSTR(P_NRSEQCONTRATO, '_' || TO_CHAR(FATURAMENTO.NRSEQCONTRATO) || '_') > 0) OR (P_NRSEQCONTRATO IS NULL))
        AND ((INSTR(P_NRFATURACONT, '_' || TO_CHAR(FATURAMENTO.NRFATURACONT) || '_') > 0) OR (P_NRFATURACONT IS NULL))
        AND NOTAFISCAL.IDSTATUS IN ('P', 'C')
    ) LOOP

        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;

            UPDATE FCT_RATEIOITFATURACONT
              SET NRAPURACAO       = NULL,
                  IDSITUVENCRATEIO = '1'
              WHERE NRORG      = P_NRORG
                AND NRAPURACAO = C_APUR.NRAPURACAO;

            UPDATE FCT_ITFATURACONT
              SET NRAPURACAO     = NULL,
                  IDSITUACAO     = '1',
                  VRFATURADO     = 0,
                  VRFALTAFATURAR = VRVENCIMENTO
              WHERE NRORG      = P_NRORG
                AND NRAPURACAO = C_APUR.NRAPURACAO;

        ELSE
            V_NRFATURACONT := SUBSTR(P_NRFATURACONT, 2, INSTR(P_NRFATURACONT, '_', 2) - 2);
            V_VRFATURADO   := ROUND(V_VRFATURADO + C_APUR.VRFATURADO, 2);
            V_VRAPURACAO   := ROUND(C_APUR.VRFATURADO, 2);
            V_VRNOTAFISC   := ROUND(C_APUR.VRNOTAFISC, 2);

            IF (V_VRFATURADO = V_VRNOTAFISC) THEN
              V_SITUVENC := '1';
            ELSE
              V_SITUVENC := '6';
            END IF;

            UPDATE FCT_FATURACONT
              SET VRFATURADO     = VRFATURADO     - V_VRAPURACAO,
                  VRFALTAFATURAR = VRFALTAFATURAR + V_VRAPURACAO,
                  IDSITUACAO     = V_SITUVENC
              WHERE NRFATURACONT = V_NRFATURACONT
                AND NRORG        = P_NRORG;

            IF C_APUR.CHAVE = 'ITEM' THEN

              UPDATE FCT_ITFATURACONT
              SET IDSITUACAO     = '1',
                  VRFATURADO     = VRFATURADO     - V_VRAPURACAO,
                  VRFALTAFATURAR = VRFALTAFATURAR + V_VRAPURACAO,
                  NRAPURACAO     = NULL
              WHERE NRITFATURACONT = C_APUR.NRVENC
                AND NRORG          = P_NRORG;

              FOR C_RATEIOS IN (
                SELECT (SELECT SUM(VRRATEADO)
                        FROM FCT_RATEIOITFATURACONT
                        WHERE NRFATURACONT  = RAT.NRFATURACONT
                          AND NRESTRUTURAM  = RAT.NRESTRUTURAM) AS VRRATEADO,
                       RAT.NRESTRUTURAM
                FROM FCT_RATEIOITFATURACONT RAT
                WHERE RAT.NRORG          = P_NRORG
                  AND RAT.NRFATURACONT   = V_NRFATURACONT
                  AND RAT.NRITFATURACONT = C_APUR.NRVENC
              ) LOOP

                BEGIN
                  SELECT NVL(SUM(VRRATEADO), 0)
                  INTO V_VRTOTESTRATEIO
                  FROM FCT_RATEIOITFATURACONT RATEIO
                  JOIN FCT_ITFATURACONT ITEM
                    ON RATEIO.NRITFATURACONT = ITEM.NRITFATURACONT
                    AND RATEIO.NRORG         = ITEM.NRORG
                  WHERE RATEIO.NRFATURACONT  = V_NRFATURACONT
                    AND RATEIO.NRESTRUTURAM  = C_RATEIOS.NRESTRUTURAM
                    AND IDSITUACAO IN('1', '6');
                EXCEPTION WHEN NO_DATA_FOUND THEN
                  V_VRTOTESTRATEIO := 0;
                END;

                IF ROUND(C_RATEIOS.VRRATEADO, 2) = ROUND(V_VRTOTESTRATEIO, 2) THEN
                  V_SITUVENC := '1';
                ELSE
                  V_SITUVENC := '6';
                END IF;

                UPDATE FCT_RATEIOITFATURACONT
                SET IDSITUVENCRATEIO = V_SITUVENC,
                    NRAPURACAO       = NULL
                WHERE NRFATURACONT  = V_NRFATURACONT
                  AND NRESTRUTURAM  = C_RATEIOS.NRESTRUTURAM
                  AND NRORG         = P_NRORG;

              END LOOP;

            ELSE IF C_APUR.CHAVE = 'RAT' THEN
              FOR C_ITENSRATEIO IN (SELECT VRFATURADO,
                                      ROUND(VRFALTAFATURAR, 2) AS VRFALTAFATURAR,
                                      ROUND(VRVENCIMENTO, 2) AS VRVENCIMENTO,
                                      NRITFATURACONT
                                    FROM FCT_ITFATURACONT
                                    WHERE NRORG           = P_NRORG
                                      AND NRITFATURACONT IN (SELECT NRITFATURACONT
                                                             FROM FCT_RATEIOITFATURACONT
                                                             WHERE NRAPURACAO = C_APUR.NRAPURACAO
                                                                AND NRORG     = P_NRORG)
              ) LOOP
                
                SELECT NVL(SUM(VRRATEADO), 0)
                INTO V_VRTOTESTRATEIO
                FROM FCT_RATEIOITFATURACONT RATEIO
                WHERE RATEIO.NRFATURACONT   = V_NRFATURACONT
                  AND RATEIO.NRITFATURACONT = C_ITENSRATEIO.NRITFATURACONT
                  AND RATEIO.IDSITUVENCRATEIO = '2'
                  AND INSTR(P_NRRATEIOITEMVENC, '_' || RATEIO.NRRATEIOITEMFAT || '_') > 0;

                IF (ROUND(V_VRTOTESTRATEIO, 2) = ROUND(C_ITENSRATEIO.VRFATURADO, 2)) THEN
                  V_SITUVENC := '1';
                ELSE
                  V_SITUVENC := '6';
                END IF;

                UPDATE FCT_ITFATURACONT
                SET IDSITUACAO     = V_SITUVENC,
                    VRFATURADO     = VRFATURADO     - V_VRTOTESTRATEIO,
                    VRFALTAFATURAR = VRFALTAFATURAR + V_VRTOTESTRATEIO
                WHERE NRORG          = P_NRORG
                  AND NRITFATURACONT = C_ITENSRATEIO.NRITFATURACONT;
              END LOOP;

              UPDATE FCT_RATEIOITFATURACONT
                 SET NRAPURACAO       = NULL,
                     IDSITUVENCRATEIO = '1'
               WHERE NRORG      = P_NRORG
                 AND NRAPURACAO = C_APUR.NRAPURACAO;
            END IF;
          END IF;
        END IF;

        V_NRAPURACAO := V_NRAPURACAO||'_'||C_APUR.NRAPURACAO;
    END LOOP; -- C_APUR

    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 NRAPURACAO = C_DELAPU.NRAPURACAO
          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;