How-To - Designer - Query For All Design And Work Request States

Version 5

    Solution

     

    Use the following SQL queries to get the states of all Designs or Work Requests

     

    Design states:

    Design state information is maintained in these tables:
    MM_WMS_DESIGN
    MM_PX_CURRENT_STATE
    MM_PX_STATE
    MM_PX_NODE_TYPE

    This query will return the state of all designs in the MM_WMS_DESIGN table:

    SELECT
      st.Name,
      dn.*
    FROM
      ((MM_PX_CURRENT_STATE cst
      INNER JOIN MM_WMS_DESIGN dn ON cst.soid = dn.id)
      INNER JOIN MM_PX_STATE st ON st.state_id = cst.state_id)
      INNER JOIN MM_PX_NODE_TYPE nt ON nt.node_type_id = st.node_type_id
    WHERE
      nt.name = 'Design';


    Work Request states:

    Work request state information is maintained in these tables:
    MM_WMS_WORK_REQUEST
    MM_PX_CURRENT_STATE
    MM_PX_STATE
    MM_PX_NODE_TYPE

    This query will return the state of all work requests in the MM_WMS_WORK_REQUEST table:

    SELECT
      st.Name,
      wr.*
    FROM
      ((MM_PX_CURRENT_STATE cst
      INNER JOIN MM_WMS_WORK_REQUEST wr ON cst.soid = wr.id)
      INNER JOIN MM_PX_STATE st ON st.state_id = cst.state_id)
      INNER JOIN MM_PX_NODE_TYPE nt ON nt.node_type_id = st.node_type_id
    WHERE
      nt.name = 'WorkRequest';