IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'P_EST_PROD_AGENDA_COMPRA_PONTO_ENCOMENDA') AND type in (N'P', N'PC'))
begin
	DROP PROCEDURE P_EST_PROD_AGENDA_COMPRA_PONTO_ENCOMENDA 
end
GO
--P_EST_PROD_AGENDA_COMPRA_PONTO_ENCOMENDA  1,16
CREATE PROCEDURE P_EST_PROD_AGENDA_COMPRA_PONTO_ENCOMENDA (@CD_EMP INT, @CD_COMPRADOR INT) AS
--DECLARE @CD_EMP       INT = 1 , @CD_COMPRADOR INT = 16;

/***********************************************************************************************/
-- Criacao das tabelas temporarias
/***********************************************************************************************/

SET NOCOUNT ON;

IF OBJECT_ID('TEMPDB.DBO.#TMP_PROD') IS NOT NULL
	BEGIN
		DROP TABLE #TMP_PROD;
	END;

CREATE TABLE #TMP_PROD (CD_EMP    TINYINT
							, CD_PROD   INT
							, CD_AGENDA INT
							);

IF OBJECT_ID('TEMPDB.DBO.#TMP_AGENDAS') IS NOT NULL
	BEGIN
		DROP TABLE #TMP_AGENDAS;
	END;

CREATE TABLE #TMP_AGENDAS (CD_EMP    TINYINT , CD_AGENDA INT  );
 	
	
/***********************************************************************************************/
-- Carga dos produtos que participam da agenda.
/***********************************************************************************************/

INSERT INTO #TMP_AGENDAS
	   SELECT CD_EMP
			, CD_CTR_AGENDA 
	   FROM EST_PROD_AGENDA_COMPRA
	   WHERE CD_COMPRADOR = @CD_COMPRADOR
			 AND CD_EMP = @CD_EMP
			 AND DT_AGENDAMENTO_DEFAULT> GETDATE(); 

