/****** Object:  UserDefinedFunction [dbo].[UFD_RELA_ACOMP_PIS_COFINS_SAIDA]    Script Date: 02/17/2014 16:21:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RELA_ACOMP_PIS_COFINS_SAIDA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_RELA_ACOMP_PIS_COFINS_SAIDA]
GO

/*

Select  *  From  UFD_RELA_ACOMP_PIS_COFINS_SAIDA ( 1 , '28', '15,14,5,10,8,1,9,11,4,17,13,12,102,0,16,101,3,7,99,2,6','08/16/2015' , '08/28/2015','41,52,66,49,20,48,15,136,38,140,62,6,75,92,34,89,53,71,148,87,56,76,73,37,72,111,58,137,35,119,83,67,44,60,69,2,84,13,118,77,86,88,57,79,109,90,104,21,138,139,14,11,55,23,121,96,1,31,134,16', 1) 
--where vlr_icms_desonerado > 0
Order By  CD_FILIAL, DS_SC, DS_PROD 

*/
-- VERSAO 2702

CREATE FUNCTION [dbo].[UFD_RELA_ACOMP_PIS_COFINS_SAIDA] (	
											@CD_EMP		INT,
											@CD_FILIAL	VARCHAR(MAX),
											@CD_SC		VARCHAR(MAX),
											@DT_INI		DATETIME,
											@DT_FIM		DATETIME,
											@CD_MOV		VARCHAR(MAX),
											@BUSCA_VD_PDV INT)



RETURNS @RS_RETORNO TABLE (
						CD_EMP 				INT,
						CD_FILIAL			INT,
						CD_VD               INT,
						NM_FANT				VARCHAR(200),
						CD_SC				INT,
						DS_SC				VARCHAR(250),
						CD_PROD				INT,
						DS_PROD				VARCHAR(250),
						VLR_LIQ_VD			MONEY,
						CD_TRIB_FC			VARCHAR(5),
						VLR_PIS				MONEY,
						VLR_COFINS			MONEY,
						VLR_BASE_PIS		MONEY,
						VLR_BASE_COFINS		MONEY,
						VLR_MOVIMENTO_TOTAL MONEY,
						CFOP				VARCHAR(10),
						NR_NCM				VARCHAR(15),
						LISTA_PROD_DS		VARCHAR(15),
						TIPO_DOCUMENTO		VARCHAR(20),
						DS_MOV_OP			VARCHAR(100),
						MOV_GERA_PIS_COFINS	VARCHAR(10),
						CD_CST_PIS          INT,
						DS_CST_PIS			VARCHAR(100),
						CD_CST_COFINS		INT,
						DS_CST_COFINS		VARCHAR(100),						
						PERC_ALIQ_PIS		MONEY, 
						PERC_ALIQ_COFINS	MONEY,
						LISTA_GERA_PIS_COFINS VARCHAR(10),
						DOCUMENTO			VARCHAR(100),
						DT_DOCUMENTO		DATETIME,
						CD_NAT_RECEITA      INT,
						TIPO_TRIBUTACAO_PIS_COFINS VARCHAR(50),
						FLAG_NAO_GERAR_ARQ_SPED VARCHAR(10), 
						MODELO VARCHAR(100),
						CHAVE  VARCHAR(44),
						RAZAO_SOCIAL VARCHAR (250),
					    CGC_CPF VARCHAR(20),
					    COD_IBGE VARCHAR (20),
					    UF VARCHAR(2),
						COD_PART INT,
						DEST_ORIG varchar(12),
						VLR_IT_DESC MONEY,
						VLR_ICMS_DESONERADO MONEY,
						VLR_BRUTO MONEY,
						VLR_ICMS_ST_DARE MONEY,
						VLR_ICMS_EFETIVO MONEY,
						VLR_ICMS_SUBS MONEY,
						VLR_ICMS_RET MONEY,
						VLR_FECOP MONEY,
						VLR_FECOP_RET MONEY,
						VLR_FECOP_ST MONEY,
						VLR_BASE_ICMS   MONEY,
						VLR_ICMS        MONEY)


AS BEGIN

