IF EXISTS
(SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[UFD_RC_ADM_CARTAO_MOV_RELATORIO]')
          AND type IN(N'FN', N'IF', N'TF', N'FS', N'FT') )
    
DROP FUNCTION [dbo].[UFD_RC_ADM_CARTAO_MOV_RELATORIO];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*
SELECT CD_EMP
	   , TP_TRANSA
	   , CD_CLI
	   , NM_FANT
	   , DT_VENCTO
	   , VLR_SLD
	   , CD_CNT
	   , NR_CNT
	   , NR_CNT_CONTABIL 
	  FROM 
		  UFD_RC_ADM_CARTAO_MOV_RELATORIO (1,1,'2009-01-01','2009-01-20',0) ORDER BY NM_FANT, TP_TRANSA

*/
CREATE FUNCTION [dbo].[UFD_RC_ADM_CARTAO_MOV_RELATORIO]
(@CD_EMP      INT,
 @CD_GRP_ECON VARCHAR(100),
 @DT_INI      DATETIME,
 @DT_FIM      DATETIME,
 @TP_DATA     INT
)
RETURNS @LANC_EXTRATO TABLE
(CD_EMP          INT,
 CD_FILIAL       INT,
 CD_GRP_ECON     VARCHAR(100),
 NM_GRP_ECON     VARCHAR(100),
 CD_CLI          INT,
 NM_FANT         VARCHAR(100),
 DT_VENCTO       DATETIME,
 DT_LANC         DATETIME,
 VLR_SLD         MONEY,
 CD_CNT          INT,
 NR_CNT          VARCHAR(100),
 TP_TRANSA       INT,
 NR_CNT_CONTABIL VARCHAR(100)
)
AS
     BEGIN
         DECLARE @VLR_SLD MONEY;
         DECLARE @RS_MOV_CARTAO TABLE
         (CD_EMP      INT,
          CD_FILIAL   INT,
          CD_GRP_ECON VARCHAR(100),
          NM_GRP_ECON VARCHAR(100),
          CD_CLI      INT,
          NM_FANT     VARCHAR(100),
          DT_VENCTO   DATETIME,
          DT_LANC     DATETIME,
          VLR_SLD     MONEY,
          CD_CNT      INT,
          TP_TRANSA   INT
         );
         INSERT INTO @RS_MOV_CARTAO
                SELECT s.CD_EMP
                     , 0 AS CD_FILIAL
                     , CD_GRP_ECON
                     , NM_GRP_ECON
                     , s.CD_CLI
                     , NM_FANT
                     , @DT_INI - 1
                     , @DT_INI - 1
                     ,
                (
                    SELECT DBO.UFD_RC_ADM_CARTAO_MOV_SLD(s.CD_CLI, s.CD_GRP_ECON, @DT_INI, @TP_DATA, 0)
                ) AS VLR_SLD
                     , s.CD_CNT
                     , -1
                FROM
                (
                    SELECT RC_ADM_CARTAO_MOV.CD_EMP
                         , 0 AS CD_FILIAL
                         , RC_ADM_CARTAO_MOV.CD_GRP_ECON
                         , PRC_GRP_ECON.NM_GRP_ECON
                         , RC_ADM_CARTAO_MOV.CD_CLI
                         , ISNULL(CD_CNT, 0) AS CD_CNT
                    FROM RC_ADM_CARTAO_MOV
                         LEFT OUTER JOIN RC_ADM_CARTAO_RC_CLI_BC_CONTAS ON RC_ADM_CARTAO_MOV.CD_CLI = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_CLI
                                                                           AND RC_ADM_CARTAO_MOV.CD_GRP_ECON = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_GRP_ECON
                         INNER JOIN PRC_GRP_ECON ON 
					   RC_ADM_CARTAO_MOV.CD_GRP_ECON = PRC_GRP_ECON.CD_GRP_ECON
                    WHERE 
				RC_ADM_CARTAO_MOV.CD_GRP_ECON IN
                    (
                        SELECT ID
                        FROM UFD_PRM_IN_STRING(@CD_GRP_ECON)
                    )
                         AND RC_ADM_CARTAO_MOV.MODALIDADE = 0
                ) s
                INNER JOIN RC_CLI ON s.CD_EMP = RC_CLI.CD_EMP
                                     AND s.CD_CLI = RC_CLI.CD_CLI
                GROUP BY s.CD_EMP
                       , s.CD_GRP_ECON
                       , s.NM_GRP_ECON
                       , s.CD_CLI
                       , s.CD_CNT
                       , NM_FANT
                UNION ALL
                SELECT v.CD_EMP
                     , 0 AS CD_FILIAL
                     , CD_GRP_ECON
                     , NM_GRP_ECON
                     , v.CD_CLI
                     , NM_FANT
                     , v.DT_VENCTO
                     , v.DT_LANC
                     , isnull(v.VLR_DEB, 0) - ISNULL(v.VLR_CRED, 0)
                     , v.CD_CNT
                     , TP_LANC
                FROM
                (
                    SELECT RC_ADM_CARTAO_MOV.CD_EMP
                         , 0 AS CD_FILIAL
                         , RC_ADM_CARTAO_MOV.CD_GRP_ECON
                         , PRC_GRP_ECON.NM_GRP_ECON
                         , RC_ADM_CARTAO_MOV.CD_CLI
                         , DT_VENCTO
                         , DT_LANC
                         , ISNULL(CD_CNT, 0) AS CD_CNT
                         , CASE
                               WHEN TP_LANC = 0
                               THEN VLR_LANC
                               ELSE 0
                           END AS VLR_CRED
                         , CASE
                               WHEN TP_LANC = 1
                               THEN VLR_LANC
                               ELSE 0
                           END AS VLR_DEB
                         , TP_LANC
                         , CD_CTR
                    FROM RC_ADM_CARTAO_MOV
                         LEFT OUTER JOIN RC_ADM_CARTAO_RC_CLI_BC_CONTAS ON 
					RC_ADM_CARTAO_MOV.CD_CLI = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_CLI
                         AND RC_ADM_CARTAO_MOV.CD_GRP_ECON = RC_ADM_CARTAO_RC_CLI_BC_CONTAS.CD_GRP_ECON
                         INNER JOIN PRC_GRP_ECON ON 
					RC_ADM_CARTAO_MOV.CD_GRP_ECON = PRC_GRP_ECON.CD_GRP_ECON
                    WHERE RC_ADM_CARTAO_MOV.CD_GRP_ECON IN
                    (
                        SELECT ID
                        FROM UFD_PRM_IN_STRING(@CD_GRP_ECON)
                    )
                         AND CASE
                                 WHEN @TP_DATA = 0
                                 THEN RC_ADM_CARTAO_MOV.DT_LANC
                                 ELSE RC_ADM_CARTAO_MOV.DT_VENCTO
                             END BETWEEN @DT_INI AND @DT_FIM
                         AND RC_ADM_CARTAO_MOV.MODALIDADE = 0
                ) v
                INNER JOIN RC_CLI ON v.CD_EMP = RC_CLI.CD_EMP
                                     AND v.CD_CLI = RC_CLI.CD_CLI;
							  
	    update  r
		  set   
			 cd_filial=a.cd_filial
		  from
			 @RS_MOV_CARTAO r	  
			 join PRC_GRP_ECON_PRC_FILIAL a on 
			 r.cd_emp	   = a.cd_emp and
			 r.CD_GRP_ECON   = a.CD_GRP_ECON
			 JOIN PRC_FILIAL b on 
			 a.cd_emp	   = b.cd_emp and
			 a.cd_filial = b.cd_filial
		  where b.FILIAL_MTZ_GRP_ECON=1

         INSERT INTO @LANC_EXTRATO
                SELECT m.CD_EMP
                     , m.CD_FILIAL
                     , m.CD_GRP_ECON
                     , m.NM_GRP_ECON
                     , m.CD_CLI
                     , m.NM_FANT
                     , m.DT_VENCTO
                     , m.DT_LANC
                     , VLR_SLD
                     , m.CD_CNT
                     , ISNULL(BC_BANCO.DS_BC, '')+' '+ISNULL(BC_CONTAS.NR_CNT, '') AS NR_CNT
                     , TP_TRANSA
                     , ISNULL(RC_CLI_CNT_CTB.CD_CTB_CNT, '') AS CD_CTB_CNT
                FROM @RS_MOV_CARTAO m
                     LEFT OUTER JOIN BC_CONTAS ON m.CD_EMP = BC_CONTAS.CD_EMP
                                                  AND m.CD_CNT = BC_CONTAS.CD_CNT
                     LEFT OUTER JOIN BC_BANCO ON BC_CONTAS.CD_BC = BC_BANCO.CD_BC
                     LEFT OUTER JOIN RC_CLI_CNT_CTB ON RC_CLI_CNT_CTB.CD_EMP = m.CD_EMP
                                                       AND RC_CLI_CNT_CTB.CD_CLI = m.CD_CLI
                WHERE ISNULL(RC_CLI_CNT_CTB.TP_CNT_CTB, 0) = 0;
         RETURN;
     END;
GO