What is the sql query to find out which BPDs in which application and snapshot have Autotracking turned on?

Question

Recently aototracking is turned off by default but since we migrate existing legacy applications there is a good change that lot of BPDs may have autotracking turned on unintentionally, how can we find out such BPDs for entire environment with SQL query

Answer 1

You can retrieve the list with the following SQL for oracle and IBM BPM 8.6 (may work for earlier versions also)

select distinct LPV.LAST_MODIFIED as LastModifiedTime, LB."NAME" as BPDName , LP."NAME" as ApplicationName, LS."NAME" as SnapshotName, LBA.NAME as BranchName, LUX.USER_NAME as LastModifiedUser
from  LSW_PO_VERSIONS LPV, LSW_BPD LB, LSW_SNAPSHOT LS, LSW_PROJECT LP, LSW_BRANCH LBA, LSW_USR_XREF LUX
where LB.VERSION_ID = LPV.PO_VERSION_ID
and LPV.BRANCH_ID = LS.BRANCH_ID
and LS.PROJECT_ID = LP.PROJECT_ID
and LS.BRANCH_ID = LBA.BRANCH_ID
and LUX.USER_ID = LPV.LAST_MODIFIED_BY_USER_ID
and dbms_lob.instr(LB."DATA", utl_raw.cast_to_raw ('<autoTrackingEnabled>true</autoTrackingEnabled>')) <> 0