/*
case  when cd_etapas = '2' then  'CONFECAO          ===> AGUARDANDO SEPARAÇÃO|EM SEPARAÇÃO|CHECKOUT'
			when cd_etapas = '1' then  'RECEBIMENTO       ===> SEPARADO'
			when cd_etapas = '3' then  'Separacao         ===> CONFERIDO'
			when cd_etapas = '4' then  'PARTIDA           ===> EM TRANSITO'
			when cd_etapas = '5' then  'ENTREGUE          ===> ENTREGUE'
			when cd_etapas = '6' then  'CANCELADO         ===> CANCELADO'
			when cd_etapas = '7' then  'Alteracao         ===> ALTERDO'
			when cd_etapas = '8' then  'PENDENCIA         ===> PENDÊNCIA|PENDÊNCIA RESOLUÇÃO'
			when cd_etapas = '9' then  'Retorno           ===> RETORNO'
			when cd_etapas = '10' then 'Devolucao Total  ===> DEVOLUÇÃO' end as DsEtapa
			*/
			
-- como saber produto que representa taxa de entrega
-- pode confiar no cd_orc_etapas tele_orc
 
IF EXISTS(SELECT * 
		FROM SYS.SYSOBJECTS 
		WHERE NAME ='P_GERA_RESSUPRIMENTO_ECOMMERCE' AND TYPE=N'P')
BEGIN
	DROP PROCEDURE P_GERA_RESSUPRIMENTO_ECOMMERCE
END
GO
CREATE PROCEDURE  P_GERA_RESSUPRIMENTO_ECOMMERCE
AS

Declare @Cd_emp int=1

SET NOCOUNT ON;
/********************************************************************************************************/
--Criacao tabelas temporarias

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_PED') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_PED
	END 

	CREATE TABLE #TBL_PED (CD_EMP		INT
							,CD_FILIAL	INT
							,CD_ORC		INT
							,CD_PROD	INT
							,QT_IT_PED	MONEY
							,ConsideraEstoque Tinyint)

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_NECESSIDADE') IS NOT NULL
	BEGIN
		DROP TABLE #TBL_NECESSIDADE
	END 

	CREATE TABLE #TBL_NECESSIDADE (CD_EMP		INT
									,CD_FILIAL	INT
									,CD_PROD	INT
									,Qt_Necessidade	MONEY
									,ConsideraEstoque Tinyint)

	IF  OBJECT_ID('TEMPDB.DBO.#TBL_RESSUP') IS NOT NULL
		DROP TABLE #TBL_RESSUP;

	CREATE TABLE #TBL_RESSUP (CD_EMP		INT
								,CD_FILIAL	INT
								,CD_PROD	INT
								,QT_UNI_RESSUP MONEY
								,Flag_CaixaFecha TinyINT
								,Qt_Nec_Loja MONEY
								,Qt_Disp_Depo  MONEY
								,Qt_Atender    MONEY
								,Qt_Falta	as (Qt_Nec_Loja - Qt_Atender))
 	
	
	IF  OBJECT_ID('TEMPDB.DBO.#LstFiliaisEcommerce') IS NOT NULL
		DROP TABLE #LstFiliaisEcommerce;

	CREATE TABLE #LstFiliaisEcommerce (CD_EMP	   INT,CD_FILIAL INT, ConsideraEstoque Tinyint);
								
	IF  OBJECT_ID('TEMPDB.DBO.#LstFiliaisDevolve') IS NOT NULL
		DROP TABLE #LstFiliaisDevolve;

	CREATE TABLE #LstFiliaisDevolve (CD_EMP	   INT,CD_FILIAL INT)
								
IF  OBJECT_ID('TEMPDB.DBO.#TBL_DEVOLVER') IS NOT NULL
		DROP TABLE #TBL_DEVOLVER;

	CREATE TABLE #TBL_DEVOLVER (CD_EMP			INT
								,CD_FILIAL_PED	INT
								,CD_FILIAL_ORIG	INT
								,CD_FILIAL_DEST	INT
								,CD_PROD		INT
								,Qt_Necessidade	MONEY
								,Qt_Falta_Atender 	MONEY
								,Qt_Est_Disp_Dev	MONEY
								,Qt_Est_loja	MONEY
								,Qt_Devolver    MONEY
								,Qt_Resta_Dev  as  (Qt_Necessidade	- Qt_Devolver))


	IF  OBJECT_ID('TEMPDB.DBO.#TBL_NF_DEV') IS NOT NULL
		DROP TABLE #TBL_NF_DEV;

	CREATE TABLE #TBL_NF_DEV(CD_FILIAL_DEST				int
								    ,CD_FIL_PED			int
									,CD_FIL_ORIG		int
									,CD_NF				int
									,CD_PROD			int										 
									,Qt_Necessidade		money
									,Qt_Est_Disp_Dev	money
									,Qt_Sld_It_NF_disp_Dev money
									,Qt_Devolver		money									)
 
	IF  OBJECT_ID('TEMPDB.DBO.#TmpGlbCidDesconsiderar') IS NOT NULL
	BEGIN
		DROP TABLE #TmpGlbCidDesconsiderar
	END 

	CREATE TABLE #TmpGlbCidDesconsiderar(CD_CID INT)
