ALTER PROCEDURE [dbo].[P_EST_NF_ENT_SAI_SAP] (@cdEmp int, @lstFiliais varchar(max), @lstNF varchar(max)='' , @DataIni date, @DataFim date, @TpRela tinyint=0)
AS
/*
Declare @lstFiliais varchar(max) ='999', 
		@DataIni	date='03/15/2019', 
		@DataFim	date ='03/15/2019', 
		@TpRela		tinyint=0*/

/**************************************************************************************************************************************/
	set Nocount on;
/**************************************************************************************************************************************/
	/*

	if object_id('#TmpFilial') is null
		drop table #TmpFilial

	if object_id('#TmpEntradas') is null
		drop table #TmpEntradas

	if object_id('#TmpResult') is null
		drop table #TmpResult
	
	*/

/**************************************************************************************************************************************/

	CREATE TABLE #TmpFilial( cd_emp                   INT
							, cd_filial                int
							, cgc                      varchar(50)
							, cd_empresa_contabil      varchar(50)
							, cd_centro_custo_contabil varchar(50)
							);

	insert into #TmpFilial 
	select 
		@cdEmp ,
		id,
		'',
		'',
		''
	from 
		[UFD_PRM_IN_STRING]  (@lstFiliais);
	
	UPDATE  T
	SET CGC= F.CGC,
		CD_EMPRESA_CONTABIL = F.CD_EMPRESA_CONTABIL ,
		CD_CENTRO_CUSTO_CONTABIL = F.CD_CENTRO_CUSTO_CONTABIL
	FROM 
		#TmpFilial T 
		JOIN PRC_FILIAL  F ON 
		T.CD_EMP = F.CD_EMP AND
		T.CD_FILIAL = F.CD_FILIAL
	WHERE
		f.cd_emp =@cdEmp ;
		
/**************************************************************************************************************************************/

	CREATE TABLE #TmpNFEnt( cd_emp		int
							, cd_filial	int
							, cd_nf		int
							, dt_ent	date
							);


	CREATE TABLE #TmpNFSai ( cd_emp		int
							, cd_filial	int
							, cd_nf		int
							, dt_emi_nf date
							);


	
if len (@lstnf) =0 
begin
	INSERT INTO #TmpNFEnt
	SELECT
			  e.cd_emp
			, e.cd_filial
			, e.cd_nf
			, e.dt_ent
	FROM
			est_nf_ent e
			JOIN #TmpFilial F ON E.CD_EMP = F.CD_EMP
								AND E.CD_FILIAL = F.CD_FILIAL
	WHERE E.STS_NF = 0 
			AND E.CD_EMP = @cdEmp
			AND E.DT_ENT BETWEEN @DataIni AND @DataFim;

	INSERT INTO #TmpNFSai
	SELECT
			  e.cd_emp
			, e.cd_filial
			, e.cd_nf
			, e.dt_emi_nf
	FROM
			est_nf_ent e
			JOIN #TmpFilial F ON E.CD_EMP = F.CD_EMP
								AND E.CD_FILIAL = F.CD_FILIAL
	WHERE E.STS_NF = 0 
			AND E.CD_EMP = @cdEmp
			AND E.dt_emi_nf BETWEEN @DataIni AND @DataFim;
end
else
begin

	INSERT INTO #TmpNFEnt
	SELECT
			  e.cd_emp
			, e.cd_filial
			, e.cd_nf
			, e.dt_ent
	FROM
			est_nf_ent e
			JOIN #TmpFilial F ON E.CD_EMP = F.CD_EMP
								AND E.CD_FILIAL = F.CD_FILIAL
	WHERE E.STS_NF = 0 
			AND E.CD_EMP = @cdEmp
			AND E.DT_ENT BETWEEN @DataIni AND @DataFim
			AND E.NR_NF IN (SELECT ID FROM 	[UFD_PRM_IN_STRING]  (@lstnf) ) ;


	INSERT INTO #TmpNFSai
	SELECT
			  e.cd_emp
			, e.cd_filial
			, e.cd_nf
			, e.dt_emi_nf
	FROM
			est_nf_ent e
			JOIN #TmpFilial F ON E.CD_EMP = F.CD_EMP
								AND E.CD_FILIAL = F.CD_FILIAL
	WHERE 
		E.STS_NF = 0 
		AND E.CD_EMP = @cdEmp
		AND E.dt_emi_nf BETWEEN @DataIni AND @DataFim
		AND E.NR_NF IN (SELECT ID FROM 	[UFD_PRM_IN_STRING]  (@lstnf) ) ;

end

--------------------------------------------------------------------------------------------

	 


/**************************************************************************************************************************************/
	
	create table #TmpResult (
			CD_PROD			varchar	(10)
			,QT_IT				money
			,VLR_IT				float
			,VLR_TOT_IT			money
			,VLR_ICMS_IT		money
			,VLR_ICMS_SUBS_IT	money
			,VLR_IPI_IT			money
			,VLR_DESC_IT		money
			,VLR_DESC_FINAN_PROD	money
			,VLR_BASE_ICMS_IT	money	
			,VLR_BASE_SUBS_IT	money	
			,VLR_ISENTO_IT		float	
			,VLR_OUTROS_IT		float	
			,VLR_OUTRAS_DESPESAS	money
			,ALIQ_ICMS				money
			,PERC_IPI				money
			,CFOP				varchar (50)
			,VLR_BASE_IPI_IT	money	
			,NR_LOTE			varchar	(15)
			,CD_EMP				int	
			,CD_FILIAL_GEST		int	
			,CD_FILIAL			varchar	(50)
			,CD_EMPRESA_CONTABIL varchar (50)
			,CD_NF	int	
			,NR_NF	int	
			,CNPJ_FORN			varchar	(50)
			,CNPJ_FILIAL		varchar	(50)
			,DT_EMI_NF			datetime
			,DT_ENT				datetime
			,VLR_NF					money
			,VLR_DESC_CAB			money
			,VLR_ICMS				money
			,VLR_ICMS_SUBS			money
			,VLR_ICMS_DARE			money
			,VLR_ICMS_SUBS_DARE		money
			,VLR_IPI				money
			,HR_ENT					varchar	(25)
			,NR_AUTORIZADOR			varchar	(50)
			,CD_PED					int	
			,VLR_BASE_ICMS			money
			,VLR_BASE_ICMS_SUBS		money
			,VLR_BASE_IPI			money
			,VLR_ISENTAS			money
			,VLR_OUTROS				money
			,VLR_DESC_COM			money
			,VLR_DESC_FINAN			money
			,CD_TRIB_FC				varchar	(50)
			,TIPO					varchar	(2)
			,TP_MOV					int	
			,SERIE					varchar	(50)
			,TP_NOTA				int
			,CONTADOR_ITENS			int
			,VLR_ICMS_CRITERIO		money
			,POSSUI_DARE			int
			,FLAG_MOV_PROD_VENCIDO	int)

