IF EXISTS (SELECT
						 *
					 FROM
						 sys.objects
					 WHERE
						object_id = OBJECT_ID('[dbo].[P_RC_CLI_CONV_EST_PROD_DESCONTOS]') AND
						type IN ( 'P', 'PC' ))
	DROP PROCEDURE [dbo].[P_RC_CLI_CONV_EST_PROD_DESCONTOS]
GO
--EXEC P_RC_CLI_CONV_EST_PROD_DESCONTOS 1,1,202
CREATE PROCEDURE P_RC_CLI_CONV_EST_PROD_DESCONTOS (@CD_EMP INT, @CD_FILIAL INT, @CD_CONV INT)
AS
/*
	Esta procedure foi criada para integracao do ecomerce do SESI - SC
*/
SELECT
  distinct RS.CD_EMP,  
  RC_CLI_CONV_EST_PROD_TBL_DESC.CD_CONV,    
  EST_PROD_TBL_DESC_PRC_FILIAL.CD_FILIAL,     
  RS.CD_TBL_DESC,          
  RS.DS_TBL_DESC,      
  RS.CD_PROD,
  RS.PERC_DESC,
  RS.PERC_DESC_MAX,
  DS_ARV_MERC_LINHA,
  DS_ARV_MERC_CATEG,
  DS_ARV_MERC_DEPTO
FROM
(SELECT           
  D.CD_EMP,           
  D.CD_TBL_DESC,          
  D.DS_TBL_DESC,      
  A.CD_PROD,
  CASE WHEN L.PERC_DESC_LIMITE > 0 THEN  L.PERC_DESC_LIMITE ELSE  L.PERC_DESC END PERC_DESC,
  L.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D  INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_LINHA L ON           
	D.CD_EMP = L.CD_EMP AND           
	D.CD_TBL_DESC = L.CD_TBL_DESC
	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,          
  D.DS_TBL_DESC,      
  A.CD_PROD,
  CASE WHEN C.PERC_DESC_LIMITE > 0 THEN  C.PERC_DESC_LIMITE ELSE  C.PERC_DESC END PERC_DESC,
  C.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D  INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_CATEGORIA C ON           
	D.CD_EMP = C.CD_EMP AND           
	D.CD_TBL_DESC = C.CD_TBL_DESC
	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,          
  D.DS_TBL_DESC,      
  P.CD_PROD,
  CASE WHEN F.PERC_DESC_LIMITE > 0 THEN  F.PERC_DESC_LIMITE ELSE  F.PERC_DESC END PERC_DESC,
  F.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D INNER JOIN EST_PROD_TBL_DESC_EST_PROD_FABRIC F ON           
	D.CD_EMP = F.CD_EMP AND           
	D.CD_TBL_DESC = F.CD_TBL_DESC    
	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,          
  D.DS_TBL_DESC,      
  P.CD_PROD,
  CASE WHEN M.PERC_DESC_LIMITE > 0 THEN  M.PERC_DESC_LIMITE ELSE  M.PERC_DESC END PERC_DESC,
  M.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D INNER JOIN EST_PROD_TBL_DESC_EST_MC M ON           
	D.CD_EMP = M.CD_EMP AND
	D.CD_TBL_DESC = M.CD_TBL_DESC         
	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,          
  D.DS_TBL_DESC,      
  A.CD_PROD,
  CASE WHEN E.PERC_DESC_LIMITE > 0 THEN  E.PERC_DESC_LIMITE ELSE  E.PERC_DESC END PERC_DESC,
  E.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D  INNER JOIN EST_PROD_TBL_DESC_EST_ARV_MERC_FAMILIA E ON           
	D.CD_EMP = E.CD_EMP AND           
	D.CD_TBL_DESC = E.CD_TBL_DESC
	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,          
    D.DS_TBL_DESC,      
    T.CD_PROD,
    CASE WHEN T.PERC_DESC_LIMITE > 0 THEN  T.PERC_DESC_LIMITE ELSE  T.PERC_DESC END PERC_DESC,
    T.PERC_DESC_MAX
 FROM 
	EST_PROD_TBL_DESC D  INNER JOIN EST_PROD_TBL_DESC_EST_PROD T ON           
	D.CD_EMP = T.CD_EMP AND           
	D.CD_TBL_DESC = T.CD_TBL_DESC) RS 
	INNER JOIN EST_PROD_TBL_DESC_PRC_FILIAL ON
	RS.CD_EMP = EST_PROD_TBL_DESC_PRC_FILIAL.CD_EMP AND
	RS.CD_TBL_DESC = EST_PROD_TBL_DESC_PRC_FILIAL.CD_TBL_DESC
	INNER JOIN RC_CLI_CONV_EST_PROD_TBL_DESC ON 
	RS.CD_EMP = RC_CLI_CONV_EST_PROD_TBL_DESC.CD_EMP AND
	RS.CD_TBL_DESC = RC_CLI_CONV_EST_PROD_TBL_DESC.CD_TBL_DESC
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	RS.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	RS.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	INNER 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
	INNER JOIN EST_ARV_MERC_CATEGORIA ON
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_CATEGORIA.CD_EMP AND
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_CATEG = EST_ARV_MERC_CATEGORIA.CD_ARV_MERC_CATEG
	INNER JOIN EST_ARV_MERC_DEPTO ON
	EST_ARV_MERC_CATEGORIA.CD_EMP = EST_ARV_MERC_DEPTO.CD_EMP AND
	EST_ARV_MERC_CATEGORIA.CD_ARV_MERC_DEPTO = EST_ARV_MERC_DEPTO.CD_ARV_MERC_DEPTO
where
	RS.CD_EMP =@CD_EMP
	AND EST_PROD_TBL_DESC_PRC_FILIAL.CD_FILIAL = @CD_FILIAL
	AND RC_CLI_CONV_EST_PROD_TBL_DESC.CD_CONV =@CD_CONV