/********************************************************************************************************/
	DECLARE @ErrorMessage NVARCHAR(4000);  
	DECLARE @ErrorSeverity INT;  
	DECLARE @ErrorState INT;  

/********************************************************************************************************/
-- Parametrização

	--Deposito
	DECLARE @CD_FILIAL_DEPO int = (select top 1 cd_filial from prc_filial_deposito where cd_emp=1 and cd_filial =90)
	 
	--Filiais que irão ser analisadas ecommerce
	INSERT INTO #LstFiliaisEcommerce
	SELECT			1 Emp, 900 Filial,1 ConsideraEstoque
	UNION SELECT	1 Emp, 903 Filial,0 ConsideraEstoque

	--Filiais que irão devolver mercadorias
	INSERT INTO #LstFiliaisDevolve
	SELECT			1 Emp, 2 Filial
	UNION SELECT	1 Emp, 7 Filial

	--CD Cid Teofilo
	Insert into #TmpGlbCidDesconsiderar(CD_CID)
	select CD_CID 
	From glb_cid 
	where ds_cid like '%TEOFILO OTONI%' and UF='MG'
	 
	

/********************************************************************************************************/
	--Remove os pedidos de Teofilo Otoni
	DELETE FROM R
	FROM 
		TELE_ORC O 
		JOIN TELE_ORC_ECOMMERCE_PEND_RESSUP AS R ON
			o.CD_ORC =R.cd_orc
		join GLB_CID_LOGRA L ON 
			o.CD_CID_LOGRA= L.CD_CID_LOGRA
		JOIN #TmpGlbCidDesconsiderar t ON 
			L.CD_CID = T.CD_CID
			
	--Captura os pedidos do Televendas pend. entrega
	INSERT INTO #TBL_PED 
	SELECT DISTINCT
		O.CD_EMP,
		O.CD_FILIAL,
		O.CD_ORC, 
		i.CD_PROD,
		I.QT_ITEM,
		f.ConsideraEstoque
	FROM 
		TELE_ORC O 
		JOIN TELE_ORC_ECOMMERCE_PEND_RESSUP AS R ON o.CD_ORC =R.cd_orc
		JOIN TELE_ORC_ITEM I ON 
		O.CD_ORC =I.CD_ORC
		JOIN #LstFiliaisEcommerce F ON 
		O.CD_EMP	= F.CD_EMP AND
		O.CD_FILIAL = F.CD_FILIAL

		--INTEGRA_ECOMMERCE_PEDIDO
		JOIN INTEGRADOR_VTEX_CONTROLE_PEDIDO AS b ON 
			o.CD_ORC = b.cd_orc
	WHERE
		O.CD_EMP=1
		AND O.CD_ETAPAS=2
		--AND O.ORG_PEDIDO=4 

/********************************************************************************************************/
	--Soma necessidade total
	INSERT INTO	#TBL_NECESSIDADE
	SELECT
		CD_EMP
		, CD_FILIAL
		, CD_PROD
		, SUM(QT_IT_PED) Qt_Necessidade
		, ConsideraEstoque
	FROM
		#TBL_PED
	GROUP BY 
		  CD_EMP
		, CD_FILIAL
		, CD_PROD
		, ConsideraEstoque;
		 
	--Remove os itens que possuem estoque
	DELETE FROM A FROM 
		#TBL_NECESSIDADE A
		JOIN V_EST_PROD_CPL_ESTOQUE E ON 
		A.CD_EMP	= E.CD_EMP AND
		A.CD_PROD	= E.CD_PROD AND
		A.CD_FILIAL = E.CD_FILIAL
	WHERE 
		a.ConsideraEstoque=1 
		and E.QT_EST_PEND >= A.Qt_Necessidade;
		
		
	--Remove os itens taxa de entrega
	DELETE FROM A FROM 
		#TBL_NECESSIDADE A
		JOIN EST_PROD p ON 
		A.CD_EMP	= p.CD_EMP AND
		A.CD_PROD	= p.CD_PROD  
	WHERE 
		p.FLAG_PRODUTO_TAXA_ENTREGA =1;
	 

	/*
	SELECT * FROM V_TELE_ORC_ESTOQUE WHERE CD_FILIAL =12 AND CD_PROD = 30749
	SELECT * FROM V_EST_PROD_CPL_ESTOQUE WHERE CD_FILIAL =12 AND CD_PROD = 30749
	*/

