Clarity

Hello,
We have a timesheet from Feb for a resource. My manager approved the timesheet and the timesheet status is Approved but it is not posted at all. Anyone have any idea or any reason for this. Please let me know. Also how are timesheets posted after managers approves it. Does any process/job updates it. what is the process name? where can check the error for this?

Please let me know for any suggestions are info.

Thank you
RK

Hi,

Have you run / scheduled the post timesheets Job?.

Post Timesheets Job-->

The Post Timesheets job is a background process that compiles and posts actuals and enters them into the project plan. This job runs at scheduled
intervals. It generates timesheet actuals from the hours for the resource assignment that were approved for at least five minutes and the time with
finish dates in the past (at least five minutes ago).
This job does the following:
Updates the resource assignment and the Transaction Import table with the actuals on the timesheets. The timesheet's status changes to "Approved".
Advances Estimate To Complete (ETC) past the time period for the posted timesheets on all task assignments of the corresponding resources.

cheers,
sundar

Sundar,
This is happening for one resource and only for 1 timesheet. We have timesheet jobs that is scheduled. Only 1 resource for feb 3rd week timesheet is not posted and it is aproved long time back. All others are posted.

Thanks
RK

Is the time period closed?

Tammi,
How can i know whether time period is closed? Its for Feb 3 week. status is approved.

Thank you

It should say closed on the timsheet, a yellow alert should be visible or you can go to the Admin side to the Project Management section, view Time Periods for the open or closed one.

Tammi,
i checked the time period, It's still open!! Not sure why there is this 1 resource time sheet is not posted for that period?

Check the resource is Track Mode set correctly? I am sure that has nothing to do with it but just in case.

Tammi,
where can i check that?

Thank you

Resource (link under Resource Management) > Resource: Properties: Main - General (after clicking the resource name) ...
You will find -

1. Track Mode - should be Clarity
2. Open for Time Entry - checkbox should be checked

NJ

@Navdeep.. The above scenarion the user will not be able to book time if the Track Mode is not clarity and open for time entry is not checked.

There are various cases where the timesheets will not get posted.

Slice Status is not correct.
Last updated by is not correct.
Time periods are closed.
Slice job not running properly.

Thanks,
Manish.

mansriva wrote:

There are various cases where the timesheets will not get posted.

Slice Status is not correct.
Last updated by is not correct.
Time periods are closed.
Slice job not running properly.

Careful with that advice - I do agree that there are quite a few reasons why a timesheet might not get posted - but timeslicing issues are not one of them, there is no direct relationship between the timeslicing job and posting timesheets.... you can post timesheets when the timeslice job is not even set to run for example. ( So I dispute 'Slice Status is not correct' and 'slice job not running properly' )

'Last updated by is not correct' - where are you suggesting this is a problem?; "last updated" is a column on MANY tables and there are known issues where it is set 'wrong' (-99) on projects/tasks/risks etc, but I don't know of a specific case where this would cause an issue in posting timesheets - can you expand on this?

'Time periods are closed' - yes, and this has already been mentioned (and discounted) in this thread.

--

@Arun - is that SQL some SQL extracted from the "Post Timesheet" job or just something you have put together? If it is from the Post Timesheet job then that is exactly what I would suggest needs to be executed in order to work out why a particular timesheet is not being picked up..... (and if its something you have put together then perhaps we should just grab the SQL from the job instead :glare: )

'Last updated by is not correct' - where are you suggesting this is a problem?; "last updated" is a column on MANY tables and there are known issues where it is set 'wrong' (-99) on projects/tasks/risks etc, but I don't know of a specific case where this would cause an issue in posting timesheets - can you expand on this?

Slice Status: We had two issue both with posting actuals not getting posted.
For one the reason was the slice_staus in Prassignments table was not correct it was some E**.
For one last updated by was -99 as indicated by you.

Thanks,
Manish

^ OK a 'corrupted' assignment record might cause an actual value to not get posted to the plan, but this is not what would affect whether a TIMESHEET gets posted or not (i.e. what changes the timesheet status from Approved to Posted), which is the point of this thread! :vader:

I've never seen "E**" in a slice_status field, indeed I'm surprised that that could even happen (the field is numeric after all so it physically can not hold an alpha-string (on Oracle) :wacko: )

