AnsweredAssumed Answered

Export Historical Timesheet Data

Question asked by MBUSA on Jun 23, 2015
Latest reply on Jun 24, 2015 by urmas

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;

Outcomes