 /****** Object:  UserDefinedFunction [dbo].[UFD_RELA_ANALISE_ENT_MERC]    Script Date: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_ANALISE_ENT_MERC]') 
			AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RELA_ANALISE_ENT_MERC]
GO
GO 

/*
	EMPRESA:	DROGARIA ROSARIO
	FUNÇÃO:		UFD_RELA_ANALISE_ENT_MERC
	OBJETIVO:	ANALISE DE ENTRADA DE MERCADORIAS PELO PEDIDO Versus NOTA FISCAL
	DT.INI.:	15/10/2004
	DT.FIM.:	15/10/2004
	RESP.:		CRISTIANO DE JESUS

	SELECT * FROM UFD_RELA_ANALISE_ENT_MERC(1,'99','515','10/20/2004','10/30/2004')

Select  CD_EMP, CD_FILIAL, NM_FANT, CD_FORN, NM_FORN, CD_PED, DT_EMI,  CD_PROD, DS_PROD, DS_UN, QT_IT_PED ,VLR_IT_PED, VLR_DESC_IT_PED, NR_NF, DT_ENT, DT_EMI_NF, QT_IT_NF, VLR_IT_NF, VLR_DESC_IT_NF, DT_PREV_ENT From  UFD_RELA_ANALISE_ENT_MERC (1,'99', '','10/22/2004', '11/03/2004')
*/



CREATE                FUNCTION [dbo].[UFD_RELA_ANALISE_ENT_MERC] ( 
						@CD_EMP		INT,
						@CD_FILIAL	NVARCHAR(2000),
						@CD_FORN	NVARCHAR(2500),
						@DT_INI		DATETIME,
						@DT_FIM		DATETIME
						)



RETURNS @RS_RETORNO TABLE (
				CD_EMP		INT,
				CD_FILIAL	INT,
				NM_FANT		NVARCHAR(150),
				CD_FORN		INT,
				NM_FORN		NVARCHAR(150),
				CD_PED		INT,
				DT_EMI		DATETIME,
				CD_PROD		INT,
				DS_PROD		NVARCHAR(250),
				DS_UN		NVARCHAR(10),
				QT_IT_PED	MONEY,
				VLR_IT_PED	MONEY,
				VLR_DESC_IT_PED	MONEY,
				NR_NF		INT,
				DT_ENT		DATETIME,
				DT_EMI_NF	DATETIME,
				QT_IT_NF	MONEY,
				VLR_IT_NF	MONEY,
				VLR_DESC_IT_NF	MONEY,
				DT_PREV_ENT	DATETIME
			   )



AS
BEGIN




--Tabela de pedido para pesquisa
DECLARE @RS_EST_PED TABLE (
								CD_EMP INT, 
								CD_FILIAL INT,
								CD_PED INT)

--Tabela de pedidos com itens
DECLARE @RS_EST_PED_IT TABLE (
								CD_EMP INT, 
								CD_FILIAL INT,
								CD_PED INT, 
								CD_FORN INT, 
								DT_EMI_PED DATETIME, 
								DT_PREV_CHEG DATETIME, 
								CD_PROD INT, 
								QT_IT MONEY, 
								VLR_IT MONEY, 
								VLR_DESC MONEY)

--Tabela de Notas Fiscais com itens
DECLARE @RS_EST_NF_IT TABLE (
								CD_EMP INT, 
								CD_FILIAL INT,
								CD_PED INT, 
								CD_FORN INT,
								NR_NF INT, 
								DT_EMI_NF DATETIME, 
								DT_ENT_NF DATETIME, 
								CD_PROD INT, 
								QT_IT_NF MONEY, 
								VLR_IT_NF MONEY, 
								VLR_DESC_NF MONEY)

--Tabela de pedidos com itens
DECLARE @RS_ANALISE TABLE (
								CD_EMP 				INT, 	
								CD_FILIAL 			INT,
								CD_PED 				INT, 	
								NR_NF					INT,
								DT_EMI_NF			DATETIME,
								DT_ENT_NF			DATETIME,						
								CD_FORN 				INT, 
								DT_EMI_PED 			DATETIME, 
								DT_PREV_CHEG_PED	DATETIME, 
								CD_PROD 				INT, 
								QT_IT_PED 			MONEY, 
								VLR_IT_PED 			MONEY, 
								VLR_DESC_PED 		MONEY,
								QT_IT_NF 			MONEY, 
								VLR_IT_NF 			MONEY, 
								VLR_DESC_NF 		MONEY)


