
IF OBJECT_ID('UFD_RC_VD_CONV_EXPORTA') IS NOT NULL
    BEGIN
        DROP FUNCTION UFD_RC_VD_CONV_EXPORTA;
END;
GO
/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 202200720
'Job.................:  
'Nome................:  [UFD_RC_VD_CONV_EXPORTA]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 
'Dt Ult Alteracão....: 2022-07-20
'Alterado por........: Marcos barros
'Criado por..........: 
'=======================================================================================

 */

-- select * from UFD_RC_VD_CONV_EXPORTA (2762,'2012-05-17','2012-05-17') where cd_plano_venda = 3
CREATE FUNCTION  [dbo].[UFD_RC_VD_CONV_EXPORTA]
(@CD_CONV VARCHAR(4000), --Codigo do conv?nio
 @DT_INI  DATETIME, --Data Inicial
 @DT_FIM  DATETIME
)	    --Data Final
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
)
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
         );
         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
                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;
--------------------------------------------------------------------------------------------------------------------------
-- 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,
                       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,            -- 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
                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

												   
                     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;




