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

CREATE FUNCTION [DBO].[UFD_RC_VD_CONV_EXPORTA_DUP]                               
                                (@CD_EMP INT,        
                                @CD_FILIAL INT,      
                                @CD_CONV VARCHAR(100), 
                                @DT_INI DATETIME,    
                                @CD_RC_DEB INT)     

RETURNS  @UFD_RC_VD_CONV_EXPORTA TABLE  (
                        CD_EMP INT,
                        CD_CLI INT,
                        NOME VARCHAR(100),
                        CPF VARCHAR(20), 
                        RZ_CONV VARCHAR(100),
                        CNPJ_CONV VARCHAR(20),
                        CD_FILIAL INT,
                        CD_CONV INT,
                        MATRICULA VARCHAR(30), 
                        CUPOM INT,
                        DT_VD DATETIME,
                        VLR_CONV MONEY,                                                    
                        CD_PROD INT,
                        DS_PROD VARCHAR(100),
                        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(20),
                        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)    

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,
			sum(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,
			MAX (ISNULL(PDV_VD_IT_REC.CD_MEDICO_REC,0)) AS CD_MEDICO_REC,
			SUM (PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT),
			SUM (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,
			ARV.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 RC_VD_CONV 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  
                
		INNER JOIN RC_VD_CONV_RC_DEB ON 
			RC_VD_CONV_RC_DEB.CD_EMP = RC_VD_CONV.CD_EMP AND 
			RC_VD_CONV_RC_DEB.CD_CTR = RC_VD_CONV.CD_CTR    

		INNER JOIN RC_DEB ON
			RC_DEB.CD_EMP=RC_VD_CONV_RC_DEB.CD_EMP AND
			RC_DEB.CD_FILIAL=RC_VD_CONV_RC_DEB.CD_FILIAL AND
			RC_DEB.CD_RC_DEB =RC_VD_CONV_RC_DEB.CD_RC_DEB           
                        
		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

		INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
		ARV.CD_EMP=PDV_VD_IT.CD_EMP AND
		ARV.CD_PROD=PDV_VD_IT.CD_PROD

	WHERE   
			RC_DEB.CD_EMP= @CD_EMP
			AND RC_DEB.CD_FILIAL= @CD_FILIAL
			AND RC_DEB.DT_DP = @DT_INI 
			AND RC_DEB.CD_RC_DEB = @CD_RC_DEB
			AND PDV_VD_IT.ST_IT=1
	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,			
			PDV_VD_IT.CD_PROD,
			RC_CLI_CONV.CD_MDL_VD,
			RC_CLI_CONV_RC_CLI.FLAG_N_EXP_VD_LAY,
			ARV.CD_ARV_MERC_LINHA  ,PDV_VD_IT.CD_BARRA  
			       
--------------------------------------------------------------------------------------------------------------------------
-- 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)

--------------------------------------------------------------------------------------------------------------------------
-- CORRIGINDO AS DIFEREN?AS NO ITEM DE MAIOR VALOR NA VENDA
--------------------------------------------------------------------------------------------------------------------------
UPDATE @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
       
------------------------------------------------------------------------------------------------------------------------
-- 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 
--------------------------------------------------------------------------------------------------------------------------
-- 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,
	TBL_EXPORTA.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,
	TBL_EXPORTA.VLR_NCC_TOTAL, 
	TBL_EXPORTA.VLR_NCC_IT,
	TBL_EXPORTA.CD_ARV_MERC_LINHA,
	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	
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  
	LEFT JOIN EST_PROD ON
		TBL_EXPORTA.CD_EMP  = EST_PROD.CD_EMP AND
		TBL_EXPORTA.CD_PROD = EST_PROD.CD_PROD
	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)						   
WHERE
       (TBL_EXPORTA.VLR_CONV_IT - CAST((TBL_EXPORTA.VLR_CONV_IT * (RC_CLI_CONV.PERC_CONV / 100)) AS NUMERIC(18,2))) > 0
RETURN          


END           