
IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_NF_ENT_IT]'))
DROP VIEW [DBO].[V_EST_NF_ENT_IT]    
GO
  
CREATE VIEW [DBO].[V_EST_NF_ENT_IT]  
AS  

--versao 2458
 
SELECT     
	dbo.EST_NF_ENT.CD_EMP, 
	dbo.EST_NF_ENT.CD_FILIAL, 
	dbo.EST_NF_ENT.CD_NF, 
	dbo.EST_NF_ENT.NR_NF, 
	dbo.EST_NF_ENT.CD_FORN, 
    dbo.EST_NF_ENT.DT_ENT, 
	dbo.EST_NF_ENT.DT_EMI_NF, 
	dbo.EST_NF_ENT_IT.CD_PROD, 
	dbo.EST_NF_ENT_IT.VLR_IT, 
	dbo.EST_NF_ENT_IT.QT_IT, 
    dbo.EST_NF_ENT_IT.VLR_ICMS_IT, 
	dbo.EST_NF_ENT_IT.ALIQ_ICMS, 
	dbo.EST_NF_ENT_IT.VLR_BASE_ICMS, 
	dbo.EST_NF_ENT_IT.VLR_BASE_SUBS, 
    dbo.EST_NF_ENT_IT.VLR_ICMS_SUBS, 
	dbo.EST_NF_ENT_IT.VLR_DESC, 
	dbo.EST_NF_ENT_IT.VLR_TOT_IT, 
	dbo.EST_NF_ENT_IT.VLR_IPI, 
	dbo.EST_NF_ENT.STS_NF, 
    dbo.EST_NF_ENT_IT.PERC_IVA, 
	dbo.EST_NF_ENT_IT.VLR_PRC_CUSTO AS VLR_CUSTO_IT, 
	dbo.EST_NF_ENT_IT.VLR_PRC_CUSTO_ANT AS VLR_CUSTO_ANT, 
	dbo.GLB_MOV_OP.TP_MOV_OP_TRANSA, 
	dbo.GLB_MOV_OP.TP_MOV, 
	dbo.GLB_MOV_OP.ATU_EST, 
	dbo.GLB_MOV_OP.ATU_EST_FLUT, 
	dbo.EST_TRIB.CD_TRIB_FC, 
    dbo.EST_NF_ENT.CD_NT_MOV, 
	dbo.GLB_MOV_OP.GERA_DP_PG, 
	dbo.EST_NF_ENT_IT.DS_PROD, 
	dbo.EST_NF_ENT.VLR_NF, 
    dbo.EST_NF_ENT_IT.PERC_DESC_FINAN, 
	dbo.EST_NF_ENT.VLR_TOT_PROD, 
	dbo.GLB_MOV_OP.DEST_ORIG, 
	dbo.EST_NF_ENT.DT_CAD, 
    CASE WHEN (dbo.EST_NF_ENT.VLR_NF - dbo.EST_NF_ENT.VLR_FRETE) = 0 THEN 
		0 
	ELSE 
		ROUND(CONVERT(FLOAT, dbo.EST_NF_ENT.VLR_FRETE) 
        / CONVERT(FLOAT, dbo.EST_NF_ENT.VLR_NF - dbo.EST_NF_ENT.VLR_FRETE), 9) 
        * (dbo.EST_NF_ENT_IT.VLR_TOT_IT + dbo.EST_NF_ENT_IT.VLR_IPI + dbo.EST_NF_ENT_IT.VLR_ICMS_SUBS) 
	END AS VLR_FRETE, 
    CASE WHEN (dbo.EST_NF_ENT.VLR_NF - dbo.EST_NF_ENT.VLR_OUTROS) = 0 THEN 
		0 
	ELSE 
		ROUND(CONVERT(FLOAT, dbo.EST_NF_ENT.VLR_OUTROS) 
        / CONVERT(FLOAT, dbo.EST_NF_ENT.VLR_NF - dbo.EST_NF_ENT.VLR_OUTROS), 9) 
        * (dbo.EST_NF_ENT_IT.VLR_TOT_IT + dbo.EST_NF_ENT_IT.VLR_IPI + dbo.EST_NF_ENT_IT.VLR_ICMS_SUBS) 
	END AS VLR_OUTROS,
	ISNULL( CASE WHEN  EST_TRIB.ICMS = 1 THEN
							EST_NF_ENT_IT.VLR_ICMS_IT
					  ELSE
							0
                      END ,0)AS VLR_ICMS_ENT,
	GLB_NT_OP.CD_NT_OP_FC,
    EST_NF_ENT.APLICATIVO ,
	EST_NF_ENT_IT.CD_TRIB,
	EST_NF_ENT_IT.CD_NT_OP,
	GLB_MOV_OP.DS_MOV_OP,
	ISNULL(EST_NF_ENT.VLR_ICMS_DESONERADO,0) AS VLR_ICMS_DESONERADO_CAB,
	EST_NF_ENT_IT.UN,
	EST_NF_ENT_IT.QT_EMB,
	EST_NF_ENT_IT.PERC_IPI,
	EST_PROD.NR_NCM,
	EST_NF_ENT_IT.PERC_REDU_BASE_ICMS,
	EST_NF_ENT_IT.PERC_REDU_BASE_SUBS,
	EST_NF_ENT_IT.ALIQ_ICMS_INTERNO,
	ISNULL(DBO.EST_NF_ENT_IT.VLR_BASE_PIS, 0) AS VLR_BASE_PIS,     
	ISNULL(DBO.EST_NF_ENT_IT.VLR_PIS, 0) AS VLR_PIS,     
	ISNULL(DBO.EST_NF_ENT_IT.PERC_ALIQ_PIS, 0) AS PERC_ALIQ_PIS,     
	ISNULL(DBO.EST_NF_ENT_IT.VLR_BASE_COFINS, 0) AS VLR_BASE_COFINS,     
	ISNULL(DBO.EST_NF_ENT_IT.VLR_COFINS, 0) AS VLR_COFINS,     
	ISNULL(DBO.EST_NF_ENT_IT.PERC_ALIQ_COFINS, 0) AS PERC_ALIQ_COFINS,
	ISNULL(DBO.EST_NF_ENT_IT.CST_PIS, 0) AS CST_PIS,      
	ISNULL(DBO.EST_NF_ENT_IT.CST_COFINS, 0) AS CST_COFINS,
	ISNULL(EST_NF_ENT_IT.VLR_FRETE_IT,0) AS VLR_FRETE_IT,
	ISNULL(EST_NF_ENT_IT.VLR_OUTRAS_DESPESAS,0) AS  VLR_OUTRAS_DESPESAS,
	ISNULL(EST_NF_ENT_IT.VLR_DESC_FINAN,0) AS VLR_DESC_FINAN,
	ISNULL(EST_NF_ENT_IT.VLR_BASE_ICMS_RET,0) AS VLR_BASE_ICMS_RET,
	ISNULL(EST_NF_ENT_IT.VLR_ICMS_RET,0) AS VLR_ICMS_RET	
