create or replace PROCEDURE CALCULA_METAS_RM_SAZONALIDADE
   (
	V_CDFILIAL IN VARCHAR2,  V_NRORG IN NUMBER, V_PERCACRES IN FLOAT,
	V_PERIODO IN VARCHAR2 DEFAULT 'N', V_DTINI IN VARCHAR2 DEFAULT NULL, V_DTFIN IN VARCHAR2 DEFAULT NULL,
	P_CDANO IN NUMBER DEFAULT 0,
    V_CDOPERADOR IN VARCHAR2 DEFAULT NULL,
    V_CDMETAPREV IN VARCHAR2,
    V_MESINICIO IN NUMBER DEFAULT 1,
    V_MESFINAL IN NUMBER DEFAULT 12
   )
AS
  TYPE tNome IS VARRAY(7) OF VARCHAR(25);
  V_META		    NUMBER;
  V_CDMES           NUMBER;
  V_CDANO           NUMBER;
  V_VALORMETA       FLOAT;
  V_VALORTCKMED     FLOAT;
  V_METAACUM        FLOAT;
  V_CONTHORA        NUMBER;
  V_NRDIA           NUMBER;
  V_DIAESPECIAL     FLOAT;
  V_VALORTEMP       FLOAT;
  V_DIASDASEMANA	NUMBER;
