Solution - Designer - "Multiple States" Messages when Exporting Process Framework Workflow

Version 26

    Behavior

     

    When exporting the Process Framework Workflow in the Process Framework Administration Tool, one or more messages similar to the following appear during the Validating Database process of the export:

     

    Validating 15180 rows of table MM_PX_CURRENT_STATE with MM_PX_STATE

      *** (NodeTypeId, NodeId)=(10, 5860) has multiple states: (In Design, Initiated)

      *** (NodeTypeId, NodeId)=(10, 6002) has multiple states: (In Design, Initiated)

      *** (NodeTypeId, NodeId)=(10, 6029) has multiple states: (In Design, Initiated)

      *** (NodeTypeId, NodeId)=(10, 6058) has multiple states: (In Design, In Design)

      *** (NodeTypeId, NodeId)=(10, 6085) has multiple states: (In Design, Initiated)

      *** (NodeTypeId, NodeId)=(10, 6222) has multiple states: (In Design, Initiated)

     

    These messages have only been seen in exports from Oracle databases.

     

    Cause

     

    This is due to a node having more than one row in the MM_PX_CURRENT_STATE table. The first message shows that there are two rows in MM_PX_CURRENT_STATE for the NodeTypeId of 10 (the design node type), and NodeId 5860 (the design id). There is a row for the In Design state and a row for the Initiated state. Only one row should exist for each unique NodeTypeId and NodeId in this table.

     

    There are two scenarios in the messages above:

     

    1. Nodes having two rows with the same state:

    *** (NodeTypeId, NodeId)=(10, 6058) has multiple states: (In Design, In Design)

     

    2. Nodes having two rows with different states:

    *** (NodeTypeId, NodeId)=(10, 5860) has multiple states: (In Design, Initiated)

    *** (NodeTypeId, NodeId)=(10, 6002) has multiple states: (In Design, Initiated)

    *** (NodeTypeId, NodeId)=(10, 6029) has multiple states: (In Design, Initiated)

    *** (NodeTypeId, NodeId)=(10, 6085) has multiple states: (In Design, Initiated)

    *** (NodeTypeId, NodeId)=(10, 6222) has multiple states: (In Design, Initiated)

     

     

    Different methods are required to delete the duplicate rows in each scenario.

     

    Solution

     

    Delete the duplicate rows for each occurrence of the scenarios above.

     

    Note: Back up the MM_PX_CURRENT_STATE table before deleting the rows.

     

    1.  Delete all but one row for the nodes with duplicate rows.

     

    First, query for the SOID and STATE_ID's that are repeated in MM_PX_CURRENT_STATE to see potential deletions:

     

    select

      SOID, STATE_ID, count(*) as "Duplicate row count"

    from

      PROCESS.MM_PX_CURRENT_STATE

    group by

      SOID, STATE_ID

    having count(*) > 1 order by SOID

     

    Next, delete the rows using the method for the type of database is use.

     

    For an Oracle database:

     

    delete from
      PROCESS.MM_PX_CURRENT_STATE
    where
      rowid in
      (select min(rowid) from PROCESS.MM_PX_CURRENT_STATE group by SOID, STATE_ID having count(*) > 1)

     

    For a SQL Server database:

     

    Use the results from the query above to create delete statements. For each row returned by the query above, create SQL statements as shown in this example.
    Rows returned by the query:
    rows.png

    Each row requires a set rowcount and delete statement. Notice that the set rowcount value is the Duplicate row count value - 1.

     

    set rowcount 1

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 325 and STATE_ID = 16

     

    set rowcount 2

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 394 and STATE_ID = 16

     

    2.  Delete one row for the nodes having two rows with different states.
    For each of the messages that have different states, create a delete statement to delete the row with the earliest state in the workflow. For example, the delete statement for the message   *** (NodeTypeId, NodeId)=(10, 5860) has multiple states: (In Design, Initiated) would be:

     

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 5860 and STATE_ID = 12;

     

    The STATE_ID of 12 is derived from MM_PX_STATE. The Initiated state is earlier in the workflow than In Design for the NODE_TYPE_ID of 10 (Design), so this STATE_ID will be used in the delete statement. The states may be configured differently at your site, so check MM_PX_STATE to be sure that the correct state is being deleted.

     

    MM_PX_STATE:

    states.png

     

    For the messages above the delete statements would be:

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 5860 and STATE_ID = 12;

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 6002 and STATE_ID = 12;

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 6029 and STATE_ID = 12;

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 6085 and STATE_ID = 12;

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID = 6222 and STATE_ID = 12;

     

    A SQL "IN" condition may also be used instead of individual delete statements:

    delete from PROCESS.MM_PX_CURRENT_STATE where SOID IN (5860, 6002, 6029, 6085, 6222) and STATE_ID = 12;

     

     

    3.  After deleting these duplicate rows, run the export again and verify that the messages no longer occur.