IF EXISTS (SELECT * FROM SYS.VIEWS WHERE NAME = 'V_EST_PROD_TBL_DESC_CADASTRO' )
	BEGIN
		DROP VIEW V_EST_PROD_TBL_DESC_CADASTRO
	END
GO
--SELECT * FROM V_EST_PROD_TBL_DESC_CADASTRO
CREATE VIEW [dbo].[V_EST_PROD_TBL_DESC_CADASTRO] AS 
--Desconto por categoria
SELECT
	ARV.CD_PROD,
	DESC_CAT.PERC_DESC,
	ARV.CD_ARV_MERC_LINHA,
	DESC_FIL.CD_FILIAL,
	'CATEGORIA' AS NIVEL,
	CONVERT(VARCHAR(5),GLB_TP_DESCONTO.CD_TP_DESCONTO) + ' - ' + GLB_TP_DESCONTO.DS_TP_DESCONTO AS DS_TP_DESCONTO
FROM
	EST_PROD_TBL_DESC DESCONTO

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL DESC_FIL ON
		DESCONTO.CD_EMP = DESC_FIL.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FIL.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_CATEGORIA DESC_CAT ON
		DESCONTO.CD_EMP = DESC_CAT.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_CAT.CD_TBL_DESC

	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		DESC_CAT.CD_EMP = ARV.CD_EMP
		AND DESC_CAT.CD_ARV_MERC_CATEG = ARV.CD_ARV_MERC_CATEG
	INNER JOIN GLB_TP_DESCONTO ON
	DESCONTO.CD_TP_DESCONTO = GLB_TP_DESCONTO.CD_TP_DESCONTO
WHERE
	DESCONTO.STS_TBL_DESC = 1
	--Aqui restringiu-se os tipos DESCONTO CADASTRO PRODUTO
	AND DESCONTO.CD_TP_DESCONTO=1

UNION 

--Desconto por linha
SELECT
	ARV.CD_PROD,
	DESC_LIN.PERC_DESC,
	ARV.CD_ARV_MERC_LINHA,
	DESC_FIL.CD_FILIAL,
	'LINHA' AS NIVEL,
	CONVERT(VARCHAR(5),GLB_TP_DESCONTO.CD_TP_DESCONTO) + ' - ' + GLB_TP_DESCONTO.DS_TP_DESCONTO AS DS_TP_DESCONTO
FROM
	EST_PROD_TBL_DESC DESCONTO

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL DESC_FIL ON
		DESCONTO.CD_EMP = DESC_FIL.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FIL.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_LINHA DESC_LIN ON
	DESCONTO.CD_EMP = DESC_LIN.CD_EMP
	AND DESCONTO.CD_TBL_DESC = DESC_LIN.CD_TBL_DESC

	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		DESC_LIN.CD_EMP = ARV.CD_EMP
		AND DESC_LIN.CD_ARV_MERC_LINHA = ARV.CD_ARV_MERC_LINHA
	INNER JOIN GLB_TP_DESCONTO ON
	DESCONTO.CD_TP_DESCONTO = GLB_TP_DESCONTO.CD_TP_DESCONTO
WHERE
	DESCONTO.STS_TBL_DESC = 1
	AND DESCONTO.CD_TP_DESCONTO=1

UNION

--Desconto por fabricante
SELECT
	PROD.CD_PROD,
	DESC_FAB.PERC_DESC,
	ARV.CD_ARV_MERC_LINHA,
	DESC_FIL.CD_FILIAL,
	'FABRICANTE' AS NIVEL,
	CONVERT(VARCHAR(5),GLB_TP_DESCONTO.CD_TP_DESCONTO) + ' - ' + GLB_TP_DESCONTO.DS_TP_DESCONTO AS DS_TP_DESCONTO
FROM
	EST_PROD_TBL_DESC DESCONTO

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL DESC_FIL ON
		DESCONTO.CD_EMP = DESC_FIL.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FIL.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_EST_PROD_FABRIC DESC_FAB ON
		DESCONTO.CD_EMP = DESC_FAB.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FAB.CD_TBL_DESC

	INNER JOIN EST_PROD PROD ON
		DESC_FAB.CD_EMP = PROD.CD_EMP
		AND DESC_FAB.CD_FABRIC = PROD.CD_FABRIC

	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		PROD.CD_EMP = ARV.CD_EMP
		AND PROD.CD_PROD = ARV.CD_PROD
	INNER JOIN GLB_TP_DESCONTO ON
	DESCONTO.CD_TP_DESCONTO = GLB_TP_DESCONTO.CD_TP_DESCONTO
WHERE
	DESCONTO.STS_TBL_DESC = 1
	AND DESCONTO.CD_TP_DESCONTO=1

UNION

--Desconto por marca
SELECT
	PROD.CD_PROD,
	DESC_MAR.PERC_DESC,
	ARV.CD_ARV_MERC_LINHA,
	DESC_FIL.CD_FILIAL,
	'MARCA' AS NIVEL,
	CONVERT(VARCHAR(5),GLB_TP_DESCONTO.CD_TP_DESCONTO) + ' - ' + GLB_TP_DESCONTO.DS_TP_DESCONTO AS DS_TP_DESCONTO
FROM
	EST_PROD_TBL_DESC DESCONTO

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL DESC_FIL ON
		DESCONTO.CD_EMP = DESC_FIL.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FIL.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_EST_MC DESC_MAR ON
		DESCONTO.CD_EMP = DESC_MAR.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_MAR.CD_TBL_DESC

	INNER JOIN EST_PROD PROD ON
		DESC_MAR.CD_EMP = PROD.CD_EMP
		AND DESC_MAR.CD_MC = PROD.CD_MC

	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		PROD.CD_EMP = ARV.CD_EMP
		AND PROD.CD_PROD = ARV.CD_PROD
	INNER JOIN GLB_TP_DESCONTO ON
	DESCONTO.CD_TP_DESCONTO = GLB_TP_DESCONTO.CD_TP_DESCONTO
WHERE
	DESCONTO.STS_TBL_DESC = 1
	AND DESCONTO.CD_TP_DESCONTO=1

UNION

--Desconto por família
SELECT
	ARV.CD_PROD,
	DESC_FAM.PERC_DESC,
	ARV.CD_ARV_MERC_LINHA,
	DESC_FIL.CD_FILIAL,
	'FAMILIA' AS NIVEL,
	CONVERT(VARCHAR(5),GLB_TP_DESCONTO.CD_TP_DESCONTO) + ' - ' + GLB_TP_DESCONTO.DS_TP_DESCONTO AS DS_TP_DESCONTO
FROM
	EST_PROD_TBL_DESC DESCONTO

	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL DESC_FIL ON
		DESCONTO.CD_EMP = DESC_FIL.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FIL.CD_TBL_DESC

	INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_FAMILIA DESC_FAM ON
		DESCONTO.CD_EMP = DESC_FAM.CD_EMP
		AND DESCONTO.CD_TBL_DESC = DESC_FAM.CD_TBL_DESC

	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		DESC_FAM.CD_EMP = ARV.CD_EMP
		AND DESC_FAM.CD_ARV_MERC_FAMILIA = ARV.CD_ARV_MERC_FAMILIA
	INNER JOIN GLB_TP_DESCONTO ON
	DESCONTO.CD_TP_DESCONTO = GLB_TP_DESCONTO.CD_TP_DESCONTO

WHERE
	DESCONTO.STS_TBL_DESC = 1
	AND DESCONTO.CD_TP_DESCONTO=1