Project and Portfolio Management Practitioners Forum
cancel

Stack trace msg/error while opening or creating time sheet for specific period

Highlighted
pradeep k.r.
Trusted Contributor.

Stack trace msg/error while opening or creating time sheet for specific period

Hello PPMer's,

We're getting attached stack trace with error log while trying to open/submit a timesheet.The timesheet ID's exist with "unsubmitted" status in DB backend. Since these timesheets cannot be opened through the UI, do we have any other way to "cancel" or "Approve" these timesheets.

Creating time sheets for any other periods works fine.


1 - 06/28/09 - 07/04/09 Abhishek K R - 6/28/09 - 7/4/09 Unsubmitted
1 - 06/21/09 - 06/27/09 Abhishek K R - 6/21/09 - 6/27/09 Unsubmitted

Thanks to suggest any workaround.

As I'm aware we don't remove any time sheets having time sheetlines with actuals in it.

Pradeep
12 REPLIES
Nisha Kurien
Honored Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Sometimes bumping the timesheet sequences from the database helps in resolving stack trace msg/errors.

Do you have a TEST environment where you can replicate this issue, so you can test if increasing the timesheet sequences helps resolve the issue?
Niraj Prabhu
Outstanding Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Pradeep,

Can you execute following piece of code on database. Let me know if you are seeing any results. I am just concerned if these columns are not initalized properly.

I will give you the update code in followup message once you confirm me this.

---------------------------------------------
SELECT *
FROM tm_actuals
WHERE act_cap_labor_cur_lcl IS NULL
OR act_cap_non_labor_cur_lcl IS NULL
OR act_op_labor_cur_lcl IS NULL
OR act_op_non_labor_cur_lcl IS NULL
OR ACT_CAP_LABOR_LCL IS NULL
OR ACT_CAP_LABOR_BSE IS NULL
OR ACT_CAP_NON_LABOR_LCL IS NULL
OR ACT_CAP_NON_LABOR_BSE IS NULL
OR ACT_OP_LABOR_LCL IS NULL
OR ACT_OP_LABOR_BSE IS NULL
OR ACT_OP_NON_LABOR_LCL IS NULL
OR ACT_OP_NON_LABOR_BSE IS NULL
---------------------------------------------
Thanks!
Niraj
Niraj P.
pradeep k.r.
Trusted Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Thanks Nisha,
Regarding replicating the issue in TEST instance, do you mean adding more time sheet sequences for the specified period?

Hi Niraj,
Executing the SQL query provided by you retrieves about 30K lines on prod DB.Let me know if you need more information.

Regards,
Pradeep
Niraj Prabhu
Outstanding Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Pradeep,

Following script should help you.

I would definately suggest to try this on specific rows and see if the issue is fixed.

------------------------------------------------------------------------------------------
update TM_ACTUALS set ACT_CAP_LABOR_LCL = 0 where ACT_CAP_LABOR_LCL is null;

update TM_ACTUALS set ACT_CAP_LABOR_BSE = 0 where ACT_CAP_LABOR_BSE is null;

update TM_ACTUALS set ACT_CAP_NON_LABOR_LCL = 0 where ACT_CAP_NON_LABOR_LCL is null;

update TM_ACTUALS set ACT_CAP_NON_LABOR_BSE = 0 where ACT_CAP_NON_LABOR_BSE is null;

update TM_ACTUALS set ACT_OP_LABOR_LCL = 0 where ACT_OP_LABOR_LCL is null;

update TM_ACTUALS set ACT_OP_LABOR_BSE = 0 where ACT_OP_LABOR_BSE is null;

update TM_ACTUALS set ACT_OP_NON_LABOR_LCL = 0 where ACT_OP_NON_LABOR_LCL is null;

update TM_ACTUALS set ACT_OP_NON_LABOR_BSE = 0 where ACT_OP_NON_LABOR_BSE is null;

UPDATE TM_ACTUALS SET act_cap_labor_cur_lcl = 'USD' WHERE act_cap_labor_cur_lcl is null;

UPDATE TM_ACTUALS SET act_cap_non_labor_cur_lcl = 'USD' WHERE act_cap_non_labor_cur_lcl is null;

UPDATE TM_ACTUALS SET act_op_labor_cur_lcl = 'USD' WHERE act_op_labor_cur_lcl is null;

UPDATE TM_ACTUALS SET act_op_non_labor_cur_lcl = 'USD' WHERE act_op_non_labor_cur_lcl is null;

commit;
------------------------------------------------------------------------------------------
Let me know how it goes.

