/****** OBJECT:  VIEW [DBO].[V_EST_NF_SAI_CAB]    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_VENDAS_DEVOLUCAO]'))
DROP VIEW [DBO].[V_EST_NF_SAI_VENDAS_DEVOLUCAO]
GO
GO
GO

CREATE VIEW [DBO].[V_EST_NF_SAI_VENDAS_DEVOLUCAO]  
AS  

SELECT 
       E.CD_EMP
     , E.CD_FILIAL
     , V.CD_NF
     , S.NF_NF
     , S.CD_CLI
     , S.CD_NF_SERIE
     , ET.NR_NF
     , ES.CD_NF_ENT
     , E.CD_PROD
     , E.VLR_IT
     , CASE WHEN E.QT_IT > 0 THEN E.VLR_TOT_IT / E.QT_IT
           ELSE E.VLR_IT
       END VLR_IT_DEV_LIQ
     , E.VLR_TOT_IT - E.VLR_DESC VLR_TOT_IT
     , E.QT_IT
     , S.DT_EMI_NF DT_VD
     , E.DT_ENT DT_DEVO
     , S.CD_VEND
     , (CASE WHEN E.QT_IT > 0 THEN E.VLR_TOT_IT / E.QT_IT
            ELSE E.VLR_IT
        END) VLR_IT_BRUTO
     , E.VLR_DESC VLR_DESC_IT
     , E.VLR_IT VLR_PMC_IT
     , I.CD_IT
     , ET.DT_CAD
     , I.PERC_COMI
     , 0 VLR_DESC_VERBA
     , 0 VLR_OFERTA
     , 0 TIPO_DESCONTO
     , E.VLR_CUSTO_MD VLR_CUSTO_MEDIO
     , 0 PERC_DESC_VERBA
     , E.VLR_PRC_CUSTO VLR_CUSTO
     , 0 CD_ORIG_VENDA
     , 0 VLR_COMISSAO_FABRIC
     , 0 CD_MT_DEV_VD
     , E.ALIQ_ICMS PERC_ICMS
     , '' DS_PERC_COMIS
     , '' DS_VLR_COMIS
     , E.VLR_BASE_PIS
     , E.VLR_PIS
     , E.PERC_ALIQ_PIS
     , E.VLR_BASE_COFINS
     , E.VLR_COFINS
     , E.PERC_ALIQ_COFINS
     , E.CST_PIS
     , E.CST_COFINS
     , E.PIS_COFINS_RECALCULADO
     , 0 FLAG_CANCELAR_RECBTO
     , 0 FLAG_NAO_PERMITIR_DEVOLUCAO
     , 0 FLAG_PRODUTO_TAXA_ENTREGA
     , S.HR_EMI_NF
     , S.CD_USU
     , E.VLR_CUSTO_GER
     , S.UUID
     , I.PIS_COFINS
     , E.VLR_ICMS_IT
FROM 
     EST_NF_SAI_VENDAS V
     INNER JOIN EST_NF_SAI S ON V.CD_EMP = S.CD_EMP
                                AND V.CD_FILIAL = S.CD_FILIAL
                                AND V.CD_NF = S.CD_NF
     INNER JOIN EST_NF_SAI_IT I ON S.CD_EMP = I.CD_EMP
                                   AND S.CD_FILIAL = I.CD_FILIAL
                                   AND S.DT_EMI_NF = I.DT_EMI_NF
                                   AND S.CD_NF = I.CD_NF
     INNER JOIN EST_NF_SAI_NF_ENT ES ON S.CD_EMP = ES.CD_EMP
                                        AND S.CD_FILIAL = ES.CD_FILIAL
                                        AND S.CD_NF = ES.CD_NF_SAI
     INNER JOIN EST_NF_ENT ET ON ES.CD_EMP = ET.CD_EMP
                                 AND ES.CD_FILIAL = ET.CD_FILIAL
                                 AND ES.CD_NF_ENT = ET.CD_NF
                                 AND ET.STS_NF = 0
     INNER JOIN EST_NF_ENT_IT E ON ES.CD_EMP = E.CD_EMP
                                   AND ES.CD_FILIAL = E.CD_FILIAL
                                   AND ES.CD_NF_ENT = E.CD_NF
                                   AND I.CD_PROD = E.CD_PROD
WHERE I.CD_EMP = 1;