IF EXISTS(SELECT 1 FROM SYS.PROCEDURES WHERE  
			NAME = 'P_EST_PROD_FABRIC_MAPA_VENDA')
	BEGIN
		DROP PROCEDURE P_EST_PROD_FABRIC_MAPA_VENDA;
	END;
GO

--EXEC P_EST_PROD_FABRIC_MAPA_VENDA 1,'1,2',6,0,0

CREATE PROCEDURE P_EST_PROD_FABRIC_MAPA_VENDA (@CD_EMP             INT
												, @CD_FILIAL          VARCHAR(max)
												, @CD_USU             INT
												, @FLAG_APLICA_FILTRO INT
												, @VENDAS_ULT         INT
												)
AS
BEGIN
	SET NOCOUNT ON;
				
	/*
		DROP TABLE #EST_PROD_CPL_PENDENTE_QT
		DROP TABLE #VENDAS_FILIAIS
		DROP TABLE #TMP_FILIAL 
		DECLARE 
			@CD_EMP INT  = 1 ,
			@CD_FILIAL VARCHAR (1000) ='1, 2, 5, 8, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 997, 998, 999',
			@CD_USU INT  = 788,
			@FLAG_APLICA_FILTRO int=0

	*/

	CREATE TABLE #VENDAS_FILIAIS (CD_FILIAL         INT
								, CD_PROD           INT
								, DS_PROD           VARCHAR(150)
								, NM_FABRIC         VARCHAR(150)
								, DS_ARV_MERC_LINHA VARCHAR(150)
								, QT_EST_CD         MONEY
								, QTDE_EST          MONEY
								, MEDIAF            MONEY
								, QT_PEND_SAI_CD    MONEY
								, QT_PEND_ENT_CD    MONEY
								, QT_PEND_SAI       MONEY
								, QT_PEND_ENT       MONEY
								, STS_PROD          VARCHAR(20)
								, STS_PROD_FILIAL   VARCHAR(20)
								, QT_VD_ATUAL       MONEY
								, VLR_VD_ATUAL      MONEY
								, QT_VD_01          MONEY
								, VLR_VD_01         MONEY
								, QT_VD_02          MONEY
								, VLR_VD_02         MONEY
								, QT_VD_03          MONEY
								, VLR_VD_03         MONEY
								, CD_BARRA          varchar (50)
								, VLR_TABELA        MONEY
								, VLR_OFERTA        MONEY
								, VLR_CUSTO_MD      MONEY
								, DS_CID            VARCHAR(100)
								, UF                VARCHAR(2)
								, DS_MC             VARCHAR(50)
								, DS_ARV_MERC_CATEG       VARCHAR(50)
								,DS_ARV_MERC_DEPTO VARCHAR(50));

	CREATE TABLE #EST_PROD_CPL_PENDENTE_QT(CD_EMP         INT
										, CD_PROD        INT
										, QT_EST_CD      MONEY
										, QT_PEND_SAI_CD MONEY
										, QT_PEND_ENT_CD MONEY
										);
		 
	CREATE TABLE #TMP_FILIAL(CD_EMP    INT
							, CD_FILIAL INT
							, DS_CID    VARCHAR(100)
							, UF        VARCHAR(2)
							);


	IF @CD_FILIAL = ''
		BEGIN

			INSERT INTO  #TMP_FILIAL(CD_EMP
									, CD_FILIAL
									, DS_CID
									, UF)
			SELECT
				CD_EMP
				, CD_FILIAL
				, GLB_CID.DS_CID
				, GLB_CID.UF
			FROM
				PRC_FILIAL
				INNER JOIN GLB_CID ON PRC_FILIAL.CD_CID = GLB_CID.CD_CID;
		END;
		ELSE
		BEGIN
			INSERT INTO  #TMP_FILIAL( CD_EMP
									, CD_FILIAL
									, DS_CID
									, UF)
			SELECT
				@CD_EMP
				, ID
				, ''
				, ''
			FROM
				UFD_PRM_IN_STRING(@CD_FILIAL);
			UPDATE T
			SET
				T.DS_CID = GLB_CID.DS_CID
				, T.UF = GLB_CID.UF
			FROM   #TMP_FILIAL T
				INNER JOIN PRC_FILIAL ON T.CD_EMP = PRC_FILIAL.CD_EMP
											AND T.CD_FILIAL = PRC_FILIAL.CD_FILIAL
				INNER JOIN GLB_CID ON PRC_FILIAL.CD_CID = GLB_CID.CD_CID;
		END;