/********************************************************************************************************/
 
	INSERT INTO #TBL_RESSUP
	SELECT 
		 a.CD_EMP		
		,a.CD_FILIAL	
		,a.CD_PROD	
		,p.QT_UNI_RESSUPRIMENTO	
		,p.FLAG_RESSUPRIMENTO_CAIXA_FECHADA
		,a.Qt_Necessidade Qt_Nec_Loja	 
		,e.QT_EST - E.QT_PEND_SAI Qt_Disp_Depo
		,CASE WHEN a.Qt_Necessidade < (e.QT_EST - E.QT_PEND_SAI) THEN a.Qt_Necessidade 
			ELSE CASE WHEN (e.QT_EST - E.QT_PEND_SAI) > 0 THEN (e.QT_EST - E.QT_PEND_SAI) ELSE 0 END END Qt_Atender
	FROM 
		#TBL_NECESSIDADE A
		JOIN V_EST_PROD_CPL_ESTOQUE E ON 
		A.CD_EMP	= E.CD_EMP AND
		A.CD_PROD	= E.CD_PROD 
		JOIN EST_PROD p ON 
		A.CD_EMP	= p.CD_EMP AND
		A.CD_PROD	= p.CD_PROD  
	WHERE 
		E.CD_FILIAL =@CD_FILIAL_DEPO;
		

