XRefUtility 10g - Round 2

We were using XrefUtility and realized that Xref utility allows us to run select clause on source and target database, so that we can manipulate any data before comparing it with Xref table values. In our case, we had to manipulate the XRef values as well before comparing it with source and target database.

For example, it is pretty common to add revision number in BRM values in Xref table. Those revision number changes in BRM without getting updated in AIA database. To solve that problem, revision number should be truncated from AIA XREF and target query. I had to modify XRefUtility to achieve this functionality. I enhanced UI as below:

Another thing we found, is that going through 8 or so report is so cumbersome. If you look at my blog, you can see each report is combination of different cases. It would be nice to have all discrepancy in one or two reports, and have more control over queries. I ended up just writing my own utility in SQL as below:

• create source view
  drop materialized view sv;
  create materialized view sv as
  select a.row_id as sv_id, a.name as sv_name from SIEBEL.S_ASSET@sbltst a, SIEBEL.S_PROD_INT@sbltst b, SIEBEL.S_ORG_EXT@sbltst c, SIEBEL.S_ASSET@sbltst a2
  where a.PROD_ID = b.ROW_ID
  AND a.BILL_ACCNT_ID = c.row_id
  AND a.root_asset_id = a2.row_id
  AND a2.status_cd = 'Active'
  
  
  
• create target view
  drop materialized view tv;
  create materialized view tv as
  select * from (
  select '0.0.0.1 '||PP.poid_type||' '||PP.poid_id0 AS tv_id, pp.descr as tv_name
  from PIN.group_t@BRMMIG g, .....
  
  
• create xref view (make it tabluer from row based view so that we can do joins and easier query).
  drop materialized view xv;
  create materialized view xv as
  select row_number, XREF_TABLE_NAME as xref_table_name, max(decode(XREF_COLUMN_NAME, 'SEBL_01', VALUE,null)) sv_id ,max(decode(XREF_COLUMN_NAME, 'COMMON', VALUE,null)) common_id
  ,max(decode(XREF_COLUMN_NAME, 'BRM_01', rtrim(substr(value,0,length(VALUE)-2)),null))
  tv_id from XREF_DATA
  WHERE XREF_TABLE_NAME like 'INSTALLEDPRODUCT_ID' group by row_number, XREF_TABLE_NAME
  
  

Note we can create materialized view so that it doesn't affect the performance and refresh the view only when required. Now it is pretty simple to generate all reports and report numbers and data in one report:

• count for sv_id null in xv - shows all descrepancy where source id is missing in AIA Xref Database
  select count(*) from xv where sv_id is null
  select * from xv where sv_id is null
  
  
• count for tv_id null in xv - shows all descrepancy where target id is missing in AIA Xref Database
  select count(*) from xv where tv_id is null
  select * from xv where tv_id is null
  
  
• count for rows in sv not in xv - shows all descrepancy where data is in source database but missing AIA xref
  select count(*) from sv where not exists ( select * from xv where xv.sv_id = sv.sv_id )
  select * from sv where not exists ( select * from xv where xv.sv_id = sv.sv_id )
  
  
• count for rows in tv not in xv - shows all descrepancy where data is in target database but missing AIA xref
  select count(*) from tv where not exists ( select * from xv where xv.tv_id = tv.tv_id )
  select * from tv where not exists ( select * from xv where xv.tv_id = tv.tv_id )