IF OBJECT_ID('UFD_RC_VD_CONV_EXPORTA') IS NOT NULL
    BEGIN
        DROP FUNCTION UFD_RC_VD_CONV_EXPORTA;
END;
GO

CREATE FUNCTION [dbo].[UFD_RC_VD_CONV_EXPORTA]
(@CD_CONV VARCHAR(4000), 
 @DT_INI  DATETIME,
 @DT_FIM  DATETIME
)	    
RETURNS @UFD_RC_VD_CONV_EXPORTA TABLE
(CD_EMP            INT,
 CD_CLI            INT,
 NOME              VARCHAR(150),
 CPF               VARCHAR(20),
 RZ_CONV           VARCHAR(150),
 CNPJ_CONV         VARCHAR(20),
 CD_FILIAL         INT,
 CD_CONV           INT,
 MATRICULA         VARCHAR(50),
 CUPOM             INT,
 DT_VD             DATETIME,
 VLR_CONV          MONEY,
 CD_PROD           INT,
 DS_PROD           VARCHAR(200),
 CD_MEDICO_REC     INT,
 QT_IT             MONEY,
 VLR_CONV_EMP      MONEY,
 VLR_CONV_TOT      MONEY,
 FLAG_N_EXP_VD_LAY INT,
 INF_CONVENIADO    VARCHAR(50),
 CD_MDL_VD         INT,
 CD_PLANO_VENDA    INT,
 VLR_NCC_TOTAL     MONEY,
 VLR_NCC_IT        MONEY,
 CD_ARV_MERC_LINHA INT,
 CNPJ_FILIAL       VARCHAR(20),
 CD_BARRA		   VARCHAR(13),
 CNPJ_MATRIZ	   VARCHAR(20),
 NR_DP			   INT
)
AS
     BEGIN
         DECLARE @TBL_EXPORTA TABLE
         (CD_EMP            INT,
          CD_CLI            INT,
          CD_FILIAL         INT,
          CD_CONV           INT,
          MATRICULA         VARCHAR(50),
          CD_CTR            INT,
          CUPOM             INT,
          DT_VD             DATETIME,
          VLR_CONV          MONEY,
          VLR_CONV_IT       MONEY,
          CD_PROD           INT,
          CD_MEDICO_REC     INT,
          VLR_TOT_IT        MONEY,
          QT_IT             MONEY,
          FLAG_N_EXP_VD_LAY INT,
          CD_MDL_VD         INT,
          VLR_NCC_TOTAL     MONEY,
          VLR_NCC_IT        MONEY,
          CD_ARV_MERC_LINHA INT,
		  CD_BARRA			VARCHAR(13),
		  CNPJ_MATRIZ		VARCHAR(20),
		  NR_DP				INT
         );
         DECLARE @TBL_CORRECAO TABLE
         (CD_CTR     INT,
          VLR_CONV   MONEY,
          VLR_TOT_IT MONEY,
          DIF        MONEY,
          PERC_NCC   MONEY
         );
         DECLARE @TBL_CONV TABLE
         (CD_EMP  INT,
          CD_CONV INT
         );
         DECLARE @CD_CTR_RS INT;
         INSERT INTO @TBL_CONV
                SELECT 1,
                       ID
                FROM UFD_PRM_IN_STRING(@CD_CONV);
