Saldo Bancário:
create or replace FUNCTION Obtem_Saldo_Banco_Tgfmbc_Orcl(P_CODCTABCOINT IN NUMBER, P_DHCONCILIACAO IN DATE)
RETURN FLOAT
IS
SALDOBANCO FLOAT;
BEGIN
SELECT NVL(SUM(SALDOBCO),0) INTO SALDOBANCO
FROM(
SELECT SBC.SALDOBCO
FROM TGFSBC SBC
WHERE SBC.CODCTABCOINT = P_CODCTABCOINT AND
SBC.REFERENCIA = (SELECT MAX(REFERENCIA)
FROM TGFSBC SBC1
WHERE SBC1.CODCTABCOINT = P_CODCTABCOINT AND
TO_DATE(SBC1.REFERENCIA) <= P_DHCONCILIACAO
)
UNION ALL
SELECT NVL(SUM(MBC.VLRLANC*MBC.RECDESP),0)
FROM TGFMBC MBC
WHERE MBC.CODCTABCOINT = P_CODCTABCOINT AND
TO_DATE(MBC.DTLANC) >= TO_DATE(TRUNC(P_DHCONCILIACAO, 'MM')) AND
TO_DATE(MBC.DTLANC) < P_DHCONCILIACAO
);
RETURN (SALDOBANCO);
END;
Saldo Real:
create or replace FUNCTION Obtem_Saldo_Real_Tgfmbc_Orcl(P_CODCTABCOINT IN NUMBER, P_DTLANC IN DATE)
RETURN FLOAT
IS
SALDOREAL FLOAT;
BEGIN
SELECT NVL(SUM(SALDOREAL),0) INTO SALDOREAL
FROM(
SELECT SBC.SALDOREAL
FROM TGFSBC SBC
WHERE SBC.CODCTABCOINT = P_CODCTABCOINT AND
TRUNC(SBC.REFERENCIA) = (SELECT MAX(TRUNC(REFERENCIA))
FROM TGFSBC SBC1
WHERE SBC1.CODCTABCOINT = P_CODCTABCOINT AND
TRUNC(SBC1.REFERENCIA) <= TRUNC(P_DTLANC)
)
UNION ALL
SELECT NVL(SUM(MBC.VLRLANC*MBC.RECDESP),0)
FROM TGFMBC MBC
WHERE MBC.CODCTABCOINT = P_CODCTABCOINT AND
TRUNC(MBC.DTLANC)>= TRUNC(P_DTLANC, 'MM') AND
TRUNC(MBC.DTLANC) < TRUNC(P_DTLANC)
);
RETURN (SALDOREAL);
END;
Nenhum comentário:
Postar um comentário