Clarity

  • 1.  Export Historical Timesheet Data

    Posted Jun 23, 2015 03:47 PM

    I have been tasked with exporting all timesheet task line items from our Clarity DB. I have gotten off to a good start so far, but am unable to find where the approval timestamp is in the database.  I have been reviewing 'Clarity Technical Reference Guide' to learn more about the DB table structure, but it does not 100% match to what I see (likely different DB versions).

     

    I am not a Clarity user and have no access to the front end.  Here is what I have so far.  Any help finding where timesheet approval timestamps are held would be greatly, greatly, greatly appreciated!!  Thanks so much.

     

    SELECT

          prtimesheet.prid as "TIMESHEET ID", -----CLARITY TIMESHEET UNIQUE IDENTIFIER

          time_period_table.prstart as "START", -----DATE TIMESHEET STARTS

          time_period_table.prfinish as "FINISH", -----DATE TIMESHEET ENDS

          prtimesheet.posted_time as "SUBMITTED DATE", -----DATE TIMESHEET WAS SUBMITTED

          --WAS NOT CORRECT-- time_period_table.prpostedtime as "POSTED TIME", -----DATE TIMESHEET WAS SUBMITTED

          (hours_table.practsum / 60 / 60) as "HOURS", -----HOURS LOGGED ON TASK

          hours_table.prassignmentid as "ASSIGNMENT ID", -----CLARITY ASSIGNMENT UNIQUE IDENTIFIER

          assignment_table.prtaskid as "TASK ID", -----CLARITY TASK UNIQUE IDENTIFIER

          task_table.prname as "TASK NAME", -----TASK LOGGED ON TIMESHEET

          prtimesheet.prresourceid as "RESOURCE ID", -----CLARITY SUBMITTER UNIQUE IDENTIFIER

          submitter_table.full_name as "RESOURCE", -----PERSON SUBMITTING TIMESHEET

          prtimesheet.prapprovedby as "APPROVER ID", -----CLARITY APPROVER UNIQUE IDENTIFIER

          (approver_table.last_name || ', ' || approver_table.first_name) as "APPROVER" -----PERSON APPROVING TIMESHEET

    FROM prtimesheet

    JOIN prtimeperiod time_period_table

    ON prtimesheet.prtimeperiodid = time_period_table.prid

    JOIN srm_resources submitter_table

    ON prtimesheet.prresourceid = submitter_table.id

    JOIN cmn_sec_users approver_table

    ON prtimesheet.prapprovedby = approver_table.id

    JOIN prtimeentry hours_table

    ON prtimesheet.prid = hours_table.prtimesheetid

    JOIN prassignment assignment_table

    ON hours_table.prassignmentid = assignment_table.prid

    JOIN prtask task_table

    ON assignment_table.prtaskid = task_table.prid

    ORDER BY time_period_table.prstart asc, prtimesheet.prresourceid asc;



  • 2.  Re: Export Historical Timesheet Data
    Best Answer

    Posted Jun 23, 2015 04:07 PM

    I don't believe timesheet approval timestamps are permanently recorded (particularly since you're referring to historical records).

     

    The data would have been in PRTimesheet but it only records two timestamps; the most recent modification of the prtimesheet record in field prmodtime, and when the timesheet was posted in field posted_time.

     

    For timesheets that undergo approval using Clarity processes, you could in theory determine when that step and action was taken by incorporating some non-trivial references to the process (bpm) tables, however even if you used those it is still unlikely I think that you would have retained old process instances for every past approved/posted timesheet once those processes were completed, you would almost certainly have had those deleted.

     

    So I am not certain you will be able to get that data from anywhere, but if you suspect your approval actions might be leaving a trail for this somewhere in the database, please describe your approval process in more detail and we can consider that.



  • 3.  Re: Export Historical Timesheet Data

    Posted Jun 23, 2015 04:19 PM

    Awesome thank you Nick for the fast/helpful reply.  While not great news for me - you can't export what doesn't exist!    Cheers.



  • 4.  Re: Export Historical Timesheet Data

    Posted Jun 24, 2015 01:09 AM

    That has been asked a couple of times before and Nick's has given the same answer as before.

    One of the links to earlier discussions still work

    RE: Timesheet Approved Time