IF @CD_FORN = ''
	BEGIN
		SET @CD_FORN = '0'
	END

--Buscando pedidos para avaliação do periodo
--IF @CD_FORN = '' -- TODOS OS FORNS
	INSERT INTO @RS_EST_PED
		SELECT     
		   	EST_PED_CP.CD_EMP, 
			EST_PED_CP.CD_FILIAL, 
			EST_PED_CP.CD_PED 
		FROM         
			EST_PED_CP INNER JOIN GLB_MOV_OP ON
				EST_PED_CP.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
			INNER JOIN EST_NF_ENT_PED_CP ON
				EST_PED_CP.CD_EMP = EST_NF_ENT_PED_CP.CD_EMP AND
				EST_PED_CP.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND
				EST_PED_CP.CD_PED = EST_NF_ENT_PED_CP.CD_PED 
			INNER JOIN EST_NF_ENT ON
				EST_NF_ENT.CD_EMP = EST_NF_ENT_PED_CP.CD_EMP AND 
				EST_NF_ENT.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND 
				EST_NF_ENT.CD_NF = EST_NF_ENT_PED_CP.CD_NF 
		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 (CASE WHEN @CD_FORN = '0' THEN @CD_FORN ELSE EST_PED_CP.CD_FORN END) IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FORN))
			AND EST_PED_CP.DT_EMI BETWEEN @DT_INI AND @DT_FIM
			AND GLB_MOV_OP.TP_MOV_OP_TRANSA = 0 
			AND GLB_MOV_OP.TP_MOV = 0

		ORDER BY 
			EST_PED_CP.CD_FILIAL, 
			EST_PED_CP.CD_PED 

/*ELSE -- FORNS SELECIONADOS

	INSERT INTO @RS_EST_PED
		SELECT     
		   	EST_PED_CP.CD_EMP, 
			EST_PED_CP.CD_FILIAL, 
			EST_PED_CP.CD_PED 
		FROM         
			EST_PED_CP, 
			GLB_MOV_OP 
		WHERE
			EST_PED_CP.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP AND
			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.CD_FORN IN(SELECT ID FROM UFD_PRM_IN_STRING(@CD_FORN))
		    	AND EST_PED_CP.DT_EMI BETWEEN @DT_INI AND @DT_FIM
			AND GLB_MOV_OP.TP_MOV_OP_TRANSA = 0 
			AND GLB_MOV_OP.TP_MOV = 0
		ORDER BY 
			EST_PED_CP.CD_FILIAL, 
			EST_PED_CP.CD_PED 

*/
--Fim
--Buscando Pedido com itens
INSERT INTO @RS_EST_PED_IT
	SELECT     
	   	EST_PED_CP.CD_EMP, 
		EST_PED_CP.CD_FILIAL, 
		EST_PED_CP.CD_PED, 
		EST_PED_CP.CD_FORN, 
		EST_PED_CP.DT_EMI, 
		EST_PED_CP.DT_PREV_ENT, 
	   	EST_PED_CP_IT.CD_PROD, 
		(EST_PED_CP_IT.QT_IT*EST_PED_CP_IT.QT_EMB) AS QT_IT, 
		CASE WHEN EST_PED_CP_IT.QT_EMB > 1 THEN 
			EST_PED_CP_IT.VLR_IT / (EST_PED_CP_IT.QT_IT*EST_PED_CP_IT.QT_EMB)
		ELSE 
			EST_PED_CP_IT.VLR_IT
		END AS VLR_IT,
		EST_PED_CP_IT.VLR_DESC
	FROM         
		EST_PED_CP, 
		EST_PED_CP_IT,
		GLB_MOV_OP,
		@RS_EST_PED RS_EST_PED
	WHERE
		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 AND 	
		EST_PED_CP.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP AND
		RS_EST_PED.CD_EMP = EST_PED_CP.CD_EMP AND 
		RS_EST_PED.CD_FILIAL = EST_PED_CP.CD_FILIAL AND 
	   	RS_EST_PED.CD_PED = EST_PED_CP.CD_PED 