/********************************************************************************************************/
	--Ressuprimento

	declare @CdCtrRessup int ,  @TP_PRECO Tinyint, @CD_DEPO_OPERADOR TINYINT 
		SET @CdCtrRessup= (SELECT MAX(CD_CTR) FROM EST_PROD_TRANSF WHERE CD_EMP=@CD_EMP AND CD_FILIAL=@CD_FILIAL_DEPO);				
		SET @TP_PRECO = ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = @CD_EMP AND CD_CHAVE = 'FLAG_TP_PRECO_CUSTO_RESSUP'),9);
		SET @CD_DEPO_OPERADOR = ISNULL((SELECT FLAG_OPERADOR_LOG FROM PRC_FILIAL WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL_DEPO),0);

	begin try
		INSERT INTO  EST_PROD_TRANSF (CD_EMP,CD_FILIAL,CD_CTR,TP_TRANSF,CD_FIL_ORIG
										,CD_FIL_DEST,DT_TRANSF,DT_ENTR
										,STS_TRANSF,DT_CAD,CD_USU,HR_TRANSF
										,TP_REQ_TRANSF,OBSERVACAO
										)
			SELECT Distinct 
				@CD_Emp			Empresa,
				@CD_FILIAL_DEPO FilialDeposito,
				@CdCtrRessup + Dense_rank() over(order by  cd_filial)	CdRessup,
				1 TP_TRANSF		,
				@CD_FILIAL_DEPO FilialOrig,
				CD_FILIAL		FilialDestino,
				Convert(date,Getdate()) DT_TRANSF,
				Convert(date,Getdate()) DT_ENTR,
				0 STS_TRANSF,
				getdate() DT_CAD,
				1 CD_USU,
				getdate() HR_TRANSF,
				0 TP_REQ_TRANSF,
				'PEDIDOS ECOMMERCE' OBSERVACAO
			from 
				#TBL_RESSUP
			where
				Qt_Atender>0

			INSERT INTO  EST_PROD_TRANSF_IT (CD_EMP,CD_FILIAL,CD_CTR,CD_PROD,QT_SOL,QT_AT,VLR_CUSTO,QT_EST_RES
												,QT_MEDIAF,QT_EMAX,QT_ESEG,FLAG_RATEIO,QT_EST_DEPO_RES,PERC_RATEIO,PERC_DESC
												,QT_EST,QT_PEND_ENT,EMAX_RES,ESEG_RES,VLR_CUSTO_MD,VLR_CUSTO_ACERTO
												,QT_UNI_RESSUPRIMENTO,QT_NECESSIDADE,TP_PRECO,ALIQ_ICMS_ENTRADA,QT_SOL_ENCOMENDA
												,FLAG_LIMPAR_SALDO,VLR_CUSTO_PROD_ORIG,PARAM_ADD_AGENDA)
			Select
				@CD_Emp CD_EMP
				, @CD_FILIAL_DEPO CD_FILIAL
				, @CdCtrRessup + Dense_rank() over(order by  r.cd_filial) CD_CTR
				,r.CD_PROD
				,case when r.Qt_Uni_Ressup > 1 then
					case when Round ((r.Qt_atender/r.Qt_Uni_Ressup),2) < 0.5 then r.Qt_Uni_Ressup
						else  Round ((r.Qt_atender/r.Qt_Uni_Ressup),0) * r.Qt_Uni_Ressup end 
					else  r.Qt_atender end QT_SOL
				,0 QT_AT
				,CASE WHEN @TP_PRECO <> 9 THEN -- Se for preco parametrizado 
									dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD, @TP_PRECO,2, r.CD_FILIAL) -- Preco parametrizado 
								ELSE -- Se nao for preco parametrizado
									CASE WHEN ISNULL(l.FLAG_FILIAL_FRANQUIA,0) = 0 THEN
										CASE WHEN @CD_DEPO_OPERADOR = 1 THEN
											CASE WHEN dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD,8,2,r.CD_FILIAL) =0 THEN
													dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD,14,2,r.CD_FILIAL) --Ressuprimento
												ELSE 
													dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD,8,2,r.CD_FILIAL) 
												END  --Preco Operador Log	 
										ELSE
											dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD, @TP_PRECO,2,r.CD_FILIAL) -- Preco parametrizado
										END
									ELSE
										dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD,13,2,r.CD_FILIAL) --Preco Franquia	 
									END
								END as VLR_CUSTO 				
				,0 QT_EST_RES
				,0 QT_MEDIAF
				,0 QT_EMAX
				,0 QT_ESEG
				,0 FLAG_RATEIO
				,r.Qt_Disp_Depo QT_EST_DEPO_RES
				,0 PERC_RATEIO
				,0 PERC_DESC
				,0 QT_EST
				,0 QT_PEND_ENT
				,0 EMAX_RES
				,0 ESEG_RES
				,ISNULL(dbo.UFD_BUSCA_PRECO_PRODUTO (r.CD_EMP,@CD_FILIAL_DEPO,r.CD_PROD,0,2,r.CD_FILIAL),0) VLR_CUSTO_MD -- Custo Medio para auditorias posteriores 
				,0 VLR_CUSTO_ACERTO
				,r.Qt_Uni_Ressup QT_UNI_RESSUPRIMENTO
				,r.Qt_Nec_Loja QT_NECESSIDADE
				,0 TP_PRECO
				,0 ALIQ_ICMS_ENTRADA
				,0 QT_SOL_ENCOMENDA
				,0 FLAG_LIMPAR_SALDO
				,0 VLR_CUSTO_PROD_ORIG
				,0 PARAM_ADD_AGENDA
			fROM 
				#TBL_RESSUP R
				INNER JOIN PRC_FILIAL  l ON
				r.CD_EMP	  = l.CD_EMP AND
				r.CD_FILIAL	  = l.CD_FILIAL
			where
				r.Qt_Atender>0
		
	end try
	begin catch
		SELECT   
			@ErrorMessage = N'Falha ao gerar Ressup. -  Ecommerce.' + ERROR_MESSAGE(),  
			@ErrorSeverity = ERROR_SEVERITY(),  
			@ErrorState = ERROR_STATE();  
		  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
	end catch