--------------------------------------------------------------------------------------------------------------------------
-- INCLUINDO DADOS DA VENDA DO CONV?NIO
--------------------------------------------------------------------------------------------------------------------------
         INSERT INTO @TBL_EXPORTA
                SELECT RC_VD_CONV.CD_EMP,
                       RC_VD_CONV.CD_CLI,
                       RC_VD_CONV.CD_FILIAL,
                       RC_VD_CONV.CD_CONV,
                       RC_CLI_CONV_RC_CLI.MATRICULA,
                       RC_VD_CONV.CD_CTR,
                       RC_VD_CONV.NR_ECF AS CUPOM,
                       RC_VD_CONV.DT_VD,
                       RC_VD_CONV.VLR_CONV,
                       (CASE
                            WHEN PDV_VD.VLR_LIQ_VD = RC_VD_CONV.VLR_CONV
                            THEN CAST((PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) AS NUMERIC(18, 2))
                            ELSE CAST(((CAST((PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) AS NUMERIC(20, 10)) / CAST(PDV_VD.VLR_LIQ_VD AS NUMERIC(20, 10))) * CAST(RC_VD_CONV.VLR_CONV AS NUMERIC(18, 2))) AS NUMERIC(18, 2))
                        END) AS VLR_CONV_IT,
                       PDV_VD_IT.CD_PROD,
                       ISNULL(PDV_VD_IT_REC.CD_MEDICO_REC, 0) AS CD_MEDICO_REC,
                       (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT),
                       PDV_VD_IT.QT_IT,
                       RC_CLI_CONV_RC_CLI.FLAG_N_EXP_VD_LAY,
                       ISNULL(RC_CLI_CONV.CD_MDL_VD, 0) AS CD_MDL_VD,
                       ISNULL(
                             (
                                 SELECT SUM(RC_VD_CONV_CRED.VLR_CRED)
                                 FROM RC_VD_CONV_CRED
                                 WHERE RC_VD_CONV_CRED.CD_EMP = RC_VD_CONV.CD_EMP
                                       AND RC_VD_CONV_CRED.CD_CTR = RC_VD_CONV.CD_CTR
                             ), 0) AS VLR_NCC,
                       0,
                       0 AS CD_ARV_MERC_LINHA,
					   
					   RIGHT(PDV_VD_IT.CD_BARRA,13) AS CD_BARRA,													-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
					   
					   (SELECT DISTINCT T2.CGC FROM RC_VD_CONV_RC_DEB T1									
						INNER JOIN PRC_FILIAL T2 ON T2.CD_EMP =T1.CD_EMP AND T2.CD_FILIAL = T1.CD_FILIAL		
						WHERE T1.CD_EMP = RC_VD_CONV.CD_EMP
						AND T1.CD_CTR = RC_VD_CONV.CD_CTR
						AND T1.VLR_VD_CONV = RC_VD_CONV.VLR_CONV GROUP BY T2.CGC) AS CNPJ_MATRIZ,					-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
												
					   (SELECT DISTINCT T2.NR_DP FROM RC_VD_CONV_RC_DEB T1
					   INNER JOIN RC_DEB T2 ON T2.CD_EMP = T1.CD_EMP 
					   AND T2.CD_RC_DEB = T1.CD_RC_DEB 
					   AND T2.CD_FILIAL = T1.CD_FILIAL
						WHERE T1.CD_EMP = RC_VD_CONV.CD_EMP
						AND T1.CD_CTR = RC_VD_CONV.CD_CTR
						AND T1.VLR_VD_CONV = RC_VD_CONV.VLR_CONV GROUP BY T2.NR_DP) AS NR_DP						-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852

                FROM 			-- Sesi
					 RC_VD_CONV -- WITH (INDEX (IX_RC_VD_CONV_DT_FECHA))
                     INNER JOIN RC_CLI_CONV_RC_CLI ON RC_VD_CONV.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
                                                      AND RC_VD_CONV.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
                                                      AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
                     INNER JOIN PDV_VD_RC_VD_CONV ON RC_VD_CONV.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
                                                     AND RC_VD_CONV.CD_CTR = PDV_VD_RC_VD_CONV.CD_CTR
                     INNER JOIN PDV_VD ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
                                          AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
                                          AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
                     INNER JOIN PDV_VD_IT ON PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP
                                             AND PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL
                                             AND PDV_VD.CD_VD = PDV_VD_IT.CD_VD
                                             AND PDV_VD.DT_VD = PDV_VD_IT.DT_VD
                     LEFT JOIN PDV_VD_IT_REC ON PDV_VD_IT_REC.CD_EMP = PDV_VD_IT.CD_EMP
                                                AND PDV_VD_IT_REC.CD_FILIAL = PDV_VD_IT.CD_FILIAL
                                                AND PDV_VD_IT_REC.CD_VD = PDV_VD_IT.CD_VD
                                                AND PDV_VD_IT_REC.CD_IT = PDV_VD_IT.CD_IT
                     INNER JOIN @TBL_CONV c ON RC_VD_CONV.CD_EMP = c.CD_EMP
                                               AND RC_VD_CONV.CD_CONV = c.CD_CONV
                     INNER JOIN RC_CLI_CONV ON RC_VD_CONV.CD_EMP = RC_CLI_CONV.CD_EMP
                                               AND RC_VD_CONV.CD_CONV = RC_CLI_CONV.CD_CONV
                WHERE 
				RC_VD_CONV.DT_FECHA BETWEEN @DT_INI AND @DT_FIM
				AND RC_VD_CONV.ST_FECHVD_CONV IN(0, 1, 5)
				AND RC_VD_CONV.CD_EMP=1
				AND RC_VD_CONV.VLR_CONV > 0
				AND PDV_VD_IT.ST_IT = 1
				AND PDV_VD_IT.cd_emp = 1
				
