/****** Object:  View [dbo].[V_EST_NF_ENT_EXPORT_CONTABIL]    Script Date: 08/25/2012 15:14:46 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_ENT_EXPORT_CONTABIL]'))
DROP VIEW [dbo].[V_EST_NF_ENT_EXPORT_CONTABIL]
GO
GO
/*SELECT          
	V_EST_NF_ENT_EXPORT_CONTABIL.CD_EMP,
	V_EST_NF_ENT_EXPORT_CONTABIL.CD_FILIAL,
	V_EST_NF_ENT_EXPORT_CONTABIL.NR_NF,
	V_EST_NF_ENT_EXPORT_CONTABIL.DT_ENT,
	V_EST_NF_ENT_EXPORT_CONTABIL.VLR_NF 
 FROM 
	V_EST_NF_ENT_EXPORT_CONTABIL 
WHERE 
	CD_EMP = 1 
	AND CD_FILIAL = 3 
	AND DT_ENT = '2009-12-04'*/

CREATE VIEW [dbo].[V_EST_NF_ENT_EXPORT_CONTABIL] AS
SELECT
         EST_NF_ENT.CD_EMP,
         EST_NF_ENT.CD_FILIAL,
         EST_NF_ENT.CD_NF,
         EST_NF_ENT.NR_NF,
         EST_NF_ENT.DT_ENT,
		 EST_NF_ENT.DT_EMI_NF AS DT_DEVO,
		 EST_NF_ENT.DT_CONTABILIZAR,
         EST_NF_ENT.VLR_NF,
		 PDV_VD.VLR_LIQ_VD
FROM
        EST_NF_ENT INNER JOIN PDV_VD_EST_NF_ENT ON
		EST_NF_ENT.CD_EMP    = PDV_VD_EST_NF_ENT.CD_EMP
		AND EST_NF_ENT.CD_FILIAL = PDV_VD_EST_NF_ENT.CD_FILIAL
		AND EST_NF_ENT.CD_NF     = PDV_VD_EST_NF_ENT.CD_NF
        INNER JOIN PDV_VD ON
		PDV_VD_EST_NF_ENT.CD_VD     = PDV_VD.CD_VD
		AND PDV_VD_EST_NF_ENT.CD_FILIAL = PDV_VD.CD_FILIAL
		AND PDV_VD_EST_NF_ENT.CD_EMP    = PDV_VD.CD_EMP
WHERE
     EST_NF_ENT.STS_NF = 0
UNION ALL
SELECT
         PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
         PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
         EST_NF_ENT.CD_NF,
         EST_NF_ENT.NR_NF,
		 EST_NF_ENT.DT_ENT,
		 PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,         
		 EST_NF_ENT.DT_CONTABILIZAR,
         SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_IT)			   AS VLR_NF,
		 PDV_VD.VLR_LIQ_VD
FROM
        PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT
        INNER JOIN PDV_VD_DEV_EST_PROD_IMP ON
		PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP    = PDV_VD_DEV_EST_PROD_IMP.CD_EMP
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_VD     = PDV_VD_DEV_EST_PROD_IMP.CD_VD
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IMP    = PDV_VD_DEV_EST_PROD_IMP.CD_IMP
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_IT    = PDV_VD_DEV_EST_PROD_IMP.CD_IT
		AND PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_PROD   = PDV_VD_DEV_EST_PROD_IMP.CD_PROD
		INNER JOIN EST_NF_ENT ON 
		EST_NF_ENT.CD_EMP    = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_EMP
        AND EST_NF_ENT.CD_FILIAL = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_FILIAL
        AND EST_NF_ENT.CD_NF     = PDV_VD_DEV_EST_PROD_IMP_EST_NF_ENT.CD_NF
		INNER JOIN PDV_VD ON
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP AND
		PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
		PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD 
GROUP   BY       
		PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
         PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
         EST_NF_ENT.CD_NF,
         EST_NF_ENT.NR_NF,
		 EST_NF_ENT.DT_ENT,
         PDV_VD_DEV_EST_PROD_IMP.DT_DEVO,
		 PDV_VD.VLR_LIQ_VD,
		 EST_NF_ENT.DT_CONTABILIZAR
GO