--Buscando Notas Fiscais de Entrada
INSERT INTO @RS_EST_NF_IT
SELECT     
	EST_NF_ENT.CD_EMP, 
	EST_NF_ENT.CD_FILIAL, 
	EST_NF_ENT_PED_CP.CD_PED, 
	EST_NF_ENT.CD_FORN, 	
	EST_NF_ENT.NR_NF, 	
	EST_NF_ENT.DT_EMI_NF, 	
	EST_NF_ENT.DT_ENT, 	   
	EST_NF_ENT_IT.CD_PROD, 
	EST_NF_ENT_IT.QT_IT, 
	EST_NF_ENT_IT.VLR_IT, 
	EST_NF_ENT_IT.VLR_DESC
FROM         
	GLB_MOV_OP,
	EST_NF_ENT,
	EST_NF_ENT_IT,
	EST_NF_ENT_PED_CP,
	@RS_EST_PED RS_EST_PED
WHERE
	GLB_MOV_OP.CD_MOV_OP = EST_NF_ENT.CD_NT_MOV AND
	EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP AND 
	EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND 
   	EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF AND
   	EST_NF_ENT.CD_EMP = EST_NF_ENT_PED_CP.CD_EMP AND 
	EST_NF_ENT.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND 
	EST_NF_ENT.CD_NF = EST_NF_ENT_PED_CP.CD_NF AND
	RS_EST_PED.CD_EMP = EST_NF_ENT_PED_CP.CD_EMP AND 
	RS_EST_PED.CD_FILIAL = EST_NF_ENT_PED_CP.CD_FILIAL AND 
	RS_EST_PED.CD_PED = EST_NF_ENT_PED_CP.CD_PED 

--Inserindo produtos em comum do pedido de vendas com a nota fiscal de entrada
INSERT INTO @RS_ANALISE 
	SELECT 
		RS_EST_PED_IT.CD_EMP,
		RS_EST_PED_IT.CD_FILIAL,
		RS_EST_PED_IT.CD_PED,
		RS_EST_NF_IT.NR_NF,
		RS_EST_NF_IT.DT_EMI_NF,
		RS_EST_NF_IT.DT_ENT_NF,
		RS_EST_PED_IT.CD_FORN,
		RS_EST_PED_IT.DT_EMI_PED,
		RS_EST_PED_IT.DT_PREV_CHEG,
		RS_EST_PED_IT.CD_PROD,
		RS_EST_PED_IT.QT_IT,	
		RS_EST_PED_IT.VLR_IT,
		RS_EST_PED_IT.VLR_DESC,
		RS_EST_NF_IT.QT_IT_NF,
		RS_EST_NF_IT.VLR_IT_NF,
		RS_EST_NF_IT.VLR_DESC_NF
	FROM
		@RS_EST_PED_IT RS_EST_PED_IT,
		@RS_EST_NF_IT RS_EST_NF_IT	
	WHERE
		RS_EST_PED_IT.CD_EMP 		= RS_EST_NF_IT.CD_EMP AND
		RS_EST_PED_IT.CD_FILIAL 	= RS_EST_NF_IT.CD_FILIAL AND
		RS_EST_PED_IT.CD_PED		  = RS_EST_NF_IT.CD_PED AND
		RS_EST_PED_IT.CD_PROD    = RS_EST_NF_IT.CD_PROD 


--Buscando produtos que constam na nota fiscal de entrada
--e não constam no pedido de vendas
INSERT INTO @RS_ANALISE 
	SELECT 
		RS_EST_NF_IT.CD_EMP,
		RS_EST_NF_IT.CD_FILIAL,
		RS_EST_NF_IT.CD_PED,
		RS_EST_NF_IT.NR_NF,
		RS_EST_NF_IT.DT_EMI_NF,
		RS_EST_NF_IT.DT_ENT_NF,
		RS_EST_NF_IT.CD_FORN,
		NULL,
		NULL,
		RS_EST_NF_IT.CD_PROD,
		0,	
		0,
		0,
		RS_EST_NF_IT.QT_IT_NF,
		RS_EST_NF_IT.VLR_IT_NF,
		RS_EST_NF_IT.VLR_DESC_NF
	FROM
		@RS_EST_NF_IT RS_EST_NF_IT
	WHERE
		NOT EXISTS (SELECT 							
							CD_PROD
						FROM 
							@RS_EST_PED_IT RS_EST_PED_IT			
						WHERE
							RS_EST_PED_IT.CD_EMP 	  = RS_EST_NF_IT.CD_EMP AND
							RS_EST_PED_IT.CD_FILIAL   = RS_EST_NF_IT.CD_FILIAL AND
							RS_EST_PED_IT.CD_PED		  = RS_EST_NF_IT.CD_PED AND
							RS_EST_PED_IT.CD_PROD     = RS_EST_NF_IT.CD_PROD)

