IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[UFD_ITENS_COMPRADO]') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [DBO].[UFD_ITENS_COMPRADO]

GO
/*
=======================================================================================
'JOB.................:  
'FUNÇÃO..............: 
'OBJETIVO............: FILTRAR OS PRODUTOS MAIS VENDO NO PERIODO
'TABELAS.............: 
'DT MODIFICAÇÃO......: 25/01/2021
'MODIFICAÇÃO.........: 
'=======================================================================================
TAG #A001 - ALTERAÇÃO DO CODIGO  
TAG #I001 - INCLUSÃO DE NOVO CODIGO 
*/ 



CREATE FUNCTION [DBO].[UFD_ITENS_COMPRADO]  (@CD_CLI INT , @DTINICIOINSERCAO DATE, @DTFIMINSERCAO DATE )    
  RETURNS @RS TABLE (
	CD_FILIAL	INT,
	DT_VD		DATE,
	CODIGO		INT,  
	PRODUTO		VARCHAR(150),  
	QTDE		MONEY,  
	VALOR		MONEY)  
AS  
BEGIN  
	INSERT INTO @RS   
		 SELECT    
				 
				V.CD_FILIAL,
				V.DT_VD,
				I.CD_PROD,   
				LEFT(P.DS_PROD,150),
				ISNULL(SUM(I.QT_IT),0) AS QTDE,   
				ISNULL(SUM( CASE WHEN I.QT_IT > 0 THEN 
										I.TOT_IT / QT_IT ELSE 0 END),0) AS VALOR   
			FROM PDV_VD V 
				INNER JOIN PDV_VD_IT I ON  V.CD_VD		= I.CD_VD 
									  AND  V.CD_FILIAL	= I.CD_FILIAL 
									  AND  V.CD_EMP		= I.CD_EMP  
									  AND  V.DT_VD		= I.DT_VD 
				INNER JOIN EST_PROD P ON I.CD_EMP	= P.CD_EMP 
									 AND I.CD_PROD = P.CD_PROD
				INNER JOIN RC_CLI  ON V.CD_EMP = RC_CLI.CD_EMP 
								  AND V.CD_CLI = RC_CLI.CD_CLI
			WHERE I.CD_EMP	= 1
			  AND V.CD_CLI	= @CD_CLI
			  AND P.FLAG_NAO_APARECER_ESTATISTICA_COMPRA_CLIENTE = 0  
			  AND V.ST_VD = 0   
			   AND I.DT_VD BETWEEN @DTINICIOINSERCAO AND @DTFIMINSERCAO  
			  AND RC_CLI.FLAG_NAO_APARECER_ESTATISTICA_COMPRA=0
			GROUP BY  
				V.CD_FILIAL,
				V.DT_VD,
				I.CD_PROD,
				P.DS_PROD,
				V.CD_CLI
			ORDER BY  V.DT_VD desc
		RETURN   
END 

		  

		  