UNION ALL

SELECT RC_VD_CONV.CD_EMP,
       RC_VD_CONV.CD_CLI,
       RC_VD_CONV.CD_FILIAL,
	   RC_VD_CONV.CD_CONV,
	   LTRIM(RTRIM(RC_CLI_CONV_RC_CLI.MATRICULA)) AS MATRICULA,
	   RC_VD_CONV.CD_CTR,   
	   RC_VD_CONV.NR_ECF AS CUPOM,
	   RC_VD_CONV.DT_VD,
	   RC_VD_CONV.VLR_CONV,
	  sum(CASE WHEN EST_NF_SAI.VLR_NF = RC_VD_CONV.VLR_CONV THEN
			 CAST(EST_NF_SAI_IT.VLR_IT - (EST_NF_SAI_IT.VLR_DESC_IT / EST_NF_SAI_IT.QT_IT) AS NUMERIC(18,2))
	  ELSE
			 CAST(((CAST((EST_NF_SAI_IT.VLR_IT - (EST_NF_SAI_IT.VLR_DESC_IT / EST_NF_SAI_IT.QT_IT)) AS NUMERIC(20,10)) / 
			 CAST(EST_NF_SAI.VLR_NF AS NUMERIC(20,10))) 
				 * CAST(RC_VD_CONV.VLR_CONV AS NUMERIC(18,2))) AS NUMERIC(18,2)) 
	  END) AS VLR_CONV_IT, 		
	  EST_NF_SAI_IT.CD_PROD,
	  MAX(ISNULL(EST_NF_SAI_IT_RECEITA.CD_MEDICO_REC, 0)) AS CD_MEDICO_REC,
	  
	  
	(EST_NF_SAI_IT.VLR_IT - (EST_NF_SAI_IT.VLR_DESC_IT / EST_NF_SAI_IT.QT_IT)),
	EST_NF_SAI_IT.QT_IT,
	
	
	RC_CLI_CONV_RC_CLI.FLAG_N_EXP_VD_LAY,
	ISNULL(RC_CLI_CONV.CD_MDL_VD,0) AS CD_MDL_VD,
	ISNULL((SELECT  SUM(RC_VD_CONV_CRED.VLR_CRED) 
				FROM  RC_VD_CONV_CRED 
				WHERE  RC_VD_CONV_CRED.CD_EMP=RC_VD_CONV.CD_EMP AND 
					RC_VD_CONV_CRED.CD_CTR=RC_VD_CONV.CD_CTR),0)  AS VLR_NCC,
			0,
			EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA AS CD_ARV_MERC_LINHA, 
			
			
	'' AS CD_BARRA,													-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
					   
					   (SELECT DISTINCT T2.CGC FROM RC_VD_CONV_RC_DEB T1									
						INNER JOIN PRC_FILIAL T2 ON T2.CD_EMP =T1.CD_EMP AND T2.CD_FILIAL = T1.CD_FILIAL		
						WHERE T1.CD_EMP = RC_VD_CONV.CD_EMP
						AND T1.CD_CTR = RC_VD_CONV.CD_CTR
						AND T1.VLR_VD_CONV = RC_VD_CONV.VLR_CONV GROUP BY T2.CGC) AS CNPJ_MATRIZ,					-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
												
					   (SELECT DISTINCT T2.NR_DP FROM RC_VD_CONV_RC_DEB T1
					   INNER JOIN RC_DEB T2 ON T2.CD_EMP = T1.CD_EMP 
					   AND T2.CD_RC_DEB = T1.CD_RC_DEB 
					   AND T2.CD_FILIAL = T1.CD_FILIAL
						WHERE T1.CD_EMP = RC_VD_CONV.CD_EMP
						AND T1.CD_CTR = RC_VD_CONV.CD_CTR
						AND T1.VLR_VD_CONV = RC_VD_CONV.VLR_CONV GROUP BY T2.NR_DP) AS NR_DP						-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852

			
