create or replace PROCEDURE TOTALIZA_VENDAS_REL(
  P_AUTOPERIODOINI IN  DATE DEFAULT TRUNC(SYSDATE)-30,
  P_AUTOPERIODO    IN  DATE DEFAULT TRUNC(SYSDATE))
AS

  V_VENDAS        NUMBER;
  Q_EMPTY         NUMBER;
  V_PERIODO       DATE;
  V_PERIODOINI    DATE;
  V_SEQUENCIAL    NUMBER;
  V_COUNT         NUMBER;
  V_TXENTREGA     NUMBER;
  V_TXSERV        NUMBER;
  V_ITVENDACAN    NUMBER;
  V_TIPOSERVICO   NUMBER;
  V_SEQVARCHAR    VARCHAR(50);
  
  V_COUNTCOMMIT   NUMBER;
  V_COMMIT        NUMBER;

  FUNCTION SEQUENCIAL_VENDASINT RETURN NUMBER
  AS
  BEGIN
    NOVO_CODIGO_NT('VENDASINT', V_SEQVARCHAR);
    V_SEQUENCIAL := TO_NUMBER(V_SEQVARCHAR);
    RETURN V_SEQUENCIAL;
  END;

  PROCEDURE COMMITA
  AS
  BEGIN
     V_COUNTCOMMIT := V_COUNTCOMMIT + 1;
     IF V_COUNTCOMMIT = V_COMMIT THEN
        V_COUNTCOMMIT := 0;
        COMMIT;
     END IF;
  END;
