IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_PED_ELTR_ATIVA_CP_HIST]') AND type in (N'P', N'TF'))
DROP FUNCTION [dbo].[UFD_EST_PED_ELTR_ATIVA_CP_HIST]  
GO

CREATE FUNCTION UFD_EST_PED_ELTR_ATIVA_CP_HIST(@CD_EMP INT, @STS INT, @CD_GRP_PED INT)
RETURNS 
        @RS_RETORNO  TABLE (CD_EMP					INT,
							CD_PED_SOL				INT,
							CD_FIL_ENTR				INT,				
							CD_GRP_PED				INT, 
							DT_GRP_PED				DATETIME, 
							NM_USU					VARCHAR(100), 
							DT_ATIVA				DATETIME, 
							HR_ATIVA				DATETIME, 
							STS_ATIVA				INT, 
							NM_USU_ATV				VARCHAR(100), 
							TP_METODO				INT,
							METODO					VARCHAR(50),
							CD_ROTEIRO				INT,	 
							DS_ROTEIRO				VARCHAR(100), 
							CD_ORD_ROTEIRO			INT, 
							CD_PED_CP_PARAM			INT, 
							DS_PED_CP_PARAM			VARCHAR(100), 
							CD_ORD_CP_PARAM			INT, 
							NM_FANT_FOR				VARCHAR(100), 
							DS_INT					VARCHAR(100), 
							DT_HR_ENV				DATETIME,
							DT_HR_RET				DATETIME,
							DT_HR_TEMPO_SIT			DATETIME,
							ST_ENV					INT, 
							ST_RET					INT, 
							ST_PED_VAZIO			INT,
							CD_CTR_ENV				INT,
							NM_ARQ_ENVIO			VARCHAR(100),
							NM_ARQ_RETORNO			VARCHAR(100),
							ST_RETORNO				INT,
							QT_IT_ENV				INT,
							VL_IT_ENV				MONEY,
							QT_IT_RET				INT,
							PERC_RET				MONEY,
							VLR_RET					MONEY,
							PED_PROCESSADO			INT,
							HR_PED_PROCESSADO		DATETIME,
							ORD_MCP					INT,
							CD_PROD					INT,
							DS_PROD					VARCHAR(150),
							CD_FABRIC               INT,
							NM_FABRIC               VARCHAR(100),
							FLAG_COMPRA_PHARMALINK  VARCHAR(40))
AS
BEGIN

