 /****** Object:  UserDefinedFunction [dbo].[[UFD_RELA_AUDIT_ELETR_PED_NF]]    Script Date: 01/13/2014 14:32:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_AUDIT_ELETR_PED_NF]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
		DROP FUNCTION [dbo].[UFD_RELA_AUDIT_ELETR_PED_NF]
GO  
SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO 
--SELECT * FROM UFD_RELA_AUDIT_ELETR_PED_NF(1,'1',0,'08/01/2009','09/01/2009',0,0,0,0) WHERE CD_PED = 987
CREATE FUNCTION [dbo].[UFD_RELA_AUDIT_ELETR_PED_NF](
										@CD_EMP		INT,
										@CD_FILIAL  NVARCHAR(2500),
										@PEDIDO		INT,
										@DT_INI		DATETIME,
										@DT_FIM		DATETIME,
										@CD_FORN	INT,
										@SOLICITADO	INT,
										@ATENDIDO	INT,
										@ENTREGUE	INT) 
--SELECT * FROM UFD_RELA_AUDIT_ELETR_PED_NF(1,1,315,'','',515,0,0,1) 
RETURNS @RS_RETORNO  TABLE(
						CD_EMP			INT,
						CD_FILIAL		INT,
						CD_PED			INT,
						CD_FORN			INT,
						RZ_FORN			NVARCHAR(250),
						DT_EMI			DATETIME,
						STS_PED			NVARCHAR(20),
						CD_PROD			INT,
						DS_PROD			NVARCHAR(250),
						DS_UN			NVARCHAR(250),
						QT_SOLICITADA	MONEY,
						QT_ATENDIDA		MONEY,
						QT_ENTREGUE		MONEY,
						QT_CANC_SLD		MONEY,
						CD_DEMANDA      INT,
						APRES_ENT		VARCHAR (9),
						CD_FABRIC		INT,
						NM_FABRIC		VARCHAR(150)			)
AS
BEGIN

INSERT INTO @RS_RETORNO
	SELECT 
		TBL.CD_EMP AS CD_EMP,
		TBL.CD_FILIAL AS CD_FILIAL,
		TBL.CD_PED AS CD_PED,
		TBL.CD_FORN AS CD_FORN,
		PG_FORN.RZ_FORN AS RZ_FORN,
		TBL.DT_EMI AS DT_EMI,
		CASE WHEN TBL.STS_PED = 2 THEN
			'CANCELADO'
		WHEN TBL.STS_PED = 4 THEN
			'PEDIDO PROCESSADO'	
		ELSE
			'ENTREGA EFETUADA'
		END AS STS_PED,
		TBL.CD_PROD AS CD_PROD,
		prod.DS_PROD AS DS_PROD,
		un_med.DS_UN AS DS_UN,
		SUM(TBL.QT_SOLICITADA) AS QT_SOLICITADA,
		SUM(TBL.QT_ATENDIDA) AS QT_ATENDIDA,
		SUM(TBL.QT_ENTREGUE) AS QT_ENTREGUE,
		SUM(TBL.QT_CANC_SLD) AS QT_CANC_SLD,
		TBL.CD_PED_SOL,
		TBL.APRES_ENT	,
		FAB.CD_FABRIC,
		FAB.NM_FABRIC
	FROM
		(
		SELECT
			ped.CD_EMP AS CD_EMP,
			vinc_ped_eltr.CD_FIL_ENTR AS CD_FILIAL,
			vinc_ped_eltr.CD_PED AS CD_PED,
			vinc_ped_eltr.CD_FORN AS CD_FORN,			
			ped.DT_EMI AS DT_EMI,
			ped.STS_PED AS STS_PED,
			ped_eletr.CD_PROD AS CD_PROD,
			ped_eletr.QT_IT_ENV * prod.APRES_QT_CP AS QT_SOLICITADA,
			0 AS QT_ATENDIDA,
			0 AS QT_ENTREGUE,
			0 AS QT_CANC_SLD,
			vinc_ped_eltr.CD_PED_SOL, 
			CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_EMB_CP,0))) + ' x ' + CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_QT_CP,0))) AS APRES_ENT
		FROM
			EST_PED_ELTR_FORN_PED_CP vinc_ped_eltr
			INNER JOIN EST_PED_ELTR_FORN_IT ped_eletr ON
			vinc_ped_eltr.CD_EMP = ped_eletr.CD_EMP
			AND vinc_ped_eltr.CD_PED_SOL = ped_eletr.CD_PED_SOL
			AND vinc_ped_eltr.CD_FORN = ped_eletr.CD_FORN
			AND vinc_ped_eltr.ORD_ENV = ped_eletr.ORD_ENV
			INNER JOIN EST_PROD prod ON
			ped_eletr.CD_EMP = prod.CD_EMP
			AND ped_eletr.CD_PROD = prod.CD_PROD
			INNER JOIN EST_PED_CP ped ON
			vinc_ped_eltr.CD_EMP = ped.CD_EMP
			AND vinc_ped_eltr.CD_FIL_ENTR = ped.CD_FILIAL
			AND vinc_ped_eltr.CD_PED = ped.CD_PED
		WHERE
			(CASE WHEN @CD_FORN > 0 THEN vinc_ped_eltr.CD_FORN ELSE	@CD_FORN END) = @CD_FORN
		UNION ALL
		SELECT
			ped.CD_EMP AS CD_EMP,
			vinc_ped_eltr.CD_FIL_ENTR AS CD_FILIAL,
			vinc_ped_eltr.CD_PED AS CD_PED,
			vinc_ped_eltr.CD_FORN AS CD_FORN,
			ped.DT_EMI AS DT_EMI,
			ped.STS_PED AS STS_PED,
			pedIt.CD_PROD AS CD_PROD,
			ISNULL(pedIt.QT_IT_ENV,0) * prod.APRES_QT_CP  AS QT_SOLICITADA,
			0 AS QT_ATENDIDA,
			0 AS QT_ENTREGUE,
			0 AS QT_CANC_SLD,
			vinc_ped_eltr.CD_PED_SOL,
			--CONVERT(CHAR(1),prod.APRES_EMB_CP) + ' x ' +			CONVERT(char(2),CONVERT(INT,ROUND(prod.APRES_QT_CP,0))) AS APRES_ENT
			CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_EMB_CP,0))) + ' x ' + CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_QT_CP,0))) AS APRES_ENT
		FROM
			EST_PED_ELTR_FORN_PED_CP vinc_ped_eltr
			INNER JOIN EST_PED_CP ped ON
			vinc_ped_eltr.CD_EMP = ped.CD_EMP
			AND vinc_ped_eltr.CD_FIL_ENTR = ped.CD_FILIAL
			AND vinc_ped_eltr.CD_PED = ped.CD_PED
			INNER JOIN EST_PED_ELTR_FORN_IT pedIt ON
			vinc_ped_eltr.CD_EMP = pedIt.CD_EMP
			AND vinc_ped_eltr.CD_PED_SOL = pedIt.CD_PED_SOL
			AND vinc_ped_eltr.CD_FORN = pedIt.CD_FORN
			AND vinc_ped_eltr.ORD_ENV = pedIt.ORD_ENV
			INNER JOIN EST_PROD prod ON
			pedIt.CD_EMP = prod.CD_EMP
			AND pedIt.CD_PROD = prod.CD_PROD
		WHERE
			(CASE WHEN @CD_FORN > 0 THEN vinc_ped_eltr.CD_FORN ELSE	@CD_FORN END) = @CD_FORN
			AND NOT EXISTS (SELECT 
								* 
							FROM 
								EST_PED_ELTR_FORN_IT ped_eletr
							WHERE	
								vinc_ped_eltr.CD_EMP = ped_eletr.CD_EMP
								AND vinc_ped_eltr.CD_PED_SOL = ped_eletr.CD_PED_SOL
								AND vinc_ped_eltr.CD_FORN = ped_eletr.CD_FORN
								AND vinc_ped_eltr.ORD_ENV = ped_eletr.ORD_ENV)
		UNION ALL
		SELECT
			ped.CD_EMP AS CD_EMP,
			ped.CD_FILIAL AS CD_FILIAL,
			ped.CD_PED AS CD_PED,
			ped.CD_FORN AS CD_FORN,
			ped.DT_EMI AS DT_EMI,
			ped.STS_PED AS STS_PED,
			ped_it.CD_PROD AS CD_PROD,
			0 AS QT_SOLICITADA,
			ISNULL(ped_it.QT_IT,0) * ped_it.QT_EMB AS QT_ATENDIDA,
			0 AS QT_ENTREGUE,
			0 AS QT_CANC_SLD,
			ISNULL(vinc_ped_eltr.CD_PED_SOL,0) AS CD_PED_SOL,
			--CONVERT(CHAR(1),prod.APRES_EMB_CP) + ' x ' +			CONVERT(char(2),CONVERT(INT,ROUND(prod.APRES_QT_CP,0))) AS APRES_ENT
			CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_EMB_CP,0))) + ' x ' + CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_QT_CP,0))) AS APRES_ENT
		FROM
			EST_PED_CP ped
			INNER JOIN EST_PED_CP_IT ped_it ON
				ped.CD_EMP = ped_it.CD_EMP
				AND ped.CD_FILIAL = ped_it.CD_FILIAL
				AND ped.CD_PED = ped_it.CD_PED
			LEFT OUTER JOIN EST_PED_ELTR_FORN_PED_CP vinc_ped_eltr ON
				vinc_ped_eltr.CD_EMP = ped.CD_EMP
				AND vinc_ped_eltr.CD_FIL_ENTR = ped.CD_FILIAL
				AND vinc_ped_eltr.CD_PED = ped.CD_PED
			INNER JOIN EST_PROD prod ON
				ped_it.CD_EMP = prod.CD_EMP
				AND ped_it.CD_PROD = prod.CD_PROD
		WHERE
			(CASE WHEN @CD_FORN > 0 THEN ped.CD_FORN ELSE	@CD_FORN END) = @CD_FORN

		UNION ALL
		SELECT
			ped.CD_EMP AS CD_EMP,
			ped.CD_FILIAL AS CD_FILIAL,
			ped.CD_PED AS CD_PED,
			ped.CD_FORN AS CD_FORN,
			ped.DT_EMI AS DT_EMI,
			ped.STS_PED AS STS_PED,
			ped_it.CD_PROD AS CD_PROD,
			0 AS QT_SOLICITADA,
			0 AS QT_ATENDIDA,
			0 AS QT_ENTREGUE,
			ped_it.QT_PROD  AS QT_CANC_SLD,
			vinc_ped_eltr.CD_PED_SOL,
			--CONVERT(CHAR(1),prod.APRES_EMB_CP) + ' x ' +			CONVERT(char(2),CONVERT(INT,ROUND(prod.APRES_QT_CP,0))) AS APRES_ENT
			CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_EMB_CP,0))) + ' x ' + CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_QT_CP,0))) AS APRES_ENT
		FROM
			EST_PED_CP ped
			INNER JOIN EST_PED_CP_IT_CANCEL_SLD ped_it ON
				ped.CD_EMP = ped_it.CD_EMP
				AND ped.CD_FILIAL = ped_it.CD_FILIAL
				AND ped.CD_PED = ped_it.CD_PED
			LEFT OUTER JOIN EST_PED_ELTR_FORN_PED_CP vinc_ped_eltr ON
				vinc_ped_eltr.CD_EMP = ped.CD_EMP
				AND vinc_ped_eltr.CD_FIL_ENTR = ped.CD_FILIAL
				AND vinc_ped_eltr.CD_PED = ped.CD_PED
			INNER JOIN EST_PROD prod ON
				ped_it.CD_EMP = prod.CD_EMP
				AND ped_it.CD_PROD = prod.CD_PROD
		WHERE
			(CASE WHEN @CD_FORN > 0 THEN ped.CD_FORN ELSE	@CD_FORN END) = @CD_FORN

	UNION ALL
		SELECT
			ped.CD_EMP AS CD_EMP,
			vinc_ped_nf.CD_FILIAL AS CD_FILIAL,
			ISNULL(vinc_ped_nf.CD_PED,0) AS CD_PED,
			nf_ent.CD_FORN AS CD_FORN,
			ped.DT_EMI AS DT_EMI,
			ped.STS_PED AS STS_PED,
			nf_ent_it.CD_PROD AS CD_PROD,
			0 AS QT_SOLICITADA,
			0 AS QT_ATENDIDA,
			nf_ent_it.QT_IT AS QT_ENTREGUE,
			0 AS QT_CANC_SLD,
			ISNULL(vinc_ped_eltr.CD_PED_SOL,0) AS CD_PED_SOL,
			--CONVERT(CHAR(1),prod.APRES_EMB_CP) + ' x ' +			CONVERT(char(2),CONVERT(INT,ROUND(prod.APRES_QT_CP,0))) AS APRES_ENT
			CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_EMB_CP,0))) + ' x ' + CONVERT(VARCHAR,CONVERT(INT,ROUND(PROD.APRES_QT_CP,0))) AS APRES_ENT
		FROM
			EST_NF_ENT_PED_CP vinc_ped_nf
			INNER JOIN EST_PED_CP ped ON
			vinc_ped_nf.CD_EMP = ped.CD_EMP
			AND vinc_ped_nf.CD_FILIAL = ped.CD_FILIAL
			AND vinc_ped_nf.CD_PED = ped.CD_PED
			INNER JOIN EST_NF_ENT nf_ent ON
			vinc_ped_nf.CD_EMP = nf_ent.CD_EMP
			AND vinc_ped_nf.CD_FILIAL = nf_ent.CD_FILIAL
			AND vinc_ped_nf.CD_NF = nf_ent.CD_NF
			INNER JOIN EST_NF_ENT_IT nf_ent_it ON
			nf_ent.CD_EMP = nf_ent_it.CD_EMP
			AND nf_ent.CD_FILIAL= nf_ent_it.CD_FILIAL
			AND nf_ent.CD_NF = nf_ent_it.CD_NF
			LEFT OUTER JOIN EST_PED_ELTR_FORN_PED_CP vinc_ped_eltr ON
			vinc_ped_eltr.CD_EMP = ped.CD_EMP
			AND vinc_ped_eltr.CD_FIL_ENTR = ped.CD_FILIAL
			AND vinc_ped_eltr.CD_PED = ped.CD_PED
			INNER JOIN EST_PROD prod ON
			nf_ent_it.CD_EMP = prod.CD_EMP
			AND nf_ent_it.CD_PROD = prod.CD_PROD
		WHERE
			(CASE WHEN @CD_FORN > 0 THEN nf_ent.CD_FORN ELSE @CD_FORN END) = @CD_FORN

	)TBL

	INNER JOIN EST_PROD prod on
	TBL.CD_EMP = prod.CD_EMP
	AND TBL.CD_PROD = prod.CD_PROD

	INNER JOIN EST_PROD_FABRIC  FAB on
	prod.CD_FABRIC = FAB.CD_FABRIC

	INNER JOIN EST_PROD_UN_MED un_med ON
	prod.CD_EMP = un_med.CD_EMP
	AND prod.CD_UN_VD = un_med.CD_CTR_UN
	INNER JOIN PG_FORN ON
	TBL.CD_EMP = PG_FORN.CD_EMP
	AND TBL.CD_FORN = PG_FORN.CD_FORN

	WHERE
		TBL.CD_EMP = @CD_EMP
		AND TBL.CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))
		AND (TBL.DT_EMI BETWEEN @DT_INI AND @DT_FIM OR TBL.CD_PED = @PEDIDO)
		
	GROUP BY
		TBL.CD_EMP,
		TBL.CD_FILIAL,
		TBL.CD_PED,
		TBL.CD_FORN,
		PG_FORN.RZ_FORN,
		TBL.DT_EMI,
		TBL.STS_PED,
		TBL.CD_PROD,
		prod.DS_PROD,
		FAB.CD_FABRIC,
		FAB.NM_FABRIC,
		un_med.DS_UN,
		CD_PED_SOL,
		TBL.APRES_ENT
	HAVING
		SUM(TBL.QT_SOLICITADA) >= @SOLICITADO
		AND SUM(TBL.QT_ATENDIDA) >= @ATENDIDO
		AND SUM(TBL.QT_ENTREGUE) >= @ENTREGUE
									
RETURN
END




--select top 100 * from EST_PED_ELTR_FORN_PED_CP where cd_ped = 10703

GO