BEGIN
    V_COMMIT      := 1000;
    V_PERIODO     := NVL(P_AUTOPERIODO, TRUNC(SYSDATE));
    V_PERIODOINI  := NVL(P_AUTOPERIODOINI, TRUNC(SYSDATE-30));
    V_COUNTCOMMIT := 0;
    Q_EMPTY       := 0;
    BEGIN
      SELECT COUNT(*) INTO Q_EMPTY
        FROM VENDASINT;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
           Q_EMPTY := 0;
    END;
    IF(Q_EMPTY = 0) THEN
      V_PERIODO     := TRUNC(SYSDATE);
      V_PERIODOINI  := TRUNC(SYSDATE)-90;
    END IF;
    FOR C_FILIAL IN (SELECT CDFILIAL, NRORG FROM FILIAL) LOOP
        /*Verifica as vendas se existe alguma que não está na tabela de venda sintetica*/
        BEGIN
            SELECT COUNT(NRSEQVENDA) INTO V_VENDAS
              FROM VENDA V
             WHERE CDFILIAL = C_FILIAL.CDFILIAL
               AND DTVENDA BETWEEN V_PERIODOINI AND V_PERIODO
               AND NRSEQVENDA NOT IN (SELECT NRSEQVENDA 
                                        FROM VENDASINT 
                                       WHERE CDFILIAL = C_FILIAL.CDFILIAL
                                         AND DTVENDA BETWEEN V_PERIODOINI AND V_PERIODO);
         EXCEPTION
          WHEN NO_DATA_FOUND THEN
               V_VENDAS := 0;
         END;
         /*Busca todas vendas com seus calculos principais ja executados*/
         IF (V_VENDAS > 0) THEN
            FOR C_VENDA IN (SELECT SUM(TRUNC(I.QTPRODVEND * ( NVL(I.VRUNITVEND, 0) + NVL(I.VRUNITVENDCL,0)), 2)) AS VRTOTVEND,
                                   SUM(I.QTPRODVEND) AS QTPRODVENDTOT, SUM(TRUNC(NVL(I.VRDESITVEND,0),2)) AS VRTOTDESITVEND, SUM(TRUNC(NVL(I.VRACRITVEND,0),2)) AS VRTOTACRITVEND,
                                   V.IDSITUVENDA, V.NRSEQVENDA, V.CDCAIXA, V.CDLOJA, V.CDFILIAL, 
                                   V.NRORG, V.NRPESMESAVENDA, V.CDCLIENTE, V.CDVENDEDOR, V.DTVENDA,
                                   V.DTENTRVENDA, V.CDOPERADOR, V.CDSUPERVISOR, V.VRREPIQUEVENDA, V.DTABERVENDA, 
                                   V.CDPRODTAXASERV, V.CDPRODTAXAENTR, V.VRTXSEVENDA, V.IDSTATUSNFCE, V.IDORIGEMVENDA,
                                   SUBSTR(V.IDORIGEMVENDA, 0, 3) AS TIPOVENDA
                              FROM ITEMVENDA I
                              JOIN ( SELECT V.IDSITUVENDA, V.NRSEQVENDA, C.CDCAIXA, C.CDLOJA, C.CDFILIAL, 
                                            C.NRORG, NVL(V.NRPESMESAVENDA, 1) AS NRPESMESAVENDA, V.CDCLIENTE, V.CDVENDEDOR, V.DTVENDA,
                                            V.DTENTRVENDA, V.CDOPERADOR, V.CDSUPERVISOR, V.VRREPIQUEVENDA, NVL(V.DTABERVENDA, V.DTVENDA) AS DTABERVENDA, 
                                            C.CDPRODTAXASERV, C.CDPRODTAXAENTR, V.VRTXSEVENDA, NVL(V.IDSTATUSNFCE, 'A') AS IDSTATUSNFCE, V.IDORIGEMVENDA
                                       FROM VENDA V
                                       JOIN ( SELECT C.CDCAIXA, L.CDLOJA, L.CDFILIAL, L.NRORG, L.CDPRODTAXASERV, L.CDPRODTAXAENTR
                                                FROM CAIXA C
                                                JOIN ( SELECT L.CDLOJA, F.CDFILIAL, F.NRORG, L.CDPRODTAXASERV, L.CDPRODTAXAENTR
                                                         FROM LOJA L
                                                         JOIN ( SELECT F.CDFILIAL, F.NRORG
                                                                  FROM FILIAL F
                                                                 WHERE F.CDFILIAL = C_FILIAL.CDFILIAL
                                                                   AND F.NRORG = C_FILIAL.NRORG ) F
                                                           ON L.CDFILIAL = F.CDFILIAL
                                                          AND L.NRORG = F.NRORG ) L
                                                  ON C.CDFILIAL = L.CDFILIAL
                                                 AND C.CDLOJA = L.CDLOJA
                                                 AND C.NRORG = L.NRORG ) C
                                 ON V.CDCAIXA = C.CDCAIXA
                                AND V.CDLOJA = C.CDLOJA
                                AND V.CDFILIAL = C.CDFILIAL
                                AND V.NRORG = C.NRORG
                                WHERE V.DTENTRVENDA BETWEEN V_PERIODOINI AND V_PERIODO ) V
                              ON I.CDFILIAL = V.CDFILIAL
                             AND I.CDCAIXA = V.CDCAIXA
                             AND I.NRSEQVENDA = V.NRSEQVENDA
                             AND I.NRORG = V.NRORG 
                           GROUP BY V.IDSITUVENDA, V.NRSEQVENDA, V.CDCAIXA, V.CDLOJA, V.CDFILIAL, 
                                    V.NRORG, V.NRPESMESAVENDA, V.CDCLIENTE, V.CDVENDEDOR, V.DTVENDA,
                                    V.DTENTRVENDA, V.CDOPERADOR, V.CDSUPERVISOR, V.VRREPIQUEVENDA, V.DTABERVENDA, 
                                    V.CDPRODTAXASERV, V.CDPRODTAXAENTR, V.VRTXSEVENDA, V.IDSTATUSNFCE, V.IDORIGEMVENDA
                           ORDER BY V.NRSEQVENDA) LOOP
              V_ITVENDACAN := 0;
              V_TXENTREGA := 0;
              V_TXSERV := 0;              
              BEGIN
              /*Verifica se existe itens cancelados para adicionar o valor a venda*/
                SELECT NVL(SUM(TRUNC((QTPRODVENDC * (VRUNITVENDC + NVL(VRUNITVENCLC,0))) - NVL(VRDESITVENDC,0) + NVL(VRACRITVENDC,0),2)), 0)
                  INTO V_ITVENDACAN 
                  FROM ITVENDACAN
                 WHERE CDFILIAL = C_VENDA.CDFILIAL
                   AND CDCAIXA  = C_VENDA.CDCAIXA
                   AND NRSEQVENDA = C_VENDA.NRSEQVENDA;
              EXCEPTION
                WHEN NO_DATA_FOUND THEN
                     V_ITVENDACAN := 0;
              END;
              IF (C_VENDA.TIPOVENDA = 'DLV') THEN
              /*Verifica se a venda e delivery e calcula a taxa de entrega caso tenha parametrizado*/
                BEGIN
                  SELECT NVL(SUM(TRUNC(NVL(I.VRACRITVEND,0),2)) - SUM(TRUNC(NVL(V.VRREPIQUEVENDA,0),2)), 0) 
                    INTO V_TXENTREGA
                    FROM ITEMVENDA I, LOJA L, VENDA V
                    WHERE I.CDPRODUTO   = L.CDPRODTAXAENTR
                      AND I.NRORG = L.NRORG                   AND V.NRSEQVENDA  = I.NRSEQVENDA
                      AND V.CDCAIXA     = I.CDCAIXA           AND V.CDFILIAL    = I.CDFILIAL
                      AND V.NRORG = I.NRORG                   AND L.CDLOJA      = V.CDLOJA
                      AND L.CDFILIAL    = V.CDFILIAL          AND L.NRORG = V.NRORG
                      AND V.CDFILIAL = C_VENDA.CDFILIAL       AND V.NRSEQVENDA = C_VENDA.NRSEQVENDA
                      AND L.IDTRATTAXAENTR = 'P'              AND SUBSTR(V.IDORIGEMVENDA, 0, 3) = 'DLV'    
                      AND I.NRORG = C_VENDA.NRORG             AND V.IDSITUVENDA = 'O'
                      AND L.CDFILIAL||L.CDLOJA = C_VENDA.CDFILIAL||C_VENDA.CDLOJA
                      AND L.CDFILIAL||L.CDLOJA||V.CDCAIXA = C_VENDA.CDFILIAL||C_VENDA.CDLOJA||C_VENDA.CDCAIXA
                      AND V.DTENTRVENDA BETWEEN V_PERIODOINI AND V_PERIODO;
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       V_TXENTREGA := 0;
                END;
              ELSE
                /* se a venda não e delivery e calcula a taxa de servico caso tenha parametrizado*/
                BEGIN
                  SELECT NVL(SUM(TRUNC(NVL(I.VRACRITVEND,0),2)), 0) 
                    INTO V_TXSERV
                    FROM ITEMVENDA I, LOJA L, VENDA V
                   WHERE I.CDPRODUTO   = L.CDPRODTAXASERV  AND I.NRORG = L.NRORG
                     AND V.NRSEQVENDA  = I.NRSEQVENDA      AND V.CDCAIXA     = I.CDCAIXA
                     AND V.CDFILIAL    = I.CDFILIAL        AND L.NRORG = V.NRORG
                     AND V.NRORG       = I.NRORG           AND L.CDLOJA      = V.CDLOJA          
                     AND L.CDFILIAL    = V.CDFILIAL        AND V.CDFILIAL = C_VENDA.CDFILIAL
                     AND L.IDCOMISVENDA IN ('S', 'G')      AND L.IDTRATTAXASERV = 'P'
                     AND L.CDFILIAL||L.CDLOJA = C_VENDA.CDFILIAL||C_VENDA.CDLOJA
                     AND L.CDFILIAL||L.CDLOJA||V.CDCAIXA = C_VENDA.CDFILIAL||C_VENDA.CDLOJA||C_VENDA.CDCAIXA
                     AND V.DTENTRVENDA BETWEEN V_PERIODOINI AND V_PERIODO
                     AND V.IDSITUVENDA = 'O'               AND SUBSTR(V.IDORIGEMVENDA, 0, 3) <> 'DLV'
                     AND I.NRORG = C_VENDA.NRORG           AND V.NRSEQVENDA = C_VENDA.NRSEQVENDA;
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       V_TXSERV := 0;
                END;
              END IF;
              V_COUNT := 0;
              /*se a venda ja se encontra na tabela ela nao e reinserida*/
              BEGIN 
                SELECT COUNT(NRSEQVENDA) INTO V_COUNT 
                  FROM VENDASINT 
                 WHERE CDFILIAL = C_VENDA.CDFILIAL 
                   AND CDLOJA = C_VENDA.CDLOJA 
                   AND CDCAIXA = C_VENDA.CDCAIXA 
                   AND NRSEQVENDA = C_VENDA.NRSEQVENDA;
              EXCEPTION 
                WHEN NO_DATA_FOUND THEN
                     V_COUNT := 0;
              END;
              IF (V_COUNT = 0) THEN
                V_SEQUENCIAL := SEQUENCIAL_VENDASINT;
                INSERT INTO VENDASINT  (NRVENDASINT, CDFILIAL, CDLOJA, CDCAIXA, NRSEQVENDA, CDCLIENTE, CDVENDEDOR, DTVENDA, DTENTRVENDA, 
                                        NRORG, CDOPERADOR, CDSUPERVISOR, NRPESMESAVENDA, VRREPIQUEVENDA, DTABERVENDA, IDSITUVENDA, 
                                        QTPRODVENDTOT, VRTOTVEND, VRTXENTREGATOT, VRTXSERVTOT, VRTOTDESITVEND, VRTOTACRITVEND, 
                                        VRTOTITVENDACAN, IDSTATUSNFCE, IDORIGEMVENDA )
                VALUES (V_SEQUENCIAL, C_VENDA.CDFILIAL, C_VENDA.CDLOJA, C_VENDA.CDCAIXA, C_VENDA.NRSEQVENDA, C_VENDA.CDCLIENTE, C_VENDA.CDVENDEDOR, 
                        C_VENDA.DTVENDA, C_VENDA.DTENTRVENDA, C_VENDA.NRORG, C_VENDA.CDOPERADOR, C_VENDA.CDSUPERVISOR, C_VENDA.NRPESMESAVENDA, C_VENDA.VRREPIQUEVENDA, 
                        C_VENDA.DTABERVENDA, C_VENDA.IDSITUVENDA, C_VENDA.QTPRODVENDTOT, C_VENDA.VRTOTVEND, V_TXENTREGA, V_TXSERV, 
                        C_VENDA.VRTOTDESITVEND, C_VENDA.VRTOTACRITVEND, V_ITVENDACAN, C_VENDA.IDSTATUSNFCE, C_VENDA.IDORIGEMVENDA);
                COMMITA;
              END IF;
            END LOOP;
        END IF;
    END LOOP;
  COMMIT;
END;

/