DECLARE @RS_RESULT   TABLE (CD_EMP					INT,
							CD_PED_SOL			INT,
							CD_FIL_ENTR			INT,				
							CD_GRP_PED			INT, 
							DT_GRP_PED			DATETIME, 
							NM_USU				VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, 
							DT_ATIVA				DATETIME, 
							HR_ATIVA				DATETIME, 
							STS_ATIVA				INT, 
							NM_USU_ATV			VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 
							TP_METODO				INT,
							METODO				VARCHAR(50),
							CD_ROTEIRO			INT,	 
							DS_ROTEIRO			VARCHAR(100), 
							CD_ORD_ROTEIRO			INT, 
							CD_PED_CP_PARAM		INT, 
							DS_PED_CP_PARAM		VARCHAR(100), 
							CD_ORD_CP_PARAM		INT, 
							NM_FANT_FOR			VARCHAR(100), 
							DS_INT				VARCHAR(100), 
							DT_HR_ENV				DATETIME,
							DT_HR_RET				DATETIME,
							DT_HR_TEMPO_SIT			DATETIME,
							ST_ENV					INT, 
							ST_RET					INT, 
							ST_PED_VAZIO			INT,
							CD_CTR_ENV			INT,
							NM_ARQ_ENVIO			VARCHAR(100),
							NM_ARQ_RETORNO			VARCHAR(100),
							ST_RETORNO			INT,
							QT_IT_ENV				INT,
							VL_IT_ENV				MONEY,
							QT_IT_RET				INT,
							PERC_RET				MONEY,
							VLR_RET				MONEY,
							PED_PROCESSADO			INT,
							HR_PED_PROCESSADO		DATETIME,
							ORD_MCP				INT,
							CD_PROD				INT,
							DS_PROD				VARCHAR(150),
							CD_FABRIC                INT,
							NM_FABRIC                VARCHAR(100),
							FLAG_COMPRA_PHARMALINK	VARCHAR(40))



	   IF @CD_GRP_PED > 0 
	   BEGIN
		  SET @STS = 1 ;

		  INSERT INTO @RS_RESULT
		  SELECT DISTINCT
			 ELTR.CD_EMP
			 ,ELTR.CD_PED_SOL
			 ,ELTR.CD_FIL_ENTR
			 ,GRP.CD_GRP_PED
			 ,GRP.DT_GRP_PED
			 ,GRP_USU.NM_USU
			 ,ATV.DT_ATIVA
			 ,ATV.HR_ATIVA
			 ,ATV.STS_ATIVA
			 ,GLB_USU_ATIVA.NM_USU                                    AS NM_USU_ATV
			 ,0                                                       AS TP_METODO
			 ,'0 - PARAMETRO DE COMPRA'                               AS METODO
			 ,ROT.CD_ROTEIRO
			 ,ROT.DS_ROTEIRO
			 ,ATV_HIST_PARAM.CD_ORD_ROTEIRO
			 ,ATV_HIST_PARAM.CD_PED_CP_PARAM
			 ,CP_PARAM.DS_PED_CP_PARAM
			 ,ATV_HIST_PARAM.CD_ORD_CP_PARAM
			 ,PG_FORN.NM_FANT                                         AS NM_FANT_FOR
			 ,FORN_INT.DS_INT
			 ,ATV_HIST_PARAM.DT_HR_ENV
			 ,ATV_HIST_PARAM.DT_HR_RET
			 ,( ATV_HIST_PARAM.DT_HR_RET - ATV_HIST_PARAM.DT_HR_ENV ) AS DT_HR_TEMPO_SIT
			 ,ISNULL(ATV_HIST_PARAM.ST_ENV, 0)                        AS ST_ENV
			 ,ISNULL(ATV_HIST_PARAM.ST_RET, 0)                        AS ST_RET
			 ,ISNULL(ATV_HIST_PARAM.ST_PED_VAZIO, 0)                  AS ST_PED_VAZIO
			 ,ISNULL(ATV_HIST_PARAM.CD_CTR_ENV, 0)                    AS CD_CTR_ENV
			 ,ARQ_PEND.NM_ARQ_ENVIO
			 ,ARQ_PEND.NM_ARQ_RETORNO
			 ,ARQ_PEND.ST_RETORNO
			 ,0                                                       AS QT_IT_ENV
			 ,0														 AS VL_IT_ENV
			 ,0                                                       AS QT_IT_RET
			 ,0                                                       AS PERC_RET
			 ,0                                                       AS VLR_RET
			 ,CTR_ENV_ARQ_PEND.ARQ_XML_PROC                           AS PED_PROCESSADO
			 ,CTR_ENV_ARQ_PEND.DT_HT_ARQ_XML_PROC                     AS HR_PED_PROCESSADO
			 ,CTR_ENV.ORDMELHORCOND                                   AS ORD_MCP
			 ,IT.CD_PROD
			 ,P.DS_PROD
			 ,FAB.CD_FABRIC                                   
			 ,FAB.NM_FABRIC 
			 ,CASE WHEN FLAG_COMPRA_PHARMALINK=0 THEN
				'0 - SEM PROCESSO DE VAN DE COMPRAS'
			 WHEN FLAG_COMPRA_PHARMALINK=1 THEN
				'1 - PHARMALINK'
			 WHEN FLAG_COMPRA_PHARMALINK=2 THEN
				'2 - ENTIRE'
			 WHEN FLAG_COMPRA_PHARMALINK=3 THEN
				'3 - PHARMALINK E ENTIRE'
			 WHEN FLAG_COMPRA_PHARMALINK=4 THEN
				'4 - OL MIX'
			 WHEN FLAG_COMPRA_PHARMALINK=5 THEN
				'5 - FIDELIZE'
			 ELSE
				'NAO DEFINIDO'				
			 END AS FLAG_COMPRA_PHARMALINK  
		  FROM
        		 EST_PED_ELTR ELTR
			 INNER JOIN EST_PED_ELTR_ATIVA_CP_EST_PED_ELTR ATV_ELTR ON
			 ELTR.CD_EMP     = ATV_ELTR.CD_EMP AND 
			 ELTR.CD_PED_SOL = ATV_ELTR.CD_PED_SOL
		  
			 INNER JOIN EST_PED_ELTR_IT IT ON
			 ELTR.CD_EMP     = IT.CD_EMP AND 
			 ELTR.CD_PED_SOL = IT.CD_PED_SOL
		  
			 INNER JOIN EST_PROD P ON
			 IT.CD_EMP  = P.CD_EMP AND 
			 IT.CD_PROD = P.CD_PROD

			 INNER JOIN EST_PROD_FABRIC FAB ON
			 P.CD_FABRIC =FAB.CD_FABRIC

			 INNER JOIN EST_PED_ELTR_ATIVA_CP ATV ON
			 ATV_ELTR.CD_EMP = ATV.CD_EMP AND 
			 ATV_ELTR.CD_CTR = ATV.CD_CTR

			  JOIN EST_PED_ELTR_GRP_PED_EST_PED_ELTR GRP_PED ON
			 ELTR.CD_EMP     = GRP_PED .CD_EMP AND 
			 ELTR.CD_PED_SOL = GRP_PED .CD_PED_SOL
		  
			  JOIN EST_PED_ELTR_GRP_PED GRP ON
			 GRP.CD_EMP     = GRP_PED.CD_EMP AND 
			 GRP.CD_GRP_PED = GRP_PED.CD_GRP_PED
		  
			 LEFT JOIN GLB_USU GRP_USU ON
			 GRP.CD_USU = GRP_USU.CD_USU
		  
			 INNER JOIN EST_PED_ELTR_ATIVA_CP_EST_PED_CP_ROTEIRO_CP ATV_ROT ON
			 ATV.CD_EMP = ATV_ROT.CD_EMP AND 
			 ATV.CD_CTR = ATV_ROT.CD_CTR
		  
			 INNER JOIN EST_PED_CP_ROTEIRO_CP ROT ON
			 ATV_ROT.CD_ROTEIRO = ROT.CD_ROTEIRO
		  
			 INNER JOIN GLB_USU GLB_USU_ATIVA ON
			 ATV.CD_USU = GLB_USU_ATIVA.CD_USU
		  
			 LEFT JOIN EST_PED_ELTR_ATIVA_CP_HIST_PARAM ATV_HIST_PARAM ON
			 ATV_ROT.CD_EMP     = ATV_HIST_PARAM.CD_EMP AND 
			 ATV_ROT.CD_CTR     = ATV_HIST_PARAM.CD_CTR AND 
			 ATV_ROT.CD_ROTEIRO = ATV_HIST_PARAM.CD_ROTEIRO AND 
			 ELTR.CD_PED_SOL    = ATV_HIST_PARAM.CD_PED_SOL
		  
			 LEFT JOIN PG_FORN_INTEGRACAO FORN_INT ON
			 ATV_HIST_PARAM.CD_EMP  = FORN_INT.CD_EMP AND 
			 ATV_HIST_PARAM.CD_FORN = FORN_INT.CD_FORN AND 
			 ATV_HIST_PARAM.CD_INT  = FORN_INT.CD_INT
		  
			 LEFT JOIN EST_PED_ELTR_FORN_CTR_ENV CTR_ENV ON
			 CTR_ENV.CD_CTR_ENV = ATV_HIST_PARAM.CD_CTR_ENV
		  
			 LEFT JOIN PG_FORN ON
			 PG_FORN.CD_EMP  = CTR_ENV.CD_EMP AND 
			 PG_FORN.CD_FORN = CTR_ENV.CD_FORN
		  
			 LEFT JOIN EST_PED_CP_PARAM CP_PARAM ON
			 ATV_HIST_PARAM.CD_EMP          = CP_PARAM.CD_EMP AND 
			 ATV_HIST_PARAM.CD_PED_CP_PARAM = CP_PARAM.CD_PED_CP_PARAM
		  
			 LEFT JOIN EST_PED_ELTR_FORN_CTR_ENV_PG_FORN_INTEGRACAO_ARQ_PENDENTE CTR_ENV_ARQ_PEND ON
			 ATV_HIST_PARAM.CD_CTR_ENV = CTR_ENV_ARQ_PEND.CD_CTR_ENV
		  
			 LEFT JOIN PG_FORN_INTEGRACAO_ARQ_PENDENTE ARQ_PEND ON
			 CTR_ENV_ARQ_PEND.CD_CTR = ARQ_PEND.CD_CTR
		
		  WHERE
			 ELTR.CD_EMP         = @CD_EMP
			 AND GRP.CD_GRP_PED  = @CD_GRP_PED 
			 AND ISNULL(ATV_HIST_PARAM.ST_PED_VAZIO, 0) <> 1
			 AND ISNULL(ATV.STS_ATIVA, 0) IN ( 0,1 ) 
 
        END
	   else
	   begin
		  
		  INSERT INTO @RS_RESULT
		  SELECT DISTINCT
			 ELTR.CD_EMP
			 ,ELTR.CD_PED_SOL
			 ,ELTR.CD_FIL_ENTR
			 ,GRP.CD_GRP_PED
			 ,GRP.DT_GRP_PED
			 ,GRP_USU.NM_USU
			 ,ATV.DT_ATIVA
			 ,ATV.HR_ATIVA
			 ,ATV.STS_ATIVA
			 ,GLB_USU_ATIVA.NM_USU                                    AS NM_USU_ATV
			 ,0                                                       AS TP_METODO
			 ,'0 - PARAMETRO DE COMPRA'                               AS METODO
			 ,ROT.CD_ROTEIRO
			 ,ROT.DS_ROTEIRO
			 ,ATV_HIST_PARAM.CD_ORD_ROTEIRO
			 ,ATV_HIST_PARAM.CD_PED_CP_PARAM
			 ,CP_PARAM.DS_PED_CP_PARAM
			 ,ATV_HIST_PARAM.CD_ORD_CP_PARAM
			 ,PG_FORN.NM_FANT                                         AS NM_FANT_FOR
			 ,FORN_INT.DS_INT
			 ,ATV_HIST_PARAM.DT_HR_ENV
			 ,ATV_HIST_PARAM.DT_HR_RET
			 ,( ATV_HIST_PARAM.DT_HR_RET - ATV_HIST_PARAM.DT_HR_ENV ) AS DT_HR_TEMPO_SIT
			 ,ISNULL(ATV_HIST_PARAM.ST_ENV, 0)                        AS ST_ENV
			 ,ISNULL(ATV_HIST_PARAM.ST_RET, 0)                        AS ST_RET
			 ,ISNULL(ATV_HIST_PARAM.ST_PED_VAZIO, 0)                  AS ST_PED_VAZIO
			 ,ISNULL(ATV_HIST_PARAM.CD_CTR_ENV, 0)                    AS CD_CTR_ENV
			 ,ARQ_PEND.NM_ARQ_ENVIO
			 ,ARQ_PEND.NM_ARQ_RETORNO
			 ,ARQ_PEND.ST_RETORNO
			 ,0                                                       AS QT_IT_ENV
			 ,0														 AS VL_IT_ENV
			 ,0                                                       AS QT_IT_RET
			 ,0                                                       AS PERC_RET
			 ,0                                                       AS VLR_RET
			 ,CTR_ENV_ARQ_PEND.ARQ_XML_PROC                           AS PED_PROCESSADO
			 ,CTR_ENV_ARQ_PEND.DT_HT_ARQ_XML_PROC                     AS HR_PED_PROCESSADO
			 ,CTR_ENV.ORDMELHORCOND                                   AS ORD_MCP
			 ,IT.CD_PROD
			 ,P.DS_PROD
			 ,FAB.CD_FABRIC                                   
			 ,FAB.NM_FABRIC 
			 ,CASE WHEN FLAG_COMPRA_PHARMALINK=0 THEN
				'0 - SEM PROCESSO DE VAN DE COMPRAS'
			 WHEN FLAG_COMPRA_PHARMALINK=1 THEN
				'1 - PHARMALINK'
			 WHEN FLAG_COMPRA_PHARMALINK=2 THEN
				'2 - ENTIRE'
			 WHEN FLAG_COMPRA_PHARMALINK=3 THEN
				'3 - PHARMALINK E ENTIRE'
			 WHEN FLAG_COMPRA_PHARMALINK=4 THEN
				'4 - OL MIX'
			 WHEN FLAG_COMPRA_PHARMALINK=5 THEN
				'5 - FIDELIZE'
			 ELSE
				'NAO DEFINIDO'				
			 END AS FLAG_COMPRA_PHARMALINK  
		  FROM
        		 EST_PED_ELTR ELTR
			 INNER JOIN EST_PED_ELTR_ATIVA_CP_EST_PED_ELTR ATV_ELTR ON
			 ELTR.CD_EMP     = ATV_ELTR.CD_EMP AND 
			 ELTR.CD_PED_SOL = ATV_ELTR.CD_PED_SOL
		  
			 INNER JOIN EST_PED_ELTR_IT IT ON
			 ELTR.CD_EMP     = IT.CD_EMP AND 
			 ELTR.CD_PED_SOL = IT.CD_PED_SOL
		  
			 INNER JOIN EST_PROD P ON
			 IT.CD_EMP  = P.CD_EMP AND 
			 IT.CD_PROD = P.CD_PROD

			 INNER JOIN EST_PROD_FABRIC FAB ON
			 P.CD_FABRIC =FAB.CD_FABRIC

			 INNER JOIN EST_PED_ELTR_ATIVA_CP ATV ON
			 ATV_ELTR.CD_EMP = ATV.CD_EMP AND 
			 ATV_ELTR.CD_CTR = ATV.CD_CTR

			 LEFT JOIN EST_PED_ELTR_GRP_PED_EST_PED_ELTR GRP_PED ON
			 ELTR.CD_EMP     = GRP_PED .CD_EMP AND 
			 ELTR.CD_PED_SOL = GRP_PED .CD_PED_SOL
		  
			 LEFT JOIN EST_PED_ELTR_GRP_PED GRP ON
			 GRP.CD_EMP     = GRP_PED.CD_EMP AND 
			 GRP.CD_GRP_PED = GRP_PED.CD_GRP_PED
		  
			 LEFT JOIN GLB_USU GRP_USU ON
			 GRP.CD_USU = GRP_USU.CD_USU
		  
			 INNER JOIN EST_PED_ELTR_ATIVA_CP_EST_PED_CP_ROTEIRO_CP ATV_ROT ON
			 ATV.CD_EMP = ATV_ROT.CD_EMP AND 
			 ATV.CD_CTR = ATV_ROT.CD_CTR
		  
			 INNER JOIN EST_PED_CP_ROTEIRO_CP ROT ON
			 ATV_ROT.CD_ROTEIRO = ROT.CD_ROTEIRO
		  
			 INNER JOIN GLB_USU GLB_USU_ATIVA ON
			 ATV.CD_USU = GLB_USU_ATIVA.CD_USU
		  
			 LEFT JOIN EST_PED_ELTR_ATIVA_CP_HIST_PARAM ATV_HIST_PARAM ON
			 ATV_ROT.CD_EMP     = ATV_HIST_PARAM.CD_EMP AND 
			 ATV_ROT.CD_CTR     = ATV_HIST_PARAM.CD_CTR AND 
			 ATV_ROT.CD_ROTEIRO = ATV_HIST_PARAM.CD_ROTEIRO AND 
			 ELTR.CD_PED_SOL    = ATV_HIST_PARAM.CD_PED_SOL
		  
			 LEFT JOIN PG_FORN_INTEGRACAO FORN_INT ON
			 ATV_HIST_PARAM.CD_EMP  = FORN_INT.CD_EMP AND 
			 ATV_HIST_PARAM.CD_FORN = FORN_INT.CD_FORN AND 
			 ATV_HIST_PARAM.CD_INT  = FORN_INT.CD_INT
		  
			 LEFT JOIN EST_PED_ELTR_FORN_CTR_ENV CTR_ENV ON
			 CTR_ENV.CD_CTR_ENV = ATV_HIST_PARAM.CD_CTR_ENV
		  
			 LEFT JOIN PG_FORN ON
			 PG_FORN.CD_EMP  = CTR_ENV.CD_EMP AND 
			 PG_FORN.CD_FORN = CTR_ENV.CD_FORN
		  
			 LEFT JOIN EST_PED_CP_PARAM CP_PARAM ON
			 ATV_HIST_PARAM.CD_EMP          = CP_PARAM.CD_EMP AND 
			 ATV_HIST_PARAM.CD_PED_CP_PARAM = CP_PARAM.CD_PED_CP_PARAM
		  
			 LEFT JOIN EST_PED_ELTR_FORN_CTR_ENV_PG_FORN_INTEGRACAO_ARQ_PENDENTE CTR_ENV_ARQ_PEND ON
			 ATV_HIST_PARAM.CD_CTR_ENV = CTR_ENV_ARQ_PEND.CD_CTR_ENV
		  
			 LEFT JOIN PG_FORN_INTEGRACAO_ARQ_PENDENTE ARQ_PEND ON
			 CTR_ENV_ARQ_PEND.CD_CTR = ARQ_PEND.CD_CTR
		
		  WHERE
			 ELTR.CD_EMP         = @CD_EMP 
			 AND ISNULL(ATV_HIST_PARAM.ST_PED_VAZIO, 0) <> 1
			 AND ISNULL(ATV.STS_ATIVA, 0) = 0;

	   end

	   UPDATE RS 
	   SET QT_IT_ENV = E.QT_IT_ENV ,
		  VL_IT_ENV = E.VL_IT_ENV
	   FROM 
		  @RS_RESULT RS 		
		  INNER JOIN  (SELECT ENV.CD_CTR_ENV,
							 FORN_IT.CD_PROD,
							 SUM(FORN_IT.QT_IT_ENV) AS QT_IT_ENV,
							 SUM(I.VLR_PROD) AS VL_IT_ENV

						  FROM 
							 EST_PED_ELTR_FORN_CTR_ENV ENV 
							 INNER JOIN EST_PED_ELTR_FORN_IT FORN_IT ON 
							 ENV.CD_EMP	  = FORN_IT.CD_EMP AND 
							 ENV.CD_PED_SOL  = FORN_IT.CD_PED_SOL AND 
							 ENV.CD_FORN	  = FORN_IT.CD_FORN  AND 
							 ENV.ORD_ENV	  = FORN_IT.ORD_ENV  
							 INNER JOIN EST_PED_ELTR_IT I ON 
							 FORN_IT.CD_EMP		= I.CD_EMP AND 
							 FORN_IT.CD_PED_SOL  = I.CD_PED_SOL AND 
							 FORN_IT.CD_PROD		= I.CD_PROD 
						  where
							 I.CD_EMP=1
						  GROUP BY ENV.CD_CTR_ENV,
							 FORN_IT.CD_PROD) E ON
		  RS.CD_PROD=E.CD_PROD AND 
		  RS.CD_CTR_ENV=E.CD_CTR_ENV;
				
		UPDATE RS 
		SET 
			QT_IT_RET = R.QT_IT_RET,
			VLR_RET = R.VLR_RET
		FROM 
			@RS_RESULT RS 				
			INNER JOIN ( SELECT 
								ENV.CD_CTR_ENV,
								IT.CD_PROD,
								SUM(QT_IT) AS QT_IT_RET,
								SUM((IT.QT_IT * IT.VLR_IT) - IT.VLR_DESC + IT.VLR_IPI + IT.VLR_ICMS_SUBS )  AS VLR_RET
							FROM 
								EST_PED_ELTR_FORN_CTR_ENV ENV 
								INNER JOIN EST_PED_ELTR_FORN_PED_CP FORN_PED_CP ON 
								ENV.CD_EMP = FORN_PED_CP.CD_EMP
								AND ENV.CD_PED_SOL = FORN_PED_CP.CD_PED_SOL
								AND ENV.CD_FORN = FORN_PED_CP.CD_FORN 
								AND ENV.ORD_ENV = FORN_PED_CP.ORD_ENV 
								INNER JOIN EST_PED_CP_IT IT ON 
								FORN_PED_CP.CD_EMP = IT.CD_EMP
								AND FORN_PED_CP.CD_FIL_ENTR = IT.CD_FILIAL
								AND FORN_PED_CP.CD_PED = IT.CD_PED 
							 where
								IT.CD_EMP=1
							 GROUP BY 
								ENV.CD_CTR_ENV,
								IT.CD_PROD) R  ON
			RS.CD_PROD=R.CD_PROD AND RS.CD_CTR_ENV=R.CD_CTR_ENV;
						

		UPDATE RS 
		SET  PERC_RET = ROUND(ISNULL((CASE WHEN QT_IT_ENV > 0 THEN 
								    (CAST(QT_IT_RET AS NUMERIC(18,4)) / CAST(QT_IT_ENV AS NUMERIC(18,4))) * 100.00 
								ELSE 0 END),0),2)
		FROM @RS_RESULT RS ;		
					 
		INSERT INTO @RS_RETORNO          		                 
		SELECT RS.*
		FROM @RS_RESULT RS         
		ORDER BY 
			 RS.CD_GRP_PED, 
			 RS.TP_METODO, 
			 RS.CD_ROTEIRO, 
			 RS.CD_ORD_ROTEIRO, 
			 RS.CD_PED_CP_PARAM, 
			 RS.CD_ORD_CP_PARAM
		
		
		RETURN
END		

