IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
            WHERE (O.NAME = 'BALAN_CONS_OFICIAL')
              AND TYPE = 'TF'
	           AND U.UID  = O.UID
	           AND U.NAME = USER)
                                                                                                                       
  DROP FUNCTION BALAN_CONS_OFICIAL
GO
                                                                                                                       
CREATE FUNCTION BALAN_CONS_OFICIAL
(
  @P_CDEMPRESA    VARCHAR(310),
  @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),
    VRTOTCRED     DOUBLE PRECISION,
    VRTOTDEBI     DOUBLE PRECISION,
    VRSALDINI     DOUBLE PRECISION,
    VRSALDFIN     DOUBLE PRECISION
  )
AS
BEGIN
    INSERT @TABLE
    SELECT *
    FROM
       (SELECT LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL,
              SUM(ISNULL(LISTA_LANCCONT.CREDITO, 0)) AS CREDITO, SUM(ISNULL(LISTA_LANCCONT.DEBITO, 0)) AS DEBITO, ISNULL(LISTA_SALDEMPR.VRSALDINI, 0) AS VRSALDINI,
              (ISNULL(LISTA_SALDEMPR.VRSALDINI, 0) + SUM(ISNULL(LISTA_LANCCONT.CREDITO, 0)) + SUM(ISNULL(LISTA_LANCCONT.DEBITO, 0))) 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))
                  AND (IDCONTCCUS <> 'R')
                /*ORDER BY CDCONTCTBL*/) LISTA_CONTCTBL LEFT JOIN (SELECT SUM(ROUND((CASE ISNULL(L.VRLANCAMENT, 0) WHEN ABS(L.VRLANCAMENT) THEN 0 ELSE L.VRLANCAMENT END), 2)) AS DEBITO,
                                                                          SUM(ROUND((CASE ISNULL(L.VRLANCAMENT, 0) WHEN ABS(L.VRLANCAMENT) THEN L.VRLANCAMENT ELSE 0 END), 2)) AS CREDITO,
                                                                          L.CDCONTCTBL
                                                                     FROM LANCCONT L, CONTCTBL CO
                                                                    WHERE (L.CDEMPRESA IN (SELECT CDEMPRESA
                                                                                             FROM EMPRESA
                                                                                            WHERE ((CHARINDEX(CDEMPRESA, @P_CDEMPRESA) > 0) OR ('T' = @P_CDEMPRESA))))
                                                                      AND (L.DTLANCCONT BETWEEN @P_DTINI   AND @P_DTFIN)
                                                                      AND (L.CDCONTCTBL BETWEEN @P_CONTINI AND @P_CONTFIN)
                                                                      AND (CO.IDCONTCCUS <> 'R')
                                                                      AND (L.CDCONTCTBL = CO.CDCONTCTBL)
                                                                    GROUP BY L.CDCONTCTBL) LISTA_LANCCONT ON LISTA_LANCCONT.CDCONTCTBL LIKE LISTA_CONTCTBL.CDCONTCTBL + '%'


                                                        LEFT JOIN (SELECT SUM(ISNULL(SALDEMPR.VRSALDEMPR, 0)) AS VRSALDINI, SALDEMPR.CDCONTCTBL
                                                                     FROM SALDEMPR, CONTCTBL
                                                                    WHERE (SALDEMPR.CDCONTCTBL BETWEEN @P_CONTINI AND @P_CONTFIN)
                                                                      AND (SALDEMPR.CDCONTCTBL = CONTCTBL.CDCONTCTBL)
                                                                      AND (CONTCTBL.IDCONTCCUS <> 'R')
                                                                      AND (SALDEMPR.CDEMPRESA IN (SELECT CDEMPRESA
                                                                                                    FROM EMPRESA
                                                                                                   WHERE ((CHARINDEX(CDEMPRESA, @P_CDEMPRESA) > 0) OR ('T' = @P_CDEMPRESA))))
                                                                      AND (SALDEMPR.DTSALDEMPR = (SELECT MAX(S.DTSALDEMPR)
                                                                                                    FROM SALDEMPR S
                                                                                                   WHERE (S.CDCONTCTBL = SALDEMPR.CDCONTCTBL)
                                                                                                     AND (S.CDEMPRESA IN (SELECT CDEMPRESA
                                                                                                                             FROM EMPRESA
                                                                                                                            WHERE ((CHARINDEX(CDEMPRESA, @P_CDEMPRESA) > 0)
                                                                                                                               OR  ('T' = @P_CDEMPRESA))))
                                                                      AND (S.DTSALDEMPR < @P_DTINI)))
                                                                      GROUP BY SALDEMPR.CDCONTCTBL) LISTA_SALDEMPR ON LISTA_SALDEMPR.CDCONTCTBL = LISTA_CONTCTBL.CDCONTCTBL
        GROUP BY LISTA_CONTCTBL.CDCONTCTBL,   LISTA_CONTCTBL.NMCONTCTBL,
                 LISTA_CONTCTBL.CDNVCONTCTBL, LISTA_SALDEMPR.VRSALDINI) TABELA
    WHERE CREDITO  <> 0
       OR DEBITO   <> 0
       OR VRSALDINI <> 0
       OR VRSALDFIN <> 0
    ORDER BY TABELA.CDCONTCTBL;
  RETURN;
 END
GO