CREATE OR REPLACE FUNCTION VALOR_SALDO_CTB(P_CONTA IN VARCHAR2,
                                           P_CDEMPRESA IN VARCHAR2,
                                           P_ID    IN VARCHAR2,
                                           P_DATASALDO IN DATE) RETURN NUMBER
   IS
      V_RESULT NUMBER;
   BEGIN
      V_RESULT := 0;
      BEGIN
         /* SALDO COMUM */
         SELECT VALOR
           INTO V_RESULT
           FROM (SELECT NVL((SUM(ROUND(S.VRSALDEMPR, 2))), 0) VALOR
                   FROM SALDEMPR S
                  WHERE S.CDCONTCTBL = P_CONTA
                    AND ((INSTR(P_CDEMPRESA, S.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))
                    AND S.DTSALDEMPR = P_DATASALDO
                    AND NOT EXISTS (SELECT 1
                                      FROM LANCCONT L
                                     WHERE L.CDEMPRESA = S.CDEMPRESA
                                       AND L.CDCONTCTBL LIKE S.CDCONTCTBL ||'%'
                                       AND L.IDLANCINTE IN ('T', 'F')
                                       AND L.DTLANCCONT = LAST_DAY(P_DATASALDO))
                 HAVING NVL((SUM(ROUND(S.VRSALDEMPR, 2))), 0) <> 0

                  UNION

                 /* SALDO ANTES DOS ENCERRAMENTOS */
                 SELECT NVL((SUM(ROUND(S.VRSALDOTRANSF, 2))), 0) VALOR
                   FROM SALDOTRANSF S
                  WHERE S.CDCONTCTBL = P_CONTA
                    AND ((INSTR(P_CDEMPRESA, S.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))
                    AND S.DTSALDOTRANSF = P_DATASALDO
                    AND EXISTS (SELECT 1
                                  FROM LANCCONT L
                                 WHERE L.CDEMPRESA = S.CDEMPRESA
                                   AND L.CDCONTCTBL LIKE S.CDCONTCTBL ||'%'
                                   AND L.IDLANCINTE IN ('T', 'F')
                                   AND L.DTLANCCONT = LAST_DAY(P_DATASALDO))
                 HAVING NVL((SUM(ROUND(S.VRSALDOTRANSF, 2))), 0) <> 0) X
           WHERE VALOR <> 0;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_RESULT := 0;
      END;
      
      BEGIN
         SELECT NVL((SUM(ROUND(L.VRLANCAMENT, 2))), 0)
           INTO V_RESULT
           FROM LANCCONT L, CONTCTBL CC
          WHERE ((INSTR(P_CDEMPRESA, L.CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))
            AND L.DTLANCCONT BETWEEN P_DATASALDO AND P_DATASALDO
            AND L.CDCONTCTBL = CC.CDCONTCTBL
            AND CC.IDTIPOCONT = 'A'
            AND CC.CDCONTCTBL LIKE P_CONTA||'%'
            AND CC.IDCONTCCUS <> 'R'
            AND NOT EXISTS (SELECT 1
                              FROM LANCLOTE LL
                             WHERE LL.CDEMPRESA = L.CDEMPRESA
                               AND LL.NRLANCDEFI = L.NRLANCCONT
                               AND L.DTLANCCONT = P_DATASALDO
                               AND L.IDLANCINTE IN ('T', 'F'))
         HAVING NVL((SUM(ROUND(L.VRLANCAMENT, 2))), 0) <> 0;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_RESULT := 0;
      END;
      RETURN V_RESULT;
END;
/