--

PS - and when you use the [ quote] tags, you are meant to include the text in between the tags, else you just get a;


when you probably meant

your quoted text should go here

:grin:

Hello Dave,

Yep the query is from post timesheet job.

Thanks,

Arun

Hi,

The queries used in post timesheets job.(The xql file where the queries are defined)

cheers,
sundar

Hello Riya,

Run the below query and check that the resource is getting listed in the query result.

select PRTimeSheet.*, PRTimePeriod.prStart, PRTimePeriod.prFinish
from PRTimeSheet, PRTimePeriod, PRJ_RESOURCES
where PRTimeSheet.prStatus=3
and PRTimeSheet.prResourceID=PRJ_RESOURCES.prID
and PRJ_RESOURCES.prTrackMode=2
and PRTimeSheet.prTimePeriodID=PRTimePeriod.prID
and 4=4
order by PRTimePeriod.prStart

Thanks,

Arun

@Riya, Arun has confirmed that the query above is what the Post Timesheet job is actually using to pick up the data, so we can see from the query that the things that would prevent a timesheet getting picked up would be if;

PRTimeSheet.prStatus does NOT =3 - this is the TIMESHEET status, and '3' is AUthorised, so that is OK in your case

PRJ_RESOURCES.prTrackMode does not =2 - this is the resource's "Track mode" and '2' is Clarity - this is why you were asked to check this earlier by Tammi and NJ - how did that turn out?

Dave,
i checked the prStatus=3 and track mode = 2 which is correct. I'm not understandng why this not picked up and changed to posted.

Thank you
RK

Hello All,
I checked the track mode, openfor time entry etc. Everything looks good. I also ran the query from timesheet job. I got one row which is approved and not changed to posted.

It is approved and not getting posted just for that time period and i checked the timeperiod and it is still open. Any thoughts?


Thanks a lot for your time and suggestions. Appreciate it.

RK

You could try 'returning' it, then 'submitting' and 'approving' it again; to see if that kicks it clear? (I'm GUESSING now)

Dave,

i did that. Returned it, Submitted and approved it. Ran the post timesheet job and still no luck for the same resource and same entry:sad. Here is the error from br-niku.log

ERROR 2011-06-24 07:15:04,070 [Dispatch Thread-2668 : bg@kocniku01] xql2.xbl (none:none:none) ****POSTTIMESHEET: Couldn't post time entry-->timesheetID=5009388; timeEntryID=5135776; assignmentID=

It doesn't give any reason for this error.

Hello Riya,

Try again the steps you did before by setting the sql trace ON. So that you can get some detail on the error.

Thanks,

Arun.

Arun,
Where can i turn ON SQL trace? I'm sure i did that setting before but i totally forgot. Also when i turn on SQL trace where can i find the error? Is that same log file br-niku.log? Please let me know.

Thanks again
RK

Riya,

Update cmn_Sec_users set sqltrace_active=10 where user_name='login user'.

You can find the log files inside clarity installation folder

clarity->logs-sqltrace->


Thanks,

Arun

Would sqltrace record why the timesheet is not being posted? (rephrase: would sqltrace record what the post timesheet job is executing): I do not think sqltrace would record that.
One other cause I can think of which might prevent the timesheets from being posted is that the BLOB objects might have been corrupted.

If you have this issue replicated in the test/dev system, try out the following:
1) Open a timesheet and add any task to it. Do not enter any time.
2) Replace the BLOB object (PRACTCURVE of PRTIMEENTRY table) of your affected timeentry records with the new timesheet's BLOB object using a sql update statement. Commit the changes.
3) Re-enter time in the affected timesheet, submit and approve it and let the post timesheet job run.

Check if the timesheet is posted.

Hello sankhadeep,

You are right. Sql trace will not record this. Sorry for that.

Riya,


Another option is that you can go to NSA

Go to server setting->logs-edit configuration

1. Enable DEBUG for com.niku.projmgr and com.niku.xql2
2. run the Post Timesheets job
3. Disable DEBUG for com.niku.projmgr and com.niku.xql2(.e.g. set it back
to ERROR)
4. Check the bg logs

Thanks,

Arun.

