DanielJiang Respected Contributor.
Respected Contributor.
400 views

Delete timesheet lines with zero hours

Jump to solution

Hi,

We are having large amount of timesheet records, which causes the ETL of Operational Reporting DB failed. Most of timesheet lines are empty line with zero hours. I want to delete those useless zero hours lines in closed timesheets and then reload the reporting DB.

Anyone has the casading delete statement to delete the TM_TIME_SHEET_LINES, and possibly TM_ACTUALS (?), TM_ACTUALS_EFFORT (?) or more?

Thanks,

Daniel

 

 

0 Likes
1 Solution

Accepted Solutions
Respected Contributor.. Ariel_V_PPM Respected Contributor..
Respected Contributor..

Re: Delete timesheet lines with zero hours

Jump to solution

Hi,

Maybe this information could help:

1. If the deleted references is request or project, you can use the following workaround to remove that timesheet line from backend via SQL then you shall be able to cancel or save the tm.

a. Make a backup of the tables that we will run delete statement.

b.
DELETE FROM TM_ACTUALS_EFFORT WHERE ACTUALS_ID IN (SELECT ACTUALS_ID FROM TM_ACTUALS TA WHERE TA.TIME_SHEET_LINE_ID=);
DELETE FROM TM_ACTUALS WHERE TIME_SHEET_LINE_ID=;
DELETE FROM TM_LINE_CHARGE_CODES WHERE TIME_SHEET_LINE_ID=;
DELETE FROM TM_USERS_TIME_APPROVERS WHERE APPROVER_TIME_SHEET_LINE_ID=;
DELETE FROM TM_TIME_SHEET_LINES WHERE TIME_SHEET_LINE_ID=;
commit;

You need to replace with the real timesheet line ID that you want to remove.

To figure out ts_line_id, run something like:

select * from tm_time_sheets where description like '%your information here%'

That would give you the ts_id for the timesheet belonging to this description. Then, run

select * from tm_time_sheet_lines where time_sheet_id=

this will give you the time_sheet_line_id linked with this timesheet, and identify the time_sheet_line_id that you need to delete.

 
0 Likes
2 Replies
Respected Contributor.. Ariel_V_PPM Respected Contributor..
Respected Contributor..

Re: Delete timesheet lines with zero hours

Jump to solution

Hi,

Maybe this information could help:

1. If the deleted references is request or project, you can use the following workaround to remove that timesheet line from backend via SQL then you shall be able to cancel or save the tm.

a. Make a backup of the tables that we will run delete statement.

b.
DELETE FROM TM_ACTUALS_EFFORT WHERE ACTUALS_ID IN (SELECT ACTUALS_ID FROM TM_ACTUALS TA WHERE TA.TIME_SHEET_LINE_ID=);
DELETE FROM TM_ACTUALS WHERE TIME_SHEET_LINE_ID=;
DELETE FROM TM_LINE_CHARGE_CODES WHERE TIME_SHEET_LINE_ID=;
DELETE FROM TM_USERS_TIME_APPROVERS WHERE APPROVER_TIME_SHEET_LINE_ID=;
DELETE FROM TM_TIME_SHEET_LINES WHERE TIME_SHEET_LINE_ID=;
commit;

You need to replace with the real timesheet line ID that you want to remove.

To figure out ts_line_id, run something like:

select * from tm_time_sheets where description like '%your information here%'

That would give you the ts_id for the timesheet belonging to this description. Then, run

select * from tm_time_sheet_lines where time_sheet_id=

this will give you the time_sheet_line_id linked with this timesheet, and identify the time_sheet_line_id that you need to delete.

 
0 Likes
Highlighted
Respected Contributor.. Ariel_V_PPM Respected Contributor..
Respected Contributor..

Re: Delete timesheet lines with zero hours

Jump to solution

Hello!,

Just want to know if the information was helpful.

Thanks!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.