IF OBJECT_ID('P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST
	END
GO 
--EXEC P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST 2,38878
CREATE PROCEDURE [dbo].[P_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST] @CD_FILIAL_PAR INT=0, @CD_PROD_PAR INT=0 WITH ENCRYPTION AS  
	BEGIN
	SET CONTEXT_INFO 0x01010101;
	 
		SET NOCOUNT ON
		DECLARE @CD_EMP		INT
		DECLARE @CD_FILIAL	INT
		DECLARE @CD_PROD	INT			
		DECLARE @CD_REGIAO_FISCAL INT	

		
		IF EXISTS ( SELECT * FROM SYS.OBJECTS
				WHERE NAME ='#TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST')
		BEGIN 
			DROP TABLE #TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST
		END

		CREATE TABLE #TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST(
			[CD_EMP]			[int] NOT NULL,
			[CD_REGIAO_FISCAL]	[int] NOT NULL,
			[CD_PROD]			[int] NOT NULL,
			[CD_TRIB]			[int] NOT NULL,
			[TP_MOV]			[int] NOT NULL,
			[TP_TRANSA]			[int] NOT NULL,
			[PERC_RED]			[money] NOT NULL,
			[TP_SUBS]			[money] NOT NULL,
			[VLR_PAUTA_IVA]		[money] NOT NULL,
			[PERC_ICMS]			[money] NOT NULL,
			[TP_RED_BASE_IMP]	[money] NOT NULL,
			[PERC_ICMS_EST]		[money] NOT NULL,
			[PERC_RED_SUBS_TRIB] [money] NULL,
			[PERC_RED_ESTORNO]	[money] NULL,
			[DT_CAD]			[datetime] NULL,
			[CD_USU]			[int] NULL,
			[CST_PIS]			[int] NULL,
			[CST_COFINS]		[int] NULL,
			[PERC_REPASSE]		[money] NULL,
			[PERC_IBPT]			[money] NULL,
			[PERC_PIS]			[money] NULL,
			[PERC_COFINS]		[money] NULL,
			[PERC_FECOP]		[money] NULL,
			[PERC_FECOP_ST]		[money] NULL,
			[PERC_ICMS_DESONERADO] [money] NULL,
			[CST_TP_TRIB_PIS]		[INT] NULL,
			[CST_TP_TRIB_COFINS]	[INT] NULL
			--PRIMARY KEY (CD_PROD,CD_REGIAO_FISCAL,CD_EMP)
			);
		
		
		SET NOCOUNT ON	 
		
		IF @CD_FILIAL_PAR <> 0 
		begin
			IF NOT EXISTS (SELECT TOP 1 CD_EMP FROM PRC_FILIAL_CONFIG WHERE CD_FILIAL = @CD_FILIAL_PAR AND CD_CHAVE = 'FLAG_CONS_TRIB_GRUPO' AND VALOR = '1')
				BEGIN
					RETURN
				END
		end

		IF @CD_FILIAL_PAR = 0 
		begin
			IF NOT EXISTS (SELECT TOP 1 CD_EMP FROM PRC_EMP_CONFIG WHERE CD_CHAVE = 'FLAG_CONS_TRIB_GRUPO' AND VALOR = '1')
				BEGIN
					RETURN
				END
		end
				
		DECLARE RS_TRIB CURSOR FAST_FORWARD	FOR	
		SELECT 
			F.CD_EMP, 
			MIN(F.CD_FILIAL) AS CD_FILIAL, 
			P.CD_REGIAO_FISCAL
		FROM 
			EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND P							
			INNER JOIN PRC_REGIAO_FISCAL_PRC_FILIAL REG_F ON 
				P.CD_REGIAO_FISCAL = REG_F.CD_REGIAO_FISCAL
			INNER JOIN PRC_FILIAL F ON 
				F.CD_EMP		= REG_F.CD_EMP
				AND F.CD_FILIAL = REG_F.CD_FILIAL
		WHERE
			F.CD_FILIAL= CASE WHEN @CD_FILIAL_PAR = 0 THEN F.CD_FILIAL ELSE @CD_FILIAL_PAR END
			AND F.ESPECIE_FILIAL=1
			AND F.STS_FILIAL=0
		GROUP BY
			F.CD_EMP, 
			P.CD_REGIAO_FISCAL 

		OPEN RS_TRIB
		FETCH NEXT FROM RS_TRIB INTO @CD_EMP, @CD_FILIAL, @CD_REGIAO_FISCAL

		WHILE @@FETCH_STATUS=0
			BEGIN	
				PRINT 'Filial: ' + CONVERT(VARCHAR(4),@CD_FILIAL) + ' Regiao fiscal: ' + CONVERT(VARCHAR(4),@CD_REGIAO_FISCAL) + ' - ' + CONVERT(VARCHAR,getdate(),108)
				----------------------------------------------------------------------------------------------------
				--INSERINDO TRIBUTACOES NA TABELA TEMPORARIA
				----------------------------------------------------------------------------------------------------
							
				INSERT INTO #TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST
					(CD_EMP,
					CD_REGIAO_FISCAL,
					CD_PROD,
					CD_TRIB,
					TP_MOV,
					TP_TRANSA,
					PERC_RED,
					TP_SUBS,
					VLR_PAUTA_IVA,
					PERC_ICMS,
					TP_RED_BASE_IMP,
					PERC_ICMS_EST,
					PERC_RED_SUBS_TRIB,
					PERC_RED_ESTORNO,
					DT_CAD,
					CD_USU,
					CST_PIS,
					CST_COFINS,
					PERC_REPASSE,
					PERC_IBPT,
					PERC_PIS,
					PERC_COFINS,
					PERC_FECOP,
					PERC_FECOP_ST,
					PERC_ICMS_DESONERADO,
					CST_TP_TRIB_PIS,
					CST_TP_TRIB_COFINS)
					SELECT 			
						T.CD_EMP,
						@CD_REGIAO_FISCAL,
						T.CD_PROD,
						T.CD_TRIB,
						T.TP_MOV,
						T.TP_TRANSA,
						T.PERC_RED,
						T.TP_SUBS,
						T.VLR_PAUTA_IVA,
						T.PERC_ICMS,
						T.TP_RED_BASE_IMP,
						T.PERC_ICMS_EST,
						T.PERC_RED_SUBS_TRIB,
						T.PERC_RED_ESTORNO,
						CONVERT(VARCHAR(10), GETDATE(),102) AS DT_CAD,
						1 AS CD_USU,
						T.CST_PIS,
						T.CST_COFINS,
						T.PERC_REPASSE,
						T.PERC_IBPT,
						T.PERC_PIS,
						T.PERC_COFINS,
						T.PERC_ALIQ_FECOP,
						T.PERC_ALIQ_FECOP,
						CASE WHEN T.ICMS_DESONERADO = 1 THEN
							T.PERC_ICMS_EST
						ELSE
							0
						END AS PERC_ICMS_DESONERADO,
						T.CST_TP_TRIB_PIS,
						T.CST_TP_TRIB_COFINS
					FROM
						EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND P
						CROSS APPLY UFD_EST_PROD_TRIB(@CD_EMP, @CD_FILIAL, 0, 0, P.CD_PROD, 0, 0, 1,0,0,0,0,0,0,0,0) T 
					WHERE 
						P.CD_REGIAO_FISCAL = @CD_REGIAO_FISCAL
						AND P.CD_PROD=CASE WHEN @CD_PROD_PAR=0 THEN P.CD_PROD ELSE @CD_PROD_PAR END ;
							
				
				PRINT 'Exclui EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST ' + CONVERT(VARCHAR,getdate(),108)
				----------------------------------------------------------------------------------------------------
				--DELETANDO TRIBUTACOES OFICIAIS QUE EXISTE NA TABELA TEMPORARIA
				----------------------------------------------------------------------------------------------------				
				
				
				DELETE I
				FROM 
					#TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST T
					inner join EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST I on 
						I.CD_EMP  				= T.CD_EMP AND 
						I.CD_REGIAO_FISCAL 		= T.CD_REGIAO_FISCAL AND 
						I.CD_PROD 				= T.CD_PROD AND 
						I.TP_MOV 				= T.TP_MOV  AND 
						I.TP_TRANSA 			= T.TP_TRANSA;
				
				
				PRINT 'Insere EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST ' + CONVERT(VARCHAR,getdate(),108)
				----------------------------------------------------------------------------------------------------
				--INCLUINDO TRIBUTACOES OFICIAIS A PARTIR DA TABELA TEMPORARIA
				----------------------------------------------------------------------------------------------------						  
							 
				INSERT INTO EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST
					   (CD_EMP,
						CD_REGIAO_FISCAL,
						CD_PROD,
						CD_TRIB,
						TP_MOV,
						TP_TRANSA,
						PERC_RED,
						TP_SUBS,
						VLR_PAUTA_IVA,
						PERC_ICMS,
						TP_RED_BASE_IMP,
						PERC_ICMS_EST,
						PERC_RED_SUBS_TRIB,
						PERC_RED_ESTORNO,
						DT_CAD,
						CD_USU,
						CST_PIS,
						CST_COFINS,
						PERC_REPASSE,
						PERC_IBPT,
						PERC_PIS,
						PERC_COFINS,
						PERC_FECOP,
						PERC_FECOP_ST,
						PERC_ICMS_DESONERADO,
						CST_TP_TRIB_PIS,
						CST_TP_TRIB_COFINS)
					SELECT 			
						distinct T.CD_EMP,
						T.CD_REGIAO_FISCAL,
						T.CD_PROD,
						T.CD_TRIB,
						T.TP_MOV,
						T.TP_TRANSA,
						T.PERC_RED,
						T.TP_SUBS,
						T.VLR_PAUTA_IVA,
						T.PERC_ICMS,
						T.TP_RED_BASE_IMP,
						T.PERC_ICMS_EST,
						T.PERC_RED_SUBS_TRIB,
						T.PERC_RED_ESTORNO,
						T.DT_CAD,
						T.CD_USU,
						T.CST_PIS,
						T.CST_COFINS,
						T.PERC_REPASSE,
						T.PERC_IBPT,
						T.PERC_PIS,
						T.PERC_COFINS,
						T.PERC_FECOP,
						T.PERC_FECOP_ST,
						PERC_ICMS_DESONERADO,
						CST_TP_TRIB_PIS,
						CST_TP_TRIB_COFINS
					FROM 
						#TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST T;
						     
				PRINT 'Ativa carga ' + CONVERT(VARCHAR,getdate(),108);

				if isnull(@CD_PROD_PAR,0) =0 
				begin
					UPDATE 
						T 
					SET
						DT_ATUALIZA_PARAM=GETDATE()
					FROM 
						EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND P
						inner join EST_PROD_CPL T on 
							P.CD_EMP 			= T.CD_EMP AND 
							P.CD_PROD 			= T.CD_PROD 
					WHERE
						T.CD_EMP= @CD_EMP
						--AND T.CD_FILIAL = @CD_FILIAL
						AND P.CD_REGIAO_FISCAL= @CD_REGIAO_FISCAL;

					----------------------------------------------------------------------------------------------------
					--DELETANDO PENDENCIA DE TRIBUTACOES QUE EXISTE NA TABELA TEMPORARIA
					----------------------------------------------------------------------------------------------------	
					PRINT 'Remove a pendencia ' + CONVERT(VARCHAR,getdate(),108);				
					DELETE FROM P
					FROM 
						EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND P
						inner join #TMP_EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST T on 
						T.CD_EMP 			= P.CD_EMP AND 
						T.CD_PROD 			= P.CD_PROD AND 
						T.CD_REGIAO_FISCAL  = P.CD_REGIAO_FISCAL;
				end
				else
				begin
					UPDATE  T 
					SET
						DT_ATUALIZA_PARAM=GETDATE()
					FROM 
						  EST_PROD_CPL T 
					WHERE
						T.CD_EMP= @CD_EMP
						AND T.CD_FILIAL = @CD_FILIAL_PAR
						AND T.CD_PROD = @CD_PROD_PAR 
				end
				
				
				FETCH NEXT FROM RS_TRIB INTO @CD_EMP, @CD_FILIAL, @CD_REGIAO_FISCAL
			END

		CLOSE RS_TRIB;	
		DEALLOCATE RS_TRIB;
	END
	--Deletando pendencias antigas
	DELETE 
	FROM
		EST_PROD_PRC_REGIAO_FISCAL_TRIB_EST_PEND 
	WHERE	
		DT_CAD <= GETDATE()-10