/********************************************************************************************************/
--Devolucoes
/********************************************************************************************************/
	Print 'Gerando devolucoes ' + convert(varchar, getdate(),108)

	/*
		--Debugs
		Select * from #tbl_ped where cd_prod =32575
		select * from #TBL_NECESSIDADE		where cd_prod =32575
		select * from		
			#TBL_NECESSIDADE A
			JOIN V_EST_PROD_CPL_ESTOQUE E ON 
			A.CD_EMP	= E.CD_EMP AND
			A.CD_PROD	= E.CD_PROD AND
			A.CD_FILIAL = E.CD_FILIAL where a.cd_prod =32575
	*/
	update #TBL_RESSUP set Qt_Nec_loja= Qt_Falta;

	INSERT INTO #TBL_DEVOLVER	
	select
		a.CD_EMP      
		,a.CD_FILIAL LJ_PED 
		,E.CD_FILIAL LJ_ORIG 
		,@CD_FILIAL_DEPO CD_FILIAL_DEST 
		,a.CD_PROD     
		,a.Qt_Nec_loja 
		,a.Qt_Nec_Loja
		,case when e.Qt_est < a.Qt_Nec_loja then e.qt_est 
			  else a.Qt_Nec_loja end Qt_Disponivel
		,case when e.Qt_est < a.Qt_Nec_loja then e.qt_est 
			  else a.Qt_Nec_loja end Qt_Disponivel
		,0 Qt_Devolver
	from 
		#TBL_RESSUP a
		JOIN V_EST_PROD_CPL_ESTOQUE E ON 
		A.CD_EMP	= E.CD_EMP AND
		A.CD_PROD	= E.CD_PROD 
		JOIN #LstFiliaisDevolve D ON 
		E.CD_EMP	= D.CD_EMP AND
		E.CD_FILIAL	= D.CD_FILIAL 
	where
		a.Qt_Falta>0
		and e.cd_emp=1
		and e.qt_est >0 ;
	  
	DECLARE @CD_FILIAL_ORIG_NF  INT, @CD_PROD_DEV INT

	WHILE EXISTS (SELECT * 
					FROM #TBL_DEVOLVER 
						WHERE QT_RESTA_DEV>0 AND Qt_Est_Disp_Dev>0)
	BEGIN
		SELECT TOP 1
				@CD_FILIAL_ORIG_NF  = CD_FILIAL_ORIG,
				@CD_PROD_DEV		= CD_PROD
			FROM #TBL_DEVOLVER
			WHERE 
				QT_RESTA_dEV>0 
				AND Qt_Est_Disp_Dev>0
			Order by CD_FILIAL_ORIG Desc;
		
		INSERT INTO #TBL_NF_DEV			 
		SELECT TOP 1			 
			 D.CD_FILIAL_DEST
			,D.CD_FILIAL_PED
			,E.CD_FILIAL CD_FIL_ORIG
			,E.CD_NF
			,D.CD_PROD 
			,D.Qt_Falta_Atender	
			,D.Qt_Est_Disp_Dev	
			,I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD)) AS Qt_Sld_It_NF_disp_Dev
			, CASE WHEN D.Qt_Falta_Atender	<= D.Qt_Est_Disp_Dev	then
					CASE WHEN D.Qt_Falta_Atender	<= I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD))  then D.Qt_Falta_Atender	
					ELSE 
						CASE WHEN  D.Qt_Est_Disp_Dev < I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD)) then  D.Qt_Est_Disp_Dev
								ELSE  I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD)) END
							
					END  
				ELSE
					CASE WHEN D.Qt_Falta_Atender	<= I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD))  then D.Qt_Est_Disp_Dev	
					ELSE  
						CASE WHEN  D.Qt_Est_Disp_Dev < I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD)) then  D.Qt_Est_Disp_Dev
								ELSE  I.QT_IT-(SELECT dbo.BUSCA_EST_NF_ENT_IT_QT_DEV(1, D.CD_FILIAL_ORIG, E.CD_NF, D.CD_PROD)) END
					END 
				END AS Qtd_Devolver	
		FROM 
			#TBL_DEVOLVER	  D
			JOIN EST_NF_ENT E ON 
			D.CD_EMP			= E.CD_EMP AND
			D.CD_FILIAL_ORIG	= E.CD_FILIAL
			JOIN EST_NF_ENT_IT I ON 
			E.CD_EMP		= I.CD_EMP AND
			E.CD_NF			= I.CD_NF	  AND
			E.CD_FILIAL		= I.CD_FILIAL AND
			E.DT_ENT		= I.DT_ENT	  AND
			D.CD_PROD		= I.CD_PROD 
			LEFT JOIN #TBL_NF_DEV Tmp ON
			D.CD_FILIAL_DEST	=Tmp.CD_FILIAL_DEST	and
			D.CD_FILIAL_PED		=Tmp.CD_FIL_PED		and
			E.CD_FILIAL 		=Tmp.CD_FIL_ORIG	and
			e.CD_NF				=Tmp.CD_NF			and
			d.CD_PROD			=Tmp.CD_PROD			
		WHERE
			E.CD_EMP=1
			AND Tmp.CD_NF IS NULL
			AND E.DT_ENT > DATEADD(YEAR,-2,CONVERT(DATE,GETDATE())) 
			AND D.CD_FILIAL_DEST= E.CD_FORN
			AND I.CD_PROD	= @CD_PROD_DEV
			AND E.CD_FILIAL = @CD_FILIAL_ORIG_NF  
		ORDER BY E.DT_ENT DESC 

		IF @@ROWCOUNT =0 
		BEGIN
			Print 'Nenhuma NF encontrada para o produto ' + Convert(varchar, @CD_PROD_DEV ) + ' Filial ' + convert(varchar,@CD_FILIAL_ORIG_NF )
			
			UPDATE #TBL_DEVOLVER
			SET
				Qt_Est_Disp_Dev=0,
				Qt_Necessidade =Qt_devolver
			WHERE
					CD_EMP			=1
				AND CD_FILIAL_ORIG	=@CD_FILIAL_ORIG_NF
				AND CD_PROD			=@CD_PROD_DEV
		END
		
		--Atualiza tabela de devolucoes
		UPDATE D
		SET
			 QT_DEVOLVER =NF.QT_DEV_NF
			,Qt_Falta_Atender =Qt_Necessidade - NF.QT_DEV_NF
			 
		FROM 
			#TBL_DEVOLVER D
			JOIN ( SELECT
					CD_FIL_PED, 
					CD_PROD,
					sum(QT_DEVOLVER) QT_DEV_NF
				FROM    
					#TBL_NF_DEV
				group by CD_FIL_PED,CD_PROD) NF ON 
			D.CD_FILIAL_PED  = NF.CD_FIL_PED AND
			D.CD_PROD		 = NF.CD_PROD  
		
		UPDATE D
		SET
			Qt_Est_Disp_Dev = Qt_Est_loja - NF.Qt_est_gasto_dev_nf
		FROM 
			#TBL_DEVOLVER D
			JOIN ( SELECT
					CD_FIL_PED, 
					CD_PROD,
					sum(QT_DEVOLVER) Qt_est_gasto_dev_nf
					FROM    
						#TBL_NF_DEV
					WHERE
							CD_FIL_ORIG	=@CD_FILIAL_ORIG_NF
						AND CD_PROD			=@CD_PROD_DEV
				group by CD_FIL_PED,CD_PROD) NF ON 
			D.CD_FILIAL_PED  = NF.CD_FIL_PED AND
			D.CD_PROD		 = NF.CD_PROD   
		WHERE CD_FILIAL_ORIG	=@CD_FILIAL_ORIG_NF 
	END
 
