IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PED_VD_FAT]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[P_EST_PED_VD_FAT]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[P_EST_PED_VD_FAT] (@CD_EMP INT, @CD_FILIAL INT) AS
	

		SET NOCOUNT ON;

		SELECT
		EST_PED_VD.CD_EMP 
		, EST_PED_VD.CD_FILIAL 
		, EST_PED_VD.CD_PED 
		, EST_PED_VD.DT_EMI_PED 
		, EST_PED_VD.CD_CLI 
		  , GLB_MOV_OP.DS_MOV_OP_USUAL AS DS_MOV_OP 
		  , EST_PED_VD.VLR_TOT_PED 
		  , NM_DEST = CASE
						  WHEN DEST_ORIG = 0 AND TP_PRECO IN(1, 2, 4) 
							THEN(
								SELECT
									PRC_FILIAL.NM_FANT
								FROM
									PRC_FILIAL
								  , PRC_FILIAL_RC_CLI
								WHERE  PRC_FILIAL.CD_EMP = PRC_FILIAL_RC_CLI.CD_EMP
									   AND PRC_FILIAL.CD_FILIAL = PRC_FILIAL_RC_CLI.CD_FILIAL
									   AND PRC_FILIAL_RC_CLI.CD_EMP = EST_PED_VD.CD_EMP
									   AND PRC_FILIAL_RC_CLI.CD_CLI = EST_PED_VD.CD_CLI
								)
						  WHEN DEST_ORIG = 0 AND TP_PRECO = 0 
							THEN(
								SELECT
									RZ_CLI
								FROM
									RC_CLI
								WHERE  RC_CLI.CD_EMP = EST_PED_VD.CD_EMP
									   AND RC_CLI.CD_CLI = EST_PED_VD.CD_CLI
							)
						  WHEN DEST_ORIG = 0 AND TP_PRECO = 3 
							THEN(
								SELECT
									PRC_FILIAL.NM_FANT
								FROM
									PRC_FILIAL
								  , PRC_FILIAL_RC_CLI
								WHERE  PRC_FILIAL.CD_EMP = PRC_FILIAL_RC_CLI.CD_EMP
									   AND PRC_FILIAL.CD_FILIAL = PRC_FILIAL_RC_CLI.CD_FILIAL
									   AND PRC_FILIAL_RC_CLI.CD_EMP = EST_PED_VD.CD_EMP
									   AND PRC_FILIAL_RC_CLI.CD_CLI = EST_PED_VD.CD_CLI
							)
						  WHEN DEST_ORIG = 1 
							THEN(
								SELECT
									RZ_FORN
								FROM
									PG_FORN
								WHERE  PG_FORN.CD_EMP = EST_PED_VD.CD_EMP
									   AND PG_FORN.CD_FORN = EST_PED_VD.CD_CLI
							)
						  WHEN DEST_ORIG = 2 
							THEN(
								SELECT
								   NM_FANT
								FROM
									PRC_FILIAL
								WHERE  PRC_FILIAL.CD_EMP = EST_PED_VD.CD_EMP
									   AND PRC_FILIAL.CD_FILIAL = EST_PED_VD.CD_CLI
							)
						  ELSE 'DESTINATARIO SEM CLASSIFICACAO, SOLICITE AO DEPTO DE INFORMATICA A CLASSIFICACAO DO MESMO'
					  END 
	
		  , NR_NF = CASE
						WHEN ISNULL(EST_NF_SAI.NF_NF, 0) > 0 THEN ISNULL(EST_NF_SAI.NF_NF, 0)
						WHEN ISNULL(EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_REQ, 0) > 0 THEN ISNULL(EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_REQ, 0)
						WHEN ISNULL(EST_NF_ENT.NR_NF, 0) > 0 THEN ISNULL(EST_NF_ENT.NR_NF, 0)
						ELSE 0
					END 
		  , CASE
				WHEN P.CD_PED IS NULL 
					THEN CASE
						   WHEN GLB_MOV_OP.TP_MOV_OP_TRANSA = 0 
							THEN CASE
									 WHEN ISNULL(EST_NF_ENT.NR_NF, 0) > 0 THEN 1
									 ELSE STS_PED
								 END
						   ELSE STS_PED
	                     END
				ELSE 0
			END STS_PED 
		  , ISNULL(EST_NF_SAI.STS_NF, EST_NF_ENT.STS_NF) AS STS_NF 
		  , EST_PED_VD.TP_PED 
		  , CASE
				WHEN EST_PED_VD.TP_PED = 11 
