/****** Object:  StoredProcedure [dbo].[P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_GERAR_PENDENCIA_ENVIO_PDV]    Script Date: 07/11/2013 14:02:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_GERAR_PENDENCIA_ENVIO_PDV]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_GERAR_PENDENCIA_ENVIO_PDV]
GO
GO
--P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_GERAR_PENDENCIA_ENVIO_PDV 5
CREATE PROCEDURE P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_GERAR_PENDENCIA_ENVIO_PDV @CD_REGIAO_FISCAL INT 
AS 
BEGIN	
	DECLARE @CD_EMP		INT
	DECLARE @CD_PROD	INT	
	DECLARE @CD_CTR		INT
	
	SET NOCOUNT ON	 
	
		DECLARE RS_TRIB CURSOR FAST_FORWARD	FOR	
			SELECT DISTINCT GRP.CD_EMP, GRP.CD_REGIAO_FISCAL, cpl.CD_PROD, GRP.CD_CTR 
			FROM EST_PROD_EST_TRIB_CONF_GRP cpl
				INNER JOIN EST_TRIB_CONF_GRP GRP ON 
					cpl.CD_EMP = GRP.CD_EMP AND
					cpl.CD_CTR = GRP.CD_CTR	
				INNER JOIN EST_TRIB_CONF_GRP_CPL GRP_CPL ON 
					GRP.CD_EMP = GRP_CPL.CD_EMP
					AND GRP.CD_CTR = GRP_CPL.CD_CTR			
			WHERE GRP_CPL.TP_MOV		= 0
					AND GRP_CPL.TP_TRANSA	= 1
					AND GRP.CD_REGIAO_FISCAL = @CD_REGIAO_FISCAL
					AND EXISTS (SELECT 
									F.CD_EMP
								FROM 
									PRC_FILIAL F INNER JOIN GLB_CID CID ON
									F.CD_CID = CID.CD_CID 
								WHERE	
									CID.UF COLLATE SQL_Latin1_General_CP1_CI_AS = GRP_CPL.UF COLLATE SQL_Latin1_General_CP1_CI_AS)				  				

		OPEN RS_TRIB
		FETCH NEXT FROM RS_TRIB INTO @CD_EMP, @CD_REGIAO_FISCAL, @CD_PROD, @CD_CTR
			
		WHILE @@FETCH_STATUS=0
			BEGIN
				IF NOT EXISTS (SELECT P.CD_EMP 
								FROM EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND P
								WHERE P.CD_EMP = @CD_EMP
										AND P.CD_REGIAO_FISCAL = @CD_REGIAO_FISCAL
										AND P.CD_PROD = @CD_PROD
										AND P.CD_CTR  = @CD_CTR)
					BEGIN							 
						INSERT INTO EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND
							VALUES (@CD_EMP, @CD_REGIAO_FISCAL, @CD_PROD, @CD_CTR, GETDATE())
					END
					
				FETCH NEXT FROM RS_TRIB INTO @CD_EMP, @CD_REGIAO_FISCAL, @CD_PROD, @CD_CTR
			END
			
		CLOSE RS_TRIB;	
		DEALLOCATE RS_TRIB;
END


