The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How to find the sendToApprove time of a time sheet

SOLVED
Go to solution
Highlighted
Korayk
Occasional Contributor

How to find the sendToApprove time of a time sheet

Hi. I want find the last update date of the user's time sheet to approve in a script.

I use the tm_time_sheets.last_update_date but it is not a history table so if the

approver approves the time sheet updates this field. For this reason i could not find

when the user send it for approve.
Is the any way to overcome this problem.
My previous script is as below ;

 SELECT   u.username, p.start_date, p.end_date,
                    aef.actual_effort AS actual_effort,
                    ts.last_update_date AS system_date,a.LAST_UPDATE_DATE,
                    (SELECT u2.username FROM ppmktprod_usr.knta_users u2 where

u2.user_id=tsl.ACTUAL_APPROVER_USER_ID) onayci
               FROM ppmktprod_usr.knta_users u
                    INNER JOIN ppmktprod_usr.tm_time_sheets ts ON ts.resource_id =

u.user_id
                    INNER JOIN ppmktprod_usr.tm_time_sheet_lines tsl ON

tsl.time_sheet_id = ts.time_sheet_id
                    INNER JOIN ppmktprod_usr.tm_actuals a ON a.time_sheet_line_id =

tsl.time_sheet_line_id AND a.totals_flag = 'N'
                    INNER JOIN ppmktprod_usr.tm_actuals_effort aef ON a.actuals_id =

aef.actuals_id AND aef.actual_effort > 0
                    INNER JOIN ppmktprod_usr.ktmg_work_items_v wiv ON wiv.work_item_id =

tsl.work_item_id
                    INNER JOIN ppmktprod_usr.ktmg_periods p ON p.period_id =

ts.period_id
              WHERE u.user_data2 LIKE 'Uygulama G%'

3 REPLIES
Korayk
Occasional Contributor

Re: How to find the sendToApprove time of a time sheet

The picture in the attachment shows the field that  I want to take.

penoles
Regular Collector
Solution

Re: How to find the sendToApprove time of a time sheet

Korayk,

If you want to get the info showed in the picture, you should go to table ITG_AUDIT_EVENTS where 

ENTITY_TYPE=77 and FIELD_ID='state' and ENTITY_ID should be the time sheet id

 

Korayk
Occasional Contributor

Re: How to find the sendToApprove time of a time sheet

Thanks for your reply. It is very userfull. My script will be as below ;

SELECT   8 AS karne_id, u.username, p.start_date, p.end_date,
                    SUM (aef.actual_effort) AS actual_effort,
                    MAX (itg.EVENT_DATE) AS system_date
               FROM ppmktprod_usr.knta_users u
                    INNER JOIN ppmktprod_usr.tm_time_sheets ts ON ts.resource_id = u.user_id
                    INNER JOIN ppmktprod_usr.tm_time_sheet_lines tsl  ON tsl.time_sheet_id = ts.time_sheet_id
                    INNER JOIN ppmktprod_usr.tm_actuals a ON a.time_sheet_line_id = tsl.time_sheet_line_id AND a.totals_flag = 'N'
                    INNER JOIN ppmktprod_usr.tm_actuals_effort aef ON a.actuals_id = aef.actuals_id AND aef.actual_effort > 0
                    INNER JOIN ppmktprod_usr.ktmg_work_items_v wiv ON wiv.work_item_id = tsl.work_item_id
                    INNER JOIN ppmktprod_usr.ktmg_periods p ON p.period_id = ts.period_id
                    LEFT JOIN ppmktprod_usr.ITG_AUDIT_EVENTS itg ON itg.ENTITY_ID= tsl.TIME_SHEET_ID
              WHERE u.user_data2 LIKE 'Uygulama G%' and itg.FIELD_ID='state' and itg.ENTITY_TYPE=77 and itg.old_value=1 and itg.new_value=2   
           GROUP BY u.username, p.start_date, p.end_date

 

 

//Add this to "OnDomLoad" event