/***************************************************************************************************************************************/	
	--Gerar a pre-fatura.		

	--select * from #TBL_NF_DEV ORDER BY CD_FIL_PED , CD_FIL_ORIG
	--select * from #TBL_DEVOLVER  

	DECLARE @FL_ORIG_NF INT,
			@FL_DEST_NF INT, 
			@CD_NF_DEV	INT, 
			@FL_PED		INT, 
			@CD_PED_VD	INT, 
			@CD_NT_MOV	INT 

	begin try

		declare c_NF_Devo CURSOR FAST_FORWARD FOR
		SELECT DISTINCT 
			 CD_FIL_ORIG 
			,CD_FILIAL_DEST
			,CD_NF
			,CD_FIL_PED		
		from 
			#TBL_NF_DEV

		OPEN c_NF_Devo
		FETCH NEXT FROM c_NF_Devo INTO @FL_ORIG_NF, @FL_DEST_NF, @CD_NF_DEV, @FL_PED

		WHILE @@FETCH_STATUS = 0
		BEGIN  

			SET @CD_PED_VD = ISNULL((SELECT MAX (CD_PED) FROM EST_PED_VD WHERE CD_EMP=1 AND CD_FILIAL =@FL_ORIG_NF),0)+1;

			SET @CD_NT_MOV = ISNULL((SELECT  TOP 1 CD_MOV_OP_DEV 
										FROM  
											EST_NF_ENT E 
											JOIN GLB_MOV_OP_CONF_DEVO_ENT  O ON 
											E.CD_NT_MOV =O.CD_MOV_OP
											WHERE
												E.CD_EMP=1
												AND E.CD_FILIAL = @FL_ORIG_NF						
												AND E.CD_NF		= @CD_NF_DEV),0);

			IF 	@CD_NT_MOV =0 
				SET @CD_NT_MOV =ISNULL((SELECT VALOR FROM PRC_FILIAL_CONFIG WHERE CD_CHAVE = 'CD_MOV_OP_TRANSF_FILIAIS' AND CD_EMP=1 AND CD_FILIAL = @FL_ORIG_NF),0);

			IF 	@CD_NT_MOV =0 
				PRINT 'Natureza nao encontrada.'
		
			SET @Tp_preco  = ISNULL((SELECT TP_PRECO FROM GLB_MOV_OP WHERE @CD_NT_MOV =CD_MOV_OP),0);


			INSERT INTO EST_PED_VD  (CD_EMP, CD_FILIAL, CD_PED, CD_CLI, CD_VEND, 
								CD_NT_MOV, DT_EMI_PED, DT_CAD, 
								VLR_TOT_PROD, VLR_TOT_PED, CD_COND_PGTO, 
								NM_COMPRADOR, NM_TRANSP, FRETE, OBS, CD_USU, STS_PED, DT_FAT, VLR_DESC, 
								DT_VALID, DT_ULT_ATU, DT_BX, TP_BX, HORA_CAD, TP_PED, VLR_FRETE, OBS_NF1, OBS_NF2, 
								PRZ_ENTRG, PRZ_VLD, COB, BLOQ_FATURAMENTO,VERSAO_APLICATIVO,APLICATIVO,NM_FRM)

			SELECT
				   @CD_EMP CD_EMP
				 , @FL_ORIG_NF CD_FILIAL
				 , @CD_PED_VD CD_PED
				 , CASE
					   WHEN eo.CD_GRP_ECON <> ed.CD_GRP_ECON THEN cd.CD_CLI
					   ELSE fd.CD_FILIAL
				   END CD_CLI
				 , 0 CD_VEND
				 , @CD_NT_MOV  CD_NT_MOV
				 , CONVERT(DATE, GETDATE()) DT_EMI_PED
				 , GETDATE() DT_CAD
				 , 0 VLR_TOT_PROD
				 , 0 VLR_TOT_PED
				 , 0 CD_COND_PGTO
				 , NULL NM_COMPRADOR
				 , NULL NM_TRASNP
				 , 9 FRETE
				 , NULL OBS
				 , 1 CD_USU
				 , 0 STS_PED
				 , NULL DT_FAT
				 , 0 VLR_DESC
				 , DATEADD(DAY, 5, GETDATE())
				 , NULL DT_ULT_ATU
				 , NULL DT_BX
				 , 0 TP_BX
				 , NULL HORA_CAD
				 , 0 TP_PED
				 , 0 VLR_FRETE
				 , NULL OBS_NF1
				 , NULL OBS_NF2
				 , 0 PRZ_ENTRG
				 , 0 PRZ_VLD
				 , 0 COB
				 , 0 BLOQ_FATURAMENTO
				 , 'Job ped devolucao ecommerce.:'+CONVERT(VARCHAR(6), @CD_NF_DEV)
				 , 'P_GERA_RESSUPRIMENTO_ECOMMERCE'
				 , 'P_GERA_RESSUPRIMENTO_ECOMMERCE'
			FROM
				 PRC_FILIAL fo
				 INNER JOIN PRC_GRP_ECON_PRC_FILIAL eo ON fo.CD_EMP = eo.CD_EMP
														  AND fo.CD_FILIAL = eo.CD_FILIAL
				 INNER JOIN PRC_FILIAL fd ON fd.CD_EMP = 1
											 AND fd.CD_FILIAL = @FL_DEST_NF
				 INNER JOIN PRC_GRP_ECON_PRC_FILIAL ed ON fd.CD_EMP = ed.CD_EMP
														  AND fd.CD_FILIAL = ed.CD_FILIAL
				 INNER JOIN PRC_FILIAL_RC_CLI cd ON fd.CD_EMP = cd.CD_EMP
													AND fd.CD_FILIAL = cd.CD_FILIAL
			WHERE FO.CD_FILIAL = @FL_ORIG_NF;

			print 'Grava os itens do pedido  - ' + CONVERT(VARCHAR, GETDATE(), 108);
			 INSERT INTO EST_PED_VD_IT 
			 (CD_EMP, CD_FILIAL, CD_PED, CD_PROD, QT_IT,    
		 		VLR_IT,  VLR_DESC, QT_EST_ANT, PERC_IPI, 
		 		PERC_DESC, VLR_ICMS_SUBS_FUT, VLR_PROD_PED, VLR_SUFRAMA, 
		 		ORD_GRAVA, PERC_DESC_CTR, PERC_COMI, PERC_COMI_CTR, VALIDADE, CD_TRIB,QT_SOL,CD_BENEF)
				SELECT
					   @CD_EMP
					 , @FL_ORIG_NF
					 , @CD_PED_VD
					 , D.CD_PROD
					 , D.QT_DEVOLVER 
					 , CASE WHEN @TP_PRECO =1 THEN  C.VLR_CUSTO 
							WHEN @TP_PRECO =2 THEN  C.VLR_COMPRA  
							WHEN @TP_PRECO =4 THEN  C.VLR_CUSTO_MD 
							ELSE C.VLR_CUSTO_MD  END 
					 , VLR_DESC = 0
					 , QT_EST_ANT = 0
					 , PERC_IPI = 0
					 , PERC_DESC = 0
					 , VLR_ICMS_SUBS_FUT = 0
					 , VLR_PROD_PED = 0
					 , VLR_SUFRAMA = 0
					 , ORD_GRAVA = 0
					 , PERC_DESC_CTR = 0
					 , PERC_COMI = 0
					 , PERC_COMI_CTR = 0
					 , VALIDADE = 0
					 , CD_TRIB = 0
					 , D.QT_DEVOLVER 
					 , F.CD_BENEF
				FROM
					 #TBL_NF_DEV d
					 INNER JOIN EST_PROD P	ON   1 = p.CD_EMP
											  AND D.CD_PROD = p.CD_PROD
					 INNER JOIN EST_PROD_CPL C	ON   
												  P.CD_EMP  = C.CD_EMP
											  AND P.CD_PROD = C.CD_PROD
											  AND C.CD_FILIAL = @FL_ORIG_NF
					 LEFT JOIN EST_PROD_COD_BENEF F ON P.CD_EMP = F.CD_EMP
													   AND P.CD_PROD = F.CD_PROD
				WHERE
					D. CD_FIL_ORIG		 = @FL_ORIG_NF 
					AND D.CD_FILIAL_DEST = @FL_DEST_NF
					AND D.CD_NF			 = @CD_NF_DEV
					AND D.CD_FIL_PED	 = @FL_PED
					and p.CD_EMP	=1;
		
				--Altera o total do pedido		
				update p
				set
					VLR_TOT_PROD= tot.vlr_tot,
					VLR_TOT_PED = tot.vlr_tot
				from
					EST_PED_VD p
					join (select 
								CD_EMP, CD_FILIAL, CD_PED, SUM (QT_IT * VLR_IT) vlr_tot 
							from 
								EST_PED_VD_IT	
							WHERE
									CD_FILIAL   = @FL_ORIG_NF 
								AND CD_PED		= @CD_PED_VD
								AND CD_EMP		=1
							GROUP BY CD_EMP, CD_FILIAL, CD_PED) Tot on
							p.CD_EMP	= tot.CD_EMP and  
							p.CD_FILIAL = tot.CD_FILIAL and
							p.CD_PED	= tot.CD_PED;

			/*
			--select * from EST_NF_ENT_IT_DEVOLUCAO
			--insert into (CD_EMP,CD_FILIAL,CD_NF,CD_PROD,QTDE_PROD,QTDE_PROD_DEVO_GERADA,DT_DEVOLUCAO,CD_USU,DT_CAD)
			SELECT
				1 CD_EMP
				, @FL_ORIG_NF CD_FILIAL
				, @CD_NF_DEV
				, d.CD_PROD
				, D.QT_DEVOLVER 
				, D.QT_DEVOLVER 
				, CONVERT(DATE, GETDATE())
				, 1
				, GETDATE()
			from 
				#TBL_NF_DEV d
			where  
				D. CD_FIL_ORIG		 = @FL_ORIG_NF 
				AND D.CD_FILIAL_DEST = @FL_DEST_NF
				AND D.CD_NF			 = @CD_NF_DEV
				AND D.CD_FIL_PED	 = @FL_PED ;
			*/

			--select * from EST_NF_ENT_EST_PED_VD
			INSERT INTO EST_NF_ENT_EST_PED_VD (CD_EMP,CD_FILIAL,CD_NF,CD_PED,NR_PROTOCOLO,FLAG_PROT_CANCELADO)
			SELECT
				   @CD_EMP CD_EMP
				 , @FL_ORIG_NF CD_FILIAL
				 , @CD_NF_DEV
				 , @CD_PED_VD CD_PED
				 ,null
				 ,0 
	 
		
			FETCH NEXT FROM c_NF_Devo INTO @FL_ORIG_NF,@FL_DEST_NF, @CD_NF_DEV, @FL_PED
		END

		--Remove as pendencias
		delete from r
		from 
			#TBL_PED O 
			JOIN TELE_ORC_ECOMMERCE_PEND_RESSUP AS R ON
				o.CD_ORC =R.cd_orc;

		CLOSE c_NF_Devo;
		DEALLOCATE c_NF_Devo; 
	end try
	begin catch
		SELECT   
			@ErrorMessage = N'Falha ao gerar Pré Faturas -  Ecommerce.' + ERROR_MESSAGE(),  
			@ErrorSeverity = ERROR_SEVERITY(),  
			@ErrorState = ERROR_STATE();  
		  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  


	end catch



	