^^ you can even recreate the timeentry BLOB in the application itself; return the timesheet, edit the timeentry record (set all values to zero). Save the timesheet. Edit the timeentry and put back the original timeentries, save the timesheet again . BLOB is rebuilt!

(Might be worth trying that then - still GUESSING though)

I cleared all the time and saved it. But in total it still shows 4 hours and when i opened the time there is nothing no details of that 4 hours. It shows all zeros but the total on timesheet page is 4. Is this causing that error. Where did this 4 hours come from without being on the time sheet. I feel like this timesheet is haunted :) lol

Any guesses or thoughts?

Thank you
RK

You could try deleting the rows in the timesheet, saving the timesheet and then re-adding the rows.... that might get rid of the ghosts?

BUT you might have problems re-adding rows (because things might have changed in the project plan since they were originally added, like the task being now closed for time entry)

Hi,

Rk wrote
/*
I went and checked in my Prod Database in prtimeentry table PRACTSUM field the hours are showing only 4 (14400/3600) where as on the webpage the timesheet detailed page shows the total hours as 37.50 and summary page shows 41.50. Which is weird. Why the time is different in 3 places? where is this 4 hours coming from?
*/

The task might be deleted so the assignment id is not really existing.Pls check the below KB.

Important:- Use only the select statements.If any results then we can consider using delete.

select * from prassignment where prid = (enter assignment id).

Pls run the query below:- (oracle)

*******************************************************************************************************************************************************************************************************************************************

How can time entries that don't have an assignment associated with it be cleaned up?

Description

If a timesheet view is showing the individual time correctly but the total time is more than it should be, this might be due to orphaned time entries.

Solution

The first query to run is below:

(resource id and timeperiod id should appear in the URL of the timesheet)

In this scenario, the user is reporting that the total hours is 20 hours more than it should be.

select ts.prid, ts.prstatus, ts.pradjustedid, te.prid, te.practsum/3600,
te.prassignmentid
from prtimeentry te, prtimesheet ts
where te.prtimesheetid = ts.prid
and ts.prresourceid = xxxxxxx
and ts.prtimeperiodid = yyyyyyy

For example:

select ts.prid, ts.prstatus, ts.pradjustedid, te.prid, te.practsum/3600,
te.prassignmentid
from prtimeentry te, prtimesheet ts
where te.prtimesheetid = ts.prid
and ts.prresourceid = 5002335
and ts.prtimeperiodid = 5000033

Below is an example of the query results from the query above

prid prstatus pradjustedid prid (No column name) prassignmentid
5007039 3 NULL 5071791 15 5017102
5007039 3 NULL 5071792 6 5017297
5007039 3 NULL 5071801 16 5017496
5007039 3 NULL 5071789 1 5013871
5007039 3 NULL 5071790 2 5014425
5007039 3 NULL 5066975 20 5017739

Since the reported problem is that it is showing 20 hours more than it should have, this points to assignment id 5017739 as a possible orphaned time entry.

The next query will tell us if the assignment id is valid:

select prid, prtaskid, prresourceid
from prassignment
where prid = xxxxxxx (plug in value of suspect prassignmentid)

e.g.
select prid, prtaskid, prresourceid
from prassignment
where prid = 5017739

If this query doesn't return anything, this indicates an orphaned time entry without an assignment. To clean this up, the following two queries should be run:

delete from prnote where
prtablename = 'PRTimeEntry' and
prrecordid = ******

delete from prtimeentry where
prid = ****** and
prassignmentid = yyyyyyy

****** represents the time entry id (PRTimeEntry.prid)
yyyyyy represents the assignment id (PPTimeEntry.prassignmentid)

e.g.
delete from prnote where
prtablename = 'PRTimeEntry' and
prrecordid = 5066975

delete from prtimentry where
prid = 5066975 and
prassignmentid = 5017739

This should only delete 1 record. A commit should be run afterwards to ensure the delete is done.

** It is always recommended to take a database backup prior to deleting records in the database.
*******************************************************************************************************************************************************************************************************************************************************************************
cheers,
sundar

Sundar,

It works like a charm. Now my timesheet shows posted. Thank you for your examples.

I tried following your steps. I was able to remove the hidden 4 hours and the totals show correct as per the timesheet

