IF EXISTS ( SELECT * FROM sys.objects  WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RC_DEB_CLI_PDV]')AND type IN(N'TF'))
	BEGIN
		DROP FUNCTION [dbo].[UFD_RC_DEB_CLI_PDV];
	END;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
	CREATE FUNCTION [dbo].[UFD_RC_DEB_CLI_PDV]
	(@CD_EMP INT, 
	 @CD_CLI INT, 
	 @FILIAL INT = 0)

/***ALTERAÇÃO DA ROTINA  [DBO].[UFD_RC_DEB_CLI]  POIS FOI ADICIONADO OUTRO PARAMETRO PARA NAO ALTERAR A ROTINA CAUSANDO TRANSTORNOS
FOI CRIADA NOVA ROTINA COM A NOVA FUNCIONALIDADE PARA VERSAO PDV 12.12.12.RC35 OU SUPERIOR NFCE 1.0.35 OU SUPERIOR
CRIADOR : MARCELO CÂNDIDO ***/

RETURNS @RC_DEB_CLI TABLE
(NR_FATURA         VARCHAR(20) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
 CD_EMP            INT, 
 CD_CLI            INT, 
 DT_LANCTO         DATETIME, 
 DT_FECHA          DATETIME, 
 DT_VENCTO         DATETIME, 
 DT_PRORROG        DATETIME, 
 DT_PAG            DATETIME, 
 JUROS_PAGOS_ANT   MONEY, 
 JUROS_PAGOS_AMORT MONEY, 
 PARCELA           VARCHAR(10) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
 VLR_LIQUIDO       MONEY, 
 VLR_ENCARGOS      MONEY, 
 VLR_TOTAL         MONEY, 
 DS_TP_LANC        VARCHAR(30) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
 CD_FILIAL         INT, 
 CD_RC_DEB         INT, 
 NR_ECF            INT, 
 CD_CX             INT, 
 TP_REG            INT, 
 PARC_COM_PGTO     INT, 
 AMORT_JUROS       INT, 
 VERBA_COMERCIAL   VARCHAR(10) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
 VLR_DESC          MONEY, 
 NR_DOC            VARCHAR(50) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
 PERC_DESC_PONTUAL MONEY, 
 TP_PERC_DESC      INT, 
 STS_DP            INT
)
AS
     BEGIN
         DECLARE @RS_FILIAL AS TABLE
         (CD_EMP    INT, 
          CD_FILIAL INT
         );
         IF @FILIAL = 0
             INSERT INTO @RS_FILIAL
                    SELECT CD_EMP, 
                           CD_FILIAL
                    FROM PRC_FILIAL;
             ELSE
             INSERT INTO @RS_FILIAL
                    SELECT PRC_FILIAL.CD_EMP, 
                           PRC_FILIAL.CD_FILIAL
                    FROM PRC_FILIAL
                         INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
                                                               AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
                    WHERE PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON IN
                    (
                        SELECT DISTINCT 
                               CD_GRP_ECON
                        FROM PRC_GRP_ECON_PRC_FILIAL AS PRC_GRP_ECON_PRC_FILIAL_1
                        WHERE CD_FILIAL = @FILIAL
                    );

         --FORMA DE QUITACAO DOS JUROS
         --0 - NÃO AMORTIZA JUROS
         --1 - AMORTIZA JUROS
         DECLARE @AMORT_JUROS INT;
         SET @AMORT_JUROS =
         (
             SELECT ISNULL(valor, 0)
             FROM prc_emp_config
             WHERE cd_chave LIKE 'FLAG_AMORT_JUROS_QUITACAO_CREDIARIO'
         );

         --@TP_ENVIO FORMA DE ENVIO DOS DEBITOS DO CLIENTE PARA LOJA
         --0 - ENVIA TITULOS EM ABERTO E COMPRAS EM ABERTO
         --1 - ENVIA SOMENTE TITULOS EM ABERTO 
         -- DESTA MANEIRA SERÃO ENVIADOS APENAS OS TITULOS QUANDO A REDE ATUAR COM FECHAMENTO IMEDIATO
         DECLARE @TP_ENVIO INT;
         SET @TP_ENVIO = ISNULL(
         (
             SELECT ISNULL(valor, 0)
             FROM prc_emp_config
             WHERE cd_chave LIKE 'FLAG_ENVIA_SOMENTE_TITULOS_CRED'
         ), 0);

         ------------------------------------------------------------------------------------
         --VALORES PARA O CAMPO TP_REG
         ------------------------------------------------------------------------------------
         --	0 = FATURA/CONTAS A RECEBER EM GERAL EM NOME DO CLIENTE;
         --	1 = PARCELAS COM VENCIMENTO FUTURO;
         --	2 = PARCELAS COM VENCIMENTO NO PROXIMO FECHAMENTO.
         INSERT INTO @RC_DEB_CLI
                SELECT RC_DEB.NR_FATURA, 
                       RC_DEB.CD_EMP, 
                       RC_DEB.CD_CLI, 
                       RC_DEB.DT_DP AS DT_LANC_CRED, 
                       NULL AS DT_FECHA_CRED, 
                       (CASE
                            WHEN RC_DEB.DT_PRORROG IS NULL
                            THEN RC_DEB.DT_VENCTO
                            ELSE RC_DEB.DT_PRORROG
                        END) AS DT_VENCTO_CRED, 
                       RC_DEB.DT_PRORROG, 
                       MAX(CASE
                               WHEN RC_CRED.VL_PGTO > 0
                               THEN RC_CRED.DT_PAG
                               ELSE NULL
                           END) AS DT_PAG, 
                       ISNULL(SUM(VL_JUR), 0) AS JUROS_PAGOS_ANT, 
                       ISNULL(
                (
                    SELECT SUM(VL_JUR)
                    FROM RC_CRED CT
                    WHERE CT.CD_EMP = RC_DEB.CD_EMP
                          AND CT.CD_FILIAL = RC_DEB.CD_FILIAL
                          AND CT.CD_RC_DEB = RC_DEB.CD_RC_DEB
                          AND CT.DT_PAG > MAX(CASE
                                                  WHEN RC_CRED.VL_PGTO > 0
                                                  THEN RC_CRED.DT_PAG
                                                  ELSE NULL
                                              END)
                ), 0) AS JUROS_PAGOS_AMORT, 
                       CAST(RC_DEB.PARC AS VARCHAR) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS PARC_CRED, 
                       RC_DEB.SLD_DP AS VLR_PARC_CRED, 
                (
                    SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)
                ) AS VLR_JUROS, 
                       RC_DEB.SLD_DP +
                (
                    SELECT DBO.UFD_RC_DEB_VL_JUR(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)
                ) - (RC_DEB.VLR_DESC - --Se houve desconto no titulo, abate os descontos concedidos nos pagamentos..
                     (CASE
                          WHEN RC_DEB.VLR_DESC > 0
                          THEN
                (
                    SELECT DBO.F_DESC_ABATIDO(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)
                )
                          ELSE 0
                      END)) AS VLR_TOTAL, 
                       'FATURA' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS DS_TP_LANC, 
                       RC_DEB.CD_FILIAL, 
                       RC_DEB.CD_RC_DEB, 
                       0 AS NR_ECF, 
                       0 AS CD_CX, 
                       0 AS TP, 
                       0 AS PARC_COM_PGTO, 
                       @AMORT_JUROS AS AMORT_JUROS,
                       CASE
                           WHEN CG_FND_VERBA_RC_DEB_DUP_NR_CTR.NR_DP IS NULL
                           THEN 'NAO'
                           ELSE 'SIM'
                       END COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS VERBA_COMERCIAL, 
                       VLR_DESC -
                (
                    SELECT DBO.F_DESC_ABATIDO(RC_DEB.CD_EMP, RC_DEB.CD_FILIAL, RC_DEB.CD_RC_DEB)
                ) VLR_DESC, 
                       RC_DEB.NR_DOC COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS NR_DOC, 
                       AVG(ISNULL(MD.PERC_DESC_PONTUAL, 0)) PERC_DESC_PONTUAL, 
                       MAX(ISNULL(MD.TP_PERC_DESC, 0)) TP_PERC_DESC, 
                       RC_DEB.STS_DP AS STS_DP
                FROM RC_DEB
                     LEFT OUTER JOIN CG_FND_VERBA_RC_DEB_DUP_NR_CTR ON RC_DEB.CD_EMP = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.CD_EMP
                                                                       AND RC_DEB.CD_FILIAL = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.CD_FILIAL
                                                                       AND RC_DEB.NR_DP = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.NR_DP
                                                                       AND RC_DEB.ANO = CG_FND_VERBA_RC_DEB_DUP_NR_CTR.ANO
                     INNER JOIN @RS_FILIAL F ON RC_DEB.CD_EMP = F.CD_EMP
                                                AND RC_DEB.CD_FILIAL = F.CD_FILIAL
                     LEFT JOIN RC_CRED ON RC_DEB.CD_EMP = RC_CRED.CD_EMP
                                          AND RC_DEB.CD_FILIAL = RC_CRED.CD_FILIAL
                                          AND RC_DEB.CD_RC_DEB = RC_CRED.CD_RC_DEB
                     LEFT JOIN RC_CLI_CREDIARIO CLI ON RC_DEB.CD_EMP = CLI.CD_EMP
                                                       AND RC_DEB.CD_CLI = CLI.CD_CLI
                     LEFT JOIN RC_MDL_VENDA Md ON CLI.CD_EMP = MD.CD_EMP
                                                  AND CLI.CD_MDL_VD = MD.CD_MDL_VD
                     LEFT JOIN RC_DEB_PAG ON dbo.RC_DEB.CD_EMP = dbo.RC_DEB_PAG.CD_EMP
                                             AND dbo.RC_DEB.CD_FILIAL = dbo.RC_DEB_PAG.CD_FILIAL
                                             AND dbo.RC_DEB.CD_RC_DEB = dbo.RC_DEB_PAG.CD_RC_DEB
                WHERE RC_DEB.CD_EMP = @CD_EMP
                      AND RC_DEB.CD_CLI = (CASE
                                               WHEN @CD_CLI = 0
                                               THEN RC_DEB.CD_CLI
                                               ELSE @CD_CLI
                                           END)
                      AND RC_DEB.STS_DP IN(0, 2, 5)
                     AND RC_DEB.SLD_DP > 0
                     AND (dbo.RC_DEB_PAG.CD_EMP IS NULL)
                GROUP BY RC_DEB.NR_FATURA, 
                         RC_DEB.CD_EMP, 
                         RC_DEB.CD_CLI, 
                         RC_DEB.DT_DP, 
                         RC_DEB.DT_VENCTO, 
                         RC_DEB.DT_PRORROG, 
                         RC_DEB.PARC, 
                         RC_DEB.SLD_DP, 
                         RC_DEB.CD_FILIAL, 
                         RC_DEB.CD_RC_DEB, 
                         RC_DEB.VLR_DESC, 
                         CG_FND_VERBA_RC_DEB_DUP_NR_CTR.NR_DP, 
                         RC_DEB.NR_DOC, 
                         RC_DEB.STS_DP;
         IF @TP_ENVIO = 0
             BEGIN
                 INSERT INTO @RC_DEB_CLI
                        SELECT CAST(PARC.CD_CTR AS VARCHAR) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
                               PARC.CD_EMP, 
                               PARC.CD_CLI, 
                               PARC.DT_LANC_CRED, 
                               PARC.DT_FECHA_CRED, 
                               PARC.DT_VENCTO_CRED, 
                               NULL DT_PRORROG, 
                               NULL AS DT_PAG, 
                               0 AS JUROS_PAGOS_ANT, 
                               0 AS JUROS_PAGOS_AMORT, 
                               CAST(PARC.PARC_CRED AS VARCHAR) + '/' + CAST(PARC.QT_PARC_VD_CRED AS VARCHAR) AS PARC_CRED, 
                               PARC.SLD_PARC_CRED AS VLR_PARC_CRED, 
                               0 AS VLR_JUROS, 
                               PARC.SLD_PARC_CRED AS VLR_TOTAL, 
                               TP_LANC.DS_TP_LANC COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, 
                               PDV_VD.CD_FILIAL, 
                               NULL AS CD_RC_DEB, 
                               ISNULL(PDV_VD.NR_ECF, 0) AS NR_ECF, 
                               ISNULL(PDV_VD.CD_CX, 0) AS CD_CX, 
                               (CASE
                                    WHEN PARC.DT_FECHA_CRED <= CLI.DT_PROX_FECHA
                                    THEN 2
                                    ELSE 1
                                END) AS TP,
                               CASE
                                   WHEN ISNULL(
                        (
                            SELECT TOP 1 RC_CREDIARIO_PARC_CRED.CD_CTR
                            FROM RC_CREDIARIO_PARC_CRED
                            WHERE PARC.CD_CTR = RC_CREDIARIO_PARC_CRED.CD_CTR
                        ), 0) > 0
                                   THEN 1
                                   ELSE 0
                               END PARC_COM_PGTO, 
                               @AMORT_JUROS AS AMORT_JUROS, 
                               'NAO' COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS VERBA_COMERCIAL, 
                               0, 
                               PARC.NR_FATURA COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NR_DOC, 
                               ISNULL(MD.PERC_DESC_PONTUAL, 0) PERC_DESC_PONTUAL, 
                               ISNULL(MD.TP_PERC_DESC, 0) TP_PERC_DESC, 
                               PARC.STS_LANC_CRED AS STS_DP
                        FROM RC_CREDIARIO_PARC PARC
                             INNER JOIN RC_CLI_CREDIARIO CLI ON PARC.CD_EMP = CLI.CD_EMP
                                                                AND PARC.CD_CLI = CLI.CD_CLI
                             INNER JOIN RC_MDL_VENDA Md ON CLI.CD_EMP = MD.CD_EMP
                                                           AND CLI.CD_MDL_VD = MD.CD_MDL_VD
                             INNER JOIN RC_CREDIARIO_TP_LANC TP_LANC ON PARC.CD_TP_LANC = TP_LANC.CD_TP_LANC
                             INNER JOIN RC_CREDIARIO_PARC_PDV_VD PARC_PDV ON PARC.CD_CTR = PARC_PDV.CD_CTR
                             INNER JOIN PDV_VD ON PARC_PDV.CD_EMP = PDV_VD.CD_EMP
                                                  AND PARC_PDV.CD_FILIAL = PDV_VD.CD_FILIAL
                                                  AND PARC_PDV.CD_VD = PDV_VD.CD_VD
                             INNER JOIN @RS_FILIAL F ON PARC_PDV.CD_EMP = F.CD_EMP
                                                        AND PARC_PDV.CD_FILIAL = F.CD_FILIAL
                        WHERE PDV_VD.ST_VD = 0
                              AND PARC.CD_EMP = @CD_EMP
                              AND PARC.CD_CLI = (CASE
                                                     WHEN @CD_CLI = 0
                                                     THEN PARC.CD_CLI
                                                     ELSE @CD_CLI
                                                 END)
                              AND PARC.STS_LANC_CRED IN(0, 2, 5)
                             AND PARC.SLD_PARC_CRED > 0;
         END;
         UPDATE @RC_DEB_CLI
           SET 
               VLR_DESC = 0.00
         WHERE VLR_DESC < 0;
         RETURN;
     END;