Untitled
unknown
plain_text
18 days ago
9.5 kB
5
Indexable
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%'
-- AND dt.trigger_name IN ('TRG_ASM_LAST_UPDATED_BY')
) 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'
WHEN 'ACCOUNT_STRATEGY_TYPE' THEN
'SYN_ACCOUNT_STRATEGY_TYPE'
WHEN 'FUND_MGMT_CODE' THEN
'SYN_PORTFOLIO_TYPE_SUBCLASS'
WHEN 'INV_MANDATE_BASE' THEN
'SYN_MANDATE'
WHEN 'INV_MANDATE_DET' THEN
'SYN_MANDATE_DETAIL'
WHEN 'INV_MANDATE_DET_BASE' THEN
'SYN_MANDATE_DETAIL'
WHEN 'MANDATE_BASE' THEN
'SYN_MANDATE'
WHEN 'ONYX_REGIONS_TBL' THEN
'SYN_REGIONS_TBL'
WHEN 'SELECTION_STRATEGY_TYPE' THEN
'SYN_SELECTION_STRATEGY_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'
LEFT OUTER JOIN dba_triggers dts
ON dts.owner = dcc.owner
and dts.table_name = dcc.table_name
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 ('CASH_VIEWS')
AND dts.trigger_name IN ('TRG_ASM_LAST_UPDATED_BY',
'TRG_BBF_CREATED_BY',
'TRG_BBF_LAST_UPDATED_BY',
'TRG_BSF_CREATED_BY'
/*'TRG_BSF_LAST_UPDATED_BY',
'TRG_COG_CREATED_BY',
'TRG_COG_LAST_UPDATED_BY',
'TRG_CDS_CREATED_BY',
'TRG_CUG_LAST_UPDATED_BY',
'TRG_CUF_CREATED_BY',
'TRG_CUF_LAST_UPDATED_BY',
'TRG_CPS_CREATED_BY',
'TRG_DEL_CREATED_BY',
'TRG_DEL_LAST_UPDATED_BY',
'TRG_FBC_CREATED_BY',
'TRG_FLT_CREATED_BY',
'TRG_FLT_LAST_UPDATED_BY',
'TRG_IST_CREATED_BY',
'TRG_IST_LAST_UPDATED_BY',
'TRG_INT_CREATED_BY',
'TRG_INT_LAST_UPDATED_BY',
'TRG_IRP_CREATED_BY',
'TRG_IRP_LAST_UPDATED_BY',
'TRG_LST_CREATED_BY',
'TRG_LST_LAST_UPDATED_BY',
'TRG_MMP_CREATED_BY',
'TRG_MMP_LAST_UPDATED_BY',
'TRG_MMT_CREATED_BY',
'TRG_MMT_LAST_UPDATED_BY',
'TRG_PCP_CREATED_BY',
'TRG_PCP_LAST_UPDATED_BY',
'TRG_PJS_CREATED_BY',
'TRG_PJS_LAST_UPDATED_BY',
'TRG_PMS_CREATED_BY',
'TRG_PRA_CREATED_BY',
'TRG_PRA_LAST_UPDATED_BY',
'TRG_RAG_CREATED_BY',
'TRG_RAG_LAST_UPDATED_BY',
'TRG_RBU_CREATED_BY',
'TRG_RBU_LAST_UPDATED_BY',
'TRG_RSC_CREATED_BY',
'TRG_RSC_LAST_UPDATED_BY',
'TRG_RTS_CREATED_BY',
'TRG_RTS_LAST_UPDATED_BY',
'TRG_RST_CREATED_BY',
'TRG_RTE_CREATED_BY',
'TRG_RTE_LAST_UPDATED_BY',
'TRG_RTY_CREATED_BY',
'TRG_RTY_LAST_UPDATED_BY',
'TRG_RLL_CREATED_BY',
'TRG_RLL_LAST_UPDATED_BY',
'TRG_TRS_CREATED_BY',
'TRG_TRS_LAST_UPDATED_BY',
'TRG_RIS_CREATED_BY',
'TRG_ASG_CREATED_BY',
'TRG_ASG_LAST_UPDATED_BY',
'TRG_RET_CREATED_BY',
'TRG_RET_LAST_UPDATED_BY',
'TRG_RSM_CREATED_BY',
'TRG_RSM_LAST_UPDATED_BY',
'TRG_SBL_CREATED_BY',
'TRG_SBL_LAST_UPDATED_BY',
'TRG_SRR_CREATED_BY',
'TRG_TST_CREATED_BY',
'TRG_TST_LAST_UPDATED_BY',
'TRG_YFT_CREATED_BY',
'TRG_YFT_LAST_UPDATED_BY',
'TRG_YPR_CREATED_BY',
'TRG_YPR_LAST_UPDATED_BY'*/
)
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',
'ACCOUNT_STRATEGY_TYPE',
'FUND_MGMT_CODE',
'INV_MANDATE_BASE',
'INV_MANDATE_DET',
'INV_MANDATE_DET_BASE',
'MANDATE_BASE',
'ONYX_REGIONS_TBL',
'SELECTION_STRATEGY_TYPE'
-- ,not ready 'ACCOUNT_STRATEGY_GROUP'
))
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
CASH_VIEWS AGENCY_SYMBOL_MAP TRG_ASM_LAST_UPDATED_BY|299|310|64,TRG_ASM_CREATED_BY|224|235|0,TRG_ASM_BIU|0|0|569 LAST_UPDATED_BY USER_TBL USER_ID FKY_ASM_LAST_UPDATED_BY LAST_UPDATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BLOOMBERG_FIELD TRG_BBF_LAST_UPDATED_BY|297|308|0,TRG_BBF_CREATED_BY|222|233|64,TRG_BBF_BIU|0|0|353 CREATED_BY USER_TBL USER_ID FKY_BBF_CREATED_BY CREATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BLOOMBERG_FIELD TRG_BBF_LAST_UPDATED_BY|297|308|64,TRG_BBF_CREATED_BY|222|233|0,TRG_BBF_BIU|0|0|563 LAST_UPDATED_BY USER_TBL USER_ID FKY_BBF_LAST_UPDATED_BY LAST_UPDATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BLOOMBERG_FIELD TRG_BBF_LAST_UPDATED_BY|297|308|0,TRG_BBF_CREATED_BY|222|233|64,TRG_BBF_BIU|0|0|353 CREATED_BY USER_TBL USER_ID FKY_BBF_CREATED_BY CREATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BLOOMBERG_FIELD TRG_BBF_LAST_UPDATED_BY|297|308|64,TRG_BBF_CREATED_BY|222|233|0,TRG_BBF_BIU|0|0|563 LAST_UPDATED_BY USER_TBL USER_ID FKY_BBF_LAST_UPDATED_BY LAST_UPDATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BROKER_SUPERFAMILY TRG_BSF_LAST_UPDATED_BY|300|311|64,TRG_BSF_CREATED_BY|225|236|0,TRG_BSF_BIU|0|0|572 LAST_UPDATED_BY USER_TBL USER_ID FKY_BSF_LAST_UPDATED_BY LAST_UPDATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT N
CASH_VIEWS BROKER_SUPERFAMILY TRG_BSF_LAST_UPDATED_BY|300|311|0,TRG_BSF_CREATED_BY|225|236|64,TRG_BSF_BIU|0|0|362 CREATED_BY USER_TBL USER_ID FKY_BSF_CREATED_BY CREATED_BY REFDATA PKY_USR_USER_ID DISABLED SYN_CONTACT NEditor is loading...
Leave a Comment