DECLARE @RS_VENDAS TABLE (
						CD_EMP 				INT,
						CD_FILIAL			INT,
						CD_VD               INT,
						CD_SC				INT,
						DS_SC				VARCHAR(250),
						CD_PROD				INT,
						DS_PROD				VARCHAR(250),
						VLR_LIQ_VD			MONEY,
						CD_TRIB_FC			VARCHAR(10),
						LISTA_PROD			INT,
						VLR_BASE_PIS		MONEY,
						VLR_BASE_COFINS		MONEY,
						VLR_PIS		        MONEY,
						VLR_COFINS		    MONEY,
						VLR_MOVIMENTO_TOTAL MONEY,
						CFOP				VARCHAR(10),
						NR_NCM				VARCHAR(15),
						LISTA_PROD_DS		VARCHAR(15),
						TIPO_DOCUMENTO		VARCHAR (20),
						DS_MOV_OP			VARCHAR(100),
						MOV_GERA_PIS_COFINS	VARCHAR(10),
						CD_CST_PIS          INT,
						DS_CST_PIS			VARCHAR(100),
						CD_CST_COFINS		INT,
						DS_CST_COFINS		VARCHAR(100),
						PERC_ALIQ_PIS		MONEY, 
						PERC_ALIQ_COFINS	MONEY,
						LISTA_GERA_PIS_COFINS	VARCHAR(10),
						DOCUMENTO			VARCHAR(100),
						DT_DOCUMENTO		DATETIME,
						CD_NAT_RECEITA      INT,
						TIPO_TRIBUTACAO_PIS_COFINS VARCHAR(50),
						FLAG_NAO_GERAR_ARQ_SPED VARCHAR(10),
						MODELO VARCHAR(100),
						CHAVE  VARCHAR(44),
						RAZAO_SOCIAL VARCHAR (250),
					    CGC_CPF VARCHAR(20),
					    COD_IBGE VARCHAR (20),
					    UF VARCHAR(2),
						COD_PART INT,
						DEST_ORIG varchar(20),
						VLR_IT_DESC MONEY,
						VLR_ICMS_DESONERADO MONEY,
						VLR_BRUTO MONEY,
						VLR_ICMS_ST_DARE MONEY,
						VLR_ICMS_EFETIVO MONEY,
						VLR_ICMS_SUBS MONEY,
						VLR_ICMS_RET MONEY,
						VLR_FECOP MONEY,
						VLR_FECOP_RET MONEY,
						VLR_FECOP_ST MONEY,
						VLR_BASE_ICMS   MONEY,
						VLR_ICMS        MONEY)

						
--------------------------------------------------------------------------------------------------------------------
DECLARE @RS_FILIAL TABLE (CD_EMP 				INT,
							CD_FILIAL			INT)
INSERT INTO @RS_FILIAL
SELECT 
	CD_EMP,
	CD_FILIAL
FROM 
	PRC_FILIAL
WHERE 
	CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL))				
						 
--------------------------------------------------------------------------------------------------------------------
DECLARE @RS_LINHAS TABLE (CD_EMP 				INT,
						  CD_ARV_MERC_LINHA	INT,
						  DS_ARV_MERC_LINHA VARCHAR (100)) 
INSERT INTO @RS_LINHAS					
SELECT 
	CD_EMP,
	CD_ARV_MERC_LINHA,
	DS_ARV_MERC_LINHA 
FROM 
	EST_ARV_MERC_LINHA
WHERE 
	CD_ARV_MERC_LINHA IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_SC))
--------------------------------------------------------------------------------------------------------------------
DECLARE @RS_OPERACAO TABLE (CD_MOV_OP INT)
INSERT INTO @RS_OPERACAO
SELECT 
	CD_MOV_OP 
FROM 
	GLB_MOV_OP
WHERE 
	CD_MOV_OP IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_MOV))