I followed your steps but i did not get any row from Just wondering?
PRNOTE for prtablename = 'PRTimeEntry' and
prrecordid = '5135776'

but i deleted one row from timeentry which deleted the hours that are orphaned.

niku.prtimeentry where
prid = '5135776' and
prassignmentid = '5018584'


Thank you for your help.
RK

OK, you have removed prtimeentry with ID=5135776 which we identified from the error log you posted earlier;

ERROR 2011-06-24 07:15:04,070 [Dispatch Thread-2668 : bg@kocniku01] xql2.xbl (none:none:none)
****POSTTIMESHEET: Couldn't post time entry-->timesheetID=5009388; timeEntryID=5135776; assignmentID=

So what does the error log say now?

Dave,
It actually worked! I ran the post timesheet job 2-3 times. Deleted timesheet re did it everything and then it showed posted.

Thank you for your help i really appreciate it!

RK

Phew! :happy

(it was the deleting of the corrupt/orphaned timeentry record that did it I think)

BIG team effort! cool)

Hi Sundar

We have a similar issue.

Using the above mentioned query, I put in the resource id and the tp id, and ran the below query -

select ts.prid, ts.prstatus, ts.pradjustedid, te.prid, te.practsum/3600,
te.prassignmentid
from prtimeentry te, prtimesheet ts
where te.prtimesheetid = ts.prid
and ts.prresourceid = 5555061
and ts.prtimeperiodid = 5000121;

I've pasted the result below

PRID
PRSTATUS
PRADJUSTEDID
PRID_1
TE.PRACTSUM/3600
PRASSIGNMENTID
5,433,323
5

7,598,204
16
5,373,482
5,464,482
4
5,433,323
7,754,948
16
5,395,484
5,464,482
4
5,433,323
7,754,947
0
5,373,482
5,483,125
3
5,464,482
7,855,678
0
5,395,484
5,483,125
3
5,464,482
7,855,679
0
5,373,482

A quick response would be much appreciated

Regards
NJ

navzjoshi00 wrote:

We have a similar issue

So have you tried ALL the ideas in this thread.... checking your settings.... recreating the timesheet.... looking at the data to identify the "corrupt" record.... deleting that record..... resubmitting the timesheet..... etc?

What is the result? (That query does not really tell us anything on its own, apart from the fact that you have adjustments "in play" too)

I ran the below query -

SELECT * FROM PRTIMEENTRY where prassignmentid in (select prid
from prassignment
where prid = 5373482);


I've attached the output for reference.

NJ

navzjoshi00 wrote:

I ran the below query -
...
I've attached the output for reference.

This still does not tell us anything useful!


You need to explain exactly what your apparent "similar" problem is!

You need to tell us what you see on the screens - on the timesheet list screen and the timesheet detail itself.

You need to tell us what attempts you have made to "fix" the problem;

Dave wrote:

So have you tried ALL the ideas in this thread.... checking your settings.... recreating the timesheet.... looking at the data to identify the "corrupt" record.... deleting that record..... resubmitting the timesheet..... etc?

and exactly what symptoms you are seeing (bg logs included)

--

You can tell from this thread that there are many possibilities (some functional, some technical) as to why a timesheet is not posting - you need to tell us exactly what your situation is or else all we can do is say "have you tried all the above ideas?"

The timesheet is an adjustment of an adjustment, and the hours are for weekend - Sat & Sun.

The timesheet was approved in 1/1/2011, but still not posted

We've even tried submitting the timesheet for approval again, but no luck

We've checked the following -

1. open for time entry - project / task (phase as well) / resource
2. track mode = clarity - project / resource
3. project and resource are active, adn the two tasks do exist

I've attached the detailed screenshots as well ... :*)


NJ

Looking at your screenshots ; the adjustment is to blank out all the hours - so why not just remove* the timeentries completely from the timesheet and try posting that (zero entry) timesheet
( * - remove in the application not the database - just return the timesheet, remove the timeentries and re-submit and approve it)

That way you remove any complication to do with the timeentry record, the assignment, the task etc - the only functional things "in play" then should be the time period being open (which it is from the screen shots) and the resource being open and tracked in clarity (which it is). If that STILL doesn't post then you need to look at the BG logs for an error message.

Dave wrote:

