IF NOT OBJECT_ID('P_INSERT_DEMANDA_BY_DANFE_PROVISORIO') IS NULL
	BEGIN
			DROP PROCEDURE P_INSERT_DEMANDA_BY_DANFE_PROVISORIO
	END
GO

CREATE PROCEDURE [dbo].[P_INSERT_DEMANDA_BY_DANFE_PROVISORIO] (
										@CD_CTR INT,
										@CD_EMP INT,
										@CD_FILIAL INT,
										@CD_FORN INT,
										@CD_PED_FORN INT,
										@CD_USU INT)
AS 
BEGIN
	SET NOCOUNT ON;


	DECLARE @CD_PED_SOL INT = 0
	DECLARE @CD_GRP_PED INT = 0
	DECLARE @CD_PED INT = 0
	DECLARE @ORD_ENV INT = 0

	SET @CD_GRP_PED = COALESCE((SELECT COALESCE(MAX(CD_GRP_PED), 0) + 1 FROM EST_PED_ELTR_GRP_PED WITH (NOLOCK) WHERE CD_EMP = @CD_EMP), 1);

	INSERT INTO EST_PED_ELTR_GRP_PED
				(CD_EMP,
				CD_GRP_PED,
				DT_GRP_PED,
				STS_GRP_PED,
				CD_USU,
				DT_CAD,
				DS_GRP_PED)
				
	VALUES      (@CD_EMP,
				@CD_GRP_PED, 
				CONVERT(DATE, GETDATE(), 102), 
				0, 
				@CD_USU, 
				CONVERT(DATE, GETDATE(), 102), 
				'PEDIDO PELA IMPORTACAO DE DANFE')

			
	SET @CD_PED_SOL = COALESCE((SELECT COALESCE(MAX(CD_PED_SOL), 0) + 1 FROM EST_PED_ELTR WITH (NOLOCK) WHERE CD_EMP = @CD_EMP), 1);
          
	INSERT INTO EST_PED_ELTR
				(CD_EMP,
				CD_PED_SOL,
				CD_FILIAL,
				DT_PED,
				DT_ENTR,
				CD_FIL_FAT,
			    CD_FIL_ENTR,
			    STS_PED,
			    DT_CAD,
			    CD_USU,
			    TP_REQ,
			    FLAG_FECHAR,
			    HORA_ENTREGA,
			    HORA_DEMANDA,
			    FLAG_ESTOQUE_ZERADO,
			    PERC_AUMENTO,
			    FLAG_PERC_AUMENTO,
			    FLAG_GERAR_CROSS,
			    CD_CTR_DEMANDA,
			    IMPORTACAO,
			    DT_FATURAMENTO)
				
	VALUES  	(@CD_EMP,
				 @CD_PED_SOL,
				 @CD_FILIAL,
				 CONVERT(DATE, GETDATE(), 102),
				 CONVERT(DATE, GETDATE(), 102),
				 @CD_FILIAL,
				 @CD_FILIAL,
				 0,
				 CONVERT(DATE, GETDATE(), 102),
				 @CD_USU,
				 0, 
				 0,
				 CONVERT(DATE, GETDATE(), 102),
				 CONVERT(DATE, GETDATE(), 102),
				 0,
				 0,
				 0,
				 0,
				 0,
				 0,
				 CONVERT(DATE, GETDATE(), 102))
			 

	-- VINCULO DE EST_PED_ELTR COM EST_PED_ELTR_GRP_PED
	INSERT INTO EST_PED_ELTR_GRP_PED_EST_PED_ELTR
		(CD_EMP,
		CD_GRP_PED,
		CD_PED_SOL)
		
	VALUES      
		(@CD_EMP,
		@CD_GRP_PED,
		@CD_PED_SOL)
			

	-- VINCULO DE PRODUTO
	INSERT INTO EST_PED_ELTR_IT
			  (CD_EMP,
			   CD_PED_SOL,
			   CD_PROD,
			   QT_IT_UN,
			   QT_IT,
			   VLR_PROD,
			   PERC_DESC,
			   CD_COND_COMPRA,
			   UNIDADE,
			   QTDE_MEDIAF,
			   QTDE_BASE_ALCADA,
			   QTDE_ALCADA,
			   VLR_ALCADA,
			   PERC_VLR_ALCADA,
			   COBERTURA,
			   COBERTURA_ALCADA,
			   PERC_COBERTURA_ALCADA)
			   
	SELECT  @CD_EMP,
			@CD_PED_SOL,
			A.CD_PROD,
			ROUND(A.QTDE_PROD,0) AS QT_IT_UN,
			

			 CASE WHEN ROUND(A.QTDE_PROD/(CASE WHEN ISNULL(B.QT_EMB_FECHADA_CP, 0) > 0 THEN B.QT_EMB_FECHADA_CP ELSE 1 END),0) > 0
				THEN
					ROUND(A.QTDE_PROD/(CASE WHEN ISNULL(B.QT_EMB_FECHADA_CP, 0) > 0 THEN B.QT_EMB_FECHADA_CP ELSE 1 END),0)
				ELSE
					 CASE WHEN ROUND(A.QTDE_PROD/(CASE WHEN ISNULL(B.QT_EMB_ALTERNATIVA_CP, 0) > 0 THEN B.QT_EMB_ALTERNATIVA_CP ELSE 1 END),0) > 0
						THEN
							ROUND(A.QTDE_PROD/(CASE WHEN ISNULL(B.QT_EMB_ALTERNATIVA_CP, 0) > 0 THEN B.QT_EMB_ALTERNATIVA_CP ELSE 1 END),0)
						ELSE
							ROUND(A.QTDE_PROD, 0)
					  END
			 END AS QT_IT,
			
			
			A.VLR_PROD_UNIT	AS VLR_PROD,
			A.PERC_PROD_DESC	AS PERC_DESC,
			NULL	AS CD_COND_COMPRA,
			1	AS UNIDADE,
			0	AS QTDE_MEDIAF,
			0	AS QTDE_BASE_ALCADA,
			0	AS QTDE_ALCADA,
			0	AS VLR_ALCADA,
			0	AS PERC_VLR_ALCADA,
			0	AS COBERTURA,
			0	AS COBERTURA_ALCADA,
			0	AS PERC_COBERTURA_ALCADA
		FROM   	EST_NF_ENT_DANFE_PROVISORIO_ITENS A
		INNER JOIN EST_PROD B ON B.CD_EMP = A.CD_EMP AND B.CD_PROD = A.CD_PROD
		WHERE   A.CD_CTR = @CD_CTR AND A.CD_EMP = @CD_EMP
						
						
						 
	-- INSERINDO PEDIDO DE COMPRA						   
	SET @CD_PED = COALESCE((SELECT COALESCE(Max(CD_PED), 0) + 1
												FROM   EST_PED_CP WITH (NOLOCK)
												WHERE  CD_EMP = @CD_EMP
													   AND CD_FILIAL = @CD_FILIAL), 1);
													   
	SET @ORD_ENV = COALESCE((SELECT COALESCE(Max(ORD_ENV), 0) + 1
							 FROM   EST_PED_ELTR_FORN WITH (NOLOCK)
							 WHERE  CD_EMP = @CD_EMP
									AND CD_PED_SOL = @CD_PED_SOL), 1);

	INSERT INTO EST_PED_CP
				(CD_EMP,
				 CD_FILIAL,
				 CD_PED,
				 CD_FORN,
				 DT_EMI,
				 VLR_PED,
				 VLR_TOT_PROD,
				 VLR_DESC,
				 DT_PREV_ENT,
				 VLR_ICMS,
				 VLR_IPI,
				 VLR_BASE_SUBS,
				 VLR_ICMS_SUBS,
				 CD_NT_MOV,
				 STS_PED,
				 VLR_BASE_ICMS,
				 VLR_FRETE,
				 CD_USU,
				 DT_CAD,
				 FLAG_GERAR_CROSS,
				 CD_COND_PGTO,
				 DT_FATURAMENTO,
				 CD_PED_FORN,
				 APLICATIVO,
				 VERSAO_APLICATIVO)
				 
	SELECT  @CD_EMP,
			@CD_FILIAL,
			@CD_PED,
			@CD_FORN,
			CONVERT(DATE, GETDATE(), 102) AS DT_EMI,
			VLR_NF AS VLR_PED,
			VLR_PROD_TOT AS VLR_TOT_PROD,
			VLR_DESCONTO AS VLR_DESC,
			CONVERT(DATE, GETDATE(), 102) AS DT_PREV_ENT,
			VLR_ICMS,
			VLR_IPI,
			VLR_BASE_SUBS,
			VLR_ICMS_SUBS,
			CD_MOV_OP AS CD_NT_MOV,
			1 AS STS_PED,
			VLR_BASE_ICMS,
			VLR_FRETE,
			@CD_USU,
			CONVERT(DATE, GETDATE(), 102) AS DT_CAD,
			0 AS FLAG_GERAR_CROSS,
			0 AS CD_COND_PGTO,
			CONVERT(DATE, GETDATE(), 102) AS DT_FATURAMENTO,
			@CD_PED_FORN,
			'COMPRAS WEB' AS APLICATIVO,
			'1.0' AS VERSAO
	FROM EST_NF_ENT_DANFE_PROVISORIO
	WHERE CD_CTR = @CD_CTR
			AND CD_EMP = @CD_EMP
			AND CD_FILIAL = @CD_FILIAL
			AND CD_FORN = @CD_FORN
			

	INSERT INTO EST_PED_CP_VINCULADA
				(CD_EMP,
				 CD_FILIAL,
				 CD_PED,
				 CD_FILIAL_VINCULADA,
				 CD_USU,
				 DT_CAD)
	VALUES      (@CD_EMP,@CD_FILIAL,@CD_PED,0,@CD_USU,CONVERT(DATE, GETDATE(), 102))

	INSERT INTO EST_PED_ELTR_FORN
				(CD_EMP,
				 CD_PED_SOL,
				 CD_FORN,
				 ORD_ENV,
				 STS_FORN)
	VALUES     (@CD_EMP,@CD_PED_SOL,@CD_FORN,@ORD_ENV,0)

	INSERT INTO EST_PED_ELTR_FORN_PED_CP
				(CD_EMP,
				 CD_PED_SOL,
				 CD_FORN,
				 CD_FIL_ENTR,
				 ORD_ENV,
				 CD_PED)
	VALUES      (@CD_EMP,@CD_PED_SOL,@CD_FORN,@CD_FILIAL,@ORD_ENV,@CD_PED)
						
						
	INSERT INTO EST_PED_CP_IT
		(CD_EMP,
		 CD_FILIAL,
		 CD_PED,
		 CD_PROD,
		 VLR_IT,
		 QT_IT,
		 CD_TRIB,
		 PERC_IPI,
		 VLR_CUSTO,
		 VLR_BASE_SUBS,
		 VLR_ICMS_SUBS,
		 ALIQ_ICMS,
		 VLR_BASE_ICMS,
		 CD_NT_OP,
		 VLR_IPI,
		 VLR_ICMS,
		 PERC_DESC,
		 VLR_DESC,
		 SEQ_ITEM,
		 QT_EMB,
		 CD_COND_COMPRA)
		 
	SELECT 	@CD_EMP,
			@CD_FILIAL,
			@CD_PED,
			A.CD_PROD,
			
			(CASE WHEN D.CD_EMP IS NULL
				THEN
					CASE WHEN ISNULL((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0) > 0
						THEN
							A.VLR_PROD_UNIT * Round((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0)
						ELSE
							A.VLR_PROD_UNIT
					END
					
				ELSE
					CASE WHEN ISNULL((D.APRES_EMB_CP * D.APRES_QT_CP), 0) > 0
						THEN
							A.VLR_PROD_UNIT * Round((D.APRES_EMB_CP * D.APRES_QT_CP), 0)
						ELSE
							A.VLR_PROD_UNIT
					END
		    END) AS VLR_IT,
			
			
			
			(CASE WHEN D.CD_EMP IS NULL
				THEN
					CASE WHEN ISNULL((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0) > 0
						THEN
							Round(A.QTDE_PROD / (CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0)
						ELSE
							Round(A.QTDE_PROD, 0)
					END
					
				ELSE
					CASE WHEN ISNULL((D.APRES_EMB_CP * D.APRES_QT_CP), 0) > 0
						THEN
							Round(A.QTDE_PROD / (D.APRES_EMB_CP * D.APRES_QT_CP), 0)
						ELSE
							Round(A.QTDE_PROD, 0)
					END
		    END) AS QT_IT,
			

			A.CD_TRIB,
			A.PERC_PROD_IPI AS PERC_IPI,
			
			(CASE WHEN D.CD_EMP IS NULL
				THEN
					CASE WHEN ISNULL((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0) > 0
						THEN
							A.VLR_PROD_UNIT * Round((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0)
						ELSE
							A.VLR_PROD_UNIT
					END
					
				ELSE
					CASE WHEN ISNULL((D.APRES_EMB_CP * D.APRES_QT_CP), 0) > 0
						THEN
							A.VLR_PROD_UNIT * Round((D.APRES_EMB_CP * D.APRES_QT_CP), 0)
						ELSE
							A.VLR_PROD_UNIT
					END
		    END) AS VLR_CUSTO,
			
			A.VLR_PROD_BASE_ST,
			A.VLR_PROD_ICMS_ST,
			A.PERC_PROD_ICMS_ST,
			A.VLR_PROD_BASE_ICMS,
			B.CD_MOV_OP,
			A.VLR_PROD_IPI,
			A.VLR_PROD_ICMS,
			A.PERC_PROD_DESC,
			A.VLR_PROD_DESC,
			A.CD_CTR_IT,
			(A.APRES_EMB_CP * A.APRES_QT_CP),
			NULL
	FROM EST_NF_ENT_DANFE_PROVISORIO_ITENS A
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO B ON B.CD_CTR = A.CD_CTR AND A.CD_EMP = B.CD_EMP
	INNER JOIN EST_PROD C ON C.CD_EMP = A.CD_EMP AND C.CD_PROD = A.CD_PROD
	LEFT JOIN EST_PROD_FORN D ON D.CD_EMP = C.CD_EMP AND D.CD_PROD = C.CD_PROD AND D.CD_FORN = B.CD_FORN
	WHERE A.CD_CTR = @CD_CTR
		  AND A.CD_EMP = @CD_EMP
		  
		  
	INSERT INTO PRC_FILIAL_PENDENCIA (CD_EMP, CD_FILIAL, DT_CAD, TIPO, CD_USU)
					VALUES (@CD_EMP, @CD_FILIAL, GETDATE(), 'PEDIDO DE COMPRA', @CD_USU)
			
		   
END
GO