/****** Object:  View [dbo].[V_SPED_PDV_VD]    Script Date: 04/11/2013 10:05:53 ******/
IF EXISTS
(
    SELECT *
    FROM sys.views
    WHERE object_id = OBJECT_ID(N'[dbo].[V_SPED_PDV_VD]')
)
    DROP VIEW [dbo].[V_SPED_PDV_VD];
GO
--  SELECT TOP 2 * FROM [V_SPED_PDV_VD]
CREATE VIEW [dbo].[V_SPED_PDV_VD]
AS
     SELECT PDV_VD.CD_VD AS CD_DOC 
          , PDV_VD.CD_CLI AS CD_ORIG
          , '0' AS COD_MOD
          , PDV_VD.CD_EMP
          , NR_ECF AS NR_DOC
          , PDV_VD.DT_VD AS DT_EMI_NF
          , PDV_VD.DT_VD AS DT_ENT_SAI
          , PDV_VD.VLR_TOT_PROD AS VLR_NF
          , 0 AS IND_PAGTO
          , PDV_VD.VLR_DESC
          , (TOT_IT - VLR_DESC_TOT_RAT) AS VLR_MERC
          , PDV_VD.CD_FILIAL
          , RC_CLI.CGC_CPF
          , ST_VD AS STS_NF
          , ISNULL(RC_DEB_PDV_VD.CD_EMP, 0) AS POSSUI_DUPL
          , 0 TP_MOV
          , 0 TP_MOV_OP_TRANSA
          , 0 DEST_ORIG
          , 'CLI' AS TIPO_PART
          , PDV_VD_IT.CD_IT AS NUM_ITEM
          , PDV_VD_IT.CD_PROD AS COD_ITEM
          , EST_PROD_CPL.TP_PROD AS TIPO_ITEM
          , EST_PROD.DS_PROD
          , PDV_VD_IT.QT_IT
          , PDV_VD_IT.VLR_IT AS VLR_ITEM
          , PDV_VD_IT.CD_TRIB_FC AS CST_ICMS
          , (CASE
                 WHEN PDV_VD_IT.CD_TRIB_FC = '010'
                      OR PDV_VD_IT.CD_TRIB_FC = '060'
                 THEN '5405'
                 ELSE '5102'
             END) AS CFOP
          , CASE
                WHEN PDV_VD_IT.TX_ICMS_IT > 0
                THEN TOT_IT
                ELSE 0
            END AS VLR_BASE_ICMS
          , PDV_VD_IT.TX_ICMS_IT
          , (PDV_VD_IT.TX_ICMS_IT * PDV_VD_IT.TOT_IT) / 100 AS VLR_ICMS_IT
          , ISNULL(PDV_VD_IT.CST_PIS, 0) CST_PIS
          , ISNULL(PDV_VD_IT.CST_COFINS, 0) CST_COFINS
          , ISNULL(PDV_VD_IT.VLR_BASE_PIS, 0) VLR_BASE_PIS
          , ISNULL(PDV_VD_IT.PERC_ALIQ_PIS, 0) PERC_ALIQ_PIS
          , ISNULL(PDV_VD_IT.VLR_PIS, 0) VLR_PIS
          , ISNULL(PDV_VD_IT.VLR_BASE_COFINS, 0) VLR_BASE_COFINS
          , ISNULL(PDV_VD_IT.PERC_ALIQ_COFINS, 0) PERC_ALIQ_COFINS
          , ISNULL(PDV_VD_IT.VLR_COFINS, 0) VLR_COFINS
          , CASE
                WHEN PDV_VD_IT.ST_IT = 1
                     AND PDV_VD.ST_VD = 0
                     AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                     AND PDV_VD_IT.TX_ICMS_IT = 5
                THEN ISNULL(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT, 0)
                ELSE 0
            END VLR_BASE_ISSQN
          , CASE
                WHEN PDV_VD_IT.ST_IT = 1
                     AND PDV_VD.ST_VD = 0
                     AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                     AND PDV_VD_IT.TX_ICMS_IT = 5
                THEN(ISNULL(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT, 0)) * PDV_VD_IT.TX_ICMS_IT / 100
                ELSE 0
            END VLR_ISSQN
          , CASE
                WHEN PDV_VD_IT.ST_IT = 1
                     AND PDV_VD.ST_VD = 0
                     AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                     AND PDV_VD_IT.TX_ICMS_IT = 5
                THEN PDV_VD_IT.TX_ICMS_IT
                ELSE 0
            END ALIQ_ISSQN
          , PDV_VD.CD_CLI AS COD_PART
          , 1 AS COD_IMP_ALMOX
          , PDV_VD_IT.LISTA_PROD
          , PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT AS VALOR_ITEM
          , PRC_FILIAL.FLAG_OPERADOR_LOG
          , PDV_VD_IT_LOTE.QTDE_PROD
          , ISNULL(EST_PROD_CPL_LOTE.NR_LOTE, 0) NR_LOTE
          , EST_PROD_CPL_LOTE.DT_VALID
          , EST_PROD_CPL_LOTE.DT_FABR
          , EST_PROD_PRECO.VLR_TABELA
          , CASE
                WHEN EST_PROD.CTR_PMC = 1
                THEN 1
                ELSE 0
            END AS PROD_MEDICAM
          , (CASE
                 WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'S'
                 THEN 0   -- siglas: E - etico,   S - similar, G - Generico |0- Similar; 1- Genrico; 2- tico;
                 WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'G'
                 THEN 1
                 WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'E'
                 THEN 2
                 ELSE 0
             END) TIPO_MEDIC
          , RC_CLI.RZ_CLI AS NOME_PART
          , RC_CLI.END_CLI AS ENDERECO
          , RC_CLI.BAIRRO
          , ISNULL(RC_CLI.NUMERO, 0) NUMERO
          , RC_CLI.INSC_EST
          , ISNULL(GLB_MUNICIPIO.COD_MUNICIPIO_SINTEGRA, 0) AS COD_IBGE
          , EST_PROD_CPL.TP_PROD
          , ISNULL(
                  (
                      SELECT TOP (1) CD_BARRA
                      FROM EST_PROD_CD_BARRA
                      WHERE CD_EMP = PDV_VD_IT.CD_EMP
                            AND CD_PROD = PDV_VD_IT.CD_PROD
                      ORDER BY CD_BARRA
                  ), '') AS COD_BARRA
          , EST_PROD.NR_NCM AS COD_NCM
          , ROUND(PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT * PDV_VD_IT.TX_DESC / 100, 2) - VLR_DESC_TOT_RAT AS VLR_DESC_IT
          , ISNULL(V_PDV_VD_IMPOSTOS_PIS_COFINS.PIS_NF, 0) PIS_NF
          , ISNULL(V_PDV_VD_IMPOSTOS_PIS_COFINS.COFINS_NF, 0) COFINS_NF
          , PDV_VD_IT.ST_IT
          , PDV_VD_IT.TIPO_IMPOSTO
          , ISNULL(PDV_VD.FLAG_NFCE, 0) FLAG_NFCE
		, ISNULL( NR_CEST ,'') NR_CEST
     FROM
          PDV_VD
          INNER JOIN PDV_VD_IT
          ON PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP
             AND PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND PDV_VD.CD_VD = PDV_VD_IT.CD_VD
          INNER JOIN PDV_CX
          ON PDV_VD.CD_EMP = PDV_CX.CD_EMP
             AND PDV_VD.CD_FILIAL = PDV_CX.CD_FILIAL
             AND PDV_VD.CD_CX = PDV_CX.CD_CX
          INNER JOIN EST_PROD_CPL
          ON EST_PROD_CPL.CD_EMP = PDV_VD_IT.CD_EMP
             AND EST_PROD_CPL.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND EST_PROD_CPL.CD_PROD = PDV_VD_IT.CD_PROD
          INNER JOIN PRC_FILIAL
          ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
             AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
          INNER JOIN EST_PROD
          ON PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP
             AND PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD
          INNER JOIN RC_CLI
          ON PDV_VD.CD_CLI = RC_CLI.CD_CLI
          LEFT JOIN EST_PROD_EST_ARV_MERCADOLOGICA
          ON EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
             AND EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
          LEFT JOIN EST_ARV_MERC_LINHA
          ON EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP
             AND EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
          LEFT JOIN PDV_VD_IT_LOTE
          ON PDV_VD_IT_LOTE.Cd_Emp = PDV_VD_IT.Cd_Emp
             AND PDV_VD_IT_LOTE.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND PDV_VD_IT_LOTE.CD_VD = PDV_VD_IT.CD_VD
          LEFT JOIN EST_PROD_CPL_LOTE
          ON PDV_VD_IT_LOTE.Cd_Emp = EST_PROD_CPL_LOTE.Cd_Emp
             AND PDV_VD_IT_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
             AND PDV_VD_IT_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
          LEFT JOIN EST_PROD_PRECO
          ON EST_PROD_PRECO.CD_EMP = PDV_VD_IT.CD_EMP
             AND EST_PROD_PRECO.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND EST_PROD_PRECO.CD_PROD = PDV_VD_IT.CD_PROD
          LEFT JOIN RC_DEB_PDV_VD
          ON RC_DEB_PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP
             AND RC_DEB_PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND RC_DEB_PDV_VD.CD_VD = PDV_VD_IT.CD_VD
          LEFT JOIN GLB_CID
          ON RC_CLI.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_CID_GLB_MUNICIPIO
          ON GLB_CID_GLB_MUNICIPIO.CD_CID = GLB_CID.CD_CID
          LEFT JOIN GLB_MUNICIPIO
          ON GLB_MUNICIPIO.CD_MUNICIPIO = GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO
          LEFT JOIN V_PDV_VD_IMPOSTOS_PIS_COFINS
          ON V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_EMP = PDV_VD_IT.CD_EMP
             AND V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_FILIAL = PDV_VD_IT.CD_FILIAL
             AND V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_VD = PDV_VD_IT.CD_VD;
GO