/**************************************************************************************************************************************/
 
 if @tpRela in (0,2)
 begin
	    SELECT 
		--'R'+RIGHT('0000000000'+CAST(E.CD_PROD AS VARCHAR(50)), 9) AS CD_PROD,
		CONVERT(VARCHAR(10), '' ) AS CD_PROD,
        IT.CD_PROD AS CD_PROD_GEST,
        IT.QT_IT,
        (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END)) VLR_IT, 


        CASE WHEN ISNULL(E.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN IT.VLR_TOT_IT
			ELSE IT.VLR_TOT_IT - IT.VLR_DESC END AS VLR_TOT_IT, 
            
		ROUND(ISNULL((CASE
							WHEN ISNULL(E.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN(IT.VLR_TOT_IT + IT.VLR_FRETE_IT)
							ELSE(IT.VLR_TOT_IT + IT.VLR_ICMS_SUBS + IT.VLR_IPI + IT.VLR_FRETE_IT) - IT.VLR_DESC
						END) * (IT.PERC_DESC_FINAN / 100), 0), 2) VLR_DESC_FINAN_PROD,

        IT.VLR_DESC VLR_DESC_IT,
        IT.VLR_IPI  VLR_IPI_IT,
        IT.PERC_IPI PERC_IPI,
        COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS, IT.VLR_ICMS_IT, 0) + IT.VLR_FECOP AS VLR_ICMS_IT,
        COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS, IT.VLR_ICMS_SUBS, 0) + IT.VLR_FECOP_ST AS VLR_ICMS_SUBS_IT,
        COALESCE(GLB_NT_OP_DARE.CD_NT_OP_FC, GLB_NT_OP.CD_NT_OP_FC) AS CFOP,  
        EST_TRIB.CD_TRIB_FC CD_TRIB_FC,
        COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_ICMS, IT.VLR_BASE_ICMS, 0) AS VLR_BASE_ICMS_IT,
        COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_SUBS, IT.VLR_BASE_SUBS, 0) AS VLR_BASE_SUBS_IT,
        COALESCE(EST_NF_ENT_DAR_IT.PERC_ICMS, E.ALIQ_ICMS, 0) AS ALIQ_ICMS,
        COALESCE(EST_NF_ENT_DAR_IT.CD_EMP, 0) AS POSSUI_DARE,
        ROUND(CASE
                    WHEN EST_TRIB_DARE.SUBS_TRIB IS NOT NULL
                    THEN CASE
                            WHEN(EST_TRIB_DARE.SUBS_TRIB + EST_TRIB_DARE.SUBS_TRIB_ANT) = 0
                            THEN CASE
                                    WHEN FORN.FLAG_FORN_MICRO_EMPRESA = 0 AND GLB_MOV_OP.DEST_ORIG=1
                                    THEN CASE
                                                WHEN EST_TRIB_DARE.ICMS = 0
                                                    AND EST_TRIB_DARE.ISENTO = 0
                                                THEN 0
                                                ELSE CASE
                                                        WHEN EST_TRIB_DARE.ISENTO = 0
                                                        THEN CASE
                                                                WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_SUBS, 0) > 0
                                                                THEN(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_ICMS, 0))
                                                                ELSE 0
                                                            END
                                                        ELSE CASE
                                                                WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                                                THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
                                                                ELSE 0
                                                            END
                                                    END
                                            END
                                    ELSE 0
                                END
                            ELSE 0
                        END
                    ELSE CASE
                            WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0
                            THEN CASE
                                    WHEN FORN.FLAG_FORN_MICRO_EMPRESA = 0 AND GLB_MOV_OP.DEST_ORIG=1
                                    THEN CASE
                                                WHEN EST_TRIB.ICMS = 0
                                                    AND EST_TRIB.ISENTO = 0
                                                THEN 0
                                                ELSE CASE
                                                        WHEN EST_TRIB.ISENTO = 0
                                                        THEN CASE
                                                                WHEN((IT.QT_IT  * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - IT.VLR_BASE_ICMS > 0
                                                                THEN(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - IT.VLR_BASE_ICMS)
                                                                ELSE 0
                                                            END
                                                        ELSE CASE
                                                                WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                                                THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
                                                                ELSE 0
                                                            END
                                                    END
                                            END
                                    ELSE 0
                                END
                            ELSE 0
                        END
                END, 2) AS VLR_ISENTO_IT,

        ROUND(CASE
                    WHEN EST_TRIB_DARE.SUBS_TRIB IS NOT NULL
                    THEN CASE
                            WHEN(EST_TRIB_DARE.SUBS_TRIB + EST_TRIB_DARE.SUBS_TRIB_ANT) = 0
                            THEN 0
                            ELSE CASE
                                    WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                    THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) + COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS, 0)
                                    ELSE 0
                                END
                        END + CASE
                                    WHEN EST_TRIB_DARE.FLAG_OUTROS = 0
                                    THEN 0
                                    ELSE CASE
                                            WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                            THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
                                            ELSE 0
                                        END
                                END
                    ELSE CASE
                            WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0
                            THEN 0
                            ELSE CASE
                                    WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                    THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) + IT.VLR_ICMS_SUBS
                                    ELSE 0
                                END
                        END + CASE
                                    WHEN EST_TRIB.FLAG_OUTROS = 0
                                    THEN 0
                                    ELSE CASE
                                            WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
                                            THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
                                            ELSE 0
                                        END
                                END
                END + ROUND(IT.VLR_OUTRAS_DESPESAS, 2), 2) AS VLR_OUTROS_IT,
        ROUND(IT.VLR_OUTRAS_DESPESAS, 2) AS VLR_OUTRAS_DESPESAS,
        CASE
            WHEN IT.VLR_IPI > 0
            THEN (CASE WHEN ISNULL(E.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN IT.VLR_TOT_IT ELSE IT.VLR_TOT_IT - IT.VLR_DESC END)
            ELSE 0
        END AS VLR_BASE_IPI_IT,
        '' COLLATE SQL_Latin1_General_CP1_CI_AS AS NR_LOTE,
        E.CD_EMP,
        E.CD_FILIAL AS CD_FILIAL_GEST,
        F.CD_CENTRO_CUSTO_CONTABIL AS CD_FILIAL,
        F.CD_EMPRESA_CONTABIL,
        E.CD_NF,
        E.NR_NF,
        E.CNPJ_EMITENTE  AS CNPJ_FORN,
        F.CGC			 AS CNPJ_FILIAL,
        E.DT_EMI_NF,
        E.DT_ENT,
        E.VLR_NF,
        E.VLR_DESC_CAB,
        Convert(money, 0 ) VLR_ICMS,			--TOTAIS_DARE.VLR_ICMS + E.VLR_FECOP AS VLR_ICMS,
        Convert(money, 0 ) VLR_ICMS_SUBS,		--TOTAIS_DARE.VLR_ICMS_SUBS + E.VLR_FECOP_ST AS VLR_ICMS_SUBS,
        Convert(money, 0 ) VLR_ICMS_DARE,		--TOTAIS_DARE.VLR_ICMS_DARE,
        Convert(money, 0 ) VLR_ICMS_SUBS_DARE,	--TOTAIS_DARE.VLR_ICMS_SUBS_DARE,             
		CONVERT(MONEY, 0) VLR_IPI,--BASE.VLR_IPI,
		COALESCE(E.VLR_FECOP,0) AS VLR_FECOP,
		COALESCE(E.VLR_FECOP_ST,0) AS VLR_FECOP_ST,
        E.HR_ENT,
        ISNULL(NFE.NR_AUTORIZADOR, '') AS NR_AUTORIZADOR,
        E.CD_PED,
        Convert(money, 0 ) VLR_BASE_ICMS	 , -- TOTAIS_DARE.VLR_BASE_ICMS,
        Convert(money, 0 ) VLR_BASE_ICMS_SUBS, -- TOTAIS_DARE.VLR_BASE_SUBS AS VLR_BASE_ICMS_SUBS,

        CONVERT(MONEY, 0) VLR_BASE_IPI,		--BASE.VLR_BASE_IPI,
        CONVERT(MONEY, 0) VLR_ISENTAS,		--BASE.VLR_ISENTAS,
        CONVERT(MONEY, 0) VLR_OUTROS,		--BASE.VLR_OUTROS,
        CONVERT(MONEY, 0) VLR_DESC_COM,		--BASE.VLR_DESC_COM,
        CONVERT(MONEY, 0) VLR_DESC_FINAN,	--BASE.VLR_DESC_FINAN,
        CASE GLB_MOV_OP.TP_MOV
            WHEN 0 THEN '01'
            WHEN 10 THEN '15'
            WHEN 11 THEN  
--ESSA CONDIÇÃO FOI COLOCADA PORQUE A COF POSSUI UM REGIME ESPECIAL DE ENTRADA DE DEVOLUÇÃO DE CLIENTE  
					CASE WHEN E.CD_FILIAL = 999 THEN '51'
						ELSE CASE WHEN GLB_MOV_OP.DEST_ORIG = 1
								THEN 'NU'
								ELSE '30'
							END
					END
            WHEN 16 THEN '73'
            WHEN 3 THEN '22'
            WHEN 5 THEN '22'
            ELSE 'NU'
        END AS TIPO,
        GLB_MOV_OP.TP_MOV,
        ISNULL(DBO.EST_NF_SERIE.SERIE, '') AS SERIE  ,
        1 AS TP_NOTA,
        CONVERT(INT, 0)  CONTADOR_ITENS, --CONTA_ITENS.CONTADOR_ITENS,
        GLB_MOV_OP.FLAG_MOV_PROD_VENCIDO
		INTO #TmpEntradas
    FROM 
	 
	EST_NF_ENT E
	join #TmpNFEnt xt on 
									E.CD_EMP	= xt.CD_EMP
								AND E.CD_FILIAL = xt.CD_FILIAL
								AND E.CD_NF		= xt.CD_NF
								AND E.DT_ENT	= xt.DT_ENT
	
	INNER JOIN EST_NF_ENT_IT IT ON		 
									E.CD_EMP	= IT.CD_EMP
								AND E.CD_FILIAL = IT.CD_FILIAL
								AND E.CD_NF		= IT.CD_NF
								AND E.DT_ENT	= IT.DT_ENT
									 
	INNER JOIN GLB_NT_OP ON IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP

	INNER JOIN DBO.EST_NF_SERIE ON E.CD_EMP = DBO.EST_NF_SERIE.CD_EMP
				AND E.CD_NF_SERIE = DBO.EST_NF_SERIE.CD_NF_SERIE

	JOIN #TmpFilial F ON
		E.CD_EMP    = F.CD_EMP
	AND E.CD_FILIAL = F.CD_FILIAL

	LEFT JOIN EST_NF_ENT_DAR_IT ON     IT.CD_EMP	 = EST_NF_ENT_DAR_IT.CD_EMP
								AND IT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
								AND IT.CD_NF	 = EST_NF_ENT_DAR_IT.CD_NF
								AND IT.CD_PROD   = EST_NF_ENT_DAR_IT.CD_PROD
	INNER JOIN GLB_MOV_OP ON E.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
		
	LEFT JOIN PG_FORN FORN ON 
	E.CD_EMP	= FORN.CD_EMP AND
	E.CD_FORN	= FORN.CD_FORN

	LEFT JOIN EST_TRIB EST_TRIB_DARE ON EST_NF_ENT_DAR_IT.CD_TRIB = EST_TRIB_DARE.CD_TRIB
	LEFT JOIN GLB_NT_OP GLB_NT_OP_DARE ON EST_NF_ENT_DAR_IT.CD_NT_OP = GLB_NT_OP_DARE.CD_NT_OP
	INNER JOIN EST_TRIB ON IT.CD_TRIB = EST_TRIB.CD_TRIB

	INNER JOIN V_EST_NF_ENT_NFE NFE ON E.CD_EMP = NFE.CD_EMP
									AND E.CD_FILIAL = NFE.CD_FILIAL
									AND E.CD_NF		= NFE.CD_NF 

	LEFT JOIN PRC_BANDEIRA_PRC_FILIAL bnd_fil ON E.CD_EMP = bnd_fil.CD_EMP
											AND E.CD_FILIAL = bnd_fil.CD_FILIAL
	LEFT JOIN PRC_BANDEIRA bnd ON bnd_fil.CD_BANDEIRA = bnd.CD_BANDEIRA
    WHERE E.STS_NF = 0
		AND GLB_MOV_OP.TP_MOV IN(0, 10, 11, 3, 5, 16)		  
		AND	E.CD_EMP = @cdEmp	      
		AND  E.DT_ENT BETWEEN @DataIni AND  @DataFim
		;  
/******************************************************************************************************************************/ 

	UPDATE  #TmpEntradas 
		SET CD_PROD = 'R'+RIGHT('0000000000'+CAST(CD_PROD_GEST AS VARCHAR(50)), 9) ; 

 /******************************************************************************************************************************/ 
 -- CONTA_ITENS
 /******************************************************************************************************************************/ 

	UPDATE E 
	SET
		CONTADOR_ITENS = CONTA_ITENS.CONTADOR_ITENS
	FROM 
		#TmpEntradas  E
		INNER JOIN ( SELECT ENT.CD_EMP,
							ENT.CD_FILIAL,
							ENT.CD_NF,
							COUNT(*) AS CONTADOR_ITENS
							FROM EST_NF_ENT_IT ENT
								LEFT JOIN EST_NF_ENT_IT_EST_PROD_CPL_LOTE LOTE ON ENT.CD_EMP = LOTE.CD_EMP
								AND ENT.CD_FILIAL = LOTE.CD_FILIAL
								AND ENT.CD_NF	  = LOTE.CD_NF
								AND ENT.CD_PROD   = LOTE.CD_PROD
						GROUP BY 
							ENT.CD_EMP,
							ENT.CD_FILIAL,
							ENT.CD_NF
						) CONTA_ITENS ON 
			E.CD_EMP			= CONTA_ITENS.CD_EMP
		AND E.CD_FILIAL_GEST	= CONTA_ITENS.CD_FILIAL
		AND E.CD_NF				= CONTA_ITENS.CD_NF;

		 
/******************************************************************************************************************************/ 
-- TOTAIS_DARE
/******************************************************************************************************************************/        
	  
	UPDATE T
	SET
		VLR_ICMS			= TOTAIS_DARE.VLR_ICMS		+ t.VLR_FECOP ,
		VLR_ICMS_SUBS		= TOTAIS_DARE.VLR_ICMS_SUBS + t.VLR_FECOP_ST,
		VLR_ICMS_DARE		= TOTAIS_DARE.VLR_ICMS_DARE,
		VLR_ICMS_SUBS_DARE	= TOTAIS_DARE.VLR_ICMS_SUBS_DARE,             
		VLR_BASE_ICMS		= TOTAIS_DARE.VLR_BASE_ICMS,
		VLR_BASE_ICMS_SUBS	= TOTAIS_DARE.VLR_BASE_SUBS
	from 
		#TmpEntradas t
		INNER JOIN (SELECT 
							EST_NF_ENT_IT.CD_EMP,
							EST_NF_ENT_IT.CD_FILIAL,
							EST_NF_ENT_IT.CD_NF,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS, EST_NF_ENT_IT.VLR_ICMS_IT)) AS VLR_ICMS,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS, EST_NF_ENT_IT.VLR_ICMS_SUBS)) AS VLR_ICMS_SUBS,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_ICMS, EST_NF_ENT_IT.VLR_BASE_ICMS)) AS VLR_BASE_ICMS,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_SUBS, EST_NF_ENT_IT.VLR_BASE_SUBS)) AS VLR_BASE_SUBS,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS, 0)) AS VLR_ICMS_DARE,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS, 0)) AS VLR_ICMS_SUBS_DARE,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_ICMS, 0)) AS VLR_BASE_ICMS_DARE,
							SUM(COALESCE(EST_NF_ENT_DAR_IT.VLR_BASE_SUBS, 0)) AS VLR_BASE_SUBS_DARE
						FROM 
							EST_NF_ENT_IT
							LEFT JOIN EST_NF_ENT_DAR_IT ON 
															EST_NF_ENT_IT.CD_EMP	= EST_NF_ENT_DAR_IT.CD_EMP
														AND EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
														AND EST_NF_ENT_IT.CD_NF		= EST_NF_ENT_DAR_IT.CD_NF
														AND EST_NF_ENT_IT.CD_PROD	= EST_NF_ENT_DAR_IT.CD_PROD
						GROUP BY 
							EST_NF_ENT_IT.CD_EMP,
							EST_NF_ENT_IT.CD_FILIAL,
							EST_NF_ENT_IT.CD_NF
					) TOTAIS_DARE ON		t.CD_EMP	     = TOTAIS_DARE.CD_EMP
										AND	t.CD_FILIAL_GEST = TOTAIS_DARE.CD_FILIAL
										AND t.CD_NF			 = TOTAIS_DARE.CD_NF; 

