/****** Object:  View [dbo].[V_EST_PROD_TBL_DESC_TIPOS]    Script Date: 12/10/2013 15:41:31 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_TBL_DESC_QTDE_TIPOS]'))
DROP VIEW [dbo].[V_EST_PROD_TBL_DESC_QTDE_TIPOS]
GO
GO
GO
--SELECT * FROM V_EST_PROD_TBL_DESC_QTDE_TIPOS
CREATE VIEW V_EST_PROD_TBL_DESC_QTDE_TIPOS AS 

SELECT
  tb.CD_EMP,      
  f.CD_FILIAL,     
  tb.CD_TBL_DESC_QTDE,          
  tb.DS_TBL_DESC_QTDE,      
  tb.DT_INI,
  tb.DT_FIM,
  tb.CD_TP_DESCONTO,
  g.DS_TP_DESCONTO,
  tb.CD_USU,
  tb.DT_CAD,
  tb.DT_ULT_ALT,
  tb.STS_TBL_DESC,
  tb.ST_PRORROG,
  tb.CD_PROD,
  tb.DS_HIERARQUIA,
  tb.PERC_DESC,
  tb.PERC_DESC_MAX,
  tb.SOBREPOR_OFERTA,
  tb.PRECO_ESPECIFICO,
  tb.TP_DESCONTO
FROM
(SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       A.CD_PROD,
       'LINHA' AS DS_HIERARQUIA,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_LINHA L ON D.CD_EMP = L.CD_EMP
                                                          AND D.CD_TBL_DESC_QTDE = L.CD_TBL_DESC_QTDE
     INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON L.CD_EMP = A.CD_EMP
                                                    AND L.CD_ARV_MERC_LINHA = A.CD_ARV_MERC_LINHA
UNION ALL
SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       A.CD_PROD,
       'CATEGORIA' AS DS_HIERARQUIA,
       ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_CATEGORIA C ON D.CD_EMP = C.CD_EMP
                                                              AND D.CD_TBL_DESC_QTDE = C.CD_TBL_DESC_QTDE
     INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON C.CD_EMP = A.CD_EMP
                                                    AND C.CD_ARV_MERC_CATEG = A.CD_ARV_MERC_CATEG
UNION ALL
SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       P.CD_PROD,
       'FABRICANTE' AS DS_HIERARQUIA,
       ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_PROD_FABRIC F ON D.CD_EMP = F.CD_EMP
                                                       AND D.CD_TBL_DESC_QTDE = F.CD_TBL_DESC_QTDE
     INNER JOIN EST_PROD P ON F.CD_EMP = P.CD_EMP
                              AND F.CD_FABRIC = P.CD_FABRIC
UNION ALL
SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       P.CD_PROD,
       'MARCA' AS DS_HIERARQUIA,
       ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_MC M ON D.CD_EMP = M.CD_EMP
                                              AND D.CD_TBL_DESC_QTDE = M.CD_TBL_DESC_QTDE
     INNER JOIN EST_PROD P ON M.CD_EMP = P.CD_EMP
                              AND M.CD_MC = P.CD_MC
UNION ALL
SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       A.CD_PROD,
       'FAMILIA' AS DS_HIERARQUIA,
       ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_ARV_MERC_FAMILIA E ON D.CD_EMP = E.CD_EMP
                                                            AND D.CD_TBL_DESC_QTDE = E.CD_TBL_DESC_QTDE
     INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON E.CD_EMP = A.CD_EMP
                                                    AND E.CD_ARV_MERC_FAMILIA = A.CD_ARV_MERC_FAMILIA
UNION ALL
SELECT D.CD_EMP,
       D.CD_TBL_DESC_QTDE,
       D.DS_TBL_DESC_QTDE,
       D.DT_INI,
       D.DT_FIM,
       D.CD_TP_DESCONTO,
       D.CD_USU,
       D.DT_CAD,
       D.DT_ULT_ALT,
       D.STS_TBL_DESC,
       D.ST_PRORROG,
       T.CD_PROD,
       'PRODUTO' AS DS_HIERARQUIA,
	   ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC,
	  ((1-(d.QTDE_PAGUE /convert(money,d.QTDE_LEVE)))*100.) PERC_DESC_MAX,
       0 AS SOBREPOR_OFERTA,
       0 AS PRECO_ESPECIFICO,
       2 AS TP_DESCONTO
FROM EST_PROD_TBL_DESC_QTDE D
     INNER JOIN EST_PROD_TBL_DESC_QTDE_EST_PROD T ON D.CD_EMP = T.CD_EMP
                                                AND D.CD_TBL_DESC_QTDE = T.CD_TBL_DESC_QTDE											
) tb
	INNER JOIN  EST_PROD_TBL_DESC_QTDE_PRC_FILIAL  f ON
	tb.CD_EMP = f.CD_EMP AND
	tb.CD_TBL_DESC_QTDE = f.CD_TBL_DESC_QTDE
	INNER JOIN GLB_TP_DESCONTO g ON
	tb.CD_TP_DESCONTO = g.CD_TP_DESCONTO
UNION ALL												
SELECT       
	EST_PROD_PRECO_DESC_QTDE_CPL.CD_EMP,
	EST_PROD_PRECO_DESC_QTDE_PRC_FILIAL.CD_FILIAL,
	EST_PROD_PRECO_DESC_QTDE.CD_PRECO_DESC_QTDE, 
	EST_PROD_PRECO_DESC_QTDE.NM_PROMOCAO, 
	EST_PROD_PRECO_DESC_QTDE.DT_INICIAL, 
	EST_PROD_PRECO_DESC_QTDE.DT_FINAL, 
	14 AS CD_TP_DESCONTO,
	'DESCONTO QUANTIDADE ANTIGO' AS DS_TP_DESCONTO,
    EST_PROD_PRECO_DESC_QTDE.CD_USU, 
	EST_PROD_PRECO_DESC_QTDE.DT_CAD, 
	EST_PROD_PRECO_DESC_QTDE.DT_ULT_ALT, 
	1 AS STS_TBL_DESC,
	0 AS ST_PRORROG,
	EST_PROD_PRECO_DESC_QTDE_CPL.CD_PROD,
	'MODELO ANTIGO' AS DS_HIERARQUIA,
	EST_PROD_PRECO_DESC_QTDE.PERC_DESC, 
    0 AS PERC_DESC_MAX, 
    0 AS SOBREPOR_OFERTA,
    0 AS PRECO_ESPECIFICO,
    1 AS TP_DESCONTO
FROM            
	EST_PROD_PRECO_DESC_QTDE INNER JOIN EST_PROD_PRECO_DESC_QTDE_CPL ON 
	EST_PROD_PRECO_DESC_QTDE.CD_PRECO_DESC_QTDE = EST_PROD_PRECO_DESC_QTDE_CPL.CD_PRECO_DESC_QTDE	
	INNER JOIN EST_PROD_PRECO_DESC_QTDE_PRC_FILIAL ON
	EST_PROD_PRECO_DESC_QTDE.CD_PRECO_DESC_QTDE = EST_PROD_PRECO_DESC_QTDE_PRC_FILIAL.CD_PRECO_DESC_QTDE	