
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PROD_EXP_INESTRA_PLUS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PROD_EXP_INESTRA_PLUS]
GO


CREATE FUNCTION  [dbo].[UFD_EST_PROD_EXP_INESTRA_PLUS](@CD_EMP			INT,                      
													  @CD_FILIAL		NVARCHAR(100),    
													  @DT_INI			DATETIME,    
													  @DT_FIM			DATETIME)    
    
             
RETURNS @EST_PROD_EXP TABLE ( NR_DOC			NVARCHAR(100),    
								CGC				NVARCHAR(20),    
								CD_FILIAL		INT,    
								DT_VD			DATETIME,    
								CD_PROD			INT,    
								CD_MEDICO_REC	INT,    
								UF_DOC			NVARCHAR(2),    
								CD_TP_DOC		NVARCHAR(10),    
								CD_VD			INT,    
								CD_VEND			INT ,
								QT_PROD			MONEY)    
    
AS    
BEGIN    
    
DECLARE @EST_PROD TABLE(     
    CD_EMP              INT,    
    CD_FILIAL           INT,    
    DT_VD               DATETIME,    
    CD_VD               INT,    
    CD_PROD				INT,    
    CD_MEDICO_REC		INT,     
    CD_VEND				INT,    
    QT_PROD				MONEY)          
  
DECLARE @TP_ORIGEM_DADOS INT  
  
SET @TP_ORIGEM_DADOS = ISNULL((SELECT TOP 1 VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'FLAG_ORIGEM_DADOS_INESTRA'),0)  
  
IF @TP_ORIGEM_DADOS = 0   
 BEGIN     
	INSERT INTO @EST_PROD    
	SELECT         
		CD_EMP,     
		CD_FILIAL,     
		DT_CAD,     
		NR_ORC,     
		CD_PROD,      
		CD_MEDICO_REC,     
		CD_VEND,     
		SUM(QT_PROD)    
	FROM             
		EST_ORC_GLB_MEDICO    
	WHERE    
		EST_ORC_GLB_MEDICO.CD_EMP = @CD_EMP AND     
		EST_ORC_GLB_MEDICO.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))    
		AND EST_ORC_GLB_MEDICO.DT_CAD BETWEEN @DT_INI AND @DT_FIM    
	GROUP BY    
		CD_EMP,     
		CD_FILIAL,     
		DT_CAD,     
		NR_ORC,     
		CD_PROD,      
		CD_MEDICO_REC,     
		CD_VEND     
 END  
ELSE  
 BEGIN  
	INSERT INTO @EST_PROD    
	SELECT         
		CD_EMP,  
		CD_FILIAL,  
		DT_VD AS DT_CAD,  
		CD_VD AS NR_ORC,  
		CD_PROD,  
		CD_MEDICO_REC,  
		CD_VEND,  
		SUM(QT_IT) AS QT_IT   
	FROM             
		V_PDV_VD_IT    
	WHERE    
		CD_EMP = @CD_EMP   
		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))    
		AND DT_VD BETWEEN @DT_INI AND @DT_FIM    
		AND CD_MEDICO_REC > 0  
	GROUP BY    
		CD_EMP,  
		CD_FILIAL,  
		DT_VD,  
		CD_VD,  
		CD_PROD,  
		CD_MEDICO_REC,  
		CD_VEND  
 END  
    
	INSERT INTO @EST_PROD_EXP    
	SELECT     
		GLB_MEDICOS.NR_DOC,    
		PRC_FILIAL.CGC,    
		PRC_FILIAL.CD_FILIAL,    
		EST_PROD.DT_VD,    
		EST_PROD.CD_PROD,    
		CD_MEDICO_REC,    
		GLB_MEDICOS.UF_DOC,    
		CD_TP_DOC = 
		
		CASE WHEN GLB_MEDICOS.CD_TP_DOC = 0 THEN    
			'NÃO DEFINIDO'    
		WHEN GLB_MEDICOS.CD_TP_DOC = 1 THEN    
			'CRM'    
		WHEN GLB_MEDICOS.CD_TP_DOC = 2 THEN    
			'CRO' 
		WHEN GLB_MEDICOS.CD_TP_DOC = 3 THEN    
			'CRMV' 
		WHEN GLB_MEDICOS.CD_TP_DOC = 4 THEN    
			'CRN'   
		WHEN GLB_MEDICOS.CD_TP_DOC = 5 THEN    
			'CRP'    
		ELSE    
			'OUTROS'    
		END,     
     
		EST_PROD.CD_VD,     
		EST_PROD.CD_VEND ,
		QT_PROD
	FROM       
		@EST_PROD EST_PROD,    
		GLB_MEDICOS,    
		PRC_FILIAL    
	WHERE       
		EST_PROD.CD_EMP = GLB_MEDICOS.CD_EMP AND    
		EST_PROD.CD_MEDICO_REC = GLB_MEDICOS.CD_CTR AND    
		EST_PROD.CD_EMP = PRC_FILIAL.CD_EMP AND    
		EST_PROD.CD_FILIAL = PRC_FILIAL.CD_FILIAL     
	    
RETURN    
END 

GO


