drop table wang_amanah;
create table wang_amanah(
 ID INTEGER,
 SODO varchar2(255),
 TYPE varchar2(255)
);
insert into wang_amanah(id,sodo,type)VALUES (1,'E15001','SODO');
insert into wang_amanah(id,sodo,type)VALUES (2,'H72,H74,H75,H79,H83,H89,E14','SEBAGAI');
insert into wang_amanah(id,sodo,type)VALUES (3,'B11,B12,B13,B14,B15,B21,B22,B23,B24,B25,B26,B27,B28,B29,B31,B32,B33,B34,B35,B36,B41,B42,B44,B45,B47,B48,B51,B52,B53,B54,B59','SEBAGAI');
insert into wang_amanah(id,sodo,type)VALUES (4,'B53001,E15004,E15005,E15008,E15009','SODO');
COMMIT;

WITH U_E15001_SODO AS ( SELECT sodo str FROM wang_amanah where id = 1 ),
 A_H_E14000_SODO AS ( SELECT sodo str FROM wang_amanah where id = 2 ),
 A_B_SODO AS ( SELECT sodo str FROM wang_amanah where id = 3 ),
 A_B53001_E15_SODO AS ( SELECT sodo str FROM wang_amanah where id = 4 ),
 U_E15001 AS (SELECT SUM((OPENING * -1) + (kredit - debit)) U_E15001
                  FROM VW_MV_EIS_LEJARAMBULANAN
                  WHERE THN_KEWANGAN = 2015
                        AND SODO IN (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str FROM U_E15001_SODO CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL)
                        AND KOD_BAJET = 'U'
                        AND BLN BETWEEN 01 AND 12),
A_H_E14000 AS (SELECT SUM(KREDIT - DEBIT) AS A_H_E14000
                   FROM VW_MV_EIS_LEJARAMBULANAN
                   WHERE THN_KEWANGAN = 2015
                         AND SEBAGAI IN (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str FROM A_H_E14000_SODO CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL)
                         AND KOD_BAJET = 'A'
                         AND BLN BETWEEN 01 AND 12),
A_B AS (SELECT SUM(DEBIT - KREDIT) AS A_B
                  FROM VW_MV_EIS_LEJARAMBULANAN
                  WHERE THN_KEWANGAN = 2015
                  AND SEBAGAI IN (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str FROM A_B_SODO CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL)
                  AND KOD_BAJET = 'A'
                  AND BLN BETWEEN 01 AND 12),
A_B53001_E15 AS (SELECT SUM(DEBIT - KREDIT) AS A_B53001_E15
                     FROM VW_MV_EIS_LEJARAMBULANAN
                     WHERE THN_KEWANGAN = 2015
                          AND SODO IN (SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str FROM A_B53001_E15_SODO CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL)
                           AND KOD_BAJET = 'A'
                           AND BLN BETWEEN 01 AND 12)
SELECT A_B + A_B53001_E15 OBL_E17105
FROM U_E15001, A_H_E14000, A_B, A_B53001_E15;