Subsubq1

with 
DUAL1 as ( 
  select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT 
        from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT 
        from dual ), 
DUAL2 as ( 
  select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ), 
UNION1 as ( 
  select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1 
  union all 
  select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 
), 
SUBQ1 as ( 
  select * from UNION1 where SHIFT = '100' ), 
SUBSUBQ1 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ), 
SUBSUBQ2 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ), 
UNION2 as ( 
  select * from SUBSUBQ1 
  union all 
  select * from SUBSUBQ2 
) 
select 'SUBSUBQ1' QUERY, SUBSUBQ1.* from SUBSUBQ1

Subsubq2

with 
DUAL1 as ( 
  select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT 
        from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT 
        from dual ), 
DUAL2 as ( 
  select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ), 
UNION1 as ( 
  select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1 
  union all 
  select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 
), 
SUBQ1 as ( 
  select * from UNION1 where SHIFT = '100' ), 
SUBSUBQ1 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ), 
SUBSUBQ2 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ), 
UNION2 as ( 
  select * from SUBSUBQ1 
  union all 
  select * from SUBSUBQ2 
) 
select 'SUBSUBQ2' QUERY, SUBSUBQ2.* from SUBSUBQ2

union, missing line

with 
DUAL1 as ( 
  select 'MM ' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID, 100 as SHIFT 
        from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID, 100 as SHIFT 
        from dual ), 
DUAL2 as ( 
  select 'MM' as ORG_SYS_TYPE, 'LOST_FIN_ID' as FIN_ID from dual 
  union all 
  select 'DER' as ORG_SYS_TYPE, 'FIN_ID_B' as FIN_ID from dual ), 
UNION1 as ( 
  select ORG_SYS_TYPE,FIN_ID, SHIFT from DUAL1 
  union all 
  select ORG_SYS_TYPE,FIN_ID, null SHIFT from DUAL2 
), 
SUBQ1 as ( 
  select * from UNION1 where SHIFT = '100' ), 
SUBSUBQ1 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'MM' ), 
SUBSUBQ2 as ( 
  select * from SUBQ1 where ORG_SYS_TYPE = 'DER' ), 
UNION2 as ( 
  select * from SUBSUBQ1 
  union all 
  select * from SUBSUBQ2 
) 
select 'UNION2' QUERY, UNION2.* from UNION2