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 ;

RDA to run on 8.X version

Copy the RDA from below URL :

1. IMPORTANT: Download latest RDA from Note 314422.1 , alternatively you can use below link:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=390193761380843&id=314422.1&_afrWindowMode=0&_adf.ctrl-state=17ze6fiyoc_4

important: if you don't have perl you can use the binary version as described in this note

2. Unzip the the file into the SOA Server machine on a location of your preference.

3. From a command prompt enter the command:

./rda.sh -ySCRP -e'RDA.BEGIN.D_ORACLE_PARENT=<MIDDLEWARE_HOME>,OFM.SOA.D_ORACLE_HOME=<SOA_HOME_PATH>,OFM.SOA.D_DOMAIN_ROOT=<DOMAIN_PARENT_PATH>,OFM.SOA.T_DOMAINS=<SOA_DOMAIN_NAME>' -p FM11g_SoaMin

For example the command might look like this:

#PROJECT Specific command

./rda.sh -ySCRP -e'RDA.BEGIN.D_ORACLE_PARENT=/app/oracle/product/fmw/soa,OFM.SOA.D_ORACLE_HOME=/app/oracle/product/fmw/soa/Oracle_SOA1,OFM.SOA.D_DOMAIN_ROOT=/app/oracle/admin/vfq_domain/mserver,OFM.SOA.T_DOMAINS=vfq_domain' -p FM11g_SoaMin


4. Provide the output zip file generated, usually into the same rda folder where above command is ran in the name of: RDA_output_*.zip

Daily Queried to perform purging OSB DB

+++++++++++++++++++++++

p11676998_111130_Generic

+++++++++++++++++++++++

BEGIN

CMS_ORASDPM.UMS_CLEANUP.PURGE(200);

END;

/

+++++++++++++++++++++++

CR325307utility

+++++++++++++++++++++++

DECLARE

amount NUMBER;

BEGIN

REMOVE_REPORT_DATA_OLDER_THAN(50, 1000 ,amount);

dbms_output.put_line(amount);

END;