/****** Object:  View [dbo].[V_EST_NF_SAI_TRANSF]    Script Date: 05/08/2013 09:22:37 ******/
IF  EXISTS (SELECT *
FROM sys.views
WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_SAI_TRANSF]'))
DROP VIEW [dbo].[V_EST_NF_SAI_TRANSF]
GO
GO
--SELECT TOP 10 * FROM V_EST_NF_SAI_TRANSF
--versao 4076
CREATE VIEW [dbo].[V_EST_NF_SAI_TRANSF]
AS
    SELECT
        dbo.EST_NF_SAI.CD_EMP,
        dbo.EST_NF_SAI.CD_FILIAL,
        dbo.EST_NF_SAI.CD_FILIAL AS CD_FORN,
        dbo.EST_NF_SAI.CD_NF,
        dbo.EST_NF_SAI.CD_NF_SERIE,
        dbo.EST_NF_SAI.NF_NF,
        dbo.EST_NF_SAI.CD_CLI,
        dbo.EST_NF_SAI.CD_NT_MOV,
        dbo.EST_NF_SAI.VLR_TOT_PROD,
        dbo.EST_NF_SAI.VLR_ICMS,
        0 AS VLR_ENCG_FIN,
        0 AS PERC_ICMS_ENCG_FIN,
        dbo.EST_NF_SAI.VLR_DESC,
        dbo.EST_NF_SAI.VLR_IPI,
        dbo.EST_NF_SAI.VLR_BASE_ICMS_SUBS AS VLR_BASE_SUBS,
        dbo.EST_NF_SAI.VLR_ICMS_SUBS,
        dbo.EST_NF_SAI.VLR_BASE_ICMS,
        dbo.EST_NF_SAI.VLR_FRETE,
        0 AS NF_IPI_BASE_CALC,
        dbo.EST_NF_SAI.STS_NF AS STS_PED,
        dbo.EST_NF_SAI_IT.CD_PROD,
        dbo.EST_PROD.DS_PROD,
        dbo.EST_PROD.CD_UN_CP,
        dbo.EST_NF_SAI_IT.QT_IT,
        dbo.EST_NF_SAI_IT.VLR_IT,
        dbo.EST_NF_SAI_IT.ALIQ_ICMS,
        dbo.EST_NF_SAI_IT.PERC_IPI,
        0 AS PERC_DESC,
        dbo.EST_NF_SAI_IT.CD_TRIB,
        dbo.GLB_MOV_OP.TP_MOV,
        dbo.EST_NF_SAI.VLR_NF,
        dbo.EST_NF_ENT.DT_ENT,
        dbo.EST_NF_SAI.DT_EMI_NF,
        dbo.EST_NF_ENT.NR_NF AS NR_NF_ENT,
        0 AS PERC_IVA,
        1 AS QT_EMB,
        dbo.EST_NF_SAI_IT.VLR_ICMS_IT,
        dbo.EST_NF_SAI_IT.VLR_BASE_SUBS AS VLR_BASE_SUBS_IT,
        dbo.EST_NF_SAI_IT.VLR_ICMS_SUBS AS VLR_ICMS_SUBS_IT,
        dbo.EST_NF_SAI_IT.VLR_BASE_ICMS_IT,
        dbo.EST_NF_SAI_IT.CD_NT_OP,
        dbo.EST_NF_SAI_IT.VLR_CUSTO AS VLR_CUSTO_IT,
        dbo.EST_NF_SAI_IT.VLR_ISENTO AS VLR_ISENTO_IT,
        dbo.EST_NF_SAI_IT.VLR_OUTROS AS VLR_OUTROS_IT,
        dbo.GLB_MOV_OP.DEST_ORIG, dbo.EST_NF_SAI_PED_VD.CD_PED,
        dbo.EST_NF_SAI.STS_NF,
        dbo.GLB_MOV_OP.DS_MOV_OP,
        dbo.GLB_MOV_OP.DS_MOV_OP_USUAL,
        EST_PROD.PROD_LIBERADO,
        dbo.EST_NF_SAI_IT.VLR_DESC_IT,
        case when isnull( dbo.GLB_MOV_OP.FLAG_NAO_ANALISAR_CUSTO_NA_ENTRADA,0) =0 then
		 ISNULL(EST_PROD.FLAG_NAO_ANALISAR_CUSTO,0) 
		 else isnull( dbo.GLB_MOV_OP.FLAG_NAO_ANALISAR_CUSTO_NA_ENTRADA,0) end AS FLAG_NAO_ANALISAR_CUSTO,
        ISNULL(VLR_OUTROS_DESPESAS,0) AS VLR_OUTROS_DESPESAS,
        EST_NF_SAI.DT_CAD,
        EST_NF_SAI_IT.UN AS UNID,
        ISNULL(EST_PROD.FLAG_NAO_CREDITAR_IMPOSTO,0) AS FLAG_NAO_CREDITAR_IMPOSTO,
        EST_PROD.APRES_EMB_CP,
        EST_PROD.APRES_QT_CP,
        EST_PROD.FLAG_IGNORAR_PMC_SUBST,
        ISNULL(EST_PROD.CTR_PMC,0) AS CTR_PMC,
        EST_NF_SAI_IT.PERC_REDU_BASE_SUBS,
        EST_NF_SAI_IT.PERC_REDU_BASE_ICMS,
        EST_NF_SAI_IT.VLR_PROD_FRETE,
        EST_NF_SAI_IT.VLR_BASE_PIS,
        EST_NF_SAI_IT.VLR_PIS,
        EST_NF_SAI_IT.VLR_BASE_COFINS,
        EST_NF_SAI_IT.VLR_COFINS,
        EST_NF_SAI_IT.PERC_ALIQ_PIS,
        EST_NF_SAI_IT.PERC_ALIQ_COFINS,
        CASE WHEN ISNULL(dbo.EST_NF_SAI.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN     
		ROUND(ISNULL(EST_NF_SAI_IT.VLR_TOT_IT * EST_NF_SAI_IT.PERC_IPI, 0)/100,2)           
	ELSE    
		ROUND(ISNULL((EST_NF_SAI_IT.VLR_TOT_IT - dbo.EST_NF_SAI_IT.VLR_DESC_IT) * EST_NF_SAI_IT.PERC_IPI, 0)/100,2)           
	END AS VLR_IPI_IT,
        ISNULL(EST_NF_SAI.CD_NT_OP_TRANSF_ENTRADA,0) AS CD_NT_OP_TRANSF_ENTRADA,
        ISNULL(EST_PROD.CONTROLE_SNGPC,0) AS CONTROLE_SNGPC,
        ISNULL(TP_SR_NFE,0) AS TP_SR_NFE,
        CASE WHEN ISNULL(EST_NF_SAI_NFE.CD_NF,0) = 0 THEN
		0
    ELSE
		1
    END AS NFE_VALIDADA,
        ISNULL(EST_NF_SAI_IT.VLR_CUSTO_GERENCIAL_TRANSF,0) AS VLR_CUSTO_GERENCIAL_TRANSF,
        EST_NF_SAI_IT.VLR_BASE_ICMS_RET,
        EST_NF_SAI_IT.VLR_ICMS_RET,
        EST_PROD.FLAG_PERMITE_RESSARCIMENTO_ICMS,
        ISNULL(EST_NF_SAI_IT.VLR_CUSTO_ACERTO,0) AS VLR_CUSTO_ACERTO,
        ISNULL(EST_NF_SAI_IT.ALIQ_ICMS_INTERNO,0) AS ALIQ_ICMS_INTERNO,
        ISNULL(EST_NF_SAI_IT.ICMS_DESONERADO_MOTIVO,0) AS ICMS_DESONERADO_MOTIVO,

        ISNULL(EST_NF_SAI_IT.VLR_BASE_FECOP,0) AS VLR_BASE_FECOP,
        ISNULL(EST_NF_SAI_IT.VLR_BASE_FECOP_ST,0) AS VLR_BASE_FECOP_ST,
        ISNULL(EST_NF_SAI_IT.VLR_BASE_FECOP_RET,0) AS VLR_BASE_FECOP_ST_RET,

        ISNULL(EST_NF_SAI_IT.PERC_FECOP,0) AS PERC_FECOP,
        ISNULL(EST_NF_SAI_IT.PERC_FECOP_ST,0) AS PERC_FECOP_ST,
        ISNULL(EST_NF_SAI_IT.PERC_FECOP_RET,0) AS PERC_FECOP_ST_RET,

        ISNULL(EST_NF_SAI_IT.VLR_FECOP,0) AS VLR_FECOP,
        ISNULL(EST_NF_SAI_IT.VLR_FECOP_ST,0) AS VLR_FECOP_ST,
        ISNULL(EST_NF_SAI_IT.VLR_FECOP_RET,0) AS VLR_FECOP_ST_RET,

        ISNULL(EST_NF_SAI.VLR_FECOP,0) AS CAB_VLR_FECOP,
        ISNULL(EST_NF_SAI.VLR_FECOP_ST,0) AS CAB_VLR_FECOP_ST,
        ISNULL(EST_NF_SAI.VLR_FECOP_RET,0) AS CAB_VLR_FECOP_ST_RET,

        ISNULL(EST_NF_SAI_IT.VLR_ICMS_DESONERADO, 0) AS VLR_ICMS_DESONERADO,
        ISNULL(EST_NF_SAI_IT.CD_BENEF, '') AS CD_BENEF,
        ISNULL(EST_NF_SAI.FLAG_NAO_ABATER_ICMS_DESONERADO, 1) AS FLAG_NAO_ABATER_ICMS_DESONERADO,
        ISNULL (EST_NF_SAI_IT.VLR_CUSTO_PROD_ORIG, 0) AS VLR_CUSTO_PROD_ORIG,
		EST_NF_SAI_IT.CD_IT,
		ISNULL(EST_NF_SAI.FLAG_VLR_ICMS_DESONERADO_DESCONTO,0)		AS FLAG_VLR_ICMS_DESONERADO_DESCONTO
    FROM
        dbo.EST_NF_SAI_IT INNER JOIN dbo.EST_NF_SAI ON 
	dbo.EST_NF_SAI_IT.CD_EMP = dbo.EST_NF_SAI.CD_EMP AND
            dbo.EST_NF_SAI_IT.CD_FILIAL = dbo.EST_NF_SAI.CD_FILIAL AND
            dbo.EST_NF_SAI_IT.CD_NF = dbo.EST_NF_SAI.CD_NF
        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_PROD ON 
    dbo.EST_NF_SAI_IT.CD_EMP = dbo.EST_PROD.CD_EMP AND
            dbo.EST_NF_SAI_IT.CD_PROD = dbo.EST_PROD.CD_PROD
        LEFT OUTER JOIN dbo.EST_NF_SAI_PED_VD ON 
    dbo.EST_NF_SAI.CD_EMP = dbo.EST_NF_SAI_PED_VD.CD_EMP AND
            dbo.EST_NF_SAI.CD_FILIAL = dbo.EST_NF_SAI_PED_VD.CD_FILIAL AND
            dbo.EST_NF_SAI.CD_NF = dbo.EST_NF_SAI_PED_VD.CD_NF
        LEFT JOIN dbo.EST_NF_ENT_EST_NF_SAI_TRANSF ON 
	dbo.EST_NF_SAI.CD_FILIAL = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG AND
            dbo.EST_NF_SAI.CD_EMP = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND
            dbo.EST_NF_SAI.CD_NF = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI
        LEFT OUTER JOIN dbo.EST_NF_ENT ON
    dbo.EST_NF_ENT.CD_EMP = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND
            dbo.EST_NF_ENT.CD_NF = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT AND
            dbo.EST_NF_ENT.CD_FILIAL = dbo.EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST
        INNER JOIN EST_NF_SERIE ON
	EST_NF_SAI.CD_EMP = EST_NF_SERIE.CD_EMP AND
            EST_NF_SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
        LEFT OUTER JOIN EST_NF_SAI_NFE ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_NFE.CD_EMP AND
            EST_NF_SAI.CD_FILIAL = EST_NF_SAI_NFE.CD_FILIAL AND
            EST_NF_SAI.CD_NF = EST_NF_SAI_NFE.CD_NF

    WHERE     
	dbo.GLB_MOV_OP.DEST_ORIG = 2