--------------------------------------------------------------------------------------------------------------------------------------------------------

	INSERT INTO  #EST_PROD_CPL_PENDENTE_QT(CD_EMP
											, CD_PROD
											, QT_EST_CD
											, QT_PEND_SAI_CD
											, QT_PEND_ENT_CD)
	SELECT
		 EST_PROD_CPL.CD_EMP
		, EST_PROD_CPL.CD_PROD
		, SUM(QT_EST) AS QT_EST_CD
		, SUM(ISNULL(EST_PROD_CPL_PENDENTE.QT_PEND_SAI, 0)) AS QT_PEND_SAI_CD
		, SUM(ISNULL(EST_PROD_CPL_PENDENTE.QT_PEND_ENT, 0)) AS QT_PEND_ENT_CD
	FROM
		EST_PROD_CPL
		INNER JOIN V_PRC_FILIAL_DEPOSITO ON EST_PROD_CPL.CD_EMP = V_PRC_FILIAL_DEPOSITO.CD_EMP
											AND EST_PROD_CPL.CD_FILIAL = V_PRC_FILIAL_DEPOSITO.CD_FILIAL_DEPO
											AND V_PRC_FILIAL_DEPOSITO.FLAG_DEPO = 1
		LEFT OUTER JOIN EST_PROD_CPL_PENDENTE ON EST_PROD_CPL.CD_EMP = EST_PROD_CPL_PENDENTE.CD_EMP
												AND EST_PROD_CPL.CD_FILIAL = EST_PROD_CPL_PENDENTE.CD_FILIAL
												AND EST_PROD_CPL.CD_PROD = EST_PROD_CPL_PENDENTE.CD_PROD
	GROUP BY
			EST_PROD_CPL.CD_EMP
		, EST_PROD_CPL.CD_PROD;
	
	CREATE NONCLUSTERED INDEX IX_EST_PROD_CPL_PENDENTE_QT_CD_EMP_CD_PROD ON #EST_PROD_CPL_PENDENTE_QT (CD_EMP ASC, CD_PROD ASC);
	 
