SQL & PL/SQL

Hi all, I come from a SQL Server background and relatively new to Oracle and I am trying to optimize the query below possibly using CTAS, eliminating the UNION ALL and also looking at changing the traditional Joins to ANSI: Ultimately I am interested in any tricks that can improve the performance of this query.

SELECT UPPER(LE.le_desc) le_desc, 1 rpt, 1 algnt, 'ADJUSTMENTS' AS rpt_typ, NULL AS uche, NULL AS uche_acct_no, NULL AS account, NULL AS description, NULL AS allocation_grp, NULL AS legal_structure, NULL AS income_bucket, NULL AS currency, NULL AS sp_investment, NULL AS amount, NULL AS allc_grp_id , ALC.allctn_id AS allctn_id, LE.le_cid,

DECODE (
(
SELECT lookup_data_mgmt.lookup_value FROM lookup_data_mgmt WHERE LE.mf_cde = lookup_data_mgmt.lookup_id),'Master',1,2
) m_f_ord FROM allctn ALC,

allctn_ds ADS,
allctn_gl_adj AGA,
allctn_gl_adj_dtl AGAD,
uche_acct CLA,
allctn_grp_acct AGAC,
ibg IBG,
ib IB,
allctn_grp AG,
allctr ALR,
allctr_le ALE,
le LE,
ls_flag,
curr CU,
ACCTNG_DS_uche_ACCT_GL ACCT_GL
WHERE
ALR.allctr_id = ALE.allctr_id AND
LE.le_cid = ALE.le_cid AND
ACCT_GL.ALLCTN_DS_ID = ads.allctn_ds_id AND ACCT_GL.ALLCTN_GRP_ACCT_ID = AGAC.ALLCTN_GRP_ACCT_ID AND LE.le_cid = ls_flag.le_cid AND ALR.allctr_id = ALC.allctr_id AND ALC.allctn_id = ADS.allctn_id AND ale.ALLCTR_LE_ID = ads.allctr_le_id AND AGA.allctn_ds_id = ADS.allctn_ds_id AND AGA.allctn_grp_id = AG.allctn_grp_id AND AGAD.allctn_gl_adj_id = AGA.allctn_gl_adj_id AND CLA.uche_acct_id = AGAD.uche_acct_id AND AGAC.uche_acct_id = CLA.uche_acct_id AND AG.allctn_grp_id = AGAC.allctn_grp_id AND IBG.ib_cid = IB.ib_cid AND IBG.ls_flag_id = ls_flag.ls_flag_id AND IBG.ibg_id = AG.ibg_id AND IBG.SUB_FLAG_HDG_CURR_ID = cu.curr_id (+)
GROUP BY
ALC.allctn_id,LE.le_cid,LE.le_desc,LE.mf_cde

UNION ALL

SELECT UPPER(LE.le_desc) le_desc, 2 rpt, 1 algnt, 'ADJUSTMENTS' AS rpt_typ, AGAD.uche_acct_id AS uche, CLA.acct_num AS uche_acct_no,
CASE WHEN CLA.uche_acct_id IN (SELECT AXA.uche_acct_id FROM ax_acct AXA) THEN (SELECT ax_acct_num FROM ax_acct WHERE AX_ACCT.uche_acct_id = CLA.uche_acct_id ) WHEN CLA.uche_acct_id IN (SELECT NERA.uche_acct_id FROM eph_nrstrcd_acct NERA) THEN (SELECT eph_acct_num FROM eph_nrstrcd_acct WHERE EPH_NRSTRCD_ACCT.uche_acct_id = CLA.uche_acct_id) WHEN CLA.uche_acct_id IN (SELECT EPH_RSTRCD_ACCT.uche_acct_id FROM eph_rstrcd_acct) THEN (SELECT EPH_RSTRCD_ACCT.eph_rstrcd_acct_num FROM eph_rstrcd_acct WHERE EPH_RSTRCD_ACCT.uche_acct_id = CLA.uche_acct_id ) END account, CLA.uche_acct_desc description, AG.allctn_grp_desc AS allocation_grp, ls_flag.ls_flag_name AS legal_structure, IB.ib_flag AS income_bucket, NULL AS currency, DECODE(IB.ib_flag,'SP ',(SELECT IBG.sub_flag_sp FROM ibg ibg1 WHERE ibg1.ibg_id = IBG.ibg_id AND IB.ib_cid = IBG.ib_cid),NULL) AS sp_investment, AGAD.adj_amt_in_base AS amount, AG.allctn_grp_id AS allc_grp_id , ALC.allctn_id AS allctn_id, LE.le_cid, DECODE ((SELECT lookup_data_mgmt.lookup_value FROM lookup_data_mgmt WHERE LE.mf_cde = lookup_data_mgmt.lookup_id),'Master',1,2) m_f_ord FROM allctn ALC, allctn_ds ADS, allctn_gl_adj AGA, allctn_gl_adj_dtl AGAD, uche_acct CLA, allctn_grp_acct AGAC, ibg IBG, ib IB, allctn_grp AG, allctr ALR, allctr_le ALE, le LE, ls_flag, curr CU, ACCTNG_DS_uche_ACCT_GL ACCT_GL WHERE ALR.allctr_id = ALE.allctr_id AND LE.le_cid = ALE.le_cid AND ACCT_GL.ALLCTN_DS_ID = ads.allctn_ds_id AND ACCT_GL.ALLCTN_GRP_ACCT_ID = AGAC.ALLCTN_GRP_ACCT_ID AND LE.le_cid = ls_flag.le_cid AND ALR.allctr_id = ALC.allctr_id AND ALC.allctn_id = ADS.allctn_id AND ale.ALLCTR_LE_ID = ads.allctr_le_id AND AGA.allctn_ds_id = ADS.allctn_ds_id AND AGA.allctn_grp_id = AG.allctn_grp_id AND AGAD.allctn_gl_adj_id = AGA.allctn_gl_adj_id AND CLA.uche_acct_id = AGAD.uche_acct_id AND AGAC.uche_acct_id = CLA.uche_acct_id AND AG.allctn_grp_id = AGAC.allctn_grp_id AND IBG.ib_cid = IB.ib_cid AND IBG.ls_flag_id = ls_flag.ls_flag_id AND IBG.ibg_id = AG.ibg_id AND AGAD.adj_amt_in_base <> 0 AND IBG.SUB_FLAG_HDG_CURR_ID = cu.curr_id (+)

Any help will be greatly appreciated...

Thanks