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
	)
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
	-----------------------------------------------------
	-- 1: Pedidos eletrônicos enviados
	-----------------------------------------------------
		;

	WITH PED_ELTR
	AS (
		SELECT PED.CD_EMP
			,VINC.CD_FIL_ENTR AS CD_FILIAL
			,VINC.CD_PED
			,VINC.CD_FORN
			,PED.DT_EMI
			,PED.STS_PED
			,PED_IT.CD_PROD
			,PED_IT.QT_IT_ENV * PROD.APRES_QT_CP AS QT_SOLICITADA
			,0 AS QT_ATENDIDA
			,0 AS QT_ENTREGUE
			,0 AS QT_CANC_SLD
			,VINC.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
		INNER JOIN EST_PED_ELTR_FORN_IT PED_IT ON VINC.CD_EMP = PED_IT.CD_EMP
			AND VINC.CD_PED_SOL = PED_IT.CD_PED_SOL
			AND VINC.CD_FORN = PED_IT.CD_FORN
			AND VINC.ORD_ENV = PED_IT.ORD_ENV
		INNER JOIN EST_PROD PROD ON PED_IT.CD_EMP = PROD.CD_EMP
			AND PED_IT.CD_PROD = PROD.CD_PROD
		INNER JOIN EST_PED_CP PED ON VINC.CD_EMP = PED.CD_EMP
			AND VINC.CD_FIL_ENTR = PED.CD_FILIAL
			AND VINC.CD_PED = PED.CD_PED
		WHERE (
				@CD_FORN = 0
				OR VINC.CD_FORN = @CD_FORN
				)
		)
		,
		-----------------------------------------------------
		-- 2: Pedidos eletrônicos não enviados (sem IT)
		-----------------------------------------------------
	PED_ELTR_SEM_IT
	AS (
		SELECT PED.CD_EMP
			,VINC.CD_FIL_ENTR AS CD_FILIAL
			,VINC.CD_PED
			,VINC.CD_FORN
			,PED.DT_EMI
			,PED.STS_PED
			,PED_IT.CD_PROD
			,ISNULL(PED_IT.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.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
		INNER JOIN EST_PED_CP PED ON VINC.CD_EMP = PED.CD_EMP
			AND VINC.CD_FIL_ENTR = PED.CD_FILIAL
			AND VINC.CD_PED = PED.CD_PED
		INNER JOIN EST_PED_ELTR_FORN_IT PED_IT ON VINC.CD_EMP = PED_IT.CD_EMP
			AND VINC.CD_PED_SOL = PED_IT.CD_PED_SOL
			AND VINC.CD_FORN = PED_IT.CD_FORN
			AND VINC.ORD_ENV = PED_IT.ORD_ENV
		INNER JOIN EST_PROD PROD ON PED_IT.CD_EMP = PROD.CD_EMP
			AND PED_IT.CD_PROD = PROD.CD_PROD
		WHERE (
				@CD_FORN = 0
				OR VINC.CD_FORN = @CD_FORN
				)
			AND NOT EXISTS (
				SELECT 1
				FROM EST_PED_ELTR_FORN_IT X
				WHERE X.CD_EMP = VINC.CD_EMP
					AND X.CD_PED_SOL = VINC.CD_PED_SOL
					AND X.CD_FORN = VINC.CD_FORN
					AND X.ORD_ENV = VINC.ORD_ENV
				)
		)
		,
		-----------------------------------------------------
		-- 3: Pedidos físicos atendidos
		-----------------------------------------------------
	PED_FISICO
	AS (
		SELECT PED.CD_EMP
			,PED.CD_FILIAL
			,PED.CD_PED
			,PED.CD_FORN
			,PED.DT_EMI
			,PED.STS_PED
			,PED_IT.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.CD_PED_SOL, 0) AS 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_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 JOIN EST_PED_ELTR_FORN_PED_CP VINC ON VINC.CD_EMP = PED.CD_EMP
			AND VINC.CD_FIL_ENTR = PED.CD_FILIAL
			AND VINC.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 (
				@CD_FORN = 0
				OR PED.CD_FORN = @CD_FORN
				)
		)
		,
		-----------------------------------------------------
		-- 4: Pedidos cancelados (saldo)
		-----------------------------------------------------
	PED_CANCEL
	AS (
		SELECT PED.CD_EMP
			,PED.CD_FILIAL
			,PED.CD_PED
			,PED.CD_FORN
			,PED.DT_EMI
			,PED.STS_PED
			,PED_IT.CD_PROD
			,0 AS QT_SOLICITADA
			,0 AS QT_ATENDIDA
			,0 AS QT_ENTREGUE
			,PED_IT.QT_PROD AS QT_CANC_SLD
			,VINC.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_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 JOIN EST_PED_ELTR_FORN_PED_CP VINC ON VINC.CD_EMP = PED.CD_EMP
			AND VINC.CD_FIL_ENTR = PED.CD_FILIAL
			AND VINC.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 (
				@CD_FORN = 0
				OR PED.CD_FORN = @CD_FORN
				)
		)
		,
		-----------------------------------------------------
		-- 5: Entradas (NF)
		-----------------------------------------------------
	NF_ENT
	AS (
		SELECT PED.CD_EMP
			,VINC.CD_FILIAL
			,ISNULL(VINC.CD_PED, 0) AS CD_PED
			,NF.CD_FORN
			,PED.DT_EMI
			,PED.STS_PED
			,NF_IT.CD_PROD
			,0 AS QT_SOLICITADA
			,0 AS QT_ATENDIDA
			,NF_IT.QT_IT AS QT_ENTREGUE
			,0 AS QT_CANC_SLD
			,ISNULL(VINC_ELTR.CD_PED_SOL, 0) AS 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_NF_ENT_PED_CP VINC
		INNER JOIN EST_PED_CP PED ON VINC.CD_EMP = PED.CD_EMP
			AND VINC.CD_FILIAL = PED.CD_FILIAL
			AND VINC.CD_PED = PED.CD_PED
		INNER JOIN EST_NF_ENT NF ON VINC.CD_EMP = NF.CD_EMP
			AND VINC.CD_FILIAL = NF.CD_FILIAL
			AND VINC.CD_NF = NF.CD_NF
		INNER JOIN EST_NF_ENT_IT NF_IT ON NF.CD_EMP = NF_IT.CD_EMP
			AND NF.CD_FILIAL = NF_IT.CD_FILIAL
			AND NF.CD_NF = NF_IT.CD_NF
		LEFT JOIN EST_PED_ELTR_FORN_PED_CP VINC_ELTR ON VINC_ELTR.CD_EMP = PED.CD_EMP
			AND VINC_ELTR.CD_FIL_ENTR = PED.CD_FILIAL
			AND VINC_ELTR.CD_PED = PED.CD_PED
		INNER JOIN EST_PROD PROD ON NF_IT.CD_EMP = PROD.CD_EMP
			AND NF_IT.CD_PROD = PROD.CD_PROD
		WHERE (
				@CD_FORN = 0
				OR NF.CD_FORN = @CD_FORN
				)
		)
	-----------------------------------------------------
	-- Consolidação final
	-----------------------------------------------------
	INSERT INTO @RS_RETORNO
	SELECT T.CD_EMP
		,T.CD_FILIAL
		,T.CD_PED
		,T.CD_FORN
		,F.RZ_FORN
		,MAX(T.DT_EMI) AS DT_EMI
		,CASE MAX(T.STS_PED)
			WHEN 2
				THEN 'CANCELADO'
			WHEN 4
				THEN 'PEDIDO PROCESSADO'
			ELSE 'ENTREGA EFETUADA'
			END AS STS_PED
		,T.CD_PROD
		,MAX(P.DS_PROD) AS DS_PROD
		,MAX(U.DS_UN) AS DS_UN
		,SUM(T.QT_SOLICITADA) AS QT_SOLICITADA
		,SUM(T.QT_ATENDIDA) AS QT_ATENDIDA
		,SUM(T.QT_ENTREGUE) AS QT_ENTREGUE
		,SUM(T.QT_CANC_SLD) AS QT_CANC_SLD
		,MAX(T.CD_PED_SOL) AS CD_PED_SOL
		,MAX(T.APRES_ENT) AS APRES_ENT
		,MAX(FAB.CD_FABRIC) AS CD_FABRIC
		,MAX(FAB.NM_FABRIC) AS NM_FABRIC
	FROM (
		SELECT *
		FROM PED_ELTR
		
		UNION ALL
		
		SELECT *
		FROM PED_ELTR_SEM_IT
		
		UNION ALL
		
		SELECT *
		FROM PED_FISICO
		
		UNION ALL
		
		SELECT *
		FROM PED_CANCEL
		
		UNION ALL
		
		SELECT *
		FROM NF_ENT
		) AS T
	INNER JOIN EST_PROD P ON T.CD_EMP = P.CD_EMP
		AND T.CD_PROD = P.CD_PROD
	INNER JOIN EST_PROD_FABRIC FAB ON P.CD_FABRIC = FAB.CD_FABRIC
	INNER JOIN EST_PROD_UN_MED U ON P.CD_EMP = U.CD_EMP
		AND P.CD_UN_VD = U.CD_CTR_UN
	INNER JOIN PG_FORN F ON T.CD_EMP = F.CD_EMP
		AND T.CD_FORN = F.CD_FORN
	WHERE T.CD_EMP = @CD_EMP
		AND T.CD_FILIAL IN (
			SELECT ID
			FROM UFD_PRM_IN_STRING(@CD_FILIAL)
			)
		AND (
			T.DT_EMI BETWEEN @DT_INI
				AND @DT_FIM
			OR T.CD_PED = @PEDIDO
			)
	GROUP BY T.CD_EMP
		,T.CD_FILIAL
		,T.CD_PED
		,T.CD_FORN
		,F.RZ_FORN
		,T.CD_PROD
	HAVING (
			SUM(T.QT_SOLICITADA) >= @SOLICITADO
			AND SUM(T.QT_ATENDIDA) >= @ATENDIDO
			AND SUM(T.QT_ENTREGUE) >= @ENTREGUE
			)
		OR (
			SUM(T.QT_ATENDIDA) <> SUM(T.QT_ENTREGUE)
			OR (
				SUM(T.QT_CANC_SLD) > 0
				AND SUM(T.QT_ENTREGUE) = 0
				)
			OR (
				SUM(T.QT_ENTREGUE) > 0
				AND SUM(T.QT_ENTREGUE) < SUM(T.QT_ATENDIDA)
				)
			);

	RETURN
END
GO


