 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_REQ_TRANSF_SLD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_REQ_TRANSF_SLD]
GO
--EXEC P_EST_PROD_REQ_TRANSF_SLD 1,998
CREATE PROCEDURE P_EST_PROD_REQ_TRANSF_SLD (@CD_EMP INT=1,@CD_FILIAL_ORIG INT=0,@CD_USU INT=0)
AS
BEGIN

CREATE TABLE  #_EST_PROD (CD_EMP					INT,
							  CD_PROD				INT,
							  DS_PROD				VARCHAR(400),
							  STS_PROD				INT,
							  QT_EMB_FECHADA_CP		INT,
							  QT_EMB_ALTERNATIVA_CP INT)

/****************************************************************************************************************
									P R O D U T O S
*****************************************************************************************************************/
 IF EXISTS(
			SELECT
				TOP 1
				CD_PROD
			FROM
				GLB_ARV_MERC_FILTROS
			WHERE
				CD_USU=@CD_USU
			) 
	BEGIN
		INSERT INTO #_EST_PROD
		SELECT 
			P.CD_EMP, 
			P.CD_PROD,
			P.DS_PROD,
			P.STS_PROD,
			P.QT_EMB_FECHADA_CP,
			P.QT_EMB_ALTERNATIVA_CP
		FROM
			EST_PROD P INNER JOIN GLB_ARV_MERC_FILTROS T ON
			P.CD_EMP = T.CD_EMP AND
			P.CD_PROD = T.CD_PROD 
		WHERE
			T.CD_USU = @CD_USU
	END
ELSE
	BEGIN
		INSERT INTO #_EST_PROD
		SELECT 
			P.CD_EMP, 
			P.CD_PROD,
			PROD.DS_PROD,
			PROD.STS_PROD,
			PROD.QT_EMB_FECHADA_CP,
			PROD.QT_EMB_ALTERNATIVA_CP
		FROM	  
		    EST_PROD_REQ_TRANSF_SLD P INNER JOIN EST_PROD PROD ON
			P.CD_EMP = PROD.CD_EMP AND
			P.CD_PROD = PROD.CD_PROD 			
			INNER JOIN GLB_ARV_MERC_FILTROS T ON
			P.CD_EMP = T.CD_EMP AND
			P.CD_PROD = T.CD_PROD 

		WHERE
		   P.CD_EMP=@CD_EMP
		   AND P.QT_PROD > 0
	END

SELECT  
	EST_PROD_REQ_TRANSF_SLD.CD_PROD, 
	P.DS_PROD, EST_PROD_CPL.CD_FILIAL, 
	EST_PROD_CPL.VLR_CUSTO_MD, 
	EST_PROD_CPL.MEDIAF, 
	ROUND((EST_PROD_CPL.MEDIAF/30.00)* EST_PROD_CPL.ESEG,0) ESEG, 
	ROUND((EST_PROD_CPL.MEDIAF/30.00)* EST_PROD_CPL.EMAX,0) EMAX, 
	EST_PROD_REQ_TRANSF_SLD.QT_PROD, 
	P.QT_EMB_FECHADA_CP CX_FCH, 
	P.QT_EMB_ALTERNATIVA_CP CX_ALT  
FROM  
	EST_PROD_CPL INNER JOIN EST_PROD_REQ_TRANSF_SLD ON 
	EST_PROD_CPL.CD_EMP = EST_PROD_REQ_TRANSF_SLD.CD_EMP AND 
	EST_PROD_CPL.CD_FILIAL = EST_PROD_REQ_TRANSF_SLD.CD_FILIAL AND 
	EST_PROD_CPL.CD_PROD = EST_PROD_REQ_TRANSF_SLD.CD_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA  ON 
	EST_PROD_CPL.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
	EST_PROD_CPL.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	INNER JOIN #_EST_PROD P ON
	EST_PROD_CPL.CD_EMP = P.CD_EMP AND
	EST_PROD_CPL.CD_PROD = P.CD_PROD 
WHERE 
	EST_PROD_REQ_TRANSF_SLD.CD_EMP = @CD_EMP
	AND EST_PROD_REQ_TRANSF_SLD.CD_FILIAL_ORIG_PROD = @CD_FILIAL_ORIG 
	AND EST_PROD_REQ_TRANSF_SLD.QT_PROD > 0 
	AND EST_PROD_CPL.TP_PROD = 0 
	AND EST_PROD_CPL.STS_PROD=0  
	AND P.STS_PROD=0  
	AND EST_PROD_REQ_TRANSF_SLD.CD_FILIAL IN (1) 
END