IF EXISTS (SELECT
						 *
					 FROM
						 sys.objects
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[TST_EST_PED_CP_STATUS]') AND
						type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
	DROP FUNCTION [dbo].[TST_EST_PED_CP_STATUS]

GO

--SELECT DBO.TST_EST_PED_CP_STATUS (1,999,35153)
CREATE FUNCTION [dbo].[TST_EST_PED_CP_STATUS](@CD_EMP    INT,
																							@CD_FILIAL INT,
																							@CD_PED    INT)
RETURNS VARCHAR(30)
WITH ENCRYPTION
AS
	BEGIN
			DECLARE @STS_PED VARCHAR (30)
			DECLARE @STATUS_PED INT

			SET @STATUS_PED=ISNULL((SELECT 	STS_PED
															FROM
																EST_PED_CP
															WHERE
															 CD_EMP    = @CD_EMP AND
															 CD_FILIAL = @CD_FILIAL AND
															 CD_PED    = @CD_PED  ), 0);

			IF @STATUS_PED <> 2
				BEGIN
						--Itens da nota fiscal para análise
						DECLARE @RS_NF_ENT_TOT_IT TABLE
							(
								 CD_PROD  INT    NOT NULL PRIMARY KEY,
								 QT_IT_NF MONEY
							)
						--Itens do pedido de compras
						DECLARE @RS_PED_ENT_TOT_IT TABLE
							(
								 CD_PROD   INT NOT NULL PRIMARY KEY,
								 QT_IT_PED MONEY
							)
						--Para comparação das quantidades
						DECLARE @RS_TOT_COMPARACAO TABLE
							(
								 CD_PROD   INT NOT NULL PRIMARY KEY,
								 QT_IT_SLD MONEY
							)
						DECLARE @POSSUI_NF INT=0;

						--Buscando Itens da nota fiscal
						INSERT INTO @RS_NF_ENT_TOT_IT
						SELECT
							EST_NF_ENT_IT.CD_PROD
							,ISNULL(SUM(EST_NF_ENT_IT.QT_IT), 0)
						FROM
							EST_NF_ENT_PED_CP
							INNER JOIN EST_NF_ENT_IT ON
								EST_NF_ENT_PED_CP.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
								EST_NF_ENT_PED_CP.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
								EST_NF_ENT_PED_CP.CD_NF = EST_NF_ENT_IT.CD_NF
						WHERE
							EST_NF_ENT_IT.CD_EMP     = @CD_EMP AND
							EST_NF_ENT_IT.CD_FILIAL  = @CD_FILIAL AND
							EST_NF_ENT_PED_CP.CD_PED = @CD_PED
						GROUP  BY
							EST_NF_ENT_IT.CD_PROD
							option(recompile)
						IF EXISTS (SELECT TOP 1
												 *
											 FROM
												 @RS_NF_ENT_TOT_IT)
							BEGIN
									SET @POSSUI_NF=1
							END

						--Buscando itens do pedido de compras
						INSERT INTO @RS_PED_ENT_TOT_IT
						SELECT
							EST_PED_CP_IT.CD_PROD
							,ISNULL(SUM(EST_PED_CP_IT.QT_IT * CASE WHEN EST_PED_CP_IT.QT_EMB=0 THEN 1 ELSE EST_PED_CP_IT.QT_EMB END), 0) - ISNULL(SUM(EST_PED_CP_IT_CANCEL_SLD.QT_PROD* CASE WHEN EST_PED_CP_IT.QT_EMB=0 THEN 1 ELSE EST_PED_CP_IT.QT_EMB END), 0)
						FROM
							EST_PED_CP_IT
							LEFT OUTER JOIN EST_PED_CP_IT_CANCEL_SLD ON
								EST_PED_CP_IT.CD_EMP = EST_PED_CP_IT_CANCEL_SLD.CD_EMP AND
								EST_PED_CP_IT.CD_FILIAL = EST_PED_CP_IT_CANCEL_SLD.CD_FILIAL AND
								EST_PED_CP_IT.CD_PED = EST_PED_CP_IT_CANCEL_SLD.CD_PED AND
								EST_PED_CP_IT.CD_PROD = EST_PED_CP_IT_CANCEL_SLD.CD_PROD
						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
						GROUP  BY
							EST_PED_CP_IT.CD_PROD
							option(recompile)
						--Comparando se os itens do pedido e da nota foram entregues
						INSERT INTO @RS_TOT_COMPARACAO
						SELECT
							ipe.CD_PROD
							,
							--Se a quantidade da nota for superior a quantidade do pedido entao foi entregue total	
							CASE WHEN ipe.QT_IT_PED - ISNULL(inf.QT_IT_NF, 0) > 0 THEN ISNULL(ipe.QT_IT_PED - ISNULL(inf.QT_IT_NF, 0), 0)
							 ELSE 0
							 END
						FROM
							@RS_PED_ENT_TOT_IT ipe
							LEFT OUTER JOIN @RS_NF_ENT_TOT_IT inf ON
								ipe.CD_PROD = inf.CD_PROD
								option(recompile)
						SELECT
							@STS_PED = CASE WHEN SUM(QT_IT_SLD) = SUM(QT_IT_PED) AND
															SUM(QT_IT_PED) > 0 THEN --Se for igual ao pedido
												 'PEDIDO PENDENTE'
												 WHEN SUM(QT_IT_SLD) <> 0 THEN --Saldo maior que zero
												 'PEDIDO COM ENTREGA PARCIAL'
												 WHEN SUM(QT_IT_SLD) = 0 THEN --Saldo zerados
													 CASE WHEN @POSSUI_NF = 1 THEN 'PEDIDO COM ENTREGA TOTAL'
													 ELSE 'PEDIDO CANCELADO'
													 END
												 ELSE 'SEM CLASSIFICAÇÃO'
												 END
						FROM
							@RS_TOT_COMPARACAO ic
							INNER JOIN @RS_PED_ENT_TOT_IT ipe ON
								ic.CD_PROD = ipe.CD_PROD
								option(recompile)
				END

			IF @STATUS_PED = 2
				BEGIN
						SET @STS_PED='PEDIDO CANCELADO'
				END

			RETURN( @STS_PED )
	END 
