create or replace PROCEDURE ESTIMA_META_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_MESINICIO IN NUMBER DEFAULT 1,
    V_MESFINAL IN NUMBER DEFAULT 12
  )
AS    
  V_CDANO           NUMBER;  
  V_CONTADOR        NUMBER;  
  V_CONTHORA        NUMBER;  
  V_CDMES           NUMBER;
  V_VNDREALIZADAS   FLOAT;
  V_CDMETAPREV      VARCHAR2(100);  
BEGIN

    IF (P_CDANO < TO_NUMBER(TO_CHAR(sysdate, 'YYYY')-1))  THEN
		V_CDANO := TO_NUMBER(TO_CHAR(sysdate, 'YYYY'));
	ELSE
		V_CDANO := P_CDANO;
	END IF;

    DELETE FROM METAHORA WHERE CDFILIAL = V_CDFILIAL AND CDANO = V_CDANO AND CDMES BETWEEN V_MESINICIO AND V_MESFINAL;

	DELETE FROM METAMODALIDADEDIA WHERE CDFILIAL = V_CDFILIAL AND CDANO = V_CDANO AND CDMES BETWEEN V_MESINICIO AND V_MESFINAL;

	DELETE FROM METADIA WHERE CDFILIAL = V_CDFILIAL AND CDANO = V_CDANO AND CDMES BETWEEN V_MESINICIO AND V_MESFINAL;

	DELETE FROM METAMES WHERE CDFILIAL = V_CDFILIAL AND CDANO = V_CDANO AND CDMES BETWEEN V_MESINICIO AND V_MESFINAL;

    FOR C_FERIADO IN
		(SELECT DSFERIFILI, DTFERIFILI FROM FERIFILI WHERE CDFILIAL = V_CDFILIAL AND NRORG = V_NRORG AND TO_NUMBER(TO_CHAR(DTFERIFILI, 'YYYY')) =  V_CDANO) LOOP

		BEGIN
				SELECT COUNT(CDFILIAL) INTO V_CONTADOR FROM METADIAESPECIAL
				WHERE CDFILIAL = V_CDFILIAL AND NRORG = V_NRORG AND TRUNC(DATAESP) = TRUNC(C_FERIADO.DTFERIFILI);
		EXCEPTION
		WHEN NO_DATA_FOUND THEN
			V_CONTADOR := 0;
		END;

		IF V_CONTADOR = 0 THEN

			INSERT INTO METADIAESPECIAL (CDFILIAL, DATAESP, PERCCORRECAO, DSMETAESP, NRORG)
			VALUES (V_CDFILIAL, C_FERIADO.DTFERIFILI, V_PERCACRES, C_FERIADO.DSFERIFILI, V_NRORG);

		END IF;
	/* END FOR */
	END LOOP;

    NOVO_CODIGO('METAPREVISAOHIST', V_CDMETAPREV, '1', V_NRORG);    

    INSERT INTO METAPREVISAOHIST (CDMETAPREV, CDFILIAL, IDSTATUS, DTINICIO, DTFINAL, CDANO, CDMESINICIAL, CDMESFINAL, VRPERCACRES, NRORGINCLUSAO, CDOPERINCLUSAO)
    VALUES (SUBSTR(V_CDMETAPREV, -10, 10), V_CDFILIAL, 'M', V_DTINI, V_DTFIN, P_CDANO, V_MESINICIO, V_MESFINAL, V_PERCACRES, V_NRORG, V_CDOPERADOR);


    FOR C_VENDA IN (
        SELECT TOTAL.MES, SUM(VRMEDIO) AS VRMEDIO FROM (
        SELECT M.MES, M.NRSEMANA, M.NRDIA, VHISTORICO.VRMEDIO FROM (
        SELECT TO_CHAR(DTDATA, 'MM') MES, 
        CASE 
            WHEN TO_CHAR(DTDATA, 'DD') <= 7 THEN '1' 
            WHEN TO_CHAR(DTDATA, 'DD') > 7 AND TO_CHAR(DTDATA, 'DD') <= 14 THEN '2' 
            WHEN TO_CHAR(DTDATA, 'DD') > 14 AND TO_CHAR(DTDATA, 'DD') <= 21 THEN '3' 
            WHEN TO_CHAR(DTDATA, 'DD') > 21 AND TO_CHAR(DTDATA, 'DD') <= 28 THEN '4' 
            WHEN TO_CHAR(DTDATA, 'DD') > 28 THEN '5' 
        END AS NRSEMANA, TO_CHAR(DTDATA, 'D') AS NRDIA
        FROM DATA
        WHERE TO_CHAR(DTDATA, 'YYYY') = V_CDANO
         AND TO_CHAR(DTDATA, 'MM') BETWEEN V_MESINICIO AND V_MESFINAL
        ) M 
        LEFT JOIN (SELECT W.CDFILIAL, W.NMFILIAL, W.NRDIA, W.NRSEMANA, W.QTD, W.TOTALVEND, W.TICKETMEDIO, W.VRQTVENDACONC, W.VRQTVENDACANC, TRUNC((W.TOTALVEND / W.QTD), 2) AS VRMEDIO
        FROM (
        SELECT Z.CDFILIAL, Z.NMFILIAL,Z.NRDIA, Z.NRSEMANA , COUNT(Z.NRDIA) AS QTD,  SUM(Z.TOTALVEND) as TOTALVEND, SUM(Z.TICKETMEDIO) as TICKETMEDIO , SUM(Z.VRQTVENDACONC) as VRQTVENDACONC, SUM(Z.VRQTVENDACANC) as VRQTVENDACANC 
        FROM (
        SELECT T.CDFILIAL, T.NMFILIAL, T.NRDIA, T.CDMES, T.NRSEMANA, SUM(T.TOTALVEND) as TOTALVEND, SUM(T.TICKETMEDIO) as TICKETMEDIO , SUM(T.VRQTVENDACONC) as VRQTVENDACONC, SUM(T.VRQTVENDACANC) as VRQTVENDACANC 
        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, 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.IDORIGEMVENDA, 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, 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, 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.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.HORAVEND, PARCIAL.NRORG
              ORDER BY 1, 2, 3) FINAL
        GROUP BY CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, HORAVEND, NRORG, TO_CHAR(DTENTRVENDA,'D'), TO_CHAR(DTENTRVENDA,'MM')
        ORDER BY CDFILIAL, DTENTRVENDA) T GROUP BY T.CDFILIAL, T.NMFILIAL, T.NRDIA, T.CDMES, T.NRSEMANA
        ORDER BY T.CDMES, T.NRSEMANA, T.NRDIA ) Z GROUP BY Z.CDFILIAL, Z.NMFILIAL, Z.NRDIA, Z.NRSEMANA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        ORDER BY Z.NRDIA, Z.NRSEMANA ) W) VHISTORICO ON (VHISTORICO.NRSEMANA = M.NRSEMANA AND VHISTORICO.NRDIA = M.NRDIA)
        GROUP BY M.MES, M.NRSEMANA, M.NRDIA, VHISTORICO.VRMEDIO
        ORDER BY M.MES, M.NRSEMANA, M.NRDIA) TOTAL GROUP BY TOTAL.MES ORDER BY TOTAL.MES) LOOP

        V_VNDREALIZADAS := C_VENDA.VRMEDIO + ((V_PERCACRES/100)*C_VENDA.VRMEDIO);

        INSERT INTO METAMES (CDFILIAL, CDMES, CDANO, VRMES, VRSEM, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO, VRMETAESPECIAL)
        VALUES (V_CDFILIAL, C_VENDA.MES, V_CDANO, V_VNDREALIZADAS, C_VENDA.VRMEDIO, V_NRORG, TO_CHAR(sysdate, 'DD/MM/YYYY'), V_NRORG, V_CDOPERADOR, 0);               

    END LOOP;

    FOR C_VENDA_DIA IN (SELECT Z.MES, Z.NRDIA, Z.VRTMES, SUM(Z.VRSEMDIA) AS VRSEMDIA,SUM(((Z.VRSEMDIA * 100) / Z.VRTMES)) AS VRPERCDIASEMANA FROM (
            SELECT X.*, SUM((X.VRSEMDIA)) OVER (PARTITION BY MES) VRTMES FROM (
            SELECT CAL.MES, CAL.NRDIA, CAL.NRSEMANA, HIST.VRQTVENDACONC, HIST.VRQTVENDACANC, HIST.TOTALVEND, 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
            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.TOTALVEND) 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, 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.IDORIGEMVENDA, 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, 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, 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.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.HORAVEND, PARCIAL.NRORG
                  ORDER BY 1, 2, 3) FINAL
            GROUP BY CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, HORAVEND, 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
            ORDER BY Z.MES, Z.NRDIA ) LOOP

        INSERT INTO METADIA (CDFILIAL, NRDIA, CDMES, CDANO, VRPERCDIA, VRTICKETDIA, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
        VALUES (V_CDFILIAL, C_VENDA_DIA.NRDIA, C_VENDA_DIA.MES,V_CDANO, C_VENDA_DIA.VRPERCDIASEMANA, 0, V_NRORG, TO_CHAR(sysdate, 'DD/MM/YYYY'), V_NRORG, V_CDOPERADOR);               

    END LOOP;

    FOR k IN V_MESINICIO..V_MESFINAL LOOP
        FOR j IN 1..7 LOOP
            V_CONTHORA := 0;
            WHILE V_CONTHORA <= 23 LOOP

                INSERT INTO METAHORA (CDFILIAL, CDHORA, NRDIA, CDMES, CDANO, VRPERCHORA, NRORG, DTINCLUSAO, NRORGINCLUSAO, CDOPERINCLUSAO)
                VALUES (V_CDFILIAL, V_CONTHORA, j, k, V_CDANO, 0, V_NRORG, TO_CHAR(sysdate, 'DD/MM/YYYY'), V_NRORG, V_CDOPERADOR);

                V_CONTHORA := V_CONTHORA + 1;
            END LOOP;            

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'CMD', j, k, V_CDANO, 0, V_NRORG);

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'TAA', j, k, V_CDANO, 0, V_NRORG);

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'DLV', j, k, V_CDANO, 0, V_NRORG);

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'BAL', j, k, V_CDANO, 0, V_NRORG);

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'MES', j, k, V_CDANO, 0, V_NRORG);

        INSERT INTO METAMODALIDADEDIA (CDFILIAL, IDMODALIDADE, NRDIA, CDMES, CDANO, VRPERCMODALIDADE, NRORG)
        VALUES (V_CDFILIAL, 'OUT', j, k, V_CDANO, 0, V_NRORG);

        /* END FOR */
        END LOOP;
    /* END FOR */
    END LOOP;    

   FOR C_VENDA_DIA_HORA IN (SELECT Z.MES AS CDMES, Z.NRDIA, Z.VRTDIAMES, Z.HORAVEND, SUM(Z.VRSEMDIA) AS VRSEMDIA,SUM(((Z.VRSEMDIA * 100) / Z.VRTDIAMES)) AS VRPERCDIASEMANA FROM (
            SELECT X.*, SUM((X.VRSEMDIA)) OVER (PARTITION BY MES, NRDIA) VRTDIAMES FROM (
            SELECT CAL.MES, CAL.NRDIA, CAL.NRSEMANA, 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
            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.IDORIGEMVENDA, 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, 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, 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.IDORIGEMVENDA, 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, 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, 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.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.HORAVEND, PARCIAL.NRORG
                  ORDER BY 1, 2, 3) FINAL
            GROUP BY CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, HORAVEND, 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.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
            ORDER BY Z.MES, Z.NRDIA, Z.HORAVEND ) LOOP

            /* Atualiza METAHORA */
			UPDATE METAHORA SET VRPERCHORA = C_VENDA_DIA_HORA.VRPERCDIASEMANA
			WHERE CDFILIAL = V_CDFILIAL AND NRDIA = C_VENDA_DIA_HORA.NRDIA AND CDMES = C_VENDA_DIA_HORA.CDMES AND CDANO = V_CDANO AND CDHORA = C_VENDA_DIA_HORA.HORAVEND AND NRORG = V_NRORG;

    END LOOP;

   FOR C_VENDA_MODALIDADEDIA IN (SELECT Z.MES AS CDMES, Z.NRDIA, Z.VRTMES, NVL(Z.IDORIGEMVENDA, 'OUT') AS IDORIGEMVENDA, 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, NRDIA) 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.IDORIGEMVENDA
            ORDER BY Z.MES, Z.NRDIA ) LOOP

			/* Atualiza a estimativa por modalidade  */
			UPDATE METAMODALIDADEDIA SET VRPERCMODALIDADE = C_VENDA_MODALIDADEDIA.VRPERCDIASEMANA
			WHERE CDFILIAL = V_CDFILIAL AND NRDIA = C_VENDA_MODALIDADEDIA.NRDIA AND CDMES = C_VENDA_MODALIDADEDIA.CDMES AND CDANO = V_CDANO AND IDMODALIDADE = C_VENDA_MODALIDADEDIA.IDORIGEMVENDA AND NRORG = V_NRORG;            
    END LOOP;     

    COMMIT;
    CALCULA_METAS_RM_SAZONALIDADE(V_CDFILIAL, V_NRORG, V_PERCACRES, V_PERIODO, V_DTINI, V_DTFIN, V_CDANO, V_CDOPERADOR, V_CDMETAPREV, V_MESINICIO, V_MESFINAL);

END;