IF NOT OBJECT_ID('P_SALVAR_DEMANDA') IS NULL
	BEGIN
			DROP PROCEDURE P_SALVAR_DEMANDA
	END
go

CREATE PROCEDURE [dbo].[P_SALVAR_DEMANDA] (@CD_EMP INT, 
										   @CD_USU INT, 
										   @DT_ENTR DATETIME = NULL,
										   @DS_GRP_DEMANDA VARCHAR(100) = '' , 
										   @APLICATIVO VARCHAR(50) = 'NAO IDENTEFICADO',
										   @VERSAO_APLICATIVO VARCHAR(10) = '0.0.0',
										   @CD_CTR_AGENDA INT = 0,
										   @DT_AGENDA DATETIME = NULL,
										   @CD_FORN INT = 0,
										   @CD_COND_PGTO INT = 0,
										   @DT_FATURAMENTO DATETIME = NULL,
										   @TP_REQ INT = 0,
										   @DT_PROX_AGENDA DATETIME = NULL,
										   @IS_INTEGRADO INT = 0,
										   @POSSUI_ALCADA INT = 0,
										   @IS_UNIFICADO INT = 0)
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @CD_FILIAL INT = 0
	DECLARE @CD_BANDEIRA INT = 0	
	DECLARE @CD_PED_SOL INT = 0
	DECLARE @CD_GRP_PED INT = 0
	DECLARE @DT_PED DATETIME = CONVERT(VARCHAR, Getdate(), 112) 
	DECLARE @FLAG_FECHAR INT = 0
	DECLARE @CD_PROD INT = 0
	DECLARE @CD_NT_MOV INT = 0
	DECLARE @VLR_TOTAL_PROD MONEY = 0
	DECLARE @VLR_DESC MONEY = 0
	DECLARE @VLR_PED MONEY = 0
	DECLARE @CD_PED INT = 0
	DECLARE @ORD_ENV INT = 0
	DECLARE @DT_ATUAL DATETIME = GETDATE()
	DECLARE @TIPO_PENDENCIA VARCHAR(50) = ''
	DECLARE @TMH_PEDIDO INT

	DECLARE @TBL_PEDIDOS TABLE (   CD_BANDEIRA             INT, 
									CD_GRP_PED              INT,
									CD_FILIAL               INT,   
									CD_PED_SOL              INT,
									CD_PED                  INT)
									
	DECLARE @TBL_DEMANDAS_GERADAS TABLE (CD_PED INT, CD_PED_SOL	INT, CD_FILIAL INT) 


	--AJUSTAR DADOS DE ENTRADA
	SET @DT_ENTR = CONVERT(VARCHAR, COALESCE(@DT_ENTR, CURRENT_TIMESTAMP), 112);
	SET @DT_AGENDA = CONVERT(VARCHAR, COALESCE(@DT_AGENDA, CURRENT_TIMESTAMP), 112);
	SET @DT_FATURAMENTO = CONVERT(VARCHAR, COALESCE(@DT_FATURAMENTO, CURRENT_TIMESTAMP), 112);

	SET @TMH_PEDIDO = (SELECT ISNULL((SELECT VALOR 
											FROM PRC_EMP_CONFIG 
											WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'QTDE_ITENS_DIVIDE_RESSUPRIMENTO'), 999999));
	
	IF @IS_INTEGRADO > 0
		BEGIN
			DELETE FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA 
			WHERE CD_EMP = @CD_EMP 
			AND CD_USU = @CD_USU
			AND CD_PROD IN 
				(SELECT A.CD_PROD
					FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
					WHERE A.CD_EMP = @CD_EMP
					AND A.CD_USU = @CD_USU 
				GROUP BY A.CD_PROD
				HAVING  ROUND(SUM(A.QTDE_PROD),0) < 1)
		END
	ELSE
		BEGIN
			DELETE FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA WHERE CD_EMP = @CD_EMP AND CD_USU = @CD_USU AND ROUND(QTDE_PROD, 0) = 0
		END
	

	IF @CD_FORN > 0
	  BEGIN
		  SET @FLAG_FECHAR = 1
		  SET @CD_NT_MOV = (SELECT TOP 1 COALESCE(CD_MOV_OP, 0) cdNtMov
							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)
         PRINT 'CAPTURADO MOVIMENTACAO '+CAST(@CD_NT_MOV AS VARCHAR)+' PARA PEDIDO DO FORNECEDOR ' + CAST(@CD_FORN AS VARCHAR)
	  END

	DECLARE PEDIDO_ELTR_BANDEIRA CURSOR FOR
	  SELECT CD_BANDEIRA
	  FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
		     LEFT JOIN PRC_BANDEIRA_PRC_FILIAL BLJ
                      ON A.CD_EMP = BLJ.CD_EMP
                        AND A.CD_FILIAL = BLJ.CD_FILIAL
	  WHERE  A.CD_EMP = @CD_EMP
			 AND A.CD_USU = @CD_USU
	  GROUP BY CD_BANDEIRA

	OPEN PEDIDO_ELTR_BANDEIRA

	FETCH NEXT FROM PEDIDO_ELTR_BANDEIRA INTO @CD_BANDEIRA

	WHILE @@FETCH_STATUS = 0
	  BEGIN
		  PRINT 'TRATANDO PEDIDO DAS FILIAIS DA BANDEIRA ' + CAST( @CD_BANDEIRA AS VARCHAR)
			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,@DT_PED,0,@CD_USU,Getdate(),@DS_GRP_DEMANDA)

			--CURSOR
			IF(@CD_BANDEIRA IS NOT NULL)
				BEGIN
					DECLARE PEDIDO_ELTR CURSOR FOR
					  SELECT DISTINCT  A.CD_EMP,
							 A.CD_FILIAL
					  FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
							 LEFT JOIN PRC_BANDEIRA_PRC_FILIAL BLJ
									  ON A.CD_EMP = BLJ.CD_EMP
										AND A.CD_FILIAL = BLJ.CD_FILIAL
					  WHERE  A.CD_EMP = @CD_EMP
							 AND A.CD_USU = @CD_USU
							 AND BLJ.CD_BANDEIRA = @CD_BANDEIRA
					  GROUP BY A.CD_EMP,
							 A.CD_FILIAL
				END
			ELSE
				BEGIN
					DECLARE PEDIDO_ELTR CURSOR FOR
					  SELECT DISTINCT  A.CD_EMP,
							 A.CD_FILIAL
					  FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
							 LEFT JOIN PRC_BANDEIRA_PRC_FILIAL BLJ
									  ON A.CD_EMP = BLJ.CD_EMP
										AND A.CD_FILIAL = BLJ.CD_FILIAL
					  WHERE  A.CD_EMP = @CD_EMP
							 AND A.CD_USU = @CD_USU
							 AND BLJ.CD_BANDEIRA IS NULL
					  GROUP BY A.CD_EMP,
							 A.CD_FILIAL
				END

			OPEN PEDIDO_ELTR

			FETCH NEXT FROM PEDIDO_ELTR INTO @CD_EMP, @CD_FILIAL

			WHILE @@FETCH_STATUS = 0
			  BEGIN
				  PRINT 'TRATANDO PEDIDO DA FILIAL ' + CAST( @CD_FILIAL AS VARCHAR)
				  
				  SET @TIPO_PENDENCIA = 'DEMANDA'
											  
				  PRINT 'TRATANDO PEDIDO ELETRONICO NR ' + CAST( @CD_PED_SOL AS VARCHAR)
				  
				  -- VALOR DA DEMANDA
				  DECLARE @VLR_TOT_DEMANDA MONEY = 0;
				  SET @VLR_TOT_DEMANDA = (SELECT SUM((VLR_PROD - VLR_PROD*(PERC_DESC/100))*ROUND(QTDE_PROD,0)) 
				  FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA WHERE CD_EMP = @CD_EMP AND CD_USU = @CD_USU AND CD_FILIAL = @CD_FILIAL);
				  
				  --INSERIDO DEMANDA COM ALCADA (PEDIDO NAO PODE SER QUEBRADO)
				  IF @POSSUI_ALCADA > 0
					BEGIN 
						-- VALOR DA ALCADA
						DECLARE @VLR_REFERENCIA_ALCADA MONEY = 0;
						SET @VLR_REFERENCIA_ALCADA = (SELECT TOP 1 ISNULL(VLR_REFERENCIA_ALCADA,0) 
															FROM  EST_PROD_SUPRIMENTO_ENTREGA_LOJA 
															WHERE  CD_EMP = @CD_EMP AND CD_USU = @CD_USU AND CD_FILIAL = @CD_FILIAL);

						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 @TBL_DEMANDAS_GERADAS VALUES (@CD_PED, @CD_PED_SOL,@CD_FILIAL)
													  
						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,
								   VLR_REFERENCIA_ALCADA)
								VALUES      (@CD_EMP,@CD_PED_SOL,@CD_FILIAL,@DT_PED,@DT_ENTR,@CD_FILIAL,@CD_FILIAL,0,Getdate(),
												@CD_USU,@TP_REQ,@FLAG_FECHAR,@DT_ENTR,Getdate(),0,0,0,0,0,0,NULL, @VLR_REFERENCIA_ALCADA)
						
						-- GRAVANDO A AUDITORIA DE DEMANDA NA EST_PED_ELTR_AUDIT
						IF @IS_UNIFICADO > 0 
							BEGIN
								INSERT INTO EST_PED_ELTR_AUDIT
									(CD_USU,DT_CAD,JUSTIFICATIVA,QUANTIDADE,CD_PROD,CD_FILIAL,CD_PED_SOL)
								SELECT
									A.CD_USU,
									A.DT_CAD,
									A.JUSTIFICATIVA,
									A.QUANTIDADE,
									A.CD_PROD,
									A.CD_FILIAL,
									@CD_PED_SOL
								FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_HIST A
								INNER JOIN EST_PROD_SUPRIMENTO_ENTREGA_LOJA B 
									ON B.CD_EMP = @CD_EMP AND B.CD_USU = A.CD_USU AND B.CD_FILIAL = A.CD_FILIAL AND B.CD_PROD = A.CD_PROD
								WHERE A.CD_USU = @CD_USU
							END
						ELSE
							BEGIN
								INSERT INTO EST_PED_ELTR_AUDIT
									(CD_USU,DT_CAD,JUSTIFICATIVA,QUANTIDADE,CD_PROD,CD_FILIAL,CD_PED_SOL)
								SELECT
									A.CD_USU,
									A.DT_CAD,
									A.JUSTIFICATIVA,
									A.QUANTIDADE,
									A.CD_PROD,
									A.CD_FILIAL,
									@CD_PED_SOL
								FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_HIST A
								INNER JOIN EST_PROD_SUPRIMENTO_ENTREGA_LOJA B 
									ON B.CD_EMP = @CD_EMP AND B.CD_USU = A.CD_USU AND B.CD_FILIAL = A.CD_FILIAL AND B.CD_PROD = A.CD_PROD
								WHERE A.CD_USU = @CD_USU AND A.CD_FILIAL = @CD_FILIAL
							END

						-- 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) 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
											1
									  END
								END AS QT_IT,
							 A.VLR_PROD,
							 A.PERC_DESC,
							 A.CD_COND_COMPRA,
							 1 UNID,
							 A.MEDIAF,
							 
							 CASE WHEN @VLR_TOT_DEMANDA > A.VLR_REFERENCIA_ALCADA 
								THEN
									 CASE WHEN A.QTDE_PROD > A.QTDE_PROD_ORIGINAL 
										THEN
											ROUND(A.QTDE_PROD_ORIGINAL, 0)
										ELSE	
											ROUND(A.QTDE_PROD, 0)
										END
								ELSE 
									 0
							 END AS QTDE_BASE_ALCADA,
							 
							 CASE WHEN @VLR_TOT_DEMANDA > A.VLR_REFERENCIA_ALCADA 
								THEN
									 CASE WHEN A.QTDE_PROD > A.QTDE_PROD_ORIGINAL 
										THEN
											ROUND(A.QTDE_PROD,0) - ROUND(A.QTDE_PROD_ORIGINAL, 0)
										ELSE	
											0
									 END 
								ELSE
									 0
							  END AS QTDE_ALCADA,
							
							 CASE WHEN @VLR_TOT_DEMANDA > A.VLR_REFERENCIA_ALCADA
								THEN
									 CASE WHEN A.QTDE_PROD > A.QTDE_PROD_ORIGINAL 
										THEN
											((ROUND(A.QTDE_PROD,0) - ROUND(A.QTDE_PROD_ORIGINAL, 0))*A.VLR_PROD) - ((ROUND(A.QTDE_PROD, 0) - ROUND(A.QTDE_PROD_ORIGINAL,0))*A.VLR_PROD*(A.PERC_DESC/100))
										ELSE	
											0
									  END
								 ELSE
									  0
							 END AS VLR_ALCADA,
							
							 CASE WHEN @VLR_TOT_DEMANDA > A.VLR_REFERENCIA_ALCADA
								THEN
									 CASE WHEN A.QTDE_PROD > A.QTDE_PROD_ORIGINAL 
										 THEN
											 (((ROUND(A.QTDE_PROD, 0) - ROUND(A.QTDE_PROD_ORIGINAL, 0))*A.VLR_PROD) - (((ROUND(A.QTDE_PROD, 0) - ROUND(A.QTDE_PROD_ORIGINAL, 0))*A.VLR_PROD)*(A.PERC_DESC/100))) / (@VLR_TOT_DEMANDA - A.VLR_REFERENCIA_ALCADA)
										 ELSE	
											0
									 END
								 ELSE
									 0
							 END AS PERC_VLR_ALCADA,
							 
							 CASE WHEN (A.MEDIAF)=0 
								THEN
									ROUND(((A.QT_PEND_ENT + ROUND(A.QTDE_PROD,0))/0.0001)*30, 2)
								ELSE
									ROUND(((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/A.MEDIAF)*30, 2)
							 END AS COB,
							
							 LIMITE_DIAS_COB_ALCADA AS COBERTURA_ALCADA,
							 
							 CASE WHEN (A.MEDIAF)=0 
								THEN
								
									CASE WHEN A.LIMITE_DIAS_COB_ALCADA < ((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/0.0001)*30
										THEN   ((((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/0.0001)*30) - A.LIMITE_DIAS_COB_ALCADA) / A.LIMITE_DIAS_COB_ALCADA
										ELSE 0
									END
								ELSE
									CASE WHEN A.LIMITE_DIAS_COB_ALCADA < ((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/A.MEDIAF)*30
										THEN ((((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/A.MEDIAF)*30) - A.LIMITE_DIAS_COB_ALCADA) / A.LIMITE_DIAS_COB_ALCADA
										ELSE 0
									END
							 END AS PERC_COBERTURA_ALCADA				
						
					    FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
						INNER JOIN EST_PROD B ON B.CD_EMP = A.CD_EMP AND A.CD_PROD = B.CD_PROD
					    WHERE  A.CD_EMP = @CD_EMP
							 AND A.CD_USU = @CD_USU
							 AND A.CD_FILIAL = @CD_FILIAL
							 AND ROUND(A.QTDE_PROD,0) > 0
							 
					END
				  -- INSERINDO DEMANDAS CASO NAO POSSUA ALCADA
				  ELSE
					BEGIN
					
						  DECLARE @FLAG_CONTINUAR INT = 1;
						  SET @CD_PED_SOL = COALESCE((SELECT COALESCE(Max(CD_PED_SOL), 0)
											  FROM   EST_PED_ELTR WITH (NOLOCK)
											  WHERE  CD_EMP = @CD_EMP), 0);
						  
						  WHILE @FLAG_CONTINUAR > 0
							BEGIN
							
								 SET @CD_PED_SOL = @CD_PED_SOL + 1;
								 
								 INSERT INTO @TBL_DEMANDAS_GERADAS VALUES (@CD_PED, @CD_PED_SOL,@CD_FILIAL)
								 				  
								 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,
								   VLR_REFERENCIA_ALCADA)
								VALUES
								   (@CD_EMP,@CD_PED_SOL,@CD_FILIAL,@DT_PED,@DT_ENTR,@CD_FILIAL,@CD_FILIAL,0,Getdate(),@CD_USU,@TP_REQ,@FLAG_FECHAR,@DT_ENTR,Getdate(),0,0,0,0,0,0,NULL, @VLR_REFERENCIA_ALCADA)
								   
								
								-- GRAVANDO A AUDITORIA DE DEMANDA NA EST_PED_ELTR_AUDIT
								IF @IS_UNIFICADO > 0 
									BEGIN
										INSERT INTO EST_PED_ELTR_AUDIT
											(CD_USU,DT_CAD,JUSTIFICATIVA,QUANTIDADE,CD_PROD,CD_FILIAL,CD_PED_SOL)
										SELECT
											A.CD_USU,
											A.DT_CAD,
											A.JUSTIFICATIVA,
											A.QUANTIDADE,
											A.CD_PROD,
											A.CD_FILIAL,
											@CD_PED_SOL
										FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_HIST A
										INNER JOIN EST_PROD_SUPRIMENTO_ENTREGA_LOJA B 
											ON B.CD_EMP = @CD_EMP AND B.CD_USU = A.CD_USU AND B.CD_FILIAL = A.CD_FILIAL AND B.CD_PROD = A.CD_PROD
										WHERE A.CD_USU = @CD_USU
									END
								ELSE
									BEGIN
										INSERT INTO EST_PED_ELTR_AUDIT
											(CD_USU,DT_CAD,JUSTIFICATIVA,QUANTIDADE,CD_PROD,CD_FILIAL,CD_PED_SOL)
										SELECT
											A.CD_USU,
											A.DT_CAD,
											A.JUSTIFICATIVA,
											A.QUANTIDADE,
											A.CD_PROD,
											A.CD_FILIAL,
											@CD_PED_SOL
										FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_HIST A
										INNER JOIN EST_PROD_SUPRIMENTO_ENTREGA_LOJA B 
											ON B.CD_EMP = @CD_EMP AND B.CD_USU = A.CD_USU AND B.CD_FILIAL = A.CD_FILIAL AND B.CD_PROD = A.CD_PROD
										WHERE A.CD_USU = @CD_USU AND A.CD_FILIAL = @CD_FILIAL
									END
								
								
								
								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)
								
								
								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  T.CD_EMP,
										T.CD_PED_SOL,
										T.CD_PROD,
										T.QT_IT_UN,
										T.QT_IT,
										T.VLR_PROD,
										T.PERC_DESC,
										T.CD_COND_COMPRA,
										T.UNID,
										T.MEDIAF,
										T.QTDE_BASE_ALCADA,
										T.QTDE_ALCADA,
										T.VLR_ALCADA,
										T.PERC_VLR_ALCADA,
										T.COB,
										T.COBERTURA_ALCADA,
										T.PERC_COBERTURA_ALCADA
									FROM
									(SELECT ROW_NUMBER() OVER(ORDER BY A.CD_PROD ASC) AS LINHA,
											@CD_EMP AS CD_EMP,
											@CD_PED_SOL AS CD_PED_SOL,
											A.CD_PROD  AS CD_PROD,
											ROUND(A.QTDE_PROD,0) 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
															1
													  END
											 END AS QT_IT,
											 A.VLR_PROD,
											 A.PERC_DESC,
											 A.CD_COND_COMPRA,
											 1             UNID,
											 A.MEDIAF,
											 0 QTDE_BASE_ALCADA,
											 0 QTDE_ALCADA,
											 0 VLR_ALCADA,
											 0 PERC_VLR_ALCADA,
										 
											 CASE WHEN (A.MEDIAF)=0 
												THEN
													ROUND(((A.QT_PEND_ENT + ROUND(A.QTDE_PROD,0))/0.0001)*30, 2)
												ELSE
													ROUND(((A.QT_PEND_ENT + ROUND(A.QTDE_PROD, 0))/A.MEDIAF)*30, 2)
											 END AS COB,
										
											 0 COBERTURA_ALCADA,
											 0 PERC_COBERTURA_ALCADA
										 
									  FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
											 INNER JOIN EST_PROD B ON B.CD_EMP = A.CD_EMP AND A.CD_PROD = B.CD_PROD
											 LEFT JOIN EST_PED_ELTR_IT C ON 
												A.CD_EMP = C.CD_EMP AND 
												A.CD_PROD = C.CD_PROD AND 
												C.CD_PED_SOL IN (SELECT CD_PED_SOL 
																	FROM @TBL_DEMANDAS_GERADAS T
																	WHERE A.CD_FILIAL = T.CD_FILIAL)
										 
									  WHERE  A.CD_EMP = @CD_EMP
											 AND A.CD_USU = @CD_USU
											 AND A.CD_FILIAL = @CD_FILIAL
											 AND ROUND(A.QTDE_PROD,0) > 0
											 AND C.CD_EMP IS NULL) AS T
								  WHERE T.LINHA <= @TMH_PEDIDO;								  
								  if @@rowcount =0		 
								  begin
									SET @FLAG_CONTINUAR =0
								  end
								  else
								  begin

									SET @FLAG_CONTINUAR =(SELECT ISNULL(COUNT(A.CD_EMP), 0)
															FROM
																EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
																LEFT JOIN EST_PED_ELTR_IT B ON A.CD_EMP = B.CD_EMP
																							   AND A.CD_PROD = B.CD_PROD
																							   AND B.CD_PED_SOL IN (SELECT CD_PED_SOL 
																														FROM @TBL_DEMANDAS_GERADAS T
																														WHERE A.CD_FILIAL = T.CD_FILIAL)
															WHERE		A.CD_EMP			= @CD_EMP
																   AND	A.CD_USU		= @CD_USU
																   AND	A.CD_FILIAL	= @CD_FILIAL
																   AND ROUND(A.QTDE_PROD, 0) > 0
																   AND B.CD_EMP IS NULL);
									end

								  -- INSERINDO PEDIDO DE COMPRA
								  IF @CD_FORN > 0
									BEGIN
									
										SET @TIPO_PENDENCIA = 'PEDIDO DE COMPRA'
									
										SELECT
											   @VLR_TOTAL_PROD = Sum(Round(A.QTDE_PROD * A.VLR_PROD, 2)),
											   @VLR_DESC = Sum(Round(( A.QTDE_PROD * A.VLR_PROD * A.PERC_DESC / 100 ), 2)),
											   @VLR_PED = Sum(Round(( A.QTDE_PROD * A.VLR_PROD ) - Round(( A.QTDE_PROD * A.VLR_PROD * A.PERC_DESC / 100 ), 2), 2))
										FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
											   INNER JOIN EST_PED_ELTR_IT B ON A.CD_EMP = B.CD_EMP AND A.CD_PROD = B.CD_PROD AND  B.CD_PED_SOL = @CD_PED_SOL
										WHERE  A.CD_EMP = @CD_EMP
											   AND A.CD_USU = @CD_USU
											   AND A.CD_FILIAL = @CD_FILIAL

										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);
																		
										UPDATE @TBL_DEMANDAS_GERADAS SET CD_PED = @CD_PED  WHERE CD_PED_SOL = @CD_PED_SOL
									

										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)
										VALUES      (@CD_EMP,@CD_FILIAL,@CD_PED,@CD_FORN,@DT_PED,@VLR_PED,@VLR_TOTAL_PROD,@VLR_DESC,@DT_ENTR,0,0,0,0,@CD_NT_MOV,0,0,0,@CD_USU,Getdate(),0,@CD_COND_PGTO,Getdate(),@CD_PED,@APLICATIVO,@VERSAO_APLICATIVO)

										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,Getdate())

										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 B.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 * Round((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0)
															ELSE
																A.VLR_PROD
														END
														
													ELSE
														CASE WHEN ISNULL((B.APRES_EMB_CP * B.APRES_QT_CP), 0) > 0
															THEN
																A.VLR_PROD * Round((B.APRES_EMB_CP * B.APRES_QT_CP), 0)
															ELSE
																A.VLR_PROD
														END
											   END) AS VLR_IT,
											   
											   
											   (CASE WHEN B.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((B.APRES_EMB_CP * B.APRES_QT_CP), 0) > 0
															THEN
																Round(A.QTDE_PROD / (B.APRES_EMB_CP * B.APRES_QT_CP), 0)
															ELSE
																Round(A.QTDE_PROD, 0)
														END
											   END) AS QT_IT,
											   
											   1,
											   0,
											  
											   (CASE WHEN B.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 * Round((CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))), 0)
															ELSE
																A.VLR_PROD
														END
														
													ELSE
														CASE WHEN ISNULL((B.APRES_EMB_CP * B.APRES_QT_CP), 0) > 0
															THEN
																A.VLR_PROD * Round((B.APRES_EMB_CP * B.APRES_QT_CP), 0)
															ELSE
																A.VLR_PROD
														END
											   END) AS VLR_CUSTO,
											  
											   0,
											   0,
											   0,
											   1,
											   1,
											   0,
											   0,
											   A.PERC_DESC,
											   Round(A.QTDE_PROD * A.VLR_PROD * (A.PERC_DESC / 100), 2),
											   Row_number()
												 OVER(
												   ORDER BY A.CD_PROD ASC),
												   
											   (CASE WHEN B.CD_EMP IS NULL
													THEN
														CONVERT(MONEY,REPLACE(C.APRES_EMB_CP,',','.')) * CONVERT(MONEY,REPLACE(C.APRES_QT_CP,',','.'))
													ELSE
														(B.APRES_EMB_CP * B.APRES_QT_CP)
											   END) AS QT_EMB,
											   A.CD_COND_COMPRA
										FROM   EST_PROD_SUPRIMENTO_ENTREGA_LOJA A
											   LEFT JOIN EST_PROD_FORN B ON A.CD_PROD = B.CD_PROD AND B.CD_FORN = @CD_FORN
											   INNER JOIN EST_PROD C ON A.CD_PROD = C.CD_PROD
											   INNER JOIN EST_PED_ELTR_IT D ON A.CD_EMP = D.CD_EMP AND A.CD_PROD = D.CD_PROD AND  D.CD_PED_SOL = @CD_PED_SOL
										WHERE  A.CD_EMP = @CD_EMP
											   AND A.CD_USU = @CD_USU
											   AND A.CD_FILIAL = @CD_FILIAL
											   AND A.QTDE_PROD > 0
									END
							
						END
						-- FIM LOOP QUEBRA DE DEMANDA	
					END
					-- FIM ELSE DEMANDA SEM ALCADA
					
					INSERT INTO PRC_FILIAL_PENDENCIA (CD_EMP, CD_FILIAL, DT_CAD, TIPO, CD_USU)
					VALUES (@CD_EMP, @CD_FILIAL, @DT_ATUAL, @TIPO_PENDENCIA, @CD_USU)

					INSERT INTO @TBL_PEDIDOS (CD_BANDEIRA, 
												CD_GRP_PED, 
												CD_FILIAL,
												CD_PED_SOL, 
												CD_PED)
					SELECT  @CD_BANDEIRA, @CD_GRP_PED, @CD_FILIAL, CD_PED_SOL, CD_PED
					FROM @TBL_DEMANDAS_GERADAS
					
					DELETE FROM @TBL_DEMANDAS_GERADAS
						
					--PROXIMA FILIAL
					FETCH NEXT FROM PEDIDO_ELTR INTO @CD_EMP, @CD_FILIAL
			  END
			  -- FIM WHILE POR FILIAL


			CLOSE PEDIDO_ELTR

			DEALLOCATE PEDIDO_ELTR 
		    --PROXIMA BANDEIRA
		    FETCH NEXT FROM PEDIDO_ELTR_BANDEIRA INTO @CD_BANDEIRA
	  END
	  -- FIM WHILE POR BANDEIRA

	CLOSE PEDIDO_ELTR_BANDEIRA
	DEALLOCATE PEDIDO_ELTR_BANDEIRA

	IF EXISTS(SELECT * FROM @TBL_PEDIDOS)
	BEGIN
		DELETE FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA WHERE CD_EMP = @CD_EMP AND CD_USU = @CD_USU
		DELETE FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_HIST WHERE CD_USU = @CD_USU
		DELETE FROM EST_PROD_SUPRIMENTO_ENCOMENDA_ENTREGA_LOJA WHERE CD_EMP = @CD_EMP AND CD_USU = @CD_USU
		DELETE FROM EST_PROD_SUPRIMENTO_ENTREGA_LOJA_PARAM WHERE  CD_EMP = @CD_EMP AND CD_USU = @CD_USU
	END
	  
	IF @CD_CTR_AGENDA > 0 AND @POSSUI_ALCADA = 0
	BEGIN
		
		DECLARE @CD_AGENDA INT = 0
		
		SET @CD_AGENDA = ISNULL((SELECT MAX(CD_AGENDA)+1 FROM EST_PROD_AGENDA_COMPRA_AGENDAMENTO WITH (XLOCK) ), 0)

		-- UPDATE AGENDAMENTO CASO EXISTA
		UPDATE EST_PROD_AGENDA_COMPRA_AGENDAMENTO
		SET    STATUS = 1
		WHERE  CD_CTR_AGENDA = @CD_CTR_AGENDA AND STATUS = 0

		-- INSERINDO QUE A AGENDA FOI REALIZADA
		INSERT INTO EST_PROD_AGENDA_COMPRA_REALIZADO
					(CD_CTR_AGENDA,
					 DT_AGENDA,
					 DT_REALIZADO,
					 CD_USU)
		VALUES      (@CD_CTR_AGENDA,@DT_AGENDA,Getdate(),@CD_USU)
		
		--DELETE EVENTO DO CALENDARIO REALIZADO
		DELETE FROM GLB_USU_AGENDA WHERE CD_CTR_AGENDA = @CD_CTR_AGENDA
		
		IF @DT_PROX_AGENDA IS NULL
		BEGIN
			SET @DT_PROX_AGENDA = (SELECT DATEADD(DAY,
													CASE
														WHEN FREQ_REV = 1 THEN 28
														WHEN FREQ_REV = 2 THEN 14
														WHEN FREQ_REV = 4 THEN 7
														WHEN FREQ_REV = 8 THEN 4
														WHEN FREQ_REV = 12 THEN 2
														ELSE 1
													END,
												DT_AGENDAMENTO_DEFAULT)
								   FROM EST_PROD_AGENDA_COMPRA
								   WHERE  CD_CTR_AGENDA = @CD_CTR_AGENDA)
		END
		
		-- REALINHAR CADASTRO DE AGENDA
		UPDATE EST_PROD_AGENDA_COMPRA
		SET    DT_AGENDAMENTO_DEFAULT = @DT_PROX_AGENDA
		WHERE  CD_CTR_AGENDA = @CD_CTR_AGENDA
		
		-- INSERE NOVO AGENDAMENTO
		INSERT INTO EST_PROD_AGENDA_COMPRA_AGENDAMENTO(CD_AGENDA,CD_CTR_AGENDA,DT_AGENDA,STATUS)
		SELECT
			 CD_AGENDA=@CD_AGENDA
			,CD_CTR_AGENDA
			,DT_AGENDAMENTO_DEFAULT
			,STATUS=0
		FROM
			EST_PROD_AGENDA_COMPRA
		WHERE
			CD_CTR_AGENDA = @CD_CTR_AGENDA
		
		-- INSERE NOVO EVENTO NO CALENDARIO
		INSERT INTO GLB_USU_AGENDA (TITULO, COR, DT_INI, DT_FIM, URL, PARAMETROS, NAO_POSSUI_HR_INICIO_E_FIM,
						EVENT_BLOQ_EDICAO_ANTERIORMENTE,EDITAVEL,CD_USU,DT_CAD,CD_CTR_AGENDA)
		SELECT DISTINCT
			LEFT('AGENDA DE COMPRA '+A.DS_AGENDAMENTO, 100),
			CASE WHEN A.DT_AGENDAMENTO_DEFAULT < CONVERT(DATE, GETDATE(), 102) THEN '#ff4d4d' --VERMELHO
				 WHEN A.DT_AGENDAMENTO_DEFAULT = CONVERT(DATE, GETDATE(), 102) THEN  '#00cc44'	--VERDE
				 ELSE '#0099ff' --AZUL
			END,
			A.DT_AGENDAMENTO_DEFAULT,
			A.DT_AGENDAMENTO_DEFAULT,
			'app.compras.demanda',
			'cdEmp:'+CAST(@CD_EMP AS VARCHAR(MAX))+' ,cdCtrAgenda:'+CAST(@CD_CTR_AGENDA AS VARCHAR(MAX)),
			1 NAO_POSSUI_HR_INICIO_E_FIM,
			0 EVENT_BLOQ_EDICAO_ANTERIORMENTE,
			1 EDITAVEL,
			C.CD_USU_COMPRADOR,
			GETDATE() DT_CAD,
			A.CD_CTR_AGENDA
		FROM
			EST_PROD_AGENDA_COMPRA A
			INNER JOIN EST_COMPRADOR C ON A.CD_COMPRADOR = C.CD_COMPRADOR AND C.CD_EMP = @CD_EMP
			INNER JOIN GLB_USU U ON C.CD_USU_COMPRADOR = U.CD_USU
		WHERE
			A.CD_CTR_AGENDA = @CD_CTR_AGENDA
				
	END

	SELECT A.*, ISNULL(B.NM_BANDEIRA, 'NÃO DEFINIDO')AS NM_BANDEIRA
	  FROM @TBL_PEDIDOS A LEFT JOIN 
	       PRC_BANDEIRA B ON A.CD_BANDEIRA = B.CD_BANDEIRA

END

GO
