Untitled

with data AS
 (SELECT dcc.owner,
         dcc.table_name,
         (select listagg(dt.trigger_name || '|' ||
                         regexp_instr(upper(dbms_metadata.get_ddl(object_type => 'TRIGGER',
                                                                  name        => dt.trigger_name,
                                                                  schema      => dt.owner)),
                                      upper(dc2.table_name)) || '|' ||
                         regexp_instr(upper(dbms_metadata.get_ddl(object_type => 'TRIGGER',
                                                                  name        => dt.trigger_name,
                                                                  schema      => dt.owner)),
                                      upper(dc2.column_name)) || '|' ||
                         regexp_instr(upper(dbms_metadata.get_ddl(object_type => 'TRIGGER',
                                                                  name        => dt.trigger_name,
                                                                  schema      => dt.owner)),
                                      upper(dcc.column_name)),
                         ',')
            from dba_triggers dt
           where dt.owner = dcc.owner
             and table_name = dcc.table_name
             and trigger_name not like 'AUD%') as trigger_name,
         dcc.column_name,
         dc2.table_name as parent_name,
         dc2.column_name as parent_column_name,
         dcc.constraint_name as FOREIGN_KEY_NAME,
         dcc.column_name as FK_COLUMN_NAME,
         dc.R_OWNER,
         dc.R_CONSTRAINT_NAME,
         dc.STATUS,
         ds.SYNONYM_NAME as curr_synyonym_name,
         (case dc2.table_name
           WHEN 'ACCT' THEN
            'SYN_ACCOUNT'
           WHEN 'ACCOUNTING_SYSTEM' THEN
            'SYN_ACCOUNTING_SYSTEM'
           WHEN 'ONYX_COMPANY_TBL' THEN
            'SYN_CLIENT'
           WHEN 'ONYX_CC_ROLES_TBL' THEN
            'SYN_CLIENT_CONTACT_ROLE'
           WHEN 'CONTACT_TBL' THEN
            'SYN_CONTACT'
           WHEN 'CONTACT_VIEW' THEN
            'SYN_CONTACT'
           WHEN 'USER_TBL' THEN
            'SYN_CONTACT'
           WHEN 'INVESTMENTPROGRAM_TBL' THEN
            'SYN_CONTRACTING_ENTITY'
           WHEN 'ONYX_COUNTRY_TBL' THEN
            'SYN_COUNTRY'
           WHEN 'LOCATION_CODE' THEN
            'SYN_LOCATION'
           WHEN 'ONYX_LOCATIONS_TBL' THEN
            'SYN_MANGEMENT_LOCATION'
           WHEN 'FUND' THEN
            'SYN_PORTFOLIO'
           WHEN 'FUND_LEGAL_TYPE' THEN
            'SYN_PORTFOLIO_LEGAL_TYPE'
         END) as new_synonym_name
    from dba_cons_columns dcc
   inner join dba_constraints dc
      on dc.OWNER = dcc.owner
     and dc.CONSTRAINT_NAME = dcc.constraint_name
     and dc.TABLE_NAME = dcc.table_name
     and dc.CONSTRAINT_TYPE = 'R'
   inner join dba_cons_columns dc2
      on dc2.owner = dc.R_OWNER
     and dc2.constraint_name = dc.r_CONSTRAINT_NAME
    left outer join dba_synonyms ds
      on ds.table_owner = dc.r_owner
     and ds.TABLE_NAME = dc2.TABLE_NAME
     and ds.owner = dcc.owner
   where dc.R_OWNER in ('REFDATA')
     and dcc.owner in ('RMS')
     and dc2.table_name in ('ACCT',
                            'ACCOUNTING_SYSTEM',
                            'ONYX_COMPANY_TBL',
                            'ONYX_CC_ROLES_TBL',
                            'CONTACT_TBL',
                            'CONTACT_VIEW',
                            'USER_TBL',
                            'INVESTMENTPROGRAM_TBL',
                            'ONYX_COUNTRY_TBL',
                            'LOCATION_CODE',
                            'ONYX_LOCATIONS_TBL',
                            'FUND',
                            'FUND_LEGAL_TYPE'
                            ))
select data.OWNER,
       data.TABLE_NAME,
       TRIGGER_NAME,
       COLUMN_NAME,
       PARENT_NAME,
       PARENT_COLUMN_NAME,
       FOREIGN_KEY_NAME,
       FK_COLUMN_NAME,
       R_OWNER,
       R_CONSTRAINT_NAME,
       data.STATUS,
       data.CURR_SYNYONYM_NAME,
       data.NEW_SYNONYM_NAME,
       ds2.synonym_name as matching_synonym,
       ds2.TABLE_OWNER matching_table_owner,
       ds2.TABLE_NAME matching_table_name,
       (case
         when ds2.synonym_name is not null and
              (ds2.table_owner <> 'REFDATA' OR
              ds2.TABLE_NAME <> data.parent_name) then
          'Y'
         else
          'N'
       end) as synonym_change_flag
  from data
  left outer join dba_synonyms ds2
    on ds2.owner = data.owner
   and ds2.synonym_name = data.NEW_SYNONYM_NAME
 where data.TRIGGER_NAME is not null