BEGIN

    IF (P_CDANO = 0)  THEN
		V_CDANO := TO_NUMBER(TO_CHAR(sysdate, 'YYYY'));
	ELSE
		V_CDANO := P_CDANO;
	END IF;
    
    /* Limpa as tabelas de Meta Calculada */
    DELETE FROM METADIAREAL
    WHERE CDFILIAL = V_CDFILIAL AND NRORG = V_NRORG AND CDANO = V_CDANO AND TO_CHAR(DATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL;

    DELETE FROM METAMODALIDADEREAL
    WHERE CDFILIAL = V_CDFILIAL AND NRORG = V_NRORG AND TO_CHAR(DATA, 'YYYY') = V_CDANO AND TO_CHAR(DATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL;
    
    DELETE FROM METAHORAREAL
    WHERE CDFILIAL = V_CDFILIAL AND NRORG = V_NRORG AND TO_CHAR(DATA, 'YYYY') = V_CDANO AND TO_CHAR(DATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL;
    
    
    FOR C_VENDA_DATA IN (SELECT CAL.MES, CAL.NRDIA, CAL.NRSEMANA, CAL.DTDATA, NVL(HIST.VRQTVENDACONC, 0) AS VRQTVENDACONC, 
        NVL(HIST.VRQTVENDACANC, 0) AS VRQTVENDACANC, NVL(HIST.TOTALVEND, 0) AS TOTALVEND,
            NVL(TRUNC((HIST.TOTALVEND/HIST.QTDVENDA), 2),0) AS VRSEMDIA , NVL(HIST.TICKETMEDIO, 0) AS TICKETMEDIO
            FROM ( SELECT TO_CHAR(DT.DTDATA, 'MM') MES, 
            CASE 
                WHEN TO_CHAR(DT.DTDATA, 'DD') <= 7 THEN '1' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 7 AND TO_CHAR(DT.DTDATA, 'DD') <= 14 THEN '2' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 14 AND TO_CHAR(DT.DTDATA, 'DD') <= 21 THEN '3' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 21 AND TO_CHAR(DT.DTDATA, 'DD') <= 28 THEN '4' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 28 THEN '5' 
            END AS NRSEMANA, TO_CHAR(DT.DTDATA, 'D') AS NRDIA, TO_CHAR(DT.DTDATA, 'DD/MM/YYYY') AS DTDATA
            FROM DATA DT
            WHERE TO_CHAR(DT.DTDATA, 'YYYY') = V_CDANO
            AND TO_CHAR(DTDATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL
            ORDER BY TO_CHAR(DT.DTDATA, 'MM'), NRSEMANA, NRDIA ) CAL
            LEFT JOIN (SELECT TDSEM.NRSEMANA, TDSEM.NRDIA, TDSEM.VRQTVENDACONC, TDSEM.VRQTVENDACANC, SUM(TDSEM.TOTALVEND) AS TOTALVEND,
                COUNT(TDSEM.TOTALVEND) AS QTDVENDA, SUM(TDSEM.TICKETMEDIO) AS TICKETMEDIO
                FROM ( SELECT CDFILIAL, NMFILIAL, DTENTRVENDA, TO_CHAR(DTENTRVENDA,'D') AS NRDIA, TO_CHAR(DTENTRVENDA,'MM') AS CDMES ,
                     CASE 
                        WHEN TO_CHAR(DTENTRVENDA, 'DD') <= 7 THEN '1' 
                        WHEN TO_CHAR(DTENTRVENDA, 'DD') > 7 AND TO_CHAR(DTENTRVENDA, 'DD') <= 14 THEN '2' 
                        WHEN TO_CHAR(DTENTRVENDA, 'DD') > 14 AND TO_CHAR(DTENTRVENDA, 'DD') <= 21 THEN '3' 
                        WHEN TO_CHAR(DTENTRVENDA, 'DD') > 21 AND TO_CHAR(DTENTRVENDA, 'DD') <= 28 THEN '4' 
                        WHEN TO_CHAR(DTENTRVENDA, 'DD') > 28 THEN '5' 
                     END AS NRSEMANA,
                     NRORG,
                     NVL(SUM(TOTALVEND), 0) TOTALVEND, 
                     NVL(TRUNC(SUM(TICKETMEDIO) / SUM(DECODE(VRQTVENDACONC, 0, 1, VRQTVENDACONC)), 2), 0) TICKETMEDIO,
                     SUM(VRQTVENDACONC) VRQTVENDACONC, SUM(VRQTVENDACANC) VRQTVENDACANC
                FROM  ( SELECT PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.DTENTRVENDA, PARCIAL.NRORG,
                                          SUM(DECODE(PARCIAL.IDSITUVENDA, 'C', 0, PARCIAL.TOTALVEND)) TOTALVEND, 
                                          TRUNC(SUM(PARCIAL.TICKETMEDIO)) AS TICKETMEDIO,
                                          SUM(COUNT(DISTINCT VRQTVENDACONC)) OVER (PARTITION BY CDFILIAL) VRQTVENDACONC,
                                          SUM(COUNT(DISTINCT VRQTVENDACANC)) OVER (PARTITION BY CDFILIAL) VRQTVENDACANC
                                     FROM ( SELECT X.CDFILIAL, X.NMFILIAL, X.DTENTRVENDA, X.NRORG,
                      DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TOTALVEND,
                      DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TICKETMEDIO,
                      X.IDSITUVENDA, 
                      DECODE(IDSITUVENDA, 'O', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACONC,
                      DECODE(IDSITUVENDA, 'C', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACANC
                FROM (SELECT DISTINCT V.CDFILIAL, V.CDCAIXA, V.NRSEQVENDA, FLC.NMFILIAL, V.IDORIGEMVENDA, V.NRORG, V.DTENTRVENDA, V.IDSITUVENDA
                         FROM VENDA V, (SELECT DISTINCT CDFILIAL, NMFILIAL, CDLOJA, CDCAIXA FROM V_FILIALLOJACAIXA WHERE CDFILIAL = V_CDFILIAL) FLC
                         WHERE V.CDCAIXA   = FLC.CDCAIXA
                         AND V.CDLOJA    = FLC.CDLOJA
                         AND V.CDFILIAL  = FLC.CDFILIAL                                      
                         AND ((V_CDANO-1) = TO_NUMBER(TO_CHAR(V.DTENTRVENDA, 'YYYY')) OR V_PERIODO = 'S')
                         AND (V.DTENTRVENDA BETWEEN TO_DATE(V_DTINI, 'DD/MM/YYYY') AND TO_DATE(V_DTFIN, 'DD/MM/YYYY') OR V_PERIODO = 'N')
                         AND V.IDSITUVENDA IN ('O', 'C')
                         ORDER BY 1, 2, 3) X
                LEFT JOIN ITEMVENDA IT ON (X.CDFILIAL = IT.CDFILIAL AND X.CDCAIXA = IT.CDCAIXA AND X.NRORG = IT.NRORG AND X.NRSEQVENDA = IT.NRSEQVENDA)
                ORDER BY 1, 2, 3) PARCIAL
                GROUP BY PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.DTENTRVENDA, PARCIAL.NRORG
                ORDER BY 1, 2, 3) FINAL
                GROUP BY CDFILIAL, NMFILIAL, DTENTRVENDA, NRORG, TO_CHAR(DTENTRVENDA,'D'), TO_CHAR(DTENTRVENDA,'MM')
                ORDER BY CDFILIAL, DTENTRVENDA ) TDSEM
                GROUP BY TDSEM.NRSEMANA, TDSEM.NRDIA, TDSEM.VRQTVENDACONC, TDSEM.VRQTVENDACANC
                ORDER BY TDSEM.NRSEMANA, TDSEM.NRDIA) HIST ON (CAL.NRSEMANA = HIST.NRSEMANA AND CAL.NRDIA = HIST.NRDIA)
                ORDER BY CAL.MES, CAL.DTDATA ) LOOP               
    
    V_VALORMETA := C_VENDA_DATA.VRSEMDIA + ((V_PERCACRES/100)*C_VENDA_DATA.VRSEMDIA);
        	
    INSERT INTO METADIAREAL (CDFILIAL, DATA, CDANO, VRMETADIA, VRTCKMED, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
    VALUES (V_CDFILIAL, C_VENDA_DATA.DTDATA, V_CDANO, V_VALORMETA, C_VENDA_DATA.TICKETMEDIO, V_NRORG, TO_CHAR(sysdate, 'DD/MM/YYYY'), V_NRORG, V_CDOPERADOR);

    END LOOP;         

    FOR C_VENDA_DATAMODALIDADE IN (SELECT Z.MES AS CDMES, Z.NRDIA, Z.VRTMES, Z.NRSEMANA, NVL(Z.IDORIGEMVENDA, 'OUT') AS IDORIGEMVENDA, Z.DTDATA, NVL(SUM(Z.VRSEMDIA), 0) AS VRSEMDIA,NVL(SUM(((Z.VRSEMDIA * 100) / Z.VRTMES)), 0) AS VRPERCDIASEMANA FROM (
            SELECT X.*, SUM((X.VRSEMDIA)) OVER (PARTITION BY MES) VRTMES FROM (
            SELECT CAL.MES, CAL.NRDIA, CAL.NRSEMANA, CAL.DTDATA,HIST.VRQTVENDACONC, HIST.VRQTVENDACANC, HIST.TOTALVEND, HIST.IDORIGEMVENDA, TRUNC((HIST.TOTALVEND/HIST.QTDVENDA), 2) AS VRSEMDIA  FROM (
            SELECT TO_CHAR(DT.DTDATA, 'MM') MES, 
            CASE 
                WHEN TO_CHAR(DT.DTDATA, 'DD') <= 7 THEN '1' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 7 AND TO_CHAR(DT.DTDATA, 'DD') <= 14 THEN '2' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 14 AND TO_CHAR(DT.DTDATA, 'DD') <= 21 THEN '3' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 21 AND TO_CHAR(DT.DTDATA, 'DD') <= 28 THEN '4' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 28 THEN '5' 
            END AS NRSEMANA, TO_CHAR(DT.DTDATA, 'D') AS NRDIA, DTDATA
            FROM DATA DT
            WHERE TO_CHAR(DT.DTDATA, 'YYYY') = V_CDANO
            AND TO_CHAR(DTDATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL
            ORDER BY TO_CHAR(DT.DTDATA, 'MM'), NRSEMANA, NRDIA ) CAL
            LEFT JOIN (SELECT TDSEM.CDFILIAL, TDSEM.NMFILIAL, TDSEM.IDORIGEMVENDA, TDSEM.NRDIA, TDSEM.NRSEMANA, TDSEM.VRQTVENDACONC, 
            TDSEM.VRQTVENDACANC, 
            SUM(TDSEM.TOTALVEND) AS TOTALVEND ,
            COUNT(TDSEM.IDORIGEMVENDA) AS QTDVENDA 
            FROM (SELECT TOTSEM.CDFILIAL, TOTSEM.NMFILIAL, TOTSEM.IDORIGEMVENDA, TOTSEM.NRDIA, TOTSEM.CDMES, TOTSEM.NRSEMANA,  
            TOTSEM.VRQTVENDACONC, TOTSEM.VRQTVENDACANC, SUM(TOTSEM.TOTALVEND) AS TOTALVEND, 
            TRUNC((SUM(TOTSEM.TOTALVEND)/TOTSEM.VRQTVENDACONC),2) AS TICKETMEDIO
            FROM (SELECT CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, TO_CHAR(DTENTRVENDA,'D') AS NRDIA, TO_CHAR(DTENTRVENDA,'MM') AS CDMES ,
                 CASE 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') <= 7 THEN '1' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 7 AND TO_CHAR(DTENTRVENDA, 'DD') <= 14 THEN '2' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 14 AND TO_CHAR(DTENTRVENDA, 'DD') <= 21 THEN '3' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 21 AND TO_CHAR(DTENTRVENDA, 'DD') <= 28 THEN '4' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 28 THEN '5' 
                 END AS NRSEMANA,
                 NRORG,
                 NVL(SUM(TOTALVEND), 0) TOTALVEND, 
                 NVL(TRUNC(SUM(TICKETMEDIO) / SUM(DECODE(VRQTVENDACONC, 0, 1, VRQTVENDACONC)), 2), 0) TICKETMEDIO,
                 SUM(VRQTVENDACONC) VRQTVENDACONC, SUM(VRQTVENDACANC) VRQTVENDACANC
            FROM  (SELECT PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.NRORG,
                          SUM(DECODE(PARCIAL.IDSITUVENDA, 'C', 0, PARCIAL.TOTALVEND)) TOTALVEND, 
                          TRUNC(SUM(PARCIAL.TICKETMEDIO)) AS TICKETMEDIO,
                          SUM(COUNT(DISTINCT VRQTVENDACONC)) OVER (PARTITION BY CDFILIAL, IDORIGEMVENDA) VRQTVENDACONC,
                          SUM(COUNT(DISTINCT VRQTVENDACANC)) OVER (PARTITION BY CDFILIAL, IDORIGEMVENDA) VRQTVENDACANC
                     FROM (SELECT X.CDFILIAL, X.NMFILIAL, CASE X.IDORIGEMVENDA WHEN 'BAL_AUT' THEN 'TAA' ELSE SUBSTR(X.IDORIGEMVENDA, 0, 3) END AS IDORIGEMVENDA, X.DTENTRVENDA, X.NRORG,
                                  DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TOTALVEND,
                                  DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TICKETMEDIO,
                                  X.IDSITUVENDA, 
                                  DECODE(IDSITUVENDA, 'O', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACONC,
                                  DECODE(IDSITUVENDA, 'C', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACANC
                          FROM (SELECT DISTINCT V.CDFILIAL, V.CDCAIXA, V.NRSEQVENDA, FLC.NMFILIAL, 
                                          V.IDORIGEMVENDA, V.NRORG, V.DTENTRVENDA, V.IDSITUVENDA
                                     FROM VENDA V, (SELECT DISTINCT CDFILIAL, NMFILIAL, CDLOJA, CDCAIXA
                                                      FROM V_FILIALLOJACAIXA WHERE CDFILIAL = V_CDFILIAL) FLC
                                    WHERE V.CDCAIXA   = FLC.CDCAIXA
                                      AND V.CDLOJA    = FLC.CDLOJA
                                      AND V.CDFILIAL  = FLC.CDFILIAL                                      
                                      AND ((V_CDANO-1) = TO_NUMBER(TO_CHAR(V.DTENTRVENDA, 'YYYY')) OR V_PERIODO = 'S')
                                      AND (V.DTENTRVENDA BETWEEN TO_DATE(V_DTINI, 'DD/MM/YYYY') AND TO_DATE(V_DTFIN, 'DD/MM/YYYY') OR V_PERIODO = 'N')
                                      AND V.IDSITUVENDA IN ('O', 'C')
                                      ORDER BY 1, 2, 3) X
                            LEFT JOIN ITEMVENDA IT
                            ON X.CDFILIAL = IT.CDFILIAL
                          AND X.CDCAIXA    = IT.CDCAIXA
                          AND X.NRORG      = IT.NRORG
                          AND X.NRSEQVENDA = IT.NRSEQVENDA
                         ORDER BY 1, 2, 3) PARCIAL
                  GROUP BY PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.NRORG
                  ORDER BY 1, 2, 3) FINAL
            GROUP BY CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, NRORG, TO_CHAR(DTENTRVENDA,'D'), TO_CHAR(DTENTRVENDA,'MM')
            ORDER BY CDFILIAL, DTENTRVENDA ) TOTSEM 
            GROUP BY TOTSEM.CDFILIAL, TOTSEM.NMFILIAL, TOTSEM.IDORIGEMVENDA, TOTSEM.CDMES, TOTSEM.NRSEMANA, TOTSEM.NRDIA, TOTSEM.VRQTVENDACONC, TOTSEM.VRQTVENDACANC
            ORDER BY TOTSEM.CDMES, TOTSEM.NRSEMANA, TOTSEM.NRDIA) TDSEM
            GROUP BY TDSEM.CDFILIAL, TDSEM.NMFILIAL, TDSEM.IDORIGEMVENDA, TDSEM.NRSEMANA, TDSEM.NRDIA, TDSEM.VRQTVENDACONC, 
            TDSEM.VRQTVENDACANC
            ORDER BY TDSEM.NRSEMANA, TDSEM.NRDIA ) HIST ON (CAL.NRSEMANA = HIST.NRSEMANA AND CAL.NRDIA = HIST.NRDIA)) X ) Z
            GROUP BY Z.MES, Z.NRDIA, Z.VRTMES, Z.NRSEMANA,Z.IDORIGEMVENDA, Z.DTDATA
            ORDER BY Z.MES, Z.DTDATA ) LOOP               

    V_VALORMETA := C_VENDA_DATAMODALIDADE.VRSEMDIA + ((V_PERCACRES/100)*C_VENDA_DATAMODALIDADE.VRSEMDIA);

    INSERT INTO METAMODALIDADEREAL (CDFILIAL, DATA, IDMODALIDADE, VRMETAMODALIDADE, NRORG)
    VALUES (V_CDFILIAL, C_VENDA_DATAMODALIDADE.DTDATA, C_VENDA_DATAMODALIDADE.IDORIGEMVENDA, V_VALORMETA, V_NRORG);


    END LOOP;   
    
    FOR CALENDARIO IN  ( SELECT  DT.DTDATA
    FROM DATA DT
    WHERE TO_CHAR(DT.DTDATA, 'YYYY') = V_CDANO
    AND TO_CHAR(DTDATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL
    ORDER BY TO_CHAR(DT.DTDATA, 'MM'), DT.DTDATA ) LOOP 
    
         V_CONTHORA := 0;
         WHILE V_CONTHORA <= 23 LOOP
    
            INSERT INTO METAHORAREAL (CDFILIAL, HORA, DATA, VRMETAHORAACUM, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
            VALUES (V_CDFILIAL, V_CONTHORA, CALENDARIO.DTDATA, 0, V_NRORG, TO_CHAR(sysdate, 'DD/MM/YYYY'), V_NRORG, V_CDOPERADOR);
    
            V_CONTHORA := V_CONTHORA + 1;
        END LOOP; 
    
    END LOOP;   
       
    
    FOR C_VENDA_DATAHORA IN ( SELECT Z.MES AS CDMES, Z.NRDIA, Z.VRTDIAMES, Z.HORAVEND, Z.DTDATA, SUM(Z.VRSEMDIA) AS VRSEMDIA,SUM(((Z.VRSEMDIA * 100) / Z.VRTDIAMES)) AS VRPERCDIASEMANA FROM (
            SELECT X.*, SUM((X.VRSEMDIA)) OVER (PARTITION BY MES) VRTDIAMES FROM (
            SELECT CAL.MES, CAL.NRDIA, CAL.NRSEMANA, CAL.DTDATA, HIST.VRQTVENDACONC, HIST.VRQTVENDACANC, HIST.TOTALVEND, HIST.HORAVEND, TRUNC((HIST.TOTALVEND/HIST.QTDVENDA), 2) AS VRSEMDIA  FROM (
            SELECT TO_CHAR(DT.DTDATA, 'MM') MES, 
            CASE 
                WHEN TO_CHAR(DT.DTDATA, 'DD') <= 7 THEN '1' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 7 AND TO_CHAR(DT.DTDATA, 'DD') <= 14 THEN '2' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 14 AND TO_CHAR(DT.DTDATA, 'DD') <= 21 THEN '3' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 21 AND TO_CHAR(DT.DTDATA, 'DD') <= 28 THEN '4' 
                WHEN TO_CHAR(DT.DTDATA, 'DD') > 28 THEN '5' 
            END AS NRSEMANA, TO_CHAR(DT.DTDATA, 'D') AS NRDIA, DT.DTDATA
            FROM DATA DT
            WHERE TO_CHAR(DT.DTDATA, 'YYYY') = V_CDANO
            AND TO_CHAR(DTDATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL
            ORDER BY TO_CHAR(DT.DTDATA, 'MM'), NRSEMANA, NRDIA ) CAL
            LEFT JOIN (SELECT TDSEM.CDFILIAL, TDSEM.NMFILIAL, TDSEM.HORAVEND, TDSEM.NRDIA, TDSEM.NRSEMANA, TDSEM.VRQTVENDACONC, 
            TDSEM.VRQTVENDACANC, 
            SUM(TDSEM.TOTALVEND) AS TOTALVEND ,
            COUNT(TDSEM.TOTALVEND) AS QTDVENDA 
            FROM (SELECT TOTSEM.CDFILIAL, TOTSEM.NMFILIAL, TOTSEM.NRDIA, TOTSEM.CDMES, TOTSEM.NRSEMANA, TOTSEM.HORAVEND, 
            TOTSEM.VRQTVENDACONC, TOTSEM.VRQTVENDACANC, SUM(TOTSEM.TOTALVEND) AS TOTALVEND, 
            TRUNC((SUM(TOTSEM.TOTALVEND)/TOTSEM.VRQTVENDACONC),2) AS TICKETMEDIO
            FROM (SELECT CDFILIAL, NMFILIAL, DTENTRVENDA, TO_CHAR(DTENTRVENDA,'D') AS NRDIA, TO_CHAR(DTENTRVENDA,'MM') AS CDMES ,
                 CASE 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') <= 7 THEN '1' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 7 AND TO_CHAR(DTENTRVENDA, 'DD') <= 14 THEN '2' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 14 AND TO_CHAR(DTENTRVENDA, 'DD') <= 21 THEN '3' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 21 AND TO_CHAR(DTENTRVENDA, 'DD') <= 28 THEN '4' 
                    WHEN TO_CHAR(DTENTRVENDA, 'DD') > 28 THEN '5' 
                 END AS NRSEMANA,
                 NRORG,
                 NVL(SUM(TOTALVEND), 0) TOTALVEND, HORAVEND,
                 NVL(TRUNC(SUM(TICKETMEDIO) / SUM(DECODE(VRQTVENDACONC, 0, 1, VRQTVENDACONC)), 2), 0) TICKETMEDIO,
                 SUM(VRQTVENDACONC) VRQTVENDACONC, SUM(VRQTVENDACANC) VRQTVENDACANC
            FROM  (SELECT PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.DTENTRVENDA, PARCIAL.NRORG,
                          SUM(DECODE(PARCIAL.IDSITUVENDA, 'C', 0, PARCIAL.TOTALVEND)) TOTALVEND, PARCIAL.HORAVEND,
                          TRUNC(SUM(PARCIAL.TICKETMEDIO)) AS TICKETMEDIO,
                          SUM(COUNT(DISTINCT VRQTVENDACONC)) OVER (PARTITION BY CDFILIAL) VRQTVENDACONC,
                          SUM(COUNT(DISTINCT VRQTVENDACANC)) OVER (PARTITION BY CDFILIAL) VRQTVENDACANC
                     FROM (SELECT X.CDFILIAL, X.NMFILIAL, X.DTENTRVENDA, X.NRORG,
                                  DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TOTALVEND,
                                  DECODE(IDSITUVENDA, 'C', 0, TRUNC(IT.QTPRODVEND*(IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0)),2) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS TICKETMEDIO,
                                  X.IDSITUVENDA, X.HORAVEND,
                                  DECODE(IDSITUVENDA, 'O', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACONC,
                                  DECODE(IDSITUVENDA, 'C', X.CDFILIAL||X.CDCAIXA||X.NRSEQVENDA) VRQTVENDACANC
                          FROM (SELECT DISTINCT V.CDFILIAL, V.CDCAIXA, V.NRSEQVENDA, FLC.NMFILIAL, TO_NUMBER(TO_CHAR(DTVENDA, 'HH24')) HORAVEND,
                                          V.IDORIGEMVENDA, V.NRORG, V.DTENTRVENDA, V.IDSITUVENDA
                                     FROM VENDA V, (SELECT DISTINCT CDFILIAL, NMFILIAL, CDLOJA, CDCAIXA
                                                      FROM V_FILIALLOJACAIXA WHERE CDFILIAL = V_CDFILIAL) FLC
                                    WHERE V.CDCAIXA   = FLC.CDCAIXA
                                      AND V.CDLOJA    = FLC.CDLOJA
                                      AND V.CDFILIAL  = FLC.CDFILIAL                                      
                                      AND ((V_CDANO-1) = TO_NUMBER(TO_CHAR(V.DTENTRVENDA, 'YYYY')) OR V_PERIODO = 'S')
                                      AND (V.DTENTRVENDA BETWEEN TO_DATE(V_DTINI, 'DD/MM/YYYY') AND TO_DATE(V_DTFIN, 'DD/MM/YYYY') OR V_PERIODO = 'N')
                                      AND V.IDSITUVENDA IN ('O', 'C')
                                      ORDER BY 1, 2, 3) X
                            LEFT JOIN ITEMVENDA IT
                            ON X.CDFILIAL = IT.CDFILIAL
                          AND X.CDCAIXA    = IT.CDCAIXA
                          AND X.NRORG      = IT.NRORG
                          AND X.NRSEQVENDA = IT.NRSEQVENDA
                         ORDER BY 1, 2, 3) PARCIAL
                  GROUP BY PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.DTENTRVENDA, PARCIAL.HORAVEND, PARCIAL.NRORG
                  ORDER BY 1, 2, 3) FINAL
            GROUP BY CDFILIAL, NMFILIAL, DTENTRVENDA, HORAVEND, NRORG, TO_CHAR(DTENTRVENDA,'D'), TO_CHAR(DTENTRVENDA,'MM')
            ORDER BY CDFILIAL, DTENTRVENDA ) TOTSEM 
            GROUP BY TOTSEM.CDFILIAL, TOTSEM.NMFILIAL, TOTSEM.CDMES, TOTSEM.NRSEMANA, TOTSEM.NRDIA, TOTSEM.HORAVEND, TOTSEM.VRQTVENDACONC, TOTSEM.VRQTVENDACANC
            ORDER BY TOTSEM.CDMES, TOTSEM.NRSEMANA, TOTSEM.NRDIA) TDSEM
            GROUP BY TDSEM.CDFILIAL, TDSEM.NMFILIAL, TDSEM.HORAVEND, TDSEM.NRSEMANA, TDSEM.NRDIA, TDSEM.VRQTVENDACONC, 
            TDSEM.VRQTVENDACANC
            ORDER BY TDSEM.NRSEMANA, TDSEM.NRDIA) HIST ON (CAL.NRSEMANA = HIST.NRSEMANA AND CAL.NRDIA = HIST.NRDIA)) X ) Z
            GROUP BY Z.MES, Z.NRDIA, Z.VRTDIAMES, Z.HORAVEND, Z.DTDATA
            ORDER BY Z.MES, Z.DTDATA , Z.HORAVEND ) LOOP               

    V_VALORMETA := C_VENDA_DATAHORA.VRSEMDIA + ((V_PERCACRES/100)*C_VENDA_DATAHORA.VRSEMDIA);
     
    UPDATE METAHORAREAL SET VRMETAHORAACUM = V_VALORMETA
    WHERE CDFILIAL = V_CDFILIAL AND TO_CHAR(DATA, 'DD/MM/YYYY') = C_VENDA_DATAHORA.DTDATA AND HORA = C_VENDA_DATAHORA.HORAVEND AND NRORG = V_NRORG;
    

    END LOOP;   
    

    UPDATE METAPREVISAOHIST 
     SET IDSTATUS = 'P',  CDOPERULTATU = V_CDOPERADOR, DTULTATU = TO_CHAR(sysdate, 'DD/MM/YYYY'), NRORGULTATU = V_NRORG
     WHERE CDMETAPREV = SUBSTR(V_CDMETAPREV, -10, 10)       
     AND CDFILIAL = V_CDFILIAL
     AND CDANO = P_CDANO
     AND IDSTATUS = 'M';

	COMMIT;
END;