Daily Queries in SOAINFRA & XREF

:

Find BRM poid & CRM row_id from XREF :

SELECT MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,3), 'BRM', XREF_COLUMN_NAME, NULL)) SOURCE_ID,
    MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,3), 'BRM', VALUE, NULL)) BRM,
    MAX(DECODE(XREF_COLUMN_NAME, 'COMMON', VALUE,NULL)) COMMON,
    MAX(DECODE(SUBSTR(XREF_COLUMN_NAME,0,4), 'SEBL', VALUE,NULL)) SIEBEL
  FROM (select * from xref_data where row_number in
(select row_number from xref_data where value in ('0.0.0.1 /billinfo 31XXXXXXXX 0')))
  WHERE IS_DELETED     ='N'
  AND (XREF_TABLE_NAME ='oramds:/apps/AIAMetaData/xref/CUSTOMERPARTY_BILLPROFILEID.xref')
  GROUP BY row_number,
    XREF_TABLE_NAME;

Find Instances in SOA_INFRA :

select b.ID "Instance ID",b.title "Name",a.composite_name"Composite",Decode(a.state,5,'SUCCESS','FAILED')"Instance State",a.modify_date "Time",TO_CHAR(SUBSTR((a.modify_date-a.creation_date),18,6)) "Execution_Time" from cube_instance a,composite_instance b where
a.cmpst_id=b.ID and b.title like '%97430037778%' order by 5 desc;

Find instance of some composite in SOA_INFRA :

select b.ID,Decode(a.state,5,'SUCCESS','FAILED') "STATUS",a.component_name,b.title,
TO_CHAR(a.creation_date,'MM/DD/YYYY HH24:MI:SS') "CREATION_TIME",TO_CHAR(SUBSTR((a.modify_date-a.creation_date),18,6)) "EXECUTION_TIME"
from VFQ_SOAINFRA.cube_instance a,VFQ_SOAINFRA.composite_instance b where
a.cmpst_id=b.ID and component_name like '%QueryRealTimeBalanceSurePayProvABCSImpl%'
and a.creation_date between
tO_DATE('01/13/2015 11:00:00','MM/DD/YYYY HH24:MI:SS') and
TO_DATE('01/13/2015 12:00:00','MM/DD/YYYY HH24:MI:SS') order by creation_date desc ;