....If that STILL doesn't post then you need to look at the BG logs for an error message.

Checked the bg logs, had received this error

ERROR 2011-06-14 10:08:06,383 [Dispatch Thread-1911 : bg@nlyehvgdc1ms219] xql2.xbl (none:none:none) ****POSTTIMESHEET: Couldn't post adjustmented timesheet-->adjustedTimesheetID=5464482; adjustedTimeEntryID=7754947;
assignmentID=5373482

NJ

OK that message is telling you that there is a "problem" with the ADJUSTED timesheet rather than the ADJUSTMENT - i.e. the timesheet with the 16 hours on it.

Check that the timeentries on that (to-be) ADJUSTED timesheet have not got "corrupted" somehow - this seems to be a much more complicated problem than Riya's earlier problem then since we could fix the corrupted records because they were on the timesheet that was failing posting - your problem is on an earlier timesheet (already posted) that is trying to be adjusted.

NJ,

You can have a look at the below KB -->Document ID: TEC507660 .

Since your adjusted timesheets are also null this is relevant.Give an try..


Adjustment of an adjustment timesheet does not post due to a NullPointer exception. This prevents the timesheet from posting.


Description:

Error messages similar to the following appear in the bg-niku.log file when the curve returns a NULL value. The result is that the timesheet doesn't get posted and a NullPointerException message is written to the log. The defect for this issue is CLRT-23415.

ERROR 2008-09-18 14:45:34,930 [Dispatch Thread-39 : bg@moosh05-755] xql2.xbl (none:none:none) ****POSTTIMESHEET: Couldn't post adjustmented timesheet-->adjustedTimesheetID=5000074; adjustedTimeEntryID=5000718; assignmentID=5000023
ERROR 2008-09-18 15:00:00,107 [Dispatch Thread-61 : bg@moosh05-755] niku.xql2 (none:none:none)
java.lang.NullPointerException

Solution:

The fix for CLRT-23415 is in Clarity 8.1.3 and Clarity 12.0.1

1. Go into Clarity and populate a brand new timesheet with direct entries. The timesheet must not have any time entered against it.

2. Choose a time entry that does not have any time entered against it.

3. Move mouse over time entry note icon and observe the id in the browser's status bar. Note this id down. (i.e. 14944138)

Note: You will need to pick a new time entry off a timesheet each time, because we cannot use a timeentry that has time entered against it.

4. Verify that the time entry we picked has no hours by looking at the practsum field of the time entry (practsum should = 0).

select * from prtimeentry where prid = 14944138

5. Identify the time entries that you would like to change:

select te.prid
from prtimesheet ts, prtimeentry te, prtimeperiod tp
where te.prtimesheetid = ts.prid
and ts.prtimeperiodid = tp.prid
and ts.prstatus = 4
and ts.prisadjustment = 1
and te.practsum = 0
and tp.prisopen <> 0

6. Using the following query, replace the nnnnnnnn below with the prid gathered from step 3.

update prtimeentry set practcurve = (select practcurve from prtimeentry where prid = nnnnnnnn) where prid in
(select te.prid
from prtimesheet ts, prtimeentry te, prtimeperiod tp
where te.prtimesheetid = ts.prid
and ts.prtimeperiodid = tp.prid
and ts.prstatus = 4
and ts.prisadjustment = 1
and te.practsum = 0
and tp.prisopen <> 0)

This will correct any timesheets that were failing to adjust due to workaround that set the practcurve to null.

cheers.
sundar

Hi Sundar

Tried in the QA environment ...

1. Ran the below query -

select * from prtimeentry where prid in( 7855679, 7855678); -- these are the two time entries in the timesheet that needs to be posted and is still in approved status

2. Ran the below query -

select te.prid
from prtimesheet ts, prtimeentry te, prtimeperiod tp
where te.prtimesheetid = ts.prid
and ts.prtimeperiodid = tp.prid
and ts.prstatus = 4
and ts.prisadjustment = 1
and te.practsum = 0
and tp.prisopen <> 0
and te.prid in( 7855679, 7855678);

but did not get the ids mentioned in 1.

3. Ran the below query -

select te.prid
from prtimesheet ts, prtimeentry te, prtimeperiod tp
where te.prtimesheetid = ts.prid
and ts.prtimeperiodid = tp.prid

