What is the relationship between LSW_SNAPSHOT and LSW_PO_VERSIONS tables?

Question

How do you find a list of PO_IDs that belong in a snapshot? One way to do that is using below REST API. How do you do the same using DB queries?

https://localhost:port/rest/bpm/wle/v1/assets?processAppId=2066.bad7e03e-728e-420c-9cda-97e9380e3cfa&snapshotId=2064.a983bb1a-0751-4a59-8bb6-8bf8f6d9e6f0&branchId=2063.b98883de-58aa-44c4-aec0-c3ac29110e9b

Answer 1

reconstructing the twx POs from DB is not trivial, though can be done it will involve identifying the root pos and then for each root PO you will need to obtain the child PO information. Attached table has the combinations of Root PO and Child PO and the tables they are contained in. For dynamic analysis of Process Server DB we have a sequence of sqls which will reconstruct all the POs from Process DB.

Answer 2

it may be clear from this query which is retrieving all validation errors for a snapshot

SELECT DISTINCT VER.PO_VERSION_ID, VER.PO_ID, VER.PO_TYPE, PTY.PROCESS_TYPE_ID, VER.PO_GUID, VER.PO_ROOT_ID, VER.LAST_MODIFIED, VER.VALID_STATE, LUX.USER_NAME LAST_MODIFIED_USERNAME, VER.PO_NAME, PT.PO_TABLE_NAME PO_TABLE, PTY.PROCESS_TYPE_NAME,

dbms_lob.substr(LPR.DEPENDENCY_SUMMARY,1000,1) DEPENDENCY_SUMMARY, dbms_lob.substr(LPR.DESCRIPTION,1000,1) DESCRIPTION, LPR.ERROR_HANDLER_ITEM_ID, LPR.EXPOSED_TYPE,  LPR.IMAGE, LPR.IS_AJAX_EXPOSED, LPR.IS_ERROR_HANDLER_ENABLED,

LPR.IS_LOGGING_VARIABLES, LPR.IS_ROOT_PROCESS, LPR.IS_SECURED, LPR.IS_TRACKING_ENABLED, LPR.IS_TRANSACTIONAL, LPR.ITEM_LABEL, LPR.MOBILE_READY, LPR.PARTICIPANT_REF, LPR.PROCESS_TIMING_LEVEL,

LPR.SBO_SYNC_ENABLED, LPR.STARTING_PROCESS_ITEM_ID, LPR.TAB_GROUP, XT1.TYPEID, XT1.MESSAGEID, XT1.MESSAGE

FROM LSW_SNAPSHOT SNAP, LSW_BRANCH BRANCH, LSW_PO_VERSIONS VER, PO_TYPES PT, LSW_PROJECT LP, LSW_PROCESS LPR, PROCESS_TYPES PTY, LSW_USR_XREF LUX,

XMLTABLE (XMLNAMESPACES (

      'http://lombardisoftware.com/schema/povalidationdata' as "pov"

    ), '/pov:po-validation-data/pov:validation-err' PASSING xmltype(VER.VALIDATION_ERRS,nls_charset_id('AL32UTF8'))    

        COLUMNS   

            TYPEID varchar2(250) PATH '@type' ,   

            MESSAGEID       varchar2(100) PATH '@messageId', 

            MESSAGE       varchar2(1000) PATH '@message' ) AS XT1

WHERE SNAP.PROJECT_ID = BRANCH.PROJECT_ID 

AND BRANCH.BRANCH_ID = VER.BRANCH_ID 

AND SNAP.BRANCH_ID = VER.BRANCH_ID 

AND VER.PO_TYPE = PT.PO_TYPE_ID

AND LP.PROJECT_ID = "BRANCH".PROJECT_ID

AND SNAP.PROJECT_ID = LP.PROJECT_ID

AND LPR.PROCESS_ID = VER.PO_ID

AND LPR.VERSION_ID = VER.PO_VERSION_ID

AND PTY.PROCESS_TYPE_ID(+) = LPR.PROCESS_TYPE

AND VER.LAST_MODIFIED_BY_USER_ID = LUX.USER_ID

AND SNAP.SNAPSHOT_ID LIKE ?

AND VER.PO_TYPE = 1

AND VER.VALIDATION_ERRS is not null