/******************************************************************************************************************************/ 
-- BASE
/******************************************************************************************************************************/        
	
	UPDATE T
	SET

		VLR_IPI			= BASE.VLR_IPI,
		VLR_BASE_IPI	= BASE.VLR_BASE_IPI,
		VLR_ISENTAS		= BASE.VLR_ISENTAS,
		VLR_OUTROS		= BASE.VLR_OUTROS,
		VLR_DESC_COM	= BASE.VLR_DESC_COM,
		VLR_DESC_FINAN	= BASE.VLR_DESC_FINAN
    FROM  
		#TmpEntradas T
		JOIN 
			(SELECT 
				   E.CD_EMP,
				   E.CD_FILIAL,
				   E.CD_NF,
				   SUM(CASE WHEN it.VLR_IPI > 0 THEN VLR_TOT_IT
						   ELSE 0 END) AS VLR_BASE_IPI,

				   SUM(ROUND(it.VLR_IPI, 2))	AS VLR_IPI,
				   SUM(it.VLR_DESC)				AS VLR_DESC_COM,
				   SUM(COALESCE(it.VLR_DESC_FINAN, 0)) AS VLR_DESC_FINAN,
				   SUM(ROUND(CASE
								 WHEN EST_TRIB_DARE.SUBS_TRIB IS NOT NULL
								 THEN CASE
										  WHEN(EST_TRIB_DARE.SUBS_TRIB + EST_TRIB_DARE.SUBS_TRIB_ANT) = 0
										  THEN 0
										  ELSE CASE
												   WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
												   THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) + EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS
												   ELSE 0
											   END
									  END + CASE
												WHEN EST_TRIB_DARE.FLAG_OUTROS = 0
												THEN 0
												ELSE CASE
														 WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
														 THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
														 ELSE 0
													 END
											END
								 ELSE CASE
										  WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0
										  THEN 0
										  ELSE CASE
												   WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
												   THEN ROUND(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) + IT.VLR_ICMS_SUBS, 2)
												   ELSE 0
											   END
									  END + CASE
												WHEN EST_TRIB.FLAG_OUTROS = 0
												THEN 0
												ELSE CASE
														 WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
														 THEN ROUND(IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END)) - IT.VLR_DESC, 2)
														 ELSE 0
													 END
											END
							 END + ROUND(IT.VLR_OUTRAS_DESPESAS, 2), 2)) AS VLR_OUTROS,
				   SUM(CASE
						   WHEN EST_TRIB_DARE.SUBS_TRIB IS NOT NULL
						   THEN CASE
									WHEN(EST_TRIB_DARE.SUBS_TRIB + EST_TRIB_DARE.SUBS_TRIB_ANT) = 0
									THEN CASE
											 WHEN  FORN.FLAG_FORN_MICRO_EMPRESA = 0 AND GLB_MOV_OP.DEST_ORIG=1  
											 THEN CASE
													  WHEN EST_TRIB_DARE.ICMS = 0
														   AND EST_TRIB_DARE.ISENTO = 0
													  THEN 0
													  ELSE CASE
															   WHEN EST_TRIB_DARE.ISENTO = 0
															   THEN CASE
																		WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - EST_NF_ENT_DAR_IT.VLR_BASE_SUBS > 0
																		THEN(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - EST_NF_ENT_DAR_IT.VLR_BASE_ICMS)
																		ELSE 0
																	END
															   ELSE CASE
																		WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
																		THEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC)
																		ELSE 0
																	END
														   END
												  END
											 ELSE 0
										 END
									ELSE 0
								END
						   ELSE CASE
									WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0
									THEN CASE
											 WHEN FORN.FLAG_FORN_MICRO_EMPRESA = 0 AND GLB_MOV_OP.DEST_ORIG=1  
											 THEN CASE
													  WHEN EST_TRIB.ICMS = 0
														   AND EST_TRIB.ISENTO = 0
													  THEN 0
													  ELSE CASE
															   WHEN EST_TRIB.ISENTO = 0
															   THEN CASE
																		WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - IT.VLR_BASE_ICMS > 0
																		THEN ROUND(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) - IT.VLR_BASE_ICMS, 2)
																		ELSE 0
																	END
															   ELSE CASE
																		WHEN((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC) > 0
																		THEN ROUND(((IT.QT_IT * (CONVERT(FLOAT, IT.VLR_TOT_IT) / CONVERT(FLOAT,CASE WHEN IT.QT_IT = 0 THEN 1 ELSE IT.QT_IT END))) - IT.VLR_DESC), 2)
																		ELSE 0
																	END
														   END
												  END
											 ELSE 0
										 END
									ELSE 0
								END
					   END) AS VLR_ISENTAS
			FROM 
				EST_NF_ENT E
				INNER JOIN EST_NF_ENT_IT IT ON		 
										E.CD_EMP	= IT.CD_EMP
									AND E.CD_FILIAL = IT.CD_FILIAL
									AND E.CD_NF		= IT.CD_NF
									AND E.DT_ENT	= IT.DT_ENT
				LEFT JOIN PG_FORN FORN ON 
				E.CD_EMP	= FORN.CD_EMP AND
				E.CD_FORN	= FORN.CD_FORN

				INNER JOIN GLB_MOV_OP ON E.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	
				INNER JOIN EST_TRIB ON it.CD_TRIB = EST_TRIB.CD_TRIB
				LEFT JOIN EST_NF_ENT_DAR_IT ON		it.CD_EMP	 = EST_NF_ENT_DAR_IT.CD_EMP
											AND it.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL
											AND it.CD_NF	 = EST_NF_ENT_DAR_IT.CD_NF
											AND it.CD_PROD	 = EST_NF_ENT_DAR_IT.CD_PROD
				 LEFT JOIN EST_TRIB EST_TRIB_DARE ON EST_NF_ENT_DAR_IT.CD_TRIB = EST_TRIB_DARE.CD_TRIB
			GROUP BY E.CD_EMP,
					 E.CD_FILIAL,
					 E.CD_NF,
					 EST_TRIB.FLAG_OUTROS,
					 EST_TRIB.SUBS_TRIB,
					 EST_TRIB.SUBS_TRIB_ANT,
					 EST_TRIB.ICMS,
					 EST_TRIB.ISENTO) BASE ON 
						T.CD_EMP		 = BASE.CD_EMP
					AND T.CD_FILIAL_GEST = BASE.CD_FILIAL
					AND T.CD_NF			 = BASE.CD_NF  ;
  
