/****** Object:  View [dbo].[V_PRECIFICACAO_CONTROLE_ENTRADA]    Script Date: 06/19/2014 11:10:23 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_PRECIFICACAO_CONTROLE_ENTRADA]'))
	BEGIN
		DROP VIEW [dbo].[V_PRECIFICACAO_CONTROLE_ENTRADA]
	END
	
GO
CREATE VIEW [DBO].[V_PRECIFICACAO_CONTROLE_ENTRADA]   
AS  
      
    SELECT   
     ENT.CD_EMP    ,  
           ENT.CD_GRUPO  ,  
           IT.CD_PROD    ,  
           IT.DS_PROD    ,  
           ENT.CD_FILIAL ,  
           NF.DT_ENT     ,  
           NF.NR_NF      ,  
           COALESCE ( (  
                         CASE  
                                WHEN FRN.CD_FORN IS NULL  
                                THEN IT.VLR_TOT_IT / IT.QT_IT  
                                ELSE OPL.VLR_ITEM  
                         END ) , 0) AS VLR_COMPRA ,  
           COALESCE ( (  
                         CASE  
                                WHEN FRN.CD_FORN IS NULL  
                                THEN 0  
                                ELSE  
                                       CASE  
                                              WHEN TRIB.ICMS = 1  
                                              THEN OPL.VLR_ITEM + (OPL.VLR_COMPRA * OPL.PERC_IPI / 100) - (OPL.VLR_ITEM * OPL.ALIQ_ICMS_COMPRA / 100)  
                                              ELSE OPL.VLR_ITEM + (((OPL.VLR_COMPRA + (OPL.VLR_COMPRA * OPL.PERC_IPI / 100) + ((OPL.VLR_COMPRA + (OPL.VLR_COMPRA * OPL.PERC_IPI / 100)) * CPL.VLR_PAUTA_IVA / 100)) * CPL.PERC_ICMS_EST / 100) - (OPL.VLR_ITEM 
* OPL.ALIQ_ICMS_COMPRA / 100))  
                                       END  
                         END ) , 0) AS SIMULADO_COF ,  
           IT.VLR_PRC_CUSTO                         ,  
           ISNULL(IT.VLR_CUSTO_GER,0)  AS VLR_CUSTO_GER,  
           COALESCE (PRC.VLR_LISTA, 0) AS VLR_LISTA ,  
           CASE  
                  WHEN ISNULL (PRC.VLR_BASE, 0) = 0  
                  THEN VLR.VLR_TABELA  
                  ELSE ISNULL (PRC.VLR_BASE, 0)  
           END AS VLR_BASE ,  
           (  
                  CASE  
                         WHEN IT.PIS_COFINS = 0  
                         THEN 0  
                         ELSE  
                               ( SELECT ISNULL( SUM(PERC_IMP),0) AS PIS_COFINS  --select * from glb_impostos
                               FROM    GLB_IMPOSTOS  
                               WHERE   IT.PIS_COFINS = 1  
                               AND  
                                       (  
                                               TIPO_IMPOSTO = 1  
                                       OR      TIPO_IMPOSTO = 2  
                                       
                                       )  
                                       AND FLAG_NAO_COMPOR_PRECIFICACAO = 0
                               )  
                  END )      AS PIS_COFINS ,  
           CPL.PERC_ICMS_EST AS ICMS_VENDA ,  
           PPL.DS_SIGLA                    ,  
           PROD.CD_FABRIC                  ,  
           ARV.CD_ARV_MERC_FAMILIA         ,  
           ARV.CD_ARV_MERC_CATEG           ,  
           CTG.CD_ARV_MERC_DEPTO           ,  
           ARV_FAMILIA.DS_ARV_MERC_FAMILIA ,  
           (  
                  CASE  
                         WHEN COALESCE(PROD.PERC_MARGEM_FIXA, 0) = 0  
                         THEN MRG.PERC_MARGEM_MIN  
                         ELSE PROD.PERC_MARGEM_FIXA  
                  END ) AS MMIN ,  
           (  
                  CASE  
                         WHEN COALESCE(PROD.PERC_MARGEM_FIXA, 0) = 0  
                         THEN MRG.PERC_MARGEM_MAX  
                         ELSE PROD.PERC_MARGEM_FIXA  
                  END ) AS MMAX                                ,  
           PPL.FORMULA_PRECIFICACAO                            ,  
           COALESCE (PROD.VLR_PRECO_FIXO, 0) AS VLR_PRECO_FIXO ,  
           COALESCE (PROD.DT_PRECO_FIXO, 0)  AS DT_PRECO_FIXO  ,  
           COALESCE (  
                       ( SELECT SUM(A.VLR_VERBA)                           AS VERBA  
                       FROM    dbo.EST_PROD_PRECIFICACAO_VERBAS_IT         AS A  
                               INNER JOIN dbo.EST_PROD_PRECIFICACAO_VERBAS AS B  
                               ON      A.CD_EMP   = B.CD_EMP  
                               AND     A.CD_VERBA = B.CD_VERBA  
                       WHERE   (  
                                       A.CD_EMP = ENT.CD_EMP  
                               )  
                AND  
                               (  
                                       A.CD_PROD = ENT.CD_PROD  
                               )  
                       AND  
                               (  
                                       A.STS_VERBA = 0  
                               )  
                       AND  
                               (  
                                       GETDATE() BETWEEN B.DT_INI_VERBA AND     B.DT_VENC_VERBA  
                               )  
                       )  
                     , 0) AS                VERBA ,  
           ( SELECT COUNT(A.CD_TABLOIDE)            AS TABLOIDE  
           FROM    dbo.EST_PROD_TABLOIDE_CPL        AS A  
                   INNER JOIN dbo.EST_PROD_TABLOIDE AS B  
                   ON      A.CD_EMP      = B.CD_EMP  
                   AND     A.CD_TABLOIDE = B.CD_TABLOIDE  
           WHERE   (  
                           A.CD_EMP = ENT.CD_EMP  
                   )  
           AND  
                   (  
                           A.CD_PROD = ENT.CD_PROD  
                   )  
           AND  
                   (  
                           GETDATE() BETWEEN B.DT_INICIO AND     B.DT_FIM  
                   )  
           )                                            AS TABLOIDE ,  
           COALESCE (BLQ.CD_PROD, 0)                    AS BLOQUEIO,  
     PRC.DT_PRECO_BASE  
        FROM   dbo.EST_PRECIFICACAO_GRP_PRECO_EST_NF_ENT_IT AS ENT  
               INNER JOIN dbo.EST_NF_ENT                    AS NF  
               ON     NF.DT_ENT     > DATEADD(DAY, - 180, GETDATE())  
               AND    ENT.CD_EMP    = NF.CD_EMP  
               AND    ENT.CD_FILIAL = NF.CD_FILIAL  
               AND    ENT.CD_NF     = NF.CD_NF  
               INNER JOIN dbo.EST_PROD_EST_ARV_MERCADOLOGICA AS ARV  
               ON     ENT.CD_EMP  = ARV.CD_EMP  
               AND    ENT.CD_PROD = ARV.CD_PROD  
               INNER JOIN dbo.EST_ARV_MERC_LINHA AS ARV_LINHA  
               ON     ARV.CD_EMP            = ARV_LINHA.CD_EMP  
               AND    ARV.CD_ARV_MERC_LINHA = ARV_LINHA.CD_ARV_MERC_LINHA  
               INNER JOIN dbo.EST_ARV_MERC_FAMILIA AS ARV_FAMILIA  
               ON     ARV.CD_EMP              = ARV_FAMILIA.CD_EMP  
               AND    ARV.CD_ARV_MERC_FAMILIA = ARV_FAMILIA.CD_ARV_MERC_FAMILIA  
               INNER JOIN dbo.EST_PROD AS PROD  
               ON     PROD.STS_PROD = 0  
               AND    ENT.CD_EMP    = PROD.CD_EMP  
               AND    ENT.CD_PROD   = PROD.CD_PROD  
               INNER JOIN dbo.EST_PROD_PRECO AS VLR  
               ON     ENT.CD_EMP    = VLR.CD_EMP  
               AND    ENT.CD_FILIAL = VLR.CD_FILIAL  
               AND    ENT.CD_PROD   = VLR.CD_PROD  
               INNER JOIN dbo.EST_ARV_MERC_CATEGORIA AS CTG  
               ON     ARV.CD_EMP            = CTG.CD_EMP  
               AND    ARV.CD_ARV_MERC_CATEG = CTG.CD_ARV_MERC_CATEG  
               LEFT JOIN dbo.EST_ARV_MERC_CATEGORIA_PRECIFICACAO_MARGENS AS MRG  
               ON     CTG.CD_EMP            = MRG.CD_EMP  
               AND    ENT.CD_GRUPO          = MRG.CD_GRUPO  
               AND    CTG.CD_ARV_MERC_CATEG = MRG.CD_ARV_MERC_CATEG  
               INNER JOIN dbo.EST_ARV_MERC_PAPEL_PRECIFICACAO_FORMULA AS PPL  
               ON     ARV.CD_EMP            = PPL.CD_EMP  
               AND    ARV.CD_ARV_MERC_PAPEL = PPL.CD_ARV_MERC_PAPEL  
               LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO AS PRF  
               ON     ENT.CD_EMP   = PRF.CD_EMP  
               AND    ENT.CD_GRUPO = PRF.CD_GRUPO  
               AND    ENT.CD_PROD  = PRF.CD_PROD  
               LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO_PRECO AS PRC  
               ON     ENT.CD_EMP   = PRC.CD_EMP  
               AND    ENT.CD_GRUPO = PRC.CD_GRUPO  
               AND    ENT.CD_PROD  = PRC.CD_PROD  
               INNER JOIN dbo.EST_NF_ENT_IT AS IT  
               ON     ENT.CD_EMP    = IT.CD_EMP  
               AND    ENT.CD_FILIAL = IT.CD_FILIAL  
               AND    ENT.CD_NF     = IT.CD_NF  
               AND    ENT.CD_PROD   = IT.CD_PROD  
               INNER JOIN dbo.EST_TRIB AS TRIB  
               ON     IT.CD_TRIB = TRIB.CD_TRIB  
               LEFT OUTER JOIN  
                      ( SELECT A.CD_EMP   ,  
                              A.CD_FILIAL ,  
                              A.CD_FORN  
                      FROM    dbo.PRC_FILIAL_PG_FORN    AS A  
                              INNER JOIN dbo.PRC_FILIAL AS B  
                              ON      A.CD_EMP    = B.CD_EMP  
                              AND     A.CD_FILIAL = B.CD_FILIAL  
                      WHERE   (  
                                      B.FLAG_OPERADOR_LOG = 1  
                              )  
                      ) AS FRN  
               ON     NF.CD_EMP  = FRN.CD_EMP  
               AND    NF.CD_FORN = FRN.CD_FORN  
               LEFT OUTER JOIN  
                      ( SELECT A.CD_EMP                                  ,  
                              A.CD_FILIAL                                ,  
                              A.CD_PROD                                  ,  
                              C.VLR_IT               AS VLR_COMPRA       ,  
                              C.VLR_TOT_IT / C.QT_IT AS VLR_ITEM         ,  
                              C.ALIQ_ICMS            AS ALIQ_ICMS_COMPRA ,  
                              C.PERC_IPI  
                      FROM    dbo.EST_PROD_ENT_OPERADOR_LOG AS A  
                              INNER JOIN dbo.EST_NF_ENT     AS B  
                              ON      B.DT_ENT    > DATEADD(DAY, - 180, GETDATE())  
                              AND     A.CD_EMP    = B.CD_EMP  
                              AND     A.CD_FILIAL = B.CD_FILIAL  
                              AND     A.CD_NF     = B.CD_NF  
                              INNER JOIN dbo.EST_NF_ENT_IT AS C  
                              ON      A.CD_EMP    = C.CD_EMP  
                              AND     A.CD_FILIAL = C.CD_FILIAL  
                              AND     A.CD_NF     = C.CD_NF  
                              AND     A.CD_PROD   = C.CD_PROD  
                      ) AS OPL  
               ON     FRN.CD_EMP    = OPL.CD_EMP  
               AND    FRN.CD_FILIAL = OPL.CD_FILIAL  
               AND    ENT.CD_PROD   = OPL.CD_PROD  
               LEFT OUTER JOIN  
                      ( SELECT  A.CD_EMP   ,  
                               A.CD_FILIAL ,  
                               A.CD_PROD   ,  
                               MAX(  
                                        CASE  
                                                 WHEN A.TP_TRANSA = 0  
                                                 THEN A.VLR_PAUTA_IVA  
                                                 ELSE 0  
                                        END ) AS VLR_PAUTA_IVA ,  
                               MAX(  
                                        CASE  
                                                 WHEN A.TP_TRANSA = 1  
                                                 THEN (  
                                                                   CASE  
                                                                            WHEN  
                                                                                     (  
                                                                                              ISNULL(B.SUBS_TRIB_ANT, 0) + ISNULL(B.SUBS_TRIB, 0) + ISNULL(B.ISENTO, 0)  
                                                                                     )  
                                                                                     > 0  
                                                                            THEN 0  
                                                                            ELSE A.PERC_ICMS_EST  
                                                                   END )  
                                                 ELSE 0  
                                        END )            AS PERC_ICMS_EST  
                      FROM DBO.EST_PROD_CPL_TRIB_EST AS A  
                               INNER JOIN EST_TRIB       AS B  
                               ON       A.CD_TRIB = B.CD_TRIB  
                      WHERE    A.TP_MOV           = 0  
                      GROUP BY A.CD_EMP    ,  
                               A.CD_FILIAL ,  
                               A.CD_PROD  
                      ) AS CPL  
               ON     IT.CD_EMP    = CPL.CD_EMP  
               AND    IT.CD_FILIAL = CPL.CD_FILIAL  
               AND    IT.CD_PROD   = CPL.CD_PROD  
               LEFT OUTER JOIN dbo.EST_PROD_PRECIFICACAO_BLOQUEIOS AS BLQ  
               ON     ENT.CD_EMP   = BLQ.CD_EMP  
               AND    ENT.CD_GRUPO = BLQ.CD_GRUPO  
               AND    ENT.CD_PROD  = BLQ.CD_PROD  
               INNER JOIN PRC_FILIAL AS FLL  
               ON     ENT.CD_EMP    = FLL.CD_EMP  
               AND    ENT.CD_FILIAL = FLL.CD_FILIAL  
               LEFT OUTER JOIN  
                      ( SELECT CD_EMP ,  
                              VALOR  
                      FROM    PRC_EMP_CONFIG  
                      WHERE   CD_CHAVE = 'PRECIFICACAO_SOMENTE_MTZ_OL'  
                      ) AS CFG  
               ON     ENT.CD_EMP = CFG.CD_EMP  
        WHERE  NOT  
               (  
                      FLL.FILIAL_MTZ         = 0  
               AND    FLL.FLAG_OPERADOR_LOG  = 0  
               AND    COALESCE(CFG.VALOR, 0) = 1  
               )  
        AND    ARV_LINHA.FLAG_PARTIC_PRECIFICACAO = 1  
          