IF NOT EXISTS ( SELECT * FROM #TMP_AGENDAS )
	RETURN;

INSERT INTO #TMP_PROD
	   SELECT A.CD_EMP
			, PA.CD_PROD
			, A.CD_AGENDA
	   FROM #TMP_AGENDAS A
			CROSS APPLY ( SELECT * FROM dbo.UFD_EST_PROD_AGENDA_COMPRA_PRODUTOS(A.CD_EMP, A.CD_AGENDA) ) PA;

/***********************************************************************************************/
IF  OBJECT_ID('TEMPDB.DBO.#TMP_FILIAL') IS NOT NULL
BEGIN
	DROP TABLE #TMP_FILIAL
END
CREATE TABLE #TMP_FILIAL (CD_EMP INT, CD_FILIAL INT, CD_AGENDA INT )


	INSERT INTO #TMP_FILIAL
	select 
		F.CD_EMP,
		F.CD_FILIAL,
		TA.CD_AGENDA
	from 
		EST_PROD_AGENDA_COMPRA A
		JOIN EST_PROD_AGENDA_COMPRA_PRC_BANDEIRA B ON
		A.CD_CTR_AGENDA =B.CD_CTR_AGENDA
		JOIN PRC_BANDEIRA_PRC_FILIAL F ON
		B.CD_BANDEIRA = F.CD_BANDEIRA 
		JOIN #TMP_AGENDAS TA ON 
		A.CD_CTR_AGENDA = TA.CD_AGENDA ;

	INSERT INTO #TMP_FILIAL
	SELECT 
		F.CD_EMP,
		F.CD_FILIAL,
		TA.CD_AGENDA
	FROM 
		EST_PROD_AGENDA_COMPRA A
		JOIN EST_PROD_AGENDA_COMPRA_PRC_FILIAL F ON
		A.CD_CTR_AGENDA =F.CD_CTR_AGENDA 
		JOIN #TMP_AGENDAS TA ON 
		A.CD_CTR_AGENDA = TA.CD_AGENDA  
		LEFT JOIN #TMP_FILIAL T ON 
		F.CD_FILIAL  = T.CD_FILIAL 
	WHERE
		T.CD_FILIAL IS NULL  ;
		  
Print 'Carregou as filiais da agenda - Time ' + convert(varchar, getdate(),108);

/***********************************************************************************************/


SELECT 
	  PONTO_ENCOMENDA.CD_AGENDA
	 , PONTO_ENCOMENDA.DS_AGENDA
	 , COUNT(PONTO_ENCOMENDA.CD_PROD) AS ITENS
	 , SUM(PONTO_ENCOMENDA.SUGESTAO) SUGESTAO
	 , SUM(PONTO_ENCOMENDA.VLR) TOTAL
	 , PONTO_ENCOMENDA.DT_AGENDA
FROM
(
	SELECT 
			C.CD_PROD
		 , A.CD_CTR_AGENDA CD_AGENDA
		 , A.DS_AGENDAMENTO DS_AGENDA
		 , A.DT_AGENDAMENTO_DEFAULT DT_AGENDA
		 , DATEDIFF(D, (GETDATE()), (DATEADD(D, A.LEAD_TIME, A.DT_AGENDAMENTO_DEFAULT))) AS CPP
		 , CASE
			   WHEN C.MEDIAF > 0
			   THEN ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (C.MEDIAF)) * 30), 0)
			   ELSE ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (0.0001)) * 30), 0)
		   END AS COB
		 , C.MEDIAF
		 , CONVERT( MONEY, ROUND(C.MEDIAF * (((A.PARAM_EST - (28. / A.FREQ_REV) + (DATEDIFF(DAY, (GETDATE()), A.DT_AGENDAMENTO_DEFAULT))) + A.LEAD_TIME - (CASE WHEN C.MEDIAF > 0 THEN ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (C.MEDIAF)) * 30), 0)
																																								ELSE ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (0.0001)) * 30), 0) END)) / 30.0), 0)) AS SUGESTAO
		 , CONVERT( MONEY, ROUND(C.MEDIAF * (((A.PARAM_EST - (28. / A.FREQ_REV) + (DATEDIFF(DAY, (GETDATE()), A.DT_AGENDAMENTO_DEFAULT))) + A.LEAD_TIME - (CASE WHEN C.MEDIAF > 0 THEN ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (C.MEDIAF)) * 30), 0)
																																								ELSE ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (0.0001)) * 30), 0) END)) / 30.0), 0)) * ((CASE WHEN ISNULL(C.VLR_FABRICA, 0) > 0 THEN ISNULL(C.VLR_FABRICA, 0)
																																																																				ELSE C.VLR_COMPRA_GRP
																																																																			END) * (1 + (CASE WHEN ISNULL(C.PERC_DESC_FABRICA, 0) > 0 THEN  ISNULL(C.PERC_DESC_FABRICA, 0) / 100.00
																																																																					ELSE 0 END))) VLR
	FROM 
		 V_EST_PROD_CPL_ESTOQUE_PEDIDO C	 

		 INNER JOIN #TMP_PROD P ON 
			C.CD_EMP	= P.CD_EMP AND 
			C.CD_PROD	= P.CD_PROD
			
		 INNER JOIN #TMP_FILIAL F ON 
			C.CD_EMP	= F.CD_EMP AND 
			C.CD_FILIAL	= F.CD_FILIAL

			AND P.CD_AGENDA = F.CD_AGENDA

		INNER JOIN EST_PROD_AGENDA_COMPRA A ON 
			P.CD_EMP	= A.CD_EMP AND 
			P.CD_AGENDA	= A.CD_CTR_AGENDA

	WHERE 
		C.CD_EMP = 1
		AND A.FREQ_REV > 0
		AND C.COMPRA_POR_SUPRIMENTO IN(1, 2)
		AND C.STS_PROD = 0
		AND C.STS_PROD_FILIAL = 0 
		AND A.DT_AGENDAMENTO_DEFAULT> GETDATE()
		AND (ROUND(C.MEDIAF * (((A.PARAM_EST - (28. / A.FREQ_REV) + (DATEDIFF(D, (GETDATE()), A.DT_AGENDAMENTO_DEFAULT))) + A.LEAD_TIME - (CASE WHEN C.MEDIAF > 0 THEN ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (C.MEDIAF)) * 30), 0)
																										ELSE ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / (0.0001)) * 30), 0)
																									END)) / 30.0), 0)) > 0
		AND A.CD_COMPRADOR = @CD_COMPRADOR
		AND DATEDIFF(D, (GETDATE()), (DATEADD(D, A.LEAD_TIME, A.DT_AGENDAMENTO_DEFAULT))) > CASE WHEN C.MEDIAF > 0 THEN ROUND((((C.QT_EST_EM_TERC + C.QT_EST + C.QT_PEND_ENT) / ( C.MEDIAF)) * 30), 0) 
																						ELSE ROUND((((C.QT_EST + C.QT_PEND_ENT) / (0.0001)) * 30), 0) 
																						END
) PONTO_ENCOMENDA
GROUP BY PONTO_ENCOMENDA.CD_AGENDA
	   , PONTO_ENCOMENDA.DS_AGENDA
	   , PONTO_ENCOMENDA.DT_AGENDA
ORDER BY TOTAL DESC;