INSERT INTO mfiles_xref_p1
   ( id_source,
     id_cconsol,
     id_cconsol_char,
     id_version,
     id_local_client,
     nm_cshort,
     nm_tsystem,
     id_csystem,
     id_csystemref,
     id_cp_source )
   SELECT /*+ ordered */
           f.id_source,
           x.cconsol,
           x.cconsol,
           0,
           NVL(x.csystemref,'^'),
           f.nm_short,
           f.nm_tsystem,
           f.id_csystem,
           x.csystemref,
           f.id_cp_source
   FROM txref x, feeder_systems_p1 f
   WHERE f.id_xref_entity = 'CP'
   AND   x.csystem = f.id_csystem
   AND   x.region IN ('SG')
   AND   f.id_cp_source = 'MSTS'
   AND  EXISTS (SELECT /*+ no_unnest */ * FROM counterparties c
                WHERE c.cpty_src = 'MSTS'
                AND c.cpty_id = x.cconsol
                AND c.seq_num = (SELECT MAX(c1.seq_num) FROM counterparties c1
                                 WHERE c1.cpty_src = c.cpty_src
                                 AND   c1.cpty_id = c.cpty_id)
                AND (c.cp_active = 'Y' OR (c.cp_active = 'N' AND c.enter_dt >= add_months(trunc(sysdate),-18)))
                )