IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
            WHERE (O.NAME = 'BALAN_CONS_GERENCIAL')
              AND TYPE = 'TF'
	           AND U.UID  = O.UID
	           AND U.NAME = USER)
                                                                                                                      
  DROP FUNCTION BALAN_CONS_GERENCIAL
GO
                                                                                                                       
CREATE FUNCTION BALAN_CONS_GERENCIAL
(
  @P_CDEMPRESA    VARCHAR(310),
  @P_CDFILIAL     VARCHAR(3000),
  @P_CDCENTCUST   VARCHAR(3000),
  @P_CONTINI      VARCHAR(20),
  @P_CONTFIN      VARCHAR(20),
  @P_DTINI        DATETIME,
  @P_DTFIN        DATETIME,
  @P_CDNVCONTCTBL VARCHAR(01)) 
                                                                                                                       
  RETURNS @TABLE TABLE(
    CDCONTCTBL    VARCHAR(20),
    NMCONTCTBL    VARCHAR(40),
    CDNVCONTCTBL  VARCHAR(01),
    CDFILIAL      VARCHAR(04),
    NMFILIAL      VARCHAR(30),
    VRTOTCRED     DOUBLE PRECISION,    
    VRTOTDEBI     DOUBLE PRECISION,
    VRSALDINI     DOUBLE PRECISION,    
    VRSALDFIN     DOUBLE PRECISION)
AS
BEGIN
    INSERT @TABLE                                                                                                                   
    SELECT LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL,
           LISTA_FILIAL.CDFILIAL, LISTA_FILIAL.NMFILIAL, 
           SUM(LISTA_LANCCONT.CREDITO), SUM(LISTA_LANCCONT.DEBITO),
           LISTA_SALDCCUS.VRSALDINI,
           (LISTA_SALDCCUS.VRSALDINI + SUM(LISTA_LANCCONT.CREDITO) + SUM(LISTA_LANCCONT.DEBITO)) AS VRSALDFIN

      FROM (SELECT CDCONTCTBL, NMCONTCTBL, CDNVCONTCTBL
              FROM CONTCTBL
             WHERE (CDCONTCTBL BETWEEN @P_CONTINI AND @P_CONTFIN)
               AND ((CDNVCONTCTBL <= @P_CDNVCONTCTBL) OR ('0' = @P_CDNVCONTCTBL))
             /*ORDER BY CDCONTCTBL*/) LISTA_CONTCTBL,
  
           (SELECT CDFILIAL, NMFILIAL
              FROM FILIAL
             WHERE ((CHARINDEX(CDFILIAL, @P_CDFILIAL) > 0) OR ('T' = @P_CDFILIAL))
             /*ORDER BY CDFILIAL*/) LISTA_FILIAL,
             
           (SELECT CDCENTCUST, NMCENTCUST
              FROM CENTCUST
             WHERE ((CHARINDEX(CDCENTCUST, @P_CDCENTCUST) > 0) OR ('T' = @P_CDCENTCUST))
             /*ORDER BY CDCENTCUST*/) LISTA_CENTCUST,
    
           (SELECT ISNULL(SUM(VRSALDCCUS), 0) AS VRSALDINI, CDCONTCTBL
              FROM SALDCCUS SALD
             WHERE (SALD.CDEMPRESA  IN (SELECT CDEMPRESA
                                          FROM EMPRESA
                                         WHERE ((CHARINDEX(CDEMPRESA, @P_CDEMPRESA) > 0) OR ('T' = @P_CDEMPRESA))))
               AND (SALD.CDFILIAL   IN (SELECT CDFILIAL
                                          FROM FILIAL
                                         WHERE ((CHARINDEX(CDFILIAL, @P_CDFILIAL) > 0) OR ('T' = @P_CDFILIAL))))
               AND (SALD.CDCENTCUST IN (SELECT CDCENTCUST
                                          FROM CENTCUST
                                         WHERE ((CHARINDEX(CDCENTCUST, @P_CDCENTCUST) > 0) OR ('T' = @P_CDCENTCUST))))
               AND (SALD.CDCONTCTBL BETWEEN @P_CONTINI AND @P_CONTFIN)
               AND (SALD.DTSALDCCUS = (SELECT MAX(DTSALDCCUS)
                                         FROM SALDCCUS S
                                        WHERE (S.CDEMPRESA  = SALD.CDEMPRESA)
                                          AND (S.CDFILIAL   = SALD.CDFILIAL)
                                          AND (S.CDCENTCUST = SALD.CDCENTCUST)
                                          AND (S.CDCONTCTBL = SALD.CDCONTCTBL)
                                          AND (S.DTSALDCCUS < @P_DTINI)))
            GROUP BY CDCONTCTBL) LISTA_SALDCCUS,
  
           (SELECT ISNULL(SUM(ROUND((CASE L.VRLANCCUST WHEN ABS(L.VRLANCCUST) THEN L.VRLANCCUST ELSE 0 END),2)),0) AS CREDITO,
                   ISNULL(SUM(ROUND((CASE L.VRLANCCUST WHEN ABS(L.VRLANCCUST) THEN 0 ELSE L.VRLANCCUST END),2)),0) AS DEBITO,
                   LC.CDCONTCTBL
              FROM LANCCCUSTO L, LANCCONT LC
             WHERE (L.CDEMPRESA  IN (SELECT CDEMPRESA
                                       FROM EMPRESA
                                      WHERE ((CHARINDEX(CDEMPRESA, @P_CDEMPRESA) > 0) OR ('T' = @P_CDEMPRESA))))
               AND (L.CDFILIAL   IN (SELECT CDFILIAL
                                       FROM FILIAL
                                      WHERE ((CHARINDEX(CDFILIAL, @P_CDFILIAL) > 0) OR ('T' = @P_CDFILIAL))))
               AND (L.CDCENTCUST IN (SELECT CDCENTCUST
                                       FROM CENTCUST
                                      WHERE ((CHARINDEX(CDCENTCUST, @P_CDCENTCUST) > 0) OR ('T' = @P_CDCENTCUST))))
               AND (LC.CDCONTCTBL BETWEEN @P_CONTINI AND @P_CONTFIN)
               AND (L.CDEMPRESA  = LC.CDEMPRESA)
               AND (L.NRLANCCONT = LC.NRLANCCONT)
               AND (L.NRSEQLANC  = LC.NRSEQLANC)
               AND (L.DTCOMPCUSTO BETWEEN @P_DTINI AND @P_DTFIN)
             GROUP BY LC.CDCONTCTBL) LISTA_LANCCONT  

     WHERE LISTA_LANCCONT.CDCONTCTBL LIKE LISTA_CONTCTBL.CDCONTCTBL + '%'
       AND LISTA_CONTCTBL.CDCONTCTBL = LISTA_SALDCCUS.CDCONTCTBL
     GROUP BY LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL,
              LISTA_FILIAL.CDFILIAL, LISTA_FILIAL.NMFILIAL, 
              LISTA_SALDCCUS.VRSALDINI;  
  RETURN; 
 END   
GO