create or replace PROCEDURE VENDA_HORA
  (
	V_CDFILIAL IN VARCHAR2,  V_NRORG NUMBER, V_DTVENDA DATE DEFAULT (SYSDATE)
  )
AS
  V_VENDAHORA       FLOAT;
  V_VENDAACUMULADA  FLOAT;
  V_CONTHORA        NUMBER;
BEGIN

	DELETE
	  FROM VENDAHORATOTAL
     WHERE TRUNC(DTVENDA) = TRUNC(V_DTVENDA) AND CDFILIAL = V_CDFILIAL;

	V_CONTHORA := 0;
	V_VENDAACUMULADA := 0;

	WHILE V_CONTHORA <= 23 LOOP

		V_VENDAHORA := 0;

		FOR C_VENDA IN
		(	SELECT CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, NRORG,
			   SUM(TOTALVEND) TOTALVEND,
			   TRUNC(SUM(TICKETMEDIO) / SUM(DECODE(VRQTVENDACONC, 0, 1, VRQTVENDACONC)), 2) 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 TO_NUMBER(TO_CHAR(DTVENDA, 'HH24')) = V_CONTHORA
									AND V.DTENTRVENDA = V_DTVENDA
									AND V.IDSITUVENDA IN ('O', 'C')
									ORDER BY V.NRSEQVENDA) X LEFT JOIN ITEMVENDA IT
																	ON X.CDFILIAL = IT.CDFILIAL
																   AND X.CDCAIXA    = IT.CDCAIXA
																   AND X.NRSEQVENDA = IT.NRSEQVENDA) PARCIAL
				  GROUP BY PARCIAL.CDFILIAL, PARCIAL.NMFILIAL, PARCIAL.IDORIGEMVENDA, PARCIAL.DTENTRVENDA, PARCIAL.NRORG) FINAL
			GROUP BY CDFILIAL, NMFILIAL, IDORIGEMVENDA, DTENTRVENDA, NRORG
			ORDER BY CDFILIAL) LOOP

				V_VENDAHORA := C_VENDA.TOTALVEND;
				V_VENDAACUMULADA := V_VENDAACUMULADA + C_VENDA.TOTALVEND;

		END LOOP;

		INSERT INTO VENDAHORATOTAL
		  (CDFILIAL, DTVENDA, HORAVENDA, VRTOTAL, VRTOTALACUMULADO, NRORG )
		  VALUES
		  (V_CDFILIAL, V_DTVENDA, V_CONTHORA,
			NVL(V_VENDAHORA, 0.00), NVL(V_VENDAACUMULADA, 0.00), V_NRORG);

		V_CONTHORA := V_CONTHORA + 1;
	END LOOP;

	COMMIT;
END;
/