/**************************************************************************************************************************
	    Por transposicao de estoque
**************************************************************************************************************************/
					THEN
						CASE
						   WHEN PRC_EMP.FLAG_TIPO_TRANSF_CONSUMO = 1 
							THEN CASE
									WHEN NOT(
										SELECT TOP 1
											   EST_PED_VD_TRANSPOSICAO.CD_PED
										FROM
											EST_PED_VD_TRANSPOSICAO
										WHERE  EST_PED_VD.CD_EMP = EST_PED_VD_TRANSPOSICAO.CD_EMP
											   AND EST_PED_VD.CD_FILIAL = EST_PED_VD_TRANSPOSICAO.CD_FILIAL
											   AND EST_PED_VD.CD_PED = EST_PED_VD_TRANSPOSICAO.CD_PED
									) IS NULL 
										THEN CASE
												WHEN NOT(
													SELECT TOP 1
														   sai_transposicao.CD_NF
													FROM
														EST_PED_VD_TRANSPOSICAO
														INNER JOIN EST_NF_SAI_PED_VD sai_transposicao ON EST_PED_VD_TRANSPOSICAO.CD_EMP = sai_transposicao.CD_EMP
																										 AND EST_PED_VD_TRANSPOSICAO.CD_FILIAL = sai_transposicao.CD_FILIAL
																										 AND EST_PED_VD_TRANSPOSICAO.CD_PED_TRANSPOSICAO = sai_transposicao.CD_PED
													WHERE  EST_PED_VD.CD_EMP = EST_PED_VD_TRANSPOSICAO.CD_EMP
														   AND EST_PED_VD.CD_FILIAL = EST_PED_VD_TRANSPOSICAO.CD_FILIAL
														   AND EST_PED_VD.CD_PED = EST_PED_VD_TRANSPOSICAO.CD_PED
													) IS NULL THEN 0
												ELSE 1
											END
										ELSE 1
								 END
						   ELSE 0
					   END
				ELSE 0
			END FAT_LIBERADO 
		  , CASE
				WHEN ISNULL(EST_PED_VD.FLAG_REALIZAR_FAT, 0) = 0 
					THEN CASE
							WHEN ISNULL(t.CD_CTR, 0) = 0 
								THEN ISNULL(c.CD_PED, 0)
							ELSE ISNULL(t.CD_CTR, 0)
						  END
					ELSE EST_PED_VD.CD_PED
			END AS PRE_CONF 
		  , GLB_MOV_OP.BX_EST 
		  , ISNULL(EST_PED_VD.BLOQ_FATURAMENTO, 0) BLOQ_FATURAMENTO 
		  , ISNULL(EST_PED_VD.FLAG_VALID_TRIB, 0) AS FLAG_VALID_TRIB 
		  ,ISNULL(r.TIPO,0) AS TIPO_RESSARC_ADJUDICACAO 

		  INTO #Valores
		FROM
		GLB_MOV_OP
		INNER JOIN EST_PED_VD ON 
							GLB_MOV_OP.CD_MOV_OP = EST_PED_VD.CD_NT_MOV
		 INNER JOIN PRC_FILIAL filialOrig ON 
							EST_PED_VD.CD_EMP	 = filialOrig.CD_EMP
						AND EST_PED_VD.CD_FILIAL = filialOrig.CD_FILIAL
		 INNER JOIN PRC_EMP ON 
						EST_PED_VD.CD_EMP = PRC_EMP.CD_EMP
		 INNER JOIN PRC_GRP_ECON_PRC_FILIAL econfilialorig ON 
							EST_PED_VD.CD_EMP		= econfilialorig.CD_EMP
						AND EST_PED_VD.CD_FILIAL	= econfilialorig.CD_FILIAL
		LEFT JOIN EST_NF_SAI_PED_VD ON EST_PED_VD.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP
																						   AND EST_PED_VD.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL
																						   AND EST_PED_VD.CD_PED = EST_NF_SAI_PED_VD.CD_PED
							 
		LEFT JOIN EST_NF_SAI ON EST_NF_SAI.CD_EMP		= EST_NF_SAI_PED_VD.CD_EMP
						AND EST_NF_SAI.CD_FILIAL	= EST_NF_SAI_PED_VD.CD_FILIAL
						AND EST_NF_SAI.CD_NF		= EST_NF_SAI_PED_VD.CD_NF
		LEFT JOIN EST_PED_VD_PENDENCIA P ON 
							EST_PED_VD.CD_EMP		= P.CD_EMP
						AND EST_PED_VD.CD_FILIAL	= P.CD_FILIAL
						AND EST_PED_VD.CD_PED		= P.CD_PED
		LEFT OUTER JOIN EST_PROD_REQ_TRANSF_LIB_EST_PED_VD ON EST_PED_VD.CD_EMP		= EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_EMP
															   AND EST_PED_VD.CD_FILIAL = EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_FILIAL
															   AND EST_PED_VD.CD_PED	= EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_PED
		

		LEFT JOIN EST_NF_ENT_PED_VD ON EST_PED_VD.CD_EMP	= EST_NF_ENT_PED_VD.CD_EMP
																				  AND EST_PED_VD.CD_FILIAL = EST_NF_ENT_PED_VD.CD_FILIAL
																				  AND EST_PED_VD.CD_PED	= EST_NF_ENT_PED_VD.CD_PED
		LEFT OUTER JOIN EST_NF_ENT ON EST_NF_ENT.CD_EMP		= EST_NF_ENT_PED_VD.CD_EMP
									AND EST_NF_ENT.CD_FILIAL	= EST_NF_ENT_PED_VD.CD_FILIAL
									AND EST_NF_ENT.CD_NF		= EST_NF_ENT_PED_VD.CD_NF
		LEFT JOIN EST_PED_VD_CONF c ON 
							EST_PED_VD.CD_EMP		= c.CD_EMP
						AND EST_PED_VD.CD_FILIAL	= c.CD_FILIAL
						AND EST_PED_VD.CD_PED		= c.CD_PED
		 
		LEFT JOIN EST_PED_VD_DEV_RESSARCIMENTO  r ON 
							EST_PED_VD.CD_EMP			= r.CD_EMP
							AND EST_PED_VD.CD_FILIAL	= r.CD_FILIAL
							AND EST_PED_VD.CD_PED		= r.CD_PED

		outer apply ( SELECT TOP 1
							   CD_EMP
							 , CD_FILIAL
							 , MAX(CD_CTR) CD_CTR
							 , CD_PED
						FROM
							EST_PROD_TRANSF_PED_VD t
						WHERE
							   EST_PED_VD.CD_EMP	= t.CD_EMP
						   AND EST_PED_VD.CD_FILIAL = t.CD_FILIAL
						   AND EST_PED_VD.CD_PED	= t.CD_PED
						GROUP BY
								 CD_EMP
							   , CD_FILIAL
							   , CD_PED
					) t  
		WHERE EST_PED_VD.CD_EMP = @CD_EMP
      AND EST_PED_VD.CD_FILIAL = @CD_FILIAL
      AND ISNULL(BLOQ_FATURAMENTO, 0) = 0
      AND STS_PED = 0
      AND CASE
				WHEN ISNULL(EST_PED_VD.FLAG_REALIZAR_FAT, 0) = 0 
					THEN CASE
							WHEN ISNULL(t.CD_CTR, 0) = 0 
								THEN ISNULL(c.CD_PED, 0)
							ELSE ISNULL(t.CD_CTR, 0)
						  END
					ELSE EST_PED_VD.CD_PED
			END = 0
      AND ISNULL(r.TIPO,0) = 0

	  select CD_PED, 
       DT_EMI_PED, 
       CD_CLI, 
       NM_DEST, 
       DS_MOV_OP, 
       NR_NF, 
       VLR_TOT_PED, 
       STS_NF, 
       STS_PED, 
       PRE_CONF, 
       TP_PED, 
       BX_EST, 
       FLAG_VALID_TRIB
	  from #Valores T
	  where FAT_LIBERADO = 0
	  
				  AND NOT EXISTS
			(
				SELECT CD_PED
				FROM EST_PROD_REQ_TRANSF_LIB_EST_PED_VD
				WHERE EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_EMP = T.CD_EMP
					  AND EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_FILIAL = T.CD_FILIAL
					  AND EST_PROD_REQ_TRANSF_LIB_EST_PED_VD.CD_PED = T.CD_PED
			)
		ORDER BY CD_PED;

		DROP TABLE #Valores;
--GO


