/****** Object:  UserDefinedFunction [dbo].[UFD_EST_PED_CP_IT_SLD_DANFE]    Script Date: 25/08/2023 14:50:00 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PED_CP_IT_SLD_DANFE]') 
			AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_PED_CP_IT_SLD_DANFE]
GO
GO

--SELECT DBO.UFD_EST_PED_CP_IT_SLD_DANFE(1,16,1538, 1, 1)
CREATE FUNCTION [dbo].[UFD_EST_PED_CP_IT_SLD_DANFE](
												  @CD_EMP				INT,
												  @CD_FILIAL 			INT,
												  @CD_PED				INT,
												  @CD_PROD				INT,
												  @CONSIDERA_DANFE		BIT,
												  @CD_CTR_DANFE_EXCECAO INT)
RETURNS MONEY
AS
BEGIN

	DECLARE @QT_SLD			MONEY, 
			@QT_IT_NF		MONEY, 
			@QT_IT_CANCEL	MONEY,
			@QT_IT_DANFE	MONEY,
			@QT_IT_SLD_FINAL MONEY,
			@ConsideraDanfeEmpresa SMALLINT;

	set @ConsideraDanfeEmpresa = ISNULL((SELECT VALOR FROM 
								 PRC_EMP_CONFIG 
								 WHERE CD_CHAVE= 'CONTROLE_SALDO_VINCULO_NOTA_COM_PEDIDO'),0) 

	--BUSCA O SALDO INICIAL
	SET @QT_SLD = (SELECT (EST_PED_CP_IT.QT_IT * EST_PED_CP_IT.QT_EMB)
						FROM         
							EST_PED_CP_IT
						WHERE
							EST_PED_CP_IT.CD_EMP		= @CD_EMP
							AND EST_PED_CP_IT.CD_FILIAL = @CD_FILIAL
							AND EST_PED_CP_IT.CD_PED	= @CD_PED 
							AND EST_PED_CP_IT.CD_PROD	= @CD_PROD)


	--BUSCA A QUANTIDADE DE ENTRADA NAS NOTAS FISCAIS  
	SET @QT_IT_NF =	(SELECT     
						SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT
					FROM         
						EST_NF_ENT_IT 
						INNER JOIN EST_NF_ENT_PED_CP ON 
						EST_NF_ENT_IT.CD_EMP	= EST_NF_ENT_PED_CP.CD_EMP AND 
						EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND 
						EST_NF_ENT_IT.CD_NF		= EST_NF_ENT_PED_CP.CD_NF
					WHERE     
						EST_NF_ENT_IT.CD_EMP		= @CD_EMP
						AND EST_NF_ENT_IT.CD_PROD	= @CD_PROD
						AND EST_NF_ENT_IT.CD_FILIAL	= @CD_FILIAL
						AND EST_NF_ENT_PED_CP.CD_PED = @CD_PED)

	--BUSCA A QUANTIDADE CANCELADA
	SET @QT_IT_CANCEL = ( SELECT     
								SUM (C.QT_PROD*i.QT_EMB) AS QT_IT
							FROM         
								EST_PED_CP_IT_CANCEL_SLD c 
								INNER JOIN EST_PED_CP_IT i ON
								c.CD_EMP	= i.CD_EMP AND
								c.CD_FILIAL = i.CD_FILIAL AND
								c.CD_PED	= i.CD_PED AND
								c.CD_PROD	= i.CD_PROD 
							WHERE     
								c.CD_EMP		= @CD_EMP
								AND c.CD_FILIAL = @CD_FILIAL
								AND c.CD_PED	= @CD_PED 
								AND c.CD_PROD	= @CD_PROD )

	SET @QT_IT_DANFE = 0

	IF @CONSIDERA_DANFE = 1 and @ConsideraDanfeEmpresa = 1
	BEGIN
		SET @QT_IT_DANFE =	(	SELECT ROUND(SUM(D.QTDE_PROD * (
									CONVERT(DECIMAL(13, 4),CASE WHEN ISNULL(APRES_QT_CP,0)=0 THEN 1  ELSE APRES_QT_CP END) / 
									CONVERT(DECIMAL(13, 4), CASE WHEN APRES_EMB_CP=0 THEN 1  ELSE APRES_EMB_CP END)
								)),4) QT_IT_VINCULADO
								FROM
									EST_NF_ENT_DANFE_PROVISORIO_ITENS D
									JOIN EST_NF_ENT_DANFE_PROVISORIO B ON B.CD_CTR = D.CD_CTR AND B.CD_EMP = D.CD_EMP
									JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP C ON C.CD_CTR = B.CD_CTR AND C.CD_EMP = B.CD_EMP AND C.CD_FILIAL = B.CD_FILIAL
									JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE PEND ON PEND.CD_CTR = B.CD_CTR 
									JOIN EST_PED_CP_IT PED ON PED.CD_EMP = B.CD_EMP AND PED.CD_FILIAL = B.CD_FILIAL AND PED.CD_PED = C.CD_PED AND PED.CD_PROD = D.CD_PROD
								WHERE
									D.QTDE_PROD		> 0
									AND B.CD_EMP	= @CD_EMP
									AND B.CD_FILIAL	= @CD_FILIAL
									AND C.CD_PED	= @CD_PED 
									AND D.CD_PROD	= @CD_PROD 
									AND B.CD_CTR   <> @CD_CTR_DANFE_EXCECAO
								GROUP  BY
									B.CD_EMP
									,B.CD_FILIAL
									,C.CD_PED
									,D.CD_PROD
							)
	END
 
	SET @QT_IT_SLD_FINAL=	ISNULL(@QT_SLD,0) - (ISNULL(@QT_IT_NF,0) + ISNULL(@QT_IT_CANCEL,0) + ISNULL(@QT_IT_DANFE,0))

	IF @QT_IT_SLD_FINAL < 0
	BEGIN
		SET @QT_IT_SLD_FINAL=0
	END

	RETURN(@QT_IT_SLD_FINAL)
END