FROM RC_VD_CONV
INNER JOIN RC_CLI ON RC_VD_CONV.CD_EMP = RC_CLI.CD_EMP
AND RC_VD_CONV.CD_CLI = RC_CLI.CD_CLI
LEFT OUTER JOIN RC_CLI_CONV_RC_CLI ON RC_CLI.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
AND RC_CLI.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
INNER JOIN RC_CLI_CONV ON RC_VD_CONV.CD_EMP = RC_CLI_CONV.CD_EMP
AND RC_VD_CONV.CD_CONV = RC_CLI_CONV.CD_CONV
LEFT OUTER JOIN EST_NF_SAI_RC_VD_CONV ON RC_VD_CONV.CD_EMP = EST_NF_SAI_RC_VD_CONV.CD_EMP
AND RC_VD_CONV.CD_FILIAL = EST_NF_SAI_RC_VD_CONV.CD_FILIAL
AND RC_VD_CONV.CD_CTR = EST_NF_SAI_RC_VD_CONV.CD_CTR
LEFT OUTER JOIN EST_NF_SAI ON EST_NF_SAI_RC_VD_CONV.CD_EMP = EST_NF_SAI.CD_EMP
AND EST_NF_SAI_RC_VD_CONV.CD_FILIAL = EST_NF_SAI.CD_FILIAL
AND EST_NF_SAI_RC_VD_CONV.CD_NF = EST_NF_SAI.CD_NF
LEFT OUTER JOIN EST_NF_SAI_IT ON EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP
AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
AND EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
LEFT OUTER JOIN EST_NF_SAI_IT_RECEITA ON EST_NF_SAI_IT_RECEITA.CD_EMP = EST_NF_SAI_IT.CD_EMP
AND EST_NF_SAI_IT_RECEITA.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
AND EST_NF_SAI.CD_NF = EST_NF_SAI_IT_RECEITA.CD_NF
LEFT OUTER JOIN EST_PROD ON RC_VD_CONV.CD_EMP = EST_PROD.CD_EMP
AND EST_NF_SAI_IT.CD_PROD = EST_PROD.CD_PROD
LEFT OUTER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP
AND EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD
INNER JOIN PRC_FILIAL ON RC_VD_CONV.CD_FILIAL = PRC_FILIAL.CD_FILIAL
AND RC_VD_CONV.CD_EMP = PRC_FILIAL.CD_EMP
WHERE RC_VD_CONV.CD_EMP = 1
--and RC_VD_CONV.CD_FILIAL = 505
AND RC_VD_CONV.DT_FECHA BETWEEN @DT_INI AND @DT_FIM
  AND EST_NF_SAI.STS_NF = 0
  AND ST_FECHVD_CONV IN (0,1,5)
  AND RC_CLI_CONV.TP_CONV IN (0,4)
  AND  RC_VD_CONV.CD_CONV IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_CONV))
  group by RC_VD_CONV.CD_EMP,
       RC_VD_CONV.CD_CLI,
       RC_VD_CONV.CD_FILIAL,
	   RC_VD_CONV.CD_CONV,
	   RC_CLI_CONV_RC_CLI.MATRICULA,
	   RC_VD_CONV.CD_CTR,   
	   RC_VD_CONV.NR_ECF,
	   RC_VD_CONV.DT_VD,
	   RC_VD_CONV.VLR_CONV,
	   EST_NF_SAI_IT.CD_PROD,
	   RC_CLI_CONV_RC_CLI.FLAG_N_EXP_VD_LAY,
	   RC_CLI_CONV.CD_MDL_VD,
	   EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA,
	   EST_NF_SAI_IT.VLR_IT,
	   EST_NF_SAI_IT.VLR_DESC_IT,
	   EST_NF_SAI_IT.QT_IT
	   
	   
--------------------------------------------------------------------------------------------------------------------------
-- INCLUINDO VENDAS COM DIFEREN?A DO VALOR DO CONV?NIO E O VALOR CALCULADO DO ITEM NO CONV?NIO
--------------------------------------------------------------------------------------------------------------------------
         INSERT INTO @TBL_CORRECAO
                SELECT CD_CTR,
                       VLR_CONV,
                       MAX(CASE
                               WHEN VLR_TOT_IT <> VLR_CONV_IT
                               THEN VLR_TOT_IT
                               ELSE 0
                           END) AS VLR_TOT_IT,
                       (VLR_CONV - SUM(VLR_CONV_IT)) AS DIF,
                       VLR_NCC_TOTAL / VLR_CONV
                FROM @TBL_EXPORTA TBL_EXPORTA
                GROUP BY CD_CTR,
                         VLR_CONV,
                         VLR_NCC_TOTAL
                HAVING(VLR_CONV <> SUM(VLR_CONV_IT))
                      OR (VLR_NCC_TOTAL > 0);
