/****** Object:  UserDefinedFunction [dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA]    Script Date: 12/10/2013 15:17:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA]
GO
GO
GO
GO
/*
	Gera Pedencia de entrada de um determinado produto
	
	EMPRESA: ITEC 
	DATA INICIO:  19/03/2004
	DATA TERMINO: 19/03/2004 
	PROFISSIONAL: Cristiano Nunes
*/
--VERSAO 2425
--SELECT * FROM  UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA(1,1,342)
CREATE    FUNCTION [dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA](
--CREATE FUNCTION UFD_BUSCA_EST_PROD_CPL_PEND_ENTRADA(
																	 @CD_EMP		INT,
																	 @CD_FILIAL	 VARCHAR(MAX),	
																	 @CD_PROD	INT)
RETURNS @RS_PEND_ENTRADA TABLE(
								CD_EMP INT, 
								CD_FILIAL INT, 
								CD_ORIG INT, 
								CD_DOC INT, 
								DT_DOC  DATETIME, 
								TP_DOC  VARCHAR(50), 
								CD_PROD INT, 
								QT_ENT  MONEY)
AS
BEGIN
/*
DECLARE @CD_EMP     INT
DECLARE @CD_PROD    INT

SET @CD_EMP     =1
SET @CD_PROD    =1930
*/

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

DECLARE @TBL_FILIAL TABLE(CD_EMP INT, CD_FILIAL INT)

INSERT INTO @TBL_FILIAL
SELECT @CD_EMP, ID FROM UFD_PRM_IN_STRING(@CD_FILIAL) ;

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


DECLARE @TBL_QT_PEND_ENT TABLE(CD_EMP	    INT, 
						  CD_FILIAL   INT, 
						  CD_ORIG INT, 
						  CD_DOC  INT, 
						  DT_DOC  DATETIME, 
						  TP_DOC  VARCHAR(50), 
						  CD_PROD INT, 
						  QT_ENT MONEY)

--Ressuprimento
INSERT INTO @TBL_QT_PEND_ENT 
SELECT   
		EST_PROD_TRANSF.CD_EMP, 
		EST_PROD_TRANSF.CD_FIL_DEST, 
		EST_PROD_TRANSF.CD_FILIAL,
		EST_PROD_TRANSF.CD_CTR,
		EST_PROD_TRANSF.DT_TRANSF,
		'RESSUPRIMENTO',
		EST_PROD_TRANSF_IT.CD_PROD, 
		EST_PROD_TRANSF_IT.QT_SOL AS QT_ENT
FROM     
		EST_PROD_TRANSF  
		INNER JOIN  EST_PROD_TRANSF_IT  ON 
		EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_IT.CD_EMP AND
		EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT.CD_FILIAL AND
		EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_IT.CD_CTR
		INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
		EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
		EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
		EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR												

		INNER JOIN @TBL_FILIAL fl ON
		EST_PROD_TRANSF.CD_EMP	 = fl.CD_EMP AND
		EST_PROD_TRANSF.CD_FIL_DEST = fl.CD_FILIAL 
		

WHERE
		EST_PROD_TRANSF.CD_EMP = @CD_EMP 
		--AND EST_PROD_TRANSF.CD_FIL_DEST IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND EST_PROD_TRANSF_IT.CD_PROD = @CD_PROD

--Notas de Transferencia nas demais filiais
INSERT @TBL_QT_PEND_ENT 
SELECT  
	EST_NF_SAI.CD_EMP,
	EST_NF_SAI.CD_CLI AS CD_FILIAL,
	EST_NF_SAI.CD_FILIAL AS CD_ORIG,
	EST_NF_SAI.NF_NF,
	EST_NF_SAI.DT_EMI_NF,
	'NOTA TRANSFERENCIA',
	EST_NF_SAI_IT.CD_PROD,
	EST_NF_SAI_IT.QT_IT AS QT_ENT_TMP
FROM    
	EST_NF_SAI INNER JOIN GLB_MOV_OP ON 
	EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
	INNER JOIN EST_NF_SAI_IT ON 
	EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
	EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
	INNER JOIN EST_NF_SAI_PENDENCIA ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_PENDENCIA.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_PENDENCIA.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_PENDENCIA.CD_NF 

	INNER JOIN @TBL_FILIAL fl ON
		EST_NF_SAI.CD_EMP    = fl.CD_EMP AND
		EST_NF_SAI.CD_CLI = fl.CD_FILIAL 
WHERE   
	EST_NF_SAI.CD_EMP = @CD_EMP 
	--AND EST_NF_SAI.CD_CLI IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	AND EST_NF_SAI_IT.CD_PROD = @CD_PROD 
	AND GLB_MOV_OP.DEST_ORIG = 2
	AND EST_NF_SAI.STS_NF = 0 
	AND EST_NF_SAI.PEND_ENTRADA = 0 							

--Nota de Venda no grupo
INSERT @TBL_QT_PEND_ENT 
SELECT     
	RS_TRANSF_PRC_FILIAL.CD_EMP, 
	RS_TRANSF_PRC_FILIAL.CD_FILIAL_DEST, 
	RS_TRANSF_PRC_FILIAL.CD_FILIAL, 
	RS_TRANSF_PRC_FILIAL.NF_NF,
	RS_TRANSF_PRC_FILIAL.DT_EMI_NF,
	DS_MOV_OP_USUAL,
	RS_TRANSF_PRC_FILIAL.CD_PROD,									
	QT_IT AS QT_ENT_TMP
FROM         
	V_EST_NF_SAI_TRANSF_PRC_FILIAL_RC_CLI RS_TRANSF_PRC_FILIAL
	INNER JOIN EST_NF_SAI_PENDENCIA ON
	RS_TRANSF_PRC_FILIAL.CD_EMP = EST_NF_SAI_PENDENCIA.CD_EMP AND
	RS_TRANSF_PRC_FILIAL.CD_FILIAL = EST_NF_SAI_PENDENCIA.CD_FILIAL AND
	RS_TRANSF_PRC_FILIAL.CD_NF = EST_NF_SAI_PENDENCIA.CD_NF
	
	
	INNER JOIN @TBL_FILIAL fl ON
		RS_TRANSF_PRC_FILIAL.CD_EMP	     = fl.CD_EMP AND
		RS_TRANSF_PRC_FILIAL.CD_FILIAL_DEST = fl.CD_FILIAL 
		 
WHERE	
	RS_TRANSF_PRC_FILIAL.CD_EMP = @CD_EMP 
	--AND RS_TRANSF_PRC_FILIAL.CD_FILIAL_DEST IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	AND RS_TRANSF_PRC_FILIAL.CD_PROD = @CD_PROD 

--Pedido de comras
INSERT @TBL_QT_PEND_ENT 
	SELECT     
		EST_PED_CP.CD_EMP, 
		EST_PED_CP.CD_FILIAL, 
		EST_PED_CP.CD_FORN, 
		EST_PED_CP.CD_PED, 
		EST_PED_CP.DT_EMI, 
		'PEDIDO DE COMPRA',
		EST_PED_CP_IT.CD_PROD, 
		--ISNULL((QT_IT_SLD / CASE WHEN EST_PED_CP_IT.QT_EMB=0 THEN 1 ELSE EST_PED_CP_IT.QT_EMB END),QT_IT)  AS QT_IT
		
		case when EST_PED_CP_IT.QT_IT_SLD is null then 
			 EST_PED_CP_IT.QT_IT* EST_PED_CP_IT.QT_EMB 
		  else EST_PED_CP_IT.QT_IT_SLD end QT_IT
	FROM         
		EST_PED_CP INNER JOIN EST_PED_CP_IT ON 
		EST_PED_CP.CD_EMP = EST_PED_CP_IT.CD_EMP AND 
		EST_PED_CP.CD_FILIAL = EST_PED_CP_IT.CD_FILIAL AND 
	    EST_PED_CP.CD_PED = EST_PED_CP_IT.CD_PED
		INNER JOIN EST_PED_CP_PENDENCIA ON
		EST_PED_CP.CD_EMP = EST_PED_CP_PENDENCIA.CD_EMP AND
		EST_PED_CP.CD_FILIAL = EST_PED_CP_PENDENCIA.CD_FILIAL AND
		EST_PED_CP.CD_PED = EST_PED_CP_PENDENCIA.CD_PED	

		
	INNER JOIN @TBL_FILIAL fl ON
		EST_PED_CP.CD_EMP	     = fl.CD_EMP AND
		EST_PED_CP.CD_FILIAL = fl.CD_FILIAL

	WHERE     
		EST_PED_CP.CD_EMP = @CD_EMP
		--AND EST_PED_CP.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND EST_PED_CP_IT.CD_PROD = @CD_PROD 

--Pendencia de entrada de implantacao de sistema
INSERT @TBL_QT_PEND_ENT 
SELECT     
	EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_EMP, 
	EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL, 
    CASE WHEN ISNULL(EST_PED_CP_PENDENCIA_IMPLANTACAO.BLQ_EST,0)=0 THEN
		EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FORN
	ELSE
		EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL 
	END AS CD_FORN, 
    EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_CTR, 
    EST_PED_CP_PENDENCIA_IMPLANTACAO.DT_CAD, 
    CASE WHEN ISNULL(EST_PED_CP_PENDENCIA_IMPLANTACAO.BLQ_EST,0)=0 THEN
		'PENDENCIA ENTRADA IMPLANTACAO' 
	ELSE
		'BLOQUEIO DE ESTOQUE DE PRODUTOS - ENTRADA'
	END AS TP_DOC, 
    EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD, 
    EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.QT_IT
FROM         
	EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL INNER JOIN EST_PROD ON 
	EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_EMP = EST_PROD.CD_EMP AND
	EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD = EST_PROD.CD_PROD	 
	INNER JOIN EST_PED_CP_PENDENCIA_IMPLANTACAO ON 
	EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_CTR = EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_CTR

		
	INNER JOIN @TBL_FILIAL fl ON
		EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_EMP	     = fl.CD_EMP AND
		EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL = fl.CD_FILIAL

WHERE
	EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_EMP = @CD_EMP
	--AND EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	AND EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD = @CD_PROD 

		
--Pendencia vinculada
	INSERT INTO @TBL_QT_PEND_ENT
	SELECT
			EST_PED_CP.CD_EMP
		, EST_PED_CP_VINCULADA.CD_FILIAL_VINCULADA
		, EST_PED_CP.CD_FORN
		, EST_PED_CP.CD_PED
		, EST_PED_CP.DT_EMI
		, 'VINCULACAO PENDENCIA FILIAL '+CONVERT(VARCHAR(3), EST_PED_CP.CD_FILIAL)
		, EST_PED_CP_IT.CD_PROD 
		, CASE WHEN EST_PED_CP_IT.QT_IT_SLD IS NULL THEN EST_PED_CP_IT.QT_IT * EST_PED_CP_IT.QT_EMB
				ELSE EST_PED_CP_IT.QT_IT_SLD
			END QT_IT
	FROM
		EST_PED_CP
		INNER JOIN EST_PED_CP_IT ON EST_PED_CP.CD_EMP = EST_PED_CP_IT.CD_EMP
									AND EST_PED_CP.CD_FILIAL = EST_PED_CP_IT.CD_FILIAL
									AND EST_PED_CP.CD_PED = EST_PED_CP_IT.CD_PED
		INNER JOIN EST_PED_CP_PENDENCIA ON EST_PED_CP.CD_EMP = EST_PED_CP_PENDENCIA.CD_EMP
											AND EST_PED_CP.CD_FILIAL = EST_PED_CP_PENDENCIA.CD_FILIAL
											AND EST_PED_CP.CD_PED = EST_PED_CP_PENDENCIA.CD_PED
		INNER JOIN EST_PED_CP_VINCULADA ON EST_PED_CP.CD_EMP = EST_PED_CP_VINCULADA.CD_EMP
											AND EST_PED_CP.CD_FILIAL = EST_PED_CP_VINCULADA.CD_FILIAL
											AND EST_PED_CP.CD_PED = EST_PED_CP_VINCULADA.CD_PED
		INNER JOIN @TBL_FILIAL fl ON EST_PED_CP_VINCULADA.CD_EMP = fl.CD_EMP
										AND EST_PED_CP_VINCULADA.CD_FILIAL_VINCULADA = fl.CD_FILIAL
	WHERE 
	EST_PED_CP.CD_EMP = @CD_EMP
	AND EST_PED_CP_IT.CD_PROD = @CD_PROD;


--Pedidos de Vendas 
INSERT @TBL_QT_PEND_ENT 
	SELECT 
		V_EST_PED_VD.CD_EMP, 
		V_EST_PED_VD.CD_CLI AS CD_FILIAL, 
		V_EST_PED_VD.CD_FILIAL AS CD_ORIG, 
		V_EST_PED_VD.CD_PED, 
		V_EST_PED_VD.DT_EMI_PED, 
		'PRE-FATURAS / PEDIDO VENDAS', 
		EST_PED_VD_IT.CD_PROD, 
		EST_PED_VD_IT.QT_IT
	FROM         
		EST_PED_VD_IT INNER JOIN V_EST_PED_VD ON 
		EST_PED_VD_IT.CD_EMP = V_EST_PED_VD.CD_EMP AND 
		EST_PED_VD_IT.CD_FILIAL = V_EST_PED_VD.CD_FILIAL AND 
		EST_PED_VD_IT.CD_PED = V_EST_PED_VD.CD_PED
		INNER JOIN EST_PED_VD_PENDENCIA ON
		V_EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP AND
		V_EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL AND
		V_EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED 

		
	     INNER JOIN @TBL_FILIAL fl ON
		V_EST_PED_VD.CD_EMP	     = fl.CD_EMP AND
		V_EST_PED_VD.CD_FIL_DEST = fl.CD_FILIAL
	WHERE   
		V_EST_PED_VD.CD_EMP = @CD_EMP 
		--AND V_EST_PED_VD.CD_FIL_DEST IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND EST_PED_VD_IT.CD_PROD = @CD_PROD

--Requisio de produtos
INSERT @TBL_QT_PEND_ENT 				
	SELECT     
		DISTINCT EST_PROD_REQ_TRANSF_LIB.CD_EMP,	
		EST_PROD_REQ_TRANSF_LIB.CD_FILIAL_DEST AS CD_FILIAL, 
		EST_PROD_REQ_TRANSF_LIB.CD_FILIAL AS CD_ORIG, 	
		EST_PROD_REQ_TRANSF_LIB.CD_REQ, 
		EST_PROD_REQ_TRANSF_LIB.DT_CAD, 
		'REQUISICAO',
		EST_PROD_REQ_TRANSF_LIB_ITENS.CD_PROD,
		EST_PROD_REQ_TRANSF_LIB_ITENS.QT_PROD
	FROM         
		EST_PROD_REQ_TRANSF_LIB INNER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS ON 
		EST_PROD_REQ_TRANSF_LIB.CD_EMP = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_EMP AND 
		EST_PROD_REQ_TRANSF_LIB.CD_FILIAL = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_FILIAL AND 
		EST_PROD_REQ_TRANSF_LIB.CD_REQ = EST_PROD_REQ_TRANSF_LIB_ITENS.CD_REQ 
		LEFT OUTER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP ON 
		EST_PROD_REQ_TRANSF_LIB_ITENS.CD_EMP = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_EMP AND 
		EST_PROD_REQ_TRANSF_LIB_ITENS.CD_FILIAL = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_FILIAL_ORIG AND 
		EST_PROD_REQ_TRANSF_LIB_ITENS.CD_REQ = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_REQ AND 
		EST_PROD_REQ_TRANSF_LIB_ITENS.CD_PROD = EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_PROD

		
	     INNER JOIN @TBL_FILIAL fl ON
		EST_PROD_REQ_TRANSF_LIB.CD_EMP	    = fl.CD_EMP AND
		EST_PROD_REQ_TRANSF_LIB.CD_FILIAL_DEST = fl.CD_FILIAL
	WHERE
		 EST_PROD_REQ_TRANSF_LIB_ITENS.CD_EMP = @CD_EMP
		 AND EST_PROD_REQ_TRANSF_LIB_ITENS.CD_PROD=@CD_PROD
	--	 AND EST_PROD_REQ_TRANSF_LIB.CD_FILIAL_DEST IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		 AND EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP.CD_IMP IS NULL
		 AND EST_PROD_REQ_TRANSF_LIB.STS_CANCEL=0

--CrossDocking Pendencia
INSERT @TBL_QT_PEND_ENT 
	SELECT  
		V_EST_NF_ENT.CD_EMP,
		V_EST_NF_ENT.CD_FILIAL AS CD_FILIAL,
		V_EST_NF_ENT.CD_FILIAL AS CD_ORIG,
		V_EST_NF_ENT.NR_NF,
		V_EST_NF_ENT.DT_EMI_NF,
		'CROSS-DOCKING PENDENCIA\BLOQUEIO DE ESTOQUE',
		V_EST_NF_ENT.CD_PROD,
		V_EST_NF_ENT.QT_IT AS QT_ENT_TMP
	FROM    
		V_EST_NF_ENT INNER JOIN EST_NF_ENT_CROSS_DOCKING_PENDENTE ON 
		V_EST_NF_ENT.CD_EMP = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_EMP AND 
		V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_FILIAL AND 
		V_EST_NF_ENT.CD_NF = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_NF

		
	     INNER JOIN @TBL_FILIAL fl ON
		V_EST_NF_ENT.CD_EMP	    = fl.CD_EMP AND
		V_EST_NF_ENT.CD_FILIAL = fl.CD_FILIAL
	WHERE   
		V_EST_NF_ENT.CD_EMP = @CD_EMP 
	--	AND V_EST_NF_ENT.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND V_EST_NF_ENT.CD_PROD = @CD_PROD 

--PBM Pendencia
INSERT @TBL_QT_PEND_ENT 
SELECT
	a.CD_EMP,
	a.CD_FILIAL,
	a.CD_FILIAL AS CD_ORIG,
	0 AS NR_NF,
	CONVERT(CHAR(10),GETDATE(),112) AS DT_EMI_NF,
	'PBM - PENDENCIA DE ENTRADA POR VENDA',
	a.CD_PROD,
	a.QT_SALDO AS QT_ENT_TMP
FROM
	RC_CLI_CONV_PBM_CONTROLE_EST_PROD a
	   INNER JOIN @TBL_FILIAL fl ON
		a.CD_EMP	    = fl.CD_EMP AND
		a.CD_FILIAL = fl.CD_FILIAL
WHERE
	    a.CD_EMP =@CD_EMP
	--AND a.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	AND a.CD_PROD   =@CD_PROD
	AND a.QT_SALDO  > 0	
		
INSERT INTO @TBL_QT_PEND_ENT
SELECT   
    D.CD_EMP,  
    CASE WHEN O.DEST_ORIG=2 THEN 
	   I.CD_FORN
    ELSE 
	   0 
    END AS CD_FILIAL, 
    D.CD_FILIAL AS CD_ORIG,
    I.NR_NF,
    I.DT_EMI_NF,
    'DEVOLUCAO',
    D.CD_PROD, 
    QTDE_PROD - QTDE_PROD_DEVO_GERADA AS QT_IT
FROM 
    EST_NF_ENT_IT_DEVOLUCAO  D
    INNER JOIN EST_NF_ENT I ON
	   D.CD_EMP	   = I.CD_EMP AND 
	   D.CD_FILIAL  = I.CD_FILIAL AND 
	   D.CD_NF	   = I.CD_NF
    INNER JOIN GLB_MOV_OP O ON
	   I.CD_NT_MOV = O.CD_MOV_OP 

			
    INNER JOIN @TBL_FILIAL fl ON
    D.CD_EMP	  = fl.CD_EMP   
    AND CASE WHEN O.DEST_ORIG=2 THEN  I.CD_FORN
	   ELSE  0  END = fl.CD_FILIAL

WHERE
    D.QTDE_PROD - D.QTDE_PROD_DEVO_GERADA > 0
    AND D.CD_EMP = 1
    
    --AND CASE WHEN O.DEST_ORIG=2 THEN  I.CD_FORN
	   --ELSE  0  END IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))

    AND D.CD_PROD = @CD_PROD


