/****** Object:  StoredProcedure [dbo].[P_GERA_PIS_COFINS_PDV_VD_DEV_EST_PROD_IMP]    Script Date: 14/04/2014 17:31:00 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_ATUALIZA_PRECO_TABLOIDE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_GERA_PIS_COFINS_PDV_VD_DEV_EST_PROD_IMP]
GO
GO
GO
 
-- VERSAO 2954
--EXEC P_GERA_PIS_COFINS_PDV_VD_DEV_EST_PROD_IMP 1,1,'2014-04-01','2014-04-30',0,1
CREATE PROCEDURE [dbo].[P_GERA_PIS_COFINS_PDV_VD_DEV_EST_PROD_IMP] (@CD_EMP INT, 
													   @CD_FILIAL INT, 
													   @DT_INI DATETIME,
													   @DT_FIM DATETIME,
													   @VISUALIZAR INT = 0,
													   @FLAG_REPROCESSAR INT=0)  WITH ENCRYPTION  AS 

													   
----Verifica bloqueio contábil
--IF EXISTS(SELECT  TOP 1 CD_EMP
--			FROM 
--				CTB_CONTROLE_EXPORTACAO
--			WHERE
--				LIBERADO = 'N'
--				AND DT_EXPORT >= @DT_INI
--				AND CD_EMP=@CD_EMP
--				AND CD_FILIAL=@CD_FILIAL)
--begin
--	print 'Data base com bloqueio contabil - Time : ' +convert(varchar,getdate(),108);   
--	--Retirado a pedido sr. Leandro CSC
-- 	--RETURN;
--end

CREATE TABLE #RsSaidasPdv (
						CD_EMP							INT,
						CD_FILIAL						INT,
						CD_VD							INT,
						CD_IT							INT,
						CD_PROD							INT,
						VLR_BASE_PIS					MONEY, 
						VLR_PIS							MONEY,		
						PERC_ALIQ_PIS					MONEY,	
						VLR_BASE_COFINS					MONEY,
						VLR_COFINS						MONEY,
						PERC_ALIQ_COFINS				MONEY, 
						CST_PIS							INT,		
						CST_COFINS						INT,
						PIS_COFINS_RECALCULADO			INT,
						LISTA_PROD						INT,
						TP_TRANSA						INT,
						PIS_COFINS						INT,
						TOTALOPERACAO					MONEY,
						TIPO_TRIBUTACAO_PIS_COFINS		INT)


DECLARE @PERCPIS MONEY
DECLARE @PERCCOFINS MONEY

SELECT @PERCPIS  = PERC_IMP FROM GLB_IMPOSTOS WHERE TIPO_IMPOSTO = 1
SELECT @PERCCOFINS  = PERC_IMP FROM GLB_IMPOSTOS WHERE TIPO_IMPOSTO = 2

--Gerando tabela inicial com o calculo padrao de PIS/COFINS
INSERT INTO #RsSaidasPdv
SELECT
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.CD_VD,
	PDV_VD_IT.CD_IT,
	PDV_VD_IT.CD_PROD,
	
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
	  (TOT_IT - VLR_DESC_TOT_RAT)	
	ELSE
		0
	END AS VLR_BASE_PIS, 
	
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
			(TOT_IT - VLR_DESC_TOT_RAT) * (@PERCPIS / 100)	
		ELSE
			0
	 				
	END AS  VLR_PIS,		
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
		@PERCPIS 
	ELSE
		0
	END AS PERC_ALIQ_PIS,
		
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
	   (TOT_IT - VLR_DESC_TOT_RAT)	
	ELSE
		0
	END AS VLR_BASE_COFINS, 
	
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
		 
	  (TOT_IT - VLR_DESC_TOT_RAT) * (@PERCCOFINS / 100)	
	  
	ELSE
		0
	END AS  VLR_COFINS,	
		
	CASE WHEN PDV_VD_IT.PIS_COFINS = 1 THEN 
		@PERCCOFINS
	ELSE
		0
	END  AS PERC_ALIQ_COFINS,

	CASE WHEN ISNULL(EST_PROD.FLAG_TRIB_PIS_COFINS_PROD,0) = 0 THEN
		0 
	ELSE
		ISNULL(EST_PROD.CD_CST_PIS_SAIDA,0) 
	END AS CST_PIS,		
	
	CASE WHEN ISNULL(EST_PROD.FLAG_TRIB_PIS_COFINS_PROD,0) = 0 THEN
		0 
	ELSE
		ISNULL(EST_PROD.CD_CST_COFINS_SAIDA,0) 
	END AS CST_COFINS,
	
	1 AS PIS_COFINS_RECALCULADO,
	PDV_VD_IT.LISTA_PROD,
	1 AS TP_MOV_OP_TRANSA,
	PDV_VD_IT.PIS_COFINS,
	(TOT_IT - VLR_DESC_TOT_RAT) AS TOTALOPERACAO,
	ISNULL(PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS,0)
FROM
	PDV_VD 
	INNER JOIN PDV_VD_IT ON
	PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND
	PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
	PDV_VD.CD_VD = PDV_VD_IT.CD_VD AND
	PDV_VD.DT_VD = PDV_VD_IT.DT_VD 
	
	INNER JOIN PDV_VD_DEV_EST_PROD_IMP D ON
	PDV_VD_IT.CD_EMP		= D.CD_EMP AND
	PDV_VD_IT.CD_FILIAL	= D.CD_FILIAL AND
	PDV_VD_IT.CD_VD		= D.CD_VD AND
	PDV_VD_IT.CD_IT		= D.CD_IT AND
	PDV_VD_IT.CD_PROD		= D.CD_PROD
	 
	
	INNER JOIN EST_PROD ON
	PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP AND
	PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD
WHERE
	PDV_VD.CD_EMP = @CD_EMP 
	AND PDV_VD.CD_FILIAL = @CD_FILIAL 
	AND D.DT_DEVO BETWEEN @DT_INI AND @DT_FIM

------Configuracao por lista, transacao e filial	
UPDATE
	s
SET 
	CST_PIS  = CD_CST,
	CST_COFINS  = CD_CST
FROM	
	#RsSaidasPdv s INNER JOIN LISTA_PROD_CST_PIS_COFINS ON
	s.CD_FILIAL  =  LISTA_PROD_CST_PIS_COFINS.CD_FILIAL AND 
	s.LISTA_PROD = LISTA_PROD_CST_PIS_COFINS.CD_LISTA AND
	s.TP_TRANSA  =  LISTA_PROD_CST_PIS_COFINS.TP_TRANSA AND 
	s.PIS_COFINS = LISTA_PROD_CST_PIS_COFINS.PIS_COFINS AND
	s.TIPO_TRIBUTACAO_PIS_COFINS = LISTA_PROD_CST_PIS_COFINS.TIPO_TRIBUTACAO_PIS_COFINS
WHERE
	(CST_PIS + CST_COFINS) = CASE WHEN @FLAG_REPROCESSAR = 1 THEN (CST_PIS + CST_COFINS) ELSE 0 END 

----Tratando pis por configuracao de natureza
UPDATE
	s
SET 
	VLR_BASE_PIS = CASE WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					TOTALOPERACAO
				  WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
				 ELSE
					VLR_BASE_PIS	  
				END,	
	VLR_PIS = CASE WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					0
			 WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
			  ELSE
					VLR_PIS	  
			  END,
	PERC_ALIQ_PIS = CASE WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					0
				  WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
			  ELSE
					PERC_ALIQ_PIS	  
			  END
FROM	
	#RsSaidasPdv	s INNER JOIN CST_PIS_COFINS ON
	s.CST_PIS = CST_PIS_COFINS.CD_CST

----Tratando COFINS por configuracao de natureza
UPDATE
	s
SET 
	VLR_BASE_COFINS = CASE WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					TOTALOPERACAO
				  WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
				 ELSE
					VLR_BASE_COFINS	  
				END,	
	VLR_COFINS = CASE WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					0
				  WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
			  ELSE
					VLR_COFINS	  
			  END,
	PERC_ALIQ_COFINS =CASE  WHEN  CST_PIS_COFINS.TP_TRIB = 2 THEN
					0
				  WHEN  CST_PIS_COFINS.TP_TRIB = 0 THEN
						0
			  ELSE
					PERC_ALIQ_COFINS	  
			  END
FROM	
	#RsSaidasPdv	s INNER JOIN CST_PIS_COFINS ON
	s.CST_PIS = CST_PIS_COFINS.CD_CST

--Alterando a entrada de acordo com o novo calculo criado
IF @VISUALIZAR =0 
begin
	UPDATE
		s
	SET 
		s.VLR_BASE_PIS = snv.VLR_BASE_PIS, 
		s.VLR_PIS=snv.VLR_PIS,		
		s.PERC_ALIQ_PIS=snv.PERC_ALIQ_PIS,	
		s.VLR_BASE_COFINS=snv.VLR_BASE_COFINS,
		s.VLR_COFINS=snv.VLR_COFINS,
		s.PERC_ALIQ_COFINS=snv.PERC_ALIQ_COFINS, 
		s.CST_PIS=snv.CST_PIS,		
		s.CST_COFINS=snv.CST_COFINS,
		s.PIS_COFINS_RECALCULADO=snv.PIS_COFINS_RECALCULADO
	FROM
		#RsSaidasPdv snv INNER JOIN PDV_VD_DEV_EST_PROD_IMP s ON
		snv.CD_EMP = s.CD_EMP AND
		snv.CD_FILIAL = s.CD_FILIAL AND 
		snv.CD_VD = s.CD_VD AND
		snv.CD_IT = s.CD_IT
end

IF @VISUALIZAR =1 
begin
--Alterando a entrada de acordo com o novo calculo criado
SELECT
*
FROM
	#RsSaidasPdv 
end

DROP TABLE #RsSaidasPdv;


GO