/******************************************************************************************************************************/
-- Resultado das entradas
/******************************************************************************************************************************/
    
	insert into #tmpResult (CD_PROD,       QT_IT,       VLR_IT,       VLR_TOT_IT,       VLR_ICMS_IT,       VLR_ICMS_SUBS_IT,       VLR_IPI_IT,       VLR_DESC_IT,
       VLR_DESC_FINAN_PROD,       VLR_BASE_ICMS_IT,       VLR_BASE_SUBS_IT,       VLR_ISENTO_IT,       VLR_OUTROS_IT,       VLR_OUTRAS_DESPESAS,       ALIQ_ICMS,
       PERC_IPI,       CFOP,       VLR_BASE_IPI_IT,       NR_LOTE,       CD_EMP,       CD_FILIAL_GEST,       CD_FILIAL,       CD_EMPRESA_CONTABIL,       CD_NF,
       NR_NF,       CNPJ_FORN,       CNPJ_FILIAL,       DT_EMI_NF,       DT_ENT,       VLR_NF,       VLR_DESC_CAB,       VLR_ICMS,       VLR_ICMS_SUBS,       VLR_ICMS_DARE,       VLR_ICMS_SUBS_DARE,
       VLR_IPI,       HR_ENT,       NR_AUTORIZADOR,       CD_PED,       VLR_BASE_ICMS,       VLR_BASE_ICMS_SUBS,       VLR_BASE_IPI,       VLR_ISENTAS,      VLR_OUTROS,       VLR_DESC_COM,
       VLR_DESC_FINAN,       CD_TRIB_FC,       TIPO,       TP_MOV,       SERIE,       TP_NOTA,       CONTADOR_ITENS,       VLR_ICMS_CRITERIO,       POSSUI_DARE,     FLAG_MOV_PROD_VENCIDO)
	SELECT 
	   CD_PROD,
       QT_IT,
       VLR_IT,
       VLR_TOT_IT,
       VLR_ICMS_IT,
       VLR_ICMS_SUBS_IT,
       VLR_IPI_IT,
       VLR_DESC_IT,
       VLR_DESC_FINAN_PROD,
       VLR_BASE_ICMS_IT,
       VLR_BASE_SUBS_IT,
       VLR_ISENTO_IT,
       VLR_OUTROS_IT,
       VLR_OUTRAS_DESPESAS,
       ALIQ_ICMS,
       PERC_IPI,
       CFOP,
       VLR_BASE_IPI_IT,
       NR_LOTE,
       CD_EMP,
       CD_FILIAL_GEST,
       CD_FILIAL,
       CD_EMPRESA_CONTABIL,
       CD_NF,
       NR_NF,
       CNPJ_FORN,
       CNPJ_FILIAL,
       DT_EMI_NF,
       DT_ENT,
       VLR_NF,
       VLR_DESC_CAB,
       VLR_ICMS,
       VLR_ICMS_SUBS,
       VLR_ICMS_DARE,
       VLR_ICMS_SUBS_DARE,
       VLR_IPI,
       HR_ENT,
       NR_AUTORIZADOR,
       CD_PED,
       VLR_BASE_ICMS,
       VLR_BASE_ICMS_SUBS,
       VLR_BASE_IPI,
       VLR_ISENTAS,
       VLR_OUTROS,
       VLR_DESC_COM,
       VLR_DESC_FINAN,
       CD_TRIB_FC,
       TIPO,
       TP_MOV,
       SERIE,
       TP_NOTA,
       CONTADOR_ITENS,
       VLR_ICMS_SUBS_IT + VLR_BASE_SUBS_IT + VLR_ICMS_IT + VLR_BASE_ICMS_IT AS VLR_ICMS_CRITERIO,
       POSSUI_DARE,
       FLAG_MOV_PROD_VENCIDO
	FROM #TmpEntradas T;  