INSERT INTO @TBL_QT_PEND_ENT
SELECT 
    E.CD_EMP,	
    E.CD_FIL_ENTR AS CD_FILIAL,	
    0 AS CD_ORIG,
    E.CD_PED_SOL,
    E.DT_PED,
    'DEMANDA DE COMPRAS EM ABERTO',
    E.CD_PROD,	 
    E.QT_IT - ISNULL(P.QT_PED,0) QT_SLD 
FROM 
    (SELECT 
	   E.CD_EMP,
	   E.CD_FIL_ENTR,
	   I.CD_PROD,
	   E.DT_PED,
	   E.CD_PED_SOL,
	   SUM(I.QT_IT_UN) QT_IT
    FROM         
	   EST_PED_ELTR  E
	   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 @TBL_FILIAL fl ON
		E.CD_EMP	    = fl.CD_EMP AND
		E.CD_FIL_ENTR = fl.CD_FILIAL
    WHERE
	   E.CD_EMP=1
	   AND not E.STS_PED in (3,4)
	   AND E.FLAG_FECHAR = 0
	   AND E.TP_REQ IN (0,1,3)
    GROUP BY 
	   E.CD_EMP,
	   E.CD_FIL_ENTR,
	   I.CD_PROD,
	   E.DT_PED,
	   E.CD_PED_SOL) E
			LEFT JOIN (SELECT 
							EP.CD_EMP, 
							EP.CD_FIL_ENTR,  
							PD.CD_PROD,
							EP.CD_PED_SOL,
							SUM( PD.QT_IT * PD.QT_EMB ) QT_PED
						FROM
							EST_PED_ELTR_FORN_PED_CP EP 
							INNER JOIN DBO.EST_PED_CP_IT PD ON 
							EP.CD_EMP		= PD.CD_EMP AND 
							EP.CD_FIL_ENTR	= PD.CD_FILIAL AND 
							EP.CD_PED		= PD.CD_PED 
	
							INNER JOIN EST_PED_CP_PENDENCIA PE ON
							PD.CD_EMP	 = PE.CD_EMP AND
							PD.CD_FILIAL = PE.CD_FILIAL AND
							PD.CD_PED	 = PE.CD_PED
							
							   INNER JOIN @TBL_FILIAL fl ON
							   EP.CD_EMP	    = fl.CD_EMP AND
							   EP.CD_FIL_ENTR = fl.CD_FILIAL
						GROUP BY 
							EP.CD_EMP, 
							EP.CD_FIL_ENTR,
							PD.CD_PROD,
							EP.CD_PED_SOL) P ON 
			E.CD_EMP		= P.CD_EMP AND 
			E.CD_FIL_ENTR	= P.CD_FIL_ENTR AND
			E.CD_PROD		= P.CD_PROD AND
			E.CD_PED_SOL=P.CD_PED_SOL
where
	E.CD_EMP = @CD_EMP 
--	AND E.CD_FIL_ENTR IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
	AND E.CD_PROD = @CD_PROD
	AND E.QT_IT - ISNULL(P.QT_PED,0) > 0 

INSERT INTO @RS_PEND_ENTRADA SELECT * FROM @TBL_QT_PEND_ENT
RETURN
END