sexta-feira, 6 de outubro de 2017

Function Saldo Real (Oracle)

CREATE OR REPLACE FUNCTION OBTEM_SUM_SALDO_REAL(DTSALDO IN DATE) 
RETURN FLOAT 
IS
SALDO_REAL  FLOAT;
BEGIN
  SELECT NVL(SUM(SALDO_REAL),0) INTO SALDO_REAL
  FROM(    
        SELECT
            SUM(SALDO1+SALDO2) AS SALDO_REAL
        FROM
        (SELECT  DISTINCT
            CT.CODCTABCOINT,
            (SELECT NVL(SUM(NVL(RECDESP *VLRLANC,0)),0) FROM TGFMBC M WHERE M.CODCTABCOINT IN (CT.CODCTABCOINT) AND TO_DATE(DTLANC)<(DTSALDO + 1) ) AS SALDO1,
            (SELECT SALDOREAL FROM TGFSBC WHERE CODCTABCOINT IN (CT.CODCTABCOINT) AND REFERENCIA=(SELECT MIN(REFERENCIA) FROM TGFSBC WHERE CODCTABCOINT IN (CT.CODCTABCOINT))) AS SALDO2
        FROM
            TGFMBC CT)SALDO
      );
  RETURN (SALDO_REAL);
END;

Nenhum comentário:

Postar um comentário