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

CREATE PROCEDURE [dbo].[P_GERA_PEDIDO_BY_GRP_DEMANDA] (
										@CD_EMP INT,
										@CD_GRP_PED INT,
										@CD_FORN INT,
										@DT_FATURAMENTO DATETIME,
										@CD_COND_PGTO INT = 0,
										@NAO_CONVERT_EMB INT = 0,
										@CD_USU INT = 0)
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @CD_PED_SOL INT
	DECLARE @CD_FILIAL INT
	DECLARE @CD_PED INT
	DECLARE @ORD_ENV INT
	DECLARE @CD_NT_MOV INT = 0
	
	DECLARE @EST_PED_ELTR_IT_TMP TABLE (  CD_EMP				   INT,
										CD_PED_SOL             INT, 
										CD_FILIAL              INT,
										CD_PROD                INT,
										QT_IT_UN			   INT,
										QT_IT			   	   INT,
										VLR_PROD_UN           MONEY,
										VLR_PROD              MONEY,
										PERC_DESC             MONEY,
										QT_EMB				  MONEY)

	SET @DT_FATURAMENTO = CONVERT(VARCHAR, COALESCE(@DT_FATURAMENTO, CURRENT_TIMESTAMP), 112)
	
	SET @CD_NT_MOV = (SELECT TOP 1 COALESCE(CD_MOV_OP, 0) CD_NT_MOV 
							FROM   GLB_MOV_OP
							WHERE  GLB_MOV_OP.TP_MOV_OP_TRANSA = 0
								   AND GLB_MOV_OP.STS_MOV = 0
								   AND GLB_MOV_OP.TP_MOV = 0
								   AND GLB_MOV_OP.ATU_EST = 1)
	
	DECLARE DEMANDAS CURSOR FOR
	  SELECT A.CD_PED_SOL, B.CD_FIL_ENTR AS CD_FILIAL
	  FROM   EST_PED_ELTR_GRP_PED_EST_PED_ELTR A
			 INNER JOIN EST_PED_ELTR B ON A.CD_EMP = B.CD_EMP AND A.CD_PED_SOL = B.CD_PED_SOL
	  WHERE  A.CD_EMP = @CD_EMP AND A.CD_GRP_PED = @CD_GRP_PED

	OPEN DEMANDAS

	FETCH NEXT FROM DEMANDAS INTO @CD_PED_SOL, @CD_FILIAL

	WHILE @@FETCH_STATUS = 0
	  BEGIN
			
			-- 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);
									
				
			DELETE FROM @EST_PED_ELTR_IT_TMP
			
			INSERT INTO @EST_PED_ELTR_IT_TMP
				SELECT 
					@CD_EMP,
					@CD_PED_SOL,
					@CD_FILIAL,
					A.CD_PROD,
					A.QT_IT_UN,
					A.QT_IT_UN AS QT_IT,
					A.VLR_PROD AS VLR_PROD_UN,
					A.VLR_PROD,
					A.PERC_DESC,
					1 AS QT_EMB
				FROM EST_PED_ELTR_IT A
				WHERE A.CD_EMP = @CD_EMP AND A.CD_PED_SOL = @CD_PED_SOL
				
									
			IF @NAO_CONVERT_EMB = 0
			BEGIN
			
				UPDATE A
				
					SET
					A.QT_EMB = B.QT_EMB_COMPRA,
					A.VLR_PROD = (A.VLR_PROD_UN * B.QT_EMB_COMPRA),
					A.QT_IT =  CASE
									WHEN COALESCE(B.QT_EMB_COMPRA, 0) > 0
										THEN Round(A.QT_IT_UN / B.QT_EMB_COMPRA, 0)
									ELSE
										0
								END
				FROM @EST_PED_ELTR_IT_TMP A
				CROSS APPLY Ufd_est_prod_emb_apres (A.CD_EMP, A.CD_PROD, @CD_FORN) B
				
				
				DELETE FROM @EST_PED_ELTR_IT_TMP WHERE QT_IT = 0

			END

			
							
			IF (SELECT CD_EMP FROM @EST_PED_ELTR_IT_TMP GROUP BY CD_EMP) = 1
			BEGIN

				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(VARCHAR, GETDATE(), 112) AS DT_EMI,
						SUM(ROUND((QT_IT * VLR_PROD ) - ROUND((QT_IT * VLR_PROD * PERC_DESC / 100 ), 2), 2)) AS VLR_PED,
						SUM(ROUND(QT_IT * VLR_PROD, 2)) AS VLR_TOT_PROD,
						SUM(ROUND((QT_IT * VLR_PROD * PERC_DESC / 100 ), 2)) AS VLR_DESC,
						CONVERT(DATE, GETDATE(), 102) AS DT_PREV_ENT,
						0,
						0,
						0,
						0,
						@CD_NT_MOV,
						0,
						0,
						0,
						@CD_USU,
						GETDATE() AS DT_CAD,
						0,
						@CD_COND_PGTO,
						@DT_FATURAMENTO,
						@CD_PED,
						'COMPRAS WEB' AS APLICATIVO,
						'1.0' AS VERSAO
				FROM @EST_PED_ELTR_IT_TMP
			
			
				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,ENV_PHLINK, COMPRA_OL)
				VALUES     (@CD_EMP,@CD_PED_SOL,@CD_FORN,@ORD_ENV,0,0,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,
					
						A.VLR_PROD AS VLR_IT,
						A.QT_IT AS QT_IT,
					 
						1 AS CD_TRIB,
						0 AS PERC_IPI,
					
						A.VLR_PROD AS VLR_CUSTO,
					 
						0 AS VLR_BASE_SUBS,
						0 AS VLR_ICMS_SUBS,
						0 AS ALIQ_ICMS,
						1 AS VLR_BASE_ICMS,
						1 AS CD_NT_OP,
						0 AS VLR_IPI,
						0 AS VLR_ICMS,
						A.PERC_DESC,
						ROUND((A.QT_IT * A.VLR_PROD * A.PERC_DESC / 100 ), 2) AS VLR_DESC,
						Row_number() 
							OVER(ORDER BY A.CD_PROD DESC),
						A.QT_EMB AS QT_EMB,
						NULL AS CD_COND_COMPRA
					
				FROM @EST_PED_ELTR_IT_TMP A
		  
				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)

				UPDATE EST_PED_ELTR SET FLAG_FECHAR = 1 WHERE CD_EMP = @CD_EMP AND CD_PED_SOL = @CD_PED_SOL
					
			END

			FETCH NEXT FROM DEMANDAS INTO @CD_PED_SOL, @CD_FILIAL
	  END
	  
	CLOSE DEMANDAS
	DEALLOCATE DEMANDAS
	
END
GO