CREATE TABLE LEDGER_ACC
(
  VA_GL_ACCOUNT_NO                     VARCHAR2(20 BYTE) NOT NULL,
  VA_GL_ACCOUNT_NAME              VARCHAR2(100 BYTE),
  VA_GL_ACCOUNT_TYPE_ID          VARCHAR2(10 BYTE),
  NU_INDENTATION                            NUMBER(5)
);
INSERT INTO LEDGER_ACC VALUES('11000000','Share capital','ACCTYP0003',1);
INSERT INTO LEDGER_ACC VALUES('11200000','Reserve & Surplus','ACCTYP0003',2);
INSERT INTO LEDGER_ACC VALUES('11200110','Capital reserve','ACCTYP0002',3);
INSERT INTO LEDGER_ACC VALUES('11200120','Security Premium','ACCTYP0002',3);
INSERT INTO LEDGER_ACC VALUES('11299999','Reserve & Surplus, Total','ACCTYP0004',2);

SELECT CASE WHEN A.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0004' THEN
                      (SELECT B.VA_GL_ACCOUNT_NAME
                       FROM LEDGER_ACC  B
                       WHERE B.VA_GL_ACCOUNT_NO LIKE (SELECT RPAD(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,'9')-1),
                                                             (LENGTH(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,'9')-1)) +
                                                             LENGTH(SUBSTR(A.VA_GL_ACCOUNT_NO,INSTR(A.VA_GL_ACCOUNT_NO,9))))-1,
                                                           '0') ACC_NUM FROM DUAL)||'%'
                        AND B.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003')
            WHEN A.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003' AND A.NU_INDENTATION IN (1,2,3,4,5) THEN
                      (SELECT B.VA_GL_ACCOUNT_NAME
                        FROM LEDGER_ACC  B
                        WHERE B.VA_GL_ACCOUNT_NO LIKE (SELECT RPAD(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,0)-2),7,'0')
                                                       FROM DUAL)||'%'
                         AND B.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003')
            WHEN A.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003' AND A.NU_INDENTATION = 0 THEN
                       (SELECT B.VA_GL_ACCOUNT_NAME
                         FROM LEDGER_ACC  B
                         WHERE B.VA_GL_ACCOUNT_NO = (SELECT RPAD(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,0)),8,'0')
                                                        FROM DUAL)
                          AND B.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0001')
            WHEN A.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0002'  AND A.NU_INDENTATION IN (1,3,4) THEN
                       (SELECT B.VA_GL_ACCOUNT_NAME
                         FROM LEDGER_ACC  B
                         WHERE B.VA_GL_ACCOUNT_NO LIKE (SELECT RPAD(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,0)-1),7,'0')
                                                       FROM DUAL)||'%'       
                          AND B.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003')
            WHEN A.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0002'  AND A.NU_INDENTATION IN (5,6) THEN
                       (SELECT B.VA_GL_ACCOUNT_NAME
                         FROM LEDGER_ACC  B
                         WHERE B.VA_GL_ACCOUNT_NO LIKE (SELECT RPAD(SUBSTR(A.VA_GL_ACCOUNT_NO,1,INSTR(A.VA_GL_ACCOUNT_NO,0)-2),7,'0')
                                                       FROM DUAL)||'%'       
                          AND B.VA_GL_ACCOUNT_TYPE_ID = 'ACCTYP0003')
       END GRP_LEDGER_NAME
FROM  LEDGER_ACC   A
WHERE A.VA_GL_ACCOUNT_NO = '11200120'

SQL> column VA_GL_ACCOUNT_NO format a10
SQL> column VA_GL_ACCOUNT_NAME format a20
SQL> column VA_GL_ACCO format a10
SQL> WITH data AS
  2    (SELECT t.*,
  3      lead(va_gl_account_no) over(order by VA_GL_ACCOUNT_NAME, VA_GL_ACCOUNT_no) rn
  4    FROM ledger_acc t
  5    )
  6  SELECT * FROM DATA
  7    WHERE rn = 11299999
  8  /

VA_GL_ACCO VA_GL_ACCOUNT_NAME   VA_GL_ACCO NU_INDENTATION RN
---------- -------------------- ---------- -------------- --------------------
11200000   Reserve & Surplus    ACCTYP0003              2 11299999

SQL>