
/****** Object:  View [dbo].[V_EST_NF_SAI_CAB_DIFAL_FECOP]    Script Date: 04/11/2013 10:05:53 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_SAI_CAB_DIFAL_FECOP]'))
DROP VIEW [dbo].[V_EST_NF_SAI_CAB_DIFAL_FECOP]
GO
GO
GO
  
/*SP_HELP V_EST_NF_SAI_CAB_DIFAL_FECOP  
SELECT TOP 1 NF_NF, CD_FILIAL_CLI FROM V_EST_NF_SAI_CAB_DIFAL_FECOP where cd_emp = 1 and cd_filial = 111 and CD_nf = 4030*/  
--Versao 2979
CREATE VIEW [dbo].[V_EST_NF_SAI_CAB_DIFAL_FECOP]  
WITH ENCRYPTION
AS  
SELECT       
	dbo.EST_NF_SAI.CD_EMP,   
	dbo.EST_NF_SAI.CD_FILIAL,   
	dbo.EST_NF_SAI.CD_NF,   
	dbo.EST_NF_SAI.CD_NF_SERIE,   
	dbo.EST_NF_SAI.CD_PED,   
	dbo.EST_NF_SAI.CD_CLI,   
	ISNULL(RSDIFALORIG.DIFAL_RECOLHER_ORIG,0) AS DIFAL_RECOLHER_ORIG,
	ISNULL(RSDIFALDEST.DIFAL_RECOLHER_DEST,0) AS DIFAL_RECOLHER_DEST,
	ISNULL(RSDIFECOPDEST.VLR_FECOP_DEST,0)    AS VLR_FECOP_DEST

FROM  dbo.EST_NF_SAI    
   
 INNER JOIN dbo.GLB_MOV_OP ON   
 dbo.EST_NF_SAI.CD_NT_MOV = dbo.GLB_MOV_OP.CD_MOV_OP   
 
 INNER JOIN dbo.EST_NF_SERIE ON   
 dbo.EST_NF_SAI.CD_EMP = dbo.EST_NF_SERIE.CD_EMP AND   
    dbo.EST_NF_SAI.CD_NF_SERIE = dbo.EST_NF_SERIE.CD_NF_SERIE  
   
INNER JOIN( SELECT DAR.CD_EMP,
		 DAR.CD_FILIAL,
		 DAR.CD_NF,
		 DAR.VLR_DAR_RECOLHER AS DIFAL_RECOLHER_DEST
                FROM EST_NF_SAI_DAR DAR
			    WHERE DAR.UF_DESTINO = 1) RSDIFALDEST
ON EST_NF_SAI.CD_EMP = RSDIFALDEST.CD_EMP
    AND EST_NF_SAI.CD_FILIAL = RSDIFALDEST.CD_FILIAL
    AND EST_NF_SAI.CD_NF = RSDIFALDEST.CD_NF

INNER JOIN( SELECT DAR.CD_EMP,
		 DAR.CD_FILIAL,
		 DAR.CD_NF,
		 DAR.VLR_DAR_RECOLHER AS DIFAL_RECOLHER_ORIG
                FROM EST_NF_SAI_DAR DAR
				WHERE DAR.UF_DESTINO = 0) RSDIFALORIG

	ON EST_NF_SAI.CD_EMP = RSDIFALORIG.CD_EMP
    AND EST_NF_SAI.CD_FILIAL = RSDIFALORIG.CD_FILIAL
    AND EST_NF_SAI.CD_NF = RSDIFALORIG.CD_NF

INNER JOIN( SELECT   DAR.CD_EMP,
				DAR.CD_FILIAL,
				DAR.CD_NF,
				ROUND(SUM(DAR_IT.VLR_BASE_ICMS ),2) DAR_BASE_FECOP,
				ROUND(SUM(DAR_IT.VLR_BASE_ICMS * ISNULL( A.PERC_ALIQ_FECOP,0)/100),2) VLR_FECOP_DEST
				FROM EST_NF_SAI_DAR DAR INNER JOIN EST_NF_SAI_DAR_IT DAR_IT ON
					DAR.CD_EMP          = DAR_IT.CD_EMP
					AND DAR.CD_FILIAL   = DAR_IT.CD_FILIAL
					AND DAR.CD_NF       = DAR_IT.CD_NF
					AND DAR.CD_DAR      = DAR_IT.CD_DAR
					AND DAR.UF_DESTINO  = 1
				INNER JOIN EST_NF_SAI SAI ON 
					SAI.CD_EMP          = DAR.CD_EMP
					AND SAI.CD_FILIAL   = DAR.CD_FILIAL
					AND SAI.CD_NF       = DAR.CD_NF
				INNER JOIN EST_NF_SAI_IT NF_IF ON
					DAR_IT.CD_EMP          = NF_IF.CD_EMP
					AND DAR_IT.CD_FILIAL   = NF_IF.CD_FILIAL
					AND DAR_IT.CD_NF       = NF_IF.CD_NF
					AND DAR_IT.CD_PROD      = NF_IF.CD_PROD 
				
				OUTER APPLY UFD_EST_PROD_TRIB(SAI.CD_EMP,
				SAI.CD_FILIAL,
				SAI.CD_NT_MOV,
				SAI.CD_CLI,
				NF_IF.CD_PROD,0,0,0,0,0,0,0,0,0,ISNULL(SAI.TIPO_TRANSACAO,0),0)  A

				--WHERE DAR.CD_NF = 91204
				GROUP BY DAR.CD_EMP, DAR.CD_FILIAL, DAR.CD_NF,NF_IF.PERC_FECOP) RSDIFECOPDEST

	ON EST_NF_SAI.CD_EMP = RSDIFECOPDEST.CD_EMP
    AND EST_NF_SAI.CD_FILIAL = RSDIFECOPDEST.CD_FILIAL
    AND EST_NF_SAI.CD_NF = RSDIFECOPDEST.CD_NF