--------------------------------------------------------------------------------------------------------------------			
				 	
						
IF @BUSCA_VD_PDV = 1 
BEGIN
	INSERT INTO @RS_VENDAS
	
	SELECT     
		PDV_VD.CD_EMP, 
		PDV_VD.CD_FILIAL,
		PDV_VD.CD_VD, 
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA AS CD_SC,
		DS_ARV_MERC_LINHA AS DS_SC,
		PDV_VD_IT.CD_PROD,          
		EST_PROD.DS_PROD,
		SUM(CASE WHEN (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0 THEN 
			PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
		ELSE
			0
		END) AS  VLR_LIQ, 
		PDV_VD_IT.CD_TRIB_FC,
		PDV_VD_IT.LISTA_PROD,
		SUM(ISNULL(PDV_VD_IT.VLR_BASE_PIS,0))    AS VLR_BASE_PIS,
		SUM(ISNULL(PDV_VD_IT.VLR_BASE_COFINS,0))  AS VLR_BASE_COFINS,
		SUM(ISNULL(PDV_VD_IT.VLR_PIS,0) ) AS VLR_PIS,
		SUM(ISNULL(PDV_VD_IT.VLR_COFINS,0))  AS VLR_COFINS,
		SUM(CASE WHEN (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0 THEN 
				PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
			ELSE
				0
			END) AS TOT_IT,
        CASE WHEN ISNULL(TIPO_IMPOSTO,0) = 0 THEN
			CASE WHEN RIGHT(PDV_VD_IT.CD_TRIB_FC,2) = '10' OR RIGHT(PDV_VD_IT.CD_TRIB_FC,2) = '60' OR RIGHT(PDV_VD_IT.CD_TRIB_FC,2) = '70' THEN
				'5.405' 
			ELSE  
				'5.102'    
		     END
		ELSE 
			'5.933'
        END AS CFOP,
		
					     
		EST_PROD.NR_NCM,
		CASE WHEN PDV_VD_IT.LISTA_PROD = 0 THEN  
			'POSITIVA'
		WHEN PDV_VD_IT.LISTA_PROD = 1 THEN  
			'NEGATIVA'
		WHEN PDV_VD_IT.LISTA_PROD = 2 THEN
			'NEUTRA'  
		ELSE
			'NAO USA LISTA'
		END AS LISTA_PROD_DS,

		CASE WHEN ISNULL(PDV_VD_NFCE.CD_NFCE,0) = 0 THEN 'CUPOM FISCAL' ELSE 'NFCE' END AS TIPO_DOCUMENTO,
		CASE WHEN ISNULL(PDV_VD_NFCE.CD_NFCE,0) = 0 THEN 'VENDAS CUPOM FISCAL' ELSE 'Nota Fiscal de consumidor Final' END AS DS_MOV_OP,

		'SIM' AS MOV_GERA_PIS_COFINS,
		
		CST_PIS.CD_CST AS CD_CST_PIS,
		CST_PIS.DS_CST AS DS_CST_PIS,
		CST_COFINS.CD_CST AS CD_CST_COFINS,
		CST_COFINS.DS_CST AS DS_CST_COFINS,
		ISNULL(PDV_VD_IT.PERC_ALIQ_PIS,0) AS PERC_ALIQ_PIS, 
		ISNULL(PDV_VD_IT.PERC_ALIQ_COFINS,0) AS PERC_ALIQ_COFINS,
		CASE WHEN PDV_VD_IT.PIS_COFINS = 0 THEN 
			'NAO' 
		ELSE 
			'SIM'
		END AS LISTA_GERA_PIS_COFINS,
		CONVERT(VARCHAR(50),PDV_VD.NR_ECF) + ' - '  +  CONVERT(VARCHAR(5),PDV_VD.CD_CX),
		PDV_VD.DT_VD , 
		ISNULL(CD_NAT_RECEITA       ,0)CD_NAT_RECEITA,
		CASE WHEN PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS = 0 THEN '0 - Debito (Aliquota basica e etc)'  
		WHEN PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS = 1 THEN '1 - Isento  (Aliquota Zero)'  
		WHEN PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS = 2 THEN '2 - Monofasico'  
		WHEN PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS = 3 THEN '3 - Tributario a aliquota zero'   
		ELSE 'Nao definido'  END AS  TIPO_TRIBUTACAO_PIS_COFINS,

		'SIM' AS FLAG_NAO_GERAR_ARQ_SPED,
		CASE WHEN ISNULL(PDV_VD_NFCE.CD_NFCE,0) = 0 THEN '2D' ELSE '65' END AS MODELO,
		'' CHAVE, 

		RZ_CLI AS RAZAO_SOCIAL  ,
		RC_CLI.CGC_CPF ,
		COD_MUNICIPIO_SINTEGRA AS COD_IBGE,
		GLB_CID.UF,
		PDV_VD.CD_CLI COD_PART,
		'CLIENTE' AS DEST_ORIG,
		SUM(PDV_VD_IT.VLR_DESC_TOT_RAT + (PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT * PDV_VD_IT.TX_DESC / 100) ) AS VLR_IT_DESC,
		0 AS VLR_ICMS_DESONERADO,
		SUM(ROUND(PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT,2)) AS VLR_BRUTO,
		SUM(ISNULL(PDV_MOV.VLR_ICMS_ST_DARE * PDV_MOV.QT_IT,0)) AS VLR_ICMS_ST_DARE , 
		SUM(ISNULL(PDV_MOV.VLR_ICMS_EFETIVO * PDV_MOV.QT_IT,0)) AS VLR_ICMS_EFETIVO,
		SUM(ISNULL(PDV_MOV.VLR_ICMS_SUBS * PDV_MOV.QT_IT,0)) AS VLR_ICMS_SUBS,
		SUM(ISNULL(PDV_MOV.VLR_ICMS_RET * PDV_MOV.QT_IT,0)) AS VLR_ICMS_RET,
		SUM(ISNULL(PDV_MOV.VLR_FECOP * PDV_MOV.QT_IT,0)) AS VLR_FECOP,
		SUM(ISNULL(PDV_MOV.VLR_FECOP_RET * PDV_MOV.QT_IT,0)) AS VLR_FECOP_RET,
		SUM(ISNULL(PDV_MOV.VLR_FECOP_ST * PDV_MOV.QT_IT,0)) AS VLR_FECOP_ST,
		SUM(ISNULL(PDV_VD_IT.VLR_BASE_CALC ,0)) AS VLR_BASE_ICMS,
		SUM(ISNULL(PDV_VD_IT.VLR_ICMS ,0)) AS VLR_ICMS 
	   
	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_CX ON
			PDV_VD.CD_EMP = PDV_CX.CD_EMP AND
			PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL AND
			PDV_VD.CD_CX = PDV_CX.CD_CX 

    	    LEFT JOIN  PDV_VD_PDV_VD_NFCE ON 
    		 PDV_VD.CD_EMP = PDV_VD_PDV_VD_NFCE.CD_EMP 
    		 AND PDV_VD.CD_FILIAL = PDV_VD_PDV_VD_NFCE.CD_FILIAL 
    		 AND PDV_VD.CD_VD = PDV_VD_PDV_VD_NFCE.CD_VD 
    	    LEFT JOIN  PDV_VD_NFCE ON 
		  PDV_VD_PDV_VD_NFCE.CD_NFCE = PDV_VD_NFCE.CD_NFCE

		  
		LEFT JOIN RC_CLI ON
		PDV_VD.CD_EMP=RC_CLI.CD_EMP AND
		PDV_VD.CD_CLI=RC_CLI.CD_CLI

		INNER JOIN EST_PROD ON 
		PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP AND 
		PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD 

		INNER JOIN @RS_FILIAL F ON 
		PDV_VD_IT.CD_EMP = F.CD_EMP AND 
		PDV_VD_IT.CD_FILIAL = F.CD_FILIAL

		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON 
		EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
		EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD

		INNER JOIN @RS_LINHAS L  ON 
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = L.CD_EMP AND 
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA

		LEFT OUTER JOIN CST_PIS_COFINS CST_PIS ON 
		PDV_VD_IT.CST_PIS = CST_PIS.CD_CST
		LEFT OUTER JOIN CST_PIS_COFINS CST_COFINS ON 
		PDV_VD_IT.CST_COFINS = CST_COFINS.CD_CST
		LEFT JOIN EST_PROD_NCM_NATUREZA_RECEITA ON 
		EST_PROD_NCM_NATUREZA_RECEITA.CD_EMP = PDV_VD_IT.CD_EMP AND
		EST_PROD_NCM_NATUREZA_RECEITA.CD_PROD = PDV_VD_IT.CD_PROD
		LEFT JOIN GLB_CID   ON  
		RC_CLI.CD_CID = GLB_CID.CD_CID  
    
		LEFT JOIN GLB_CID_GLB_MUNICIPIO  ON  
		GLB_CID.CD_CID = GLB_CID_GLB_MUNICIPIO.CD_CID 
    
		LEFT JOIN GLB_MUNICIPIO   ON  
		GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO = GLB_MUNICIPIO.CD_MUNICIPIO 

		LEFT JOIN EST_NF_ENT_ENTRADA_ICMS_ST_MOV PDV_MOV ON  
		PDV_VD_IT.CD_EMP =  PDV_MOV.CD_EMP
		AND PDV_VD_IT.CD_FILIAL  =  PDV_MOV.CD_FILIAL
		AND PDV_VD_IT.CD_VD =  PDV_MOV.CD_DOC
        AND PDV_VD_IT.CD_PROD =  PDV_MOV.CD_PROD
		AND PDV_MOV.TIPO_DOC = 0
		AND PDV_MOV.STATUS IN (0,1)
		
	WHERE
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.DT_VD BETWEEN @DT_INI AND @DT_FIM
		AND PDV_VD.ST_VD = 0  
		AND PDV_VD_IT.ST_IT = 1
		--AND ISNULL(PDV_CX.FLAG_NFCE,0) = 0
 
	GROUP BY
		PDV_VD.CD_EMP,
		PDV_VD.CD_FILIAL, 
		PDV_VD_NFCE.CD_NFCE,
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA,
		DS_ARV_MERC_LINHA,
		PDV_VD_IT.CD_PROD, 
		EST_PROD.DS_PROD, 
		PDV_VD_IT.CD_TRIB_FC, 
		PDV_VD_IT.TX_ICMS_IT,
		PDV_VD_IT.LISTA_PROD,
		PDV_VD_IT.PIS_COFINS,
		EST_PROD.NR_NCM,
		CST_PIS.CD_CST,
		CST_PIS.DS_CST,
		CST_COFINS.CD_CST,
		CST_COFINS.DS_CST,
		PDV_VD_IT.PERC_ALIQ_PIS, 
		PDV_VD_IT.PERC_ALIQ_COFINS,
		PDV_VD_IT.PIS_COFINS,
		CONVERT(VARCHAR(50),PDV_VD.NR_ECF) + ' - '  +  CONVERT(VARCHAR(5),PDV_VD.CD_CX),
		PDV_VD.DT_VD,
		CD_NAT_RECEITA,
		PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS,
		RZ_CLI,
		RC_CLI.CGC_CPF,
		COD_MUNICIPIO_SINTEGRA,
		GLB_CID.UF,
		PDV_VD.CD_CLI,
		PDV_vD.CD_VD,
		TIPO_IMPOSTO,
		PDV_MOV.VLR_ICMS_ST_DARE, 
		PDV_MOV.VLR_ICMS_EFETIVO,
		PDV_MOV.VLR_ICMS_SUBS,
		PDV_MOV.VLR_ICMS_RET,
		PDV_MOV.VLR_FECOP,
		PDV_MOV.VLR_FECOP_RET,
		PDV_MOV.VLR_FECOP_ST
 
END
INSERT INTO @RS_VENDAS
	SELECT     
		V_EST_NF_SAI.CD_EMP, 
		V_EST_NF_SAI.CD_FILIAL,
		V_EST_NF_SAI.CD_NF, 
		L.CD_ARV_MERC_LINHA AS CD_SC,
		L.DS_ARV_MERC_LINHA AS DS_SC,
		V_EST_NF_SAI.CD_PROD, 
		V_EST_NF_SAI.DS_PROD, 
		SUM(V_EST_NF_SAI.VLR_TOT_IT),
		V_EST_NF_SAI.CD_TRIB_FC,     ---- SELECT TOP 1 * FROM V_EST_NF_SAI
		V_EST_NF_SAI.LISTA_PROD,
		SUM(ISNULL (V_EST_NF_SAI.VLR_BASE_PIS,0))VLR_BASE_PIS,
		SUM(ISNULL (V_EST_NF_SAI.VLR_BASE_COFINS,0)) AS VLR_BASE_COFINS,
		SUM(ISNULL(V_EST_NF_SAI.VLR_PIS,0))  AS VLR_PIS,
		SUM(ISNULL(V_EST_NF_SAI.VLR_COFINS,0))  AS VLR_COFINS,
		SUM(V_EST_NF_SAI.VLR_TOT_IT + V_EST_NF_SAI.VLR_ICMS_SUBS + V_EST_NF_SAI.VLR_IPI_IT),
		V_EST_NF_SAI.CD_NT_OP_FC,
		V_EST_NF_SAI.NR_NCM,
		CASE WHEN V_EST_NF_SAI.LISTA_PROD = 0 THEN  
			'POSITIVA'
		WHEN V_EST_NF_SAI.LISTA_PROD= 1 THEN  
			'NEGATIVA'
		WHEN V_EST_NF_SAI.LISTA_PROD= 2 THEN  
			'NEUTRA'
		ELSE
			'NAO USA LISTA'
		END AS LISTA_PROD,
		'NOTA FISCAL' AS TIPO_DOCUMENTO,
		DS_MOV_OP,
		CASE WHEN ISNULL(V_EST_NF_SAI.FLAG_NAO_TRIBUTAR_PIS_COFINS,0) = 0 THEN  
			'SIM'
		ELSE
			'NAO'
		END AS MOV_GERA_PIS_COFINS,
		CST_PIS.CD_CST AS CD_CST_PIS,
		CST_PIS.DS_CST AS DS_CST_PIS,
		CST_COFINS.CD_CST AS CD_CST_COFINS,
		CST_COFINS.DS_CST AS DS_CST_COFINS,
		ISNULL(V_EST_NF_SAI.PERC_ALIQ_PIS,0) AS PERC_ALIQ_PIS, 
		ISNULL(V_EST_NF_SAI.PERC_ALIQ_COFINS,0) AS PERC_ALIQ_COFINS,
		CASE WHEN V_EST_NF_SAI.PIS_COFINS = 0 THEN 
			'NAO' 
		ELSE 
			'SIM'
		END AS LISTA_GERA_PIS_COFINS,
		NF_NF,
		DT_EMI_NF ,
		ISNULL(CD_NAT_RECEITA,0) CD_NAT_RECEITA,
			
		CASE WHEN V_EST_NF_SAI.TIPO_TRIBUTACAO_PIS_COFINS = 0 THEN '0 - Debito (Aliquota basica e etc)'  
		WHEN V_EST_NF_SAI.TIPO_TRIBUTACAO_PIS_COFINS = 1 THEN '1 - Isento  (Aliquota Zero)'  
		WHEN V_EST_NF_SAI.TIPO_TRIBUTACAO_PIS_COFINS = 2 THEN '2 - Monofasico'  
		WHEN V_EST_NF_SAI.TIPO_TRIBUTACAO_PIS_COFINS = 3 THEN '3 - Tributario a aliquota zero'   
		ELSE 'Nao definido'  END AS  TIPO_TRIBUTACAO_PIS_COFINS,
        CASE WHEN V_EST_NF_SAI.FLAG_NAO_GERAR_ARQ_SPED = 0 THEN 
			 'SIM' 
        ELSE 
			'NAO'
        END AS FLAG_NAO_GERAR_ARQ_SPED, -- SELECT TOP 1 * FROM V_EST_NF_SAI
        V_EST_NF_SAI.MODELO,
        ISNULL(V_EST_NF_SAI.NR_AUTORIZADOR,'') CHAVE,
		NM_DEST ,
		CGC_CPF ,
		COD_IBGE ,
		UF,
        CD_CLI COD_PART,
		CASE WHEN V_EST_NF_SAI.DEST_ORIG = 0 THEN 'CLIENTE'
        WHEN V_EST_NF_SAI.DEST_ORIG = 1 THEN 'FORNECEDOR'
        WHEN V_EST_NF_SAI.DEST_ORIG = 2 THEN 'FILIAL' END AS DEST_ORIG,
	   ROUND( V_EST_NF_SAI.QT_IT * V_EST_NF_SAI.VLR_IT,2) AS VLR_IT_DESC ,
	   SUM(V_EST_NF_SAI.VLR_ICMS_DESONERADO) VLR_ICMS_DESONERADO,
	   SUM(ROUND(V_EST_NF_SAI.QT_IT * V_EST_NF_SAI.VLR_IT,2)) AS VLR_BRUTO,
	   SUM(ISNULL(PDV_MOV.VLR_ICMS_ST_DARE * PDV_MOV.QT_IT,0)) AS VLR_ICMS_ST_DARE , 
		SUM(ISNULL(PDV_MOV.VLR_ICMS_EFETIVO * PDV_MOV.QT_IT,0)) AS VLR_ICMS_EFETIVO,
		SUM(ISNULL(PDV_MOV.VLR_ICMS_SUBS * PDV_MOV.QT_IT,0)) AS VLR_ICMS_SUBS,
		SUM(ISNULL(PDV_MOV.VLR_ICMS_RET * PDV_MOV.QT_IT,0)) AS VLR_ICMS_RET,
		SUM(ISNULL(PDV_MOV.VLR_FECOP * PDV_MOV.QT_IT,0)) AS VLR_FECOP,
		SUM(ISNULL(PDV_MOV.VLR_FECOP_RET * PDV_MOV.QT_IT,0)) AS VLR_FECOP_RET,
		SUM(ISNULL(PDV_MOV.VLR_FECOP_ST * PDV_MOV.QT_IT,0)) AS VLR_FECOP_ST,
		SUM(ISNULL(V_EST_NF_SAI.VLR_BASE_ICMS_IT ,0)) AS VLR_BASE_ICMS,
		SUM(ISNULL(V_EST_NF_SAI.VLR_ICMS_IT ,0)) AS VLR_ICMS 
		
	FROM         
		V_EST_NF_SAI INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON 
		V_EST_NF_SAI.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
		V_EST_NF_SAI.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
		
		INNER JOIN @RS_LINHAS L  ON 
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = L.CD_EMP AND 
		EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA

		INNER JOIN @RS_FILIAL F ON 
		V_EST_NF_SAI.CD_EMP = F.CD_EMP AND 
		V_EST_NF_SAI.CD_FILIAL = F.CD_FILIAL

		LEFT OUTER JOIN CST_PIS_COFINS CST_PIS ON 
		V_EST_NF_SAI.CST_PIS = CST_PIS.CD_CST
		LEFT OUTER JOIN CST_PIS_COFINS CST_COFINS ON 
		V_EST_NF_SAI.CST_COFINS = CST_COFINS.CD_CST
		LEFT JOIN EST_PROD_NCM_NATUREZA_RECEITA ON 
		EST_PROD_NCM_NATUREZA_RECEITA.CD_EMP = V_EST_NF_SAI.CD_EMP
		AND  EST_PROD_NCM_NATUREZA_RECEITA.CD_PROD = V_EST_NF_SAI.CD_PROD

		LEFT JOIN EST_NF_ENT_ENTRADA_ICMS_ST_MOV PDV_MOV ON  
		V_EST_NF_SAI.CD_EMP =  PDV_MOV.CD_EMP
		AND V_EST_NF_SAI.CD_FILIAL  =  PDV_MOV.CD_FILIAL
		AND V_EST_NF_SAI.CD_NF =  PDV_MOV.CD_DOC
        AND V_EST_NF_SAI.CD_PROD =  PDV_MOV.CD_PROD
		AND PDV_MOV.TIPO_DOC = 2
		AND PDV_MOV.STATUS IN (0,1)
		
	WHERE
		V_EST_NF_SAI.CD_EMP = @CD_EMP 
		AND V_EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM
		and V_EST_NF_SAI.STS_NF = 0
		AND NOT EXISTS (SELECT 
							CD_NF 
						FROM 
							PDV_VD_EST_NF_SAI 
						WHERE 
							PDV_VD_EST_NF_SAI.CD_EMP = V_EST_NF_SAI.CD_EMP AND
							PDV_VD_EST_NF_SAI.CD_FILIAL = V_EST_NF_SAI.CD_FILIAL AND
							PDV_VD_EST_NF_SAI.CD_NF = V_EST_NF_SAI.CD_NF)
	GROUP BY
		V_EST_NF_SAI.CD_EMP, 
		V_EST_NF_SAI.CD_FILIAL, 
		L.CD_ARV_MERC_LINHA, 
		L.DS_ARV_MERC_LINHA, 
		V_EST_NF_SAI.CD_PROD, 
		V_EST_NF_SAI.DS_PROD, 
		V_EST_NF_SAI.CD_TRIB_FC,
		V_EST_NF_SAI.ALIQ_ICMS,
		V_EST_NF_SAI.LISTA_PROD,
		V_EST_NF_SAI.PIS_COFINS,
		V_EST_NF_SAI.CD_NT_OP_FC,
		V_EST_NF_SAI.NR_NCM,
		DS_MOV_OP,
		V_EST_NF_SAI.FLAG_NAO_TRIBUTAR_PIS_COFINS,
		CST_PIS.CD_CST,
		CST_PIS.DS_CST,
		CST_COFINS.CD_CST,
		CST_COFINS.DS_CST,
		V_EST_NF_SAI.PERC_ALIQ_PIS, 
		V_EST_NF_SAI.PERC_ALIQ_COFINS,
		V_EST_NF_SAI.PIS_COFINS,
		V_EST_NF_SAI.NF_NF,
		DT_EMI_NF,
		CD_NAT_RECEITA,
		V_EST_NF_SAI.TIPO_TRIBUTACAO_PIS_COFINS,
		V_EST_NF_SAI.FLAG_NAO_GERAR_ARQ_SPED,
		V_EST_NF_SAI.MODELO, 
		NR_AUTORIZADOR ,
		NM_DEST   ,
		CGC_CPF ,
		COD_IBGE ,
		UF ,
		CD_CLI,
		DEST_ORIG ,
		V_EST_NF_SAI.CD_NF,
		V_EST_NF_SAI.QT_IT,
		V_EST_NF_SAI.VLR_IT,
		PDV_MOV.VLR_ICMS_ST_DARE, 
		PDV_MOV.VLR_ICMS_EFETIVO,
		PDV_MOV.VLR_ICMS_SUBS,
		PDV_MOV.VLR_ICMS_RET,
		PDV_MOV.VLR_FECOP,
		PDV_MOV.VLR_FECOP_RET,
		PDV_MOV.VLR_FECOP_ST
		
	INSERT INTO @RS_RETORNO
	SELECT
		RS.CD_EMP,
		RS.CD_FILIAL,
		RS.CD_VD,
		PRC_FILIAL.NM_FANT,
		RS.CD_SC,
		RS.DS_SC,
		RS.CD_PROD,
		RS.DS_PROD,
		SUM(RS.VLR_LIQ_VD),
		RS.CD_TRIB_FC,
		SUM(RS.VLR_PIS),
		SUM(RS.VLR_COFINS),
		SUM(VLR_BASE_PIS),
		SUM(VLR_BASE_COFINS),
		SUM(VLR_MOVIMENTO_TOTAL),
		CFOP,
		NR_NCM,
		LISTA_PROD_DS,
		TIPO_DOCUMENTO,
		DS_MOV_OP,
		MOV_GERA_PIS_COFINS,
		ISNULL(CD_CST_PIS,0),
		DS_CST_PIS,
		ISNULL(CD_CST_COFINS,0),
		DS_CST_COFINS,
		RS.PERC_ALIQ_PIS, 
		RS.PERC_ALIQ_COFINS,
		LISTA_GERA_PIS_COFINS,
		DOCUMENTO,
		DT_DOCUMENTO ,
		CD_NAT_RECEITA ,
		TIPO_TRIBUTACAO_PIS_COFINS,
		FLAG_NAO_GERAR_ARQ_SPED,
		MODELO,
		CHAVE ,
		RAZAO_SOCIAL   ,
		CGC_CPF ,
		COD_IBGE ,
		UF,
		COD_PART,
		DEST_ORIG,
		VLR_IT_DESC ,
		VLR_ICMS_DESONERADO,
		VLR_BRUTO,
		VLR_ICMS_ST_DARE ,
		VLR_ICMS_EFETIVO ,
		VLR_ICMS_SUBS ,
		VLR_ICMS_RET ,
		VLR_FECOP ,
		VLR_FECOP_RET ,
		VLR_FECOP_ST,
        VLR_BASE_ICMS,
		VLR_ICMS
		
	FROM
		@RS_VENDAS RS INNER JOIN PRC_FILIAL ON
			RS.CD_EMP = PRC_FILIAL.CD_EMP AND
			RS.CD_FILIAL = PRC_FILIAL.CD_FILIAL
	GROUP BY
		RS.CD_EMP,
		RS.CD_FILIAL,
		RS.CD_VD,
		PRC_FILIAL.NM_FANT,
		RS.CD_SC,
		RS.DS_SC,
		RS.CD_PROD,
		RS.DS_PROD,
		RS.CD_TRIB_FC,
		rs.LISTA_PROD,
		rs.CFOP,
		rs.NR_NCM,
		LISTA_PROD_DS,
		TIPO_DOCUMENTO,
		DS_MOV_OP,
		MOV_GERA_PIS_COFINS,
		CD_CST_PIS,
		DS_CST_PIS,
		CD_CST_COFINS,
		DS_CST_COFINS,		
		RS.PERC_ALIQ_PIS, 
		RS.PERC_ALIQ_COFINS,
		LISTA_GERA_PIS_COFINS,
		DOCUMENTO,
		DT_DOCUMENTO,
		CD_NAT_RECEITA ,
		TIPO_TRIBUTACAO_PIS_COFINS,
		FLAG_NAO_GERAR_ARQ_SPED,
		MODELO,
		CHAVE,
		RAZAO_SOCIAL   ,
		CGC_CPF ,
		COD_IBGE ,
		UF ,
		COD_PART,
		DEST_ORIG,
		VLR_IT_DESC,
		VLR_ICMS_DESONERADO,
		VLR_BRUTO,
		VLR_BRUTO  ,
	    VLR_ICMS_ST_DARE  ,
	    VLR_ICMS_EFETIVO  ,
	    VLR_ICMS_SUBS  ,
	    VLR_ICMS_RET  ,
	    VLR_FECOP  ,
	    VLR_FECOP_RET  ,
	    VLR_FECOP_ST,
		VLR_BASE_ICMS,
		VLR_ICMS
		

RETURN
END


