IF EXISTS (SELECT  *  FROM  sys.objects
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_PBM_VINCULACAO]') AND
						type IN ( N'P', N'PC' ))
	BEGIN
			DROP PROCEDURE [dbo].[P_EST_NF_ENT_PBM_VINCULACAO]
	END

GO

--EXEC P_EST_NF_ENT_PBM_VINCULACAO 1
CREATE PROCEDURE P_EST_NF_ENT_PBM_VINCULACAO @CD_EMP INT=1
AS
	SET NOCOUNT ON
	--SET ANSI_WARNINGS off; 
	
	-----------------------------------------------------------------------------------------------------------------

	create table #TblDanfeAnalisar (CD_CTR INT, FLAG_REPOSICAO_PBM TINYINT, FLAG_REPOSICAO_PBM_DANFE TINYINT )

	INSERT INTO #TblDanfeAnalisar
	SELECT
		d.cd_Ctr,
		1 FLAG_REPOSICAO_PBM,
		FLAG_REPOSICAO_PBM
	FROM
		V_EST_NF_ENT_DANFE_PROVISORIO_REPOSICAO_PBM p
		JOIN EST_NF_ENT_DANFE_PROVISORIO d ON 
		p.cd_ctr = d.cd_ctr
	WHERE  
		p.PERC_ATEND = 100
		AND d.dt_Cad > GETDATE() - 5;
	-----------------------------------------------------------------------------------------------------------------


	--Retirando reposicao PBM quando a compra possui vinculo com pedido de compras
	UPDATE
		EST_NF_ENT_DANFE_PROVISORIO
	SET 
		EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =0
	from
		#TblDanfeAnalisar A 
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_PENDENTE.CD_CTR 
		INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR 
		INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP.CD_CTR 
	WHERE
		EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =1
		
	-----------------------------------------------------------------------------------------------------------------
	--Indicando que o danfe tem reposicao e nao possui pedido de compras
	UPDATE
		EST_NF_ENT_DANFE_PROVISORIO
	SET 
		EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =1
	from
		#TblDanfeAnalisar A 
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_PENDENTE.CD_CTR 
		INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR 
		LEFT OUTER JOIN  EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP.CD_CTR 
	WHERE
		EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP.CD_CTR  IS NULL
		AND EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =0	

	-----------------------------------------------------------------------------------------------------------------
	PRINT 'Marcando nota fiscal de entrada com reposicao PBM - Time ' + convert(varchar(20),getdate(),108); 
	--Marcando danfe que possui entrada de nota fiscal
	UPDATE
		EST_NF_ENT_DANFE_PROVISORIO
	SET 
		EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =1
	from
		#TblDanfeAnalisar A
		INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR 
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR 
		INNER JOIN EST_NF_ENT ON
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF  = EST_NF_ENT.CD_NF 
		LEFT OUTER JOIN  EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP ON
		A.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP.CD_CTR 
		LEFT OUTER JOIN EST_NF_ENT_IT_PBM_GLOSADA ON
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP = EST_NF_ENT_IT_PBM_GLOSADA.CD_EMP AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT_PBM_GLOSADA.CD_FILIAL AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF  = EST_NF_ENT_IT_PBM_GLOSADA.CD_NF 
	WHERE
		EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP.CD_CTR  IS NULL
		AND EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =0
		AND EST_NF_ENT.CD_EMP=@CD_EMP
		AND EST_NF_ENT.DT_ENT >=GETDATE()-30
		AND EST_NF_ENT_IT_PBM_GLOSADA.CD_NF IS NULL;

	-----------------------------------------------------------------------------------------------------------------
	--Marcando a entrada da nota fiscal
	UPDATE
		EST_NF_ENT
	SET 
		EST_NF_ENT.FLAG_REPOSICAO_PBM =1
	from
		EST_NF_ENT_DANFE_PROVISORIO 
		INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
		EST_NF_ENT_DANFE_PROVISORIO.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR 
		INNER JOIN EST_NF_ENT ON
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF  = EST_NF_ENT.CD_NF 
		LEFT OUTER JOIN EST_NF_ENT_IT_PBM_GLOSADA ON
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP = EST_NF_ENT_IT_PBM_GLOSADA.CD_EMP AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT_PBM_GLOSADA.CD_FILIAL AND
		EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF  = EST_NF_ENT_IT_PBM_GLOSADA.CD_NF 	
	WHERE
		EST_NF_ENT_DANFE_PROVISORIO.FLAG_REPOSICAO_PBM =1
		AND EST_NF_ENT_DANFE_PROVISORIO.dt_Cad > GETDATE() - 5
		AND EST_NF_ENT.DT_ENT > GETDATE()-15
		AND EST_NF_ENT.CD_EMP=@CD_EMP
		AND EST_NF_ENT_IT_PBM_GLOSADA.CD_NF IS NULL 
	
	-----------------------------------------------------------------------------------------------------------------
	PRINT 'Vinculando a reposicao a PBM - Time ' + convert(varchar(20),getdate(),108); 		
	INSERT INTO EST_NF_ENT_PBM	(CD_EMP,CD_FILIAL,	CD_NF,	CD_CONV,	CD_USU,	DT_CAD)
	SELECT
		DISTINCT EST_NF_ENT.CD_EMP,
		EST_NF_ENT.CD_FILIAL,
		EST_NF_ENT.CD_NF,
		EST_PROD_RC_CLI_CONV.CD_CONV,
		EST_NF_ENT.CD_USU,
		GETDATE()
	FROM
		EST_NF_ENT 
		INNER JOIN EST_NF_ENT_IT ON
		EST_NF_ENT.CD_EMP	 = EST_NF_ENT_IT.CD_EMP AND
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
		EST_NF_ENT.CD_NF	 = EST_NF_ENT_IT.CD_NF AND 
		EST_NF_ENT.DT_ENT 	 = EST_NF_ENT_IT.DT_ENT 
		INNER JOIN EST_PROD ON
		EST_NF_ENT_IT.CD_EMP = EST_PROD.CD_EMP AND
		EST_NF_ENT_IT.CD_PROD = EST_PROD.CD_PROD
		INNER JOIN EST_PROD_RC_CLI_CONV ON
		EST_NF_ENT_IT.CD_EMP = EST_PROD_RC_CLI_CONV.CD_EMP AND
		EST_NF_ENT_IT.CD_PROD = EST_PROD_RC_CLI_CONV.CD_PROD 
		LEFT OUTER JOIN EST_NF_ENT_PBM ON
		EST_NF_ENT.CD_EMP = EST_NF_ENT_PBM.CD_EMP AND
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_PBM.CD_FILIAL AND
		EST_NF_ENT.CD_NF = EST_NF_ENT_PBM.CD_NF
		LEFT OUTER JOIN EST_NF_ENT_IT_PBM_GLOSADA ON
		EST_NF_ENT.CD_EMP = EST_NF_ENT_IT_PBM_GLOSADA.CD_EMP AND
		EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT_PBM_GLOSADA.CD_FILIAL AND
		EST_NF_ENT.CD_NF  = EST_NF_ENT_IT_PBM_GLOSADA.CD_NF 
	WHERE
		EST_NF_ENT.CD_EMP =@CD_EMP
		AND EST_NF_ENT_IT.CD_EMP=@CD_EMP
		AND EST_NF_ENT_IT.DT_ENT >=GETDATE()-30
		AND EST_NF_ENT.FLAG_REPOSICAO_PBM=1
		AND EST_NF_ENT_PBM.CD_NF IS NULL
		AND EST_NF_ENT_IT_PBM_GLOSADA.CD_NF IS NULL