END

/******************************************************************************************************************************/

 if @tpRela in (1,2)
 begin

	 SELECT
		  'R0000000000' AS CD_PROD
		 , it.CD_PROD as CD_PROD_GES
		 , COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT) AS QT_IT
		 , IT.VLR_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN (CASE WHEN ISNULL(e.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN IT.VLR_TOT_IT ELSE IT.VLR_TOT_IT - IT.VLR_DESC_IT END) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE (CASE WHEN ISNULL(e.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN IT.VLR_TOT_IT ELSE IT.VLR_TOT_IT - IT.VLR_DESC_IT END)
				 END, 2, 1) AS VLR_TOT_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(IT.VLR_ICMS_IT + IT.VLR_FECOP) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE IT.VLR_ICMS_IT + IT.VLR_FECOP
				 END, 2, 1) AS VLR_ICMS_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(E.VLR_ICMS_SUBS + IT.VLR_FECOP_ST) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE E.VLR_ICMS_SUBS + IT.VLR_FECOP_ST
				 END, 2, 1) AS VLR_ICMS_SUBS_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN ( CASE WHEN ISNULL(it.VLR_IPI_IT, 0) = 0 THEN 
		CASE WHEN ISNULL(e.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN ROUND(ISNULL((it.VLR_TOT_IT + it.VLR_OUTROS_DESPESAS) * it.PERC_IPI, 0) / 100, 2)
			 ELSE ROUND(ISNULL((it.VLR_TOT_IT - it.VLR_DESC_IT + it.VLR_OUTROS_DESPESAS) * it.PERC_IPI, 0) / 100, 2) END
		ELSE it.VLR_IPI_IT END) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE ( CASE WHEN ISNULL(it.VLR_IPI_IT, 0) = 0 THEN 
		CASE WHEN ISNULL(e.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN ROUND(ISNULL((it.VLR_TOT_IT + it.VLR_OUTROS_DESPESAS) * it.PERC_IPI, 0) / 100, 2)
			 ELSE ROUND(ISNULL((it.VLR_TOT_IT - it.VLR_DESC_IT + it.VLR_OUTROS_DESPESAS) * it.PERC_IPI, 0) / 100, 2) END
		ELSE it.VLR_IPI_IT END)
				 END, 2, 1) AS VLR_IPI_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN IT.VLR_DESC_IT / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE IT.VLR_DESC_IT
				 END, 2, 1) AS VLR_DESC_IT
		 , 0 AS VLR_DESC_FINAN_PROD
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN E.VLR_BASE_ICMS_IT / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE E.VLR_BASE_ICMS_IT
				 END, 2, 1) AS VLR_BASE_ICMS_IT
		 , ROUND(CASE
					 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN IT.VLR_BASE_SUBS / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
					 ELSE IT.VLR_BASE_SUBS
				 END, 2, 1) AS VLR_BASE_SUBS_IT
		 , ROUND(CASE
					 WHEN EST_TRIB.ICMS = 0
						  AND EST_TRIB.ISENTO = 0 THEN 0
					 ELSE CASE
							  WHEN EST_TRIB.ISENTO = 0 THEN CASE
																WHEN((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) - E.VLR_BASE_ICMS_IT > 0 THEN CASE
																																			 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) - E.VLR_BASE_ICMS_IT) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
																																			 ELSE(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) - E.VLR_BASE_ICMS_IT)
																																		 END
																ELSE 0
															END
							  ELSE CASE
									   WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2)) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
									   ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2)
								   END
						  END
				 END, 2, 1) AS VLR_ISENTO_IT
		 , ROUND(CASE
					 WHEN E.CD_FILIAL = 999 THEN CASE
													 WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0 THEN 0
													 ELSE CASE
															  WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2)) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
															  ELSE(E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))
														  END + CASE
																	WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN IT.VLR_OUTROS_DESPESAS / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
																	ELSE IT.VLR_OUTROS_DESPESAS
																END
												 END
					 ELSE CASE
							  WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0 THEN 0
							  ELSE CASE
									   WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
									   ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))
								   END
						  END + CASE
									WHEN EST_TRIB.FLAG_OUTROS = 0 THEN 0
									ELSE CASE
											 WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
											 ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))
										 END
								END + CASE
										  WHEN LOTE_ENT.QTDE_PROD IS NOT NULL THEN(IT.VLR_OUTROS_DESPESAS / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT))
										  ELSE IT.VLR_OUTROS_DESPESAS
									  END
				 END, 2, 1) AS VLR_OUTROS_IT
		 , 0 AS VLR_OUTRAS_DESPESAS
		 , E.ALIQ_ICMS
		 , 0 AS POSSUI_DARE
		 , IT.PERC_IPI
		 ,GLB_NT_OP.CD_NT_OP_FC AS CFOP
		 , CASE
			   WHEN (CASE WHEN ISNULL(IT.VLR_IPI_IT, 0) = 0 THEN 
		CASE WHEN ISNULL(E.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN 
			ROUND(ISNULL((IT.VLR_TOT_IT + IT.VLR_OUTROS_DESPESAS) * IT.PERC_IPI, 0) / 100, 2) 
			ELSE ROUND(ISNULL((IT.VLR_TOT_IT - IT.VLR_DESC_IT + IT.VLR_OUTROS_DESPESAS) * IT.PERC_IPI, 0) / 100, 2) 
			END
		ELSE IT.VLR_IPI_IT END)  > 0 THEN (CASE WHEN ISNULL(e.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN IT.VLR_TOT_IT ELSE IT.VLR_TOT_IT - IT.VLR_DESC_IT END) / it.QT_IT * COALESCE(LOTE_ENT.QTDE_PROD, it.QT_IT)
			   ELSE 0
		   END AS VLR_BASE_IPI_IT
		 , LEFT(COALESCE(LOTE.NR_LOTE, ''), 10) AS NR_LOTE
		 , E.CD_EMP
		 , E.CD_FILIAL AS CD_FILIAL_GEST
		 , F.CD_CENTRO_CUSTO_CONTABIL AS CD_FILIAL
		 , F.CD_EMPRESA_CONTABIL
		 , E.CD_NF
		 , E.NF_NF AS NR_NF
		 , E.CNPJ_DESTINATARIO  AS CNPJ_FORN
		 , F.CGC AS CNPJ_FILIAL
		 , E.DT_EMI_NF
		 , E.DT_EMI_NF AS DT_ENT  
  
		 , E.VLR_NF
		 , E.VLR_DESC_CORPO AS VLR_DESC_CAB
		 , E.VLR_ICMS + E.VLR_FECOP AS VLR_ICMS
		 , E.VLR_ICMS_SUBS + E.VLR_FECOP_ST AS VLR_ICMS_SUBS
		 , 0 AS VLR_ICMS_DARE
		 , 0 AS VLR_ICMS_SUBS_DARE
		 , E.VLR_IPI
		 , E.HR_EMI_NF AS HR_ENT
		 , ISNULL(NFE.NR_AUTORIZADOR, '') AS NR_AUTORIZADOR
		 , E.CD_PED
		 , E.VLR_BASE_ICMS
		 , E.VLR_BASE_ICMS_SUBS AS VLR_BASE_ICMS_SUBS


		 , Convert(money, 0)  VLR_BASE_IPI	--, BASE.VLR_BASE_IPI
		 , Convert(money, 0)  VLR_ISENTAS	--, BASE.VLR_ISENTAS
		 , Convert(money, 0)  VLR_OUTROS	--, BASE.VLR_OUTROS
		 , Convert(money, 0)  VLR_DESC_COM	--, BASE.VLR_DESC_COM

		 , 0 AS VLR_DESC_FINAN
		 , EST_TRIB.CD_TRIB_FC  
		 , CASE GLB_MOV_OP.TP_MOV
			   WHEN 0 THEN '50'
			   WHEN 10 THEN '13'
			   WHEN 11 THEN '40'
			   WHEN 4 THEN '11'
			   WHEN 16 THEN CASE GLB_MOV_OP.DEST_ORIG
								WHEN 2 THEN '11'
								ELSE '12'
							END
			   WHEN 24 THEN '84'
			   WHEN 12 THEN '84'
			   ELSE 'NU'
		   END AS TIPO
		 , GLB_MOV_OP.TP_MOV
		 , EST_NF_SERIE.SERIE
		 , 2 AS TP_NOTA
		 , CONVERT(INT, 0) CONTADOR_ITENS --, CONTA_ITENS.CONTADOR_ITENS
		 , GLB_MOV_OP.FLAG_MOV_PROD_VENCIDO
		 into #TmpSaidas
	FROM
		 EST_NF_SAI E

		join #TmpNFSai xt on 
								E.CD_EMP	= xt.CD_EMP
							AND E.CD_FILIAL = xt.CD_FILIAL
							AND E.CD_NF		= xt.CD_NF
							AND E.DT_EMI_NF	= xt.DT_EMI_NF

		 JOIN EST_NF_SAI_IT IT ON E.CD_EMP = IT.CD_EMP
								  AND E.CD_FILIAL = IT.CD_FILIAL
								  AND E.CD_NF = IT.CD_NF
								  AND E.DT_EMI_NF = IT.DT_EMI_NF
		 INNER JOIN GLB_NT_OP ON IT.CD_NT_OP = dbo.GLB_NT_OP.CD_NT_OP
		 INNER JOIN EST_TRIB ON IT.CD_TRIB = EST_TRIB.CD_TRIB
		 
		 INNER JOIN #TmpFilial F ON E.CD_EMP = F.CD_EMP
									AND E.CD_FILIAL = F.CD_FILIAL

		 INNER JOIN EST_NF_SAI_NFE NFE ON E.CD_EMP = NFE.CD_EMP
										  AND E.CD_FILIAL = NFE.CD_FILIAL
										  AND E.CD_NF = NFE.CD_NF
		 INNER JOIN GLB_MOV_OP ON E.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
	 
		 INNER JOIN dbo.EST_NF_SERIE ON E.CD_EMP = dbo.EST_NF_SERIE.CD_EMP
											AND E.CD_NF_SERIE = dbo.EST_NF_SERIE.CD_NF_SERIE

 
    
		 LEFT JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE LOTE_ENT ON IT.CD_EMP = LOTE_ENT.CD_EMP
															   AND IT.CD_FILIAL = LOTE_ENT.CD_FILIAL
															   AND IT.CD_NF = LOTE_ENT.CD_NF
															   AND IT.CD_PROD = LOTE_ENT.CD_PROD
		 LEFT JOIN EST_PROD_CPL_LOTE LOTE ON LOTE_ENT.CD_EMP = LOTE.CD_EMP
											 AND LOTE_ENT.CD_FILIAL = LOTE.CD_FILIAL
											 AND LOTE_ENT.CD_LOTE = LOTE.CD_LOTE
											 AND LOTE_ENT.CD_PROD = LOTE.CD_PROD
		 LEFT JOIN PRC_BANDEIRA_PRC_FILIAL bnd_fil ON E.CD_EMP = bnd_fil.CD_EMP
													  AND E.CD_FILIAL = bnd_fil.CD_FILIAL
		 LEFT JOIN PRC_BANDEIRA bnd ON bnd_fil.CD_BANDEIRA = bnd.CD_BANDEIRA
	WHERE E.STS_NF = 0
		  AND glb_mov_op.TP_MOV IN(0, 10, 11, 4, 16, 24, 12)
		  AND e.CD_EMP=@cdEmp 
		  AND E.DT_EMI_NF BETWEEN @DataIni AND  @DataFim;


	/***********************************************************************************************************************************/

	/***********************************************************************************************************************************/

		UPDATE #TmpSaidas
			SET CD_PROD = 'R'+RIGHT('0000000000'+CAST(CD_PROD_GES AS VARCHAR(50)), 9);

	/***********************************************************************************************************************************/

		update s
		set
			CONTADOR_ITENS = CONTA_ITENS.CONTADOR_ITENS
		FROM
			#TmpSaidas s
			INNER JOIN (
					SELECT
							SAI.CD_EMP
							, SAI.CD_FILIAL
							, SAI.CD_NF
							, COUNT(*) AS CONTADOR_ITENS
					FROM
							est_nf_sai_it SAI
							LEFT JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE LOTE ON SAI.CD_EMP = LOTE.CD_EMP
																			AND SAI.CD_FILIAL = LOTE.CD_FILIAL
																			AND SAI.CD_NF = LOTE.CD_NF
																			AND SAI.CD_PROD = LOTE.CD_PROD
					GROUP BY
								SAI.CD_EMP
							, SAI.CD_FILIAL
							, SAI.CD_NF
				) CONTA_ITENS ON		s.CD_EMP		 = CONTA_ITENS.CD_EMP
									AND s.CD_FILIAL_GEST = CONTA_ITENS.CD_FILIAL
									AND s.CD_NF			 = CONTA_ITENS.CD_NF;


	/***********************************************************************************************************************************/

	/***********************************************************************************************************************************/

		UPDATE S
		  SET
			  VLR_BASE_IPI = BASE.VLR_BASE_IPI
			, VLR_ISENTAS = BASE.VLR_ISENTAS
			, VLR_OUTROS = BASE.VLR_OUTROS
			, VLR_DESC_COM = BASE.VLR_DESC_COM
		FROM #TmpSaidas S
			 INNER JOIN
		(
			SELECT
				   CD_EMP
				 , CD_FILIAL
				 , CD_NF
				 , SUM(ROUND(VLR_DESC_COM, 2)) AS VLR_DESC_COM
				 , 0 AS VLR_DESC_FINAN
				 , SUM(VLR_OUTROS) AS VLR_OUTROS
				 , SUM(VLR_BASE_IPI) AS VLR_BASE_IPI
				 , SUM(VLR_ISENTAS) AS VLR_ISENTAS
				 , SUM(QT_IT_TOTAL) AS QT_IT_TOTAL
			FROM
		(
			SELECT
				   e.CD_EMP
				 , e.CD_FILIAL
				 , e.CD_NF
				 , SUM(CASE
						   WHEN PERC_IPI > 0 THEN VLR_TOT_IT
						   ELSE 0
					   END) AS VLR_BASE_IPI
				 , SUM(VLR_DESC_IT) AS VLR_DESC_COM
				 , SUM(ROUND(CASE
								 WHEN E.CD_FILIAL = 999 THEN CASE
																 WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0 THEN 0
																 ELSE(E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2)) + IT.VLR_OUTROS_DESPESAS
															 END
								 ELSE CASE
										  WHEN(EST_TRIB.SUBS_TRIB + EST_TRIB.SUBS_TRIB_ANT) = 0 THEN 0
										  ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))
									  END + CASE
												WHEN EST_TRIB.FLAG_OUTROS = 0 THEN 0
												ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2))
											END + IT.VLR_OUTROS_DESPESAS
							 END, 2)) AS VLR_OUTROS
				 , SUM(ROUND(CASE
								 WHEN EST_TRIB.ICMS = 0
									  AND EST_TRIB.ISENTO = 0 THEN 0
								 ELSE CASE
										  WHEN EST_TRIB.ISENTO = 0 THEN CASE
																			WHEN((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) - E.VLR_BASE_ICMS_IT > 0 THEN(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) - E.VLR_BASE_ICMS_IT)
																			ELSE 0
																		END
										  ELSE((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) + E.VLR_ICMS_SUBS + ROUND(ISNULL(((it.QT_IT * IT.VLR_IT) - IT.VLR_DESC_IT) * IT.PERC_IPI, 0) / 100, 2)
									  END
							 END, 2)) AS VLR_ISENTAS
				 , SUM(it.QT_IT) AS QT_IT_TOTAL
			FROM
				 EST_NF_SAI E
				 JOIN EST_NF_SAI_IT IT ON E.CD_EMP = IT.CD_EMP
										  AND E.CD_FILIAL = IT.CD_FILIAL
										  AND E.CD_NF = IT.CD_NF
										  AND E.DT_EMI_NF = IT.DT_EMI_NF
				 INNER JOIN EST_TRIB ON IT.CD_TRIB = EST_TRIB.CD_TRIB
			GROUP BY
					 E.CD_EMP
				   , E.CD_FILIAL
				   , E.CD_NF
				   , EST_TRIB.FLAG_OUTROS
				   , EST_TRIB.SUBS_TRIB
				   , EST_TRIB.SUBS_TRIB_ANT
				   , EST_TRIB.ICMS
				   , EST_TRIB.ISENTO
		) TBL
			GROUP BY
					 CD_EMP
				   , CD_FILIAL
				   , CD_NF
		) BASE ON S.CD_EMP = BASE.CD_EMP
				  AND S.CD_FILIAL_GEST = BASE.CD_FILIAL
				  AND S.CD_NF = BASE.CD_NF;



	/***********************************************************************************************************************************/
	/***********************************************************************************************************************************/

			  /*
		 LEFT JOIN
	(
		SELECT
			   EST_NF_ENT_NF_SAI.CD_EMP
			 , EST_NF_ENT_NF_SAI.CD_FILIAL
			 , EST_NF_ENT_NF_SAI.CD_NF_SAI AS CD_NF
			 , MAX(EST_NF_ENT_EST_PED_VD.NR_PROTOCOLO) AS NR_PROTOCOLO
			 , SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_TOTAL_ENT
		FROM
			 EST_NF_ENT_NF_SAI
			 INNER JOIN EST_NF_ENT_EST_PED_VD ON EST_NF_ENT_NF_SAI.CD_EMP = EST_NF_ENT_EST_PED_VD.CD_EMP
												 AND EST_NF_ENT_NF_SAI.CD_FILIAL = EST_NF_ENT_EST_PED_VD.CD_FILIAL
												 AND EST_NF_ENT_NF_SAI.CD_NF_ENT = EST_NF_ENT_EST_PED_VD.CD_NF
			 INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT_NF_SAI.CD_EMP = EST_NF_ENT_IT.CD_EMP
										 AND EST_NF_ENT_NF_SAI.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL
										 AND EST_NF_ENT_NF_SAI.CD_NF_ENT = EST_NF_ENT_IT.CD_NF
		GROUP BY
				 EST_NF_ENT_NF_SAI.CD_EMP
			   , EST_NF_ENT_NF_SAI.CD_FILIAL
			   , EST_NF_ENT_NF_SAI.CD_NF_SAI
	) PROTOCOLO ON E.CD_EMP = PROTOCOLO.CD_EMP
				   AND E.CD_FILIAL = PROTOCOLO.CD_FILIAL
				   AND E.CD_NF = PROTOCOLO.CD_NF
			   
				   */

	/***********************************************************************************************************************************/


	insert into #tmpResult
	SELECT CD_PROD,
       QT_IT,
       VLR_IT,
       VLR_TOT_IT,
       VLR_ICMS_IT,
       VLR_ICMS_SUBS_IT,
       VLR_IPI_IT,
       VLR_DESC_IT,
       VLR_DESC_FINAN_PROD,
       VLR_BASE_ICMS_IT,
       VLR_BASE_SUBS_IT,
       VLR_ISENTO_IT,
       VLR_OUTROS_IT,
       VLR_OUTRAS_DESPESAS,
       ALIQ_ICMS,
       PERC_IPI,
       CFOP,
       VLR_BASE_IPI_IT,
       NR_LOTE,
       CD_EMP,
       CD_FILIAL_GEST,
       CD_FILIAL,
       CD_EMPRESA_CONTABIL,
       CD_NF,
       NR_NF,
       CNPJ_FORN,
       CNPJ_FILIAL,
       DT_EMI_NF,
       DT_ENT,
       VLR_NF,
       VLR_DESC_CAB,
       VLR_ICMS,
       VLR_ICMS_SUBS,
       VLR_ICMS_DARE,
       VLR_ICMS_SUBS_DARE,
       VLR_IPI,
       HR_ENT,
       NR_AUTORIZADOR,
       CD_PED,
       VLR_BASE_ICMS,
       VLR_BASE_ICMS_SUBS,
       VLR_BASE_IPI,
       VLR_ISENTAS,
       VLR_OUTROS,
       VLR_DESC_COM,
       VLR_DESC_FINAN,
       CD_TRIB_FC,
       TIPO,
       TP_MOV,
       SERIE,
       TP_NOTA,
       CONTADOR_ITENS,
       VLR_ICMS_SUBS_IT + VLR_BASE_SUBS_IT + VLR_ICMS_IT + VLR_BASE_ICMS_IT AS VLR_ICMS_CRITERIO,
       POSSUI_DARE,
       FLAG_MOV_PROD_VENCIDO
	FROM #TmpSaidas S;  
 end

/******************************************************************************************************/
	SELECT
		   *
	FROM
		 #tmpResult
	ORDER BY
			 CD_FILIAL
		   , TP_NOTA
		   , DT_ENT
		   , CD_NF
		   , VLR_ICMS_SUBS_IT + VLR_BASE_SUBS_IT + VLR_ICMS_IT + VLR_BASE_ICMS_IT
		   , VLR_OUTROS_IT
		   , CD_PROD;
/******************************************************************************************************/
GO


