Get rid of the parentheses around the main queries -- those "tell" SQL that a subquery is coming, but these not subqueries.

/*(*/

Select distinct b.lev5

from bf_data a

inner join bf_orgn_cnsl_tbl b

on a.bf_orgn_cd = b.bf_orgn_cd

inner join bf_fund_cnsl_tbl c

on a.bf_fund_cd =c.bf_fund_cd

inner join bf_Orgn d /*---> inner join with bf_orgn_cd colunm*/

on b.bf_orgn_cd =d.cd

WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='ADOPTTOT')

and a.bf_bdob_cd in (select bf_bdob_chld_cd from bf_bdob_rlup_tbl where rlup1 in ('TOTEXP','TOTBSAEXP'))

and c.bf_fund_cnsl_slcn_cd = '01-FUND'

and b.bf_orgn_cnsl_slcn_cd = '01-ORG'

and d.bf_acty_cd in (select distinct bf_acty_cd from bf_acty_cnsl_tbl where lev2 in ('400_FUNC'))

and c.LEV4='GF'

AND c.bf_fund_cd= 'A01' /*)*/

union

/*(*/

select distinct b.lev5

from bf_data a

inner join bf_orgn_cnsl_tbl b

on a.bf_orgn_cd = b.bf_orgn_cd

inner join bf_fund_cnsl_tbl c

on a.bf_fund_cd =c.bf_fund_cd

inner join bf_Orgn d

on b.lev5 =d.cd ---> inner join with lev5 colunm

WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='ADOPTTOT')

and a.bf_bdob_cd in (select bf_bdob_chld_cd from bf_bdob_rlup_tbl where rlup1 in ('TOTEXP','TOTBSAEXP'))

and c.bf_fund_cnsl_slcn_cd = '01-FUND'

and b.bf_orgn_cnsl_slcn_cd = '01-ORG'

and b.lev5NAME like 'CP%' --> the only difference from first query

and d.bf_acty_cd in (select distinct bf_acty_cd from bf_acty_cnsl_tbl where lev2 in ('400_FUNC'))

and c.LEV4='GF'

AND c.bf_fund_cd= 'A01' /*)*/

group by b.lev5 ,b.lev5NAME , C.lev7, C.lev7NAME

order by lev5 --Edit: should be lev5 not b.lev5