--Buscando produtos do pedido que não 
--constam na nota fiscal de entrada
INSERT INTO @RS_ANALISE 
	SELECT 
		RS_EST_PED_IT.CD_EMP,
		RS_EST_PED_IT.CD_FILIAL,
		RS_EST_PED_IT.CD_PED,
		0,
		NULL,
		NULL,
		RS_EST_PED_IT.CD_FORN,
		RS_EST_PED_IT.DT_EMI_PED,
		RS_EST_PED_IT.DT_PREV_CHEG,
		RS_EST_PED_IT.CD_PROD,
		RS_EST_PED_IT.QT_IT,	
		RS_EST_PED_IT.VLR_IT,
		RS_EST_PED_IT.VLR_DESC,
		0,	
		0,
		0
	FROM
		@RS_EST_PED_IT RS_EST_PED_IT
	WHERE
		NOT EXISTS (SELECT 							
							CD_PROD
						FROM 
							@RS_EST_NF_IT RS_EST_NF_IT								
						WHERE
							RS_EST_PED_IT.CD_EMP 		= RS_EST_NF_IT.CD_EMP AND
							RS_EST_PED_IT.CD_FILIAL 	= RS_EST_NF_IT.CD_FILIAL AND
							RS_EST_PED_IT.CD_PED		  = RS_EST_NF_IT.CD_PED AND
							RS_EST_PED_IT.CD_PROD    = RS_EST_NF_IT.CD_PROD)

--Retorno da função
INSERT INTO @RS_RETORNO
	SELECT 
		RS_ANALISE.CD_EMP,
		RS_ANALISE.CD_FILIAL,
		PRC_FILIAL.NM_FANT,
		RS_ANALISE.CD_FORN,
		PG_FORN.RZ_FORN,
		RS_ANALISE.CD_PED,
		RS_ANALISE.DT_EMI_PED,
		RS_ANALISE.CD_PROD,
		EST_PROD.DS_PROD,
		EST_PROD_UN_MED.DS_UN,
		RS_ANALISE.QT_IT_PED,
		RS_ANALISE.VLR_IT_PED,
		CASE WHEN RS_ANALISE.QT_IT_PED > 0 THEN 
			RS_ANALISE.VLR_DESC_PED / RS_ANALISE.QT_IT_PED
		ELSE
			0
		END,
		RS_ANALISE.NR_NF,
		RS_ANALISE.DT_ENT_NF,
		RS_ANALISE.DT_EMI_NF,
		RS_ANALISE.QT_IT_NF,
		RS_ANALISE.VLR_IT_NF,
		CASE WHEN RS_ANALISE.QT_IT_NF > 0 THEN  
			RS_ANALISE.VLR_DESC_NF / RS_ANALISE.QT_IT_NF
		ELSE
			0
		END,
		RS_ANALISE.DT_PREV_CHEG_PED
	FROM
		@RS_ANALISE	RS_ANALISE,
		PRC_FILIAL,
		PG_FORN,
		EST_PROD,
		EST_PROD_UN_MED
	WHERE
		RS_ANALISE.CD_EMP 		= PRC_FILIAL.CD_EMP		AND
		RS_ANALISE.CD_FILIAL 	= PRC_FILIAL.CD_FILIAL AND
		RS_ANALISE.CD_EMP 		= PG_FORN.CD_EMP  	AND
		RS_ANALISE.CD_FORN 		= PG_FORN.CD_FORN 	AND
		RS_ANALISE.CD_EMP			= EST_PROD.CD_EMP     AND
		RS_ANALISE.CD_PROD 		= EST_PROD.CD_PROD AND
		EST_PROD_UN_MED.CD_EMP = EST_PROD.CD_EMP AND
		EST_PROD_UN_MED.CD_CTR_UN = EST_PROD.CD_UN_CP
	ORDER BY
		RS_ANALISE.CD_EMP,
		RS_ANALISE.CD_FILIAL,
		RS_ANALISE.CD_FORN,
		RS_ANALISE.CD_PED

	

RETURN
END

GO


