Project and Portfolio Management Practitioners Forum
cancel

Creating a Time Sheet Details SQL Query

Highlighted
Stephen Cates
Trusted Contributor.

Creating a Time Sheet Details SQL Query

I have looked at all of the TM_XXX_YYY tables and cannot figure out how to associate the actual date with each actual value entered on a timesheet (TIME SHEET DETAIL) in an SQL query. I can find PERIODS but not a date for each value an individual enters on their time sheet. Any pointers would be appreciated.
10 REPLIES
EricDH
Contributor.

Re: Creating a Time Sheet Details SQL Query

There is a document called the "Supplied Entities Guide" it shows the relation ships between all the tables.
EricDH
Contributor.

Re: Creating a Time Sheet Details SQL Query

Sorry gave you the wrong doc, it should be the data model guide.
Semenov Alexand
Acclaimed Contributor.

Re: Creating a Time Sheet Details SQL Query

Hi,
see attach.
Good Luck
Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

I tried the SQL provide but was not that successful since there were several one-to-many (Calendar_ID and ACTUALS_ID) situations that resulted in more results than I should have gotten and I got no single day results that added up to our 8-hour-day.

I have been through the Data Model Guide and while it is mostly complete, it does not provide you with a road map as to how to extract data from which tables to get the results you want.

I am still groping to find a way to map the ACTUAL_EFFORT to a specific day of the year.

Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

I have solved the riddle. Utilizing the CALENDAR_ID in TM_TIME_SHEETS (linked to ITG_COMPONENT)to get the date and using EFFORT_ORDER in TM_ACTUALS_EFFORT as a day incrementer, I can now reproduce actual values entered into timesheets. The SQL previously provided was a good stepping stone to the solution.
Erik Cole_1
Outstanding Contributor.

Re: Creating a Time Sheet Details SQL Query

Hi Stephen,

FYI you can also use the KTMG_ views to return time sheet info...

select ts.resource_full_name,tsl.work_item_set,tsl.work_item,d.actual_date,d.actual_time
from ktmg_time_sheets_v ts
join ktmg_time_sheet_lines_v tsl on tsl.time_sheet_id = ts.time_sheet_id
join ktmg_time_sheet_line_details d on (d.time_sheet_line_id = tsl.time_sheet_line_id AND d.totals_flag = 'Y')
where d.actual_time > 0
and d.actual_date >= sysdate-60
order by 1,4,3
Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

Those views are not listed in my Data Model Guide, where can I find them?
Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

The KTMG views do not appear to have all of the latest timesheet data and they are summing values since I am getting values > 8 (8-hours in a day)in the ACTUAL_TIME field.
Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

The way that the KTMG_TIMEXXX views are summing and presenting data does not provide me with the detail that I need and I am able to get with my SQL.

Thanks for the pointer to the views though....I will explore them further.
Stephen Cates
Trusted Contributor.

Re: Creating a Time Sheet Details SQL Query

I have solved the riddle. Utilizing the CALENDAR_ID in TM_TIME_SHEETS (linked to ITG_COMPONENT)to get the date and using EFFORT_ORDER in TM_ACTUALS_EFFORT as a day incrementer, I can now reproduce actual values entered into timesheets. The SQL previously provided was a good stepping stone to the solution.