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;