and ts.prstatus = 4 --------------------------------> commented line

and ts.prisadjustment = 1
and te.practsum = 0
and tp.prisopen <> 0
and te.prid in( 7855679, 7855678);

then I got the above two entries

Thanks
NJ

You write
select * from prtimeentry where prid in( 7855679, 7855678); -- these are the two time entries in the timesheet that needs to be posted and is still in approved status

The second query does not return those because



The status of the timesheet. Values are:

0 = Unsubmitted
1 = Submitted
2 = Rejected (returned)
3 = Approved
4 = Posted
5 = Adjusted

you are querying for the posted entries not the approved.

Sounds as if you problem is not what is described in the KB article

Martti K.

@Riya - do a

SELECT * FROM PRTIMEENTRY WHERE PRID = 5135776;

and see if there is anything wrong with the timeentry data (similar to what mansriva was talking about earlier in the thread)

If you post the output here (it'll only be the one line) we can have a looksee if anything looks "corrupt".

--

As for the "sqltrace" options - Sankhadeep_DC is correct, that is NOT going to help you (sorry Arun!) unless you are sqltracing the user who controls the "PostTimesheet" job and that is likely to be an admin user and that is going to create a whole HUGE amount of trace files for you to look at; so I DON'T recommend that in a PRODUCTION environment (maybe in DEV / TEST).

Dave,
I attached the result from your query

Hello All,
So now i'm guessing the reason that timesheet is not posted is because of that 4 hours that is in the timesheet and i'm not able to see for which project those are added.

I went and checked in my Prod Database in prtimeentry table PRACTSUM field the hours are showing only 4 (14400/3600) where as on the webpage the timesheet detailed page shows the total hours as 37.50 and summary page shows 41.50. Which is weird. Why the time is different in 3 places? where is this 4 hours coming from?

Any guesses Aprreciate it!


Thanks
RK

Looking like assginment/task were deleted after the timesheet was submitted.

Based on the result from Yoda's query the assignment id is 5018584

do you get anything back from

select prid, prtaskid, prresourceid
from prassignment
where prid = 5018584

If you do, then get the task from prtask, anything?

(part of Sundar's steps)

I think Sundar's steps would help you identify the 'ghost'


Connie

Run Post timesheet job
Run Post to Financials job
Look at Invalid Transactions to see if there are any errors. Example projects with hours charged but financails are closed.
If the above is clear, then run Post to WIP.

This will process all approved timesheets.

Dave,

I had a pending acutal after the timesheet was approved. I did the following.

Project is marked for closed and is awaiting the financial run this week.

Captured resources that had pending actuals.

Pulled back reporting periods containing the actual thru date.

Opened(adjust) resources timesheet to locate the investments (in this case projects/task) the resource put time to that week.

In this case we noticed the ETC's were not zeroed out. We went ahead zeroed them out.

made sure the related project/task was temporarily opened for time entry (meaning we'll close for time entry once this process is complete).

make sure charge code has a value.

ran the post timesheet job.

once completed - went back to review resources pending actuals and they were now zero.

went back and closed task and project for time entry.

ready for the financials to run later this week.

 

southwest:

Dave,

I had a pending acutal after the timesheet was approved. I did the following.

Project is marked for closed and is awaiting the financial run this week.

Captured resources that had pending actuals.

Pulled back reporting periods containing the actual thru date.

Opened(adjust) resources timesheet to locate the investments (in this case projects/task) the resource put time to that week.

In this case we noticed the ETC's were not zeroed out. We went ahead zeroed them out.

made sure the related project/task was temporarily opened for time entry (meaning we'll close for time entry once this process is complete).

make sure charge code has a value.

ran the post timesheet job.

once completed - went back to review resources pending actuals and they were now zero.

went back and closed task and project for time entry.

ready for the financials to run later this week.

 

Hello - I'm not sure what you are trying to say here - are you describing a problem you have or a potential solution to the original Timesheet is not posted from approved problem.   Assuming the latter, then I would guess that the project being "closed" at the time of posting would cause an issue.
 

Hello - Thanks for responding :)

I was sharing my resolution for a similar issue. You are correct...the project being "closed" at the time of posting would cause an issue.