--EXEC sp_msforeachtable "ALTER TABLE EST_PROD_TBL_DESC NOCHECK CONSTRAINT all"
--GO
--EXEC sp_msforeachtable "ALTER TABLE EST_PROD_TBL_DESC_QTDE NOCHECK CONSTRAINT all"
  
	    BEGIN TRY DROP TABLE #RS_TP_DESC END TRY 
		BEGIN CATCH END CATCH

		GO
		SELECT CD_TP_DESCONTO,
			DS_TP_DESCONTO,
			TP_DESCONTO 
			INTO #RS_TP_DESC
		FROM
		(
		SELECT  0 AS TP_DESCONTO, 'SEM DESCONTO'  			             							AS DS_TP_DESCONTO,0 CD_TP_DESCONTO
		UNION
			SELECT  1 AS TP_DESC, 'DESCONTO CADASTRO PRODUTO'										AS DS_TP_DESC,1
		UNION
			SELECT  2 AS TP_DESC, 'DESCONTO LOJA'													AS DS_TP_DESC,2 -- CONCEDIDO PELOS VENDEDORES DA LOJA
		UNION
			SELECT  3 AS TP_DESC, 'DESCONTO CONVENIO'												AS DS_TP_DESC,3
		UNION
			SELECT  4 AS TP_DESC, 'DESCONTO FIDELIDADE'												AS DS_TP_DESC,4
		UNION
			SELECT  5 AS TP_DESC, 'DESCONTO PBM'													AS DS_TP_DESC,5 -- DIFERENCA DO PRODUTO PELO VALOR VENDIDO PBM
		UNION
			SELECT  6 AS TP_DESC, 'DESCONTO OFERTA'													AS DS_TP_DESC,6 -- QUANDO O PRODUTO ESTA NO TABLOIDE
		UNION
			SELECT  7 AS TP_DESC, 'DESCONTO OFERTA FIDELIDADE'										AS DS_TP_DESC,7 -- NAO EXISTE MAIS
		UNION
			SELECT  8 AS TP_DESC, 'DESCONTO CUPONAGEM'												AS DS_TP_DESC,8 -- NAO EXISTE MAIS
		UNION
			SELECT  9 AS TP_DESC, 'DESCONTO CRM'													AS DS_TP_DESC,9 
		UNION
			SELECT 10 AS TP_DESC, 'DESCONTO POR TROCA DE PONTOS'	 								AS DS_TP_DESC,10 
		UNION
			SELECT 11 AS TP_DESC, 'DESCONTO ANIVERSARI0' 											AS DS_TP_DESC,11
		UNION
			SELECT 12 AS TP_DESC, 'DESCONTO POR CLASSIFICACAO DE CLIENTE'							AS DS_TP_DESC,12
		UNION
			SELECT 13 AS TP_DESC, 'DESCONTO PRECO DE FABRICA'										AS DS_TP_DESC,13
		UNION
			SELECT 14 AS TP_DESC, 'DESCONTO POR QUANTIDADE'  										AS DS_TP_DESC,14
		UNION
			SELECT 15 AS TP_DESC, 'DESCONTO PROMOCAO NO TELEVENDAS'  								AS DS_TP_DESC,15
		UNION
			SELECT 16 AS TP_DESC, 'DESCONTO CONVENIO COM RECEITA'  									AS DS_TP_DESC,16
		UNION
			SELECT 17 AS TP_DESC, 'DESCONTO POR VENDA DE PRODUTO CASADA POR VLR DO TICKET MEDIO' 	AS DS_TP_DESC,17
		UNION
			SELECT 18 AS TP_DESC, 'DESCONTO POR FORMA DE PAGAMENTO'									AS DS_TP_DESC,18
		UNION
			SELECT 19 AS TP_DESC, 'DESCONTO CREDIARIO'  											AS DS_TP_DESC,19
		UNION
			SELECT 20 AS TP_DESC, 'DESCONTO CAMPANHA'  											    AS DS_TP_DESC,20
		UNION
			SELECT 21 AS TP_DESC, 'DESCONTO CONVENIO CUSTOMIZADO'								    AS DS_TP_DESC,21
		UNION
			SELECT 22 AS TP_DESC, 'DESCONTO MEDICAMENTO GRATUITO' 								    AS DS_TP_DESC,22
		UNION
			SELECT 23 AS TP_DESC, 'DESCONTO PROGRAMADO' 			 					  			AS DS_TP_DESC,23
		UNION
			SELECT 24 AS TP_DESC, 'DESCONTO PAGUE LEVE' 			 					  			AS DS_TP_DESC,24
		UNION
			SELECT 25 AS TP_DESC, 'DESCONTO FINANCEIRO NOTA FISCAL' 			 					AS DS_TP_DESC,25
		UNION
			SELECT 26 AS TP_DESC, 'DESCONTO PRE-VENCIDO' 			 								AS DS_TP_DESC,26			
		UNION
			SELECT 27 AS TP_DESC, 'DESCONTO POS-PBM' 			 									AS DS_TP_DESC,27			
		UNION
			SELECT 28 AS TP_DESC, 'DESCONTO ESPECIAL LISTA DE PRODUTOS CLIENTE' 			 		AS DS_TP_DESC,28		
		UNION
			SELECT 99 AS TP_DESC, 'DESCONTO CONVENIO/CREDIARIO FORMA DE PGTO'  						AS DS_TP_DESC,99
		 ) TBL


		 UPDATE T
		 SET
			DS_TP_DESCONTO=R.DS_TP_DESCONTO,
			TP_DESCONTO=R.TP_DESCONTO
		 FROM 	
			#RS_TP_DESC R
			INNER JOIN GLB_TP_DESCONTO T ON 
			R.CD_TP_DESCONTO = T.CD_TP_DESCONTO;

		GO

		INSERT INTO GLB_TP_DESCONTO (CD_TP_DESCONTO,DS_TP_DESCONTO,TP_DESCONTO)
		SELECT R.CD_TP_DESCONTO, R.DS_TP_DESCONTO,R.TP_DESCONTO
		FROM 
			#RS_TP_DESC R
			LEFT JOIN GLB_TP_DESCONTO T ON 
			R.CD_TP_DESCONTO = T.CD_TP_DESCONTO 
		WHERE T.CD_TP_DESCONTO IS NULL

--GO
--EXEC sp_msforeachtable "ALTER TABLE EST_PROD_TBL_DESC_QTDE WITH CHECK CHECK CONSTRAINT all"
--GO
--EXEC sp_msforeachtable "ALTER TABLE EST_PROD_TBL_DESC WITH CHECK CHECK CONSTRAINT all"