--------------------------------------------------------------------------------------------------------------------------------------------------------
	IF @FLAG_APLICA_FILTRO = 0
		BEGIN
			INSERT INTO #VENDAS_FILIAIS (CD_FILIAL  , CD_PROD  , DS_PROD , NM_FABRIC         
										, DS_ARV_MERC_LINHA , QT_EST_CD, QTDE_EST  , MEDIAF            
										, QT_PEND_SAI_CD , QT_PEND_ENT_CD, QT_PEND_SAI , QT_PEND_ENT       
										, STS_PROD , STS_PROD_FILIAL , QT_VD_ATUAL , VLR_VD_ATUAL  
										, QT_VD_01 , VLR_VD_01 , QT_VD_02 , VLR_VD_02 , QT_VD_03 
										, VLR_VD_03, CD_BARRA , VLR_TABELA  , VLR_OFERTA
										, VLR_CUSTO_MD , DS_CID , UF, DS_MC,DS_ARV_MERC_CATEG,DS_ARV_MERC_DEPTO)
			SELECT  
				C.CD_FILIAL
				, C.CD_PROD
				, C.DS_PROD
				, F.NM_FABRIC
				, L.DS_ARV_MERC_LINHA
				, isnull(D.QT_EST_CD,0)
				, C.QT_EST QTDE_EST
				, C.MEDIAF MEDIAF
				, isnull(D.QT_PEND_SAI_CD,0)
				, isnull(D.QT_PEND_ENT_CD,0)
				, C.QT_PEND_SAI AS QT_PEND_SAI
				, C.QT_PEND_ENT AS QT_PEND_ENT
				, CASE
					WHEN C.STS_PROD = 0 THEN 'ATIVO'
					WHEN C.STS_PROD = 1 THEN 'INATIVO'
					WHEN C.STS_PROD = 2 THEN 'BLOQUEADO'
				END STS_PROD
				, CASE
					WHEN C.STS_PROD_FILIAL = 0 THEN 'ATIVO'
					WHEN C.STS_PROD_FILIAL = 1 THEN 'INATIVO'
					WHEN C.STS_PROD_FILIAL = 2 THEN 'BLOQUEADO'
				END STS_PROD_FILIAL
				, ISNULL(VD_A.QT_VD_ATUAL, 0) QT_VD_ATUAL
				, ISNULL(VD_A.VLR_VD_ATUAL, 0) VLR_VD_ATUAL
				, ISNULL(VD_A.QT_VD_01, 0) QT_VD_01
				, ISNULL(VD_A.VLR_VD_01, 0) VLR_VD_01
				, ISNULL(VD_A.QT_VD_02, 0) QT_VD_02
				, ISNULL(VD_A.VLR_VD_02, 0) VLR_VD_02
				, ISNULL(VD_A.QT_VD_03, 0) QT_VD_03
				, ISNULL(VD_A.VLR_VD_03, 0) VLR_VD_03
				, '' AS CD_BARRA
				, (PRC.VLR_TABELA * C.QT_EST) AS VLR_TABELA
				, (PRC.VLR_OFERTA * C.QT_EST) AS VLR_OFERTA
				, (C.VLR_CUSTO_MD * C.QT_EST) AS VLR_CUSTO_MD
				, FIL.DS_CID
				, FIL.UF
				, M.DS_MC
				, G.DS_ARV_MERC_CATEG
				,depto.DS_ARV_MERC_DEPTO
			FROM
				V_EST_PROD_CPL_ESTOQUE C  

				LEFT JOIN #EST_PROD_CPL_PENDENTE_QT D ON C.CD_EMP = D.CD_EMP
														AND C.CD_PROD = D.CD_PROD
				INNER JOIN EST_ARV_MERC_LINHA L ON C.CD_EMP = L.CD_EMP
												AND C.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
				INNER JOIN EST_PROD_FABRIC F ON C.CD_FABRIC = F.CD_FABRIC
				LEFT JOIN V_EST_PROD_CPL_VD_MESES VD_A ON C.CD_EMP = VD_A.CD_EMP
														AND C.CD_FILIAL = VD_A.CD_FILIAL
														AND C.CD_PROD = VD_A.CD_PROD
				INNER JOIN EST_PROD_PRECO PRC ON C.CD_EMP = PRC.CD_EMP
												AND C.CD_FILIAL = PRC.CD_FILIAL
												AND C.CD_PROD = PRC.CD_PROD
				INNER JOIN #TMP_FILIAL FIL ON C.CD_EMP = FIL.CD_EMP
											AND C.CD_FILIAL = FIL.CD_FILIAL
								JOIN EST_PROD_EST_ARV_MERCADOLOGICA AM ON	C.CD_EMP  = AM.CD_EMP
														AND C.CD_PROD = AM.CD_PROD
				JOIN EST_MC M ON	AM.CD_EMP  = M.CD_EMP
								AND AM.CD_MC   = M.CD_MC
					JOIN EST_ARV_MERC_CATEGORIA G ON	AM.CD_EMP  = G.CD_EMP
												AND AM.CD_ARV_MERC_CATEG   = G.CD_ARV_MERC_CATEG

								JOIN EST_ARV_MERC_DEPTO depto ON	
								G.CD_EMP  = depto.CD_EMP
								AND G.CD_ARV_MERC_DEPTO   = depto.CD_ARV_MERC_DEPTO
			WHERE 
				C.CD_EMP = 1
				AND CASE
						WHEN @VENDAS_ULT = 1 THEN C.QT_EST
						ELSE 2
					END > 1;
		END;
		ELSE
		BEGIN
			INSERT INTO #VENDAS_FILIAIS
			SELECT
				C.CD_FILIAL
				, C.CD_PROD
				, C.DS_PROD
				, F.NM_FABRIC
				, L.DS_ARV_MERC_LINHA
				, isnull(D.QT_EST_CD,0)
				, C.QT_EST QTDE_EST
				, C.MEDIAF MEDIAF
				, isnull(D.QT_PEND_SAI_CD,0)
				, isnull(D.QT_PEND_ENT_CD,0)
				, C.QT_PEND_SAI AS QT_PEND_SAI
				, C.QT_PEND_ENT AS QT_PEND_ENT
				, CASE
					WHEN C.STS_PROD = 0 THEN 'ATIVO'
					WHEN C.STS_PROD = 1 THEN 'INATIVO'
					WHEN C.STS_PROD = 2 THEN 'BLOQUEADO'
				END STS_PROD
				, CASE
					WHEN C.STS_PROD_FILIAL = 0 THEN 'ATIVO'
					WHEN C.STS_PROD_FILIAL = 1 THEN 'INATIVO'
					WHEN C.STS_PROD_FILIAL = 2 THEN 'BLOQUEADO'
				END STS_PROD_FILIAL
				, ISNULL(VD_A.QT_VD_ATUAL, 0) QT_VD_ATUAL
				, ISNULL(VD_A.VLR_VD_ATUAL, 0) VLR_VD_ATUAL
				, ISNULL(VD_A.QT_VD_01, 0) QT_VD_01
				, ISNULL(VD_A.VLR_VD_01, 0) VLR_VD_01
				, ISNULL(VD_A.QT_VD_02, 0) QT_VD_02
				, ISNULL(VD_A.VLR_VD_02, 0) VLR_VD_02
				, ISNULL(VD_A.QT_VD_03, 0) QT_VD_03
				, ISNULL(VD_A.VLR_VD_03, 0) VLR_VD_03
				, '' AS CD_BARRA
				, (PRC.VLR_TABELA * C.QT_EST) AS VLR_TABELA
				, (PRC.VLR_OFERTA * C.QT_EST) AS VLR_OFERTA
				, (C.VLR_CUSTO_MD * C.QT_EST) AS VLR_CUSTO_MD
				, FIL.DS_CID
				, FIL.UF
				, M.DS_MC
				, G.DS_ARV_MERC_CATEG
				,depto.DS_ARV_MERC_DEPTO
			FROM
				V_EST_PROD_CPL_ESTOQUE C
				INNER JOIN GLB_ARV_MERC_FILTROS T ON C.CD_EMP = T.CD_EMP
													AND C.CD_PROD = T.CD_PROD
													AND T.CD_USU = @CD_USU
				LEFT JOIN #EST_PROD_CPL_PENDENTE_QT D ON C.CD_EMP = D.CD_EMP
														AND C.CD_PROD = D.CD_PROD
				INNER JOIN EST_ARV_MERC_LINHA L ON C.CD_EMP = L.CD_EMP
												AND C.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
				INNER JOIN EST_PROD_FABRIC F ON C.CD_FABRIC = F.CD_FABRIC
				LEFT JOIN V_EST_PROD_CPL_VD_MESES VD_A ON C.CD_EMP = VD_A.CD_EMP
														AND C.CD_FILIAL = VD_A.CD_FILIAL
														AND C.CD_PROD = VD_A.CD_PROD
				INNER JOIN EST_PROD_PRECO PRC ON C.CD_EMP = PRC.CD_EMP
												AND C.CD_FILIAL = PRC.CD_FILIAL
												AND C.CD_PROD = PRC.CD_PROD
				INNER JOIN #TMP_FILIAL FIL ON C.CD_EMP = FIL.CD_EMP
											AND C.CD_FILIAL = FIL.CD_FILIAL

				JOIN EST_PROD_EST_ARV_MERCADOLOGICA AM ON	C.CD_EMP  = AM.CD_EMP
														AND C.CD_PROD = AM.CD_PROD
				JOIN EST_MC M ON	AM.CD_EMP  = M.CD_EMP
								AND AM.CD_MC   = M.CD_MC
				JOIN EST_ARV_MERC_CATEGORIA G ON	AM.CD_EMP  = G.CD_EMP
												AND AM.CD_ARV_MERC_CATEG   = G.CD_ARV_MERC_CATEG
								JOIN EST_ARV_MERC_DEPTO depto ON	G.CD_EMP  = depto.CD_EMP
												AND G.CD_ARV_MERC_DEPTO   = depto.CD_ARV_MERC_DEPTO
			WHERE  C.CD_EMP = 1
				AND CASE
						WHEN @VENDAS_ULT = 1 THEN C.QT_EST
						ELSE 2
					END > 1;
		END;


-----------------------------------------------------------------------------------------------------------------------------
		 UPDATE #VENDAS_FILIAIS
		   SET
			CD_BARRA = (SELECT TOP 1 CD_BARRA
							FROM
								EST_PROD_CD_BARRA
							WHERE  EST_PROD_CD_BARRA.CD_PROD = #VENDAS_FILIAIS.CD_PROD
									AND NAO_EXPORTAR	  =0
									AND EAN_CAIXA_FECHADA =0
									AND NAO_USAR_PED_ELETR=0
						);
-----------------------------------------------------------------------------------------------------------------------------
		 SELECT
				*
		 FROM
			 #VENDAS_FILIAIS;
	 END;