/****** Object:  View [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_ITENS]    Script Date: 26/08/2014 11:50:51 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_ITENS]'))
DROP VIEW [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_ITENS]
GO
GO
CREATE view  [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_ITENS] AS
SELECT     
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP, 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL, 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF, 
	EST_NF_ENT_DANFE_PROVISORIO.NR_NF,
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_CTR, 
    EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_PROD, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.QTDE_PROD) AS QTDE_PROD, 
    AVG(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_UNIT) AS VLR_PROD_UNIT, 
	AVG(EST_NF_ENT_DANFE_PROVISORIO_ITENS.PERC_PROD_DESC) AS PERC_PROD_DESC, 
    SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_TOTAL) AS VLR_PROD_TOTAL, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_TOTAL - EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_DESC) AS VLR_PROD_TOTAL_LIQ, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_BASE_ICMS) AS VLR_PROD_BASE_ICMS, 
    SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_ICMS) AS VLR_PROD_ICMS, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_BASE_IPI) AS VLR_PROD_BASE_IPI, 
    SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_IPI) AS VLR_PROD_IPI, 
	AVG(EST_NF_ENT_DANFE_PROVISORIO_ITENS.PERC_PROD_IPI) AS PERC_PROD_IPI, 
    AVG(EST_NF_ENT_DANFE_PROVISORIO_ITENS.PERC_PROD_ICMS) AS PERC_PROD_ICMS, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_BASE_ST) AS VLR_PROD_BASE_ST, 
    SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_ICMS_ST) AS VLR_PROD_ICMS_ST, 
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_DESC) AS VLR_PROD_DESC,
	EST_NF_ENT_DANFE_PROVISORIO.VLR_NF,
	AVG(EST_NF_ENT_DANFE_PROVISORIO_ITENS.PERC_PROD_ICMS_ST) AS PERC_PROD_ICMS_ST,
	CASE WHEN ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0)>0 THEN 
		CONVERT(VARCHAR(3),ISNULL(CD_SIT_OPERA_SIMPLES_NACIO,0))
	ELSE
		CONVERT(VARCHAR(1),ORIGEM) + CONVERT(VARCHAR(2),RIGHT('00'+RTRIM(LTRIM(ISNULL(CD_TRIB, 0))),2))
	END AS CST,
	CFOP,
	CD_SIT_OPERA_SIMPLES_NACIO,
	CONVERT(VARCHAR(1),ORIGEM) AS ORIGEM_DANFE,
	CONVERT(VARCHAR(2),RIGHT('00'+RTRIM(LTRIM(ISNULL(CD_TRIB, 0))),2)) AS CST_DANFE,
	AVG(ISNULL(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_PROD_FRETE,0)) AS VLR_PROD_FRETE,
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_BASE_ICMS_RET) AS VLR_BASE_ICMS_RET,
	SUM(EST_NF_ENT_DANFE_PROVISORIO_ITENS.VLR_ICMS_RET) AS VLR_ICMS_RET
FROM         
	EST_NF_ENT_DANFE_PROVISORIO_ITENS INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP AND 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO ON 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO.CD_EMP AND 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO.CD_CTR	
GROUP BY
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP, 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL, 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF, 
	EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_CTR, 
    EST_NF_ENT_DANFE_PROVISORIO_ITENS.CD_PROD,
	EST_NF_ENT_DANFE_PROVISORIO.VLR_NF,
	CONVERT(VARCHAR(1),ORIGEM),
	CONVERT(VARCHAR(2),RIGHT('00'+RTRIM(LTRIM(ISNULL(CD_TRIB, 0))),2)),
	CD_SIT_OPERA_SIMPLES_NACIO,
	CFOP,
	EST_NF_ENT_DANFE_PROVISORIO.NR_NF