FROM         
	DBO.EST_NF_ENT INNER JOIN DBO.EST_NF_ENT_IT ON 
	DBO.EST_NF_ENT.CD_EMP = DBO.EST_NF_ENT_IT.CD_EMP AND 
	DBO.EST_NF_ENT.CD_FILIAL = DBO.EST_NF_ENT_IT.CD_FILIAL AND 
    DBO.EST_NF_ENT.CD_NF = DBO.EST_NF_ENT_IT.CD_NF 
	INNER JOIN DBO.GLB_MOV_OP ON 
	DBO.EST_NF_ENT.CD_NT_MOV = DBO.GLB_MOV_OP.CD_MOV_OP 
	INNER JOIN DBO.EST_TRIB ON 
	DBO.EST_NF_ENT_IT.CD_TRIB = DBO.EST_TRIB.CD_TRIB
	INNER JOIN DBO.GLB_NT_OP ON 
	EST_NF_ENT_IT.CD_NT_OP = DBO.GLB_NT_OP.CD_NT_OP 
    INNER JOIN DBO.EST_PROD ON 
    DBO.EST_PROD.CD_EMP = DBO.EST_NF_ENT_IT.CD_EMP AND 
    DBO.EST_PROD.CD_PROD = DBO.EST_NF_ENT_IT.CD_PROD
