Can somebody suggest me better way to write this query.
Thanks in advance.


SELECT DISTINCT clcla.parn_cl_id cl_id
FROM v_regs_clcla clcla,
(
SELECT a.parn_cl_id , a.cl_id
FROM v_regs_clh clh,
(
SELECT DISTINCT z.parn_cl_id,z.cl_id,z.pr_id , z.cmp_pr_id
FROM v_regs_prh prh , spc spc,
(

SELECT DISTINCT clcla.parn_cl_id,clpsh.cl_id,clpsh.pr_id , NVL(prpra.pr_id,clpsh.pr_id) AS cmp_pr_id
FROM v_regs_clcla clcla, v_regs_clpsh clpsh, v_regs_prpra prpra
WHERE clcla_typ_c = 104
AND clcla_eff_to_dt IS NULL
AND clcla_stus_c = 'A'
AND clcla.cl_id = clpsh.cl_id
AND clps_ackrat_catg_c IN (pc_ASC,pc_ASO,pc_CPL,pc_RFA,pc_RTS)
AND clps_stus_c = 'A' AND clps_mkt_stus_c = 100
AND clpsh_wrk_flow_c= 7
--AND (clps_eff_to_dt >= regs_pkg_maint.STLM_DATE OR clps_eff_to_dt IS NULL)
AND (clps_cn_end_dt >= regs_pkg_maint.STLM_DATE )
AND clcla.parn_cl_id = NVL(c_cl_id_in, clcla.parn_cl_id)
AND clpsh.pr_id = prpra.parn_pr_id (+)
AND prpra_typ_c (+)= 2000
AND prpra.prpra_stus_c (+) = 'A'
--AND (prpra.prpra_eff_to_dt >= regs_pkg_maint.STLM_DATE OR prpra.prpra_eff_to_dt IS NULL)
) z

WHERE z.pr_id = prh.pr_id
AND prh.copc_id = spc.copc_id
AND prh.pr_stus_c='A'
AND spc.stlm_rcd_sta_cd='A'
AND spc.spc_rt_reqd_in = 'Y'
AND prh.prh_rec_mtn_ts = ( select max(prh_rec_mtn_ts) from v_regs_prh prh_inner
where prh_inner.pr_id = prh.pr_id and prh_inner.pr_stus_c='A')
) a
WHERE clh.cl_id = a.parn_cl_id
AND cl_stus_c = 'A'
AND cl_acct_typ_c NOT IN (4,6,10)
AND NVL(cl_prtnr_aff_c,'XXX') <> 'A'
) b
WHERE b.cl_id = clcla.cl_id
AND clcla_typ_c = 104
AND clcla_stus_c = 'A'
AND clcla.parn_cl_id NOT IN ( SELECT DISTINCT CL_ID FROM V_REGS_CLCLA X WHERE X.CL_ID =
clcla.parn_cl_id AND X.clcla_typ_c IN (101,102,111));