Bom dia Robinson e Potter.
O relatório é formado por parametros e duas SQL, estes parametros definem o nvel da contas:
Nivel 1: 1º Nível Ini #.: e 1º Nível Fin #.:
Nivel 2: 2º Nível Ini #.#.: e 2º Nível Fin #.#.: segue assim até o nível 6.
A duas SQL que formam o relatório são:
01: SELECT DISTINCT
C.CODCONTA AS CONTA$$ ,
C.DESCRICAO AS DESCRICAO,
C.REDUZIDO AS REDUZIDO
FROM CCONTA C
WHERE C.CODCOLIGADA = 0
AND LENGTH(C.CODCONTA) = 1
AND C.CODCONTA >= :ESPELHO#1_S
AND C.CODCONTA <= :ESPELHO#2_S
ORDER
BY 1
02: SELECT DISTINCT
(
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.DEBITO LIKE :ESPELHO#36_S || '%' AND C.DATA < :ESPELHO#3_D),0.00) -
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.CREDITO LIKE :ESPELHO#36_S || '%' AND C.DATA < :ESPELHO#3_D),0.00)
) AS SLDANTERIOR,
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.DEBITO LIKE :ESPELHO#36_S || '%' AND C.DATA >= :ESPELHO#3_D AND C.DATA <= :ESPELHO#4_D),0.00) AS DEBITO,
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.CREDITO LIKE :ESPELHO#36_S || '%' AND C.DATA >= :ESPELHO#3_D AND C.DATA <= :ESPELHO#4_D),0.00) AS CREDITO,
(
(
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.DEBITO LIKE :ESPELHO#36_S || '%' AND C.DATA < :ESPELHO#3_D),0.00) -
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.CREDITO LIKE :ESPELHO#36_S || '%' AND C.DATA < :ESPELHO#3_D),0.00)
) +
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.DEBITO LIKE :ESPELHO#36_S || '%' AND C.DATA >= :ESPELHO#3_D AND C.DATA <= :ESPELHO#4_D),0.00) -
NVL((SELECT SUM(C.VALOR) FROM CPARTIDA C WHERE C.CODCOLIGADA = :ESPELHO#5_N AND C.CREDITO LIKE :ESPELHO#36_S || '%' AND C.DATA >= :ESPELHO#3_D AND C.DATA <= :ESPELHO#4_D),0.00)
) AS SLDATUAL
FROM DUAL