Project and Portfolio Management Practitioners Forum
cancel

Work Item id on time sheet line for projects

SOLVED
Go to solution
Highlighted
JHarris941
Super Contributor.

Work Item id on time sheet line for projects

Hello All,

I'm using this query to return each item that is on a specific time sheet:

select tsl.work_item_id
from
tm_time_sheets ts,
tm_time_sheet_lines tsl
where
ts.time_sheet_id = tsl.time_sheet_id
and ts.time_sheet_id = 167552

When I add a request to book time to, the request number is shown as the item id, but when I add a project to the sheet, it shows a number that is unknown to me.  I checked to see if its a project id, but it is not.  Does anyone know what number is being displayed for projects on a time sheet? Here are the numbers that was returned:

 

127825 <--- req id
633135 <--- number returned for project
123274 <--- req id
115672 <--- req id

 

Thanks,

Jajcen 

6 REPLIES
Erik Cole
Acclaimed Contributor.

Re: Work Item id on time sheet line for projects

Hi,

tsl.WORK_ITEM_TYPE will inform what your ID is...looks like it may actually be a TASK_ID, are you booking time at the task or the project level?

We have a report that converts the task_id from a time sheet line into the project's request_id; the logic is

select to_char(PFM_REQUEST_ID)
 from PM_PROJECTS 
 where PROJECT_NAME = to_char(ktmg_utils.derive_work_item_set(tsl.WORK_ITEM_ID,tsl.WORK_ITEM_TYPE)

JHarris941
Super Contributor.

Re: Work Item id on time sheet line for projects

Thanks Erik!,

Correct me if im wrong, but you have to have a task on a project to book time to it right?  So I am booking time to the project and it is showing up as me booking time to the task

Based on your input, I did find that ID number on the WP_TASKS table :)

I ran your code, but it didnt work because "tsl" was a bad identifier because the time sheet lines table isint included.  How am I to use that ktmg_utils logic to join in that table? 

Thanks,

Jajcen 

 

Erik Cole
Acclaimed Contributor.
Solution

Re: Work Item id on time sheet line for projects

Right...that was just a snippet. You'd have to do something like

select
tsl.work_item_id,
tsl.work_item_type,
CASE
  When tsl.WORK_ITEM_TYPE = 'TASK' Then
    (select to_char(PFM_REQUEST_ID) from PM_PROJECTS where PROJECT_NAME = to_char(ktmg_utils.derive_work_item_set(tsl.WORK_ITEM_ID,tsl.WORK_ITEM_TYPE)))
  Else 
    tsl.work_item_id
END as ITEM_PARENT_ID
from
tm_time_sheets ts,
tm_time_sheet_lines tsl
where
ts.time_sheet_id = tsl.time_sheet_id
and ts.time_sheet_id = 167552

So PARENT_ID is the request_id of the related project. Your SQL would need to be able to handle whatever work_item_types you use, like MISC. There may be an easier way of doing it, but it gets the job done.

JHarris941
Super Contributor.

Re: Work Item id on time sheet line for projects

Awesome thanks alot Erik!,

By the way, do you know where I can find other utilitiy conversion logic processes from?

I feel as if there are more of them that you can pass column info into them and get more solutions.

Like I know they have one for last logon date by passing a user id into the code.

Thanks again Erik!,

 

Jajcen 

Erik Cole
Acclaimed Contributor.

Re: Work Item id on time sheet line for projects

Yeah there are a lot of functions in the packages in the database...you should browse them with SQL Developer. Most of the ones useful in queries are named starting with "GET_" so for example KCRT_REQUEST_UTIL.GET_LAST_UPDATE_DATE(). For ones corresponding to workflow info, look in KWFL etc.

Here's a quick & dirty list...

select object_name,procedure_name
from sys.all_procedures
where OWNER = '[your database user name]'
and object_type = 'PACKAGE'
and object_name like '%UTIL%'
and (procedure_name like 'GET_%' or procedure_name like 'DERIVE_%')
order by 1,2

JHarris941
Super Contributor.

Re: Work Item id on time sheet line for projects

You are the man! thanks alot Erik! couldn't have done this without you!