--------------------------------------------------------------------------------------------------------------------------
-- CASO N?O EXISTA ITEM COM O TOTAL EM CONV?NIO DIFERENTE, JOGA A DIFEREN?A DO CALCULO NO MAIOR ITEM.
-- ACONTECE QUANDO O RECEBIMENTO EM CONV?NIO ? MUITO PR?XIMO A 100% DA VENDA E O RATEIO N?O CONSEGUE APURAR NO ITEM.
--------------------------------------------------------------------------------------------------------------------------
         UPDATE TBL_CORRECAO
           SET
               VLR_TOT_IT =
         (
             SELECT MAX(TBL_EXPORTA.VLR_TOT_IT)
             FROM @TBL_EXPORTA TBL_EXPORTA
             WHERE TBL_EXPORTA.CD_CTR = TBL_CORRECAO.CD_CTR
         )
         FROM @TBL_CORRECAO TBL_CORRECAO
         WHERE TBL_CORRECAO.VLR_TOT_IT = 0;
--------------------------------------------------------------------------------------------------------------------------
-- CORRIGINDO AS DIFEREN?AS NO ITEM DE MAIOR VALOR NA VENDA
--------------------------------------------------------------------------------------------------------------------------
         DECLARE RS_CORRECAO CURSOR FAST_FORWARD
         FOR
             SELECT CD_CTR
             FROM @TBL_CORRECAO;
         OPEN RS_CORRECAO;
         FETCH NEXT FROM RS_CORRECAO INTO @CD_CTR_RS;
         WHILE @@FETCH_STATUS = 0
             BEGIN
                 UPDATE TOP (1) @TBL_EXPORTA
                   SET
                       VLR_CONV_IT = CAST((VLR_CONV_IT + DIF) AS NUMERIC(18, 2))
                 FROM @TBL_EXPORTA TBL_EXPORTA
                      INNER JOIN @TBL_CORRECAO TBL_CORRECAO ON TBL_EXPORTA.CD_CTR = TBL_CORRECAO.CD_CTR
                                                               AND TBL_EXPORTA.VLR_TOT_IT = TBL_CORRECAO.VLR_TOT_IT
                 WHERE TBL_CORRECAO.CD_CTR = @CD_CTR_RS;
                 FETCH NEXT FROM RS_CORRECAO INTO @CD_CTR_RS;
             END;
         CLOSE RS_CORRECAO;
         DEALLOCATE RS_CORRECAO;
--------------------------------------------------------------------------------------------------------------------------
-- ALTERANDO VALOR DA VENDA POR CAUSA DA NCC
--------------------------------------------------------------------------------------------------------------------------
         UPDATE @TBL_EXPORTA
           SET
               VLR_CONV_IT = CAST((VLR_CONV_IT - (VLR_CONV_IT * PERC_NCC)) AS NUMERIC(18, 2)),
               VLR_NCC_IT = CAST((VLR_CONV_IT * PERC_NCC) AS NUMERIC(18, 2))
         FROM @TBL_EXPORTA TBL_EXPORTA
              INNER JOIN @TBL_CORRECAO TBL_CORRECAO ON TBL_EXPORTA.CD_CTR = TBL_CORRECAO.CD_CTR
         WHERE PERC_NCC <> 0; 
--------------------------------------------------------------------------------------------------------------------------
-- ALTERANDO VALOR DA LINHA
--------------------------------------------------------------------------------------------------------------------------
         UPDATE @TBL_EXPORTA
           SET
               CD_ARV_MERC_LINHA = ARV.CD_ARV_MERC_LINHA
         FROM @TBL_EXPORTA TBL_EXPORTA
              INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON TBL_EXPORTA.CD_EMP = ARV.CD_EMP
                                                               AND TBL_EXPORTA.CD_PROD = ARV.CD_PROD
         WHERE ARV.CD_ARV_MERC_LINHA <> 0; 	
