3 Replies Latest reply: Oct 17, 2012 9:07 AM by RMG123 RSS

    Extract for Activities and Owners at current step

    RMG123
      Can you help point me in the right direction of how to extract activities, their current workflow step, and the owner(s) for that current step? I can easily get activities out of the DB, but not current workflow step and owners at that step. Even if you can point me to the right tables I can probably get it from there but sql is always appreciated :)

      5.2 DB schema
        • 1. Re: Extract for Activities and Owners at current step
          Ron M-Oracle
          By Activities, I am assuming you talking about NPD Activities. If so, the following is how you can get the Activity status:


          SELECT ml.Title, st.Caption, * FROM
          npdActivities act
          inner join npdActivityML ml on act.pkid = ml.fkActivity and ml.langID = 0
          inner join npdWorkflowStatuses st on act.fkStatus = st.pkid and st.langID = 0

          We'll get you answers for the Owners shortly.
          • 2. Re: Extract for Activities and Owners at current step
            Huiping Cao-Oracle
            To extract the owners at the current step, please try this script:

            SELECT ml.Title, st.Caption, users.FirstName + ' ' + users.LastName, * FROM
            npdActivities act
            inner join npdActivityML ml on act.pkid = ml.fkActivity and ml.langID = 0
            inner join npdWorkflowStatuses st on act.fkStatus = st.pkid and st.langID = 0
            inner join npdActivityTemplateSteps ats on act.fkActivityTemplate = ats.fkParent
            inner join npdMatrixItems mi on ats.pkid = mi.fkOwnerParent
            inner join npdMatrixItemUserJoin jo on mi.pkid = jo.fkMatrixItem
            inner join users on jo.fkUser = users.pkid
            order by act.pkid;

            Thanks
            • 3. Re: Extract for Activities and Owners at current step
              RMG123
              Thanks - this gets me closer. The output seems to pull all steps and owners, however, I am looking to output Activity title, npdActivity.fkstatus = npdWorkflowStatuses.caption, and related npdMatrixItems step owners. Basically the output would show for each activity the current workflow status and owners at that step. Thx!