Regards,
Niraj
Niraj P.
pradeep k.r.
Trusted Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hi Niraj,

Thanks a lot for response.

Since our's is a prod env, it's not feasible to try these steps out w/o knowing what impact this would have on the timesheet data. Do you want me to try these update steps for particular Timesheet ID to set "Null" values to "0" on our TEST DB? issue does'nt exist for same time period in TEST though. Apprciate your help in fixing the stack trace issue.

Pradeep
Mahen M
Acclaimed Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

I hope Niraj Solution is supported by HP, We also faced with same issue, I think we have implemented the same solution, It is long back, not exactly sure, Please raise a case with HP and share the solution with them, You can proceed with their guidance. If something goes wrong, they can help you out.

Before running the update Query take the backup of DB.

Regards,
Mahendran M
Niraj Prabhu
Outstanding Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hello Pradeep,

Yes we received this from HP on fixing the Stack Trace issue. The query updates the records which has NULL in specific column with its default value. You can try this solution. Please take a export of the result of the first query so that you can set them back to NULL if the solution doesnt fix the problem. You can use either timestamp or actuals ids to do so.

Let me know how it goes.

Thanks!
Niraj
Niraj P.
pradeep k.r.
Trusted Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hi Niraj,

Many thanks for your inputs. As suggested by you, I've queried the time_sheet_lines table based on time_sheet_id and got the actuals_id for the related tm_actuals (table to be updated with "0" default instead of null values).Based on the query results, I don't find any "null" values in the tm_actuals table for these actual_id's listed.Does it still help if I go ahead and update the "null" value rows with default "0" values??

select * from tm_time_sheet_lines where time_sheet_id = '169209'
-- Taking the time_sheet_line_id


select * from tm_actuals where time_sheet_line_id in ('1123267','1123268','1123269','1123270','1123271',
'1123272','1123273','1123276','1123277','1123278','1123279','1123280','1123283','1123284','1123285','1123286',
'1123291','1123292','1123296','1123297','1123298','1123300','1123301','1123303')
--gives the actuals id

These actuals_id/time_sheet_line_id are not listed in the first query output provided by you i.e checking for "null" values.Any thoughts here??

Thanks,
Pradeep
Niraj Prabhu
Outstanding Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hi Pradeep,

IF you dont find any "null" values in the tm_actuals table for these actual_id's listed; then what values do you see for these columns? If they are "null" then updating them with either default "0" or "USD" / Currency that you are following for PPM implementation should resolve the stack trace error.

I assume that since you would be doing this update on limited rows you will have ability to revert the column values in case you still see stack trace for this timesheet. Remember to clear cache before checking the timesheet.

Thanks!
Niraj
Niraj P.
pradeep k.r.
Trusted Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hi Niraj,

Not sure if you saw my attachment. There are no "null" value rows for those actual_id's in the tm_actuals table. The "Null" values are displayed for other actual_id's (not related to this timesheet_id ) based on the first query provided by you.

The values contained for these are default "0" or "USD" respectively.I may need to update the 30k odd rows for this table having "null" values as per first query. Since specific rows are not showing any "null" values.

Thanks,
Pradeep
Nisha Kurien
Honored Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hi Pradeep,

Apologies for the delay in getting back.

I wanted to know if this issue is occurring in a different environment like a DEV or TEST? If it is then you have more flexibility in running DB/SQL scripts to resolve the issue.

Often we have had this stack trace errors in our DEV and TEST environments. So we did have more flexibility in running different scripts.

We usually perform the following:

For the tables:
TM_TIME_SHEETS
TM_TIME_SHEET_LINES
TM_ACTUALS

we increase the sequence values. Sometimes we just increase it to around 1000 to even 20000.

These are the SQL we run.

1) select TM_TIME_SHEETS_S.nextval from dual
2) select TM_TIME_SHEET_LINES_S.nextval from dual
3) select TM_ACTUALS_S.nextval from dual

I guess I would look to the other folks here at the forum to confirm if running these SQL statements in a prod environment wouldnt cause any problems later. (as I mentioned we faced this issue in our non-prod environment so it was pretty comfortable to run such SQL commands)

- Nisha Kurien


Niraj Prabhu
Outstanding Contributor.

Re: Stack trace msg/error while opening or creating time sheet for specific period

Hello Pradeep,

Did you receive any reply from HP support team on this? I think you should involve them on highest priority as this is production issue. They should able to confirm on what is the best solution for this scenario.

Regards,
Niraj
Niraj P.