IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'UFD_CG_EXTRATO_FORN_SLD_ANT_GERAL')
	DROP FUNCTION [dbo].[UFD_CG_EXTRATO_FORN_SLD_ANT_GERAL]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
--select * from UFD_CG_EXTRATO_FORN_SLD_ANT_GERAL (1,'2016-01-01')
CREATE FUNCTION [dbo].[UFD_CG_EXTRATO_FORN_SLD_ANT_GERAL] (@CD_EMP  INT, 
             @DATA  DATETIME) 

RETURNS @RS_RETORNO TABLE ( DT_LANCTO DATETIME,
       CD_FORN  INT,
       VERBA  VARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS,
       HIST  VARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS,
       NEGOCIADO   MONEY,
       CREDITO  MONEY,
       DEBITO  MONEY,
       SLD_USO  MONEY,
       SLD_RECB MONEY,
       DS_FND_VERBA_TIPO VARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS)
WITH ENCRYPTION AS
BEGIN

 INSERT INTO @RS_RETORNO

 SELECT 
  @DATA DATA,
  CD_FORN,
  'SALDO ANTERIOR A ' + CONVERT(VARCHAR,@DATA, 103)  VERBA,
  'SALDO ANTERIOR A ' + CONVERT(VARCHAR,@DATA, 103)  HIST,
  ISNULL(SUM(VLR_LANC),0) AS NEGOCIADO,
  ISNULL(SUM(CREDITO),0) CREDITO,
  ISNULL(SUM(DEBITO),0) DEBITO,
  ISNULL(SUM(VLR_LANC) - SUM(DEBITO),0) AS SLD_USO,
  ISNULL(SUM(VLR_LANC) - SUM(CREDITO),0) AS SLD_RECB,
  ISNULL(DS_FND_VERBA_TIPO,'') AS DS_FND_VERBA_TIPO
 FROM
  (

   SELECT
    V.CD_FORN,
    0 CREDITO,
    0 DEBITO,
    SUM(VLR_LANC - VLR_GLOSA) VLR_LANC,
    A.DS_FND_VERBA_TIPO
   FROM 
    CG_FND_VERBA V 
    INNER JOIN CG_FND_VERBA_TIPO A ON
    V.CD_FND_VERBA_TIPO = A.CD_FND_VERBA_TIPO
   WHERE
    V.CD_EMP=@CD_EMP
    AND V.DT_LANCTO < @DATA
    AND V.STATUS=0
   GROUP BY 
    V.CD_FORN,
    A.DS_FND_VERBA_TIPO
   UNION ALL
   SELECT
    V.CD_FORN,
    SUM(R.VLR_RECB) AS CREDITO,
    0 AS DEBITO, 
    0 VLR_LANC,
    A.DS_FND_VERBA_TIPO
   FROM     
    CG_FND_VERBA V
    INNER JOIN  CG_FND_VERBA_RECB R ON
    V.CD_EMP = R.CD_EMP AND
    V.CD_FND_VERBA = R.CD_FND_VERBA 
    INNER JOIN CG_FND_VERBA_TIPO A ON
    V.CD_FND_VERBA_TIPO = A.CD_FND_VERBA_TIPO   
   WHERE
    V.CD_EMP=@CD_EMP
    AND R.STATUS=0
    AND R.DT_RECEB < @DATA
   GROUP BY 
    V.CD_FORN,
    A.DS_FND_VERBA_TIPO
   UNION ALL
   SELECT
    V.CD_FORN,
    0 AS CREDITO,
    SUM(S.VLR_SAQUE) AS DEBITO,
    0 VLR_LANC,
    A.DS_FND_VERBA_TIPO
   FROM 
    CG_FND_VERBA V
    INNER JOIN CG_FND_VERBA_SAQUE S ON
    V.CD_EMP = S.CD_EMP AND
    V.CD_FND_VERBA = S.CD_FND_VERBA
    INNER JOIN CG_FND_VERBA_TIPO A ON
    V.CD_FND_VERBA_TIPO = A.CD_FND_VERBA_TIPO
   WHERE
    V.CD_EMP=@CD_EMP
    AND S.STATUS=0
    AND S.DT_SAQUE < @DATA
   GROUP BY
    V.CD_FORN,
    A.DS_FND_VERBA_TIPO
   UNION ALL
   SELECT
    V.CD_FORN,
    0 AS CREDITO,
    SUM(P.VLR_VERBA_TOT) AS DEBITO,
    0 VLR_LANC,
    A.DS_FND_VERBA_TIPO
   FROM 
    CG_FND_VERBA V
    INNER JOIN CG_FND_VERBA_PROD P ON
    V.CD_EMP = P.CD_EMP AND
    V.CD_FND_VERBA = P.CD_FND_VERBA
    INNER JOIN CG_FND_VERBA_TIPO A ON
    V.CD_FND_VERBA_TIPO = A.CD_FND_VERBA_TIPO
   WHERE
    V.CD_EMP=@CD_EMP
    AND P.DT_CAD < @DATA
   GROUP BY
    V.CD_FORN,
    A.DS_FND_VERBA_TIPO
  ) TBL 
 GROUP BY
  CD_FORN,
  DS_FND_VERBA_TIPO
 RETURN
END

GO
