IF OBJECT_ID('P_EST_PED_ELTR_DEMANDA_X_COMPRA') IS NOT NULL
	DROP PROCEDURE P_EST_PED_ELTR_DEMANDA_X_COMPRA

GO

--Versao 3231 
CREATE PROCEDURE [dbo].[P_EST_PED_ELTR_DEMANDA_X_COMPRA] ( @CD_EMP			INT, 
													@CD_FILIAL			VARCHAR(2500),
													@DT_INI				DATETIME, 
													@DT_FIM				DATETIME,
													@CD_FABRIC			VARCHAR(MAX), 
													@CD_ARV_MERC_LINHA	VARCHAR(MAX))
 	 
WITH ENCRYPTION AS

SET NOCOUNT ON;
  
BEGIN

	SELECT
		CD_EMP,
		CD_FILIAL 
	 INTO #PRC_FILIAL  
	 FROM PRC_FILIAL
	 WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) ;
	 	
	SELECT 
		CD_FABRIC,
		NM_FABRIC
	INTO #EST_PROD_FABRIC
	FROM EST_PROD_FABRIC
	WHERE 
		CD_FABRIC  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FABRIC)) ;

	SELECT 
		CD_EMP,
		CD_ARV_MERC_LINHA,
		DS_ARV_MERC_LINHA
	INTO 
		#EST_ARV_MERC_LINHA
	FROM 
		EST_ARV_MERC_LINHA
	WHERE 
		CD_ARV_MERC_LINHA  IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_ARV_MERC_LINHA))  
		AND CD_EMP=@CD_EMP; 

	SELECT 
		E.CD_EMP,
		E.CD_PED_SOL,
		E.CD_FIL_ENTR CD_FILIAL,
		E.DT_PED,
		I.CD_PROD,
		GLB_USU.CD_USU,
		GLB_USU.NM_USU,
		P.DS_PROD, 
		FAB.NM_FABRIC,
		L.DS_ARV_MERC_LINHA,
		SUM (I.QT_IT * I.VLR_PROD) VLR_IT_DEMANDA, 
		SUM(I.QT_IT) QT_IT_DEMANDA,
		0 FLAG_PHARMALINK,
		CASE WHEN E.TP_REQ = 0 THEN 'Eletronico'
		when E.TP_REQ = 1 THEN   'Sugestao - agenda'
		when E.TP_REQ = 2 THEN   'Unificado'
		when E.TP_REQ = 3 THEN   'Solicitacao - Encomenda'
		ELSE 'Não definido' END TP_DEMANDA
		INTO #TBL_DEMANDA
	FROM 
		EST_PED_ELTR E
		INNER JOIN GLB_USU ON
			E.CD_USU = GLB_USU.CD_USU
		INNER JOIN #PRC_FILIAL F ON 
			E.CD_EMP = F.CD_EMP AND
			E.CD_FIL_ENTR = F.CD_FILIAL
		INNER JOIN EST_PED_ELTR_IT I ON
			E.CD_EMP = I.CD_EMP AND
			E.CD_PED_SOL = I.CD_PED_SOL    
		
		INNER JOIN EST_PROD P ON
			I.CD_EMP  = P.CD_EMP AND
			I.CD_PROD = P.CD_PROD
		
		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA  ARV ON
			P.CD_EMP  =  ARV.CD_EMP AND
			P.CD_PROD = ARV.CD_PROD   
		
		INNER JOIN #EST_PROD_FABRIC FAB  ON
			P.CD_FABRIC = FAB.CD_FABRIC   
		
		INNER JOIN #EST_ARV_MERC_LINHA L  ON
			ARV.CD_EMP = L.CD_EMP AND
			ARV.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA   
		
	WHERE
		E.CD_EMP=@CD_EMP
 		AND E.DT_PED BETWEEN @DT_INI AND @DT_FIM 
	 GROUP BY 
		E.CD_EMP,
		E.CD_PED_SOL,
		E.CD_FIL_ENTR,
		E.TP_REQ,
		E.DT_PED,
		I.CD_PROD,
		P.DS_PROD, 
		FAB.NM_FABRIC,
		L.DS_ARV_MERC_LINHA,
		GLB_USU.CD_USU,
		GLB_USU.NM_USU
	HAVING SUM(I.QT_IT) > 0	;
	
	
	-- DEMANDAS ENVIADAS PARA O PHARMALINK
	UPDATE  D 
	SET
		FLAG_PHARMALINK =  isnull(E.ENV_PHLINK,0)
	FROM 
		#TBL_DEMANDA D
		INNER JOIN EST_PED_ELTR_FORN E ON 
		D.CD_EMP	 = E.CD_EMP AND  
		D.CD_PED_SOL = E.CD_PED_SOL 
	WHERE
		D.CD_EMP=@CD_EMP; 
		 
    set @DT_INI =dateadd(day, -10, @DT_INI)
    set @DT_FIM =dateadd(day, 10, @DT_FIM);

		   
	SELECT  
		EP.CD_PED_SOL,
		P.CD_FILIAL,
		I.CD_PROD,
		I.QT_EMB,
		SUM(I.QT_IT) QT_CP,
		SUM(I.QT_IT * I.VLR_IT) VLR_IT_CP ,
		SUM(i.VLR_DESC)	VLR_DESC
	INTO 
		#TBL_COMPRA
	FROM
		EST_PED_CP P
		INNER JOIN EST_PED_CP_IT I ON 
			P.CD_EMP  =I.CD_EMP AND 
			P.CD_PED  = I.CD_PED  AND 
			P.CD_FILIAL = I.CD_FILIAL 
		
		INNER JOIN 	EST_PED_ELTR_FORN_PED_CP EP ON
			P.CD_EMP	= EP.CD_EMP AND 
			P.CD_PED	= EP.CD_PED  AND 
			P.CD_FILIAL = EP.CD_FIL_ENTR   
		
		INNER JOIN #TBL_DEMANDA D ON  
			EP.CD_PED_SOL	= D.CD_PED_SOL		AND 
			EP.CD_FIL_ENTR	= D.CD_FILIAL  AND 
			I.CD_PROD		= D.CD_PROD
		  	
	WHERE
		p.CD_EMP=@CD_EMP
 		AND p.DT_EMI BETWEEN @DT_INI AND @DT_FIM 
	GROUP BY 
		EP.CD_PED_SOL,
		P.CD_FILIAL,
		I.CD_PROD,
		I.QT_EMB; 
		
	  
	SELECT 
		D.CD_PED_SOL,	
		D.DT_PED DT_DEMANDA,
		D.CD_FILIAL,	
		D.CD_PROD,
		D.DS_PROD,
		D.DS_ARV_MERC_LINHA,
		D.NM_FABRIC,  
		S.DS_SIGLA,
		P.MEDIAF,
		CONVERT( MONEY,ROUND( (P.MEDIAF/30.000) * P.EMAX ,0)) EMAX,
		CONVERT( MONEY,ROUND( (P.MEDIAF/30.000) * P.ESEG ,0)) ESEG,
		P.QTDE_FACEAMENTO,
		P.QTDE_FINANCIADO,
		P.QTDE_LIMITADOR,
		P.QT_EST QT_ESTOQUE_ATUAL,
		D.QT_IT_DEMANDA,
		D.VLR_IT_DEMANDA,	 
		ISNULL(C.QT_CP,0) QT_CP,
		ISNULL(C.QT_EMB,1) AS QT_EMB,
		ISNULL(C.VLR_IT_CP,0) VLR_IT_CP,
		ISNULL(C.VLR_DESC,0) VLR_DESC_CP,
		CONVERT( MONEY,(ISNULL(C.QT_CP,0)/D.QT_IT_DEMANDA) * 100.000) AS PERC_ATEND,
		CASE WHEN ISNULL(D.FLAG_PHARMALINK,0) = 0 THEN 'NAO' ELSE 'SIM' END FLAG_PHARMALINK,
		CASE WHEN CONVERT( MONEY,(ISNULL(C.QT_CP,0)/D.QT_IT_DEMANDA) * 100.000) = 0 THEN 'NAO' ELSE 'SIM' END ATENDIDO,
		TP_DEMANDA
	FROM	
		#TBL_DEMANDA D
		LEFT JOIN #TBL_COMPRA C ON
			D.CD_PED_SOL = C.CD_PED_SOL  AND
			D.CD_FILIAL = C.CD_FILIAL  AND 
			D.CD_PROD = C.CD_PROD
		
		INNER JOIN EST_PROD_CPL P ON 
			D.CD_EMP	= P.CD_EMP AND
			D.CD_PROD	= P.CD_PROD AND 
			D.CD_FILIAL = P.CD_FILIAL
		
		INNER JOIN EST_PROD_CURVA_SIGLA S   ON 
			P.CD_SIGLA = S.CD_SIGLA 
 
		
END

GO


