11 Replies Latest reply on Nov 18, 2013 10:19 AM by Kevin Leben

    How can I get a report of the Work Request and Designs?

    Julio Dumeynieu

      Hello

      I am working with Designer. I want to know how can I get reports of Work Request and Designs in any format.

      Anybody can help me?

       

      Thank you in advance.

        • Re: How can I get a report of the Work Request and Designs?

          Hi Julio,

           

          I'm going to @ mention another place to help broadcast this question across a larger audience since you haven't gotten any responses from the Designer place yet: GIS Support

          • Re: How can I get a report of the Work Request and Designs?
            Kevin Leben

            Hi Julio,

             

            What type of reports are you looking for? Do you want a format different from what is available in Workflow Manager? All of this information is stored in the Workflow Manager database tables, so it may be possible to create SQL queries for your reporting requirements. Please let us know what kind of reports you require, and we'll see if someone in the community has experience creating them.

             

            Kevin

              • Re: How can I get a report of the Work Request and Designs?
                Julio Dumeynieu

                Hi Kevin,

                Thanks for your answer. In Workflow Manager I know that is possible to see reports of WR and Designs with their current status and I need them to be downloaded.

                You said that this information is stored in the Workflow Manager database tables, please can you tell me which table is the once that has the information?

                Kind regards

                 

                Julio

                  • Re: How can I get a report of the Work Request and Designs?
                    James Wright

                    Julio, there are numerous tables involved in both  Work functions and Designs. What particular information do you need to 'download'?

                    • Re: How can I get a report of the Work Request and Designs?
                      Kevin Leben

                      Hi Julio,

                       

                      The Workflow Manager tables are described here: http://resources.arcfmsolution.com/10.2/DesktopSDK/WFMDatabaseTables.html,

                      and the Process Framework tables here: http://resources.arcfmsolution.com/10.2/DesktopSDK/PXTables.html

                       

                      Here is some additional information on these tables and keys for joins in queries:

                       

                      CU Table
                      All assigned CUs are stored in the MM_WMS_COMPATIBLE_UNIT table.  This table contains the CU_Library_ID, Work_Function, Length, and Quantity of assigned CUs.

                       

                      CU Library
                      The CU Library table contains all available CUs and is stored in the MM_WMS_COMPATIBLE_UNIT_LIBRARY table. The CU table can be joined to the CU Library table to retrieve more information about the CU based on the following key fields:
                      MM_WMS_COMPATIBLE_UNIT.CU_LIBRARY_ID  MM_WMS_COMPATIBLE_UNIT_LIBRARY.ID

                       

                      Work Function Table
                      The work function table is used to translate the coded value work function to a string value.  The table is named MM_WMS_WORK_FUNCTION.  The CU table can be joined to the Work Function table based on the following key fields:
                      MM_WMS_COMPATIBLE_UNIT.WORK_FUNCTION_ID  MM_WMS_WORK_FUNCTION.ID

                       

                      Design Table
                      Some CUs in the CU table are associated to Work Locations, and other directly to the design.  If MM_WMS_COMPATIBLE_UNIT.WORK_LOCATION_ID > 0, then a join will be necessary to the Design table.  The CU table can be joined to the design table based on the following key fields:

                      MM_WMS_COMPATIBLE_UNIT.DESIGN_ID  MM_WMS_DESIGN.ID

                       

                      Work Location Table
                      Some CUs in the CU table are associated to Work Locations, and other directly to the design.  If MM_WMS_COMPATIBLE_UNIT.WORK_LOCATION_ID > 0, then a join will be necessary to the Work Location Table, then to the design table to find the CU’s for a design
                      The work location table is named MM_WMS_WORK_LOCATION.  The CU table can be joined to the work location based on the following key fields:
                      MM_WMS_COMPATIBLE_UNIT.WORK_LOCATION_ID  MM_WMS_WORK_LOCATION.ID

                      The work location table could then be joined to the design table based on the following key fields:
                      MM_WMS_WORK_LOCATION.DESIGN_ID  MM_WMS_DESIGN.ID

                       

                      Work Request Table

                      This table is MM_WMS_WORK_REQUEST. To get the designs associated with a work request you would join using the keys MM_WMS_DESIGN.WORK_REQUEST_ID and MM_WMS_WORK_REQUEST.ID

                       

                      Kevin

                      1 of 1 people found this helpful
                      • Re: How can I get a report of the Work Request and Designs?
                        Kevin Leben

                        Hi Julio,

                         

                        Here is a query that gets the state of all designs in MM_WMS_DESIGN. The where clause at the end makes sure that you only get designs (NODE_TYE_ID=11, from MM_PX_NODE_TYPE). Please try this out and see if it meets your requirements.

                         

                        SELECT

                           dn.ID as 'Design ID',

                           dn.NAME as 'Design Name',

                           dn.DESCRIPTION as 'Design Description',

                           cs.STATE_ID as 'State ID',

                           st.NAME as 'State'

                        FROM process.MM_WMS_DESIGN dn

                           join process.MM_PX_CURRENT_STATE cs on

                           dn.ID = cs.SOID

                           join process.MM_PX_STATE st on

                           cs.STATE_ID = st.STATE_ID

                           where st.NODE_TYPE_ID = 11

                        1 of 1 people found this helpful
                        • Re: How can I get a report of the Work Request and Designs?
                          Kevin Leben

                          Hi Julio,

                           

                          Here is another query that uses mm_wms_design, mm_px_state, mm_px_current_state and mm_px_versions. These tables all contain design infomation.

                           

                          SELECT

                             vr.NODE_ID as 'Design ID',

                             vr.NAME as 'Version Name',

                             vr.DESCRIPTION as 'Version Description',

                             dn.NAME as 'Design Name',

                             dn.DESCRIPTION as 'Design Description',

                             cs.STATE_ID as 'State ID',

                             st.NAME as 'State'

                          FROM process.MM_PX_VERSIONS vr

                             inner join process.MM_PX_CURRENT_STATE cs on

                             vr.NODE_ID = cs.SOID

                             inner join process.MM_PX_STATE st on

                             cs.STATE_ID = st.STATE_ID

                             inner join process.MM_WMS_DESIGN dn on

                             dn.ID = vr.NODE_ID

                          WHERE vr.NODE_TYPE_ID = 11 and st.NODE_TYPE_ID = 11 and vr.STATUS <> 4 -- STATUS of 4 means version is pending deletion, but Orphaned Versions Cleanup Tool hasn't deleted it yet. Let's leave these out.