3 Replies Latest reply on Oct 17, 2012 2:07 PM by Randy G

    Extract for Activities and Owners at current step

    Randy G
      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
              Randy G
              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!