/****** Object:  UserDefinedFunction [dbo].[TST_EST_PROD_TRANSF_STATUS]    Script Date: 04/15/2013 16:40:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TST_EST_PROD_TRANSF_STATUS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TST_EST_PROD_TRANSF_STATUS]
GO
GO

/*
	Testa Status do ressuprimento

	EMPRESA: CORPORATE BUSINES
	DATA INICIO:  15/12/2003
	DATA TERMINO: 15/12/2003 
	PROFISSIONAL: Cristiano Nunes
*/

--SELECT DBO.TST_EST_PROD_TRANSF_STATUS(1,999,1414760)
CREATE         FUNCTION [dbo].[TST_EST_PROD_TRANSF_STATUS](
													@CD_EMP 		INT,
													@CD_FILIAL 		INT,
													@CD_CTR			INT)
RETURNS NVARCHAR(30) AS
BEGIN
DECLARE @QT_PRE_FAT MONEY
DECLARE @QT_NF MONEY
DECLARE @STATUS  VARCHAR(30)

SET @QT_PRE_FAT=0
SET @QT_NF=0
SET @STATUS=''

SET @QT_PRE_FAT =(SELECT     
								ISNULL(COUNT(EST_PROD_TRANSF_PED_VD.CD_PED),0) AS NR_PRE_FAT
				  FROM         
								EST_PROD_TRANSF_PED_VD INNER JOIN EST_PROD_TRANSF ON 
								EST_PROD_TRANSF_PED_VD.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
   								EST_PROD_TRANSF_PED_VD.CD_FILIAL = EST_PROD_TRANSF.CD_FILIAL AND 
				  				EST_PROD_TRANSF_PED_VD.CD_CTR = EST_PROD_TRANSF.CD_CTR
				  WHERE     
								EST_PROD_TRANSF.CD_EMP = @CD_EMP
								AND EST_PROD_TRANSF.CD_FILIAL = @CD_FILIAL 
								AND EST_PROD_TRANSF.CD_CTR = @CD_CTR)



SET @QT_NF =(SELECT 
				ISNULL(COUNT(EST_NF_SAI_PED_VD.CD_NF),0) AS NR_NF
			 FROM         
				EST_PROD_TRANSF_PED_VD INNER JOIN EST_NF_SAI_PED_VD ON 
				EST_PROD_TRANSF_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP AND 
				EST_PROD_TRANSF_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL AND 
				EST_PROD_TRANSF_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED
			WHERE     
				EST_PROD_TRANSF_PED_VD.CD_EMP = @CD_EMP
				AND EST_PROD_TRANSF_PED_VD.CD_FILIAL = @CD_FILIAL 
				AND EST_PROD_TRANSF_PED_VD.CD_CTR = @CD_CTR)

--Verifica se tem por requisicao
IF @QT_NF = 0 
	BEGIN
		SET @QT_NF =(SELECT 
										ISNULL(COUNT(EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_REQ),0) AS NR_NF
						 				FROM         
											EST_PROD_REQ_TRANSF_LIB_EST_PED_VD INNER JOIN EST_PROD_TRANSF_PED_VD ON 
											EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP AND 
											EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL AND 
											EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_PED = EST_PROD_TRANSF_PED_VD.CD_PED
										WHERE     
											EST_PROD_TRANSF_PED_VD.CD_EMP = @CD_EMP
											AND EST_PROD_TRANSF_PED_VD.CD_FILIAL = @CD_FILIAL 
											AND EST_PROD_TRANSF_PED_VD.CD_CTR = @CD_CTR)
	END	


--Se a pre-fatura for cancelada conta como um nota fiscal para mudar o status do ressuprimento para 
--fechado
SET @QT_NF =@QT_NF + (SELECT     
								ISNULL(COUNT(EST_PROD_TRANSF_PED_VD.CD_PED),0) AS NR_PRE_FAT
							  FROM         
								EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_PED_VD ON 
								EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP AND 
		                      	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL AND 
		                      	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PED_VD.CD_CTR 
								INNER JOIN EST_PED_VD ON 
								EST_PROD_TRANSF_PED_VD.CD_EMP = EST_PED_VD.CD_EMP AND 
		                      	EST_PROD_TRANSF_PED_VD.CD_FILIAL = EST_PED_VD.CD_FILIAL AND 
								EST_PROD_TRANSF_PED_VD.CD_PED = EST_PED_VD.CD_PED
						   	   WHERE 
								EST_PROD_TRANSF.CD_EMP = @CD_EMP 
								AND EST_PROD_TRANSF.CD_FILIAL = @CD_FILIAL
								AND EST_PROD_TRANSF.CD_CTR = @CD_CTR
								AND EST_PED_VD.STS_PED = 2)

	
SET @STATUS = CASE WHEN @QT_PRE_FAT = 0 THEN
									'PENDENTE'	
							WHEN    @QT_PRE_FAT > @QT_NF THEN
									'PRÉ-FATURA'
							WHEN    @QT_PRE_FAT = @QT_NF THEN
									'FATURADO'
							END
							
IF EXISTS(SELECT 
				T.CD_CTR 
			FROM   
				V_EST_PROD_TRANSF_IT T  
				LEFT JOIN 	(SELECT 
								V.CD_EMP,
								V.CD_FILIAL,
								V.CD_PED,
								T.CD_CTR,
								I.CD_PROD,
								I.QT_IT
							FROM 
								EST_PED_VD V
								INNER JOIN EST_PED_VD_IT I ON 
								V.CD_EMP	= I.CD_EMP AND
								V.CD_FILIAL = I.CD_FILIAL AND
								V.CD_PED	= I.CD_PED
								
								INNER JOIN EST_PROD_TRANSF_PED_VD T ON
								V.CD_EMP	= T.CD_EMP AND
								V.CD_FILIAL = T.CD_FILIAL  AND
								V.CD_PED	= T.CD_PED 

							WHERE
								V.CD_EMP = @CD_EMP 
								AND V.CD_FILIAL = @CD_FILIAL  ) P ON 
				T.CD_EMP	= P.CD_EMP AND
				T.CD_FILIAL = P.CD_FILIAL  AND
				T.CD_CTR	= P.CD_CTR AND
				T.CD_PROD	= P.CD_PROD
			WHERE
				P.CD_EMP IS NULL
				AND T.QT_AT > 0
				AND T.STS_TRANSF IN (1,6)
				AND T.CD_EMP = @CD_EMP 
				AND T.CD_FILIAL = @CD_FILIAL
				AND T.CD_CTR = @CD_CTR )
	BEGIN
		SET @STATUS ='SALDO PENDENTE'
	END					
							
RETURN(@STATUS)
END