select p.ssi_id, p.name, p.amount_mm, p.probability, p.prospect_source_id, source.prospect_source_desc, status.prospect_status_code, status.prospect_status_desc, status.prospect_status_date, phase.prospect_status_code, phase.prospect_status_desc, phase.prospect_status_date, phase.prospect_status_comment, pr.ssi_id, pr.product_desc, salesperson.ssi_id, salesperson.name, consultant_relations.ssi_id, consultant_relations.name, consultant.ssi_id, consultant.name, ps.ssi_id, ps.name, ps.category_code, ps.category, pc.ssi_id, pc.name, ipc.ssi_id, ipc.name, rfp.ssi_id, rfp.name from prospect p, prospect_source source, product pr, ( select psh.ssi_id, psc.prospect_status_code, psc.prospect_status_desc, psh.prospect_status_date from prospect_status_history psh, prospect_status_code psc where psh.prospect_status_seq = psc.prospect_status_seq and psc.prospect_status_flag = 'Y' and psh.prospect_status_date = ( select max(psh2.prospect_status_date) from prospect_status_history psh2, prospect_status_code psc2 where psh.ssi_id = psh2.ssi_id and psh2.prospect_status_seq = psc2.prospect_status_seq and psc2.prospect_status_flag = 'Y' ) ) status, ( select psh.ssi_id, psc.prospect_status_code, psc.prospect_status_desc, psh.prospect_status_date, psh.prospect_status_comment from prospect_status_history psh, prospect_status_code psc where psh.prospect_status_seq = psc.prospect_status_seq and psc.prospect_phase_flag = 'Y' and psh.prospect_status_date = ( select max(psh1.prospect_status_date) from prospect_status_history psh1, prospect_status_code psc1 where psh.ssi_id = psh1.ssi_id and psh1.prospect_status_seq = psc1.prospect_status_seq and psc1.prospect_phase_flag = 'Y' ) ) phase, ( select ir.ssi_id1 as prospect_ssi_id, ls.ssi_id, ls.lname as name from item_rel ir, lsemp ls where ir.ssi_id2 = ls.ssi_id and ir.item_rel_type_id = 114 and ir.end_date is null ) salesperson, ( select ir.ssi_id1 as prospect_ssi_id, ls.ssi_id, ls.lname as name from item_rel ir, lsemp ls where ir.ssi_id2 = ls.ssi_id and ir.item_rel_type_id = 115 and ir.end_date is null ) consultant_relations, ( select ir.ssi_id1 as prospect_ssi_id, c.ssi_id, c.name as name from item_rel ir, consultant c where ir.ssi_id2 = c.ssi_id and ir.item_rel_type_id = 111 and ir.end_date is null ) consultant, ( select ir.ssi_id1 as prospect_ssi_id, ps.ssi_id, ps.name, ct.category_code, ct.category_desc as category from item_rel ir, plan_sponsor ps, category_type ct where ir.ssi_id2 = ps.ssi_id and ir.item_rel_type_id = 110 and ir.end_date is null and ps.category_code = ct.category_code (+) ) ps, ( select ir.ssi_id1 as prospect_ssi_id, pc.ssi_id, pc.name from item_rel ir, private_client pc where ir.ssi_id2 = pc.ssi_id and ir.item_rel_type_id = 112 and ir.end_date is null ) pc, ( select ir.ssi_id2 as prospect_ssi_id, r.ssi_id, r.descr as name from item_rel ir, rfp r where ir.ssi_id1 = r.ssi_id and ir.item_rel_type_id = 94 and ir.end_date is null ) rfp, ( select ir.ssi_id1 as prospect_ssi_id, ipc.ssi_id, ipc.name from item_rel ir, intermediary ipc where ir.ssi_id2 = ipc.ssi_id and ir.item_rel_type_id = 113 and ir.end_date is null ) ipc where p.ssi_id = status.ssi_id (+) and p.ssi_id = phase.ssi_id and p.prospect_source_id = source.prospect_source_id (+) and p.loomis_product_ssi_id = pr.ssi_id (+) and p.ssi_id = salesperson.prospect_ssi_id (+) and p.ssi_id = consultant_relations.prospect_ssi_id (+) and p.ssi_id = consultant.prospect_ssi_id (+) and p.ssi_id = pc.prospect_ssi_id (+) and p.ssi_id = rfp.prospect_ssi_id (+) and p.ssi_id = ps.prospect_ssi_id (+) and p.ssi_id = ipc.prospect_ssi_id (+) and (status.prospect_status_code in (1,2,3) or (phase.prospect_status_code in (7,8) and phase.prospect_status_date >= to_date('07/01/2005', 'MM/dd/yyyy'))) and (consultant.name is not null or ps.name is not null) and status.prospect_status_desc in ('A','B','C') and ltrim(rtrim(pr.product_desc)) in ('Large Cap Growth') order by status.prospect_status_desc, p.name