--------------------------------------------------------------------------------------------------------------------------
-- PREPARANDO OS DADOS PARA O RETORNO
--------------------------------------------------------------------------------------------------------------------------
         INSERT INTO @UFD_RC_VD_CONV_EXPORTA
                SELECT tbl_exporta.CD_EMP,
                       tbl_exporta.CD_CLI,
                       RC_CLI.RZ_CLI AS NOME,
                       RC_CLI.CGC_CPF AS CPF,
                       RC_CLI_CONV.DS_CONV AS RZ_CONV,
                       cli_conv.CGC_CPF AS CNPJ_CONV,
                       tbl_exporta.CD_FILIAL,
                       tbl_exporta.CD_CONV,
                       LTRIM(RTRIM(tbl_exporta.MATRICULA)) AS MATRICULA,
                       tbl_exporta.CUPOM,
                       tbl_exporta.DT_VD,
                       tbl_exporta.VLR_CONV_IT - CAST((tbl_exporta.VLR_CONV_IT * (RC_CLI_CONV.PERC_CONV / 100)) AS NUMERIC(18, 2)) AS VLR_CONV,
                       tbl_exporta.CD_PROD,
                       EST_PROD.DS_PROD,
                       tbl_exporta.CD_MEDICO_REC,
                       tbl_exporta.QT_IT,
                       CAST((tbl_exporta.VLR_CONV_IT * (RC_CLI_CONV.PERC_CONV / 100)) AS NUMERIC(18, 2)) AS VLR_CONV_EMP,
                       tbl_exporta.VLR_CONV_IT,
                       tbl_exporta.FLAG_N_EXP_VD_LAY,
                       RC_CLI.INF_CPL_CONV,
                       tbl_exporta.CD_MDL_VD,
                       -- RC_CLI.CD_PLANO_VENDA,            -- COLUNA DESABILITADA EM:  12/07/2022 , CHAMADO: 31233 , Registrado na sub tarefa para BANCO DE DADOS:  31736,  (RFS)
					   RC_CLI_CONV_RC_CLI.CD_PLANO_VENDA,   -- COLUNA INCLUSA      EM:  12/07/2022 , CHAMADO: 31233 , Registrado na sub tarefa para BANCO DE DADOS:  31736,  (RFS)
                       tbl_exporta.VLR_NCC_TOTAL,
                       tbl_exporta.VLR_NCC_IT,
                       tbl_exporta.CD_ARV_MERC_LINHA,
					   REPLACE(REPLACE(REPLACE(PRC_FILIAL.CGC,'-',''),'/',''),'.','') AS CNPJ_FILIAL,
					   tbl_exporta.CD_BARRA,																	-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
					   REPLACE(REPLACE(REPLACE(tbl_exporta.CNPJ_MATRIZ,'-',''),'/',''),'.','') AS CNPJ_MATRIZ,	-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852
					   tbl_exporta.NR_DP																		-- COLUNA INCLUSA      EM:  19/05/2023 , CHAMADO: FARMAGOI-39852

                FROM @TBL_EXPORTA tbl_exporta
                     INNER JOIN RC_CLI ON tbl_exporta.CD_EMP = RC_CLI.CD_EMP
                                          AND tbl_exporta.CD_CLI = RC_CLI.CD_CLI
                     INNER JOIN RC_CLI_CONV ON tbl_exporta.CD_EMP = RC_CLI_CONV.CD_EMP
                                               AND tbl_exporta.CD_CONV = RC_CLI_CONV.CD_CONV
                     INNER JOIN RC_CLI cli_conv ON RC_CLI_CONV.CD_EMP = cli_conv.CD_EMP
                                                   AND RC_CLI_CONV.CD_CLI = cli_conv.CD_CLI
                     INNER JOIN RC_CLI_CONV_RC_CLI  ON RC_CLI.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP   -- INNER JOIN INCLUSO      EM:  12/07/2022 , CHAMADO: 31233 , Registrado na sub tarefa para BANCO DE DADOS:  31736,  (RFS)
                                                   AND RC_CLI.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
					 INNER JOIN PRC_FILIAL ON tbl_exporta.CD_FILIAL = PRC_FILIAL.CD_FILIAL AND tbl_exporta.CD_EMP = PRC_FILIAL.CD_EMP	-- INNER JOIN INCLUSO      EM:  05/09/2022 , CHAMADO: FARMAGOI-33147 , Registrado na sub tarefa para P&D:  FARMAGOI-33184,  (RFS)						   
                     LEFT JOIN EST_PROD ON tbl_exporta.CD_EMP = EST_PROD.CD_EMP
                                           AND tbl_exporta.CD_PROD = EST_PROD.CD_PROD									   
										   
                WHERE(tbl_exporta.VLR_CONV_IT - CAST((tbl_exporta.VLR_CONV_IT * (RC_CLI_CONV.PERC_CONV / 100)) AS NUMERIC(18, 2))) > 0
                     AND rc_cli.cd_emp = 1;
         RETURN;
     END;


